Transformations > SQL transformation > SQL transformation configuration
  

SQL transformation configuration

Use the Properties panel to configure the SQL transformation. When you configure the transformation, you specify the transformation name and description, configure fields and field mapping, and specify the type of SQL that the transformation processes. You also set the advanced properties for the transformation.
The following image shows the Properties panel of an SQL transformation that is configured to process a stored procedure:
On the Properties panel an Oracle connection is selected, the SQL type is set to "Stored Procedure," and a stored procedure named "SALARY_INCREMENT_DOUBLE" is selected from the database.
Configure the transformation using the following tabs on the Properties panel:
General
Configure the SQL transformation name and optional description.
Incoming Fields
Define field rules that determine the data to include in the transformation.
Not applicable to unconnected SQL transformations.
SQL
Define the database connection and the type of SQL that the transformation processes: either a stored procedure, stored function, or query.
If you configure the transformation to process a stored procedure, stored function, or saved query, you select the stored procedure, stored function, or saved query on this tab.
If you configure the transformation to process a stored procedure, you can choose to run the transformation as an unconnected transformation.
If you configure the transformation to process a user-entered query, the SQL editor appears on this tab. Enter the query in the SQL editor.
Input Fields
For transformations that process stored procedures, displays the stored procedure input fields.
Field Mapping
For stored procedure and stored functions, specify how to map incoming fields to the input fields of the selected stored procedure or function.
You do not configure the field mapping for queries or unconnected SQL transformations.
Output Fields
For stored procedures, stored functions, and saved queries, displays a preview of the SQL transformation output fields. For user-entered queries, configure output fields for the columns retrieved from the database.
For queries, the output fields also include the SQLError field, the optional NumRowsAffected field, and optional pass-through fields.
Advanced
Define advanced properties for the transformation. Advanced properties differ based on the type of SQL that the transformation processes.
Note: Field name conflicts must be resolved in an upstream transformation. You cannot use field name conflict resolution rules in an SQL transformation.

Configuring the SQL type

You can configure the SQL transformation to process a stored procedure, stored function, saved query, or user-entered query on the SQL tab of the SQL transformation.
The steps for configuring the transformation vary based on the type of SQL that the transformation processes.

Selecting a stored procedure or function

You can configure the SQL transformation to process a stored procedure or stored function on the SQL tab of the SQL transformation.
    1. In the Properties panel of the SQL transformation, click the SQL tab.
    2. Select the connection to the database.
    You can select the connection or use a parameter.
    Note: If you want to parameterize the connection, create the parameter after you select the stored procedure or function.
    3. Set the SQL type to Stored Procedure or Stored Function.
    4. Click Select to select the stored procedure or function from the database, or enter the exact name of the stored procedure or function to call.
    The stored procedure or function name is case-sensitive.
    Note: If you add a new stored procedure to the database while you have the mapping open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.
    5. If the transformation processes a stored procedure and you want to run the transformation in unconnected mode, select Unconnected Stored Procedure.
    6. If you want to parameterize the connection, click New Parameter and enter the details for the connection parameter.

Selecting a saved query

You can configure the SQL transformation to process a saved query on the SQL tab of the SQL transformation.
    1. In the Properties panel of the SQL transformation, click the SQL tab.
    2. Select the connection to the database.
    You can select the connection or use a parameter.
    3. Set the SQL type to SQL Query.
    4. Set the query type to Saved Query.
    5. Click Select to select the saved query from a list of Data Integration assets.

Entering a query

You can configure the SQL transformation to process a user-entered query on the SQL tab of the SQL transformation. Optionally, you can parameterize the query. When you parameterize the query, you enter the full query in the mapping task.
    1. In the Properties panel of the SQL transformation, click the SQL tab.
    2. Select the connection to the database or use a parameter.
    3. Set the SQL type to SQL Query.
    4. Set the query type to Entered Query.
    5. If you do not want to parameterize the query, enter the query in the query editor.
    Incoming fields are listed on the Fields tab. To add a field to the query, select the field and click Add.
    You can format the SQL and validate the syntax.
    Note: The syntax validation performs a general SQL syntax check but does not verify the SQL against the database. The validation can return a syntax error even though the SQL is valid for the database. In this case, you can still save and run the mapping.
    If you update the incoming fields after you configure the query, open the SQL tab to refresh the changes.
    6. If you want to parameterize the query, perform the following steps:
    1. a. Open the Parameters tab and create a new string parameter.
    2. b. Select the parameter, and then click Add to add the parameter to the query editor.
    3. When you add the parameter to the query editor, Data Integration encloses it in dollar sign characters ($).
      Do not format the SQL or validate the query.

