DB2 z/OS Spring Cleaning

As another year passed, I started to schedule all the normal summer time activities – going to the dentist, that wonderful checkup to be poked by the doctor, and cleaning up or throwing out all the additional stuff I have accumulated over the last year.  Since vacation schedule is upon us and the environment won’t freeze for a while, the time is right to double check and clean up your DB2 z/OS systems.

Examination of DB2 Version 9 for z/OS

The following material guides you through an application and systems examination of DB2 Version 9 for z/OS.  The article describes several areas that can be quickly examined to eliminate unnecessary objects, improve overall application performance and double check your DB2 application systems.  The article highlights database statistics, application performance aspects and database backup information considerations.  After examining and cleaning up these areas, your DB2 system will be more efficient and perform better.

Table and Index Review – RUNSTATS

The first aspect that can be easily researched is the database table statistics that DB2 uses for determining access paths.  Check that statistics have been collected for every database table and index.  Also verify that the statistics are recently gathered from the table.  In the example below we use three months, running our statistics gathering quarterly.

When looking at the your shop’s DB2 tables, you need to exclude the various appropriate third party products, tools, work and temporary tables that are used in your environment. Eliminate these like we are doing in the example below with the DBNAME and TSNAME parameters.  Also remember to always use the WITH UR phrase to eliminate any locking on the DB2 Catalog.

Since these statistics are input to the optimizer’s complex access path calculations, running the simple queries for the DB2 Version 9 for z/OS platform displays the tables and indexes that have not had the RUNSTATS utility run against them.  It is a very simple, straightforward item to check, but everyone is usually amazed at the number of objects that do not have valid statistics.  This is even more important these days because of the amount of dynamic SQL from major Java applications and application software packages.

SELECT
DBNAME, TSNAME, PARTITION, CARDF,
FARINDREF, NEARINDREF, PERCACTIVE, STATSTIME
FROM SYSIBM.SYSTABLEPART
WHERE STATSTIME < ‘2008-05-01-01.01.01.000001’
AND DBNAME NOT LIKE ‘PTD%’
AND DBNAME NOT LIKE ‘RAA%’
AND DBNAME NOT LIKE ‘WRK%’
AND DBNAME NOT LIKE ‘%_C’
AND TSNAME   NOT LIKE ‘Z_%’
WITH UR;

SELECT DBNAME,TBNAME,CREATOR,NAME,STATSTIME
FROM   SYSIBM.SYSINDEXES
WHERE  STATSTIME < ‘2008-04-01-01.01.01.000001’
AND    DBNAME NOT LIKE ‘PTD%’
AND    DBNAME NOT LIKE ‘RAA%’
AND    DBNAME NOT LIKE ‘%C’
AND    DBNAME NOT LIKE ‘%T’
ORDER  BY 1,2,4
WITH UR;

SELECT
IXCREATOR, IXNAME, PARTITION, CARDF,
FAROFFPOSF, LEAFDIST, NEAROFFPOSF, STATSTIME
FROM SYSIBM.SYSINDEXPART
WHERE CARDF = -1
WITH UR;

The table and index lists from these queries can be surprising, especially considering the numerous third-party applications and utility packages installed and often overlooked.

Less Than Optimum Rows Hurt DB2 Performance

Next analyze the FARINDREF, NEARINDREF columns for the SYSIBM.SYSTABLEPART columns and the FAROFFPOSF and LEAFDIST for the SYSIBM.SYSINDEXPART columns.  These DB2 system catalog table columns tell how many database rows have been relocated away from their optimum position in the database table and index structures.  Any numbers in these FAROFFPOSF, NEAROFFPOSF and LEAFDIST columns should be analyzed completely for potential improvements through table reorganization.

Table Clustering

Next verify the clustering of the tables and indexes by executing the query below.  DB2 tries to preserve the order of your data and this query identifies where data clustering is no longer possible.  Any tables identified out of clustering sequence will have poor access and any new programs bound to these tables may not choose index access, resulting in elongated runtimes.  Any tables identified should be further analyzed for possible reorganization.  Unfortunately, the query only looks at data from when RUNSTATS utility last collected them.

