Working with Relational Targets in a Mapping - Informatica

When you add a relational target to a mapping, you can configure the following properties:

• Reject truncated and overflow data. Select this option in the target instance Properties tab when you want the Integration Service to write truncated data to the reject file.
• Update override. Override the default UPDATE statement using the SQL Editor in the target instance Properties tab.
• Table name prefix. Specify the owner of the target tables in the target instance Properties tab.
• Pre- and post-session SQL. You can enter pre-session SQL commands for a target instance in a mapping to execute commands against the target database before the Integration Service reads the source. Enter post- session SQL commands to execute commands against the target database after the Integration Service writes to the target.
• Target table name. You can override the default target table name.

Note:You cannot configure these properties in the Target Designer.

Rejecting Truncated and Overflow Data

The Designer lets you convert data by passing it from port to port. Sometimes a conversion causes a numeric overflow (numeric data) or truncation (on character columns). For example, passing data from a Decimal (28, 2) to a Decimal (19, 2) port causes a numeric overflow. Likewise, if you pass data from a String(28) port to a String(10) port, the Integration Service truncates the strings to 10 characters. When a conversion causes an overflow, the Integration Service, by default, skips the row. The Integration Service does not write the data to the reject file. For strings, the Integration Service truncates the string and passes it to the next transformation.

The Designer provides an option to let you include all truncated and overflow data between the last transformation and target in the session reject file. If you select Reject Truncated/Overflow Rows, the Integration Service sends all truncated rows and any overflow rows to the session reject file or to the row error logs, depending on how you configure the session.

Configuring the Target Update Override

By default, the Integration Service updates target tables based on key values. However, you can override the default UPDATE statement for each target in a mapping. You might want to update the target based on non-key columns.

When the Integration Service executes SQL against a source, target, or lookup database, it searches the reserved words file stored in the Integration Service installation directory. It encloses matching reserved words in quotes. If you use target update override, you must manually put all reserved words in quotes.

For a mapping without an Update Strategy transformation or a Custom transformation with the update strategy property enabled, configure the session to mark source rows as update. The Target Update option only affects source rows marked as update. The Integration Service processes all rows marked as insert, delete, or reject normally. When you configure the session, mark source rows as data-driven. The Target Update Override only affects source rows marked as update by the Update Strategy or Custom transformation.

For example, a mapping passes the total sales for each salesperson to the T_SALES table.
The Designer generates the following default UPDATE statement for the target T_SALES:

Because the target ports must match the target column names, the update statement includes the keyword:TU to specify the ports in the target transformation. If you modify the UPDATE portion of the statement, be sure to use :TU to specify ports.

Overriding the WHERE Clause

You can override the WHERE clause to include non-key columns. For example, you might want to update records for employees named Mike Smith only. To do this, you edit the WHERE clause as follows:

Rules and Guidelines for Configuring the Target Update Override

Use the following rules and guidelines when you enter target update queries:

• If you use target update override, you must manually put all database reserved words in quotes.
• You cannot override the default UPDATE statement if the target column name contains any of the following characters:
' , ( ) < > = + - * / t n 0 <space>

• You can use parameters and variables in the target update query. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the UPDATE statement, or you can use a parameter or variable as the update query. For example, you can enter a session parameter, $ParamMyOverride, as the update query, and set $ParamMyOverride to the UPDATE statement in a parameter file.
• When you save a mapping, the Designer verifies that you have referenced valid port names. It does not validate the SQL.
• If you update an individual row in the target table more than once, the database only has data from the last update. If the mapping does not define an order for the result data, different runs of the mapping on identical input data may result in different data in the target table.
• A WHERE clause that does not contain any column references updates all rows in the target table, or no rows in the target table, depending on the WHERE clause and the data from the mapping. For example, the following query sets the EMP_NAME to “MIKE SMITH” for all rows in the target table if any row of the transformation has EMP_ID > 100:
UPDATE T_SALES set EMP_NAME = 'MIKE SMITH' WHERE :TU.EMP_ID > 100
• If the WHERE clause contains no port references, the mapping updates the same set of rows for each row of the mapping. For example, the following query updates all employees with EMP_ID > 100 to have the EMP_NAME from the last row in the mapping:
UPDATE T_SALES set EMP_NAME = :TU.EMP_NAME WHERE EMP_ID > 100
• If the mapping includes an Update Strategy or Custom transformation, the Target Update statement only affects records marked for update.
• If you use the Target Update option, configure the session to mark all source records as update.

