DB2 Performance Maintenance - Trust But Verify

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 it’s recommended that you look into your various program’s plan and package bind parameters and characteristics.

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 following DB2 Catalog SQL statements.

First let’s figure out if there are plans or packages that need to be cleaned up. Look at the DB2 z/OS plans and packages that are within your DB2 system that are not operational.

SELECT NAME, CREATOR, BINDDATE, VALID, OPERATIVE

FROM SYSIBM.SYSPLAN

WHERE OPERATIVE = ’N’ OR VALID = ’N’

WITH UR;


SELECT COLLID, NAME, VERSION, BINDTIME, VALID

FROM SYSIBM.SYSPACKAGE

WHERE OPERATIVE = ’N’ OR VALID = ’N’

WITH UR;


For DB2 LUW:

SELECT NAME, CREATOR, LAST_BIND_TIME, VALID

FROM SYSIBM.SYSPLAN

WHERE VALID = ‘N’

WITH UR;


These queries will tell you which DB2 plan and package modules are invalid and non-operational within your environment. This list is usually the start of further research to find out if these are really a true situation. Sometimes a large list can be produced by database changes that happen in your environment and plans and packages don’t get rebound right away so be careful. Usually a large list indicates a number of plans and packages that potentially can be cleaned up through a DB2 FREE process.

The queries above only look at the OPERATIVE and VALID values within the respective SYSPLAN and SYSPACKAGE tables and there are many other columns that can indicate bad or unwanted situations. Some of the other parameters to examine within your environment are the CURRENT DATA setting, the Isolation Level setting, the amount of storage allocated for authority cache and others that we will talk about in the coming weeks that are vital for your system’s overall performance.

DB2 Performance Maintenance

Modify the queries above to examine all the SYSPLAN and SYSPACKAGE values to clean up and maximize performance. If you don’t have the correct plan and package settings, your applications can deadlock, not protect transaction integrity or run out of memory, so verify your environment settings as soon as possible.



IBM zEnterpirse and DB2 Roadshow

Be on the alert for the 2011 zSummit Meetings coming to a city near you. These sessions are great for discovering the latest technology advances with DB2, CICS, WebSphere and the entire zEnterprise platform.

So far these are the cities announced and check out the website for further cities to be scheduled.

http://www.ibm.com/software/os/systemz/summit/index.html

January 27, 2011 Toronto, ON

February 8, 2011 Dallas, TX

February 10, 2011 San Francisco, CA

February 15, 2011 Washington, DC

February 17, 2011 Atlanta, GA

March 8, 2011 New York City

March 15, 2011 Costa Mesa, CA

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>