SQL transformation field mapping

Configure field mapping in an SQL transformation to define how to use data from the upstream transformation in a stored procedure or function. You do not configure field mapping when the transformation processes a query.
Configure field mapping on the Field Mapping tab of the Properties panel.
You can configure the following field mapping options:
Field Map Options
Method of mapping fields to the SQL transformation. Select one of the following options:
Show Fields
Controls the fields that appear in the Incoming Fields list. Show all fields, unmapped fields, or mapped fields.
Automap
Links fields with matching names. Allows you to link matching fields, and then manually configure other field mappings.
You can map fields in the following ways:
You can use both Exact Field Name and Smart Map in the same field mapping. For example, use Exact Field Name to match fields with the same name and then use Smart Map to map fields with similar names.
You can undo all automapped field mappings by clicking Automap > Undo Automap. To unmap a single field, select the field to unmap and click Actions > Unmap.
Data Integration highlights newly mapped fields. For example, when you use Exact Field Name, Data Integration highlights the mapped fields. If you then use Smart Map, Data Integration only highlights the fields mapped using Smart Map.
Action menu
Additional field link options. Provides the following options:
Show
Determines how field names appear in the Stored Procedure Input Fields list. Use technical field names or labels.

SQL transformation output fields

You can view output fields for the SQL transformation on the Output Fields tab of the Properties panel. The Mapping Designer displays the name, type, precision, scale, and origin for each output field.
Information on the Output Fields tab varies based on the SQL type.

Output fields for stored procedures and functions

When the SQL transformation processes a stored procedure or function, the output fields include output parameters from the database. You cannot edit the transformation output fields. If you want to exclude output fields from the data flow or rename output fields before you pass them to a downstream transformation, configure the field rules for the downstream transformation.

Output fields for queries

When the SQL transformation processes a query, the output fields include the following fields:
Retrieved column fields
When the SQL query contains a SELECT statement, the transformation returns one row for each database row that it retrieves.
For user-entered queries, you must configure an output field for each column in the SELECT statement. The output fields must be in the same order as the columns in the SELECT statement.
For saved queries, Data Integration creates the output fields.
SQLError field
Data Integration returns row errors to the SQLError field when it encounters a connection or syntax error. It returns NULL to the SQLError field when no SQL errors occur.
For example, the following SQL query generates a row error from an Oracle database when the Employees table does not contain Product_ID:
SELECT Product_ID FROM Employees
Data Integration generates one row. The SQLError field contains the following error text in one line:
ORA-0094: “Product_ID”: invalid identifier Database driver error... Function Name: Execute SQL Stmt: SELECT Product_ID from Employees Oracle Fatal Error
When a query contains multiple statements, and you configure the SQL transformation to continue on SQL error, the SQL transformation might return rows from the database for one query statement, but return database errors for another query statement. The SQL transformation returns any database error in a separate row.
NumRowsAffected field
You can enable the NumRowsAffected output field to return the number of rows affected by the INSERT, UPDATE, or DELETE query statements in each input row. Data Integration returns the NumRowsAffected for each statement in the query. NumRowsAffected is disabled by default.
When you enable NumRowsAffected and the SQL query does not contain an INSERT, UPDATE, or DELETE statement, NumRowsAffected is zero in each output row.
The following table lists the output rows that the SQL transformation generates when you enable NumRowsAffected:
Query Statement
Output Rows
UPDATE, INSERT, DELETE only
One row for each statement with the NumRowsAffected for the statement.
One or more SELECT statements
Total number of database rows retrieved.
NumRowsAffected is zero in each row.
DDL queries such as CREATE, DROP, TRUNCATE
One row with zero NumRowsAffected.
When a query contains multiple statements, Data Integration returns the NumRowsAffected for each statement. NumRowsAffected contains the sum of the rows affected by each INSERT, UPDATE, and DELETE statement in an input row.
For example, a query contains the following statements:
DELETE from Employees WHERE Employee_ID = ‘101’;
SELECT Employee_ID, LastName from Employees WHERE Employee_ID = ‘103’;
INSERT into Employees (Employee_ID, LastName, Address)VALUES (‘102’, 'Gein', '38 Beach Rd')
The DELETE statement affects one row. The SELECT statement does not affect any row. The INSERT statement affects one row.
Data Integration returns one row from the DELETE statement. NumRowsAffected is equal to one. It returns one row from the SELECT statement, NumRowsAffected is zero. It returns one row from the INSERT statement with NumRowsAffected equal to one.
Pass-through fields
Define incoming fields as pass-through fields to pass data through the SQL transformation. The SQL transformation returns data from pass-through fields whether or not the SQL query returns rows.
When the source row contains a SELECT statement, the SQL transformation returns the data in the pass-through field in each row it returns from the database. If the query result contains multiple rows, the SQL transformation repeats the pass through field data in each row.
When a query returns no rows, the SQL transformation returns the pass-through column data and null values in the output fields. For example, queries that contain INSERT, UPDATE, and DELETE statements return no rows. If the query has errors, the SQL transformation returns the pass-through column data, the SQLError message, and null values in the output fields.
When you configure an incoming field as a pass-through field, Data Integration adds the field with the suffix "_output" in the Pass-Through Fields area.
If you configure a field as a pass-through field and then change the field name in the source, Data Integration does not update the pass-through field name and no data is passed through the field. In the SQL transformation, delete the old pass-through field and configure the updated incoming field as a pass-through field.

