Database Adapter (Publish & Subscribe)- BW 5.x

Database Adapter is basically used for enabling communication between TIBCO and DB (Oracle, MySQL..etc) system. 

Below are the two main services are being used in Adapter.

Publication Service

Adapter Publication service is used to retrieve data(changed rows) from a database table and publishes them on given subject names to RV or JMS, which are then subscribed by adapter subscriber(process).

Subscription Service

Subscription service of a database adapter is used to retrieve data from queue/topics and then process further based on business requirement like send it to File System or Salesforce or another DB (in case of data sync)etc.

Here, I am going to explain how to publish modified records from DB to JMS/RV and then how to process those records by using subscription service.

Create DB table in DB, here I used MySQL.

Next, create DB Adapter..

Vendor : MYSQL, if you're using Oracle or something..select accordingly

Design Time Connection:

Specify JDBC driver, URL (host, port) and DB user and password. And, then Test the connection. No changes are required in other tabs for this simple example. But, in real-time go thru Tibco documentation and make necessary changes based on your business requirement.

Under Adapter Service folder, create Publication Service to publish data to RV/JMS.

In this case, I have selected JMS as transport type and Wire format is XML and Delivery Mode is Peristent.

Go to Table tab and click on Add table to select main (source table) which you have created in first step and then apply and save it so that you will see Publication Table in Publisher Options tab.

Next go to Publisher Options, you can see the table "p_emp_publish_main" gets created automatically.

Last tab, Advanced, you can see Endpoint Reference. No actions are needed from our end.

Relevant setup/configuration will be done by Tibco automatically. That's a beauty of Tibco :-)

Please ensure not to make any changes here, else it will break somewhere.

Now, test the ADB adapter service

Go to Tools> Show Adapter Tester and select the adapter which you have created just now..

Working Directory - specify any empty folder and click on Start

Go to console to see the log info..about retrieving data from Main table to Publication table.

Finally, check p_emp_publish_main table for the modified data 

ADB_L_DELIVERY_STATUS 

    N - NEW - initial status 

    F - FAILED - failed due to some reasons

    C - COMPLETED - successfully sent to JMS

    P - PENDING

That's all about Publication part.

***********************************************************************

Let's see how to build Subscription part.

It's a simple process, but in real-time you need to build logic to process the modified records.

Configure Adapter Service

Under Transport Tab>select Transport Type, Queue..etc based on your business requirement.

Output: body will have the output structure to map and process further

Start EMS server and check queues/topics.

Important point, here queues / topics are created dynamically, so * is added in front of queue/topic name.

Thanks for reading :-)