Creating New Index on BIG Table

Hi,

As always Prakash promised to back with the solutions with respect to practical scenario.
Here is another one for you.

Task: You are asked to create an Index in an existing table which is very huge.

Problem: You might face this issue. When you fired this SQL through batch spufi, it will run for minutes and at last it will abend with -904 return code saying 4K page is not available.
Investigation: You will find many messages in DSN?MSTR and DSN?DBM1 whcih says the temporary tablespace DSNDB07. unable to extend itself as it might reached it's limit of 2GB.

Solution: There are some fact you must know while creating index in a big table.
When you are creating the index, by default it will try to build the index instantly. CREATE INDEX statement uses RDS Sort to sort the keys. RDS Sort is very efficient for smaller tables. But when there is a huge table you must defer the creation of index by specifying DEFER YES in CREATE INDEX statement so that index for the table is registered in the DB2 catalog but the new index is in Rebuild pending status giving warning while creation of index.
REBUILD INDEX uses the the EXTERNAL SORT which outperforms the RDS Sort if the table size is significant. Even the performance of REBUILD INDEX is improved with parallel partition key extract and parallel index build.

By deferring the index creation and rebuilding the index, your index creation is over in few minutes and you will not get any extend failure message in MSTR started task.

Cheers..
Prakash C. Singh
IBM Certified DB2 DBA

Understanding DSN1CHKR Utility

Hello Everybody!!

Sometimes it is worth to verify the intigrity of DB2 directory and Catalog table spaces. DSN1CHKR utility is designed for this.
This utility checks the specified tablespace for

1) Broken Links
2) Broken Hash Chanins
3) Orphan records

This is a service aid and a diagonistic tool which executes outside the control of DB2.

Restriction:
You should not DSN1CHKR in a tablspace while it is active under DB2. Tablespace should be in STOPPED status before running this utility.

Privilege Required:
RACF auth required on the specified tablespace. No other privileges required to run this utility

Recommendation:
Copy the stopped tablespace into a similar VSAM dataset with DB2 naming convention and then run the utility on the copy.

Never run this utility on following tablespaces:

DSNDB06.SYSCOPY
DSNDB06.SYSDDF
DSNDB06.SYSGPAUT
DSNDB06.SYSPKAGE
DSNDB06.SYSSTATS
DSNDB06.SYSSTR
DSNDB06.SYSUSER
DSNDB01.SCT02
DSNDB01.SPT01
DSNDB01.SYSLGRNX
DSNDB01.SYSUTILX

Sample Job Step:

//**********************************************************************000
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************000
//**********************************************************************000
//DH000701 EXEC PGM=DSN1CHKR,PARM='FORMAT' 000
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 000
//SYSPRINT DD SYSOUT=(2,,CRTL) 000
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB01.DBD01.I0001.A001,DISP=SHR 000
//**********************************************************************000
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************001
//**********************************************************************001
//DH000702 EXEC PGM=DSN1CHKR,PARM='FORMAT' 001
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 001
//SYSPRINT DD SYSOUT=(2,,CRTL) 001
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSDBASE.I0001.A001,DISP=SHR 001
//**********************************************************************001
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************001
//**********************************************************************001
//DH000703 EXEC PGM=DSN1CHKR,PARM='FORMAT' 001
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 002
//SYSPRINT DD SYSOUT=(2,,CRTL) 002
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSDBAUT.I0001.A001,DISP=SHR 002
//**********************************************************************002
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************002
//**********************************************************************002
//DH000704 EXEC PGM=DSN1CHKR,PARM='FORMAT' 002
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 002
//SYSPRINT DD SYSOUT=(2,,CRTL) 002
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSGROUP.I0001.A001,DISP=SHR 002
//**********************************************************************003
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************003
//**********************************************************************003
//DH000705 EXEC PGM=DSN1CHKR,PARM='FORMAT' 003
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 003
//SYSPRINT DD SYSOUT=(2,,CRTL) 003
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSPLAN.I0001.A001,DISP=SHR 003
//**********************************************************************003
//**************** CHECKS CATALOG LINK & HASH CHAINS *****************003
//**********************************************************************003
//DH000706 EXEC PGM=DSN1CHKR,PARM='FORMAT' 004
//STEPLIB DD DSN=SYS1.DSN1.SDSNLOAD,DISP=SHR 004
//SYSPRINT DD SYSOUT=(2,,CRTL) 004
//SYSUT1 DD DSN=DSN1.DSNDBC.DSNDB06.SYSVIEWS.I0001.A001,DISP=SHR 004
//*

