SYSPROC.ACCEL_GET_TABLES_INFO

Example of a result set containing XML table specifications

<?xml version="1.0" encoding="UTF-8" ?>
<aqttables:tableSpecifications xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
	version="1.0">
  <table name="SALES" schema="BCKE">
	<distributionKey>
		<column name="STOREID"  />
		<column name="PRODUCTID" />
	</distributionKey>
	<organizingKey name="CUSTOMERID" />
	<organizingKey name="ORDERDATE" />
  </table>
  <table name="PRODUCT" schema="BCKE" /> <!-- 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. For the SALES table, a distribution key is selected, which consists of the STOREID and PRODUCTID columns. In addition, the CUSTOMERID and ORDERDATE columns are listed as organizing keys for the table. You can define a maximum of four columns in a distribution key, and a maximum of four organizing keys (consisting of one column each). For more information about the terms distribution key and organizing key, see Related concepts at the end of this topic..

Examples of result sets containing table status information

<?xml version="1.0" encoding="UTF-8" ?>
<dwa:tableInformation 
 xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.3">
   <table schema=”BCKE” name="SALES">
      <status loadStatus="InitialLoadPending" accelerationStatus="false" 
       integrityStatus="Unimpaired" replicationEnabled="true" />
      <statistics usedDiskSpaceInMB="1" rowCount="2" skew="0.3" 
       capturePointTimestamp="2012-01-09T11:55:27.997141Z" />
   </table>
</dwa:tableInformation>

<?xml version="1.0" encoding="UTF-8" ?>
<dwa:tableInformation 
 xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.3">
   <table schema="BCKE" name="SALES">
       <status loadStatus="Loaded" accelerationStatus="true" 
       integrityStatus="Unimpaired" 
       archiveStatus="true" archiveProblemDetected="false" />
       <statistics usedDiskSpaceInMB="1" rowCount="2" 
        archiveDiskSpaceInMB="100" 
        archiveRowCount="10000" skew="0.3" organizedPercent="95.00" 
        lastLoadTimestamp="2012-01-09T11:53:27.997141Z" />
   </table>
</dwa:tableInformation>

<?xml version="1.0" encoding="UTF-8" ?>
</dwa:tableInformation 
 xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" version="1.3">
   <table schema=”BCKE” name="PRODUCT">
      <status loadStatus="Loaded" accelerationStatus="true" 
       integrityStatus="Unimpaired" replicationEnabled="true" />
      <statistics usedDiskSpaceInMB="100" rowCount="600000" skew="0.1" 
       capturePointTimestamp="2012-01-09T11:55:27.997141Z" />
   </table>
</dwa:tableInformation>

<?xml version="1.0" encoding="UTF-8"?>
<dwa:TTableInformation xmlns:dwa="http://www.ibm.com/xmlns/prod/dwa/2011" 
 name="TEST3A" schema="Db2O">
  <status accelerationStatus="true" archiveStatus="false" 
   integrityStatus="Unimpaired" 
   loadStatus="FederatedTableOperational" replicationStatus="false" 
   type="FederatedReference"/>
  <statistics organizedPercent="0.000" rowCount="0" skew="0.000" 
   usedDiskSpaceInMB="0"/>
</dwa:TTableInformation>

The example shows status information about two three tables, SALES, PRODUCT, and TEST3A.

The loadStatus of the SALES table is InitialLoadPending. The acceleration status is false because the table cannot 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 1 MB of disk space and has just two rows. The skew value of 0.3 hints at a slightly uneven distribution of rows across the processing nodes.

The number and the data size of storage saver rows are computed asynchronously so that the display of changes might be delayed for some time after the storage saver operations.

You can interpret the information about the PRODUCT table analogously.

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.

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

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: