October 14, 2008

IBM Redpaper: Considerations on Small and Large Packages

Have you ever considered the performance impact (high CPU) of having a few short-running SQL statements out of a larger number of statements in a package? What if you have a few short-running SQL statements spread across a larger number of packages executed within a transaction?

A new IBM Redpaper, "DB2 for z/OS: Considerations on Small and Large Packages," outlines the impact of these two scenarios and compares the impact across DB2 V7, V8 and V9.

The Redpaper goes through some of the bind options--like ACQUIRE(ALLOCATE) vs. ACQUIRE(USE) and RELEASE(DEALLOCATE) vs. RELEASE(COMMIT)--and explains the impact these parameters have on CPU usage in different situations.

I know that generally I see common I/O routines used when the architects decide that the presentation layer must be separate from the business layer, which must be separate from the data access layer. I've supported these types of applications and they have their pros and cons, usually related to support and debug issues and, at times, performance.

The Redpaper goes through a list of recommendations for BIND parameters to help improve overall CPU usage. However, the No. 1 recommendation is to design your application to include only the SQL you need within one package.