This is what IBM specified for the Sample JCL:
//YOUR JOBCARD
//*
//JOBCAT DD DSNAME=DSNCAT1.USER.CATALOG,DISP=SHR
//STEP1 EXEC PGM=IDCAMS
//********************************************************************
//* ALLOCATE A TEMPORARY DATA SET FOR SYSDBASE *
//********************************************************************
//SYSPRINT DD SYSOUT=A
//SYSUDUMP DD SYSOUT=A
//SYSIN DD *
DELETE -
(TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001) -
CATALOG(DSNCAT)DEFINE CLUSTER -
( NAME(TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001) -
NONINDEXED -
REUSE -
CONTROLINTERVALSIZE(4096) -
VOLUMES(XTRA02) -
RECORDS(783 783) -
RECORDSIZE(4089 4089) -
SHAREOPTIONS(3 3) ) -
DATA -
( NAME(TESTCAT.DSNDBD.TEMPDB.TMPDBASE.I0001.A001)) -
CATALOG(DSNCAT)
/*
//STEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//********************************************************************
//* STOP DSNDB06.SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=A
//SYSPRINT DD SYSOUT=A
//SYSTSIN DD *
DSN SYSTEM(V61A)
-STOP DB(DSNDB06) SPACENAM(SYSDBASE)END
/*
//STEP3 EXEC PGM=DSN1COPY,PARM=(CHECK)
//********************************************************************
//* CHECKSYSDBASE AND RUN DSN1COPY *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=A
//SYSUT1 DD DSN=DSNCAT.DSNDBC.DSNDB06.SYSDBASE.I0001.A001,DISP=SHR
//SYSUT2 DD DSN=TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001,DISP=SHR
/*
Example 2:
To Run DSN1CHKR on an actual table space. STEP1 stopsdatabase DSNDB06 with the STOP DATABASE command. STEP2 runsDSN1CHKR on the target table space; its output is identical to the output inExample 1. STEP3 restarts the database with the START DATABASE command.
//STEP4 EXEC PGM=IKJEFT01,DYNAMNBR=20
//********************************************************************
//* START DSNDB06.SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=A//SYSPRINT DD SYSOUT=A
//SYSTSIN DD *DSN SYSTEM(V61A)
-START DB(DSNDB06) SPACENAM(SYSDBASE)END
/*
//STEP5 EXEC PGM=DSN1CHKR,PARM='MAP=RID(00000201,06,00000B01,06)',
// COND=(4,LT)
//********************************************************************
//* CHECKLINK S OF SYSDBASE *
//********************************************************************
//STEPLIB DD DSN=prefix.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=A
//SYSUT1 DD DSN=TESTCAT.DSNDBC.TEMPDB.TMPDBASE.I0001.A001,DISP=SHR
/*

Thanks for reading this blog

Cheers
Prakash C. Singh
IBM Certified DB2 DBA

Calculating size of Database or Table in DB2 for z/OS

Hi All,

Sometimes people around you ask simple question:
What is the size of a particular database or a particular table?

How will you calculate???

Option 1: Get the total number of rows multiplied by row length.. What if you have variable columns...
Option 2: Get the total number of pages allocated for individual tables and multiply by 4K. What if there are large number of tables in a databases??
Option 3: List the underlying VSAM dataset of tablespace and indexspace. Get the Hi Used RBA and Low Used RBA and calculate the difference. Same Constrains as avove option.

Here you go ...
IBM provided a stand alone alone utility which will calculate the space for you within minutes.
The Utility name is STOSPACE. All you need to give the input is the Storage Group of the underlying dataset of spacific table or index. This will update the catalogue tables of DB2 regarding space information from where you can get by firing simple Select query.

Authorization required:
STOSPACE privilege SYSCTRL or SYSADM authority

This JCL step should be like:
//*---------------------------------------------------------------------
//* STOSPACE UTILITY TO UPDATE INFORMATION IN DSN1 CATALOG
//*---------------------------------------------------------------------
//DH011510 EXEC DSNUPROC,SYSTEM=DSN1,UID='TSG11510',UTPROC=''
//*
//SYSPRINT DD SYSOUT=*
//DSNUPROC.SYSIN DD *
STOSPACE STOGROUP (GDSN1)
//*

After Executing the utility, you may get following messages in SYSPRINT
Information you get in SYSPRINT
DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = DH011510
DSNU050I DSNUGUTC - STOSPACE STOGROUP(GDSN1)
DSNU640I - DSNUGSPC - DATA SET= 0 OF TABLESPACE= SPRODDET IN DATABASE= DISSUES2 HAS BEEN MIGRATED BY HSM
DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

After successful execution of STOSPACE utility you can query the catalogue tables as below and add the tablespace and index's space value to calculate the size of a database.
Calculating all index space in a database:
SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

Calculating all index space in a database:
SELECT DBNAME, SUM(SPACE)
FROM SYSIBM.SYSINDEXES
WHERE DBNAME = 'DTSG10'
GROUP BY DBNAME;

You can query SYSIBM.SYSSTOGROUP to findout total space in KB aquired by a particular storage group.

More about STOSPACE Utility:

STOSPACE output stored in columns as mentioned below:

The output from STOSPACE consists of updated values in the columns and tables in the following list. In each case, an amount of space is given in kilobytes (KB).
SPACE - in SYSIBM.SYSINDEXES shows the amount of space that is allocated to indexes. If the index is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLESPACE shows the amount of space that is allocated to table spaces. If the table space is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSINDEXPART shows the amount of space that is allocated to index partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSTABLEPART shows the amount of space that is allocated to table partitions. If the partition is not defined using STOGROUP, or if STOSPACE has not been executed, the value is zero.

SPACE - in SYSIBM.SYSSTOGROUP shows the amount of space that is allocated to storage groups. STATSTIME in SYSIBM.SYSSTOGROUP shows the timestamp for the time at which STOSPACE was last executed.

Note: If the value is too large to fit in the SPACE column, the SPACEF column is updated.

Concurrency and compatibility for STOSPACE:
STOSPACE does not set a utility restrictive state on the target object.
STOSPACE can run concurrently with any utility on the same target object. However, because STOSPACE updates the catalog, concurrent STOSPACE utility jobs or other concurrent applications that update the catalog might cause timeouts and deadlocks.

Terminating or restarting STOSPACE:
You can terminate a STOSPACE utility job with the TERM UTILITY command if you have submitted the job or have SYSOPR, SYSCTRL, or SYSADM authority.
You can restart a STOSPACE utility job, but it starts from the beginning again.

Cheers...
Prakash C. Singh
IBM Certified DB2 DBA