Accelerator view in IBM Db2 Analytics Accelerator Studio

To show the tables on an accelerator, view the query history, or access various other functions, double-click an accelerator name in the Object List Editor, which opens the related Accelerator view.

Figure 1. Accelerator view
The image shows the Accelerator view in the Accelerator perspective of IBM Db2 Analytics Accelerator Studio. The Accelerator view provides links to other functions, a list of the tables on the accelerator that is currently displayed, and the query history for that accelerator. Descriptions of the elements in this view are given in yellow bubbles.

Status information and functional links

The header of the Accelerator view contains status information and links to related functions.

Table 1. Header information
Label Description
Acceleration Whether the accelerator is currently enabled in Db2 for z/OS. To change the current status, click Start or Stop.

If the accelerator is attached to a Db2 data sharing group, the behavior is different. The status Started is displayed if acceleration has been started for at least one member of the data sharing group. Other statuses are shown only if the status is the same for all members of the data sharing group. If the status of individual members is different, a warning icon is displayed.

If you move the mouse pointer over the displayed status or the warning icon, a pop-up window shows the acceleration status for all members of the data sharing group.

An additional functional link labeled Change is displayed for data sharing groups. Clicking it opens a window in which you can change the acceleration status of each member in the data sharing group.
Status The status of the accelerator hardware.

If the accelerator is attached to a Db2 data sharing group, you can move the mouse pointer over the status information to display the status of each member in the data sharing group.
Used space The amount of disk space that is taken up by the tables, in proportion to the available space on the accelerator.

This is the space allocated in the file system of the accelerator for the table spaces of the connected Db2 subsystem. Currently, these table spaces can only grow. So if you have removed tables from the accelerator that have used space before, then this size is a high watermark that may include free space in the table spaces for this subsystem. This free space can be reused when you add additional tables from this Db2 subsystem to the accelerator or when existing tables grow. However, the free space cannot be used by other Db2 subsystems paired with the same accelerator because these Db2 subsystems have their own table spaces. The free space can be given back to the operating system when you remove the pairing between the current Db2 subsystem and the accelerator. This action will remove the table spaces for this subsystem.
Replication Only visible if incremental updates are configured. A status of Started indicates that the incremental update process is running for tables included in the process. Stopped indicates that the incremental update process has been suspended. To change the current status, click Start or Stop.
Credentials valid since Shows the date on which the last authentication of the accelerator took place. To renew the authentication, click Update.
Encryption details If you click the Encyption details, the Encyption Details window opens, which shows information about the use of encryption for the selected accelerator. This includes the status of hard-disk encryption, the status of encryption of data in motion, a list of encrypted IPsec tunnels (IP connections), and a list of the certificates that are used.
Trace Shows the trace profiles for the accelerator and the stored procedures that are currently in use. To change a profile, click Configure. To change the settings for the saving of trace information to a file, click Save.
Active queries The number of accelerated queries that are currently being processed. In parentheses, you see the number of queued queries.
Replication latency Only visible if incremental updates are configured. This is not the case in Figure 1. The value indicates how much the incremental update function is lagging behind. Possible values:
Stopped
Incremental updates have been stopped for the entire accelerator.
Low
It will take less than three minutes to propagate recent changes to the corresponding accelerator-shadow tables.
Medium
Recent changes to the original DB2® tables will be propagated to the corresponding accelerator-shadow tables within three and six minutes.
High
It will take six minutes or longer to apply recent changes to DB2 tables to the corresponding accelerator-shadow tables.

See Note on replication latency.

