Identify Records in a SQL Database that have been created or modified since the BW Process last executed

Question

I need to identify records in a database that have been updated or created since the previous execution of my BW process. My process will execute every 5 minutes, and my plan was to identify the rows by comparing the last_modified_timestamp field in the database to the system time minus 5 minutes. However, this would not take into account periods where my process could be offline/down for maintenance etc. So I was thinking that if I can just track the timestamps when my process runs, I could then compare the timestamp to that and not have to worry about periods of the process being down.

What would the proper approach be to get around this issue?

Thanks for the help! (I am new to Tibco and apologies if this is a simple question or if I am missing something fundamental)

Answer 1

not sure which version of Tibco you are using. You can try to leverage Tibco Database Adapter for your needs.

Tibco Database Adapter is used for enabling communication between TIBCO processes and database system. There are two types of services that can be used with a database adapter:

Publication Service Adapter Publication service extracts data from the changed rows of a database table and publishes them on appropriate subject names which are then subscribed by adapter subscriber process starter.

Subscription Service Subscription service of a database adapter does opposite to a publication service. When running as a subscriber, database adapter listens on a subject, receives messages and updates the relevant tables in its associated database.

Please see tutorial here: https://tutorialspedia.com/tibco-database-adapter-step-by-step-tutorial/

Adapter for Database concept document: https://docs.tibco.com/pub/activematrix-adapter-for-database/6.0.0_april_2009/adbadapter/pdf/tib_adadb_concepts.pdf