Topic
  • 8 replies
  • Latest Post - ‏2009-09-11T12:25:45Z by CozyRoc
CozyRoc
CozyRoc
10 Posts

Pinned topic Unable to use ODBC bulk load - SQLBulkOperations

‏2009-08-20T13:25:38Z |
Hello Guys,

I have issues using the ODBC bulk load facilities. The same code has been already tested with other database systems and it works properly. When I call SQLBulkOperations method, I receive the following error message:


[IBM][CLI Driver] CLI0150E  Driver not capable. SQLSTATE=HYC00 [SQLSTATE: HYC00]


The DB2 database engine version is v9.5.0.808 and it is the trial version. The client software is IBM Data Server Runtime Client 9.5. I have also downloaded and installed the latest IBM DB2 CLI client. Same problem there.

Is there a way to find what is the issue? I have already read and re-read the DB2 documentation and it looks like the ODBC bulk load API is be supported.

Thank you for your time in advance!
  • CozyRoc
    CozyRoc
    10 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-08-31T17:45:26Z  
    Isn't anyone from DB2 team available to answer this question?
  • Sawan
    Sawan
    56 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-09-01T02:58:28Z  
    • CozyRoc
    • ‏2009-08-31T17:45:26Z
    Isn't anyone from DB2 team available to answer this question?
    Hi,

    Could you provide us a CLI and db2 trace of the scenario.

    CLI may return "Driver not Capable" if the cursor is neither Keyset or Dynamic.

    Regards,
    Sawan Gupta
  • CozyRoc
    CozyRoc
    10 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-09-01T20:01:01Z  
    • Sawan
    • ‏2009-09-01T02:58:28Z
    Hi,

    Could you provide us a CLI and db2 trace of the scenario.

    CLI may return "Driver not Capable" if the cursor is neither Keyset or Dynamic.

    Regards,
    Sawan Gupta
    Hi Sawan,

    Find attached the trace file. There is something strange going on. When I have both cli and db2trc active, the cli trace is very different. I have included cli trace with and without db2trc.

    p.s.

    The type of cursor is SQL_CURSOR_KEYSET_DRIVEN. I have tried also SQL_CURSOR_DYNAMIC and the error is still there.
  • CozyRoc
    CozyRoc
    10 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-09-08T15:55:12Z  
    • Sawan
    • ‏2009-09-01T02:58:28Z
    Hi,

    Could you provide us a CLI and db2 trace of the scenario.

    CLI may return "Driver not Capable" if the cursor is neither Keyset or Dynamic.

    Regards,
    Sawan Gupta
    Sawan,

    Did you have a chance to check the trace file I have sent?
  • Sawan
    Sawan
    56 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-09-10T13:25:53Z  
    • CozyRoc
    • ‏2009-09-08T15:55:12Z
    Sawan,

    Did you have a chance to check the trace file I have sent?
    Hi,

    I had a look at the traces. It seems that you requested SQL_CURSOR_KEYSET_DRIVEN in one thread (which was successful) and called SQLBulkOperations in the other thread.

    Maybe the cursor was downgraded if the following criteria is met

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.cli.doc/doc/c0007645.html

    "Note: A column with a LOB type, distinct type on a LOB type, A column with a LONG VARCHAR, LONG VARGRAPHIC, DATALINK, LOB, XML type, distinct type on any of these types, or structured type cannot be specified in the select-list of a scrollable cursor. DB2 CLI will downgrade the cursor type from scrollable to forward-only and return a CLI0005W (SQLSTATE 01S02) warning message."

    Regards,
    Sawan
  • CozyRoc
    CozyRoc
    10 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-09-10T14:03:08Z  
    • Sawan
    • ‏2009-09-10T13:25:53Z
    Hi,

    I had a look at the traces. It seems that you requested SQL_CURSOR_KEYSET_DRIVEN in one thread (which was successful) and called SQLBulkOperations in the other thread.

    Maybe the cursor was downgraded if the following criteria is met

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.cli.doc/doc/c0007645.html

    "Note: A column with a LOB type, distinct type on a LOB type, A column with a LONG VARCHAR, LONG VARGRAPHIC, DATALINK, LOB, XML type, distinct type on any of these types, or structured type cannot be specified in the select-list of a scrollable cursor. DB2 CLI will downgrade the cursor type from scrollable to forward-only and return a CLI0005W (SQLSTATE 01S02) warning message."

    Regards,
    Sawan
    Sawan,

    These are the column types in the destination table: INTEGER, TIMESTAMP, DOUBLE, VARCHAR, DECIMAL

    The SQLBulkOperations fails even when trying to insert only in the INTEGER column. The initialization and SQLBulkOperations are called on the same thread, so I don't know how the cursor could be downgraded. Probably the CLI driver itself is multithread internally, I don't know.

    Do you have other ideas?
  • Sawan
    Sawan
    56 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-09-11T10:33:27Z  
    • CozyRoc
    • ‏2009-09-10T14:03:08Z
    Sawan,

    These are the column types in the destination table: INTEGER, TIMESTAMP, DOUBLE, VARCHAR, DECIMAL

    The SQLBulkOperations fails even when trying to insert only in the INTEGER column. The initialization and SQLBulkOperations are called on the same thread, so I don't know how the cursor could be downgraded. Probably the CLI driver itself is multithread internally, I don't know.

    Do you have other ideas?
    Though Keyset cursor was requested, but it was not granted by server.
    SQLExecDirectW( hStmt=6:1, pszSqlStr="SELECT * FROM "TEST"."TEST_TABLE"", cbSqlStr=33 )
    ---> Time elapsed - +4.240000E-004 seconds
    ( StmtOut="SELECT * FROM "TEST"."TEST_TABLE"" )
    ( Package="SYSSH200 ", Section=4 )
    ( Requested Cursor Attributes=134217747 )
    ( Reply Cursor Attributes=134742026 )
    ( Actual Cursor Attributes=134742027 )
    Regards,
    Sawan Gupta
  • CozyRoc
    CozyRoc
    10 Posts

    Re: Unable to use ODBC bulk load - SQLBulkOperations

    ‏2009-09-11T12:25:45Z  
    • Sawan
    • ‏2009-09-11T10:33:27Z
    Though Keyset cursor was requested, but it was not granted by server.
    SQLExecDirectW( hStmt=6:1, pszSqlStr="SELECT * FROM "TEST"."TEST_TABLE"", cbSqlStr=33 )
    ---> Time elapsed - +4.240000E-004 seconds
    ( StmtOut="SELECT * FROM "TEST"."TEST_TABLE"" )
    ( Package="SYSSH200 ", Section=4 )
    ( Requested Cursor Attributes=134217747 )
    ( Reply Cursor Attributes=134742026 )
    ( Actual Cursor Attributes=134742027 )
    Regards,
    Sawan Gupta
    Sawan,

    This still doesn't answer the question why DB2 would do that. This exact same code has been already successfully tested with SQL Server, Oracle, Informix, PostgreSQL and it works properly. The only possible explanation I could think is either there is some setting on the server side that has to be made or this is a bug in the DB2 stack.

    Do you have some other idea how this could be further diagnosed?