The Poll Table activity checks if
a database operation (insert or update) has occurred on any rows of
a database table by checking the status of a special flag column at
the specified time interval.
During run time, for the insert and update operations,
the
Poll Table activity returns the data that
has changed in your database table and directly maps that data to
variables in the orchestration while the
Get Inserted Rows and
Get
Updated Rows activities returns the changed data into
a buffer table and that buffer table is mapped to a variable in the
orchestration.
Note: Use the Poll Table activity
instead of the Get Inserted Rows, Get
Deleted Rows, and Get Updated Rows activities
only when you must avoid adding triggers to your database.
Before
configuring the
Poll Table activity in Studio,
you must first either create two additional database columns in your
source database table or use two appropriate existing database columns
from your database table. The following database columns are required
in your source database table:
- A sequence column of INTEGER type - This column contains the
primary keys (a sequence of unique numbers: 1, 2, 3 ...)
- A flag column of CHAR(1) type - This column contains a flag that
the Integration Appliance uses during run time to determine if the
Integration Appliance should process the row.
Before an orchestration with a Poll Table
activity is run on the Integration Appliance, you must also populate
these columns in your database table. Set the flag column to P if
you do not want the Integration Appliance to process the row during
run time. Set the flag column to null if you want
the Integration Appliance to process the row during run time.
In the following example database table, all the values
in the
Flag column are set initially to P,
as shown in the following figure:
Attention: You must
manage the state of the flag column. After processing a row, the Integration
Appliance changes the flag column to
P for processed
as described in the following example scenario:
- A row is inserted into the database. The flag column is set to null for
the row.
- The Poll Table activity runs for the first
time and sets the flag value to P for this row.
- The same row is updated, and the Poll Table activity
runs again. The row is not returned in the return data of the activity
because the Integration Appliance checked the flag column and the
flag indicated that this row had already been processed.
In order for a row to be processed again by the
Integration Appliance, you must reset the flag column back to null before
an orchestration job with a Poll Table activity
accessing the same source database table is run.
The steps for adding and configuring a
Poll Table activity
to an orchestration are described in the following procedures:
Add a Poll Table activity in the
orchestration
- Create or
open an orchestration. A graphical representation of the orchestration
opens.
- Select the Activities tab and expand the Database folder.
- Drag the Poll Table activity onto the orchestration.
- Select the Poll Table activity.
Create, select, or edit
a database endpoint
- Click Pick Endpoint task from the Checklist and
choose from one of the following actions:
- Select an existing endpoint:
- Select Browse. The Project Explorer opens.
- Select an existing endpoint and click OK.
Skip to the Specify the columns of the source table to poll for database operations procedure.
- Select an existing endpoint and edit the endpoint:
- Select Browse. The Project Explorer opens.
- Select an existing endpoint and click OK.
- Click Edit. The Edit Endpoint pane
shows.
- Create a new endpoint:
- Select New. The Create Endpoint pane
opens.
- Create
or edit a database endpoint.
Specify the columns of the source
table to poll for database operations
- Click Pick Endpoint from the Checklist.
The Pick Endpoint pane opens.
- Click Browse. The Select a Single
Source Table dialog box opens.
- Select a table that during run time, the Poll Table activity
polls for database operations.
- Select a user schema from the Filter by User Schema list.
- Select Hide Buffer and Control Tables to
filter out any buffer or control tables used by the Integration Appliance.
Note: This filter only hides buffer and control tables that use the
standard names generated in scripts from Studio.
- Select a single table from the list:
- Click >> to add a table.
- Click << to remove a table.
- If you only want to poll for database operations on primary key
columns of the source table, select the Select Only the
Primary Keys from Table check box.
- Click OK. The columns of the table show
in the Pick Table pane. (If you selected the Select Only
the Primary Keys from Tables check box only the primary
key columns display.)
- If the character encoding for this table is different from the
encoding for the database, enter another encoding using one of the
following options:
- From the Encoding list, select one of the
default encoding types.
- Enter your encoding type directly by clicking in the Encoding field
and typing in your encoding type.
- Select the columns in the table that during run time you want
the PPoll Table activity to poll for database
operations (insert, delete, or update). By default all columns are
selected. Clear the Use check box for unwanted
columns. Click Use All to reselect all the
columns.
- Click the heading of the column to sort
the rows by the column value. For example, to sort the rows by their
column name, click in the Column Name heading
of the column. An arrow shows to the right of the column header name.
An up arrow indicates the column is sorted by alphabetic order starting
with a and continuing to z.
A down arrow indicates the column is sorted by reverse alphabetic
order starting with z and continuing to a.
Click the arrow to reverse the alphabetic order.
- If necessary, change the settings of the Data type, Encoding,
or Nullable values for each query input parameter.
Click the field to make a new selection:
Select the Sequence and Flag columns
- Select Choose Columns from the Checklist.
- Select a Sequence Column from the list.
This is the sequence column of INTEGER type containing the primary
keys (a sequence of unique numbers: 1, 2, 3 ...) that you either added
to your source database table or determined that you could use an
existing column in your source database table.
- Select a Flag Column from the list. A flag
column of CHAR type containing a flag that the Integration Appliance
sets to P when the Integration Appliance has processed
the row. The Integration Appliance uses sequence and flag columns
to check for duplicate messages. In addition, both columns and the
setting of the Delete rows after they have been processed check
box in the Delivery Rules pane are used by
the Integration Appliance to determine the action for a delete request:
- If the Delete rows after they have been processed check
box is selected, the Integration Appliance sets the flag to M and
then deletes the row.
- If the Delete rows after they have been processed check
box is cleared, the Integration Appliance sets the flag to M and
then P but the row is not deleted. In order
for a row to be processed again by the Integration Appliance, you
must reset the flag column back to null before
an orchestration job with a Poll Table activity
accessing the same source database table is run.
- Configure the Delivery
Rules.
Note: If you select the Exactly once option
in the Delivery Rules task, you must create
control tables that are used by the Integration Appliance during run
time. You create the control tables after publishing the orchestration
from Studio to the Integration Appliance. You create the control tables
using the Web Management Console (WMC) before you deploy the project.
Note: If
you select the
Exactly Once option, you must
enable persistence. For more information, see
Enabling Persisence.
- Configure the Retry
Options.
Map the output of the activity
- Select the Map Outputs task in the Checklist.
The output parameters for the activity are displayed under the row element
in the From Activity pane. The output parameters displayed in the
From Activity pane correspond to the columns you selected in the Pick
Table task. If the Batch option
was selected in Delivery Rules, the multiple
occurrences symbol displays next to the row element.
Note: During
run time, the rows output parameter of the Poll
Table activity is not populated with the sequence and
flag column values. Only the columns selected in the Pick
Table task are returned.
- Map the required output parameters to variables. See Creating a map for general instructions on
mapping. You are not required to map the output parameters for these
activities.