User-defined SQL

The InfoSphere® DataStage® Balanced Optimization tool attempts to parse user-defined SQL, but sometimes the SQL cannot be parsed, even if it is valid for the database.

For SQL in a target connector, failure to parse the SQL prevents pushing of processing into that database target. In addition to this restriction, optimizing user-defined SQL in a target connector is only supported for the Netezza connector.

For SQL in a source or reference connector, failure to parse the SQL causes a warning in the optimization trace log. The unparsed SQL statement is embedded in generated SQL for pushing processing to sources. Optimization attempts to drop any ORDER BY clauses in the unparsed SQL statement.

When the whole job is pushed into target connector SQL, any unparsed source or reference SQL is embedded in generated target SQL. Check the detailed execution log of such an optimized job to ensure that the combined SQL that includes such embedded source or reference SQL functions correctly.

If you use multiple database instances when defining SQL, fully qualify table names with the intended database instance name (for example, dbinstancename.tablename not tablename). When InfoSphere Balanced Optimization cannot parse user-defined SQL, fully qualified names ensure correct name resolution when SQL statements are combined.