Load Multiple Database Tables with a Single Connector Stage

Check out the video associated with this blog!

It is now possible to load multiple database tables with a single Connector stage. Let’s take a look at how to do it. First we will create a simple parallel job which has two output links from the Transformer stage going to the target DB2 Connector stage.

multiple input links connector stage parallel job

In this example, we have an input file that contains raw data which will be used to update two tables: table A and table B. The Transformer stage will perform the required business transformations and split the data going to the two target database tables. One output link from the Transformer has the data which will update table A and the other output link contains the data which will go to table B.

splitting data transformer links

The Transformer stage settings will allow us to apply the required business transformations to the raw data. Inside the Transformer we have to make sure that all the columns and the metadata has correctly been defined on the output links to match what has been defined in the target database tables.

transformer constraints split data

Now let’s take a look at the settings for the target DB2 Connector stage. Inside the Connector stage we can access all of the input links by using the shown drop down menu. For each input link (corresponding to the different target tables), we need to define the following properties:

  • SQL
  • Database Connection
  • Write Mode
  • Array Size

specify db2 connector stage property

If we are loading different tables with one Connector stage then we have to make sure our links have been ordered as per the referential integrity requirements of the table. Setting the Record ordering option in the Connector stage equal to All records will process all of the data in the first input link, then the second, and so on.

record ordering property connector stage

We can use the other Connector stages to update multiple tables as well. For example, the ODBC Connector stage can be configured to load the multiple tables via ODBC.