Transformations > Target transformation > Database targets
  

Database targets

Database targets include relational sources such as Oracle, MySQL, and Microsoft SQL Server.
When you configure a Target transformation for a database target, you can write data to a single target table. You can select an existing table or create the table at run time.
Ensure that the table and column names do not exceed 74 characters.

Database target properties

You configure database target properties on the Target tab of the Properties panel.
The following table describes the database target properties:
Property
Description
Connection
Name of the target connection.
Alternatively, you can define a parameter, and then specify the connection in the mapping task.
Target Type
Target type, either single object or parameter.
Object
Name of the target object.
If you select a single object, you can also preview the data.
Operation
Target operation, either insert, update, upsert, delete, or data driven.
Truncate Target
Truncates the target object before inserting new rows.
Applies to insert and data driven operations.
Enable Target Bulk Load
Uses the database bulk API to perform an insert operation.
Use the bulk API to write large amounts of data to the database with a minimal number of API calls. Loading in bulk mode can improve performance, but it limits the ability to recover because no database logging occurs.
Applies to insert operations.
Update Columns
The fields to use as temporary primary key columns when you update, upsert, or delete target data. When you select more than one update column, the mapping task uses the AND operator with the update columns to identify matching rows.
Applies to update, upsert, delete and data driven operations.
Data Driven Condition
Enables you to define expressions that flag rows for an insert, update, delete, or reject operation.
For example, the following IIF statement flags a row for reject if the ID field is null. Otherwise, it flags the row for update:
IIF (ISNULL(ID), DD_REJECT, DD_UPDATE )
Applies to the data driven operation.
Forward Rejected Rows
Causes the mapping task to forward rejected rows to the reject file.
If you do not forward rejected rows, the mapping task drops rejected rows and writes them to the session log.
If you enable row error handling, the mapping task writes the rejected rows and the dropped rows to the row error logs. It does not generate a reject file. If you want to write the dropped rows to the session log in addition to the row error logs, you can enable verbose data tracing.
Pre SQL
SQL command to run against the target before reading data from the source.
You can enter a command of up to 5000 characters.
Post SQL
SQL command to run against the target after writing data to the target.
You can enter a command of up to 5000 characters.
Update Override
Overrides the default UPDATE statement for the target.
Enter the update statement. Alternatively, click Configure to generate the default UPDATE statement, and then modify the default statement.
The UPDATE statement that you enter overrides the default UPDATE statement that Data Integration uses to update targets based on key columns. You can define an override UPDATE statement to update target tables based on non-key columns.
For more information about database target properties, see the help for the appropriate connector.

Database targets created at run time

If a mapping includes a database target, you can select an existing target table or create the target table at run time. When you create a database target at run time, Data Integration automatically discovers the target object metadata for data type, precision, and scale, based on the data source.
If you need to edit target object metadata, you can edit it in the Source transformation.
You cannot link the target fields to the upstream transformation. If you want to reduce the number of unused fields in the target, configure field rules in the Target transformation or in the upstream transformations.
When you create a database target at run time, the mapping task creates the database table the first time the mapping runs based on the fields from the upstream transformation.
In subsequent runs, the mapping task replaces the data in the target table that was created in the initial run. Consequently, if you change the mapping after the initial run, in subsequent runs the target will not reflect changes to the number of target fields and its metadata. To see the changes, you can either delete the existing target before you run the mapping or change the name of the target.
If you create a relational target at run time, the target operation is always insert. You can choose to truncate the target.
Note: In mappings created before the Spring 2020 September release, Data Integration converts Bigint data from a parameterized source to Int data in database targets created at runtime. To write Bigint data to the target without conversion, edit the mapping in the Mapping Designer and enable the option in the mapping advanced properties.
Data Integration does not convert Bigint data in mappings created after the Spring 2020 September release.

Creating a database target at run time

To create a database target at run time, select Create New at Runtime in the Target Object dialog box and enter the target table name.
    1. On the Target tab of the Target transformation, select a database connection.
    2. Set the target type to Single Object.
    3. Click Select to select the target object.
    4. In the Target Object dialog box, select Create New at Runtime.
    5. Enter the target table name.
    6. Click OK.

Update columns for relational targets

You can configure one or more fields as update columns to update or upsert data to relational targets.
The mapping task uses an update column to update or upsert data in the target. When you select more than one update column, the mapping task uses the AND operator with the update columns to identify matching rows.
When you use a parameter for the target connection or target object, you can configure update columns in the task.

Configuring update columns

You can configure update columns when you use the update or upsert operation to update data in a relational target.
    1. In the Properties panel, click the Target tab.
    2. Select a relational connection.
    You can also use a connection parameter for a relational database connection type.
    3. Select the target type that you want to use.
    4. Select a target object.
    5. Select the update or upsert operation.
    6. To select update columns, click Add.
    The Update Columns window displays all target columns.
    7. Move the fields that you want to use from the Target Columns list to the Update Columns list.
    8. Click OK.

Target update override

By default, Data Integration 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.
You can enter a target update override for relational and ODBC connections. For more information, see the help for the appropriate connector.
Override the UPDATE statement in the Target transformation advanced properties. Enter the target UPDATE statement in the Update Override field. Alternatively, click Configure to generate the default UPDATE statement and then modify the statement.
Because the target fields must match the target column names, the update statement includes the keyword :TU to specify the fields in the target transformation. If you modify the UPDATE portion of the statement, you must use :TU to specify fields.
When you override the default UPDATE statement, you must enter an SQL statement that is valid for the database. Data Integration does not validate the syntax.

Example

A mapping passes the total sales for each salesperson to the T_SALES table.
Data Integration generates the following default UPDATE statement for the target T_SALES:
UPDATE
T_SALES
SET
EMP_NAME = :TU.EMP_NAME,
DATE_SHIPPED = :TU.DATE_SHIPPED,
TOTAL_SALES = :TU.TOTAL_SALES
WHERE
EMP_ID = :TU.EMP_ID
You want to override the WHERE clause to update records for employees named Mike Smith only. To do this, you edit the WHERE clause as follows:
UPDATE
T_SALES
SET
DATE_SHIPPED = :TU.DATE_SHIPPED,
TOTAL_SALES = :TU.TOTAL_SALES
WHERE
:TU.EMP_NAME = EMP_NAME AND EMP_NAME = 'MIKE SMITH'

Guidelines for configuring the target update override

Use the following guidelines when you enter target update queries:

Entering a target update statement

Enter a target update statement on the Target tab of the Target transformation.
    1. On the Target tab of the Target transformation, open the advanced properties.
    2. Click Configure next to the Update Override field.
    3. In the Update Override SQL Editor, click Generate SQL.
    The default UPDATE statement appears.
    4. Modify the UPDATE statement.
    Tip: Click Format SQL to format the UPDATE statement for easier readability.
    You can override the WHERE clause to include non-key columns. Enclose all database reserved words in quotes.
    5. Click OK.