Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    62

    Unanswered: Database Lock (long)

    Dear listers.

    My DB2 is version 9.5 "DB2 Enterprise Server Edition", Fix Pack 5, running on a 4 processor 64bit Intel Machine, Windows Server 2008 (SP2).

    I had a problem in this database which stayed out of service for many hours (now it's OK).
    I suspect a storage had gone offline and caused the unavailability.

    I send you below a long list of log messages so you help me diagnose what the problem could have been:

    thanks in advance,


    2012-03-04-15.56.37.301000-180 I75590F538 LEVEL: Warning
    PID : 13688 TID : 23672 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-63486 APPID: *LOCAL.DB2.120304185536
    AUTHID : ADMINISTRATOR
    EDUID : 23672 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:60
    MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
    "LockTimeOut - tran rollback Reason code 68"

    2012-03-04-16.02.10.245000-180 I76130F538 LEVEL: Warning
    PID : 13688 TID : 23672 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-63486 APPID: *LOCAL.DB2.120304185536
    AUTHID : ADMINISTRATOR
    EDUID : 23672 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:60
    MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
    "LockTimeOut - tran rollback Reason code 68"

    2012-03-04-16.03.10.246000-180 I76670F538 LEVEL: Warning
    PID : 13688 TID : 23672 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-63486 APPID: *LOCAL.DB2.120304185536
    AUTHID : ADMINISTRATOR
    EDUID : 23672 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:60
    MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
    "LockTimeOut - tran rollback Reason code 68"

    2012-03-04-16.03.26.946000-180 I77210F538 LEVEL: Warning
    PID : 13688 TID : 16472 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-63527 APPID: *LOCAL.DB2.120304190226
    AUTHID : ADMINISTRATOR
    EDUID : 16472 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:60
    MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
    "LockTimeOut - tran rollback Reason code 68"

    2012-03-04-16.09.26.865000-180 I77750F536 LEVEL: Warning
    PID : 13688 TID : 11576 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-63555 APPID: AC121E68.I806.10D344190823
    AUTHID : TIBCO
    EDUID : 11576 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:60
    MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
    "LockTimeOut - tran rollback Reason code 68"

    2012-03-04-16.19.11.582000-180 I78288F533 LEVEL: Warning
    PID : 13688 TID : 16292 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-63600 APPID: *LOCAL.DB2.120304191811
    AUTHID : DBA
    EDUID : 16292 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, catcache support, sqlrlc_systables_fetch_from_disk, probe:60
    MESSAGE : ZRC=0x80100044=-2146435004=SQLP_LTIMEOUT
    "LockTimeOut - tran rollback Reason code 68"

    2012-03-04-16.21.36.887000-180 I78823F412 LEVEL: Event
    PID : 13688 TID : 18352 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 18352
    FUNCTION: DB2 UDB, base sys utilities, DB2StopMain, probe:104
    DATA #1 : String, 26 bytes
    Force phase is in progress
    DATA #2 : Hexdump, 4 bytes
    0x000000002DD4FEFC : 0000 0000 ....

    ...

    2012-03-04-16.21.36.930000-180 I79728F452 LEVEL: Error
    PID : 13688 TID : 3628 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 3628 EDUNAME: db2tcpcm 0
    FUNCTION: DB2 UDB, common communication, sqlcctcpconnmgr_child, probe:126
    MESSAGE : Return code from sqleIsStopPending =
    DATA #1 : Hexdump, 4 bytes
    0x000000000971FBF0 : 6CE8 FFFF l...

    2012-03-04-16.21.36.935000-180 I80182F452 LEVEL: Error
    PID : 13688 TID : 9408 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 9408 EDUNAME: db2tcpcm 0
    FUNCTION: DB2 UDB, common communication, sqlcctcpconnmgr_child, probe:126
    MESSAGE : Return code from sqleIsStopPending =
    DATA #1 : Hexdump, 4 bytes
    0x0000000009F1FBF0 : 6CE8 FFFF l...

    2012-03-04-16.21.37.398000-180 I80636F512 LEVEL: Severe
    PID : 13688 TID : 23684 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-54666 APPID: *LOCAL.DB2.120304041717
    AUTHID : DBA
    EDUID : 23684 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_index_obj, probe:750
    DATA #2 : Hexdump, 4 bytes
    0x0000000030742FD0 : 0300 1080 ....

    2012-03-04-16.21.37.400000-180 E81150F712 LEVEL: Warning
    PID : 13688 TID : 23684 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-54666 APPID: *LOCAL.DB2.120304041717
    AUTHID : DBA
    EDUID : 23684 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrreorg_indexes, probe:1000
    MESSAGE : ADM9504W Index reorganization on table "SYSIBM .SYSTABLES" (ID "5")
    and table space "SYSCATSPACE" (ID "0") failed on this node with
    SQLCODE "-952" reason code "". To resolve this problem, re-submit
    the REORG INDEXES command on the failing node(s).

    2012-03-04-16.21.37.415000-180 E81864F588 LEVEL: Error
    PID : 13688 TID : 23684 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-54666 APPID: *LOCAL.DB2.120304041717
    AUTHID : DBA
    EDUID : 23684 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, DRDA Application Server, sqljsTermAgentReply, probe:10
    MESSAGE : DIA5000C A DRDA AS token "AGENT TERMINATING" was detected. The
    diagnostic data returned is (SRVDGN): "SQLERRPQLRRRCP
    SQLCODE:-1224".

    2012-03-04-16.21.37.422000-180 E82454F845 LEVEL: Error
    PID : 13688 TID : 23684 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : COSPJ
    APPHDL : 0-54666 APPID: *LOCAL.DB2.120304041717
    AUTHID : DBA
    EDUID : 23684 EDUNAME: db2agent (COSPJ) 0
    FUNCTION: DB2 UDB, DRDA Application Server, sqljsTermAgentReply, probe:11
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQLRRRCP
    sqlerrd : (1) 0x80100003 (2) 0x00000003 (3) 0x00000000
    (4) 0x00000000 (5) 0xFFFFFC7C (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    2012-03-04-16.21.37.566000-180 I83301F500 LEVEL: Error
    PID : 16360 TID : 24056 PROC : db2dasstm.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 24056
    FUNCTION: DB2 Tools, DB2 administration server, MDUtility::connectMD, probe:10
    DATA #1 : signed integer, 4 bytes
    1
    DATA #2 : signed integer, 4 bytes
    -6036
    DATA #3 : signed integer, 4 bytes
    0
    DATA #4 : String, 7 bytes
    TOOLSDB
    DATA #5 : String, 8 bytes
    SYSTOOLS
    DATA #6 : String, 4 bytes
    NULL

    2012-03-04-16.21.37.567000-180 I83803F499 LEVEL: Error
    PID : 16360 TID : 24056 PROC : db2dasstm.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 24056
    FUNCTION: DB2 Tools, DB2 administration server, MDUtility::connectMD, probe:10
    DATA #1 : signed integer, 4 bytes
    -300
    DATA #2 : signed integer, 4 bytes
    0
    DATA #3 : signed integer, 4 bytes
    0
    DATA #4 : String, 7 bytes
    TOOLSDB
    DATA #5 : String, 8 bytes
    SYSTOOLS
    DATA #6 : String, 4 bytes
    NULL

    2012-03-04-16.21.37.567000-180 I84304F608 LEVEL: Error
    PID : 16360 TID : 24056 PROC : db2dasstm.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 24056
    FUNCTION: DB2 Tools, DB2 administration server, MDUtility::connectMD, probe:50
    DATA #1 : String, 13 bytes
    MD RC = -300
    DATA #2 : String, 12 bytes
    SQLCODE = 0
    DATA #3 : String, 16 bytes
    SQLSTATE = -300
    DATA #4 : String, 17 bytes
    DBName = TOOLSDB
    DATA #5 : String, 18 bytes
    Schema = SYSTOOLS
    DATA #6 : String, 10 bytes
    UserID =
    DATA #7 : String, 11 bytes
    Count = 24
    DATA #8 : String, 11 bytes
    Limit = 25

    2012-03-04-16.21.37.618000-180 I84914F452 LEVEL: Error
    PID : 13688 TID : 17660 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000
    EDUID : 17660 EDUNAME: db2tcpcm 0
    FUNCTION: DB2 UDB, common communication, sqlcctcpconnmgr_child, probe:126
    MESSAGE : Return code from sqleIsStopPending =
    DATA #1 : Hexdump, 4 bytes
    0x0000000009B1FBF0 : 6CE8 FFFF l...

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Looks like you have some LockTimeOuts (SQL0911N reason code 68) and also a SQL0952N. I would look these up in the DB2 Messges Manual Vol 2 and read what it says about them.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2009
    Posts
    62
    Thanks Marcus, will do and get back here.

  4. #4
    Join Date
    Dec 2009
    Posts
    62
    I examined the applications running over my DB ("list application show detail" and "db2pd -db <dbname> -transaction") and discovered that there was one of them, among about 30, that was keeping about 200 locks all the time.

    As a result, not only the REORG tasks were not running properly, and returning -911 error, but also my client's queries were very slow.

    I asked the developer of the application that feeds the DB to shorter the period of commit commands. This alone resolved the problem with the clients. Now the application keeps only 5 locks.

    I'll schedule a REORG task for tonight and tomorrow we'll see if the problem is finally solved.

    cheers

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by zevang View Post
    I examined the applications running over my DB ("list application show detail" and "db2pd -db <dbname> -transaction") and discovered that there was one of them, among about 30, that was keeping about 200 locks all the time.

    As a result, not only the REORG tasks were not running properly, and returning -911 error, but also my client's queries were very slow.

    I asked the developer of the application that feeds the DB to shorter the period of commit commands. This alone resolved the problem with the clients. Now the application keeps only 5 locks.

    I'll schedule a REORG task for tonight and tomorrow we'll see if the problem is finally solved.

    cheers
    Just remember that you can never be too thin, too rich, or commit too often.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Dec 2009
    Posts
    62
    I'll keep an eye on that.

    Thanks a lot Marcus

Posting Permissions

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