








	*SMF Tools*

DB2 V11 Incompatibility Report from SMF 102 IFCID 376 Records

It's easy to report on SMF data!

We have a low-cost 4GL report writer especially for SMF files. It's called Spectrum SMF Writer.

Spectrum SMF Writer handles the difficult SMF record parsing for you automatically. You just specify which fields you want to see.

Spectrum SMF Writer also converts the arcane date and time fields and reformats them into an attractive report.

Plus Spectrum SMF Writer even exports SMF data as comma delimited files to use on your PC.

The sample SMF report below was created with Spectrum SMF Writer, the low-cost 4GL SMF report writer.

It reads as input the SMF file and selects just the type 102 (DB2 Audit) records with an IFCID of 376 (unique incompatible changes in V11). (See SMF 102 record layout.)

We print a report showing each event of an incompatible change. The report shows a description of what the incompatible change consisted of, and the transaction, program name and statement number containing the error. It also shows the plan name, package collection name and operator ID.

Note that the actual SMF record just contains codes for the incompatible change. Our report code expands those numeric values into descriptive texts.

Spectrum SMF Writer also uses a special I/O exit to parse the variably formatted SMF 102 records. This lets us easily print the hard-to-access details in the IFCID 376 section.

These incompatible change events are sorted into program name order.

All of this with just a few lines of code!
Why not install a Spectrum SMF Writer trial right now and start making your own SMF reports!

These Spectrum SMF Writer Statements:

*SELECT THE INPUT FILE WITH DEFINITIONS FOR 102 V11                     
INPUT: SM102B11                                                         
            IOEXIT('GENNORM','A 102 --- 036 2I N ] 028 004 2 00376')    
*                                                                       
*SELECT THE RECORDS WE WANT FOR THIS REPORT                             
INCLUDEIF: SM102RTY = 102                                               
           AND QWHSIID = 376                                            
*                                                                       
COMPUTE: DB2GRP = WHEN(QWHADSGN=' ') ASSIGN(QWHSSSID)                   
                 ELSE ASSIGN(QWHADSGN)                                  
*                                                                       
COMPUTE: F376DESC =                                                     
     WHEN(QW0376FN = 1) ASSIGN('V9 SYSIBM.CHAR(DECIMAL-EXPR) FUNCTION') 
     WHEN(QW0376FN = 2) ASSIGN('VARCHAR/CAST DECIMAL')                  
     WHEN(QW0376FN = 3) ASSIGN('UNSUPPORTED TIMESTAMP VALUE')           
     WHEN(QW0376FN = 4) ASSIGN('RESERVED WORD:ARRAY_EXISTS')            
     WHEN(QW0376FN = 5) ASSIGN('RESERVED WORD:CUBE')                    
     WHEN(QW0376FN = 6) ASSIGN('RESERVED WORD:ROLLUP')                  
     WHEN(QW0376FN = 7) ASSIGN('POSSIBLE SQLCODE -301')                 
     WHEN(QW0376FN = 8) ASSIGN('DDF_COMPATIBILITY/JAVA EXCP')           
     WHEN(QW0376FN = 9) ASSIGN('TIMEZONE IGNORE')                       
     WHEN(QW0376FN = 10) ASSIGN('LTRIM, RTRIM OR STRIP')                
     WHEN(QW0376FN = 11) ASSIGN('SELECT INTO WITH SET OPERATOR')        
     WHEN(QW0376FN = 1101) ASSIGN('XML NODE')                           
     WHEN(QW0376FN = 1102) ASSIGN('XPATH PROCESSING')                   
     WHEN(QW0376FN = 1103) ASSIGN('ASUTIME')                            
     WHEN(QW0376FN = 1104) ASSIGN('CLIENT_USERID')                      
     WHEN(QW0376FN = 1105) ASSIGN('CLIENT WKSTNAME')                    
     WHEN(QW0376FN = 1106) ASSIGN('CLIENT_APPLNAME')                    
     WHEN(QW0376FN = 1107) ASSIGN('CLIENT_ACCTNG')                      
     WHEN(QW0376FN = 1108) ASSIGN('CLIENT SPECIAL REGISTER TRUNCATED')  
     WHEN(QW0376FN = 1109) ASSIGN('CAST(STRING AS TIMESTAMP)')          
     WHEN(QW0376FN = 1110) ASSIGN('BUILT-IN FUNCTION: SPACE')           
     WHEN(QW0376FN = 1111) ASSIGN('BUILT-IN FUNCTION: VARCHAR')         
     WHEN(QW0376FN = 1112) ASSIGN('XML ELEMENT IS SERIALIZED')          
