IBM Support

Why does my application receive -952, -30081 or -4499 in locktimeout?

Question & Answer


Question

Some applications or clients receive SQL0952N, SQL30081N (selectForRecvTimeout) or -4499 (Read timeout) when it's expected to receive locktimeout (SQL0911N rc=68). Please tell me why this happens.

Cause

Shorter query timeout/ receive timeout values than lock timeout can cause -952, -30081 or -4499 instead of -911 (lock timeout).

Answer

Shorter query timeout or receive timeout can prevent getting SQL0911N rc=68 in lock timeout.
The following steps illustrate how this timeout happens.

1. Set LOCKTIMEOUT as 20 sec on SAMPLE database.

    $ db2 get db cfg for sample | grep LOCK
     Lock timeout (sec)      (LOCKTIMEOUT) = 20

2. Lock ORG table in exclusive mode by using command line processor (CLP).
    $ db2 connect to sample
    $ db2 +c lock table org in exclusive mode

3. Run Jcc test program to execute a query against ORG table, it's expected to fail with SQL0911 rc=68.
    $ java com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://localhost:50000/SAMPLE" -user db2inst1 -password passw0rd -sql \'select deptname from org\'
    [jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:50000/SAMPLE:commandTimeout=10; -user db2inst1 -password ******** -sql 'select deptname from org'

    [jcc][10516][13709]Test Connection Successful.
    [jcc][10515][13711]Execution of SQL failed with error code -911.
    Exception:
    com.ibm.db2.jcc.am.SqlTransactionRollbackException: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=3.69.24

4. Run the same program with shorter query timeout (commandTimeout=10) than locktimeout (20).
It's expected to fail with SQL0952N, because the application sent a cancel request after waiting for 10 sec.
    $ java com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://localhost:50000/SAMPLE:commandTimeout=10;" -user db2inst1 -password passw0rd -sql \'select deptname from org\'
    [jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:50000/SAMPLE:commandTimeout=10; -user db2inst1 -password ******** -sql 'select deptname from org'

    [jcc][10516][13709]Test Connection Successful.
    [jcc][10515][13711]Execution of SQL failed with error code -952.
    Exception:
    com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=3.69.24

5. Run the same program with shorter receive timeout (blockingReadConnectionTimeout=10) than locktimeout (20).
It's expected to fail with -4499, because socket read() throws a timeout exception after waiting for 10 sec.
    $ java com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://localhost:50000/SAMPLE:blockingReadConnectionTimeout=10;" -user db2inst1 -password passw0rd -sql \'select deptname from org\'

    [jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:50000/SAMPLE:blockingReadConnectionTimeout=10; -user db2inst1 -password ******** -sql 'select deptname from org'

    [jcc][10516][13709]Test Connection Successful.
    [jcc][10515][13711]Execution of SQL failed with error code -4,499.
    Exception:
    com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: [jcc][t4][2030][11211][3.69.24] A communication error occurred during operations on the connection's underlying socket, socket input stream,
    or socket output stream.  Error location: Reply.fill() - socketInputStream.read (-1).  Message: Read timed out. ERRORCODE=-4499, SQLSTATE=08001

NOTE:
-4499 is specific for Jcc applications. Non-Java DB2 clients return SQL30081N (selectForRecvTimeout) in the same situation.
For example:
    $ db2set DB2TCP_CLIENT_RCVTIMEOUT=10
    $ db2 terminate

    $ db2 connect to SAMPLE user db2inst1 using passw0rd

       Database Connection Information

     Database server        = DB2/AIX64 10.5.8
     SQL authorization ID   = DB2INST1
     Local database alias   = SAMPLE

    $ db2 "select * from org"
    SQL30081N  A communication error has been detected. Communication protocol
    being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location
    where the error was detected: "192.168.100.123".  Communication function
    detecting the error: "selectForRecvTimeout".  Protocol specific error code(s):
    "0", "*", "*".  SQLSTATE=08001

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - JCC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5;11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21995530

Page Feedback