Hello!
AIX 5.1, DB2 8.1, 64 bit (details below)

We have serious performance problems when dropping procedures.
Each drop takes 30 seconds or more.

There is no difference depending on the amount of code in a procedure.

We have approximately 800 stored procedures and each time we redeploy the system about 200 procedures need to be dropped and recreated.

This means that it takes over an hour to redeploy the procedures alone, which is unacceptable.

We're running shell-scripts to redeploy procedures:

#!/bin/sh
db2 connect to ... user ... etc

for file in $procfiles ; do
db2 -td@ -vf $file >> $log
done
# end example script

each file contains:
drop procedure foo
@
create procedure foo ...
@

However...
Command line or GUI, same problem!

Creating a procedure after the drop takes between 1 and 5 seconds depending on the amount of code.

The problem occurs in our production environment only which is a 64-bit-installation. In the test environment (which is 32 bit), no problem.

Need i say that the production machine is "a lot faster", double processor, 2 GB memory, faster processors etc

Any ideas?

best regards Johan Linderoth


Version stuff:

$ uname -a
AIX hostname 1 5 0030E22C4C00

$ db2level
DB21085I Instance "db" uses "64" bits and DB2 code release "SQL08016"
with level identifier "02070106".
Informational tokens are "DB2 v8.1.1.56", "s040616", "U497635", and FixPak "6".
Product is installed at "/usr/opt/db2_08_01".

Do you have a lot of packages? I suspect db2 scans the packages to see if any are dependent on the procedure... for each one.

I'm not sure... I'll check that as soon as possible!
Thanks. /johan

There is a dependency matrix(table) keeping track of which object is dependent on which other object. When you drop a procedure, system catalog etc. are updated, undo info written to log etc. If system is busy, it could take some time. Can you split 200 drop procedures to 20 scripts and run them in parallel?