IBM Support

In some cases DB2 connector fails to create tables when working with column-organized database

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.

[{"Product":{"code":"SSZJPZ","label":"IBM InfoSphere Information Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"11.5.0.2","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSZJPZ","label":"IBM InfoSphere Information Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":" ","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg22003176

Page Feedback