SYSPROC.ACCEL_GET_TABLES_INFO

For all or a subset of the tables on an accelerator, this stored procedure returns the XML specifications that were used to define these tables using the SYSPROC.ACCEL_ADD_TABLES stored procedure. In addition, this stored procedure returns status information about the specified tables including accelerator-only tables.

Syntax

CALL SYSPROC.ACCEL_GET_TABLES_INFO
(accelerator_name,
table_set,
message);

Options description

accelerator_name

The unique name of the accelerator. This accelerator must have been defined by the SYSPROC.ACCEL_ADD_ACCELERATOR2 stored procedure.

table_set

A list of the tables that you want to process. The XML string that you specify as input for this parameter must conform to the structure of the tableSet element in the SAQTSAMP(AQTSXSD1) data set.

The tables must be accelerator-shadow tables that were defined by the SYSPROC.ACCEL_ADD_TABLES stored procedure, or accelerator-only tables. The same table name must not appear more than once in the table set.

You can set this parameter to NULL, in which case the stored procedure returns information about all tables that are defined in the SYSACCEL.SYSACCELERATEDTABLES table or that are present on the specified accelerator. This allows you to detect orphaned or incompletely deleted tables, which are listed with an integrity status of ViolatedOnAccelerator or ViolatedInDatabaseManagementSystem in the TABLE_INFORMATION element of the result set. You cannot restore such tables, but the rests of such tables can be cleaned up by using the ACCEL_REMOVE_TABLES stored procedure.

Note: An accelerator-shadow table that is enabled for replication by the IBM Integrated Synchronization function does not have to be removed when the original Db2 table is dropped. It is removed automatically. That is, there will not be an orphaned table on the accelerator.

Example:

<?xml version="1.0" encoding="UTF-8" ?> 
   <dwa:tableSet xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" 
    version="1.0"> 
      <table name="SALES" schema="SCHM" /> 
      <table name="PRODUCT" schema="SCHM" /> 
   </dwa:tableSet>

The sample XML code instructs the stored procedure to work on the SALES and PRODUCT tables.

message

For the description, follow the appropriate link under Related reference at the end of this topic.

Result sets

This stored procedure returns four result sets:

First result set (TABLE_INFO_SPEC_CURSOR)

The first result set contains a table specification that can be used as input for the table_specification parameter of the SYSPROC.ACCEL_ADD_TABLES stored procedure. The result set is identified by the cursor TABLE_INFO_SPEC_CURSOR. The result set can be empty if no tables exist or missing if an error occurred. It consists of the following columns:

SEQID of type INTEGER
TABLE_SPECIFICATION of type VARCHAR, with a maximum length of 32698 characters

The XML data in the TABLE_SPECIFICATION column conforms to the structure of the tableSpecifications element in the SAQTSAMP(AQTSXSD1) data set.

Second result set (TABLE_INFO_STATES_CURSOR)

The second result set contains status information about the set of tables on the accelerator. It is identified by the cursor TABLE_INFO_STATES_CURSOR. The result set can be empty if no tables exist or missing if an error occurred. It consists of the following columns:

SEQID of type INTEGER
TABLE_STATES of type VARCHAR, with a maximum length of 32698 characters

Each cell in the TABLE_STATES column contains table status information or table statistics in XML format. The information is provided in the form of XML attributes. These belong to the <status> and <statistics> elements.

The <status> element has the following attributes:

loadStatus

The value of the loadStatus is a table state. For more information, see Tables states. Consider the peculiarities of the following states in case they are output by the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure:

FederatedTableOperational: The output is slightly different, depending on the Db2 subsystem that sent the stored procedure call: The result set includes the load status if the call comes from the Db2 subsystem that owns the tables; if the call comes from the referencing Db2 subsystem, the load status is always FederatedTableOperational.
Furthermore, the output includes the location name of the remote Db2 subsystem if you call the stored procedure from the referencing Db2 subsystem. This is not the case if you call the stored procedure from the remote Db2 subsystem because the tables are local tables if viewed from the perspective of the remote Db2 subsystem.
Operational: This value is reserved for accelerator-only tables, and indicates that the table is ready for processing.
The stored procedure does not list or create output for a table if the creation of the accelerator-only table has not been committed. Likewise, the procedure might still list and produce output for a table whose removal has not been committed.

type

The value of the type can be one of the following:

