IBM Support

DataStage job with ODBC Connector receives an error when using LOB Column

Technote (troubleshooting)


Problem(Abstract)

When selecting data on a SQL server table using ODBC Connector stage the job sometime fails with the following error:

table,0: ODBC function "SQLGetData" reported: SQLSTATE = 07009: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver] Invalid Descriptor Index (CC_OdbcInputStream::getTotalSize, file CC_OdbcInputStream.cpp, line 349)

Cause

Some ODBC drivers have a limitation when you are working with large object (LOB) columns. With the SQL Server drivers, if a select list contains a LOB column, the LOB column must be listed last in the select list.

Diagnosing the problem

For these drivers, there are two different scenarios where the query fails:

  • The LOB columns are not listed last.
  • You specify a wildcard asterisk (*) for the select list and the position of the LOB column in the table is not the last column in that table.

When you use drivers that have this limitation and you have LOBs in your table, you must ensure that the LOB columns are listed last in the select list.

In this example, you have two LOB columns in the "Employees" table: "Description" and "Comments". If you write the following SELECT statement and run the job, the job fails:

select EmployeeID, Description , Comments , Photo from Employees

This is because the select list includes the Photo column after the LOB columns.

Edit this statement to move the LOBs to the end as specified in the following statement:

select EmployeeID, Photo, Description , Comments from Employees

The LOB columns are now at the end of the statement. The job will run successfully.

Resolving the problem

Ensure that the LOB columns are listed last in the select statement.

Document information

More support for: InfoSphere DataStage

Software version: 8.1, 8.5, 8.7

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1595601

Modified date: 30 May 2012