The Business Transaction Monitoring (BTM) capability provided in IBM Integration Bus V10.0.0.3 allows a business user to track the life-cycle of a business transaction that has been processed by multiple message flows. To do this, BTM exploits and builds on the existing message flow monitoring functionality to capture and correlate events that are published by the message flows involved in a business transaction and record this information to a database.
Business Transaction Monitoring introduces the WMB_BUSTRANS table which is described in the Advanced usages of Business Transaction Monitoring article. This table is used to associate monitoring events that have been published by message flows and stored in the existing WMB_MSGS and WMB_BINARY_DATA tables with a particular instance of a business transaction. Over time this data will build up, potentially resulting in detrimental impacts to the performance of the BTM capability. It is therefore important to perform housekeeping of this data in order to keep the data current and maintain acceptable performance.
This article provides information on the relationships between the database tables used to store Business Transaction data and provides an example of one possible approach where the tools provided by the database are used to archive data that is no longer required. The BTM capability is currently supported with DB2 and Oracle and the example provides a technique that is applicable to both these Relational Database Managements Systems (RDBMS). In some cases there are small differences between DB2 and Oracle in the queries that are used and these are highlighted where appropriate.
Tip : Before running queries to manipulate and delete data from the database tables used by IBM Integration Bus it is strongly recommended that you ensure there is an up-to-date backup from which deleted data can be restored if necessary.
It is important not to delete data for a transaction when further activity is expected on that transaction. If this is done then the transaction state will become inconsistent.
Database Tables Used By BTM
As described in Advanced usages of Business Transaction Monitoring the database tables used by BTM are:
-
WMB_MSGS – All monitoring events that were defined on the message flow nodes are recorded into this table.
-
WMB_BINARY_DATA – If you have specified that you want the payload data to be included in the recorded event, then the payload data is recorded in this table.
-
WMB_BUSTRANS – The set of events that are correlated by BTM to ascertain the state of a business transaction are recorded into this table
The following diagram shows the relationships between these tables:
Figure 1.0 – Relationships between tables used by BTM
From a Business Transaction perspective this relationship means that an event in the WMB_BUSTRANS table must have a corresponding event, matched on ‘wmb_msgkey’, in the WMB_MSGS table. However, not all events in the WMB_MSGS table are required to have a corresponding event in the WMB_BUSTRANS table. Similarly, a row in the WMB_BINARY_DATA table must have a corresponding row, matched on ‘wmb_msgkey’, in the WMB_MSGS table but not all rows in the WMB_MSGS table are required to have a matching row in the WMB_BINARY_DATA table.
Example
The example presented in this article shows an approach where CancelOrder transactions are archived from a database where the following three business transaction types have been recorded:
-
AccountUpdate
-
ReturnOrder
-
CancelOrder
An AccountUpdate transaction consists of three events, as follows:
Figure 2.0 – Example of events for an AccountUpdate transaction.
A ReturnOrder transaction consists of four events, as follows:
Figure 3.0 – Example of events for a ReturnOrder transaction.
Finally, a CancelOrder transaction consists of two events, as follows:
Figure 4.0 – Example of events for a CancelOrder transaction.
The database contains AccountUpdate and ReturnOrder transactions that were processed in December 2015. In addition to these, CancelOrder transactions exist that were recorded in December 2015 and January 2016. In this article we will archive the December 2015 CancelOrder transactions. To simplify the example, the events for the CancelOrder transactions are not configured to capture message payload data into the WMB_BINARY_DATA.
The CancelOrder Transactions Data Set
The following query was used in Oracle’s ‘sqlplus’ tool to return information about CancelOrder transactions from the WMB_BUSTRANS and WMB_MSGS table. The same query can also be used in the DB2 Command Line Processor (CLP).
select id, seq_no, event_name, to_timestamp(last_seen_timestamp,’YYYY-MM-DD HH24:MI:SS.FF’) last_seen
from wmb_bustrans, wmb_msgs
where btd_name like ‘CancelOrder’
and wmb_bustrans.wmb_msgkey=wmb_msgs.wmb_msgkey
order by last_seen, seq_no;
In this example, this query returns twenty rows for the ten CancelOrder business transactions that are stored in the database. Remember, each CancelOrder transaction has two events.
Figure 5.0 – All CancelOrder transactions
As can be seen in Figure 5.0, the database contains CancelOrder transactions for the 2nd and 3rd December 2015 and the 1st and 2nd January 2016. By including an additional comparison in the WHERE clause of this query we can retrieve only the five CancelOrder transactions that occurred in December 2015. This is achieved by asking for transactions with a last_seen_timestamp before 2016-01-01.
select id, seq_no, event_name, to_timestamp(last_seen_timestamp, ‘YYYY-MM-DD HH24:MI:SS.FF’) last_seen
from wmb_bustrans, wmb_msgs
where to_timestamp(last_seen_timestamp, ‘YYYY-MM-DD HH24:MI:SS.FF’) < to_date(‘2016-01-01’, ‘YYYY-MM-DD’) and btd_name = ‘CancelOrder’ and wmb_bustrans.wmb_msgkey=wmb_msgs.msgkey order by last_seen, seq_no
This returns the following data:
Figure 6.0 – CancelOrder transactions recorded before 01-01-2016
For the purposes of this example, these are the CancelOrder transactions that we are not expecting any further activity on and are the ones we want to remove from the ‘live’ tables and archive. There are a number of approaches that could be employed to achieve this but, in this example, queries on the WMB_BUSTRANS and WMB_MSGS tables are used to create temporary ‘archive’ tables from the result sets that are returned. The contents of the archive tables can then be exported before deleting data from both the archive and live tables to free up space.
First we create the DEC_CANCELORDER_TXNS table from the result set of CancelOrder transactions that were taken prior to ‘2016-01-01’. For Oracle, this table can be created with a CREATE TABLE AS SELECT statement like this:
create table dec_cancelorder_txns as
select * from wmb_bustrans
where to_timestamp(last_seen_timestamp,’YYYY-MM-DD HH24:MI:SS.FF’) < to_date(‘2016-01-01’, ‘YYYY-MM-DD’) and btd_name=’CancelOrder’
For DB2, the DEC_CANCELORDER_TXNS table is created first and then a separate INSERT statement is run to populate the table with the desired data, as follows:
create table dec_cancelorder_txns like wmb_bustrans
insert into dec_cancelorder_txns
(select * from wmb_bustrans where to_timestamp (LAST_SEEN_TIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS.FF’) < to_date(‘2016-01-01′,’YYYY-MM-DD’) and btd_name=’CancelOrder’)
Having created a table containing the CancelOrder business transaction data that occurred before ‘2016-01-01’ we can select the event data that those transactions are comprised of from the WMB_MSGS table and insert it into the DEC_CANCEL_ORDER_EVENTS table.
For Oracle, the following SQL can be used:
create table dec_cancelorder_events as
select * from wmb_msgs
where wmb_msgkey in
(select wmb_msgkey from dec_cancelorder_txns)
For DB2, the DEC_CANCELORDER_EVENTS temporary table is created first and then populated with the required data.
create table dec_cancelorder_events like wmb_msgs
insert into dec_cancelorder_events
(select * from wmb_msgs where wmb_msgkey in
(select wmb_msgkey from dec_cancelorder_txns))
The data from the ‘live’ tables can then be deleted. To delete the archived rows from the WMB_BUSTRANS table the following SQL can be used.
delete from wmb_bustrans
where id in
(select id from dec_cancelorder_txns)
The archived data from the WMB_MSGS table can be removed using the following SQL.
delete from wmb_msgs
where wmb_msgkey in
(select wmb_msgkey from dec_cancelorder_events)
The database export utilities such as ‘Oracle Data Pump’ or DB2’s EXPORT CLP command can then be used to unload the data from the temporary ‘archive’ tables, in this case DEC_CANCELORDER_TXNS and DEC_CANCELORDER_EVENTS, to operating system files.
Summary
This article described an approach that allows you to maintain the data recorded into an Oracle or DB2 database when using the IBM Integration Bus – Business Transaction Monitoring (BTM) capability. It showed the database tables that are used to store this data and the relationship between them. There are several strategies that could be used to archive data from the live tables but this article focused on providing an example using temporary tables and archiving BTM data based on the time that last activity was seen for a business transaction.