DB2 Performance Maintenance – Get All the Cache

By Dave Beulke, on January 25th, 2011

DB2 Clean up – Close Out the Past

As I said last week, within every DB2 LUW or DB2 z/OS systems there are things that always need to be cleaned up. This week we look into the DB2 Plan and Package settings. These settings can have a huge impact on the amount of memory used within your system and by each application user. Cleaning up your system can really help memory constrained systems, have a huge impact on the EDM Pool storage and reduce the operating size of your DB2 system.

While doing performance reviews for clients over that last 15 years, I have seen a huge number of performance issues and it is always surprising to see applications where their processes have the wrong bind parameters. These are very easily monitored and fixed through the DB2 Catalog SQL statements below.

For example the first setting that needs to be examined is the amount of AUTHCACHE that is associated with the CACHESIZE parameter for individual DB2 Plans. Since thread creation and authorization is very expensive performance wise, the DB2 system allows security caching of authority ids. This way if another user needs to do the same PLAN process and their security is cached they can quickly reuse the thread with no extra overhead. Eliminating this extra thread creation overhead can be a great performance boost. But if the Plan is already GRANTed to PUBLIC this cache is not necessary and is only wasting precious memory within the DB2 system. This is why the CACHESIZE parameter needs close attention for the appropriate PLAN setting. This setting and the following other settings for DB2 Plan and packages need to be reviewed.

First let’s figure out if there are plans bind parameters that need to be cleaned up. Look at the DB2 z/OS plans and packages that are within your DB2 system that are bound with different parameters.

–Query1

SELECT
VALID,
OPERATIVE,
ACQUIRE,
RELEASE,
ISOLATION,
VALIDATE,
EXPREDICATE,
CACHESIZE,
DEGREE,
REOPTVAR,
OPTHINT,
KEEPDYNAMIC,
IMMEDWRITE,
RELBOUND,
COUNT(*)
FROM SYSIBM.SYSPLAN
GROUP BY
VALID,
OPERATIVE,
ACQUIRE,
RELEASE,
ISOLATION,
VALIDATE,
EXPREDICATE,
CACHESIZE,
DEGREE,
REOPTVAR,
OPTHINT,
KEEPDYNAMIC,
IMMEDWRITE,
RELBOUND
ORDER BY COUNT(*) DESC
WITH UR;

–Query2

SELECT
VALIDATE,
ISOLATION,
RELEASE,
EXPLAIN,
DYNAMICRULES,
DEFERPREPARE,
KEEPDYNAMIC,
REOPTVAR,
OPTHINT,
IMMEDWRITE,
RELBOUND,
COUNT(*) AS COUNT
FROM SYSIBM.SYSPACKAGE
GROUP BY
VALIDATE,
ISOLATION,
RELEASE,
EXPLAIN,
DYNAMICRULES,
DEFERPREPARE,
KEEPDYNAMIC,
REOPTVAR,
OPTHINT,
IMMEDWRITE,
RELBOUND
ORDER BY COUNT(*) DESC
WITH UR;

–Query3

SELECT A.NAME, A.CACHESIZE, B.GRANTEE, A.BINDDATE, A.BOUNDBY
FROM SYSIBM.SYSPLAN A, SYSIBM.SYSPLANAUTH B
WHERE A.NAME = B.NAME
AND B.GRANTEE = ‘PUBLIC’
AND A.CACHESIZE <> 0
ORDER BY 1,2
WITH UR;

These queries, like last week’s, will tell you which DB2 plan and package modules are invalid and non-operational within your environment through the VALID, OPERATIVE parameters. This more extensive list or parameters is usually the start of further research to find out how the plans and packages with non-standard parameters were able to get into your DB2 system.

These queries show the usage of the ISOLATION, CURRENTDATA, REOPTVARS, VALIDATE and other parameters. Each of these parameters is especially important for performance.

VALID indicates whether the module is operational and valid within the DB2 system. While the ‘A’, ‘H’ values indicate an ALTER statement has affected the module, anything other than a ‘Y’ value should be investigated.

For the OPERATIVE parameter anything other than a ‘Y’ value should be investigated.

The ACQUIRE, RELEASE and ISOLATION parameters should match your standard practices.

The VALIDATE parameter indicates when DB2 will validate the DB2 module. If it is ‘R’ for validate at runtime, you probably want to change it as soon as possible since it is creating validation performance overhead while the application is running.

If the EXPREDICATE parameter indicates the CURRENTDATA option and should be monitored closely because of its implications for data integrity and extra locking overhead within the DB2 system.

The CACHESIZE is the amount of storage that is associated with the DB2 Plan authorization cache. The cache helps a plan reuse an existing thread and can be a good performance boost to prevent creation of another thread.

The DEGREE, REOPTVAR, OPTHINT parameters are great for performance. DEGREE allows the SQL to be executed in parallel. REOPTVAR makes sure the values within the SQL are used during re-optimization of the DB2 access path. And OPTHINT tells the DB2 system to use the special HINT information setup when determining its SQL access path. Only your special DB2 modules should be using special settings. Make sure your system doesn’t have any unusual special modules.

The KEEPDYNAMIC parameter should be verified against the type of module execution. If the SQL from the module should be kept around for reuse, make sure this set to ‘Y’. You would be surprised to find out how many times the KEEPDYNAMIC parameter is set wrong for dynamic SQL.

The IMMEDWRITE parameter is very important for data sharing modules and needs to be set correctly to avoid having to immediately write updated group buffer pool pages. Make sure to set this one correctly to avoid extra performance overhead.

The RELBOUND indicates what the DB2 release was when this module was last bound. Make sure to re-bind your modules with every release and verify that they are up-to-date with this parameter.