When you move the mouse pointer over the latency value in IBM Db2 Analytics Accelerator Studio, a pop-up window shows the following information:
Target Agent
Operations completed by the incremental update function.
Inserts
Number of row inserts in accelerator-shadow tables that have been included in the incremental update process.
Deletes
Number of row deletions from accelerator-shadow tables that have been included in the incremental update process.
Remember:
  • An UPDATE is a DELETE followed by an INSERT, so these two figures account for all incremental updates that have been applied.
  • Since an UPDATE in DB2 is a DELETE followed by an INSERT on the accelerator side, the values can be different from those in Db2 for z/OS (see Source Agent). Furthermore, multiple operations on a single row within a short time might be reported as just one single operation.
  • If a load operation is in progress and lock mode Row has been selected, the original DB2 tables are not locked during the load. Hence it is possible to INSERT new rows into the DB2 tables even though a load is taking place. If incremental updates are enabled for the same tables, the incremental update function stores these new rows in the spill queue until the load has finished, and applies these changes thereafter (draining the spill queue). In the course of a drain, a DELETE and an INSERT operation is carried out for each row in the affected accelerator-shadow tables. The DELETE deletes the row that was inserted by the previous load operation, and the INSERT adds this row again (applies the latest change) . This has the effect that the number Deletes increases in cases like this.

Clicking Show events opens the event viewer, which keeps a record of all transactions that are related to incremental updates.
Note on replication latency: The replication latency is disclosed through different channels, at different points in time. Sometimes, it is calculated by different systems. Values are reported:
  • In the Accelerator view as described in the previous table
  • When you run the Db2 command -DIS ACCEL(<name>) DETAIL
  • In trace files:
    <Event id="61098" timestamp="2018-11-28 18:07:51" level="W" addressee="0" >
        The current replication latency of XXXXXs  on DB2 location ADB2K04 (subscription DWE9PLVY) 
        has exceeded the threshold of 3600s.
</Event>
  • In the status monitoring counter Q8STCRL
  • As a result of running the SYSPROC.ACCEL_CONTROL_ACCELERATOR stored procedure in connection with the <getAcceleratorInfo> parameter.

These systems or reporting channels are not synced, so deviating values are inevitable. Deviations can be considerable, depending the channel that is tapped and on the time when this is done. Stick to one reporting channel if you need to compare latency values.

Table 2. Header information (continued)
Label Description
Replication latency
Source Agent
Number of data changes to relevant Db2 for z/OS tables (tables for which accelerator-shadow tables have been defined and that are included in the incremental update process).
Inserts
Number of row inserts in the relevant Db2 for z/OS tables.
Updates
Number of updates to rows in the relevant Db2 for z/OS tables.
Deletes
Number of row deletions from the relevant Db2 for z/OS tables.
Latency
Repetition of the Replication latency value.

Clicking Show events opens the event viewer, which keeps a record of all transactions that are related to incremental updates.

Monitoring

The Monitoring section provides the following information:

Table 3. Information in the Monitoring section
Label Description
Queries Metrics related to queries since the start of the currently viewed accelerator, divided into the following categories:
Successful
Number of queries that ran successfully
Failed
Number of queries that failed
Queue Metrics related to queuing since the start of the currently viewed accelerator:
Avg. wait time
Average time that queries had to stay in the queue. The basis for the calculation is all queries that were processed during the last minute.
CPU Cost CPU time that was used since the start of the currently viewed accelerator, with regard to the connected Db2 subsystem. The total CPU time is divided into the following categories:
Query execution
CPU time needed for query processing
Data maintenance
CPU time needed for load and High Performance Storage Saver operations
Replication
CPU time needed for incremental updates

About

The following information is displayed in the About section:

