Blog
DB2utor

Categories

June 11, 2008

Where to Find DB2 V9 IFCID Descriptions

I recently needed to find the description and layout of IFCID 184. After spending an hour or so futilely pulling up different DB2 V9 manuals in PDF format and searching for IFCID, I sent a message to my friend Roger Miller. His reply? Check the DB2 Information Center.

I felt silly because I know about this website, but for me, looking in manuals is a habit. But now my browser has a permanent tab with this Information Center link.

Back when I did more DB2 systems work, I knew I'd set up some DB2 tables containing the description of the different trace types (ACCOUNTING, AUDIT, GLOBAL, MONITOR, PERFORMANCE, STATISTICS). And I knew that the table definitions and the data loaded into these tables was contained in hlq.SDSNSAMP(DSNWMSGS). But when I searched for this in my DSN910.SDSNSAMP library, I couldn't find it. I couldn't find it when I went back to DSN810.SDSNSAMP either. Turns out that this member was pulled and is now located in hlq.SDSNIVPD(DSNWMSGS).

So I took the DDL from DSN910.SDSNIVPD(DSNWMSGS) and ran this in SPUFI to create the tables I needed to load. Here's the SQL:

CREATE TABLESPACE TRACETS                  
  IN DSN8D91A                              
  USING STOGROUP DSN8G910#                  
                                           
CREATE TABLE DSN8910.TRACE_DESCRIPTIONS    
  (IFCID       INTEGER  NOT NULL,          
   NAME        CHAR(20)  NOT NULL,         
   DESCRIPTION CHAR(60) NOT NULL,          
   SEQ         INTEGER  NOT NULL)          
   IN DSN8D91A.TRACETS#                     
                                           
CREATE TABLE DSN8910.TRACE_TYPES            
  (TYPE     CHAR(17) NOT NULL,             
   CLASS    INTEGER  NOT NULL,             
   IFCID    INTEGER  NOT NULL,             
   COMMENTS CHAR(45) NOT NULL WITH DEFAULT)
   IN DSN8D91A.TRACETS#                     

The information needed to load these tables is also in this member. Just scroll down a little to see it:

//SYSREC DD DSN=DSN910.SDSNIVPD(DSNWMSGS),DISP=SHR      
...                                                    
  LOAD DATA INDDN(SYSREC) LOG(NO)                      
    INTO TABLE DSN8910.TRACE_DESCRIPTIONS WHEN (1) = '0'
    (IFCID       POSITION(1:4)   INTEGER EXTERNAL(4),   
     NAME        POSITION(6:25)  CHAR(20),             
     DESCRIPTION POSITION(27:86) CHAR(60),             
     SEQ         POSITION(87:92) INTEGER EXTERNAL(6) ) 
    INTO TABLE DSN8910.TRACE_TYPES WHEN (1) = '1'      
    (TYPE        POSITION(3:19)  CHAR(17),             
     CLASS       POSITION(27:28) INTEGER EXTERNAL(2),   
     IFCID       POSITION(35:39) INTEGER EXTERNAL(5),   
     COMMENTS    POSITION(42:86) CHAR(45) )            

Once you have the data loaded into TRACE_DESCRIPTIONS and TRACE_TYPES, you can run some handy reports in SPUFI or (my favorite) QMF to format the output. 

A description of the different types of queries you can run is also documented in the DSNWMSGS member:

* TO RETRIEVE ALL TRACE FIELD DESCRIPTIONS, ORDERED
   BY TRACE TYPE AND TRACE CLASS:                   
                                                 
  SELECT TYPE, CLASS, A.IFCID, DESCRIPTION, SEQ   
    FROM DSN8910.TRACE_TYPES A,                  
    DSN8910.TRACE_DESCRIPTIONS B                  
    WHERE A.IFCID = B.IFCID                      
    ORDER BY TYPE, CLASS, SEQ#                   

What follows is the type of information you'll see. To fit it on the screen I cut and pasted just the IFCID_DESCRIPTION field. The trace TYPE is ACCOUNTING and the CLASS is 8. The IFCIDs listed below is 351 and 352:

IFCID  DESCRIPTION                                              
-+---------+---------+---------+---------+---------+---------+---
  351   ________________IFCID 0351______________________________
  351   (RMID 27)                                                
  351   IFCID 0351 IS GENERATED AT THE BEGINNING OF THE TIME THAT
  351   IS SPENT WAITING FOR TCP/IP TO MATERIALIZE A LOB.       
  351                                                            
  351   YOU CAN ACTIVATE THIS TRACE BY STARTING ACCOUNTING      
  351   TRACE CLASS 3 OR 8.                                     
  351   ---------------------------------------------------------
  351   REQUEST TYPE, FOR GET, CLOSE, OR CLOSEALL:               
  351   X'01': MATERIALIZE A LOB VALUE INTO THE DATABASE.       
  351   X'0C': RECEIVE AND DISCARD A LOB VALUE FROM THE NETWORK.
  351   X'0D': RECEIVE AND DISCARD ALL LOB VALUES FOR THIS      
  351          REQUEST.                                          
  352   ________________IFCID 0352______________________________
  352   (RMID 27)                                                
  352   IFCID 0352 IS GENERATED AT THE END OF THE TIME THAT      
  352   IS SPENT WAITING FOR TCP/IP TO MATERIALIZE A LOB.       
  352                                                            
  352   YOU CAN ACTIVATE THIS TRACE BY STARTING ACCOUNTING      
  352   TRACE CLASS 3 OR 8.                                     
  352   ---------------------------------------------------------
  352   UNIQUE ID (LOB NUMBER FOR THIS REQUEST).                
  352   LENGTH OF THE LOB THAT IS RETURNED FROM THIS LOB         
  352    MATERIALIZATION EVENT.                                 
  352   RETURN CODE FROM THIS LOB MATERIALIZATION EVENT.         
  352   REASON CODE FROM THIS LOB MATERIALIZATION EVENT.         

Sometimes when you're working with a reporting tool like SAS or some vendor performance-reporting tool, you need to know the field names in the trace record. 

* TO RETRIEVE FIELD DESCRIPTIONS FOR PERFORMANCE   
   TRACE RECORDS, CLASSES 1 THROUGH 7:             
                                                
  SELECT DISTINCT NAME, DESCRIPTION, A.IFCID, SEQ
    FROM DSN8910.TRACE_DESCRIPTIONS A ,         
    DSN8910.TRACE_TYPES B                        
    WHERE A.IFCID = B.IFCID AND                  
    ((TYPE = 'PERFORMANCE' AND                  
      CLASS IN (1,2,3,4,5,6,7))                  
     OR NAME = 'COPY')                           
    ORDER BY SEQ#

Here's a sample of description you'll find for fields found in a PERFORMANCE trace for IFCID 1:

QLSTCNVS 
      NUMBER OF CONVERSATIONS THAT WERE INITIATED FROM THE      
      REQUESTER LOCATION.  THIS VALUE IS MAINTAINED AT THE    
      REQUESTER.  A CONVERSATION IS A SPECIFIC INSTANCE       
      OF USING AN LU 6.2 SESSION TO TRANSFER INFORMATION      
      BETWEEN A REQUESTER AND A SERVER.  A SESSION IS A       
      LOGICAL CONNECTION BETWEEN A REQUESTER AND A SERVER.    

QLSTCNVR         
     NUMBER OF CONVERSATIONS THAT WERE INITIATED FROM THE      
     REQUESTER TO THE SERVER LOCATION.  THIS VALUE IS UPDATED
     AT THE SERVER LOCATION.                                 

QLSTMSGS   
     NUMBER OF MESSAGES SENT TO THE REMOTE LOCATION. A MESSAGE,
     AS DEFINED BY VTAM, IS A GROUP OF CHARACTERS AND CONTROL
     BIT SEQUENCES TRANSFERRED AS AN ENTITY. THIS VALUE IS   
     MAINTAINED AT THE LOCATION WHERE THE MESSAGES ORIGINATED.

You can learn a lot about what's available with different trace classes. This information can also help if you need to run a performance trace to resolve a problem. Just make sure you collect only specific IFCIDs to minimize the performance impact on your system.

Remember, as you apply maintenance to DB2, you may want to refresh the TRACE_DESCRIPTIONS and TRACE_TYPES tables with the latest member in hlq.SDSNIVPD(DSNWMSGS). New IFCIDs are added and descriptions do change, particularly from one DB2 release to the next.