Steps to Enter a Target Update Statement

Use the following procedure to create an update statement.

To enter a target update statement:

1. Double-click the title bar of a target instance.
2. Click Properties.
3. Click the Open button in the Update Override field.
The SQL Editor displays.

4. Select Generate SQL.
The default UPDATE statement appears.

5. Modify the update statement.
You can override the WHERE clause to include non-key columns.
Enclose all reserved words in quotes.

6. Click OK.
The Designer validates the SQL when you save the mapping.

Configuring the Table Name Prefix

The table name prefix is the owner of the target table. For some databases, such as DB2, target tables in a session can have different owners. If the database user specified in the database connection is not the owner of the target tables in a session, specify the table owner for each target instance. A session can fail if the database user is not the owner and you do not specify the table owner name.

You can specify the table owner name in the target instance or in the session properties. When you enter the table owner name in the session properties, you override the transformation properties.

Note:When you specify the table owner name and you set the sqlid for a DB2 database in the connection environment SQL, the Integration Service uses table owner name in the target instance. To use the table owner name specified in the SET sqlid statement, do not enter a name in the target name prefix.

To specify a target owner name at the target instance level:

1. In the Designer, open the Mapping Designer tool.
2. Double-click the title bar of a relational target instance in the mapping.
3. On the Properties tab, enter the table owner name or prefix in the Value field for Table Name Prefix.
4. Click OK.

Adding Pre- and Post-Session SQL Commands

You can enter pre- and post-session SQL commands on the Properties tab of the target instance in a mapping.
You might want to run pre- and post-session SQL on the target to drop indexes before a session runs, and recreate them when the session completes.

The Integration Service runs pre-session SQL commands against the target database before it reads the source. It runs post-session SQL commands against the target database after it writes to the target.

You can override the SQL commands on the Mappings tab of the session properties. You can also configure the Integration Service to stop or continue when it encounters errors executing pre- or post-session SQL commands.

Rules and Guidelines for Adding Pre- and Post-Session SQL Commands

Use the following rules and guidelines when you enter pre- and post-session SQL commands in the target instance:

• Use any command that is valid for the database type. However, the Integration Service does not allow nested comments, even though the database might.
• You can use parameters and variables in the in the target pre- and post-session SQL commands. For example, you can enter a parameter or variable within the command. Or, you can use a session parameter, $ParamMyCommand, as the SQL command, and set $ParamMyCommand to the SQL statement in a parameter file.
• Use a semicolon (;) to separate multiple statements. The Integration Service issues a commit after each statement.
• The Integration Service ignores semicolons within /* ...*/.
• If you need to use a semicolon outside of comments, you can escape it with a backslash ().
• The Designer does not validate the SQL.

Note:You can also enter pre- and post-session SQL commands on the Properties tab of the Source Qualifier transformation.

Overriding the Target Table Name

You can override the target table name in the target instance of a mapping. Override the target table name when you use a single mapping to load data to different target tables. Enter a table name in the target table name. You can also enter a parameter or variable. You can use mapping parameters, mapping variables, session parameters, workflow variables, or worklet variables in the target table name. For example, you can use a session parameter, $ParamTgtTable, as the target table name, and set $ParamTgtTable to the target table name in the parameter file.

To override a target table name:

1. In the Designer, open the Mapping Designer.
2. Double-click the title bar of a relational target instance in the mapping.
3. On the Properties tab, enter the target table name. Or, enter a parameter or variable name in the Target Table Name field.

If you use a user-defined mapping parameter, mapping variable, workflow variable, or worklet variable, you must declare the parameter or variable.

4. Click OK.
5. If you use a parameter or variable for the target table name, define the parameter or variable in the appropriate section of the parameter file.