Single-Table Outbound Database Activities

This section contains the procedures for configuring the following single-table outbound database activities.

These activities include:
  • Insert Rows - This activity takes data from variable or variables that are mapped to the input parameters of the activity and inserts this data as rows into the specified database table.
  • Update Rows - This activity takes data from variable or variables that are mapped to the input parameters of the activity and updates the rows of the specified database table with this new data.
  • Delete Rows - This activity takes data from variable or variables that are mapped to the input parameters of the activity and deletes rows of the specified database table based on this data.
For the Update Rows and the Delete Rows activities, the Integration Appliance integrates the database table to determine which columns in the table are primary key columns. When the Update Rows or the Delete Rows activities are started during run time, the Integration Appliance uses the value of the incoming variable that contains the primary key number to determine which rows to update or delete.
For example, the PK column is defined in the database as being a primary key column for the database table. The Use check box is selected for the PK column in the Pick Table task of the Checklist for the Delete Rows activity. In the Map Inputs pane of the Delete Rows activity, the variable called myIncomingPK is mapped to the PK element of the row as shown in Figure 1:
Figure 1. myIncomingPK is mapped to the PK element of the row
During run time, if the value of the myIncomingPK variable is equal to 3 the row with the PK equal to 3 is deleted from the table as shown in Figure 2:
Figure 2. The row with the primary key equal to 3 is deleted
If no primary key columns are specified in the Pick Table task of the Checklist, specify at least one column to act like the primary key. Specify acting primary key column or columns using the Configure Primary Keys dialog box, available by clicking Keys in the Pick Table task of the Checklist for the Delete Rows and Update Rows activities. If no primary keys are specified in the database table or selected as acting primary keys in the Configure Primary Keys dialog box, all the rows of the database table are affected by the delete or the update operation.

You can also use the Configure Primary Keys dialog box to specify additional columns to act as primary key columns. For example for the table in the preceding figure, you could define that the Name column should act like primary key column in addition to the PK primary column that was defined in the database as being a primary key column.

Add a single-table outbound database activity to the orchestration

  1. Create or open an orchestration. A graphical representation of the orchestration is displayed.
  2. Select the Activities tab and expand the Database folder.
  3. Drag the wanted single-table outbound database activity (Insert Rows, Delete Rows, or Update Rows) onto the orchestration.
  4. Select the activity.

Create, select, or edit a database endpoint

  1. Click Pick Endpoint task from the Checklist and choose from one of the following actions:
    • Select an existing endpoint:
      1. Select Browse. The Project Explorer is displayed.
      2. Select an existing endpoint and click OK. Skip to the "Configure the activity" procedure.
    • Select an existing endpoint and edit the endpoint:
      1. Select Browse. The Project Explorer is displayed.
      2. Select an existing endpoint and click OK.
      3. Click Edit. The Edit Endpoint pane is displayed.
    • Create an endpoint: Select New. The Create Endpoint pane is displayed.
  2. Create or edit an HTTP endpoint, see Creating or editing an HTTP endpoint.

Select the columns in the source database table that during run time the Integration Appliance polls for changes

  1. Click Pick Table from the Checklist. The Pick Table pane is displayed.
  2. Click Browse... to select the database table that is to be the source for inbound messages. The Browse Database Tables dialog box is displayed.
  3. Select a table from the list and click OK. Column information for the selected table is displayed in the Pick Table pane.
  4. 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.
    This sets the encoding for all the columns of the resulting buffer table.
    Note: Some double-byte characters are not converted using the SHIFT-JIS encoding. For more information, see Using the Shift-JIS encoding.
  5. By default all columns in the source table are selected. Clear the Use check box to remove a column from resulting buffer table. Click Use All to reselect all the columns.
  6. Click in the heading of the column to sort the rows by the column value. For example, to sort the rows by their column name, click the Column Name heading of the column. An arrow is displayed 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.
  7. For the Get Updated Rows activity only - if you want the resulting buffer table to contain both the new values and the original values of the updated columns follow these steps:
    1. Click Old Values. The Old Value Columns for Updates dialog box displays.
    2. Select the Store check box for the database columns that you want to save the original values.
    3. You can change the default names assigned to columns to store the original values. Double-click the appropriate Column Name to Store Old Value cell.
    4. Click OK.  The selected columns are displayed in the From Activity of the Map Outputs pane prefixed with the string: old_. You can map both the new and original values from the Map Outputs pane.  
    Note: For more information see Defining Columns for Old Values in Updates.
  8. If necessary, change the settings of the Data Type, Encoding, or Nullable values. Click the field to make a new selection.
    • Data Type - Defaults to the data type for the column in the source database table. If the required data type for the column in the buffer table is different from the data type of the source table, select a different data type by clicking in the Data Type cell and selecting another Data Type from the list. You must select an appropriate data type for those data types that are marked UNSUPPORTED.
      Note: For the XMLTYPE data type in an Oracle database and the MONEY data types in a SQL Server database, Studio does not automatically associate the correct data type so you must manually select the appropriate data type.
      Attention: You must select a compatible data type with the source data type to avoid invalid data during run time. For example, do not select the SMALLINT data type for the buffer table if the source data type is INTEGER because during run time a large number stored in an INTEGER column will not convert correctly to the SMALLINT data type.
    • Size - Size of the column defined in the table. This size is retrieved from the database table and is not configurable.
    • Encoding - Defaults to current default encoding that was set either at the database level or the table level. If the required encoding for the column is different from the current default encoding, select another encoding by clicking in the Encoding cell and selecting another Encoding from the list.  
    • Nullable - Defaults to the nullable setting for the column in the source database table. The Use check box must be selected for those columns where nullable=false. A column marked nullable=false cannot not contain a null during run time.
  9. Click Reset to reset all column properties to their defaults. This also resets the Use check box for all columns.
  10. Click Refresh to update the list of columns in reference to the latest state of the source database. Recent changes to the source are updated in this pane.
  11. Configure the Specifying Delivery Rules for Database Activities.
    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 persistence.
  12. Configure the retry options. For more information, see Retry Options For Inbound Database Activities.

