IBM Support

DataStage job using ODBC stage to access SQL Server table fails with SQL errors if the table name is a subset of a column name.

Troubleshooting


Problem

DataStage job with ODBC driver reading from SQL Server table where the table name is a subset of any column name in that table, the query fails with several errors: DSD.BCIOpenWcall to function SQLPrepare failed, SQLSTATE=S0022, DBMS.CODE=207 SQLSTATE=37000, DBMS.CODE=8180 Statement(s) could not be prepared.

Symptom

DataStage job with ODBC driver reading from SQL Server table fails with errors:
CatDesc..Transformer_2: CatDesc..Cat_Desc_SQL.DSLink8: DSD.BCIOpenWcall to function SQLPrepare failed.
SELECT DTCCATNO,DTCCATDESCFROM CATDESC SQLSTATE=S0022, DBMS.CODE=207
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'DTCCATNO'. SQLSTATE=S0022, DBMS.CODE=207
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'DTCCATDESCFROM'. SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Error occurs when table name (i.e. CATDESC) is a subset of a column name (i.e. DTCCATDESC).

Resolving The Problem

This error is the result of a parsing problem which occurs when table name is a subset of a column name in the same table.

This issue can be bypassed using any of the following 3 methods:

  1. Add schema to table name in stage properties field for table name.
  2. Change table name in stage definition to lowercase (if table name is lowercase and column name is uppercase, the problem does not occur).
  3. Modify original table so that the table name is not used as part of any column name within the same table.

[{"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.1;8.7;8.5;8.1;8.0;11.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21687787

Page Feedback