When you add a relational target to a mapping, you can configure the following properties:
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:
Steps to Enter a Target Update Statement
Use the following procedure to create an update statement.
To enter a target update statement:
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:
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:
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:
If you use a user-defined mapping parameter, mapping variable, workflow variable, or worklet variable, you must declare the parameter or variable.
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.