AcceleratorOnly: Indicates that the table is an accelerator-only table.
FederatedReference: Indicates that the table is a referencing accelerator-only table that was created for federated access.
Regular: Indicates that the table is an accelerator-shadow table.

accelerationStatus

An that shows whether acceleration is enabled for a table. A value of 1 means enabled; 0 means disabled.

integrityStatus

An that indicates whether the table deviates structurally from its counterpart in Db2 for z/OS. To this end, the information about the table in SYSACCEL.SYSACCELERATEDTABLES is compared to that information about the same table in the catalog of the accelerator.

replicationStatus

An that shows whether incremental updates are enabled for the table. A value of 1 means enabled; 0 means disabled.

replicationDetails

Information about the currently ongoing replication process for the table. It indicates whether replication was progressing as expected at the time the stored procedure returned the results or whether errors were encountered up to that point in time.

archiveStatus

An attribute that shows whether the table has been archived by the High Performance Storage Saver on the specified accelerator or on another accelerator. The term specified accelerator refers to the accelerator that you specified as an input parameter for this stored procedure. A value of 1 means archived; a value of 0 not archived.

archiveProblemsDetected

This attribute indicates whether problems occurred while archiving the table, such as partitions that have been incompletely archived or restored. You can obtain more detailed information about the problems by running the SYSPROC.ACCEL_GET_TABLES_DETAILS stored procedure. Some problems can only be detected by SYSPROC.ACCEL_GET_TABLES_DETAILS, so a value of false does not necessarily mean that no problems exist. It just means that no problems have been detected by SYSPROC.ACCEL_GET_TABLES_INFO.

archiveSynchronizationStatus

This attribute contains a more detailed description of the archiveStatus. Its value is archiveSynchronizationStatusType, which takes on one of numerous other values detailing the archiving state. If you are interested in these values, search for archiveSynchronizationStatusType in the Transcript of the SAQTSAMP(AQTSXSD1) data set member.

The <statistics> element has the following attributes:

usedDiskSpaceInMB: The amount of disk space that is taken up by the table in MB.
skew: 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 the table data in megabytes (MB).
organizedPercent: The percentage of the table data that is organized based on the specified organizing keys. For organized tables, this value is typically 100, and 0 for tables that are not organized.
lastLoadTimestamp: Timestamp that indicates the last successful load of the table. Because accelerator-only tables are not loaded by IBM Db2 Analytics Accelerator functions, the attribute shows the table state (for example Organized) for this type of table.
archiveBackendStatisticsCollectionTimestampCollected: An attribute that indicates whether statistics were (ever) collected for an archived table. A value of 1, indicating that statistics were in fact collected, can occur only for tables that have been archived by the High Performance Storage Saver. Otherwise, the value is 0.
archiveBackendStatisticsCollectionTimestamp: A timestamp attribute that shows the last time statistics were collected for an archived table.
tableBackendStatisticsCollectionTimestampCollected: An attribute that indicates whether statistics about the table were (ever) collected. A value of 1 means yes; 0 means no.
tableBackendStatisticsCollectionTimestamp: A timestamp attribute that shows the last time statistics were collected for the table.
lastAccessTimestamp: An attribute that shows the last time the table was used (accessed) by a query. The attribute is not returned if the table has just been loaded, but not yet used by a query.
accessCount: An attribute that shows how many times the table was accessed by queries since its definition on the accelerator. The attribute is not returned if the table has just been loaded, but not yet used by a query.
capturePointTimestamp: Shows the time of when the last replication update started.
archiveDiskSpaceInMB: The amount of disk space occupied by an archived table.
archiveRowCount: The number of archived table rows.

The output XML document conforms to the structure of the tableInformation element in the SAQTSAMP(AQTSXSD1) data set.

Third result set (SP_TRACE_CURSOR)

Depending on the trace configuration in the message input parameter, this result set is empty or contains trace information about the stored procedure execution. It is identified by the cursor SP_TRACE_CURSOR and contains the following columns:

SEQID of type INTEGER
TRACEDATA of type VARBINARY, with a maximum length of 32698 characters

The information in the TRACEDATA column is encoded in UTF-8. It is intended for analysis by IBM support. If the length of a value exceeds 32698 characters, it is split into multiple result set rows. The rows are concatenated in ascending order of SEQID.

Fourth result set (MESSAGES_CURSOR)

