IBM Support

InformationServer: Cannot use the system column ROWID or other reserved word in a Netezza Common Connector query

Technote (troubleshooting)


Problem(Abstract)

User wishes to extract the Netezza ROWID system column along with other columns from a given table.

Symptom

A job containing ROWID or a reserved word as a Netezza column will compile without warning. Upon running the job, the job will fail and an error similar to the following will be logged to the IIS Director Log:

Netezza_Connector_1,0: Failed to execute SQL statement:

CREATE EXTERNAL TABLE NZCC_20120313101217_19835_0(ROWID BIGINT NOT NULL, COL1 CHAR(10) NOT NULL)
USING (DATAOBJECT ('/tmp/Redhat6485_19835_nzw_0_0')
  remotesource 'odbc' delimiter '' ignorezero false ctrlchars true escapechar ''
logDir '/tmp' boolStyle 'T_F' encoding 'internal' nullValue 'N' ).

Reason: [SQLCODE=42S21][Native=30] ERROR:  Attribute 'ROWID' has a name conflict
 Name matches an existing system attribute (CC_NZConnectionRep::executeDirect, file CC_NZConnectionRep.cpp, line 172)

or

Netezza_Connector_0,0: Failed to execute SQL statement: CREATE EXTERNAL TABLE NZCC_20160706122520565670_7891_0(SUID CHAR(10) NOT NULL, LOAD CHAR(6) NOT NULL, ...) USING (DATAOBJECT '/tmp/XXXXX_###_0_0_20160706122520565653') remotesource 'odbc' delimiter '?' ignorezero false ctrlchars true escapechar '\' logDir '/tmp' boolStyle 'T_F' encoding 'internal' nullValue 'N' ). Reason: Failed to get error details (CC_NZConnectionRep::executeDirect, file CC_NZConnectionRep.cpp, line 201)

When run manually outside of DataStage receive the following error:
ERROR [42000] ERROR:  'CREATE EXTERNAL TABLE NZCC_20160706115641480005_6067_0(SUID NCHAR(10) NOT NULL, DIST NCHAR(8) NOT NULL,  LOAD NCHAR(6) NOT NULL, ...) USING (DATAOBJECT '/tmp/NNNN_###_0_0_20160706115641479987')'


Cause

As part of a Netezza Common Connector table extract, an external Netezza table is created with the same columns as defined with the IIS D/S job. Netezza does not allow the reserved system column name or reserved word to be used.


Environment

Netezza

Resolving the problem

Use user-defined SQL such as: SELECT ROWID as rid,.. and use rid in the table schema in Columns tab of the connector stage. This is ensure that the external table will be created with a column named 'rid' rather than 'ROWID' or other reserved word.

Document information

More support for: InfoSphere DataStage

Software version: 8.5, 8.7, 9.1, 11.3, 11.5

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

Software edition: All Editions

Reference #: 1588159

Modified date: 2016-07-12