Troubleshooting
Problem
DB2 connector fails to create tables using generated create statement when working with column-organized databases and any of the table's columns is of a type that cannot be included in column-organized tables (BLOB, CLOB, DBCLOB, NCLOB or XML).
Cause
Columns of several data types (BLOB, CLOB, DBCLOB, NCLOB or XML) cannot be included in a column-organized table as described in DB2 documentation:
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0061528.html
DB2 connector does not distinguish between row- and column-organized DB2 databases and does not support the "ORGANIZE BY ROW" clause introduced in DB2 together with column-organized tables. In databases with default table organization by column it is required to manually configure tables to be organized by row at table creation time, when any of the table columns is of one of the unsupported types.
Diagnosing The Problem
When working with DB2 databases with default table organization set to column, DataStage jobs with DB2 connector configured to create or replace the target table, using create statement generated at runtime, fails with message similar to:
SQLExecDirect() reported: SQLSTATE = 42613: Native Error Code = -1,666: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL1666N The CREATE TABLE statement failed because some functionality was specified in the table definition that is not supported with the table type. Unsupported functionality: "CLOB". SQLSTATE=42613
Resolving The Problem
Change DB2 connector configuration property 'Generate create statement at runtime' from 'Yes' to 'No' and manually specify the table create statement in the 'Create statement' property field.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22003176