Of interest to performance people in DB2 Stored Procedures environments

(Originally posted 2005-05-04.)

I just saw the following APAR close. PQ99525 fixes a couple of problems for “nested” DB2 access i.e stored procedures, triggers and user-defined functions (UDFs).

For anyone thinking “this is not my shop” I think you should consider that these functions are appearing in many modern applications and your Applications people probably won’t think to tell you when they start using them.

To quote from the APAR description:

1. Class 1 accounting logic for UDFs, stored procedures and triggers could capture and record small fractions of class 2 in db2 time. This would result in QWACASC and QWACAJST having non-zero values when class 2 accounting was NOT active.
2. UDFs and stored procedure require in db2 time to connect and disconnect UDF or stored procedure tasks to db2. This time was not being accounting for in class 2 in db2 times (QWACSPTT, QWACSPEB, QWACUDTT, QWACUDEB). Class 3 suspension time is clocked during this connect and disconnect processing and thus class 3 time could be significantly greater than class 2 time.

It’s hard enough keeping track of the nested time without problems like this. We described how to do this in the “Stored Procedures: Through The Call And Beyond” Red Book.