TIBCO ADB Adapter - Publication service Configuration

---

       Typically vendors have their own way of formatting and exposing data. Therefore, integrating the various applications across your enterprise poses significant challenges. An adapter provides a bridge between an application and your TIBCO integration environment. Adapters are responsible for making information from different applications available to other applications across an enterprise.

       TIBCO ActiveMatrix Adapter for Database allows data changes in a database to be sent as they occur to other databases (or) applications. It extends publish-subscribe and request-response technology to databases.

       TIBCO ActiveMatrix Adapter for Database Publication service is used to monitor/listens and extracts data from the changed rows of the database tables and publishes it on the specified transport(RV or EMS). This data is then available for other applications listening on these transports and on the other side, Subscription service is used to update the database tables for ESB layer changes.

To get the overview on Subscription service, follow the post on : How to configure subscription service in TIBCO ADB Adapter

       To demonstrate the Publication service, We consider the online order system, In which customer does multiple orders and all that information is captured and stored in database tables. For this, we are going to build a Publication service listens to changes happened in database table(s) and writes those information to flat files for auditing purpose.

and to explain the things, We consider Oracle database and EMS(via queue) as transport.

Follow the below steps to configure and test the publication service :

STEP-1 : Create the below two tables i.e. CUSTOMER (parent table) and ORDER_DETAILS (child table), Adapter Publication service is going to monitor the changes in these tables.

STEP-2 : Create System DSN for Oracle, which is used by ADB Adapter service at runtime. To create, Follow the earlier post on : How to create the System DSN for Oracle

STEP-3 : Click the Project folder (AdaptersTestApp/Adapter Services), then Drag the ActiveDatabase Adapter Configuration icon from the Palette panel to Design panel and fill the Configuration tab fields as follows.

• Enter instance name field -- "ADBAdapter-CustPub-Config"
• Select the database type as Oracle from the Vendor drop down list as we are using Oracle.
• Check the Write to Database on Save check box. This default option writes configuration information to the database when you save the project in TIBCO designer.

STEP-4 : Click the Design Time Connection tab and specify database connection parameters for your environment and then click on Test Connection button to verify the parameters entered are correct or not and then click on OK button to the confirmation message dialog that appears, then click on Apply button.

STEP-5 : Click the Run-time Connection tab and enter System DSN created in "STEP-2" and also check and configure "Maximum Number of Reconnect Attempts" and "interval between Reconnect Attempts" values if required (or) else leave the default values.

NOTE : TIBCO ActiveMatrix Adapter for Database uses JDBC to access your database during design time and uses ODBC to access your database during runtime.

STEP-6 : Click the Configuration tab and check the Show All Tabs checkbox, then click the General tab and update the Termination Topic details and then click Apply button.

STEP-7 : Click the Adapter Services tab and change Polling Interval under ALL Publication Services section if required. It is default to 5 seconds means adapter publication service polls publication table for every 5 seconds and click on Publish Child data checkbox.

STEP-8 : Click on Adapter Services folder and drag Publication Service from Palette panel to Design panel and in Configuration tab, select JMS from the Transport Type drop-down list, and the fields in the configuration tab will be changed.

STEP-9 : Click the Table tab, then click the Add Table icon in the toolbar. The Question dialog appears and click OK button to retrieve the tables from the database and Add Table dialog appears with the available tables, select the CUSTOMER table and click OK button.

STEP-10 : Click the Publisher Options tab and verify that P_CUSTOMER is set in the Publishing Table filed. If the value is not set, type the value in the filed and click the Apply button.

Then configuration is saved and creates Publication table (P_CUSTOMER) automatically in the database as we already check Write to Database on Save checkbox in "STEP-3". This table has CUSTOMER table columns and also added with few other columns(names prefix with 'ADB_') used by the adapter instance while monitoring the parent(CUSTOMER) table.

NOTE : FYI.. Apart from creation of Publication table (P_CUSTOMER) table, even you can observe creation of Indexes(applied on publication table) and trigger. This trigger will monitor the parent table(CUSTOMER) and for any row changes, writes data to Publication table.

STEP-11 : Click the Table tab again and expand the CUSTOMER item. With the CUSTOMER table highlighted, click the Add Child Table icon in the toolbar. The Add dialog appears, Select the ORDER_DETAILS table from the Add Table drop-down list, then click OK button.
Expand the ORDER_DETAILS table, and add the join between the CUSTOMER table and ORDER_DETAILS table by configuring UserKey and JoinTo checkboxes as shown in below diagram.

STEP-12 : Click on Advanced tab and provide the Destination name. Adapter Publication service monitors the Publication table for any row changes and for each row change, it publishes the message on this destination.

STEP-13 : Create a Business Process with Adapter Subscriber as receiver and configure it with Adapter Publication service(ADBPublisher).

Adapter Publication Service (ADBPublisher) polls the Publication table(P_CUSTOMER) and extract the data for any changed rows and publishes it on to destination (Queue : ADBAdapter-CustPub-Config.ADBPublisher). We have written a business process in such a way that Adapter Subscriber listens to it and writes the customer data into "CustomerInfo.csv" file and Order related data into "OrderInfo.csv" file and finally confirms the adapter message by using confirm activity.

STEP-14 : To test this, First start the Adapter instance by clicking Tools --> Show Adapter Tester and it opens a dialog, Configure the Run Settings --> Working Directory with <<ADB_HOME>>/bin directory and then click on Start button to run the adapter instance.

As we configured the polling interval as 5 seconds (refer STEP-7) and you can observe below, Adapter instance is polling the Publication table for every 5 seconds.

NOTE : Please note that, as part of Enterprise Archive(ear) file, Adapter service will be added to Adapter Archive and will run in separate engine/service and it won't be a part of process archive.

And also run the Business Process in test mode.

STEP-15 : Then run below Database script to create a customer with two order details.

STEP-16 : Finally you can observe that business process is triggered and data is written to two CSV files.

---