How To: Build an HVR Statistics Channel in a relational format for reporting
Introduction
HVR statistics is a utility that analysis HVR's log files and returns all kinds of statistical information on the replication system. HVR Statistics can be run from the HVR GUI to produce graphical output.
or it can be used 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 installation (i.e. multiple hvr.out files) into a database table so that it can be used for further analysis more easily.
Steps
Step 1] The 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 part of the capture process and invokes the HVR Statistics utility analysis the HVR log files and generates output in CSV-file format. This CSV file will be captured by HVR and replicated into a database table. An example 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.
Step 2] Channel Setup
The channels behavior is configured using 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 top of each hour. This is chosen to avoid conflicts with running HVR Maint jobs which may be archiving HVR log files and 'traditionally' run at the top of the hour
The following describes the steps needed to build the channel.
Step 3] Creating the Target Table
The target table can be created using 2 scripts (e.g. in sqlplus or any other client) which can be found in $HVR_HOME/demo/hvr_stats/base/oracle
$ sqlplus hvrtgt/hvr < hvr_stats.cre $ sqlplus hvrtgt/hvr < hvr_stats.mod
Step 4] Import Channel Definition
Import the channel definition (hvr_stats_chn.xml) into the hub database (by right-mouse clicking the hub in HVR GUI).
Step 5] Creating Locations
Multiple source locations can be created for the channel. These file locations need to point at 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.
Step 6] Running the Channel
An HVR Load step is necessary to create the jobs for this channel. The jobs still need to be un-suspended. The jobs will be scheduled to run 5 minutes past the top of the hour due to the configured Scheduling action.