ODBC Connection issue

piyu · Post by **piyu** » Thu Oct 13, 2011 9:08 pm

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

piyu · Post by **piyu** » Fri Oct 14, 2011 2:00 am

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

ppgoml · Post by **ppgoml** » Fri Oct 14, 2011 2:06 am

try use

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

piyu · Post by **piyu** » Fri Oct 14, 2011 2:30 am

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