Freeing up storage in Db2 for z/OS

The High Performance Storage Saver archives data of table partitions in Db2® for z/OS® to an accelerator. The resulting tables are called accelerator-archive tables. Moving data that is no longer actively used (historical data) to a less expensive storage device, you can free up costly storage space on your database server.

Details

The High Performance Storage Saver is a fully integrated function. Its use has the positive side-effect that it also makes the database system more responsive. This is because fewer objects need to be maintained in the catalog, smaller or fewer indexes need to be searched, reorganizations become quicker, report data and statistics can be gathered faster.

If a query could be accelerated while the original data resided in Db2, it can also be accelerated when the data has been archived to an accelerator.

Just like before, the query routing process is handled by the Db2 query optimizer and IBM® Db2 Analytics Accelerator. If data has been archived to an accelerator, and an incoming query needs to access this data, and if furthermore the query qualifies for acceleration, then the query is automatically processed by IBM Db2 Analytics Accelerator.

You can determine whether archived data is to be included in queries. By default, this type of data is excluded, and IBM Db2 Analytics Accelerator for z/OS automatically skips archived records when it processes a query. This often leads to different results for queries that are run repeatedly because the query is run just against the remaining Db2 data. If this behavior is not wanted, you can set the CURRENT GET_ACCEL_ARCHIVE special register or the GET_ACCEL_ARCHIVE configuration parameter to YES.

You can reload accelerator tables without thinking about archived partitions. Partitions that have been archived are automatically excluded from the load process.

The High Performance Storage Saver is flexible. It is still possible to define one and the same query table on multiple accelerators. It is also possible to archive the data of one or more partitions to an accelerator, while the data of other partitions remains in Db2 for z/OS and on the same or other accelerators.

Before any data is moved to an accelerator and deleted from Db2, image copies are created, which allow you to restore the data if needed. The table spaces of the affected (archived) partitions are set to a persistent read-only state, which prevents future INSERT, UPDATE, and DELETE operations on these partitions. Furthermore, the High Performance Storage Saver ensures that the data in the image copies is consistent with the data that is going to be archived.

The table space of an archived partition continues to exist. It will be empty, but will still claim as much disk space as defined by the minimum primary space allocation. To meet your space-saving goal, check the minimum primary space allocation (PRIQTY) for the table spaces of the partitions that you want to archive. Decrease the value if needed by submitting an appropriate ALTER TABLESPACE ... PRIQTY statement.

Restrictions

The following restrictions exist in connection with the High Performance Storage Saver:
  • The High Performance Storage Saver works on range-partitioned tables only. If the partitioning is controlled by an index, the index must (already) exist.
  • The smallest unit that you can archive is a partition. That is, all the table rows in specified partitions are copied to the accelerator and are finally removed from Db2.
  • You can only archive partitions of tables that exist as accelerator-shadow tables and that are in InitialLoadPending or Loaded state. This implies that partitions cannot be archived if incremental updates have been enabled for the table.
  • Table data cannot be archived if a column in the table serves as the parent in a foreign-key-relationship. The reason for this is that the original data is deleted at the end of the archiving process, which removes the values of the foreign key. This cannot be permitted because it violates a fundamental rule for data integrity in a relational database.
  • Normally, you cannot rotate a Db2 partition or change partition boundaries if the data of this partition data has been archived to an accelerator. Commands to rotate a partition or change partition boundaries will be rejected by Db2 for z/OS. This restriction can be circumvented by first removing the Partition Read-Only (PRO) state with the help of the Db2 REPAIR utility. If you rotate a partition after removing the PRO state, you are still able to recover the archived partitions by using the image copies, except for those partitions that have been "rotated out". If you change the partition boundaries, on the other hand, the image copies become unusable and the data cannot be restored. You will also end up with orphaned data on the accelerator.
    Attention: A rotation leads to the deletion of the data in those Db2 partitions that have been "rotated out". In addition, the corresponding image copies, which were created when the data was archived, will become unusable. The first archiving operation on the table that used to contain the "rotated -out" partitions will also delete the data of the "rotated-out" partitions from the accelerator, irrespective of the partitions that you specified for archiving.
  • Starting with product version 4.1 PTF-3, you can archive partitions that contain Db2 archive tables. However, you cannot archive Db2 archive-enabled tables. Do not confuse these tables with partitions and tables that were archived to an accelerator by the High Performance Storage Saver (accelerator-archive tables). If necessary, check the Db2 documentation for definitions of the terms Db2 archive table and Db2 archive-enabled table. Bear in mind also that when a Db2 archive table has been processed by the High Performance Storage Saver, you can no longer shift rows from the corresponding Db2 archive-enabled table to the Db2 archive table or vice versa. A message with SQLCODE=-904 is returned when you try to do that.
  • No failback mechanism is in place in case an error occurs. Records that have already been archived are not rolled back. The Db2 catalog explicitly marks the partitions containing these records as “archived”.
  • Columns that cannot be loaded into accelerator tables because their data types are not supported can also not be archived. The following data types lead to a failure of the archiving operation:
    • BLOB
    • CLOB
    • DBCLOB
    • XML

    The behavior is different for user-defined data types that are not supported. Trying to archive columns of such types will result in just a warning; the rest of the partition or table data will be archived.

    Although the original data is deleted after archiving, the data in unsupported columns is not lost because it is saved to the image copies.

  • If the GET_ACCEL_ARCHIVE special register (or ZPARM) has been set to the value YES, and a query addresses tables whose data has been archived to different accelerators, the query fails.
  • For an archiving operation to succeed, a shared lock is required on the involved tables or partitions. Long-running transactions, such as database queries, might prevent the lock from being obtained. Make sure that long-running transactions are finished before you start archiving.