Querying columns from other tables

The XML message you want to send may depend on columns from other tables. You can query these columns from other databases for which you have configured a JDBC connection or from tables in the CDC Replication Engine for Event Server staging database.

Procedure

  1. Click Configuration > Datastores.

    Ensure that you are connected to a CDC Replication Engine for Event Server datastore.

  2. Click Configuration > Subscriptions.
  3. Select the subscription that contains the table mapping to a message destination.

    The selected subscription must use an IBM® CDC Event Server datastore as the target.

  4. Select the table mapping to work with on the Table Mappings view.
  5. Right-click and select Open Details....
  6. Click the XML Message tab.
  7. Expand the Other Tables tree.
  8. Double-click on Add Tables.
  9. Choose one of the following options:
    Add table as top level node
    Select this option when you want the other table to reside at the node level.
    Add table as a child of another table
    Select this option when you want the table to reside as the child of another table. You must add a parent table before you can add a child table.
  10. Click Next.
  11. Select the table you want to add. This can be from a database you have configured a JDBC connection for or from the CDC Replication Engine for Event Server staging database.
  12. Choose one of the following options:
    Click Next to continue with the wizard
    Directs the wizard to create the SQL statement for you based on the columns in the other table. You can modify the SQL statement in the SQL Expression editor.
    Click Finish to add the table and open the SQL editor
    Allows you to create your own SQL statement in the SQL Expression editor.
  13. If you chose the Click Finish to add the table and open the SQL editor option, click Finish. The SQL Expression Editor opens and you are required to build a valid SQL expression. When you have completed building the SQL statement, you must map this expression to an XML element or attribute in your XML document. When you start replication, the CDC Replication Engine for Event Server will retrieve data values from the table based on your query.

    Otherwise, click Next to continue build your SQL statement with the help of the wizard.

  14. Review the columns in your select statement on the SELECT Clause.
  15. If you want to add more columns, click Add.

    Specify a name for the column and the column or expression you want the CDC Replication Engine for Event Server to retrieve. You can modify the name to an alias name. With SQL, aliases can be used for column names and table names. If you specify an alias for the column name, the SELECT statement will retrieve the column and return the result with the alias name you specified.

    Select the column from the Column/Expression list box and click OK

  16. Click Next.
  17. On the WHERE Clause page, you can choose to add filters to restrict which rows are returned by the query. The where clause is optional. Omitting the WHERE clause from your SQL statement specifies that all rows are returned by the query. If you want to create a WHERE clause, click Add to enable the fields required to build your WHERE clause statement.
    Your WHERE clause can return one of the following values:
    Static values
    To build your WHERE clause so that it returns a static value, select Value from the Type list and specify the value in the Value box.
    The before image or the after image of a row
    To build your WHERE clause so that it returns the before image or the after image of the column, select Trigger from the Type list and then select either the before image or the after image of the column from the Value list.
    Also, if you want the CDC Replication Engine for Event Server to detect any missing before images or after images, then enable the If before/after image does not exist, use other image check box. For example, if you map the before image of a column to an XML element or attribute and the operation on the source database was an insert, then because there is no before image of that column, the CDC Replication Engine for Event Server inserts the after image of the column instead when you enable this check box. Also, if you map the after image of a column to XML element or attribute and the operation on the source database was a delete, then because there is no after image of a delete operation, the CDC Replication Engine for Event Server inserts the before image of that column instead.
    The column of a parent table
    To build your WHERE clause so that it returns the column of the parent table, select Parent Table Column from the Type list and then select the column name from the Value list. This option is only available if the table is added a child of a parent table.
  18. Click Next.
  19. On the GROUP BY clause page, group the results by one or more columns. The GROUP BY clause is optional. When specified, it can be used in a SELECT statement to collect data across multiple rows.
  20. Click Next.
  21. On the ORDERED BY clause page, sort the records in your result set. The ORDER BY clause is optional. You can order the result set in either ascending or descending order.
  22. Click Finish.