This result set contains an XML string that contains the same messages as the MESSAGE output parameter. In contrast to the MESSAGE output parameter, the result set does not have a 64 KB size limitation. Therefore, it always contains the whole set of <message> elements (no truncation). The structure of the XML string conforms to that of the messageOutput element in the SAQTSAMP(AQTSXSD1) data set. The result set is identified by a cursor named MESSAGES_CURSOR and contains the following columns:

SEQID of type INTEGER
MESSAGES of type VARBINARY, with a maximum length of 32698 characters

The rows in the result set are concatenated in ascending order of the values in the SEQID column. The information in the MESSAGES column is encoded in UTF-8.

Example of first result set (TABLE_INFO_SPEC_CURSOR)

<?xml version="1.0" encoding="UTF-8" ?>
<aqttables:tableSpecifications xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
	version="1.0">
  <table name="CUSTOMER" schema="ABCTS1">
	
  </table>
  <table name="TIME24" schema="ABCTS1" /> <!-- sub-elements are optional -->
</aqttables:tableSpecifications>

What you see is the content or value of the table_specifications parameter that was used when you defined the table by using the SYSPROC.ACCEL_ADD_TABLES stored procedure. This example shows the original XML specifications for two tables, CUSTOMER and TIME24. Both tables belong to the ABCTS1 schema.

Example of second result set (TABLE_INFO_STATES_CURSOR)

<aqt:tableInformation xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011"    
     version="1.5">    
    <table schema="ABCTS1" name="CUSTOMER">
        <status loadStatus="Loaded" type="Regular" 
         accelerationStatus="1" integrityStatus="Unimpaired" 
         replicationStatus="0" archiveStatus="0" />
        <statistics usedDiskSpaceInMB="5" skew="0" organizedPercent="0" 
            lastLoadTimestamp="2018-03-09T08:23:19.710632Z"
            tableBackendStatisticsCollectionTimestampCollected="1"   
            archiveBackendStatisticsCollectionTimestampCollected="0" 
            tableBackendStatisticsCollectionTimestamp="2022-11-21T12:17:52.888358Z" 
            lastAccessTimestamp="2023-01-23T09:14:06.300476Z" 
            accessCount="748"
            archiveDiskSpaceInMB="0" >
            <statisticsProfile>RUNSTATS ON TABLE "ABCXYZ11"."CUSTOMER-ID_180794-V2" 
                ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED 
                DETAILED INDEXES ALL
            </statisticsProfile>
        </statistics>
    </table>

    <table schema="ABCTS1" name="TIME24">
        <status loadStatus="ReplicationInProgress" type="Regular" 
         accelerationStatus="1" integrityStatus="Unimpaired" 
         replicationStatus="1" replicationDetails="Active" 
         archiveStatus="0" />
        <statistics usedDiskSpaceInMB="8" skew="0.713489" 
         organizedPercent="0" 
         lastLoadTimestamp="2018-03-08T12:27:52.688616Z" 
         tableBackendStatisticsCollectionTimestampCollected="1"   
         archiveBackendStatisticsCollectionTimestampCollected="0" 
         tableBackendStatisticsCollectionTimestamp="2022-11-21T12:17:52.888358Z" 
         lastAccessTimestamp="2023-01-23T09:14:06.300476Z" 
         accessCount="748"
         capturePointTimestamp="2018-03-08T12:27:52.688616Z" 
         archiveDiskSpaceInMB="0" />
       </table>

   <table schema="ABCTS1" name="TEST3A">
        <status accelerationStatus="1" archiveStatus="0"
         integrityStatus="Unimpaired" 
         loadStatus="FederatedTableOperational"
         replicationStatus="0" type="FederatedReference"/>
        <statistics organizedPercent="0.000" 
         skew="0.000" usedDiskSpaceInMB="0"
         tableBackendStatisticsCollectionTimestampCollected="1"   
         archiveBackendStatisticsCollectionTimestampCollected="0" 
         tableBackendStatisticsCollectionTimestamp="2021-11-21T12:17:52.888358Z" 
         lastAccessTimestamp="2022-08-22T14:04:21.543614Z" 
         accessCount="242" />
    </table>

</dwa:tableInformation>

The example shows status information about three tables, CUSTOMER, TIME24, and TEST3A.

In the information about the CUSTOMER table, the <statisticsProfile> element contains the information that was passed in by use of the <passThrough> element in connection with the SYSPROC.ACCEL_COLLECT_STATISTICS stored procedure.

