Collection under PLANNAME DSNTIAUL

Products

• Detector for DB2 for z/OS

Issue/Introduction

The IBM DSNTIAUL program and some other Db2 programs handle SQL statements in a way that seems Detector does not capture the SQL Text correctly.  
   
1) Run the following batch job: 

//SELECT   EXEC PGM=IKJEFT01                          
//STEPLIB   DD  DISP=SHR,DSN=hlq.SDSNEXIT    
//          DD  DISP=SHR,DSN=hlq.SDSNLOAD      
//*                                                   
//SYSTSPRT  DD  SYSOUT=*                              
//SYSPRINT  DD  SYSOUT=*                              
//SYSOUT    DD  SYSOUT=*                              
//*                                                   
//SYSREC00 DD DSN=hlq.DATA00,   
//            SPACE=(TRK,(1,1)),DISP=(MOD,CATLG)      
//SYSREC01 DD DSN=hlq.DATA01,   
//            SPACE=(TRK,(1,1)),DISP=(MOD,CATLG)      
//SYSREC02 DD DSN=hlq.DATA02,   
//            SPACE=(TRK,(1,1)),DISP=(MOD,CATLG)      
//SYSPUNCH DD DSN=hlq.PUNCH,    
//            SPACE=(TRK,(1,1)),DISP=(MOD,CATLG)      
//*                                                   
//SYSTSIN   DD  *                                     
 DSN SYSTEM(ssid)                                     
 RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL') -  
      LIB('ssid.RUNLIB.LOAD')                         
//*                                                   
//SYSIN     DD  *                                     
 SELECT CHAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1;     
 SELECT CHAR(CURRENT TIME) FROM SYSIBM.SYSDUMMY1;     
 SELECT CHAR(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
//                  
                                 
2) Go to Detector using DT 

3) Q - Dynamic SQL next to the PLANAME DSNTIAUL

4) Q - SQL Text next to the SQL_TEXT SELECT CHAR(CURRENT DATE) FROM ...;       

5) The result is displayed as below.
SELECT  CHAR ( CURRENT DATE )      
   FROM SYSIBM.SYSDUMMY1 ;         
 SELECT  CHAR ( CURRENT TIME )     
   FROM SYSIBM.SYSDUMMY1 ;         
 SELECT  CHAR ( CURRENT TIMESTAMP )
   FROM SYSIBM.SYSDUMMY1 ;         

6) Q - SQL Text next to the SQL_TEXT SELECT CHAR(CURRENT TIME) FROM ...; 
SELECT  CHAR ( CURRENT TIME )      
   FROM SYSIBM.SYSDUMMY1 ;         
 SELECT  CHAR ( CURRENT TIMESTAMP )
   FROM SYSIBM.SYSDUMMY1 ;            

7) Q - SQL Text next to the SQL_TEXT SELECT CHAR(CURRENT TIMESTAMP) FROM ...;
SELECT  CHAR ( CURRENT TIMESTAMP )
   FROM SYSIBM.SYSDUMMY1 ;

It looks like Detector concatenates the input lines together so the first SELECT statement has the three SELECT statements in the SQL Text,
the second SELECT statement has the two SELECT statements in the SQL Text, and the third SELECT statement only has one SELECT statement in the SQL Text.

Resolution

The root problem is caused by how IBM DSNTIAUL program and some other IBM Db2 programs handle SQL statements.

During PREPARE processing, Db2 ignores everything after the semicolon. However, Db2 still includes that information in its control block if the program provides a length that accommodates extra information after the semicolon.
 
Detector copies the SQL Text from the Db2 control block using the embedded SQL Text length provided by the program. However since Db2 ignores all SQL text following the semicolon, the metrics collected by Detector show only the activity that occurred within Db2 executing the SQL Text preceding the semicolon. But the SQL Text after the semicolon is captured by Detector.
 
The appropriate way to correct the problem would be to change the DSNTIAUL program to PREPARE the SQL Text in a buffer with an SQL Text length that excludes the SQL Text following the semicolon.