Using User Tokens to populate a heartbeat table

A vast array of user tokens exist in GoldenGate. Let’s start by looking at a common method of replicating system information to populate a heartbeat table that can be used to monitor performance. We can use the TOKENS option of the Extract TABLE parameter to define a user token and associate it with the GoldenGate environment data.

The following Extract configuration code shows the token declarations for the heartbeat table:

TABLE GGADMIN.GG_HB_OUT, &
TOKENS (
EXTGROUP = @GETENV (“GGENVIRONMENT”,”GROUPNAME”), &
EXTTIME = @DATE (“YYYY-MM-DD HH:MI:SS.FFFFFF”,”JTS”,@GETENV (“JULIANTIMESTAMP”)), &
EXTLAG = @GETENV (“LAG”,”SEC”), &
EXTSTAT_TOTAL = @GETENV (“DELTASTATS”,”DML”), &
), FILTER (@STREQ (EXTGROUP, @GETENV (“GGENVIRONMENT”,”GROUPNAME”)));

 

The EXTSTAT_TOTAL user token is particularly useful to determine the amount of DML load that is taking place against the source database with reference to the EXTLAG user token within the same replicated data stream.

For the data pump, the example Extract configuration is as follows:

TABLE GGADMIN.GG_HB_OUT, &
TOKENS (
PMPGROUP = @GETENV (“GGENVIRONMENT”,”GROUPNAME”), &
PMPTIME = @DATE (“YYYY-MM-DD HH:MI:SS.FFFFFF”,”JTS”,@GETENV(“JULIANTIMESTAMP”)), &
PMPLAG = @GETENV (“LAG”,"SEC”));

Also, for the Replicat, the following configuration populates the heartbeat table on the target database with the token data derived from Extract, data pump, and Replicat, containing system details and replication lag:

MAP GGADMIN.GG_HB_OUT_SRC, TARGET GGADMIN.GG_HB_IN_TGT, &
KEYCOLS (DB_NAME, EXTGROUP, PMPGROUP, REPGROUP), &
INSERTMISSINGUPDATES, &
COLMAP (USEDEFAULTS, &
ID = 0, &
SOURCE_COMMIT = @GETENV (“GGHEADER”, “COMMITTIMESTAMP”), &
EXTGROUP = @TOKEN (“EXTGROUP”), &
EXTTIME = @TOKEN (“EXTTIME”), &
PMPGROUP = @TOKEN (“PMPGROUP”), &
PMPTIME = @TOKEN (“PMPTIME”), &
REPGROUP = @TOKEN (“REPGROUP”), &
REPTIME = @DATE (“YYYY-MM-DD HH:MI:SS.FFFFFF”,”JTS”,@GETENV (“JULIANTIMESTAMP”)), &
EXTLAG = @TOKEN (“EXTLAG”), &
PMPLAG = @TOKEN (“PMPLAG”), &
REPLAG = @GETENV (“LAG”,”SEC”), &
EXTSTAT_TOTAL = @TOKEN (“EXTSTAT_TOTAL”));

 

As in the heartbeat table example, the defined user tokens can be called in a MAP statement using the @TOKEN function. The SOURCE_COMMIT and LAG metrics are self-explained.

As mentioned previously, EXTSTAT_TOTAL, which is derived from DELTASTATS, is particularly useful to measure the load on the source system when you evaluate latency peaks. If the user token data is copied to an audit table, normally via a trigger on the target database’s heartbeat table, the statistics can be graphed over time as illustrated in the example below.

OGG ReplicationKey

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]

Leave a Reply

Your email address will not be published. Required fields are marked *