SELECT TBNAME, NAME, CLUSTERRATIO,
FIRSTKEYCARDF, FULLKEYCARDF, NLEAF, NLEVELS, STATSTIME
FROM SYSIBM.SYSINDEXES
WHERE CLUSTERING = ‘Y’
AND CLUSTERED  = ‘N’
AND FIRSTKEYCARDF > 1
ORDER BY 3 DESC, 1, 2
WITH UR;

By using the DB2 RUNSTATS parameters below, with the SHRLEVEL CHANGE and UPDATE NONE, a statistics report of the current state of the environment can be produced unobtrusively. Analyzing the RUNSTATS report, try to find “CLUSTERED = N”, and look closely at the CLUSTERRATIOs.  The “CLUSTERED = N” condition notes a cluster ratio of less than 96%. A cluster ratio below 96% means data access performance is suffering because of non-sequenced data.  This condition is bad for database performance and should be addressed by doing a table or index reorganization as soon as possible.

RUNSTATS TABLESPACE database.tablespace UPDATE NONE
INDEX(ALL) REPORT YES SHRLEVEL CHANGE

Make sure to gather and update your DB2 Catalog with current statistics on the tables and indexes at your earliest opportunity.  Once any necessary table reorganizations are completed and good clustering statistics are in the DB2 Catalog, note the access path of any poor performing application and REBIND it with EXPLAIN (YES) and see if the access path has changed or improved. Any access path changes should be noted and the application execution time documented through performance reports. You may just become a hero fixing a performance problem.

Examine DB2 Application Plans and Packages

Looking at the application plans and packages is very important also.  One of the best things to clean up is DB2 plans or packages that are invalid or non-operational.  Supplying the appropriate package or plan name masks for your environment to the following SQL queries can quickly identify invalid or non-operational packages or plans.

SELECT LOCATION, COLLID, OWNER, CREATOR, VALID,
OPERATIVE, VALIDATE, ISOLATION, EXPLAIN
FROM SYSIBM.SYSPACKAGE
WHERE NAME LIKE ‘package name mask%’
ORDER BY LOCATION, COLLID, OWNER, CREATOR
WITH UR;

SELECT CREATOR, NAME, VALID, OPERATIVE, ACQUIRE,
RELEASE, CACHESIZE, DEFERPREP, DEGREE, EXPLAN, EXPREDICATE
FROM SYSIBM.SYSPLAN
WHERE NAME LIKE ‘plan-name mask%’
ORDER BY CREATOR, NAME;
WITH UR;

The first SQL statement identifies a number of DB2 package considerations.  The packages that are VALID = N or OPERATIVE = N should be examined closely before deleting them from the environment by FREEing the package.  Also the isolations level, package validation time and whether the package has been explained should always conform to your shop’s standards.  Confirmation of your shop’s bind standards, such as the locations, collection ids and especially the isolation level are important.  Probably the most important aspect available from this query is verifying the Explain process.  Validating the proper package SQL access path through the Explain process is mandatory for any shop that wants to ensure proper performance.

The next SQL statement identifies the DB2 Plan considerations. Like the packages, any plans found with VALID = N or OPERATIVE = N should be examined closely before deleting them from the environment by FREEing the plan.  Also the Explain parameter and Plan SQL access path information should be verified to ensure performance.

The query also displays the plan information about how, when and how much resources the plan gets when it executes through the ACQUIRE, and RELEASE columns.  The ACQUIRE and RELEASE parameters are very important for locking and DB2 thread reuse.  To maximize thread reuse these parameters should indicate that the plan is getting resources when it starts up (ACQUIRE = A) and releases these resources when it is de-allocated (RELEASE = D).  This is especially important for plans that are used in a DB2 Sysplex data-sharing environment and a CICS-DB2 environment.  Thread reuse can improve performance dramatically and should be closely monitored.

