Sunday, December 21, 2008

Dude! Where's my Trace?

While an administrator can turn on traces, it's also possible for a developer to put some tracing commands in his or her WebFOCUS code.

When WebFOCUS traces are turned on, you can display them as comments within your output's HTML -- something called "client-side tracing." If you pick an output that does not generate an HTML page (e.g., PDF) then you will not be able to see the trace.

While most WebFOCUS environmental settings are basically toggle switches with ON and OFF settings, traces are different. There is a TRACEON setting as well as one for TRACEOFF. For each, you identify the trace component that you want to turn on/off. For example, I may want to see the SQL statements being generated from the 4GL commands; I need to TRACEON that particular SQL component (called STMTRACE -- comparable to the FSTRACE4 for FOCUS).

If you want to query the current environmental settings, you can use "?" for the trace component value. There is one setting to see the trace options turned on (SET TRACEON = ?) and another for those turned off (SET TRACEOFF = ?). With a syntax inconsistent with other settings, the WebFOCUS tracing feature is less than intuitive.

See the WebFOCUS Server Console diagnostics custom-filtering page for a full list of the trace components. Relational data adapter traces include:
  • SQLDI (like FSTRACE) - SQL physical layer (BX, BY messages)
  • SQLAGGR (like FSTRACE3) - optimization information (BR messages)
  • STMTRACE (like FSTRACE4) - SQL statements (AE, AF messages)
  • SQLCALL - exchange between physical and logical layers of the data adapter (BW messages)
For non-relational interfaces, you have:
  • ADBSIN (Adabas) - has 4 trace levels
  • IDMS - has 2 levels
  • IMS - has 4 levels
  • M204
  • Others (Nomad, Millenium, Supra/Total, CA-Datacom)
  • Proprietary traces for IBI developers (IBITROUT)

Some components do not seem to be made for HTML tracing -- their messages are not formatted properly for HTML standards. For example, NWH, NWH2, and CEH components may corrupt your HTML output. The R1H communications component may have a glitch -- it starts two WebFOCUS agents which never end. So, do not tinker with traces on the production server.

Also, instead of identifying a specific trace component, you can say "ALL" -- that is fine for turning OFF all traces, but you probably should not turn ON all traces.

In addition to the trace component, there is an optional parameter (separated by a forward slash) for the "Trace Level" as some components can display different levels of details. If you omit the trace level, you get all traces for that component. Setting the trace level to 1 will give you the top level of the details; 0 will turn it off. For the STMTRACE, you can specify the RDBMS adapter as the trace level, e.g., "STMTRACE/DB2/CLIENT"

Another optional parameter (again separated by a forward slash) is the location of the trace output. Frankly, I question the quality of this feature -- traces are okay going into the HTML comments, but do not seem to go to physical file locations properly (there is an additional setting of TRACEUSER to identify the filename). To send the trace output to the HTML in your browser, use "CLIENT" for this parameter.

Here is the syntax:
SET TRACEON = component [/tracelevel [ /filename]]
SET TRACEOFF = component [/tracelevel [ /filename]]

Here are some examples of the trace settings:
SET TRACEOFF = ALL(turn all the traces off)
SET TRACEOFF = ?(show the inactive trace settings)

SET TRACEON = MFP//CLIENT(MFD parsing; displays FG messages)
SET TRACEON = PRH//CLIENT(FEX parsing; displays AG messages)
SET TRACEON = STMTRACE//CLIENT(SQL calls; displays AE, AF messages)
SET TRACEON = SQLDI//CLIENT(SQL physical layer; displays BX, BY messages)
SET TRACEON = SQLCALL//CLIENT(exchange between SQL physical & logical layer; displays BW messages)
SET TRACEON = SQLAGGR//CLIENT(SQL optimization messages; displays BR messages)
SET TRACEON = ESSBASE//CLIENT(Essbase calls; displays CE, CF, CG, CH messages)
SET TRACEUSER = ON(turn on the user tracing)

In FOCUS, to see your generated SQL and optimization messages, you would have allocated some FSTRACE files and looked at the contents afterwards. To do something comparable in WebFOCUS, you use these commands and look in your HTML output:
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET XRETRIEVAL = OFF
SET TRACEUSER = ON

Note: The XRETRIEVAL setting turns off the actual execution of the procedure, so you can see the SQL without actually running it and waiting for the answer set.

So while this WebFOCUS tracing feature may test your nerve and skill, it can be very useful to see what is going on under the covers.

No comments:

About Me

My photo

I am a project-based software consultant, specializing in automating transitions from legacy reporting applications into modern BI/Analytics to leverage Social, Cloud, Mobile, Big Data, Visualizations, and Predictive Analytics using Information Builders' WebFOCUS. Based on scores of successful engagements, I have assembled proven Best Practice methodologies, software tools, and templates.

I have been blessed to work with innovators from firms such as: Ford, FedEx, Procter & Gamble, Nationwide, The Wendy's Company, The Kroger Co., JPMorgan Chase, MasterCard, Bank of America Merrill Lynch, Siemens, American Express, and others.

I was educated at Valparaiso University and the University of Cincinnati, where I graduated summa cum laude. In 1990, I joined Information Builders and for over a dozen years served in regional pre- and post-sales technical leadership roles. Also, for several years I led the US technical services teams within Cincom Systems' ERP software product group and the Midwest custom software services arm of Xerox.

Since 2007, I have provided enterprise BI services such as: strategic advice; architecture, design, and software application development of intelligence systems (interactive dashboards and mobile); data warehousing; and automated modernization of legacy reporting. My experience with BI products include WebFOCUS (vendor certified expert), R, SAP Business Objects (WebI, Crystal Reports), Tableau, and others.