InformationServer: Cannot use the system column ROWID in a Netezza Common Connector query
User wishes to extract the Netezza ROWID system column along with other columns from a given table.
A job containing ROWID 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)
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 of ROWID to be used.
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'.
More support for:
Software version: 188.8.131.52, 8.5, 184.108.40.206, 220.127.116.11, 8.7
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Software edition: All Editions
Reference #: 1588159
Modified date: 2012-08-08