SYSPROC.ACCEL_ALTER_TABLES

Details

• InitialLoadPending
• Error
• Loaded

In other words, do not call the procedure while a load operation or another ALTER TABLE operation is running on any of the tables in the specified set. To avoid interference with other ALTER TABLE and load operations, the SchemaModificationInProgress state is assigned to the tables in the set while SYSPROC.ACCEL_ALTER_TABLES is running.

After a successful completion, the tables are set to their previous states. If an error occurs during processing, the affected table is set to the Error state and the stored procedure returns an error message. Changes are not rolled back for already altered tables, and processing does not continue for the remaining tables. However, you can rerun the stored procedure with the same input after fixing the error.

Old keys remain valid until SYSPROC.ACCEL_ALTER_TABLES has run to completion. During an operation, the tables in the set can be queried as usual. Even concurrent queries are allowed.

The data types that can be used in distribution key columns or organizing key columns are limited. Not all data types are supported. For more information, click the appropriate Related tasks link at the end of this topic.

Syntax

CALL SYSPROC.ACCEL_ALTER_TABLES
(accelerator_name,
table_alter_specification,
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_alter_specification

Specifies an XML input document that lists the tables or partitions to be updated. Any table on the accelerator that is not listed in the input XML document will not be updated.

Not only do you have to specify the keys that you want to add or change, but also those that you want to keep. Otherwise, the existing keys are removed.

The XML content must conform to the structure of the tableSpecifications element in the SAQTSAMP(AQTSXSD1) data set. For information on how to specify the input XML string, follow the link to the element content in the appendix at the end of this topic.

Sub-elements:

<informationalUniqueKey>

For more information, see the description of the <informationalUniqueKey> element for the SYSPROC.ACCEL_ADD_TABLES stored procedure.

Important:

• It is not possible at the moment to change an informational unique constraint once it has been selected.
• If you omit an informational unique constraint for a table, although you specified it for earlier runs of the stored procedure, you actually give instructions to remove the constraint.
• To make the query optimizer use an informational unique constraint you have defined, you must also set the <useConstraintsForQueryOptimization> element.

<useConstraintsForQueryOptimization>

This sub-element enables the use of informational unique constraints by the query optimizer. This includes informational unique constraints that are not explicitly set by a user (see sub-option <informationalUniqueKey>), but are based on the primary key and other unique keys of a table in the Db2 for z/OS catalog.

If you omit the <useConstraintsForQueryOptimization> element, informational unique constraints are not used.

Important:

• If you set this option on one or more of the listed accelerator-shadow tables, you can no longer reload just a subset of the partitions of these tables (partial reload).
• If a table is replicated, and unique keys of the Db2 for z/OS table were removed from the catalog, you have to run SYSPROC.ACCEL_ALTER_TABLES to re-synchronize the Db2 for z/OS and the accelerator-shadow table. Otherwise, you might receive incorrect query results.

The use of constraints for query optimization can be switched on or off for each table listed in the <tableSpecifications> block. The value can be true or false. Example:

<?xml version="1.0" encoding="UTF-8" ?>
<aqt:tableSpecifications xmlns:aqt="http://www.ibm.com/xmlns/prod/dwa/2011" 
	version="1.0">
  <table name="SALES" schema="TBSCH1">
	<informationalUniqueKey>
           <column name="STOREID" />
           <column name="PRODUCTID" />
       </informationalUniqueKey>
       <useConstraintsForQueryOptimization>
          true
       </useConstraintsForQueryOptimization>
  </table>
</aqt:tableSpecifications>

In this example, there is only one table called SALES. Setting <useConstraintsForQueryOptimization> to true allows the query optimizer to use the primary key of that table, any other unique keys defined for the table in the Db2 for z/OS catalog, and the informational unique key for performance optimization. The informational unique key defined by the user is a combined key consisting of two columns.

message

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

Result sets

The stored procedure always returns two result sets:

First 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.

Second 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.

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®).
• The accelerator_name parameter must specify an accelerator name that is listed in the catalog tables of the product.
• The tables must exist on the accelerator that the accelerator_name parameter specifies.

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
• MONITOR1 (needed so that DSNWLIR can be used to run Db2 commands via the Db2 Instrumentation Facility Interface (IFI)).
• Authorization to run ADMIN_INFO_SYSPARM so that ZPARMs can be retrieved.
• 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.

Accessed data and systems