Archiving data with Storage Saver in IBM Db2 for z/OS Data Gate 2.1
How to reduce your Db2 for z/OS storage consumption and maintenance cost while keeping all your data queryable on a cloud replica.
By Eirini Kalogeiton, Felix Beier, Sowmya Kameswaran & Vassil Dimov
Introduction
In the previous blog posts, we described new Data Gate 2.1 functionalities. Watson Knowledge Catalog integration, Query acceleration features, and described the new Data Gate 2.1 GUI. (To learn more about IBM Db2 for z/OS Data Gate, read “What is Db2 Data Gate? Db2 Data Gate Blog Series Part 1). In this blog, we will discuss another new feature of Data Gate 2.1, the Storage Saver. In a nutshell, Storage Saver allows users to archive table partitions from Db2 for z/OS in the cloud. By “moving” data partition, storage consumption on Db2 for z/OS can be reduced and maintenance operations, such as the RUNSTATS utility, can be significantly accelerated. In case the data is required again on Db2 for z/OS at some time in the future, it can be easily restored.
Technical Details
Storage Saver allows users to archive and restore data of range-partitioned Db2 for z/OS tables to cloud storage. By doing so, partitions that are no longer frequently used, for instance, historical data, can be moved to less expensive storage in the cloud. The data partitions can be archived and restored with a few simple clicks via the Db2 Data Gate user interface. In comparison to other archiving solutions, the archived data is still queryable with excellent query performance results while the storage consumption and operational cost on Db2 for z/OS are reduced. This provides a unique opportunity for the user to use the archived data for read-only purposes.
Requirements
Archiving and restoring partitions of Db2 for z/OS to the cloud is possible through the new Data Gate 2.1 User Interface. Storage Saver in Data Gate 2.1 is available only when a Db2 Warehouse database instance is selected as a target database in the Data Gate instance provisioning dialog. Moreover, the checkbox “Use the data gate instance for query acceleration” needs to be marked, as seen below.
Provisioning a Data Gate instance with query acceleration and storage saver support
Archiving Db2 for z/OS Data to DB2 Warehouse on Cloud Pak for Data
After provisioning a Data Gate instance and a table has been added and loaded in Data Gate, users can select the table for archiving via the Table menu. Inside the menu, all tables that should be archived can be selected.
After pressing the “Archive” button, a dialog appears where all table partitions are listed. Table partitions represent the granularity of data that can be archived or restored. All partitions that should be archived from Db2 for z/OS to Db2 Warehouse on IBM Cloud Pak for Data can be selected, reviewed, and will be moved after pressing the confirmation button.
The following animation shows how users can archive table partitions on Data Gate UI. By clicking on the table, and the archive button, users can select the table partitions and archive them into Cloud storage. After this, the table partitions are not clickable, indicating that they are archived.
When partitions are archived, Db2 Data Gate will do the following things:
On Db2 for z/OS side:
- Mark the partitions as read-only in Db2 for z/OS by setting them to the Persistent Read Only (PRO) state
- Create an image copy of to-be-archived partitions as z/OS data sets
- Remove the partition data from the Db2 for z/OS tables.
Note: 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 the space-saving goal, the minimum primary space allocation can be adjusted as described here.
On Db2 Warehouse on Cloud Pak for Data side:
- Create a separate target table for storing archived partition data next to the “regular” table that stores non-archived partitions
- Copy the to-be-archived partition data from the non-archived target table to the archive table
- Create a union-all view over the archive and non-archive data tables for running queries
- Remove the archived partitions from the regular table
Running Db2 Warehouse on Cloud Pak for Data Queries on Archived Data
The following animation shows how queries can be executed on Db2 Warehouse on Cloud Pak for Data for accessing both, non-archived and archived data. It presents options to execute queries on archived data only, non-archived data only, or a combination of archived and non-archived data via the union-all view.
Running Accelerated Queries on Archived Data
When data is archived on cloud storage, Data Gate 2.1 offers the execution of accelerated queries on this data. Queries can be submitted directly on Db2 for z/OS and Data Gate will route the queries to the Db2 Warehouse on Cloud and present the results directly to the client application. This is enabled by specifying the following special register settings prior to the query statement:
SET CURRENT QUERY ACCELERATION ALL;
SET CURRENT GET_ACCEL_ARCHIVE YES;
Accelerating queries through Db2 for z/OS (source database)
Restoring Archived partitions from DB2 Warehouse on Cloud Pak for Data to DB2 for z/OS
Archived partitions of tables, can be easily restored through the Data Gate 2.1 UI. Users can select the table with the archived partitions and click on the Restore button. Then, all table partitions that are currently archived on Cloud storage will be displayed. Users can select the partitions they want to restore and click on the restore button from Data Gate UI.
Restoring archived table partitions via Data Gate
When partitions are restored, Db2 Data Gate will do the following things:
On Db2 for z/OS side:
- Restore the archived data to archived partitions from the image copies using the recover utility
- Resets the persistent read-only state of the partitions allowing write access by SQL or utilities again
On Db2 Warehouse on Cloud Pak for Data side:
- Move the archived partitions from the archived table versions to the regular table.
- If all partitions of a table have been restored: remove the archived table version as well as the archive union-all view