Table 4. Information in the About section
Label Description Information blocks Description
z/OS Information about components on the z/OS data server Stored procedures The currently installed version of the IBM Db2 Analytics Accelerator stored procedures
Server Information about software components that are installed on the IBM Integrated Analytics System or as part of your Db2 Analytics Accelerator on Z Accelerator server The version of the accelerator software that is currently in use.
Level The current version of the database run by your accelerator.
Access Server Only visible if incremental updates are configured. This is the version of the CDC Access Server, the central component in the incremental update process, which manages the list of subscriptions (incremental update requests) and controls the agents involved in the process.    
Replication engine Only visible if incremental updates are configured. This is the version of the CDC Apply Agent, which propagates data changes to the IBM Integrated Analytics System, according to the instructions received from the CDC Access Server. Unicode sort order Only visible if the connected DB2 subsystem or data sharing group is a DB2 11 for z/OS database. The value reflects the sort order that was selected for tables on the accelerator. Possible values are UTF-8 binary and UTF-16 binary. The choice has an impact on how supplementary Unicode characters are sorted in query results.
Client Information about your IBM Db2 Analytics Accelerator Studio installation Studio The currently installed version of IBM Db2 Analytics Accelerator Studio. Click Check for Updates to look for a newer version.

Tables

The Tables section in the Accelerator view for contains buttons and a listing of the tables on the accelerator.
Table 5. Buttons in the Tables section
Button Description
Add Starts the Add Tables wizard.
Remove Removes the selected tables from the accelerator.
Load Opens the Load Tables window, in which you can select accelerator-shadow tables for loading and set the lock scope for the load process.
Acceleration Opens a pull-down menu, from which you can enable or disable the selected accelerator-shadow tables so that these are included or excluded from query acceleration.
Replication Only visible if incremental updates are configured. This is not the case in Figure 1. Clicking the button opens a pull-down menu from which you can enable or disable incremental updates for selected accelerator-shadow tables.
List Tasks Opens the List Tasks window, which displays the running tasks on the currently viewed accelerator, such as table load processes. To cancel a running task, select it in the list and click Cancel Selected Task.
Table 6. Icons and columns in the table of tables
Item Description
Icon representing a table schema Icon representing the schema of a table
Icon representing an accelerator-only table Icon representing an accelerator-only table
Icon representing an accelerator-shadow table Icon representing an accelerator-shadow table
Name Column containing the table names
Size Column containing the table sizes.
The table sizes displayed in the Size column are the sizes reported by the database engine of the accelerator (Db2 Warehouse). They are taken from the catalog of the database. The size is calculated as follows:
<number of pages allocated to the table> * <page size>

If rows have been deleted from the table (for example by replication or by a partial reload), a certain space on the pages might remain empty. So if you run many data maintenance jobs on a table, such as replication, without ever reloading the table fully, the table might still grow over time, even if the overall number of rows does not change.

Acceleration Column that shows whether acceleration is enabled for a table
Last Load Column that shows the timestamp of the last successful load for each table or the current table state. Because accelerator-only tables are not loaded by IBM Db2 Analytics Accelerator functions, the column always shows the table state (for example AOT ready) for this type of table.
Storage Saver Partition Column that provides information about the archived partitions of a table. The column is visible only if tables with archived partitions exist.

At the schema level, the column shows how many partitions out of the total number of partitions were archived. At the table level, it shows the compressed data size of the archived partitions and the number of rows in these partitions. If archiving is in progress for a partition, the columns shows In Progress.
Replication Since Column that shows the last incremental-update capture-point (a timestamp) for incrementally updated accelerator-shadow tables or Disabled for the tables that are not updated in that way.

The column shows Suspended when a table load has started and incremental updates are therefore on halt, and Refreshing when table changes are being applied, be this through the ongoing load process or the draining of incremental updates from the spill queue.
Distribution Key Column that shows Random if random distribution is used or the names of distribution-key-columns if a distribution key is used.
Skew Column that shows the skew value for each table on the accelerator. The skew value is a metric for the distribution imbalance of table rows across the worker nodes. The value is the difference between the smallest and the largest portion of data for a table in megabytes (MB).

Query Monitoring

The Query Monitoring section shows an extensive number of additional buttons, information columns, and other controls. For a description, see Selecting and ordering query monitoring columns.

Stored procedure call

To display or refresh the list of schemas and tables, IBM Db2 Analytics Accelerator Studio invokes the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure on your data server. For information about the privileges that are required to run this procedure and further details, follow the appropriate Related reference link at the end of this topic.