Troubleshooting
Problem
DB2 ODBC Connection Pooling does not work with Microsoft ADO and the MSDASQL provider.
Symptom
When running a Microsoft ADO application with the Microsoft OLE DB-ODBC bridge provider (MSDASQL) and the IBM DB2 ODBC/CLI driver, ODBC Connection Pooling may not work.
When the application performs a disconnect, the physical connection on the database will go away as well.
This problem can also occur with a Microsoft .NET application when using the ODBC.NET data provider.
Cause
Microsoft ADO is setting a connection attribute called SQL_ATTR_CURRENT_CATALOG. The DB2 CLI/ODBC provider returns a CLI0150E "Driver not capable" error message, and this causes ADO to disable connection pooling.
Diagnosing The Problem
Taking a CLI trace of the application will reveal the error condition. In the CLI trace, look for the following:
SQLSetConnectAttrW( hDbc=0:1, fOption=SQL_ATTR_CURRENT_CATALOG, pvParam=&0341a814, iStrLen=12 )
---> Time elapsed - +4.510609E+000 seconds
SQLSetConnectAttrW( )
<--- SQL_ERROR Time elapsed - +3.955300E-002 seconds
SQLGetDiagRecW( fHandleType=SQL_HANDLE_DBC, hHandle=0:1, iRecNumber=1, pszSqlState=&045add48, pfNativeError=&045ad938, pszErrorMsg=&045ad948, cbErrorMsgMax=512, pcbErrorMsg=&045ad930 )
---> Time elapsed - +5.745000E-003 seconds
( iRowNumber=-2, iColumnNumber=-2 )
SQLGetDiagRecW( pszSqlState="S1C00", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=S1C00", pcbErrorMsg=62 )
<--- SQL_SUCCESS Time elapsed - +1.017800E-001 seconds
Microsoft ADO is setting the connection attribute SQL_ATTR_CURRENT_CATALOG. The DB2 CLI/ODBC driver does not support this attribute, and rightfully so returns an error message to tell the application that this attribute is not supported. Microsoft ADO then performs an SQLDisconnect() call to disconnect from the database even though connection pooling should be enabled.
Resolving The Problem
The reason why Microsoft ADO sets the attribute for SQL_ATTR_CURRENT_CATALOG is because earlier on, Microsoft ADO checks to see what the value of the SQL_ATTR_CURRENT_CATALOG attribute is. The DB2 CLI/ODBC driver will return the name of the database when that call is done.
Microsoft ADO then believes that since a value is being returned from the ODBC provider it can set a new value later on. This assumption is what causes this problem to occur.
The DB2 ODBC/CLI driver has an internal db2cli.ini keyword called PATCH2=64 that will return an error if the SQL_ATTR_CURRENT_CATALOG attribute is being queried. When that happens ADO will not try to set this attribute later on to a new value since it knows that the driver does not support this attribute. By not setting the attribute, ADO will not perform a disconnect which will then keep connection pooling in tact.
PATCH2=64 can be set in the db2cli.ini file under the data source section, or it can be passed into the connection string.
For example, if the data source name is called "SAMPLE", then the following should be added to the db2cli.ini file:
[SAMPLE]
PATCH2=64
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21455135