RDBMerge - Execute a Merge Statement
Execute a merge statement for the RDBMS table.
Stream Information
| Input | Format | Record |
|---|---|---|
| Number of Inputs | 1 | |
| Description |
Define the fields of the input stream in a special setting page. The field definition will be copied to the field definition of the output stream of the preceding mapper, then the value will mapped there. |
|
| Output | Format | Record |
| Description |
The input stream is copied to the output stream. |
Component Properties
| Name | Data Type | Mapping | Description |
|---|---|---|---|
| Connection | connection | - | Specify the target RDB for connection. Select an RDB connection name created in the connection pane or the Management Console. |
| TableName | string | In & Out |
Specify the table name for the object to be updated. Once you click the value field, the setting page of the table name and the field name will be shown. By defining the field here, the table name will be set automatically. About the setting page, please refer to the "Set table name and field" below. |
| BatchCount | int | - | Set how many pieces of record can be sent in a single batch processing. |
| UpdateCount | int | Out | The piece number of the record updated actually can be got. |
| QueryTimeout | int | In & Out |
Specify the waiting time, which is from sending the updated statement to RDBMS until a result is returned, in the unit of second. If the execution doesn't end up although the specified time has expired, an Exception will occur. If the time is specified as 0, the execution won't be time out. |
| Field(Input) | category | - |
Define the fields of the input stream. Specify how to process the item by the Merge statement in category. Specify "key" in the column which will be the key of the table. Specify "Insert Only" for the item which is only used when Insert. Specify "Update Only" for the item which is only used when Update. |
Transaction
| Commit | Commit RDBMS. |
|---|---|
| Rollback | Roll back RDBMS. |
Exceptions
| Type | Parameter | Stream for error handling flow | Error Code | Description |
|---|---|---|---|---|
| Exception | None | This component's input stream | - | When connection specified in Connection can't be found. |
| - | If the table or the field can't be found. | |||
| - | If you failed to execute SQL | |||
| ConnectException | None | This component's input stream | - | If you failed to connect to DBMS. |
Cancel of the flow
When flow is canceled, the flow usually aborts after the execution of that component is ends. However, the execution of this component is forced to be ended, then it aborts. If the execution is forced to be ended, the transaction is rolled back.
About the Merge Processing
In this component, the fast insertion can be realized by using the Merge Syntax existed in DBMS.
The processing content is as same as the "Update/Insert" (or "Insert/Update")mode of the RDB Component, the advantages of using the component are as below.
- Insert and Update can be executed in a single SQL statement, so the SQL's execution frequency will be lower, and the performance will be better.
- SQL can be executed as batching processing.
- The items which can be only used when Insert and Update can be specified.
The DBMSs can be used in this component are as below.
- Oracle 9i or above. (※1)
- SQLServer 2008 or above.(※2)
- DB2 9.1 or above.
- MySQL 5.1 or above.
However, when the DBMS's server version are one of the above, but the JDBC driver's version is too old, it can't run normally neither.
Please check the action when you apply the component. If it runs abnormally, please use the RDBPut Component.
Set Table and Field
You can select tables and fields to process for the RDBMS table in a special dialog box.
Once you select them from the dialog box, they will be shown as the target fields to be processed under the "Input" tab of the inspector.
After selecting the fields from the special dialog box, you can set the category in the inspector.
By setting value for the display name, the column name displayed in the table and the field name used in the stream definition can be distinguished.
●Display Definition Page
You can operate in one of the below ways to display the definition page.
- Click the value field of TableName.
- Double click the RDBMerge Component.
- Right click the RDBMerge Component to open a menu, then click "Select Table" in it.
_1.png)
- (1)Left：the area shows the table list.
- (2)Upper-right：function buttons.
- (3)Upper-right：the area shows the details of the RDBMS.
- (4)Lower-right：the checkboxes are used to select the fields to be processed.
- (5)Lower-right：the area shows the field list.
●Specify the Table to Read
If the definition page is shown, the table list will be shown in (1). If the table is specfied, the information will be shown in (3),(5).
- To specify a table to process, you can click a table name in the list of (1).
- To define a table name and add it, you can click "Define and add a table name" in (2) to open a dialog box, then define a table name here.
- Once you select "Add schema to table name", the table name will become "<schema name>.<table name>".
●Specify Field to Process
Once you specify a table, the field list of the table will be shown in (5). Under the initial status, the all fields will be processed and all of the (4)'s checkboxes have been selected.
- If you don't want to process a field, you can unselect its checkbox in (4).
- If you don't want to process the all fields, you can click "Unselect all" in (2).
- If you want to process the all fields, you can click "Select all".
Once you select fields and click "OK", the field list will be shown under the "Input" tab of the inspector.
By setting value for the display name, the column name displayed in the table and the field name used in the stream definition can be distinguished.