75 ways to demystify DB2: #56: ResourceInfo: ODBC applications fail to return data when connecting through V10.5 FP4 or FP5 DB2 Connect gateway.

Body

DB2 ODBC applications running on a DB2 client fail with "No data returned" error when connecting through V10.5 Fp4 or Fp5 DB2 Connect gateway connecting to a DB2/zOS subsystem.

Symptoms of the issue:

Direct connection from DB2 Client to DB2/zOS successfully returns data.

Issue happens only when connecting through V10.5 FP 4 or FP5 DB2 connect gateway.

Other Scenarios noticed:

MS-Access application using IBM DB2 ODBC driver failed to fetch the data and returned "No current record"

CLI application return:  SQL_NO_DATA_FOUND

ADODB.Connection fails with error 800A0E78  "Operation is not allowed when the object is closed".
 

In the CLI traces from the DB2 Client, we noticed:

08/05/2015 09:19:28.264032] SQLExecDirectW( )
[08/05/2015 09:19:28.264197]     <--- SQL_NO_DATA_FOUND   Time elapsed

Solution:

The issue is caused by a known APAR: IT07411 ODBC APPLICATIONS RETURN NO ROW AFTER UPGRADING THE DB2 GATEWAY

This APAR is on the DB2 Connect gateway and the fix needs to be applied on the DB2 Connect gateway.

As of now the APAR is in open state and will be fixed in future Fix Packs of DB2 V10.5

Local Fix: 

If .NET applications, update db2dsdriver.cfg file with following property.

    <parameter name="CommitOnEOF" value="0"/>

If CLI applications, update db2cli.ini file with the following keyword:

db2 update cli cfg for section <DBNAME> using CommitOnEOF 0

Refer:  CommitOnEOF

Thanks for reading!

-Subbu Prabhu