Mainframe Tips, Tricks And Tutorials: December 2011

Thursday, December 29, 2011

How to solve if a DB2 table is in recovery pending status

When a LOAD utility is terminated while the LOAD job is in progress, DB2 puts table in recovery pending (RECP) status. There are several ways to solve this recovery pending status issue.

Solution 1

Run the below query for the table space is in question and it will give the QUIESCE points. Note down the last QUIESCE points (i.e, START_RBA column)

SELECT

DBNAME

,TSNAME

,HEX(START_RBA) AS START_RBA

,TIMESTAMP

FROM SYSIBM.SYSCOPY

WHERE DBNAME = 'MYDB' AND

TSNAME = ' TBLSPACE ' AND

ICTYPE = 'Q';

In the below job, put the latest QUIESCE point (START_RBA) after the TOLOGPOINT parameter. This job will recover table space to the latest full image copy and then it applies all the DB2 log records to the table space starting from the QUIESCE point X'C8E090980F3F'. The RECOVER puts the associated index spaces in REBUILD pending status. So, we should build the all indexes as part of recover job.

//RECOVR EXEC PGM=DSNUTILB,PARM='DB0T,RECOVR'

//SYSPRINT DD SYSOUT=*

//UTPRINT DD SYSOUT=*

//SYSIN DD *

LISTDEF COPONE INCLUDE TABLESPACE MYDB.TBLSPACE PARTLEVEL

RECOVER LIST COPONE

TOLOGPOINT X'C8E090980F3F'

PARALLEL 6

TAPEUNITS 6

REBUILD INDEX (ALL) TABLESPACE MYDB.TBLSPACE

SORTKEYS

SORTDEVT SYSDA

SORTNUM 12

//*

Solution 2

We can use the latest full/partial image copy datasets to recover the table space. If the data set is a full image copy, it is the only data set that is used in the recovery. If it is an incremental image copy, RECOVER also uses the previous full image copy and any intervening incremental image copies.

//RECOVR EXEC PGM=DSNUTILB,PARM='DB0T,RECOVR'

//SYSPRINT DD SYSOUT=*

//UTPRINT DD SYSOUT=*

//SYSIN DD *

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 1

TOCOPY MYDB.TBLSPACE.FC00001.D3362331

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 2

TOCOPY MYDB.TBLSPACE.FC00002.D3362331

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 3

TOCOPY MYDB.TBLSPACE.FC00003.D3362331

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 4

TOCOPY MYDB.TBLSPACE.FC00004.D3362331

REBUILD INDEX (ALL) TABLESPACE MYDB.TBLSPACE

SORTKEYS

SORTDEVT SYSDA

SORTNUM 12

//*

Solution 3

The below job specifies that RECOVER is to restore the object to the last image copy that was taken. If the last image copy is a full image copy, it is restored to the object. If the last image copy is an incremental image copy, the most recent full copy along with any incremental copies are restored to the object.

//RECOVR EXEC PGM=DSNUTILB,PARM='DB0T,RECOVR'

//SYSPRINT DD SYSOUT=*

//UTPRINT DD SYSOUT=*

//SYSIN DD *

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 1

TOLASTCOPY

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 2

TOLASTCOPY

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 3

TOLASTCOPY

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 4

TOLASTCOPY

REBUILD INDEX (ALL) TABLESPACE MYDB.TBLSPACE

SORTKEYS

SORTDEVT SYSDA

SORTNUM 12

//*

Solution 4

The below job specifies that the RECOVER utility is to restore the object to the last full image copy that was taken. Any incremental image copies that were taken after the full image copy are not restored to the object.

//RECOVR EXEC PGM=DSNUTILB,PARM='DB0T,RECOVR'

//SYSPRINT DD SYSOUT=*

//UTPRINT DD SYSOUT=*

//SYSIN DD *

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 1

TOLASTFULLCOPY

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 2

TOLASTFULLCOPY

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 3

TOLASTFULLCOPY

RECOVER TABLESPACE MYDB.TBLSPACE DSNUM 4

TOLASTFULLCOPY

REBUILD INDEX (ALL) TABLESPACE MYDB.TBLSPACE

SORTKEYS

SORTDEVT SYSDA

SORTNUM 12

//*

Find if program is doing table scan or index scan

When a program selects table scan for a DB2 table, it processes all the records from that table and this will ok if the table is small, but if the table is big it will cause serious performance issue. DB2 resorts to table scan, when the WHERE condition in SQL query does not use any index defined on the table.

