CA SYSVIEW for DB2 batch report for IFCID 376

Document ID : KB000010756
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

CA SYSVIEW for DB2 does not provide any batch report to work with IFCID 376 and check for DB2 11 incompatibilities. You can work with online menus with option '17 SQL Incompatibilities (IFCID 376)' but there is no request to run in batch.

Instructions:

You can run a batch report over IFCID 376 with the following steps.
 
. Update HIST-STATS-RECS parameter to collect IFCID 376 in History files.
HIST-STATS-RECS=(1,2,...,376)
 
. Unload the History files to a SMF data set.
 
. Over the SMF data set you can run a request to list the DB2 11 incompatibilities.
 
 DECLARE APPL-SQL-INCOMPAT STMT-TYPE (A7) VALOF                     
  IF     STMT-TYPE-FLAG = X'8000' RESULTIS 'Dynamic'                
  ELSE                            RESULTIS 'Static '                
  IFEND ;                                                           
                                                                    
 DECLARE APPL-SQL-INCOMPAT       REASON    (A25) VALOF              
  IF     SYSIBM-CHAR-FUNC = 1    RESULTIS 'CHAR(dec) Format'        
  ELSEIF SYSIBM-CHAR-FUNC = 2    RESULTIS 'VARCHAR/CAST(dec) Format'
  ELSEIF SYSIBM-CHAR-FUNC = 3    RESULTIS 'Unsupported Timestamp'   
  ELSEIF SYSIBM-CHAR-FUNC = 4    RESULTIS 'DB2 10 default SQL path' 
  ELSEIF SYSIBM-CHAR-FUNC = 5    RESULTIS 'CUBE unqual UDF name'    
  ELSEIF SYSIBM-CHAR-FUNC = 6    RESULTIS 'ROLLUP unqual UDF name'  
  ELSEIF SYSIBM-CHAR-FUNC = 7    RESULTIS 'Incompatible conversion' 
  ELSEIF SYSIBM-CHAR-FUNC = 8    RESULTIS 'Match CALL statement'    
  ELSEIF SYSIBM-CHAR-FUNC = 1101 RESULTIS 'INSERT w/o XMLDOCUMENT'  
  ELSEIF SYSIBM-CHAR-FUNC = 1102 RESULTIS 'XPath evaluation error'  
  ELSEIF SYSIBM-CHAR-FUNC = 1103 RESULTIS 'RLF governing'           
  ELSEIF SYSIBM-CHAR-FUNC = 1104 RESULTIS 'Long CLIENT_ACCTNG SR'   
  ELSEIF SYSIBM-CHAR-FUNC = 1105 RESULTIS 'Long CLIENT_APPLNAME SR' 
  ELSEIF SYSIBM-CHAR-FUNC = 1106 RESULTIS 'Long CLIENT_USERID SR'   
  ELSEIF SYSIBM-CHAR-FUNC = 1107 RESULTIS 'Long CLIENT_WRKSTNNAME'  
  ELSEIF SYSIBM-CHAR-FUNC = 1108 RESULTIS 'Long client RLF SR value'
  ELSEIF SYSIBM-CHAR-FUNC = 1109 RESULTIS 'CAST(strng AS TIMESTAMP)'
  ELSEIF SYSIBM-CHAR-FUNC = 1110 RESULTIS 'SPACE int greater 32764' 
  ELSEIF SYSIBM-CHAR-FUNC = 1111 RESULTIS 'VARCHAR int greater 32764'
  ELSE                           RESULTIS 'QW0376FN Unknown Type'   
  IFEND ;                                                           
                                                                    
*                                                                   
INCOMPBR: TRACE (SUBTOT=OFF SL=1 LS=277 IS=NO)                      
 << APPL-SQL-INCOMPAT                                               
    PROGRAM-NAME-VAR    (PF=A18 HD='Program name' HC=L)             
    REASON              (HD='Incompatibility reason' HC=L)          
    SYSIBM-CHAR-FUNC    (PF=NL4 HD='Rsn','Code' HC=L)               
    STMT-ID             (PF=H16 HD='Stmt ID (HEX)' HC=L)            
    STMT-TYPE           (PF=A7  HD='Type' HC=L)                     
    STMT-NUM            (PF=NL8 HD='Stmt','number' HC=L)            
    SECTION-NUM         (PF=NL8 HD='Section','number' HC=L)         
    CONN-NAME           (PF=A8  HD='ConnName' HC=L)                 
    AUTH-ID             (PF=A8  HD='Auth ID' HC=L)                  
    CORR-ID             (PF=A12 HD='Corr ID' HC=L)                  
    PLANNAME            (PF=A8  HD='PlanName' HC=L)                 
    COLLECTION-ID-VAR   (PF=A18 HD='Collection ID' HC=L)            
    VERSION-NAME-VAR    (PF=A18 HD='Version' HC=L)                  
    CONTOKEN            (PF=H16 HD='Contoken (HEX)' HC=L)           
    ENDUSER-USERID      (PF=A16 HD='Enduser' HC=L)                  
    ENDUSER-WORKSTATION (PF=A18 HD='Workstation' HC=L)              
    ENDUSER-TRANSACTION (PF=A32 HD='Transaction' HC=L)              
    EVENT-TIME          (PF=DATETIME HD='First exec' HC=L)          
    WHERE STMT-TYPE-FLAG = X'8000'                                  
 >>                                                                 
 << APPL-SQL-INCOMPAT                                      
    PROGRAM-NAME-VAR    (PF=A18 HD='Program name' HC=L)    
    REASON              (HD='Incompatibility reason' HC=L) 
    SYSIBM-CHAR-FUNC    (PF=NL4 HD='Rsn','Code' HC=L)      
    STMT-ID             (PF=H16 HD='Stmt ID (HEX)' HC=L)   
    STMT-TYPE           (PF=A7  HD='Type' HC=L)            
    STMT-NUM            (PF=NL8 HD='Stmt','number' HC=L)   
    SECTION-NUM         (PF=NL8 HD='Section','number' HC=L)
    CONN-NAME           (PF=A8  HD='ConnName' HC=L)        
    AUTH-ID             (PF=A8  HD='Auth ID' HC=L)         
    CORR-ID             (PF=A12 HD='Corr ID' HC=L)         
    PLANNAME            (PF=A8  HD='PlanName' HC=L)        
    COLLECTION-ID-VAR   (PF=A18 HD='Collection ID' HC=L)   
    VERSION-NAME-VAR    (PF=A18 HD='Version' HC=L)         
    CONTOKEN            (PF=H16 HD='Contoken (HEX)' HC=L)  
    ENDUSER-USERID      (PF=A16 HD='Enduser' HC=L)         
    ENDUSER-WORKSTATION (PF=A18 HD='Workstation' HC=L)     
    ENDUSER-TRANSACTION (PF=A32 HD='Transaction' HC=L)     
    EVENT-TIME          (PF=DATETIME HD='First exec' HC=L) 
    WHERE STMT-TYPE-FLAG NE X'8000'                        
  >>                                                       
                                                           
 TITLE = ('SQL Incompatibilities (IFCID 376)')             
 PRINT (INCOMPBR)                                          
;