How To Build HVR Statistics Channel in Relational Format for Reporting

Question

How can I build an HVR Statistics Channel in a relational format for reporting?

Environment

HVR 5

Answer

HVR statistics is a utility that analyses HVR's log files and returns all kinds of statistical information on the replication system. You can run HVR's statistics from the GUI to produce graphical output.

You can also use it on the command line:

$ hvrstatistics -g channel -g table sfhub

Channel **hvr_demo15**

Table **dm15_tab1a**

Captured rows : 306

Integrated inserts : 100

Integrated updates : 104

Integrated deletes : 50

Integrated changes : 254

...

Capture cycles : 32

Scanned transactions : 212

Scanned rows : 612

Scanned inserts : 200

Scanned updates : 208

Scanned deletes : 100

Routed bytes : 100702

Integrate cycles : 32

Integrate transactions : 12

This document describes how to use an HVR channel that loads the output of the HVR Statistics utility from multiple hub installations (i.e. multiple hvr.out files) into a database table so that you can easily use it for further analysis.

HVR Statistics Channel

The HVR Statistics channel is a File-to-Database channel that uses an agent script that is part of the HVR distribution (HVR 4.6.1/9 and higher): hvrstatsagent.pl. This script is called as a part of the capture process and invokes the HVR Statistics utility analysis of the HVR log files and generates output in CSV file format. This CSV file is captured by HVR and replicated into a database table. An example of a channel that sources data from multiple hub installations is shown in the following screenshot.

HVR Statistics will be run in incremental mode (continuing at the point it last processed in the log file) to avoid duplication. The channel runs once an hour (although this is configurable) and the statistical data will also be grouped per hour.

Channel Setup

The channel's behavior is configured using the following actions:

• On CAP_LOG_DIR: Agent /Command =hvrstatsagent.pl Invokes the pre-capture agent to build the CSV file.
• On CAP_LOG_DIR: FileCapture /DeleteAfterCapture /Pattern =hvr_stats_*.csv Capture the CSV file generated by HVR Statistics and delete it.
• On CAP_LOG_DIR: Transform /Builtin =Csv2Xml /ArgumentValue ="-h –snone" Transforms the CSV file into HVR's native XML format.
• On INTEG_DB: DbIntegrate /Resilient Database integration with resilience to overwrite data if (parts of) the same hour is processed twice.
• On "": Scheduling /CaptureStartTimes="5 * * * *" /IntegrateStartAfterCapture runs capture 5 minutes after the end of each hour. This is chosen to avoid conflicts with running hvrmaint jobs which may be archiving HVR log files and 'traditionally' run at the end of the hour.

Steps

Use the following steps to build the channel:

1. Create the target table.

   You can create the target tables using 2 scripts (e.g. in SQL Plus or any other client) which can be found in the $HVR_HOME/demo/hvr_stats/base/oracle directory.

   $ sqlplus hvrtgt/hvr < hvr_stats.cre

   $ sqlplus hvrtgt/hvr < hvr_stats.mod

2. Import channel definition.

   Right-click the hub in the HVR GUI and import the channel definition (hvr_stats_chn.xml) into the hub database.

   

3. Create locations.

   You can create multiple source locations for the channel. These file locations need to point to the log file directories of each hub whose statistics need to be captured. These locations need to be made a member of the CAP_LOG_DIR group.

   

4. Initialize the channel.

   The HVR Initialize step is necessary to create the jobs for this channel. We still need to un-suspended the jobs. The jobs are scheduled to run 5 minutes past the end of the hour due to the configured Scheduling action.