DSXchange

Hi,

I need help on this error faced while accessing DB2 using a ODBC connection :
ODBC_Connector_41,0: [IIS-CONN-ODBC-000013] ODBC Info: SQLSTATE = HY000: Native Error Code = 1,210: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Abnormal end unit of work condition occurred. ODBC_Connector_41,0: [IIS-CONN-ODBC-000013] ODBC Info: SQLSTATE = 40001: Native Error Code = -911: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and Linux]ROLLBACK DUE TO DEADLOCK/TIMEOUT; REASON 68.

ODBC_Connector_41,0: [IIS-CONN-ODBC-000016] Execute failed on statement SELECT X,Y,Z FROM intdm.TBINTL_PROD_ASSETS_DIM [IIS-CONN-ODBC-000004] ODBC function "SQLExecute" reported: SQLSTATE = HY000: Native Error Code = 1,210: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver]Abnormal end unit of work condition occurred

We have tried running the job multiple times and it does not seem to be the usual timeout which causes these deadlock errors. We also tried using explicit "with ur" option in sql other than the default Uncommitted Read in the stage.

The same connection works fine as a native db2 connection. The ODBC stage however is required to be used as the db2 native connector stage is goofing up decimals while the odbc is not.

Any help will be much appreciated.

Thanks,
Anu

Here is some more info :

The DBA has informed us that the Metastage process query is holding a lock on the table when job runs. Any idea what this is about?

Query holding lock :

select Computer, SoftwareProduct, DataStore, DataSchema from MetaStage_Loc_Info

try use

select Computer, SoftwareProduct, DataStore, DataSchema from MetaStage_Loc_Info with UR

This is done by Datastage in background. I dont think we can edit the query.