Using the Heartbeat Mechanism to Monitor Loader Sessions
There are a variety of methods to monitor JCC LogMiner Loader sessions to ensure they are still active and sending data. On the source OpenVMS and Rdb system, one can create a command procedure that periodically checks a list of loader sessions and restarts them as appropriate.
But how can you monitor a loader session from the target database? One method is to use the JCC LogMiner Loader Heartbeat option.
The heartbeat mechanism is sometimes needed because the Continuous LogMiner (RMU/Unload/After/Continuous) holds a lock on the AIJ file it is currently reading. This lock blocks an AIJ backup and is not released until the CLM pointer is moved to the next AIJ. For databases where AIJ backups occur at times when the database is fairly quiet, this blocking lock can be a problem. The hearbeat mechanism works by adding the table JCCLML$HEARTBEAT to the source database and periodically updating a timestamp column in the one row in in that table. This simple update transaction is written to the AIJ which allows the CLM to move its pointer to the next AIJ, thereby allowing the AIJ backup to complete.
By default, the JCC LogMiner Loader filters out updates to the JCCLML$HEARTBEAT table, but with a little work, the heartbeat timestamp can be replicated to a target database which can then be queried to tell if the source replication is still active.
The following sections describe how this can be accomplished.
Add a table to the Target DB
I added a table to the target database. Since the input will come from multiple databases, I added a column SOURCE_DB, and several date columns. These will be populated by VirtualColumns in the MapTable configuration file for the source loader sessions.
---- Create a table for the heartbeat timestamp in the target db--create table CHECK_SOURCE_HEARTBEAT (JCCLML$TIMESTAMP date vms ,SOURCE_DB CHAR(24) ,TRANSACTION_COMMIT_TIME date vms ,TRANSMISSION_DATE_TIME date vms );---- Change <your storage area> to the storage area where you would like to place-- the table.---- Add an index on the column source_db--create unique index CSH_SOURCE_DB on CHECK_SOURCE_HEARTBEAT(SOURCE_DB) type is sorted ranked node size 480 store in <your storage area> ;---- Add a storage map--create storage map CHECK_SOURCE_HEARTBEAT_MAP for CHECK_SOURCE_HEARTBEAT store in <your storage area>;commit;
Add JCCLML_HEARTBEAT to the Table configuration file
The command procedure JCC_LML_CREATE_CONTROL_FILE explicitly excludes the JCCLML_HEARTBEAT table when it generates the Table and MapTable configuration files. However, to replicate the table, it has to be defined, so I edited the following into the Table INI file for the loader session on the source system and added the following:
!! The table JCCLML$HEARTBEAT is explicitly excluded! when the Table INI file is generated, so I added it manually!Table~JCCLML$HEARTBEAT~1~~NoMapTableColumn~JCCLML$HEARTBEAT~JCCLML$TIMESTAMP~1~8~0~35~0!
The syntax is a bit obscure, but this example will work for your JCC LogMiner Loader sessions unless you have explicitly modiied the JCCLML$HEARTBEAT table.
Add Virtual Columns and JCCLML_HEARTBEAT to the MapTable Configuration File
In the MapTable INI file, I added columns to the JCCLML$HEARTBEAT table with the VirtualColumn keyword. The LOADERNAME keyword is assigned the name for this JCC_LOGMINER_LOADER session. The VirtualColumns TRANSACTION_COMMIT_TIME and TRANSMISSION_DATE_TIME allow me to identify delays in the source JCC LogMiner Loader session.
The MapTable maps the JCCLML$HEARTBEAT to the CHECK_SOURCE_HEARTBEAT table I created in the target database.
I also mapped the column LOADERNAME to the output column SOURCE_DB.
This example assumes that all loader sessions writing to the target database have different loader names.
!
! Replicate the table JCCLML$HEARTBEAT to the target DB
! as a mechanism for detecting source database failures
!
VirtualColumn~JCCLML$HEARTBEAT~LOADERNAME
VirtualColumn~JCCLML$HEARTBEAT~TRANSACTION_COMMIT_TIME
VirtualColumn~JCCLML$HEARTBEAT~TRANSMISSION_DATE_TIME
!
MapTable~JCCLML$HEARTBEAT~JCCLML$HEARTBEAT,CHECK_SOURCE_HEARTBEAT~Replicate
MapColumn~JCCLML$HEARTBEAT~JCCLML$TIMESTAMP
MapColumn~JCCLML$HEARTBEAT~LOADERNAME,SOURCE_DB
MapColumn~JCCLML$HEARTBEAT~TRANSACTION_COMMIT_TIME
MapColumn~JCCLML$HEARTBEAT~TRANSMISSION_DATE_TIME
!
MapKey~JCCLML$HEARTBEAT~SOURCE_DB
!
This example specifies “Replicate” so I will have only one instance of a row for each source database in the target database. I could have used the “Audit” qualifier here to give me a time sequence in the target databases.
Enable Heartbeat
I added the following logical name definitions to the command procedure for the JCC LogMiner Loader session:
$ define jcc_clml_heartbeat_enable 1$ define jcc_clml_heartbeat_interval 60
This sets up the heartbeat mechanism to write to the JCCLML$HEARTBEAT table in the source database every 60 seconds. Note that if you have multiple JCC LogMiner Loader sessions against a source database, heartbeat only needs to be enabled in one of the sessions.
Once this JCC LogMiner Loader session is started, the CHECK_SOURCE_HEARTBEAT table in the target database is updated every 60 seconds.
Query the Target Database
A sequence of queries against the CHECK_SOURCE_HEARTBEAT table in the target database shows that the JCC LogMiner Loader session is alive.
SQL> declare transaction read write isolation level read committed;SQL> select * from CHECK_SOURCE_HEARTBEAT;JCCLML$TIMESTAMP SOURCE_DB TRANSACTION_COMMIT_TIME TRANSMISSION_DATE_TIME3-NOV-2016 11:47:59.18 KWH_RDB 3-NOV-2016 11:47:59.29 3-NOV-2016 11:48:00.591 row selectedSQL> select * from CHECK_SOURCE_HEARTBEAT;JCCLML$TIMESTAMP SOURCE_DB TRANSACTION_COMMIT_TIME TRANSMISSION_DATE_TIME3-NOV-2016 11:51:59.17 KWH_RDB 3-NOV-2016 11:51:59.18 3-NOV-2016 11:51:59.181 row selectedSQL> select * from CHECK_SOURCE_HEARTBEAT;JCCLML$TIMESTAMP SOURCE_DB TRANSACTION_COMMIT_TIME TRANSMISSION_DATE_TIME3-NOV-2016 11:51:59.17 KWH_RDB 3-NOV-2016 11:51:59.18 3-NOV-2016 11:51:59.181 row selectedSQL>
With this data in the target database, it is reatively simple to create a process that periodically queries the CHECK_SOURCE_HEARTBEAT table and raises an alarm if a source database has not recently sent data.
Monitoring for Gaps
This example is a straight forward method to montior if a source LML session is sending data. With some simple changes, the example can be modified to keep a sequence of heart beat records which would allow you to monitor for gaps.
To support this, I made two changes.
I changed the index on the target table to add a timestamp and allow duplicates:
create index CSH_SOURCE_DB on CHECK_SOURCE_HEARTBEAT(SOURCE_DB, JCCLML$TIMESTAMP) type is sorted ranked node size 480 -- store in <your storage area> ;
And changed the MapTable to:
!VirtualColumn~JCCLML$HEARTBEAT~LOADERNAMEVirtualColumn~JCCLML$HEARTBEAT~TRANSACTION_COMMIT_TIMEVirtualColumn~JCCLML$HEARTBEAT~TRANSMISSION_DATE_TIME!MapTable~JCCLML$HEARTBEAT~JCCLML$HEARTBEAT,CHECK_SOURCE_HEARTBEAT~AuditMapColumn~JCCLML$HEARTBEAT~JCCLML$TIMESTAMPMapColumn~JCCLML$HEARTBEAT~LOADERNAME,SOURCE_DBMapColumn~JCCLML$HEARTBEAT~TRANSACTION_COMMIT_TIMEMapColumn~JCCLML$HEARTBEAT~TRANSMISSION_DATE_TIME!MapKey~JCCLML$HEARTBEAT~SOURCE_DB!
By specifying "Audit" instead of "Replicate" all updates of the JCCLML$HEARTBEAT table are turned into Inserts in the target table.
The data in the target database is now:
SQL> select * from CHECK_SOURCE_HEARTBEAT;JCCLML$TIMESTAMP SOURCE_DB TRANSACTION_COMMIT_TIME TRANSMISSION_DATE_TIME3-NOV-2016 12:03:46.63 KWH_RDB 3-NOV-2016 12:03:46.67 3-NOV-2016 12:03:47.213-NOV-2016 12:04:46.63 KWH_RDB 3-NOV-2016 12:04:46.63 3-NOV-2016 12:04:46.643-NOV-2016 12:05:46.63 KWH_RDB 3-NOV-2016 12:05:46.63 3-NOV-2016 12:05:46.643-NOV-2016 12:06:46.63 KWH_RDB 3-NOV-2016 12:06:46.63 3-NOV-2016 12:06:46.64...3-NOV-2016 13:01:46.63 KWH_RDB 3-NOV-2016 13:01:46.63 3-NOV-2016 13:01:46.643-NOV-2016 13:02:46.63 KWH_RDB 3-NOV-2016 13:02:46.63 3-NOV-2016 13:02:46.6461 rows selectedSQL>
I could now write a (somewhat complex) query that flagged cases where the gap had exceded some threshold.
Additional Comments
With this mechanism, you can control the frequency of updates using the jcc_clml_heartbeat_interval logical name. For many environment, the 60 seconds used in this example will be sufficient without adding too much overhead.