Hi,

I have experienced the following problems. Anybody can help?

1. DB2 timeout occurred when 2 stored procedures (accessing 2 different sets of application tables) running at the same time. It was found that they locked the same system table SYSIBM.SYSTABLES.

2. DB2 timeout occurred when stored procedures and DB2 load command job (accessing 2 different sets of application tables) running at the same time. It was found that they locked the same system table SYSIBM.SYSPROC.

Very hard to say without seeing the SP code.

But it seems not related to programs, right? In other word, can 2 stored procedures run at the same time. Also, can it run parallel with DB2 load command?

Normally, the same stored procedure can concurrently. The SP that runs the load command is fairly new, so I am not sure if there is some restriction on that one.

Sorry for misleading. I mean I have 2 jobs. One calling stored procedure to update table and the other calling DB2 load command (in shell script level) to load from file to DB. Since the DB2 load command is actually using some internal stored procedures, it also locked the same system table which caused the problem. That's what I observed in DB2 v7 environment.

I am sorry, I have no idea what you are talking about. First you need to specify the exact DB2 product and OS you are using.

Then you need to post the SP's in question.

I am using AIX OS v5.1 with DB2 UDB v7.2 installed. The problems are:

(1) I have two stored procedures. Both are for updating DB2 tables. When they are running at the same time, the following error message was prompted in one of the stored procedures.
SQL0911N The current transaction has been rolled back because of a de
adlock or timeout. Reason code "68". SQLSTATE=40001
After reviewing the log captured from DB2 snapshot, it was found that it waited for a system table.
04:25:09, 131, Lock-wait, 5, SYSIBM. SYSTABLES, IS,|, 81, SYSIBM. SYSTABLES, Row, Next Key Share (NS)
Based on the above information, I guess that there was problem when more than one stored procedures running at the same time.

(2) I have one stored procedures and one shell script using DB2 load command to load from file to DB2 table. Similar problem occurred except that the locked table is SYSIBM.SYSPROCEDURES.

Please help.

Any 2 transactions (whether or not in a SP) can deadlock or timeout if they are not written properly to maximize concurrency. This may include accessing (and locking) the multiple tables in the same order for each transaction, performing commits on a frequent basis, etc.

You will have probably to post your SP code and relevant DDL for me to help you further.

However, there are a couple of system parameters that you can try to maximize concurrency in general:

db2set DB2_RR_TO_RS=YES (this is only needed for DB2 V7)
db2 update db cfg for db-name using locklist 2048 (or higher if you have lots of available memory)
db2 update db cfg for db-name using maxlocks 40
db2stop
db2start

Please find the codes attached. Thanks a lot.

A quick glance at your code shows that it is creating and dropping new temp tables, and then doing DML against the tables (the same table name each time) so that could be why you have locktimeouts when you run multiple instances of the code or run it while other code is hitting the same tables.

If you want to have temporary tables that are unique and separate to each execution of the code (and that will probably eliminate concurrency problems), then try using global temporary tables (See SQL Reference Vol 2).

Thank you for your information.

However, the case is that STPR_SAM_CUS_DMCL_BR_LOAD.java and STPR_SAM_CUS_SGMT.java are running at the same time which they are using different sets of application tables. This should not be multiple instance of the code. Please comment.

Unfortuneately, it is a little harder for me to know exactly what is happening since I am not a java programmer (I was hoping for SQL stored procedures). BTW, SQL SP's run much faster than Java SP's.

You might want to make sure that all the necessary commits are being executed, including at the end of the SP. If possible, you might want to try the SP with autocommit on.

You say that SYSIBM.SYSTABLES is being locked, and this is not surprising since you are using DDL for creating tables and dropping tables in your stored procedure. During this same time in another SP, you are apparently issuing DML (which needs to read SYSIBM.SYSTABLES to resolve the query) and it is being locked out.

But I would not expect to see a problem unless your LOCKTIMEOUT parm is set very low. What is LOCKTIMEOUT parm set to in your db config? I would recommend at least 10 seconds (I normally use 30 seconds).

Did you set the other parms that I gave you in the post above?

My LOCKTIMEOUT is se to 40 seconds. It seems that I need to commit every steps regarding table creation/alternation to avoid table lock on SYSIBM.SYSTABLES. However, it would not be feasible as there may be problem during a rollback (incomplete rollback may be resulted).

Since there is no table creation/alternation in STPR_SAM_CUS_SGMT.java, I don't know why it locks the SYSIBM.SYSTABLES which caused the DB2 timeout. Any ideas?

My assumption is that it is the reverse, i.e., that the table create/drops lock the SYSTABLES table in exclusive mode, and the DML (select, insert, update, delete) in the other SP only needs to read SYSTABLES to come up with an access plan to execute the SQL (but must wait while the DDL statements are uncommitted).

With regard to the first comment about not commiting so that a rollback can undo previous statements, it is also possible to auto-commit the DDL, and then if you need to undo them, then execute drops of the objects you created. Obviously, this would probably require more complex program logic, but just remember what I tell developers:

"You can never be too rich, too thin, or commit too often"