Viewing details, SQLCA, and text for an SQLCODE

You can view detailed data captured for a specific SQLCODE. You can access both the SQL Communication Area (SQLCA) and the SQL text for the activity of interest.

Procedure

To view details, SQLCA, and text for an SQLCODE:

On the IBM DB2 Query Monitor for z/OS panel (the main menu)), specify option 2, and press Enter.

On the DB2 QM SQL Code Summary panel, either specify the S line command next to the SQLCODE for which you want to view details or specify no grouping (Group by *).

The DB2 QM SQL Code Detail Display panel displays the SQLCODE details:

Figure 1. DB2 QM SQL Code Detail Display panel

YYYY/MM/DD HH:MM:SS  --- DB2 QM SQL Code Detail Display ---  Row 1 of 5        
Option  ===>                                                  Scroll ===> CSR  
 QM Subsystem : QM01         Interval Start Date: MM/DD/YYYY  Time: HH:MM:SS 
                               Interval End   Date: CURRENT     Time: CURRENT  
 Detail data captured for SQLCODE: -204                                        
   Primary Authid:  AUTHID01                                                    
   DBRM/Package:    PACKAGE1                                                  
                                                                               
C: S-SQL Text, C - SQLCA, SF-FORMATTED SQL TEXT                                                    
------------------------------------------------------------------------     > 
CMD SSID Plan     DBRM/Package  JOBNAME  Stmt # Collection ID Sect# Authid     
 -  ---- -------- ------------- -------- ------ ------------- ----- ------     
 _  DB01 DSNTEP2  DSN@EP2L      JOB0001   1,494 DSNTEP2          1  USERA    
 _  DB02 DSNTEP2  DSN@EP2L      JOB0002   1,559 DSNTEP2          1  USERA    
 _  DB01 DSNTEP2  DSN@EP2L      JOB0001   1,494 DSNTEP2          1  USERA    
 _  DB02 DSNTEP2  DSN@EP2L      JOB0002   1,559 DSNTEP2          1  USERA    
 _  DB01 DSNTEP2  DSN@EP2L      JOB0001   1,494 DSNTEP2          1  USERA    
***************************** Bottom of Data **********************************

Specify one of the following line commands to view the information that you want.

C line command

Use this line command to view the SQL Communication Area (SQLCA) for the selected item. The SQLCA Display panel is displayed:

Figure 2. SQLCA Display panel

YYYY/MM/DD HH:MM:SS  ----------- SQLCA Display ----------  Row 1 of 32         
 Option  ===> ________________________________________________ Scroll ===> CSR  
                                                                                
  DB2 SSID: DBD01   Plan: DSNTEP2    DBRM: DSN@EP2L  Coll: DSNTEP2               
                   Cursor:                     Section:    1                    
                   Stmt:   1,494    Type: PREPARE                               
 ------------------------------------------------------------------------    +  
  DSNT408I SQLCODE = -204, ERROR:  USERA.U_TAB IS AN UNDEFINED NAME           
  DSNT418I SQLSTATE   = 42704 SQLSTATE RETURN CODE                              
  DSNT415I SQLERRP    = DSNXOTL SQL PROCEDURE DETECTING ERROR                   
  DSNT416I SQLERRD    = -500  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION         
  DSNT416I SQLERRD    = X'FFFFFE0C'  X'00000000'  X'00000000'                   
           X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC                 
           INFORMATION                                                          
                                                                                
  SQLCAID       SQLCA                                                           
  SQLCABC       136                                                             
  SQLCODE       -204                                                            
  SQLERRML      13                                                              
  SQLERRMC      USERA.U_TAB                                                   
  SQLERRP       DSNXOTL

S line command

Use this line command to display SQL text. The Display SQL Statement Text panel is displayed:

Figure 3. Display SQL Statement Text panel

YYYY/MM/DD HH:MM:SS   ---- Display SQL Statement Text ----   Row 1 of 3
Option ===> ________________________________________________ Scroll ===> CSR

 DB2 SSID: DB01 Plan: DSNTEP2    DBRM: DSN@EP2L    Coll: DSNTEP2
           Cursor: N/A              Section: 1
------------------------------------------------------------------------

     SELECT UC1, HEX(UC1), UG1, HEX(UG1)
     FROM U_TAB                         
     WHERE UF > 1234 ORDER BY UC1       
***************************** Bottom of Data **********************************

Tip: On this panel, you can use the VIEW command to open an ISPF VIEW session where you can edit and save the SQL text. See Viewing SQL text.

SF line command

Use this line command to display formatted SQL text. The Display SQL Statement Text panel is displayed:

Figure 4. Display SQL Statement Text panel

YYYY/MM/DD HH:MM:SS   ---- Display SQL Statement Text ----   Row 1 of 4
Option ===> ________________________________________________ Scroll ===> CSR

 DB2 SSID: DB01 Plan: DSNTEP2    DBRM: DSN@EP2L    Coll: DSNTEP2
           Cursor: N/A              Section: 1
------------------------------------------------------------------------

     SELECT UC1, HEX(UC1), UG1, HEX(UG1)
       FROM U_TAB                       
       WHERE UF > 1234                  
       ORDER BY UC1                     
***************************** Bottom of Data **********************************

Tip: On this panel, you can use the VIEW command to open an ISPF VIEW session where you can edit and save the SQL text. See Viewing SQL text.

Exit (PF3) back to the DB2 QM SQL Code Detail Display panel.