The CACHESIZE information is also very important because it indicates the cache size in bytes for storing plan execution security privileges.  This storage is allocated from the EDM Pool when the plan is started and the default is 1024 bytes.  If your shop has a wide variety of concurrent plans executed by a scheduler or single id, the plans are bound with a much larger cache size by mistake, or the majority of your plans are bound to public, this cache may be unnecessary.  Review the cache size settings for your particular plans and you may be able to save vital EDM Pool storage, making room for everything to run more smoothly.

Also through the information from this SQL SYSPLAN query, the plan’s DEFERPREP, DEGREE and EXPREDICATE plan settings are displayed.  These plan settings can have a dramatic effect on plan performance and should also be compared to your shop’s standards to insure they are being followed.  The DEFER parameter defines when the plan SQL is going to be prepared and bound to the environment for execution.  This parameter comes into play generally for dynamic SQL but can be a major time consumer for static SQL plans.  The DEGREE parameter indicates possible parallelism for the application. If the plan is bound improperly, parallelism may be attempted encountering extra overhead that could be avoided.  Finally the EXPREDICATE information indicates the setting for the CURRENTDATA bind parameter.  In most cases the setting should be CURRENTDATA (NO) or EXPREDICATE = B for no data currency required and data blocking allowed for ambiguous cursors.  This setting is especially important for DB2 Sysplex data-sharing environments to minimize locking and allow maximum application concurrency. Based on the application requirements CURRENTDATA should be set appropriately and should be following your shop’s standards.

Look next at your application’s plans and packages to check if any incremental binds or re-optimization of variables are happening during the running of your applications.  This can have a big performance impact and should be avoided especially within busy environment or data sharing environments because acquiring locks on the objects for the incremental bind or re-optimization of variables can be very time consuming and expensive.

SELECT DISTINCT NAME
FROM SYSIBM.SYSSTMT
WHERE STATUS = ‘F’ OR STATUS = ‘H’
WITH UR;

SELECT DISTINCT COLLID, NAME, VERSION
FROM   SYSIBM.SYSPACKSTMT
WHERE STATUS = ‘F’ OR STATUS = ‘H’
WITH UR;

SELECT * FROM SYSIBM.SYSPLAN
WHERE REOPTVAR = ‘Y’
ORDER BY NAME
WITH UR;

SELECT * FROM SYSIBM.SYSPACKAGE
WHERE REOPTVAR = ‘Y’
ORDER BY NAME
WITH UR;

Useful DB2 Indexes?

Another item to review within your DB2 system is whether your applications are using all the indexes created over the tables.  This can be determined quite easily through the following SQL DB2 Catalog query.  This SQL query should return zero rows if all the indexes are being used by the statically bound plans and packages.  The first part of the query retrieves a list of indexes that are defined in the database.  The query then eliminates the indexes from the list that were found to have package and plan dependencies.  If any indexes are listed, they need to be researched further to determine if they are being used for QMF or end-user dynamic SQL tools prior to removing them.  If you really think these indexes are not being used, I recommend stopping them first to see if anyone is affected and then deleting them after a week or two.  Cleaning up these unused indexes will save considerable space and performance time for update, insert and delete transactions.

SELECT NAME
FROM SYSIBM.SYSINDEXES
WHERE DBNAME LIKE ‘database name mask%’
AND NAME NOT IN (
SELECT DISTINCT BNAME
FROM SYSIBM.SYSPACKDEP
WHERE BTYPE = ‘I’)
AND NAME NOT IN (
SELECT DISTINCT BNAME
FROM SYSIBM.SYSPLANDEP
WHERE BTYPE = ‘I’)
WITH UR;

There are many more ways to give your DB2 system a check up, but these items are uncomplicated, quick and can easily lead you to any problem areas.  So check out your system and clean up the situations that can cause your pager to go off in the middle of the night.  We all need our rest and a good, clean and efficient DB2 system is a great way to start.