Monday, December 3, 2018

TIBCO ADB Adapter - Subscription 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.

       Publication service is used to listen the database changes and publish it on to ESB layer via a transport(RV/EMS). To get the overview on Publication service, you can check the earlier post on : How to create a Publication Service in TIBCO ADB Adapter and on the other side Subscription service updates database tables for changes in TIBCO ESB layer.

       Precisely TIBCO Application listens to the changes in ESB layer and uses ADB Adapter Subscription service to push the data to database tables via a transport(RV/EMS).

       To demonstrate the Subscription service, We consider the online order system, In which customer does multiple orders and all that information is listen and capture by ESB layer via HTTP call and calls subscription service to update it to database tables.

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

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

STEP-1 : Create the below two tables i.e. CUSTOMER_SUB (parent table) and ORDER_DETAILS_SUB (child table), Adapter Subscription service will update the data to 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-CustSub-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 check the Use Exception Table checkbox under ALL Subscription Services section.

STEP-8 : Click on Adapter Services folder and drag Subscription 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_SUB table and click OK button.

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

STEP-11 : Click the Subscriber Options tab and provide Exception table name value as CUSTOMER_SUB_EXCEPTION and then click the Apply button, then configuration is saved and creates Exception table (CUSTOMER_SUB_EXCEPTION) automatically in the database as we already check Write to Database on Save checkbox in STEP-3.

STEP-12 : Click on Advanced tab and provide the Destination name. TIBCO application writes the ESB layer changes to this destination and Adapter Subscription service read the message from this destination and writes the data to database tables.

STEP-13 : Create a Business Process with HTTP receiver(listens to Customer and Order information) and also configure Publish to Adapter activity with Adapter Subscription service(ADBSubscriber).

HTTP Receiver triggers once you receive a customer order information via HTTP call then we parse data with Parse XML activity and calls "Publish to Adapter" and sends data on to destination (EMS Queue : ADBAdapter-CustSub-Config.ADBSubscriber) and in turn subscription service listens to this data and writes to database tables.

Even we created one "SubServiceTest.process", which will create customer order information and send a HTTP Request to trigger above said HTTP receiver process.

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.

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 two Business Processes (ADBAdapterSubService.process and SubServiceTest.process) in test mode then create a job for SubServiceTest process and it internally sends customer order information via HTTP request and in turn ADBAdapterSubService process is triggered and publishes data to Adapter.

STEP-15 : Finally you can observe that customer order information is written to database tables.


Read More »

Sunday, December 2, 2018

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.


Read More »

Create DSN for an Oracle Database - TIBCO ADB Adapter


       TIBCO ADB Adapter is developed on C/C++, hence it needs System DSN/ODBC during run time. We can configure the System DSN for Oracle Database in following way :

STEP-1 : Open the windows control panel ( Start --> Control Panel) and navigate to "Administrative Tools" and then click on "Set up ODBC data sources (32-bit)" or "Set up ODBC data sources (64-bit)" based on the requirement.

             (OR)

From your local, navigate to and click on below executable file to open the ODBC Data Source Administrator.

C:\Windows\System32\odbcad32.exe   -- For 64-bit
C:\Windows\SysWOW64\odbcad32.exe   -- For 32-bit

STEP-2 : In ODBC Data Source Administrator, navigate to "System DSN" and click on "Add" button.

STEP-3 : Then select data source driver as "TIBCO 7.00 Oracle Wire Protocol" and in general tab, provide "Data Source Name" and required DB connection details like Host, Port and SID etc and then click on Test Connection button.

STEP-4 : in Next window, provide the database username and password details, to check/test the database connectivity. Once connection is successful click on OK button.

STEP-5 : Click on Apply button, Finally you can see the System DSN is created with data source name "OracleDSN".



Read More »
back to top