Sunday, March 27, 2011

DB2's Got Talent Finals

Hi All,

Recently I have been taking part in the DB2's Got Talent competition on the DB2 Night Show.
On last Friday, the last round of finals took place and I'm proud to say that I am one of the 4 who made it all the way through.

If anyone wishes to see the replays, they are available at www.db2nightshow.com.
If you liked my presentations, and wish to support me (the only finalist who talked about DB2 for zOS), please take 1 minute to vote at

https://www.surveymonkey.com/s/DB2TalentFinals4

you can win a 25$ gift certificate at Amazon.com by voting.

Thanks

Saturday, March 19, 2011

Getting More From The REPORT Utility

Hi All,

It has been way too long since my last post. In the past weeks I have been taking part in the DB2 Got Talent contest on the DB2 Night Show (www.db2nightshow.com).
During the finals (which are still going on, so you are more than welcomed to vote for me) I talked about the REPORT utility and I wanted to add a blog post about that topic.

The REPORT utility might seem a bit less important than the other ones at first glance, but I will try to show you what you can do with it's output.
Basically the report utility provides a list of resources that are required to recover a page set (or a set of related page sets). This data is extracted from three sources:
1) The BSDS file
2) The SYSCOPY catalog table
3) The SYSLGRNX directory table

I find the third part to be the most interesting one since it is harder to get that information in any other way.

SYSLGRNX is a special directory table which contains for each page set, the ranges of log records during which this page set might have been updated. DB2 will start a range when the page set is updated. When the range is closed depends on several factors (for example, starting the tablespace in RO status will obviously close the range), but unless the tablespace state changes, there are two parameters which control it:
PCLOSEN - the number of checkpoints since the last update
PCLOSET - the number of minutes since the last update

So if any of the above thresholds is reach, the page set status will be set to a logical read only (logical means you can still update it, but the SYSLGRNX range was closed).

SYSLGRNX is used by DB2 to reduce the amount of log records that are processed when a page set is recovered.

The report utility will format that information from SYSLGRNX in a way that makes it very easy to load into a table. The data that is returned by the utility contains the following columns:
UCDATE, UCTIME, START_RBA, STOP_RBA, START_LRSN, STOP_LRSN, PARTITION, MEMBER_ID

By running the REPORT utility on all the tablespaces in our subsystem and loading the output into a table, we can use the table to:
1) Map RBA to time - this might not give us the level of accuracy we need, but it will certainly give us a general idea of which RBA was used at what time.
2) Get a list of objects that were changed in a specific time frame / RBA range - this information is extremely helpful when we need to recover a DB2 system. Lets assume for example that we have a DB2 system running SAP with 10k tables, that we want to take back 1 hour. It is very likely that most object were not updated during that hour. The REPORT output that we loaded into the table can help us identify which tablespace were changed, and only recover those.
3) Usage trend analysis - we could use the information to see what are the update patterns in our system. By understanding when each tablespace is being updated, we could construct different maintenance windows to different groups of tablesapces and minimize the contention between application workload and utility workload.
4) We could use the data to try and tune the values we provide to PCLOSET and PCLOSEN. If we see that most of the ranges of activity are PCLOSEN checkpoints long, we can understand that maybe it would be better to lower PCLOSEN. Setting these two parameters to optimal values will improve logging, shorten recovery time and improve data sharing processing.

Besides all that I have mentioned, the report utility also indicates which image copies are no longer usable (for example, if a LOAD REPLACE LOG NO was executed after the image copy was created) and we could use that information in order to delete those image copy and remove them from the SYSCOPY table with the MODIFY utility (which helps us keep the catalog and directory nice and clean).

The following code, is a simple REXX code which reads the output from the REPORT utility and generates a file that can later be loaded into a table. This script can work on an output file that contains information for more than one tablespace.


/*                           REXX                                   */                                                                          


/* read the input into a stem variable */                               
"EXECIO * DISKR INFILE (FINIS OPEN STEM INRECS. )"                      
                                                                        
