Wednesday, October 14, 2009

Compare Static SQL Access Paths

What I am going to talk about this time is one of the most critical factors (in my opinion) of application performance - the access paths that were selected for your SQL statements.

There are two types of SQL statements - dynamic and static. This post will be devoted for static SQL statements, that is, statements for which an access path is chosen at bind time.

As a part of project I did for a big bank, I was asked to review explain output for packages before they were moved from the test environment to the production environment. In that bank, every package was manually approved by a DBA before the program could go into production.

Since the test environment was not identical to the production environment in terms of computers resources and database capacity, and since I don't like to waste my time, I immediately asked them if they had any mechanism that checked that the access path chosen in production, is identical to the one in the test environment (otherwise, the time spent checking each package was useless).

Since they did not have such mechanism, I wrote one myself. The idea is pretty simple: you bind your packages with the explain(yes) parameter and then you just compare the output that was written to the plan_table of the two environments. I implemented this procedure using REXX.

As it turned out, they had hundreds of packages with different access paths (we simply copied the production plan_tabe to the test environment and compared the two using the same SQL from my script). Today, each time they move a program to the production environment, my script is executed in order to verify the access path has not changed.

My REXX code can be found here.
The code is very straightforward. The procedure receives the following parameters:
DB2SYS - DB2 system to which the REXX connects
DB2SRC - Source DB2 system
QSRC - PLAN_TABLE qualifier in source system
DB2TAR - Target DB2 system
QTAR - PLAN_TABLE qualifier in target system
PACKNAME - The package name
CIDSRC - Collection ID of source package.
CIDTAR - Collection ID of the target package.

The REXX code first connects to the source DB2 system and gets the version name and collection of the package that was bounded last. The source collection id that is passed as a parameter can use a like pattern. If no value is supplied, % is taken. If no value is provided for target collection id, the source collection id will be used.
Then, the REXX fetches all the data from the plan_table in the source DB2 system, fetches all the data from the target DB2 system, and compares the two.
Not all the plan_table columns are being compared. Just the ones I found relevant. If a difference is found then it is printed and the exit code will be 4. If no differences were found, the return code is 0. If a SQL error is encountered, the REXX will exit with code 8.

I hope you can find this REXX useful. If you have any comments or suggestions, please post them in the comments section.

Till next time…

No comments:

Post a Comment