Environment SQL
The Integration Service runs environment SQL in auto-commit mode and closes the transaction after it issues the SQL. Use SQL commands that do not depend on a transaction being open during the entire read or write process. For example, if a source database is set to read only mode and you create an environment SQL statement in the source connection to set the transaction to read only, the Integration Service issues a commit after it runs the SQL and cannot read the source in read only mode.
You can configure connection environment SQL or transaction environment SQL.
Use environment SQL for source, target, lookup, and stored procedure connections. If the SQL syntax is not valid, the Integration Service does not connect to the database, and the session fails.
Connection Environment SQL
This custom SQL string sets up the environment for subsequent transactions. The Integration Service runs the connection environment SQL each time it connects to the database. If you configure connection environment SQL in a target connection, and you configure three partitions for the pipeline, the Integration Service runs the SQL three times, once for each connection to the target database. Use SQL commands that do not depend on a transaction being open during the entire read or write process.
For example, use the following SQL statement to set the quoted identifier parameter for the duration of the connection:
SET QUOTED_IDENTIFIER ON
Use the SQL statement in the following situations:
- •You want to set up the connection environment so that double quotation marks are object identifiers.
- •You configure the target load type to Normal and the Microsoft SQL Server target name includes spaces.
Transaction Environment SQL
This custom SQL string also sets up the environment, but the Integration Service runs the transaction environment SQL at the beginning of each transaction.
Use SQL commands that depend on a transaction being open during the entire read or write process. For example, you might use the following statement as transaction environment SQL to modify how the session handles characters:
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR
This command must be run before each transaction. The command is not appropriate for connection environment SQL because setting the parameter once for each connection is not sufficient.
Guidelines for Configuring Environment SQL
Consider the following guidelines when creating the SQL statements:
- •You can enter any SQL command that is valid in the database associated with the connection object. The Integration Service does not allow nested comments, even though the database might.
- •When you enter SQL in the SQL Editor, you type the SQL statements.
- •Use a semicolon (;) to separate multiple statements.
- •The Integration Service ignores semicolons within /*...*/.
- •If you need to use a semicolon outside of comments, you can escape it with a backslash (\).
- •You can use parameters and variables in the environment SQL. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the SQL statement, or you can use a parameter or variable as the environment SQL. For example, you can use a session parameter, $ParamMyEnvSQL, as the connection or transaction environment SQL, and set $ParamMyEnvSQL to the SQL statement in a parameter file.
- •You can configure the table owner name using sqlid in the connection environment SQL for a DB2 connection. However, the table owner name in the target instance overrides the SET sqlid statement in environment SQL. To use the table owner name specified in the SET sqlid statement, do not enter a name in the target name prefix.