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
A
-->loadStatus
value ofArchivingInProgress
indicates that Db2 for z/OS data is currently being archived on an accelerator. This state prevents load or update operations, incremental updates, or the changing of a distribution key or organizing keys at the same time.Accelerator-only tables can be identified by their
type
attribute, whose value is alwaysAcceleratorOnly
, and by theirloadStatus
. TheloadStatus
valueOperational
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.
Referencing accelerator-only tables that were created for federated access can also be identified by their type, which is FederatedReference. 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.
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="https://www.ibm.com/xmlns/prod/dwa/2011"
version="1.0">
<table name="CUSTOMER" schema="SPERA">
</table>
<table name="TIME24" schema="SPERA" /> <!-- 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, SALES and PRODUCT. Both tables belong to the BCKE schema.
Example of second result set (TABLE_INFO_STATES_CURSOR)
<?xml version="1.0" encoding="UTF-8" ?>
<aqt:tableInformation xmlns:aqt="https://www.ibm.com/xmlns/prod/dwa/2011"
version="1.5">
<table schema="SPERA" 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"
archiveDiskSpaceInMB="0" />
</table>
<table schema="SPERA" name="TIME242">
<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"
capturePointTimestamp="2018-03-08T12:27:52.688616Z"
archiveDiskSpaceInMB="0" />
</table>
<table schema="SPERA" name="TEST3A">
<status accelerationStatus="1" archiveStatus="false"
integrityStatus="Unimpaired"
loadStatus="FederatedTableOperational"
replicationStatus="0" type="FederatedReference"/>
<statistics organizedPercent="0.000"
skew="0.000" usedDiskSpaceInMB="0"/>
</table>
</dwa:tableInformation>
The example shows status information about three tables, CUSTOMER, TIME24, and TEST3A.
The loadStatus of the SALES 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.
The number and the data size of HPSS rows are computed asynchronously so that the display of changes might be delayed for some time after the HPSS 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 FederatedTableOperationaland 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.
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.
- 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 |
|