*                                                                       
COMPUTE: F376TYP =                                                      
     WHEN(QWHCATYP = 1) ASSIGN('TSO ')                                  
     WHEN(QWHCATYP = 2) ASSIGN('DB2 CALL ATTACH')                       
     WHEN(QWHCATYP = 11) ASSIGN('DB2 UTILITIES')                        
     WHEN(QWHCATYP = 12) ASSIGN('RRSAF')                                
     WHEN(QWHCATYP = 8) ASSIGN('DRDA ')                                 
*                                                                       
COMPUTE: F376TYPE =                                                     
     WHEN(QW0376TY = X'8000') ASSIGN('DYNAMIC')                         
     WHEN(QW0376TY = X'4000') ASSIGN('STATIC')                          
*                                                                       
*SELECT THE DATA FIELDS TO PUT IN REPORT                                
COLUMNS:                                                                
     SM102DTE('SMF]LOG DATE' 10)                                        
     SM102SID('SYSTEM NAME' 6)                                          
     DB2GRP('DSHARING GROUP' 10)                                        
     QWHAMEMN('DB2 MEMBER NAME' 10)                                     
     QW0376PL('PLAN NAME FOR QUERY' 10)                                 
     EDIT_QW0376PC_VAR('COLLECTION' 10)                            
     EDIT_QW0376PN_VAR('PROGRAM' 8)                               
     QW0376SN('STATEMENT NUMBER' 8)                                     
     F376TYPE('TIPO STATEMENT' 10)                                      
     QW0376FN('FUNCTIONCODE' 8)                                         
     F376DESC('FUNCTION DESCRIPTION' 20)                   
     QWHCAID('AUTHID' 10)                                               
     QWHCCN('CONNECTION NAME' 10)                                       
     QWHCEUWN('END USER WORKSTATION NAME' 20)                           
     F376TYP('TYPE' 4)                                            
     QWHCCV('CORRELATION ID VALUE' 14)                                  
*                                                                       
SORT:  QWHSSSID QWHAMEMN QW0376PL EDIT_QW0376PC_VAR EDIT_QW0376PN_VAR

Produce This SMF Report:

                                                          DATA FROM SM102B11                                                                                                                                                                                               PAGE     1

 SMF DATE   SYSTEM DSHARING G DB2 MEMBER PLAN NAME  COLLECTION PROGRAM  STATEMEN TIPO STATE FUNCTION FUNCTION DESCRIPTION   AUTHID   CONNECTION END USER WORKSTATION TYPE CORRELATION ID                                   
 __________ ______ __________ __________ __________ __________ ________ ________ __________ ________ ____________________ __________ __________ ____________________ ____ ______________

  01/15/19   SYA0   DSNDB0Y   DB1Y       DISTSERV  NULLID     SYSLH200        0  DYNAMIC      1,104   CLIENT_USERID       GAWASDB    SERVER     192.168.62.37        DRDA   db2jcc_appli
  01/15/19   SYA0   DSNDB0Y   DB1Y       DISTSERV  NULLID     SYSLH200        0  DYNAMIC      1,105   CLIENT WKSTNAME     GAWASDB    SERVER     192.168.62.37        DRDA   db2jcc_appli                         
  01/15/19   SYA0   DSNDB0Y   DB1Y       DISTSERV  NULLID     SYSLH200        0  DYNAMIC      1,106   CLIENT_APPLNAME     GAWASDB    SERVER     192.168.62.37        DRDA   db2jcc_appli                         
  01/15/19   SYA0   DSNDB0Y   DB1Y       DISTSERV  NULLID     SYSLH200        0  DYNAMIC      1,107   CLIENT_ACCTNG       GAWASDB    SERVER     192.168.62.37        DRDA   db2jcc_appli                         
  01/15/19   SYA0   DSNDB0Y   DB1Y       DISTSERV  NULLID     SYSLH200        0  DYNAMIC      1,104   CLIENT_USERID       GAWASDB    SERVER     192.168.62.37        DRDA   db2jcc_appli                         
  01/15/19   SYA0   DSNDB0Y   DB1Y       DISTSERV  NULLID     SYSLH200        0  DYNAMIC      1,105   CLIENT WKSTNAME     GAWASDB    SERVER     192.168.62.37        DRDA   db2jcc_appli                         
  01/15/19   SYA0   DSNDB0Y   DB1Y       DISTSERV  NULLID     SYSLH200        0  DYNAMIC      1,106   CLIENT_APPLNAME     GAWASDB    SERVER     192.168.62.37        DRDA   db2jcc_appli

See Other Sample SMF Reports