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.
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 themessageOutput
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.
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.
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:
Value of table_set | Output returned by table_specifications and table_information for tables | Messages |
---|---|---|
NULL | T1, T2, T3 |
|
T1 | T1 |
|
T2 | T2 |
|
T3 | No output |
|
T4 | No output |
|