Advanced properties

Configure advanced properties for the SQL transformation on the Advanced tab. The advanced properties vary based on whether the transformation processes a stored procedure or function or a query.

Advanced properties for stored procedures or functions

The following table describes the advanced properties when the transformation processes a stored procedure or function:
Property
Description
Tracing Level
Detail level of error and status messages that Data Integration writes in the session log. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Subsecond Precision
Subsecond precision for datetime fields. You can change the precision for databases that have an editable scale for datetime data.
If you enable pushdown optimization, the database returns the complete datetime value, regardless of the subsecond precision setting.
Enter a positive integer value from 0 to 9. Default is 6 microseconds.
Stored Procedure Type
For unconnected transformations, determines when the stored procedure runs.
Select one of the following options:
  • - Target Pre Load. Runs before the target is loaded.
  • - Target Post Load. Runs after the target is loaded.
  • - Normal. Runs on a row-by-row basis.
  • - Source Pre Load. Runs before the mapping receives data from the source.
  • - Source Post Load. Runs after the mapping receives data from the source.
Call Text
For unconnected transformations with stored procedure type Target Pre/Post Load or Source Pre/Post Load, enter the call text for the stored procedure.
The call text is the stored procedure name followed by the input parameters in parentheses. If there are no input parameters, you must include an empty pair of parentheses. Do not include the SQL statement EXEC or use the :SP keyword.
Does not apply to Normal stored procedure types.

Advanced properties for queries

The following table describes the advanced properties when the transformation processes a saved or user-entered query:
Property
Description
Behavior
Transformation behavior, either active or passive.
If active, the transformation can generate more than one output row for each input row. If passive, the transformation generates one output row for each input row.
Default is Active.
Continue on SQL Error within Row
Continues processing the remaining SQL statements in a query after an SQL error occurs.
Enable this option to ignore SQL errors in a statement. Data Integration continues to run the rest of the SQL statements for the row. The SQL transformation does not generate a row error, but the SQLError field contains the failed SQL statement and error messages.
Tip: Disable this option to debug database errors. Otherwise, you might not be able to associate errors with the query statements that caused them.
Default is disabled.
Auto Commit
Enables auto-commit for each database connection.
Each SQL statement in a query defines a transaction. A commit occurs when the SQL statement completes or the next statement is executed, whichever comes first.
Default is disabled.
Max Output Row Count
The maximum number of rows that the SQL transformation can output from a SELECT query.
To configure unlimited rows, set this property to zero. Default is 600.
Tracing Level
Detail level of error and status messages that Data Integration writes in the session log. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Transformation Scope
The method in which Data Integration applies the transformation logic to incoming data.
Select one of the following options:
  • - Row. Applies the transformation logic to one row of data at a time. Choose Row when the results of the transformation depend on a single row of data.
  • - Transaction. Applies the transformation logic to all rows in a transaction. Choose Transaction when a row of data depends on all rows in the same transaction, but does not depend on rows in other transactions.
  • - All Input. Applies the transformation logic on all incoming data. When you choose All Input, Data Integration drops incoming transaction boundaries. Choose All Input when a row of data depends on all rows in the source.
Default is Row.