Hi,

We have a application that runs on DB2 8.1 FP2 on AIX 5.2. Here the
application creates procedures and temporary tables i.e. run time they
are compiled and executed and also dropped.
But when such things happen frequently with same type of procedure, we get locking issues. I have taken the snapshot of the locks, application, dynamic SQL and database which suggest that the SYSIBM tables are having locks.
My botheration is that there are locks on SYSIBM tables. How can such locks be resolved?

We are using DB2_RR_to_RS=ON and
DB2_SQLROUTINE_PREPOPTS=ISOLATION CS.

Any help will be appreciable.

Cheers

Hi,

I would suggest if possible please fire frequent commits in your application.

Please also see whether increasing LockList and MaxLocks Parameter helps and whether indexes are properly created on the tables that your application accesses.

However we have no control over the modifications to SYSIBM Tables.

Hope all that helps.

Regards

Nitin.

Thank's Nitin,

I have already taken care of the indexes and commits.
I agrre with your suggestion of increasing the maxlocks and locklist but it is not a perfect solution.
Again i would like to stress on same point, procedures are created on a fly and executed and dropped.
First procedure is working fine and while the first one is executing the second one tries to compile and we see locks escalating on SYSIBM tables. I am not sure why DB2 is going for table level locks on SYSIBM tables when a row level lock is enough as a new procedure is being created.

One hint is that each procedure uses a different global temp table, can that be a problem. I am sure of one thing that global temp tables do not use catalog tables.

Would like something on isolation level. I guess isolation level can do the trick.

Cheers,

Hi,

Indeed what you said is 100% true and believe me DB2 is a LOCKING MONSTER. We are in the same soup as you are in and if you go by the threads in the Dbforums you would see that there has been a lot of discussion on the same topic.

If you are refering to Lock Contention and Lock Escalations then the only parameters that can help you is LOCKLIST and MAXLOCKS with LOCKTIMEOUT.

By default the isolation level is supposed to be CS however it behaves like RR that's the highest level.

One of my colleagues feels that you are obtaining 'P' Level locks which are package level and he suggests that you do a rebind of your stored procedures and packages, is that possible??

What we have done is changed the isolation level to UR and taken the hit however we do find that there are occasion of deadlocks. But now we have no other option.

We also had raised a service request with IBM and they also had no more to offer.

Proabably try and tweak around your application and that would help.

Hope that helps.

Nitin.