SYSPROC.ACCEL_REMOVE_TABLES

Removes (drops) accelerator-shadow tables and accelerator-archive tables, but not accelerator-only tables and deletes the corresponding entries from the IBM Db2® Analytics Accelerator catalog in Db2 for z/OS.

Details

The procedure first removes tables from the accelerator, then the corresponding entries from the SYSACCEL.SYSACCELERATEDTABLES catalog table. In case of an error, such as an only partial removal of a table, you can rerun the procedure.

If you remove a table from Db2 for z/OS, you also have to remove the corresponding accelerator-shadow table from all of your accelerators. That is, you have remove each copy of the accelerator-shadow table in a separate, manual step, either by using the removal function in your administration client, or by running this stored procedure.

The only exception are tables that are replicated by the IBM Integrated Synchronization function. In this case, the accelerator-shadow tables are removed automatically when you remove the original Db2 tables.

Attention:
  • In general, the SYSPROC.ACCEL_REMOVE_TABLES stored procedure removes tables regardless of their state. This means that running operations (table load, for example) will be interrupted and ended prematurely. Running queries will be canceled.
  • If you want to remove a table whose original data has partly or entirely been archived on an accelerator by the High Performance Storage Saver, the operation is not carried out and a warning is returned. This happens because the data on the accelerator would become inaccessible after removing the table. It is possible to override this default behavior by specifying the force="true" option in the input XML string. If you do that, you must recover the partition data manually from the Db2 for z/OS image copies. Note that the table spaces of the affected partitions might have been left in a restricted state like PRO (partition read-only). If so, you must remove the restricted states manually in Db2 for z/OS before you can insert new data into the partitions.
  • If you remove a table whose data has been archived by the High Performance Storage Saver, you also lose information about the image copies. That is, you lose information telling you in which image copies the table data resides. It might prove hard to find the proper image copy so that you can restore the data if needed. So before you remove such a table, run SYSPROC.ACCEL_GET_TABLE_DETAILS to obtain the information about the image copy. Store this information in a safe place.
  • As indicated at the beginning, this stored procedure does not remove accelerator-only tables. The reason is that after removing it from the accelerator, a table without data is left in Db2. Such a table cannot be used anymore. Therefore, you must run the Db2 DROP command to remove an accelerator-only table.

    There is one exception though: If the accelerator does not receive information about the committal of a DROP TABLE statement, an orphaned accelerator-only table remains. You can use SYSPROC.ACCEL_REMOVE_TABLES to remove such orphaned tables. To identify such tables, you can run the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure. The output of this procedure lists such tables with a type of (acceleratorOnly) and an integrity status of (ViolatedInDatabaseManagementSystem).

    If, on the contrary, an accelerator-only table is orphaned in Db2 for z/OS, you must submit a DROP TABLE statement to remove the leftovers. Such a situation can occur if a table creation has been committed, but the information flow (DRDA connection) between Db2 and the accelerator has been disrupted. In this case, the table creation on the accelerator is rolled back, leaving an orphaned table in Db2. Again, you can identify such tables by running the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure. In the output, such tables are listed with a type of (acceleratorOnly) and an integrity status of (ViolatedOnAccelerator).

  • The stored procedure always attempts to delete a table from the specified accelerator, even if it is not listed in the catalog or does not exist in Db2 for z/OS because it is the only way to remove (the rests of) tables for which the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure returns an integrity status of ViolatedInDatabaseManagementSystem.
  • The stored procedure continues with a warning if a table is not found on the accelerator or in the SYSACCEL.SYSACCELERATEDTABLES table.
  • In the (unlikely) case of an error, the stored procedure ends. Tables that have already been removed at this point cannot be restored (no rollback). Tables that were not yet processed at the time of the error are not removed. Rerun the stored procedure to remove these tables. As you do this, warnings will be issued for the tables that have already been removed before the error occurred.

Impact on federated access

To be on the safe side, remove any federated access to tables before removing the tables themselves. That is, run the following stored procedures in the order indicated or the corresponding functions in your administration client:
  1. SYSPROC.ACCEL_REMOVE_REFERENCE_TABLES
  2. SYSPROC.ACCEL_REVOKE_TABLES_REFERENCE
The implications are as follows if you remove a table, but forget to remove the federated access before:
  • Related entries are removed from the SYSIBM.SYSACCELERATEDAUTH table and existing grants are revoked.
  • You end up with a referencing accelerator-only table whose reference object (the original table) is gone. This leaves an orphaned entry in the catalog of the referencing Db2 subsystem. SQLCODE -204 (object not found) is returned when you run a query against this table. If you run the SYSPROC.ACCEL_GET_TABLES_INFO stored procedures to obtain information about tables, it will report a Violated on accelerator error for accelerator-only tables with dangling references.

Syntax

CALL SYSPROC.ACCEL_REMOVE_TABLES 
(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 <aqttables:tableSet … > or <aqttables:tableSetForRemove … > element in the SAQTSAMP(AQTSXSD1) data set. The latter allows you to specify the force="true" option.

The tables must have been defined by the SYSPROC.ACCEL_ADD_TABLES stored procedure. The same table name must not appear more than once in the table set.

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

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

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.

Messages

The following messages are likely to occur in connection with the SYSPROC.ACCEL_REMOVE_TABLES stored procedure:
AQT10076W
Is returned, along with a success message, if a table could only be found on the accelerator or in the Db2 for z/OS catalog table SYSIBM.SYSACCELERATEDTABLES.

The orphaned table or catalog entry is not removed, but processing for the other tables continues.

AQT10077W
If a table could neither be found on the accelerator, nor in the Db2 for z/OS catalog table SYSIBM.SYSACCELERATEDTABLES.

Processing for the other tables continues after such a warning.

AQT10411E
Is returned if you try to remove an accelerator-only table by using SYSPROC.ACCEL_REMOVE_TABLES.
AQT10505W
Is returned, along with a success message, if you try to remove a table archived with the High Performance Storage Saver.

The archived table is not removed, but processing for the other tables continues.

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.

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
  • DELETE on SYSACCEL.SYSACCELERATEDTABLESAUTH
  • MONITOR1 authorization
  • Read/write and execute access to the /tmp directory for the user who calls 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.

Accessed data and systems

The SYSPROC.ACCEL_REMOVE_TABLES stored procedure deletes rows from the SYSACCEL.SYSACCELERATEDTABLES catalog table in Db2 for z/OS.

On the accelerator, it updates the IBM Db2 Analytics Accelerator catalog and removes tables from the accelerator database. If the last table is removed from the accelerator, the accelerator database is also removed (dropped).