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:
|
Control Tables | Used with the following activities when
messages are specified to be delivered exactly once:
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:
|
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:
|
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:
|