Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41

    Question Unanswered: Deadlock with SQLCAMESSAGE

    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"?
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41
    Quote Originally Posted by sathyaram_s

    i've got this redbook already... so they have changed the name
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  5. #5
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41
    Quote Originally Posted by sathyaram_s
    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 ) ...
    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

    Quote Originally Posted by sathyaram_s
    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
    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
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41
    Quote Originally Posted by sathyaram_s
    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
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Last edited by sathyaram_s; 09-13-04 at 07:14.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41
    Quote Originally Posted by sathyaram_s
    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
    Attached Files Attached Files
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  10. #10
    Join Date
    Oct 2003
    Posts
    706
    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?
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •