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
Related Information
[{"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"}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21995530