IBM Support

Db2 Analytics Accelerator -Incremental Update Monitoring

How To


Summary

Incremental update feature in IBM Db2 Analytics Accelerator for z/OS allows the propagation of changes from Db2 for z/OS to an accelerator. Those changes are replicated in near-real time and there is a low latency between the source and target. There are two options for incremental updates, IBM Integrated Synchronization or IBM InfoSphere Data Replication for z/OS (CDC)
This document provides information about:
• the counters provided by an accelerator for replication monitoring
• the interfaces that can be used for replication monitoring

Steps

Incremental update monitoring counters
The following link provides the full list of Instrumentation Facility Component Identifier (IFCID) record trace blocks for the accelerator. This information is generated when a Db2 for z/OS trace is started. Monitor programs, such as Omegamon for Db2, can use the instrumentation facility interface (IFI) to request online trace information from the trace facility. IFCID counters are mostly used for batch reporting, but some of them are also monitored online, as explained in the -DIS ACCEL command below.
IFCID 002 - Accelerator Data - Subsystem/Group Perspective V4 or later
https://www.ibm.com/support/knowledgecenter/SSUSPS_5.4.0/kdp54/ko2rr/rh002out.html
IFCID 002 - Accelerator Data - Accelerator Perspective V4 or later
The following table is extracted from IFCID records and summarizes the replication of related counters. They are common for both, Integrated Synchronization and InfoSphere Data Replication for z/OS replication. If there are counters only for Integrated Synchronization or InfoSphere Data Replication for z/OS, this is mentioned in the comment column of the table.  If a comment cell is empty, this means that the counter is valid for both technologies. Counters can be used for capacity planning, utilization, or resource consumption monitoring. Each counter is classified according to one or more of these purposes and documented in the comments column.

Counter

Comment

Q8STNLRS - LOG RECORDS READ: The number of log records read by the replication capture agent for this DB2 system.

capacity planning, utilization

Q8STNLRA - LOG RECORDS READ: The number of log records read by the replication capture agents for all DB2 systems.

capacity planning, utilization

Q8STNLTS - LOG RECORDS FOR ACCELERATOR TABLES: The number of log records (read by the replication capture agent for this DB2 system) that are applicable to tables in this accelerator.

capacity planning, utilization

Q8STNLTA - LOG RECORDS FOR ACCELERATOR TABLES: The number of log records read by the replication capture agents for all DB2 systems that are applicable to tables on this accelerator.

capacity planning, utilization

Q8STNBS - LOG RECORD BYTES PROCESSED: The number of log record bytes processed by the replication capture agent for this DB2 system.

capacity planning, utilization

Q8STNBA - LOG RECORD BYTES PROCESSED:  The number of log record bytes processed by the replication capture agents for all DB2 systems.

capacity planning, utilization

Q8STNIS - INSERT ROWS FOR ACCELERATOR TABLES: The number of INSERT rows applicable to accelerator tables that were processed by the replication capture agent for this DB2 system.

capacity planning, utilization

Q8STNIA - INSERT ROWS FOR ACCELERATOR TABLES: The number of INSERT rows applicable to accelerator tables that were processed by the replication capture agents for all DB2 systems.

capacity planning, utilization

Q8STNUS - UPDATE ROWS FOR ACCELERATOR TABLES: The number of UPDATE rows applicable to accelerator tables that were processed by the replication capture agent for this DB2 system.

capacity planning, utilization

Q8STNUA - UPDATE ROWS FOR ACCELERATOR TABLES: The number of UPDATE rows applicable to accelerator tables that were processed by the replication capture agents for all DB2 systems.

capacity planning, utilization

Q8STNDS - DELETE ROWS FOR ACCELERATOR TABLES: The number of DELETE rows applicable to accelerator tables that were processed by the replication capture agent for this DB2 system.

capacity planning, utilization

Q8STNDA - DELETE ROWS FOR ACCELERATOR TABLES: The number of DELETE rows applicable to accelerator tables that were processed by the replication capture agents for all DB2 systems.

capacity planning, utilization

Q8STCRL - REPLICATION LATENCY: The current replication latency for this DB2 system. Latency is defined as the time difference between the time when the last log record was applied to the target and the current time.

utilization

Q8STCSS - The current replication state of the accelerator for this subsystem

availability

Q8STTLSC - The timestamp when the last status change of the replication state occurred. The timestamp is specified in UTC.

availability

Q8STTCCS - The total CPU cost associated to the replication apply process for this DB2 system.

only valid for CDC, resource

Q8STLRCP - CPU time used by the asynchronous log reader task of the Integrated Synchronization function on behalf of this accelerator

valid for Insync, requires Db2 z/OS APAR PH26681 (UI71042)  and V7.5.3

resource

Q8STLRZI - zIIP time used by the asynchronous log reader task of the Integrated Synchronization function on behalf of this accelerator

valid for Insync, requires Db2 z/OS APAR PH26681 (UI71042)  and V7.5.3

resource

Q8STLRZE - zIIP-eligible time used by the asynchronous log reader task of the Integrated Synchronization function on behalf of this accelerator

valid for Insync, requires Db2 z/OS APAR PH26681 (UI71042)  and V7.5.3

resource

QISTLRCP – CPU time used by all log reader tasks since the Db2 start

valid for Insync, requires Db2 z/OS APAR PH18334 (UI70139)

resource

QISTLRZI - zIIP time used by all log reader tasks since the Db2 start