If the WHERE condition in the query uses all the fields from an index or few fields from an index, it will perform index scan and index scan is more efficient from performance standpoint. When the WHERE condition uses few fields from an index, it must refer to top order fields of the index, so DB2 can select index scan. For example, if an index has 5 fields, then WHERE condition should refer to 1^st field, or 1^st &2^nd, or 1^st & 2^nd & 3^rd fields and so on.

The output of EXPLAIN is stored in the PLAN_TABLE. We can find if a program is doing table scan or index scan by analyzing the records from this table.

The below query retrieves all data related to table space scan for latest package of a program. ACCESSTYPE column from the PLAN_TABLE tells if program is doing table scan or index scan.

SELECT

A.QUERYNO

,SUBSTR(A.PROGNAME,1,8) AS PROGNAME

,SUBSTR(A.COLLID,1,8) AS COLLID

,SUBSTR(A.TNAME,1,20) AS TAB_NAME

,A.ACCESSTYPE

,A.TIMESTAMP

FROM MYPRODDB.PLAN_TABLE AS A

WHERE (A.COLLID = 'MYCOLLID') AND

(A.PROGNAME = ‘TESTPGM’) AND

(A.ACCESSTYPE = 'R') AND

A.BIND_TIME =

(SELECT MAX(B.BIND_TIME)

FROM MYPRODDB.PLAN_TABLE AS B

WHERE B.PROGNAME = A.PROGNAME AND

B.COLLID = A.COLLID);

The various possible values of ACCESSTYPE column is given below.

Column Name Description

ACCESSTYPE The method of accessing the table

I By an index

I1 By a one-fetch index scan

N By an index scan when the matching predicate contains the IN keyword

R By a table space scan

M By a multiple index scan (followed by MX, MI, or MU)

MX By an index scan on the index named in ACCESSNAME

MI By an intersection of multiple indexes

MU By a union of multiple indexes

blank Not applicable to the current row

The below query retrieves all data related to index space scan for latest package of a program. COLCOUNT column has number of columns present in an index. MATCHCOLS tells how many columns of the index are used in matching

SELECT

A.QUERYNO

,SUBSTR(A.PROGNAME,1,8) AS PROGNAME

,SUBSTR(A.COLLID,1,8) AS COLLID

,SUBSTR(A.TNAME,1,20) AS TAB_NAME

,A.ACCESSTYPE

,SUBSTR(A.ACCESSNAME,1,15) AS INDEX_NAME

,X.COLCOUNT

,A.MATCHCOLS

FROM MYPRODDB.PLAN_TABLE AS A,

SYSIBM.SYSINDEXES AS X

WHERE (A.COLLID = 'MYCOLLID') AND

(A.PROGNAME = ‘TESTPGM’) AND

((X.TBCREATOR = 'MYPRODDB' AND

X.TBNAME = A.TNAME AND

X.CREATOR = 'MYPRODDB' AND

X.NAME = A.ACCESSNAME AND

A.ACCESSTYPE IN('I','I1','N','M','MX','MI','MU'))) AND

A.BIND_TIME =

(SELECT MAX(B.BIND_TIME)

FROM MYPRODDB.PLAN_TABLE AS B

WHERE B.PROGNAME = A.PROGNAME AND

B.COLLID = A.COLLID)

WITH UR;

Wednesday, December 28, 2011

Find the programs impacted by copybook change

Searching for the impacted programs by a copybook change is pretty common scenario in development. Endevor has a cool feature called ACMQ (Automated Configuration Manager query) to get this done.

Type “ACM” from the Endevor main menu. This command may be different in your installation. Now fill in copybook and other fields as shown below and press ENTER key.

--------------------------------- ACM QUERY ---------------------------------

OPTION ===> _________________________________________________________

BLANK - Perform Element query C - Perform Comment query

M - Perform Member query O - Perform Object query

ELEMENT/MEMBER ===> MYCPYBOK

ELEMENT Query Information: Query Options:

ENVIRONMENT ===> * Where-used/Components used ===> WHE (WHE/COM)

SYSTEM ===> * Foreground/Batch Mode =========> F (F/B)

SUBSYSTEM ===> * Create GENERATE SCL =========> N (Y/N)

TYPE ===> * Exclude circular references ===> N (Y/N)

