StarQuest Technical Documents

How to Query SQ_STATISTICS to Determine Lag Time on the Host

Last Update: 17 March 2010
Product: SQDR Plus
Version: 3.6 or later
Article ID: SQV00PL005

Abstract

The SQDR Plus software maintains a table called SQ_STATISTICS which stores statistical data related to the operation of the software. It may be useful to query this table to evaluate the performance of the software on the host database.

To illustrate, if your incremental replication groups are active but the destination tables are not in sync with the source tables, you can review the "lag time" recorded in SQ_STATISTICS to determine if the cause of the backlog occurs at the DB2 source database.

Background

The SQ_STATISTICS table captures the total "lag time" (in seconds) for each journal in hourly increments, over a 7 day period. The lag time represents the difference between the time the source table(s) received updates and the time the data is moved to the staging table(s). If the lag time is zero, no significant backups have occurred during the hour. A non-zero value indicates that the SQDR Plus log reader is lagging behind in processing changed data. If this value is relatively high, such as 1-3 hours, this could indicate that the source table receives a very large number of changes in a short period of time and may benefit from frequent baseline replications or may be better suited for snapshot replication.

Solution

The SQ_STATISTICS table contains the following fields:

COMPONENT: This is the qualified journal name.
STATNAME: Identifies the type of statistic (e.g., Lag time).
STATDATE: The date of the statistic entry.
STATHOUR: The hour of the statistic entry (e.g., 1-24).
STATTYPE: The type of the statistic (e.g., TOTAL).
INTVALUE: The value of the statistic, except for an average.
DOUBLEVALUE: The value of an average (AVG) statistic.

Using a spreadsheet application, such as Microsoft Excel, connect to the SQDR host database and create a query that imports the data into a spreadsheet worksheet. Ideally, you should limit the result set to include only the total Lag times (i.e., STATNAME='Lag time' and STATTYPE = 'TOTAL'). From there, you can easily sort the data by any criteria, such as by journal name or date.

If you would like to review the lag times over the last 7 days, perform a query such as the following:

select STATDATE, STATHOUR, STATTYPE, INTVALUE
from SQDR.SQ_STATISTICS
where COMPONENT='SQDR.SQDR' and STATNAME = 'Lag time' and STATTYPE = 'TOTAL'
group by STATDATE, STATHOUR, STATTYPE, INTVALUE

If you are attempting to identify any lag time that might have occurred on a certain date, use a query such as:

select STATDATE, STATHOUR, STATTYPE, INTVALUE
from SQDR.SQ_STATISTICS
where COMPONENT='SQDR.SQDR' and STATNAME = 'Lag time'
and STATTYPE = 'TOTAL' and STATDATE = '<YYYY-MM-DD>'
group by STATDATE, STATHOUR, STATTYPE, INTVALUE

Alternatively, view the SQ_STATISTICS data in CAMAINT by choosing Option 4 Display current statistics or Option 5 Display all statistics. The statistics reflect the activity for the last 7 days, in hourly increments. Displaying the current statistics shows all of the statistics in the current hour. For example, if you check the statistics at 10:18 AM, the statistics shown are those that were captured in the last 18 minutes of activity (between 10:00AM and 10:18AM). If you would like to view statistics that were
recorded before the current hour, use the Display all statistics option.

The SQDR Plus Documentation Addendum describes the SQ_STATISTICS table in greater detail.