Monday, August 25, 2014

Generate Insert/Update SQL for DataStage Oracle Connector Automatically (Updated)


Last year I published a post for automatically generating insert/update SQL used in DataStage Oracle connectors. I used Oracle 11g's new function LISTAGG(), which resulted in 2 major limitations.

  1. It only applies to Oracle 11g or later.
  2. LISTAGG has 4000-character length limit. So it doesn't work on wide tables with more than 100 columns.
Here is an updated version without using LISTAGG(), hence removing the limitations.

Insert SQL:
Update SQL:

Wednesday, August 6, 2014

DataStage Netezza Connector Sparse Lookup Error: Count field incorrect


Error Message:
Unexpected ODBC error occured. Reason: [SQLCODE=07002][Native=8] Count field incorrect (CC_NZStatement::executeSelect, file CC_NZStatement.cpp, line 137)
Cause:
 One or more fields in the data stream are used multiple times in the lookup SQL statement.

Work-around:
Duplicate the field(s) so that each is used exactly once.