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
Sunday, March 27, 2011
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
/*
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
Subscribe to:
Posts (Atom)