NEW_TABLESPACE_MESSAGE = 'DSNU581I'                                     
END_TABLESPACE_MESSAGE = 'DSNU589I'                                     
START_SYSLGRNX_MESSAGE = 'DSNU583I'
row_COUNT              = 0
DBNAME  =''                                                             
TSNAME  =''                                                             
PART    =''                                                             
                                                                        
IF RC > 0 THEN                                                          
   Do                                                                   
      SAY "ERROR WHILE READING INPUT FILE, RC = "RC                     
   End                                                                  
else                                                                    
   do                                                                   
      say "open file was ok"                                            
   end                                                                  
                                                                        
                                                                        
/* loop through the rows from the input file */                         
say "file has "inrecs.0" records"                                       
                                                                        
do i = 1 to inrecs.0                                                    
                                                                        
   /* CHECK FOR NEW TABLESPACE OUTPUT */                                
   IF WORD(INRECS.I,1) = NEW_TABLESPACE_MESSAGE THEN                    
      DO                                                                
         dummy = word(inrecs.i,8)
         parse value dummy with dbname'.'tsname
         SAY "PROCESSING TABLESPACE "DBNAME"."TSNAME                    
      END


   IF WORD(INRECS.I,1) = START_SYSLGRNX_MESSAGE THEN
      DO
         /* now we are focused on the beginning of the lgrnx section */
         i = i + 2


         do while (inrecs.i ¬= '')
            parse var inrecs.i ucdate uctime start_rba stop_rba,
                start_lrsn stop_lrsn partition member_id dummy


OUTPUT_ROW = OVERLAY(DBNAME,'        ')||,
                         OVERLAY(TSNAME,'        ')||,
                         OVERLAY(UCDATE,'      ')||,                       
                         OVERLAY(UCTIME,'        ')||,                     
                         OVERLAY(START_RBA, '            ')     ||,        
                         OVERLAY(STOP_RBA,  '            ')     ||,        
                         OVERLAY(START_LRSN,'            ')     ||,        
                         OVERLAY(STOP_LRSN, '            ')     ||,        
                         OVERLAY(PARTITION,'    ') ||,                     
                         OVERLAY(MEMBER_ID,'    ')                         
                                                                                                                                                      
            ROW_COUNT = ROW_COUNT + 1                                      
            OUTRECS.ROW_COUNT = OUTPUT_ROW                                 


            i = i+1
         end                                                               


      END                                                               
END                                                                     
                                                                        
/* WRITE THE OUTPUT ROWS TO THE OUTPUT FILE */                          
OUTRECS.0 = TABLESPACE_COUNT                                            
                                                                        
  "EXECIO * DISKW OUTFILE (STEM OUTRECS. OPEN FINIS)"                   
                                                                        
say "Done..."                                                           
say "Thank you, come again"                                             
                                                                                                                                          EXIT                                                                  


The following job is an example of how to use the above script. We will run the REPORT utility on the desired tablespaces (I recommend to run it on all the tablespaces in the system. Note that you can not use one listdef statement because some restrictions apply to the catalog tablespaces), and then load the output to a table:


//XXXXXX JOB (XXXX,XXXX,XXXXXX),'XXXX',TIME=999,CLASS=X, 
//         MSGCLASS=R,MSGLEVEL=(1,1)
//AAAAAA EXEC DSNUPROC,
//   SYSTEM=DB2A,
//   UID=MOMI,
//   REGION=3M
//STEPLIB  DD DISP=SHR,DSN=SYS1.DB2.SDSNEXIT.DB2A
//         DD DISP=SHR,DSN=SYS1.DB2.SDSNLOAD.DB2A
//SYSPRINT DD DSN=BBBB.GENERAL.SYSPRINT.H.LIST,
// DISP=(NEW,CATLG),UNIT=SYSDA,
// SPACE=(TRK,(40,50))
//SYSUDUMP DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSDUMP  DD SYSOUT=*
//SYSIN    DD *


  LISTDEF REPLIST
     INCLUDE TABLESPACE A*.*


