While working with TIBCO projects dealing certain database operations; we have a choice either to use JDBC activities from the JDBC Palette or we can go for Active Database Adapter based communication between our processes and database systems. Provided that, both JDBC and ADB can perform all sorts of DML operations, question arises that which one should be used under certain situations?
In this post, I am going to shed some light on both ADB Adapters and JDBC with a comparative analysis focusing on their usage, performance, strengths and weaknesses.
What is TIBCO Active Database Adapter?
TIBCO ADB adapter is a bridge between TIBCO application and database systems allowing bi-directional transfer of data between TIBCO processes and Database Systems by utilizing publication or subscription service on top of JMS or Rendezvous messaging platform.
Adapters provide a seamless and transparent integration between external applications and TIBCO environment. TIBCO ADB Adapter can be configured with various types of services for synchronous or asynchronous transfer of data.
What are JDBC Activities?
TIBCO provided a set of activities together in JDBC Palette which can be used to interact with database systems and perform various DML Operations from within TIBCO processes. These activities include JDBC Query Activity to fetch data from database tables, JDBC Update activity to perform database updates or inserts (upsert) and SQL Direct activity to dynamically create and execute a query in database.
Comparative Analysis of ADB Adapter Vs JDBC
- ADB Adapter services are Event driven which trigger based on certain events. For Example, ADB Publication Service is triggered when data changes happen in a database table which has been made a part of the publication service. Based on that event; data is published to a publishing table and then made available to TIBCO environment where It can be made available to a process using ADB Subscriber process starter.
On the other hand; JDBC is demand driven where data is fetched from database tables on demand by using JDBC activities in the processes when and where required.
- ADB Adapter is a right choice for high amount of data as adapter services can perform better for quite huge amount od database records while on the other hand; JDBC activities based process development is suitable for scenarios where data being loaded/fetched is low to medium scale. When trying to load huge number of records using JDBC activities, processing becomes quite slow and synchronous activities in the current process or subsequent process will not be executed in time which will result in overall performance degradation.
- JDBC activities are always synchronous in nature where process waits for JDBC activity to complete before proceeding to the next activity while ADB based services run separately and can be synchronous or asynchronous. Based on the transport used, data pulled from the adb based tables; can be subscribed and consumed at later stage as well. For example; for a JMS Based ADB Service, once data has been placed in the queues, ADB Subscriber based process can consume it later at anytime.
- When it comes to the setup and initial configurations; JDBC activities are comparatively easier to configure while Active Database Adapter configurations are more tricky. That’s why, for simple database operations; using plain JDBC activities is preferred (It makes no sense to use a missile when a simple gun can do the needful ).
- For ADB Adapters, you need ODBC for run-time (deployment) configurations while JDBC activities don’t require any system DSN.
- For rollbacks, transaction management and dynamic SQL queries; JDBC provides better options and is a preferred choice for such cases. On the other hand; using ADB Adapter services you Cannot implement roll-back mechanism just like JDBC transaction group.