The below mentioned database connectors have been enhanced to generate and validate SQL statements at design time in the DataStage Designer.
- DB2 connector
- DRS connector
- Netezza connector
- ODBC connector
- Oracle connector
- Teradata connector
The following enhancement has been made to generate and validate SQL statements at design time.
Generating SQL statements
You can generate SQL statements by selecting the Generate SQL statement option in the context menu of the Build button associated with each statement property in the stage editor. Each connector will generate SQL by using the data types and syntax specific to the RDBMS it supports. After the statement is generated it will be populated in the appropriate property.
The Generate SQL statement option will only be available for statements which that connector supports generating at design time. In some cases a connector may only support generating the SQL at runtime during job execution.
Validating SQL statements
After generating the SQL statements you can then validate the SQL statements. The new function Validate SQL is also added to the context menu that is accessed from the Build button and will only be enabled if the statement property contains a value. The connector will execute design-time validation by preparing the statement with the RDBMS it supports. If there are any issues with the SQL (syntax errors, for example), the SQL error will be displayed.
Similar to SQL generation, the SQL validation option will only be available for statements which that connector supports validating at design time.
Note: Under some circumstances, the connector requires a connection for generating and validating SQL statements. When a user name and password are not supplied and a connection is required, then a connection will be made by using the user who is running the ASBAgent service/process.