Recently, while working at a customer site and I encountered an anomaly in the Netezza Connector stage, when choosing ‘User-defined SQL’ write mode, the ‘Table name’ displays a caution / warning even though a table name should not be required. If you are using a user-defined SQL statement and/or have parametrized your SQL scripts to make the job reusable, each SQL and/or SQL script would have its own schema and table name being passed in. After some investigation, a workaround was found, which both allows you to populate table name and leverage with different schema and table names within your SQl statement and/or.
Table Name, User-defined SQL, Warning
You will notice, in a screen shot below the ‘User-defined SQL’, ‘write mode’, property has been chosen, a parameter has been placed in the ‘User-defined SQL’ property, and ‘Read user defined SQL from a file’ property has been set to ‘Yes’. However, yellow triangle displays on the ‘Table name’ property marking it as a required item. This, also, occurs when placing SQL statements in the User-defined SQL property, whether reading from a file of not.
Netezza Connector User-Defined SQL , Table Name Required , Warning
Table Name, User-defined SQL, Warning Workaround
After some experimentation, the workaround is straight forward enough. Basically, give the ‘table name’ property something to read successfully, so it can move on to the user-defined SQL and/or user defined SQl file script, which the process actually needs to execute. In the screenshot below, the SYSTEM.DEFINITION_SCHEMA._V_DUAL view was used, so, it could be found, then the script file passed by the parameter runs fine. Another view or table, which the DataStage user has access to, should just as well.
Netezza Connector, User-Define SQL, Table Name Required Fix