Bharath's Tech Blog

DB2 MSTR log processing

DB2 MSTR logs have all kinds of DB2 Error messages, which can provide more insight into the Application performance & activity.

We use Data-Sharing setup and have 13 MSTR logs in DEV/QA/Prod regions combined. There is no way of getting customized details from the MSTR logs, except for going through the complete logs.

I developed a new Process that’ll access the MSTR logs, parse the Data and provide the details needed for Application monitoring.

The Process includes 2 Steps.

        Step 1: This Step will be executed on Daily basis.

1.   Connect to SDSF

I coded a REXX routine that connects to SDSF and gets the DB2 MSTR logs.

ISFIN_PARA:                                                     

 "ALLOC F(ISFIN) UNIT(VIO) NEW REU",                           

  "CYLINDERS SPACE(10,10) RECFM(F,B) LRECL(80) DSORG(PS)"      

 "ALLOC FI(ISFOUT) NEW DELETE REU ",                           

  "CYLINDERS SPACE(10,10) LRECL(133) RECFM(F,B,A) DSORG(PS)"   

 "ALLOC F(OUTP) UNIT(VIO) NEW REU",                            

  "CYLINDERS SPACE(100,100) LRECL(133) RECFM(F,B,A) DSORG(PS)" 

 QUEUE "ST"                     /* OPEN SDSF STATUS QUEUE     */

 QUEUE "OWNER *"                /* OPEN SDSF STATUS QUEUE     */

 QUEUE "PRE "A""                /* OPEN SDSF STATUS QUEUE     */

 QUEUE "FIND "A""               /* LOCATE YOUR JOB            */

 QUEUE "++S"                    /* BROWSE JESYSMSG DATASET    */

 QUEUE "PRINT FILE OUTP"        /* PRINT JESYSMSG TO DATASET  */

 QUEUE "PRINT 1 999999"         /* PRINT ALL RECS OF JESYSMSG */

 QUEUE "PRINT CLOSE"            /* CLOSE PRINT FILE           */

 QUEUE "END"                    /* END SDSF SESSION           */

 QUEUE "EXIT"                   /* EXIT SDSF                  */

 "EXECIO" QUEUED()" DISKW ISFIN (FINIS"                        

RETURN;   

                                                      

2.  Next REXX routine I coded will parse the output captured by above REXX routine to find out details of below mentioned DSN messages.

DSNL030I

DSNI031I

DSNT375I

DSNT376I

DSNT501I

DSNB260I

DSNJ139I

3.    Finally, the details parsed by above REXX routine will be inserted into a DB2 Table by another REXX routine I coded.

I created Views for each DSN message. So, the corresponding Details will be inserted into the respective Views.

   Step 2: This Step will be executed on Weekly basis. 

                I setup a JCL that executes the below SQL query and emails the details to my Team.

          SELECT

              STC_REGION

             ,DSN_MSG

             ,COUNT(*) AS OCCURENCES

          FROM DBMSTRLG.MSTRLOG

          WHERE WEEK(ROW_ENTRY_TS) = WEEK(CURRENT TIMESTAMP) - 1

          GROUP BY

              STC_REGION

             ,DSN_MSG

          WITH UR;

This SQL query will gives the no.of occurrences of each DSN message during last week.

For more details about the DSN messages, we can query the base DB2 Table.