valid for Insync, requires Db2 z/OS APAR PH18334 (UI70139)

resource

QISTLRZE - zIIP-eligible time executed on CP in all log reader tasks since Db2 start

valid for Insync, requires Db2 z/OS APAR PH18334 (UI70139)

resource

HTAP Counters

Q8STTDPS - total number of query requests that have been run under the Delay Protocol sent by this db2 system to the accelerator

capacity planning, utilization

Q8STEDPS - total number of query requests that have been run under the Delay Protocol sent by this db2 system to the accelerator, and that expired because the total wait time was higher than the specified WAITFORDATA value.

capacity planning, utilization

Q8STTDPA - total number of query requests that have been run under the Delay Protocol sent by all db2 systems to the accelerator

capacity planning, utilization

Q8STEDPA - total number of query requests that have been run under the Delay Protocol sent by all db2 systems to the accelerator, and that expired because the total wait time was higher than the specified WAITFORDATA value

capacity planning, utilization

Q8STVLCS - the replication velocity for this db2 system, calculated as the ratio of log reader seconds to the number of replication changes per second. A value greater than 1 means that the accelerator can currently process changes faster than they are generated in Db2 for z/OS, and is catching up. The replication latency is decreasing. A value lower than 1 means that there are currently more changes in DB2 for z/OS than the log reader can consume, thus the replication latency is increasing.

utilization

Replication monitoring interfaces
-DIS ACCEL DETAIL Command
This is one of the online monitoring interfaces. As opposed to IFCID2 reports, which are generated by monitoring tools, the -DISPLAY ACCEL command displays information about accelerators in real time. If you need to monitor the total CPU, replication latency, and replication velocity in real time, -DISPLAY ACCEL is the correct choice. Below is an extract of a monitoring output, which covers replication only.
The details of the command can be found here
https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/comref/src/tpc/db2z_cmd_displayaccel.html
 
DSNX810I  -DBZ1 DSNX8CMD DISPLAY ACCEL FOLLOWS -                      
DSNX830I  -DBZ1 DSNX8CDA                                              
ACCELERATOR                      MEMB  STATUS  REQUESTS ACTV QUED MAXQ
-------------------------------- ---- -------- -------- ---- ---- ----
IDAAZV7                          DBZ1 STARTED        37    0    0  N/A
LOCATION=IDAAZV7 HEALTHY                                              
DETAIL STATISTICS                                                     
  LEVEL  = AQT07052                                                   
  STATUS = ONLINE                                                     
  TOTAL CPU FOR REPLICATION FOR THIS DB2 SYSTEM      =      580 MS      
  CURRENT REPLICATION LATENCY FOR THIS DB2 SYSTEM    =      120 MS
  NUMBER OF SUCCESSFUL QUERY REQUESTS WITH DELAY PROTOCOL         
         FOR ALL DB2 SYSTEMS                         =        0   
  NUMBER OF EXPIRED QUERY REQUESTS WITH DELAY PROTOCOL            
         FOR ALL DB2 SYSTEMS                         =        0   
  REPLICATION VELOCITY                               =   1.000374
Q8STTCCS - The total CPU cost associated to the replication apply process for this DB2 system.
Q8STCRL - REPLICATION LATENCY: The current replication latency for this DB2 system. Latency is defined as the time difference between the time when the last log record was applied to the target and the current time.
Q8STVLCS - the replication velocity for this db2 system, calculated as the ratio of log reader seconds to the number of replication changes per second.
Monitor Db2 messages and log reader task
This section is for Integrated Synchronization only. There are new DSNI messages that report on the availability of the log reader task. If there are outages or replication errors, you need to monitor and automatically process these messages. The status information for error analysis can be found here:

DSNI090IIt means that an asynchronous task (SRB) for reading Db2 logs has started.

DSNI091I: The replication is unable to receive data changes. A resynchronization of the data might be required. An attempt is made to restart data replication.

DSNI092I: This message notifies you that an asynchronous task (SRB) for reading Db2 logs has ended. If it is ended with a status of NORMAL or TIMEOUT CAUSED, no action is required. For the termination mode ERROR CAUSED, an attempt is made to restart synchronization from the accelerator. If you are unable to successfully restart synchronization, contact IBM support.

You can also issue a -DISPLAY STATS command with the LOGREADERTASKS option. Check the DSNT788I message output for the details of the message. If multiple Integrated Synchronisation clients are connected to the same Db2 for z/OS subsystem, you might see multiple log reader SRBs in the output. For example:

DSNT788I  -SSID                              
SESSIONID        STATUS     CURR. POSITION       NUM RECS   AGE  
---------------- ---------- -------------------- ---------- ------
2BE6DF0A87BB0801 SUSP READ  00000000000188B2CD0D 12420      84s  
2BE6E6B580FE0A02 SUSP EOS   00000000000188B4DA2D 1703       7s   
******* DISPLAY OF STATS TERMINATED *********************************
DSN9022I  -DB2A DSNTDSTS 'DISPLAY STATS' NORMAL COMPLETION      
 

Document Location

Worldwide

Operating System

z/OS:All operating systems listed

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SS4LQ8","label":"Db2 Analytics Accelerator for z\/OS"},"ARM Category":[{"code":"a8m0z0000000741AAA","label":"Administration"},{"code":"a8m0z000000073IAAQ","label":"Commands"}],"ARM Case Number":"","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"5.1;7.1;7.5"}]

Document Information

Modified date:
26 January 2021

UID

ibm16405758