Database Entities and Activities

The following table shows the relationship between the database entities and the different types of database activities. In addition, this table describes how the database entity is created in the database.

Note: For DB2® UDB, the asset generation script limits the names of buffer and control tables to nine characters.
Note: For Generic JDBC Driver connections: The scripts used to generate assets are only a template based on the information available from the database. The scripts should be analyzed to confirm that the SQL syntax and datatypes are supported for the target database. If they are not, the scripts must be modified and run via another mechanism to create the required assets for the database.
Database Entity Associated Activity Description How created?
Buffer Tables Used with the inbound single-table activities: Get Inserted Rows, Get Updated Rows, and Get Deleted Rows. The buffer tables contain the result of a database operation.

For example, a Get Inserted Rows activity is polling for an insert on the database. When the insert occurs in the database, the buffer tables are populated with data that was inserted.

The buffer tables used with inbound single-table activities are created using the following procedure:
  1. In the Web Management Console (WMC), go to the Assets screen and execute the scripts.
  2. From the WMC, deploy the project.
Control Tables Used with the following activities when messages are specified to be delivered exactly once:
  • Insert Rows
  • Update Rows
  • Delete Rows
  • Call Procedure

Control tables are not needed if another delivery option is specified. Specify a delivery option in the Deliver Rules task of the Checklist for an activity.

The control tables used with activities are created using the following procedure:
  1. In the Web Management Console (WMC), go to the Assets screen and execute the scripts.
  2. From the WMC, deploy the project.
Triggers Used with inbound single-table activities, Get Inserted Rows, Get Updated Rows, and Get Deleted Rows, to populate the buffer table. The triggers used with inbound single-table activities are created using the following procedure:
  1. In the Web Management Console (WMC), go to the Assets screen and execute the scripts.
  2. From the WMC, deploy the project.
Stored Procedures Stored procedures are required with the Call Procedure activity. The stored procedure must be written and installed in the database before you can complete the configuration of the Call Procedure activity in Studio. The stored procedure is not created using the WMC. The database administrator must create the stored procedure directly in the database.
Sequence Used with inbound single-table activities, Get Inserted Rows, Get Updated Rows, and Get Deleted Rows, for the Oracle database only. Oracle only - The sequences used with inbound single-table activities are created using the following procedure:
  1. Publish a project with an orchestration that contains the inbound single-table activity to the Integration Appliance from Studio.
  2. Use the Web Management Console (WMC) to create the sequences in the Oracle database used by the orchestration.
  3. From the WMC, deploy the project.



Feedback | Notices


Timestamp icon Last updated: Tuesday, 27 September 2016


http://pic.dhe.ibm.com/infocenter/wci/v7r0m0/topic/com.ibm.wci.doc/conn_ref_Connector_Prerequisites_for_Database_Operations.html