Configuring a Data Integration Object

Name and Description Pane

The following image shows the Name and Description pane, where you can enter a name and description for an instance of the Data Integration object. This name will display in your process flow.

Select Statement Pane

The Select Statement pane allows you to enter an SQL statement to retrieve the records for which you are interested. The SQL Query Builder provided by the DTP can be used to assist in this task.

Top of page

Procedure: How to Access the SQL Query Builder

To access the SQL Query Builder:

Select the connection profile from the Connection Profile drop-down list.
If you have not created a connection profile, exit the wizard by clicking Cancel, and follow the steps in the DTP help.
Select the database from the Database drop-down list.
Click SQL Builder.
For more information on using the SQL Query Builder, see the DTP help.
Once you have completed entering the SELECT statement, click Next, as shown in the following image.

Insert Statement Parameters Pane

This section describes the Insert Statement Parameters pane, as shown in the following image.

Top of page

Parameterized SQL

The destination SQL is expected to take the following form:

INSERT INTO <table name> (<column name>*) VALUES (<?name>*)

where:

?name: Is the name of a user parameter, the value of which is inserted for the specific row. It is anticipated that this will be an _iwxpath() expression to locate the value in the document that reaches the iterator on the loop edge. It is not required for the name to be the actual column name in the destination table.

Top of page

Procedure: How to Generate Parameterized SQL

To generate the required parameterized SQL:

Enter a destination table name in the Table Name field.
Click the plus () icon.
The Property dialog opens, as shown in the following image.
Enter the name of your parameter and a corresponding value. The value can be a constant, XPath, IFL, or SREG.
Click OK.
Once you have entered your parameters, click Generate to create the INSERT statement, as shown in the following image.

If the target table has the same schema as the source database, you can click the following icon:

This allows you to create parameters and XPath statements to extract the data from an XML document that has the same format as when it left the Data Integration object.

This extracted data is then used to populate the parameters. Clicking Generate will then create the INSERT statement using the parameters. You can leverage this feature if your target table has the same number of columns but different names by using aliases in your SELECT statement.

Object Properties Pane

This section describes the Object Properties pane, which is shown in the following image.

The following table lists and describes the available parameters for the Data Integration object.

Name

Description

Source

Input Provider

(required)

Name of the JDBC provider used to access the input (source) database.

This parameter must be provided with the name of the data provider created in iSM for the source database. For more information on creating data providers, see the iWay Service Manager User’s Guide.

Select SQL

The SQL SELECT statement used to retrieve rows from the source database.

Transaction Isolation Level

Determine if the transactional control of the input should be set to a specific value. The following list shows the available options that can be specified:

As is. As set for the database. This value is set by default.

Read Uncommitted. Allows reading of a record that may be rolled back later.

Read Committed. Will never read data that another application has changed and not yet committed, but does not ensure that the data will not be changed before the end of the transaction.

Repeatable Read. Dirty reads and non-repeatable reads can not occur. All data used in the query is locked, and other transactions cannot update the data.

Serializable. Most restrictive isolation level. Phantom values cannot occur. This prevents other users from updating or inserting rows into the data set until the transaction is completed.

Base 64 if Needed

Determines if binary data should be passed to the loop as base 64 if binary data is read. This value is set to false by default.

Input Encoding

Determines what IANA encoding should be assumed for binary data during conversion to base 64. The default is the current system default.

Format as SQLAgent

If set to true, then the generated rows are formatted according to the SQL service field schema. This value is set to false by default.

Three Part Name

If set to true, then the names will be presented as full three part names if supported by the database. This value is set to false by default.

Destination

Output Provider (required)

Name of the JDBC provider used to access the output (destination) database.

This parameter must be provided with the name of the data provider created in iSM for the destination database. For more information on creating data providers, see the iWay Service Manager User’s Guide.

Output Insert

The SQL INSERT statement used to insert data into the target database. This SQL uses a special format to delineate the columns to receive data from the loop return.

Out Encoding

Determines what IANA encoding should be assumed for binary data during conversion from base 64. The default is the current system default.

Batch Size

Determines how many inserts constitute a batch. Each sub-batch is executed to the destination. The use of sub-batches may reduce memory depending on the characteristics of the destination database and its drivers.

Commit Sub-batches

If sub-batches are requested, determine whether the batches should be committed or if all of the commits should be held for EOS/process flow end or a transactional commit. This value is set to false by default.

Fail First

Determine if an insert failure on the first row should be considered as a catastrophic failure. The default is to treat such a failure as a normal row insert failure. This value is set to false by default.

Omit Test

If present, this is an iFL test that is evaluated for each candidate destination record. If it evaluates to true, then the candidate record is omitted.

Main

Output document type

Determines whether the output document that is emitted should be the original input document (input) or a status document (status).

Name	Description
Source
Input Provider (required)	Name of the JDBC provider used to access the input (source) database. This parameter must be provided with the name of the data provider created in iSM for the source database. For more information on creating data providers, see the iWay Service Manager User’s Guide.
Select SQL	The SQL SELECT statement used to retrieve rows from the source database.
Transaction Isolation Level	Determine if the transactional control of the input should be set to a specific value. The following list shows the available options that can be specified: As is. As set for the database. This value is set by default. Read Uncommitted. Allows reading of a record that may be rolled back later. Read Committed. Will never read data that another application has changed and not yet committed, but does not ensure that the data will not be changed before the end of the transaction. Repeatable Read. Dirty reads and non-repeatable reads can not occur. All data used in the query is locked, and other transactions cannot update the data. Serializable. Most restrictive isolation level. Phantom values cannot occur. This prevents other users from updating or inserting rows into the data set until the transaction is completed.
Base 64 if Needed	Determines if binary data should be passed to the loop as base 64 if binary data is read. This value is set to false by default.
Input Encoding	Determines what IANA encoding should be assumed for binary data during conversion to base 64. The default is the current system default.
Format as SQLAgent	If set to true, then the generated rows are formatted according to the SQL service field schema. This value is set to false by default.
Three Part Name	If set to true, then the names will be presented as full three part names if supported by the database. This value is set to false by default.
Destination
Output Provider (required)	Name of the JDBC provider used to access the output (destination) database. This parameter must be provided with the name of the data provider created in iSM for the destination database. For more information on creating data providers, see the iWay Service Manager User’s Guide.
Output Insert	The SQL INSERT statement used to insert data into the target database. This SQL uses a special format to delineate the columns to receive data from the loop return.
Out Encoding	Determines what IANA encoding should be assumed for binary data during conversion from base 64. The default is the current system default.
Batch Size	Determines how many inserts constitute a batch. Each sub-batch is executed to the destination. The use of sub-batches may reduce memory depending on the characteristics of the destination database and its drivers.
Commit Sub-batches	If sub-batches are requested, determine whether the batches should be committed or if all of the commits should be held for EOS/process flow end or a transactional commit. This value is set to false by default.
Fail First	Determine if an insert failure on the first row should be considered as a catastrophic failure. The default is to treat such a failure as a normal row insert failure. This value is set to false by default.
Omit Test	If present, this is an iFL test that is evaluated for each candidate destination record. If it evaluates to true, then the candidate record is omitted.
Main
Output document type	Determines whether the output document that is emitted should be the original input document (input) or a status document (status).