I am just done with an assignment where I had to tune the performance of DB2 procedures that were performing very bad.  While in one environment it was taking close to 10 minutes, in another environment it was taking close to 7 hours to complete (same set of data, better server configuration in latter scenario, etc.)

Below are few checks that I did (and that even you might probably do as well) to improve the performance of the DB2 procedure:

  • Checked for CPU Utilization, Disk I/O, Network I/O, Memory Utilization on the server.  It was NORMAL
  • Checked for Disk space on the server.  There was plenty of space.
  • Ran the statistics for all the tables involved.  No improvement in performance.
  • Compared the 'Access Paths' for the SQLs used in the procedure in both the environments. The server with better configuration was having a better access path (had lesser 'timerons').  However, the queries were performing very bad even though it had a better access path. 
  • Applied indexes on few columns based on what were being used by the queries present in the procedure.  The Access Paths improved further, and the query execution times of the individual queries too improved.  However, the DB2 procedure was still performing slow, same as before applying the index. 
After researching for a long time, found out that it is because of the STATIC SQLs that I had used in my procedure.  

When a SQL is executed, DB2 parses the SQL, compiles it and determines the access paths using various information including the statistics. For STATIC SQLs in a SQL stored procedure, this happens when the stored procedure is compiled (created) and the access path information is stored in the database. Then the stored procedure uses the fixed access paths every time it runs.

As for DYNAMIC SQLs, the access path is always calculated when the SQLs actually run.

Whether a SQL is STATIC or DYNAMIC depends on how it is written in the stored procedure.  Here is more information on the Differences between STATIC and DYNAMIC SQL.

I had few STATIC SQLs in my procedure, and few DYNAMIC ones, and the bad performance was due to these STATIC SQLs using old access paths (before indexes were created on few columns) even though the queries were tuned to perform better.

There are quite a number of ways to compile the procedures once again, so that it chooses the best access path as per the latest information that is available:
  • Drop the procedures, run statistics on the tables and re-create the procedures
  • Run the statistics on the tables, and re-bind all the packages of the database (using 'db2rbind').  Check out the link on DB2 Packages: Concepts, examples and common problems, and search for 'db2rbind'.
Hence it is very important that the queries that are going to be used in the procedure are tuned (by applying necessary performance aiding constructs like indexes, etc), use them in the procedure and then create the procedure. 

There might be many more reasons why a procedure might not be performing good, however thought of penning down my experience and the trick that solved the problem for me.