CODEWITHSUNDEEP
databasedb2db2-400ibm-data-studio
Anandan M
Anandan M

Reputation: 31

DB2 temp table session concurrent user issue

We are working with an application in .net with DB2 as a database. I am using the temp table in my stored procedure. Sometimes it throws an error "table is in use".

Declare Global Temporary Table TRNDETAILS (USERID INT ,
Name VARCHAR ( 25 )) WITH REPLACE;

As per the below document, temp tables are specific to the session. Then why is showing "table is in use". How can resolve it?

https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0003272.html

Upvotes: 0

Views: 1236

Answers (2)

Charles
Charles

Reputation: 23783

QTEMP is unique for every job/connection...

I assume your app only creates the temporary table in one place and that creating the temporary table is one of the first things it does.

I also suspect that you're using connection pooling within .NET.

Thus the connection isn't actually being closed, it's left open in the connection pool.

Somewhere in your app, you're not properly disposing of a result set and/or committing changes to the temp table. Thus leaving rows inside it locked when the connection is returned to the pool.

You probably should drop the temporary table before your app closes the connection and returns the connection to the pool.

That should prevent the error, but it'd be a good idea to track down the bug that's leaving the rows locked in the first place.

Upvotes: 0

mao
mao

Reputation: 12267

SQL0913N is either a lock-timeout or a deadlock.

This might not be in your session table. Unless your .net app is multithreading SQL on a single connection.

Check DSPRCDLCK, WRKOBJLCK related tools. You need to track down the SQL-statement(s) that are conflicting, and take action dependent on the cause. Sometimes this involves changing the isolation level in your application.

Examine the Db2 for i diagnostics to get more information , i.e. whether it is a lock-timeout or a deadlock, and which connections are involved, and which objects are involved.

Upvotes: 1

Related Questions