Specify the target database table for the activity

  1. Click Pick Table from the Checklist. The Pick Table pane is displayed.
  2. Click Browse... to select the target database table. The Browse Database Tables dialog box is displayed.
  3. Select a table from the list and click OK. Column information for the selected table is displayed in the Pick Table pane.
  4. 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.
    This sets the encoding for all the columns of the resulting buffer table.
    Note: Some double-byte characters are not converted using the SHIFT-JIS encoding. For more information, see Using the Shift-JIS encoding.
  5. By default all columns in the source table are selected. Clear the Use check box to remove a column from resulting buffer table. If a column is not selected, it is not displayed in the Map Inputs pane and is not updated at run time. Click Use All to reselect all the columns.
  6. Click in the heading of the column to sort the rows by the column value. For example, to sort the rows by their column name, click the Column Name heading of the column. An arrow is displayed 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.
  7. If necessary, change the settings of the Data Type, Encoding, or Nullable values. Click the field to make a new selection.
    • Data Type - Defaults to the data type for the column in the source database table. If the wanted data type for the column in the buffer table is different from the data type of the source table, select a different data type by clicking in the Data Type cell and selecting another Data Type from the list. You must select an appropriate data type for those data types that are marked UNSUPPORTED.
      Note: For the XMLTYPE data type in an Oracle database and the MONEY data types in a SQL Server database, Studio does not automatically associate the correct data type so you must manually select the appropriate data type.
      Attention: You must select a compatible data type with the source data type to avoid invalid data during run time. For example, do not select the SMALLINT data type for the buffer table if the source data type is INTEGER because during run time a large number stored in an INTEGER column is not converted correctly to the SMALLINT data type.
    • Size - Size of the column defined in the table. This size is retrieved from the database table and is not configurable.
    • Encoding - Defaults to current default encoding that was set either at the database level or the table level. If the wanted encoding for the column is different from the current default encoding, select another encoding by clicking in the Encoding cell and selecting another Encoding from the list.  
    • Nullable - Defaults to the nullable setting for the column in the source database table. The Use check box must be selected for those columns where nullable=false. A column marked nullable=false cannot not contain a null during run time.
  8. Click Reset to reset all column properties to their defaults. This also resets the Use check box for all columns.
  9. Click Refresh to update the list of columns in reference to the latest state of the source database. Recent changes to the source are updated in this pane.
  10. For the Update Rows activity only - Select the Insert row if row does not already exist check box, if during run time you want the Update Rows activity to first try and update the specified row but if the row does not exist,insert the row into the database table.  
  11. For the Update Rows activity only - if you want to save both the new values and the original values in the database, follow these steps:
    1. Click Old Values. The Old Value Columns for Updates dialog box is displayed.
    2. Select the Store check box for the database columns that you want to save the original values.
    3. You can change the default names assigned to columns that store the original values during run time. Double-click in the appropriate Column Name to Store Old Value cell.
    4. Click OK.  The selected columns are displayed in the To Activity of the Map Outputs pane prefixed with the string: old_. You can map to both the new and original values from the Map Outputs pane.  
    Note: For more information see Defining Columns for Old Values in Updates.
  12. For the Update Rows or Delete Rows activities only - To specify acting primary key columns in addition to any primary key columns already defined by the database, follow these steps:  
    1. Click Keys. The Configure Primary Keys dialog box is displayed.
    2. To specify a column as an acting primary key column, click the column then click >>.
    3. Click OK.
    Note: If no primary keys columns (as defined by the database) were specified in the Pick Table task of the Checklist, you must specify at least one column as an acting primary key column.
    Repeat this step to specify more acting primary key columns.
  13. Configure the Specifying Delivery Rules for Database Activities.
    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 persistence.
  14. Configure the retry options. For more information, see Retry Options For Outbound Database Activities.

Map the input of the activity

  1. Select the Map Inputs task in the Checklist.
    The input parameters for the activity are displayed under the row element in the To Activity pane. The input parameters displayed in the From Activity pane correspond to the columns you selected in the Pick Table task. If the Batch Multiple Rows Per Message check box was selected in Delivery Rules, the multiple occurrences symbol displays next to the row element.
    Note: For the Get Updated Rows activity only, if columns were selected in the Old Values Columns for Updates dialog box (in the Pick Table task), these additional output parameters (by default prefixed with the string: old_) also display under the rows element.  
  2. Map all the input parameters. See Creating a map for general instructions on mapping. You must map all the input parameters of the activity.

Map the output of the activity

  1. Select the Map Outputs task in the Checklist.
    The GetRowsCount check box determines what is displayed in the Map Outputs pane:
    • If the Get Row Counts check box is cleared in the Delivery Rules, the Map Outputs pane is blank.
    • If the Get Row Counts check box is selected in the Delivery Rules, the Map Outputs pane displays the rowCount element.  If the Batch Multiple Rows Per Message check box is selected in the Delivery Rules, the multiple occurrences symbol is displayed next to the rowCount element.
  2. If the RowCount output parameter is displayed, map the RowCount output parameter if wanted. See Creating a map for general instructions on mapping. You are not required to map the output parameters of the activity.  



Feedback | Notices


Timestamp icon Last updated: Wednesday, 15 June 2016


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