IBM Support

SQL30081N with Communication function detecting the error: "selectForRecvTimeout". Protocol specific error <irrelavant>

Troubleshooting


Problem

Intermittent communication error SQL30081n with function detecting the error "selectForRecvTimeout" after upgrade from v8 to v9.*

Cause


Connection timeout is not supported in DB2 V8 .
It is supported starting from DB2 V9.1.

What's new for V9.1: http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.rn.doc/doc/c0023014.htm
Connection timeout support for database applications added

You can now set a connection timeout value for DB2(R) database connections. A connection timeout value is a limit to the amount of time that an application should wait for a connection. Setting a connection timeout value is useful in case the database server is inaccessible. In this situation, it can take a long time for connection requests to fail and return.

For .NET, CLI, ODBC, and OLE DB applications, you need to use the ConnectTimeout configuration keyword to enable connection timeout. For other types of application interfaces, such as the command line processor (CLP), you need to set the DB2TCP_CLIENT_CONTIMEOUT registry variable. For more information, follow the related links.

Diagnosing The Problem

Collect applicable traces with the timestamp: for example CLI (with TraceTimeStamp=3) / db2trc with the timestamp option: db2trc -t -f t.dmp / reproduce the problem / db2trc off / db2trc flw -t t.dmp t.flw / db2trc fmt t.dmp t.fmt

Check application trace. As a sample I use the CLI trace:

Find if application is setting a limit on the Connection timeout through SQL_ATTR_LOGIN_TIMEOUT, e.g.:

SQLSetConnectAttrW(hDbc=0:1, fOption=SQL_ATTR_LOGIN_TIMEOUT, pvParam=&000000000000003c,iStrLen=-6 )

pvParam=&000000000000003c --> 60 sec

and check if there is ConnectTimeout keyword is used to set the connect timeout limit (using command db2 get cli cfg).

The default value for ConnectTimeout is 0 (connection never time out):
For more information please read about the Connecttimeout CLI/ODBC configuration keyword in the DB2 Information Center.

If the ConnectTimeout value explicitly specified in the db2cli.ini file, it take precedence over the value of SQL_ATTR_LOGIN_TIMEOUT during the execution of the application.

Check the elapsed time for the failing connection:

SQLDriverConnectW(......)
SQLDriverConnectW( ) <--- SQL_ERROR Time elapsed - +7.173860E+001 seconds ---> 71.7 sec

There is 2 possible scenarios:

1. If you have elapsed time that exeeds the value of SQL_ATTR_LOGIN_TIMEOUT or ConnectTimeout (whichever value is effective) then it means that the connection could not be established during the time set by the client.

You need to investigate this issue to find out why connection is taking so long time.



2. If elapsed time is much less then the value of SQL_ATTR_LOGIN_TIMEOUT or ConnectTimeout (if it used) but connection is still failing with SQL30081n / selectForRecvTimeout like in the following snippet:

SQLDriverConnectW(.....)
    sqlccconnr( timeout - +6.000000E+001, protocol - 0x03 ) => 60 secs
    sqlccconnr( ) - time elapsed - +4.294967E+003
    sqlccsend( Handle - 114234016 )
    sqlccsend( ulBytes - 238 )
    sqlccsend( ) rc - 0, time elasped - +1.500000E-005
    sqlccrecv( timeout - +1.000000E-006 )
    sqlccrecv( ulBytes - 0 ) - rc - 158, time elapsed - +5.250100E-002

SQLDriverConnectW( )  <--- SQL_ERROR   Time elapsed - +1.088000E-002 seconds

and db2trc shows that the timestamp for exit from function sqlccconnr is less then the timestamp for entry to the function sqlccconnr like here:

8270 entry DB2 UDB common communication sqlccconnr cei (1.3.43.3.2)
 pid 6088 tid 6108 cpid -1 node 0 sec 3 nsec 752945553

...

8321 exit DB2 UDB common communication sqlccconnr cei (2.3.43.3.2)
 pid 6088 tid 6108 cpid -1 node 0 sec 3 nsec 491364953
 rc = 0


then the reason can be related to the Windows defect for QueryPerformanceCounter() API that we use for the calculation of the timestamp. Basically, the timestamp in db2trc comes from the ossticks() function. ossticks() uses Windows API QueryPerformanceCounter() to get the time.
The symptom shown here is caused directly by the  QueryPerformanceCounter() API. So it is a problem to be answered by Microsoft.

Microsoft has published many KB articles on problem with QueryPerformanceCounter(). Some of these were written long time ago, and may or may not apply here. It depends on the type of CPU, and version of Windows, and fix pack level. Microsoft should be consulted to see which fix may apply.

The following article  http://support.microsoft.com/kb/895980 may be of some interest.

In order to workaround the problem for the scenario 2 your application should use setting for SQL_ATTR_LOGIN_TIMEOUT/ConnectTimeout = 0 and then address the problem with MS support team..

Some samples for setting SQL_ATTR_LOGIN_TIMEOUT value:

for MS SQL SERVER the SQL_ATTR_LOGIN_TIMEOUT it is set by the value of "remote login timeout";

for PHP application: DB2::Admin::->SetConnectAttributes('ConnectTimeout' => 0);

Probably it is easier just to use the ConnectTimeout=0 keyword in the db2cli.ini file in order to workaround the problem.

In order to compensate the defect with non-sync timestamp values from different CPUs we opened APARs:

JR33305 for v9.5 and JR33307 for v9.1 : "CLI CONNECTIONS TIMEOUT BEFORE THE REQUESTED TIME HAS ACTUALY ELAPSED (SQL30081N WITH FUNCTION SELECTFORRECVTIMEOUT)".

The fix for this APARs is a client side fix.

Resolving The Problem

Upgrade to the FixPak that have the fix for APAR JR33307 (v9.1) or JR33305 (v9.5) or use a workaround: set ConnectTimeout=0 in db2cli.ini and then restart the application.

Related Information

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"OTHER - Uncategorised","Platform":[{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21389666