i never thought i would post a problem here... but a member of my DB2 user group had this problem that i couldn't figure out.... maybe someone else encountered this problem.... here goes:


OS: Red Hat Linux 8
DB: DB2 UDB ESE 8 (fixpak 7)

Here are some info on DB2:
Instance uses "32" bits and DB2 code release "SQL08020" with level identifier "03010106"
"DB2 v8.1.0.64", "s040812", "MI00086", and FixPak "7"

Product Name = "DB2 Enterprise Server Edition"
Product Identifier = "DB2ESE"
Version Information = "8.2"
Expiry Date = "Permanent"
Registered Connect User Policy = "Enabled"
Number Of Entitled Connect Users = "5"
Enforcement Policy = "Soft Stop"
Number of processors = "4"
Number of licensed processors = "128"
Database partitioning feature = "Not entitled"

Application:
a JAVA application running and executing multiple threads.

(... and here is the) PROBLEM:
Deadlock (lock-wait) with:
1. a thread executing CREATE TABLE, and
2. a thread executing:
call SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?)


i've check the monitors and all other UOW are waiting for the LOCK-WAIT between the two applications. i could specify a value in the LOCKTIMEOUT parameter, but that would cause the JAVA app to end abnormally and rollback everything.

my questions are (besides solving this problem ) :
1. what does SYSIBM.SQLCAMESSAGE do? (i know it's a stored procedure)
2. i suspect authority issues in executing SYSIBM.SQLCAMESSAGE. do one need special authority in executing this stored procedure?
3. how was that stored procedure executed anyway? it is not encoded the in application
4. where can i get the documentation on SYSIBM.SQLCAMESSAGE?
5. do you guys have a redbook (pdf) on "DB2 Application Programming Guide and Reference for Java"?

DB2 Universal JDBC driver uses a number of stored procedures with schema SYSIBM to retrieve catalog information, tracing and error message formatting ... (SQLCAMESSAGE seems to be error message formatting stored procedure ) ...

Though I have not seen any specific information, if you would like to restrict catalog access then you should also consider doing the same for these procedures ... Otherwise, granting to PUBLIC will be fine
HTH

Sathyaram

Appl programming guide for Java
ftp://ftp.software.ibm.com/ps/produc...r/db2aje80.pdf

i've got this redbook already... so they have changed the name

just my thoughts exactly... i assumed that the DB2 was trying so send out an error message... but why would it cause deadlock?

prior to the CREATE TABLE and SQLCAMESSAGE dead lock, there were CREATE TABLE statements... and those were successful table creations

i just check it... public already has execute access


i wish i could provide more details, but i am not familiar with JAVA... my analysis was based on the snapshots of the whole database

I'm not very sure what would have caused the deadlock, but I guess your snapshot or a event monitor (a deadlock event monitor is created by default) should tell you the locks held that may give you some fairly good idea ..

HTH

Sahyaram

From the snapshot of locks:

Application handle = 23
Application status = Lock-wait

ID of agent holding lock = 21
Lock object type = Table
Lock mode = Intention Share Lock (IS)
Lock mode held = Intention Exclusive Lock (IX)
Lock mode requested = Exclusive Lock (X)
Name of tablespace holding lock = SYSCATSPACE
Schema of table holding lock = SYSIBM
Name of table holding lock = SYSTABLES

Application handle = 21
Application status = UOW Waiting


from the snapshot of applications:

Application handle = 23
Application status = Lock-wait
Dynamic SQL statement text:
CREATE TABLE ...

ID of agent holding lock = 21
Lock object type = Table
Lock mode = Intention Share Lock (IS)
Lock mode held = Intention Exclusive Lock (IX)
Lock mode requested = Exclusive Lock (X)
Name of tablespace holding lock = SYSCATSPACE
Schema of table holding lock = SYSIBM
Name of table holding lock = SYSTABLES
Lock is a result of escalation = YES


Application handle = 21
Application status = UOW Waiting
Dynamic SQL statement text:
call SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?)


can you make anything out of it? like i said, previous CREATE TABLE statements were successful

i tried to suppress some info as this is running in the production server

I guess it may be something that has taken place in the UOW (not yet committed) in application 21 that is be causing the problem ...

What did the lock snapshot say ?

Cheers
Sathyaram

it is the SQLCAMESSAGE that is causing the locks.... the stored procedure is just hogging the system tables!!!

... now i wanted to learn more about the stored procedure to identify what is causing this

SQLCA is simply the "SQL Communications Area" which is used to talk to a task. In this context it is a red herring. The bottom line is: "there is a deadlock; period." The question is why.

What kind of transaction-behavior is the Java application requesting? Can you log the entire, actual command sequence it is presenting to the SQL server?