Datacom SQL Tracing - Activation and Deactivation

Products

• Datacom
• Datacom/DB
• Datacom/AD

Issue/Introduction

Support may request SQL traces to analyze specific query behaviors or performance issues within the Multi-User Facility (MUF).

Tracing is managed through the batch utility DBUTLTY and output is directed to the PXX or to one of the PXXxxx## Spool files if using the SYSOUT MUF Startup Option.

Cause

Technical troubleshooting of SQL queries requires high-granularity diagnostic data typically suppressed during standard operations to minimize overhead on the Multi-User Facility (MUF). Specific trace classes must be enabled dynamically to capture internal execution paths, expressions, or error states.

Resolution

1. Verify the current status of the Multi-User Facility (MUF) and ensure the DBUTLTY batch utility has connectivity to the database directory (CXX).
2. Run the DBUTLTY program with this input (SYSIN):
   
   

   • COMM OPTION=ALTER,TRACE=NONE                                <--turn off any existing activated traces.
     COMM OPTION=CLRPXX  - or -                                  <-- clears the existing PXX SYSOUT or PXX dump.
     COMM OPTION=CLRSQL                                          <-- closes the current file and opens a new Spool file if using SYSOUT option.
     COMM OPTION=ALTER,TRACE=<trace_class>,JOBNAME=<jobname>     <-- turn on trace for specific jobname. Use multiple statements for additional traces.

   • Note: Multiple COMM OPTION=ALTER statements may be used to enable concurrent trace classes (e.g., TRACEMSG, TRACEDETAIL).
3. Execute the SQL application or query using the SQL Command Processor (DBSQLPR) to generate the diagnostic data.
4. Deactivate the diagnostic tracing immediately following the completion of the application run, using DBUTLTY with this input:
   

   • COMM OPTION=ALTER,TRACE=NONE                        <--turn off any existing activated traces.

5. Extract the collected trace data from the Partitioned Index (PXX) for analysis:
   1. II using the PXX file and not using SYSOUT in the MUF Startup Options, run DBUTLTY with this input, then attach the output of the DBUTLTY job and the entire MUF output to the support case for review.

      • REPORT AREA=PXX,DUMPS=TRACE

   2. If using the SYSOUT setting, attach the entire MUF output to the support case for review.

Additional Information

COMM ALTER (Modify Error Table)