The loadStatus of the CUSTOMER table is Loaded. The acceleration status is 1, which means the table can be used for accelerated queries in its current state.

The integrity status is Unimpaired, meaning that the information about this table in the SYSACCEL.SYSACCELERATEDTABLES table in Db2 is consistent with the information about the same table in the catalog of the accelerator. Were the table not mentioned in the accelerator catalog, but marked as eligible for accelerated queries in the SYSACCEL.SYSACCELERATEDTABLES table, its integrity status would be ViolatedOnAccelerator. If, on the other hand, the table were listed on the accelerator, but not marked as eligible for accelerated queries, its status would be ViolatedInDatabaseManagementSystem. The latter can happen if rows were accidentally deleted from the SYSACCEL.SYSACCELERATEDTABLES table or if a backup of the Db2 catalog was restored that did not contain the accelerator information at the time when the backup was created.

The table uses 5 MB of disk space. The skew value of 0 indicates an even distribution of rows across the processing nodes. A day in November 2022 was the last time statistics were collected for the table. The table has not yet been archived by the High Performance Storage Saver. The last query that accessed the table was run in January 2023. In total, the table was accessed by queries 748 times.

Note: The number and the data size of High Performance Storage Saver rows are computed asynchronously so that the display of changes might be delayed for some time after the High Performance Storage Saver operations.

You can interpret the information about the TIME24 table analogously. The main difference is that this table is enabled for incremental updates. For that reason, the replicationEnabled attribute shows the value 1. The capturePointTimestamp attribute shows when the last update started.

The TEST3A table is a referencing accelerator-only table, which has been created for federated access. You can see that its load status is FederatedTableOperational and that its type is FederatedReference.

Prerequisites

For the user ID that calls the stored procedures on z/OS, you must define an OMVS segment in the Resource Access Control Facility (RACF®).

Authorizations for z/OS

On z/OS, the user ID under which this stored procedure is run must have the following privileges:

EXECUTE on the stored procedure
RACF ACCESS(READ) on the data set that contains the AQTENV file in the started task procedure of the Workload Manager (WLM) environment.
RACF ACCESS(READ) on the data set that contains the AQTDEF6 file in the started task procedure of the Workload Manager (WLM) environment.
EXECUTE on the DSNADM.DSNADMIZ package to allow access to system parameters when the SYSPROC.ADMIN_INFO_SYSPARM stored procedure is called.

Messages

The following messages are likely to occur in connection with the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure:

AQT10000I: Success message indicating that the stored procedure could provide the requested table information in the result sets.
AQT10076W: Information about tables in Db2 for z/OS and on the accelerator is out of sync. As a result, one or more tables cannot be detected on either side. To solve the problem, remove the tables in question from the accelerator and then redefine these if they still exist in the Db2 subsystem.
AQT10507W: Original tables were altered so that the column definitions in Db2 for z/OS and on the accelerator might be out of sync.
This warning is not issued if columns were dropped from a Db2 for z/OS table. Because the dropping of columns results in the deletion of column metadata from the Db2 for z/OS catalog, the change cannot be tracked anymore.
AQT10134E: The SYSPROC.ACCEL_GET_TABLES_INFO stored procedure failed because one or more of the specified tables are not found in the Db2 for z/OS catalog table SYSIBM.SYSACCELERATEDTABLES.

The following scenario shows you which messages to expect under which conditions.

Table 1. Disparate table sets in the database and on the accelerator
Database	Accelerator
T1
T2	T2
	T3

Assume that you have the following set of tables in your database and on the accelerator:

In this case, you will receive the following messages, according to the input value of the table_set parameter:

Table 2. Error messages resulting from disparate table sets
Value of `table_set`	Output returned by `table_specifications` and `table_information` for tables	Messages
NULL	T1, T2, T3	AQT10000I Warning AQT10076W for table T1, which cannot be found on the accelerator Warning AQT10076W for table T3. The table exists on the accelerator, but since it has not been specified as input (`table_set` is NULL), AQT10076W is returned instead of AQT10134E.
T1	T1	AQT10000I Warning AQT10076W because table T1 cannot be found on the accelerator
T2	T2	AQT10000I
T3	No output	Error AQT10134E for table T3, which is not found in SYSIBM.SYSACCELERATEDTABLES
T4	No output	T4 does not exist at all. In cases like this, just error message AQT10134E is issued although this only indicates that the table is not found in SYSIBM.SYSACCELERATEDTABLES.