REPORT RECOVERY TABLESPACE LIST REPLIST INDEX NONE
/*
//*********************************************************
//RUNREXX  EXEC PGM=IRXJCL,PARM='PARSREPT'
//SYSEXEC  DD   DISP=SHR,DSN=BBBB.DB2UTIL.EXEC
//SYSTSPRT DD   SYSOUT=*
//INFILE   DD   DISP=SHR,DSN=BBBB.GENERAL.SYSPRINT.H.LIST
//OUTFILE  DD   DISP=(NEW,CATLG),SPACE=(TRK,(80,80)),
//   DCB=(LRECL=86,RECFM=FB,BLKSIZE=8600),STORCLAS=XSYS,
//   DSN=BBBB.GENERAL.REPORT.H.LIST,UNIT=3390
//
//*------------------------------------------------------------*//
//* LOAD THE TABLE
//*
//* CREATE TABLE MOMI.TBLGRNX
//* (DBNAME     CHAR(8) NOT NULL,
//*  TSNAME     CHAR(8) NOT NULL,
//*  UCDATE     CHAR(6) NOT NULL,
//*  UCTIME     CHAR(8) NOT NULL,
//*  START_RBA  CHAR(12) NOT NULL,
//*  STOP_RBA   CHAR(12) NOT NULL,
//*  START_LRSN CHAR(12) NOT NULL,
//*  STOP_LRSN  CHAR(12) NOT NULL,
//*  PARTITION  INTEGER  NOT NULL,
//*  MEMBER_ID  INTEGER  NOT NULL)
//*  IN DBMOMI.TS0003;
//*------------------------------------------------------------*//
//LOAD     EXEC DSNUPROC,
//   SYSTEM=DB2A,
//   UID=LOADDATA,
//   REGION=0M
//STEPLIB  DD DISP=SHR,DSN=SYS1.DB2.SDSNEXIT.DB2A
//         DD DISP=SHR,DSN=SYS1.DB2.SDSNLOAD.DB2A
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSREC   DD DISP=SHR,DSN=&&SYSREC
//SYSDUMP  DD SYSOUT=*
//SYSIN    DD *
TEMPLATE WORK1
 DSN 'BBBB.WORK1.&DB..&TS..T&TIME.'
 VOLCNT 5
 DISP (NEW,DELETE,DELETE)
 SPACE CYL MAXPRIME 100


TEMPLATE WORK2
 DSN 'BBBB.WORK02.&DB..&TS..T&TIME.'
 VOLCNT 5
 DISP (NEW,DELETE,DELETE)
 SPACE CYL MAXPRIME 100


 LOAD DATA LOG NO  REPLACE REUSE NOCOPYPEND ENFORCE NO
 WORKDDN(WORK1,WORK2) EBCDIC  CCSID(00424,00000,00000)
 INTO TABLE "MOMI"."TBLGRNX"
 (
   DBNAME       POSITION(0001:0008) CHAR(8) ,
   TSNAME       POSITION(0009:0016) CHAR(8) ,
   UCDATE       POSITION(0017:0022) CHAR(6) ,
   UCTIME       POSITION(0023:0030) CHAR(8) ,
   START_RBA    POSITION(0031:0042) CHAR(12),
   STOP_RBA     POSITION(0043:0054) CHAR(12),
   START_LRSN   POSITION(0055:0066) CHAR(12),
   STOP_LRSN    POSITION(0067:0078) CHAR(12),
   PARTITION    POSITION(0079:0082) INTEGER EXTERNAL,
   MEMBER_ID    POSITION(0083:0086) INTEGER EXTERNAL
 )


  STATISTICS TABLE(ALL) INDEX(ALL)
  KEYCARD FREQVAL NUMCOLS 5 COUNT 10
  REPORT NO
   UPDATE ALL HISTORY NONE
/*


That is it for now. As always questions and comments are more than welcomed.
Make sure you check out the db2 night show, and if you like what I do on the show, please vote for me.

Thanks
Momi