STAGE NBR ===> * (1/2/*) Exclude indirect references ===> N (Y/N)

Exclude related references ===> N (Y/N)

MEMBER Query Information:

DSNAME ===> *

COMMENT/OBJECT Query Information:

________________________________________________________________________

You will get the below screen showing all the impacted programs by the copybook.

Menu Utilities Compilers Help

sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss

BROWSE SYS11362.T172928.RA000.TSUXXXX.ACMQ.H06 Line 00000000 Col 001 080

Command ===> Scroll ===> CSR

********************************* Top of Data **********************************

17:29:28 ACMQ203I LIST USING COMPONENTS FOR

17:29:28 ACMQ204I ELEMENT MYCPYBOK ENVIRONMENT *

17:29:28 ACMQ205I SYSTEM * SUBSYSTEM *

17:29:28 ACMQ206I TYPE * STAGE NUMBER *

17:29:28 ACMQ408I OPTIONS

LVL ELEMENT TYPE ENVIRON SYSTEM SUBSYS STG

1 MYCPYBOK COPY TESTA1 FNAC S9000 1

2 TESTPGM1 BATCOB PROD FNAC S9000 2

2 TESTPGM2 BATCOB PROD FNAC S9000 2

2 TESTPGM3 BATCOB PROD FNAC S9000 2

2 TESTPGM4 BATCOB PROD FNAC S9000 2

2 TESTPGM5 BATCOB PROD FNAC S9000 2

If you want to run this in batch, then set “Foreground/Batch Mode” option to “B”

--------------------------------- ACM QUERY ---------------------------------

OPTION ===> _________________________________________________________

BLANK - Perform Element query C - Perform Comment query

M - Perform Member query O - Perform Object query

ELEMENT/MEMBER ===> MYCPYBOK

ELEMENT Query Information: Query Options:

ENVIRONMENT ===> * Where-used/Components used ===> WHE (WHE/COM)

SYSTEM ===> * Foreground/Batch Mode =========> B (F/B)

SUBSYSTEM ===> * Create GENERATE SCL =========> N (Y/N)

TYPE ===> * Exclude circular references ===> N (Y/N)

STAGE NBR ===> * (1/2/*) Exclude indirect references ===> N (Y/N)

Exclude related references ===> N (Y/N)

MEMBER Query Information:

DSNAME ===> *

COMMENT/OBJECT Query Information:

________________________________________________________________________

If you want to generate all the impacted programs, you can easily generate SCL for them. In the below screen, set “Create GENERATE SCL” to “Y” and press ENTER key

--------------------------------- ACM QUERY ---------------------------------

OPTION ===> _________________________________________________________

BLANK - Perform Element query C - Perform Comment query

M - Perform Member query O - Perform Object query

ELEMENT/MEMBER ===> MYCPYBOK

ELEMENT Query Information: Query Options:

ENVIRONMENT ===> * Where-used/Components used ===> WHE (WHE/COM)

SYSTEM ===> * Foreground/Batch Mode =========> F (F/B)

SUBSYSTEM ===> * Create GENERATE SCL =========> Y (Y/N)

TYPE ===> * Exclude circular references ===> N (Y/N)

STAGE NBR ===> * (1/2/*) Exclude indirect references ===> N (Y/N)

Exclude related references ===> N (Y/N)

MEMBER Query Information:

DSNAME ===> *

COMMENT/OBJECT Query Information:

________________________________________________________________________

In the below screen supply your personal PDS to store the SCL statements.

--------------------------- ACM Create GENERATE SCL ---------------------------

COMMAND ===> _________________________________________________________

Action Options:

CCID ===> ____________ COMMENT ===> ________________________________________

COPYBACK ===> N (Y/N)

NOSOURCE ===> N (Y/N)

To GENERATE W/COPYBACK or NOSOURCE specify ENV/STG and optionally SYS/SBS:

ENVIRONMENT ===> ________

STAGE NUMBER ===> _

SYSTEM ===> ________ SUBSYSTEM ===> ________

REQUEST DATA SET:

PROJECT ===> ________ APPEND ===> N (Y/N - F/G only)

GROUP ===> ________ STOPRC ===> 12 (4/99)

TYPE ===> ________

MEMBER ===> ________

OTHER PARTITIONED OR SEQUENTIAL DATA SET:

DSNAME ===> 'TSUXXXX.TESR.SCL(SCL)'___________________