IBM DB2 - The Ultimate Database for Cloud, Analytics & Mobile
This is the third part of “Backup and Recovery of DB2 Analytics Accelerator data now possible”. In this section, we explore how data can be restored from a backup in one or more accelerators.
The backup itself is stored on z/OS, because the backup and recovery process for IBM DB2 Analytics Accelerator should be integrated as much as possible into existing backup and recovery processes on DB2 z/OS. A question that often arises is why backup data sets are stored on z/OS on not on the Accelerator. If a backup data set is stored on an Accelerator that goes down unexpectedly, the backup itself would be of no help in such a situation. Therefore, customers have asked to store the backup data set under the control of z/OS. This doesn’t mean that the data set itself needs to reside on z/OS DASD. It can, for example, be archived on tape or virtual tape and treated in the same way as DB2 for z/OS image copies or other backup data sets. Many z/OS installations have a virtual tape drive that is replicated to their remote disaster recovery site. Storing the Accelerator backup on replicated virtual tape device would be an ideal location for many installations.
The Accelerator backup data set contains data in DB2 for z/OS internal format. This is the same format as if you ran the UNLOAD utility and specified data to be unloaded in internal format. Besides being able to restore data to the original table where it was originally backed up, having backed up data in DB2 for z/OS internal format provides additional benefits. You can use the backup data set to load data into any other table that has the exact same column structure as the table that has been backed up. The target table for the recover process can exist in the same Accelerator, another Accelerator, in the same or in any other DB2 for z/OS subsystem. There’s no additional DBID, OBID and PSID checking from DB2 for z/OS when trying to restore a backup data set to a different table space.
Note: DB2 for z/OS internal format doesn’t allow for encoding changes. For example, a backup that was created from a table with an encoding scheme of EBCDIC can only be restored to a table that has the exact same column structure and encoding scheme. The same applies to other encoding schemes. There are not additional checks that prevent you from recovering a backup data set to a table where either the table definition or the encoding scheme doesn’t match.
Once you find yourself in a situation that you need to restored a table in an Accelerator, you have multiple options on how to complete that task. The first option is to use the ISPF interface to build and execute the job to perform the restore process. The second option is to use an existing restore job and modify the JCL to satisfy your requirements.
Restoring data in an accelerator using the ISPF interface
The IBM DB2 Analytics Accelerator Loader provides an ISPF interface with a list of available backups to select the one you want to use for the restore process.
Information about previously taken backups are stored in a DB2 for z/OS table named HLOUCOPY. The Loader provides you the ability to recover a table to the current point in time, to a timestamp end-point and to a specific backup data set that you can select. If you want to recover to a specific backup, IBM DB2 Analytics Accelerator Loader provides an ISPF interface with a list of available backups to select the one you want to use for the restore process. Figure 3-1 shows an example of the information provided when selecting a backup data set for the restore process:
Copy Created Timestamp
Cmd Data Set Name (Local Time)
--- ----------------------------> ------------------------->
Created Timestamp Copy Copy Share Unit
(UTC) Type Site Level Type Unit
-------------------------> ---- ---- --------- ---- --------
2017-05-31-11.19.29.084352 FULL LP CHANGE DASD 3390
2017-05-31-06.53.25.270087 FULL LP CHANGE DASD 3390
This information is derived from table HLOUCOPY. The table itself isn’t part of the DB2 for z/OS catalog which means that it is updateable via SQL. The table is not cleaned up automatically if a table is removed from an Accelerator or a corresponding table is dropped in DB2 for z/OS. Thus, you will need to manually delete rows that point to backup data sets no longer needed. The MODIFY utility will not remove rows from HLOUCOPY, but a simple SQL statement can be constructed to remove rows from HLOUCOPY that are older than a specified date using where CREATED_TIMESTAMP < CURRENT TIMESTAMP – 30 DAYS for example. There are other common reasons for removing rows from HLOUCOPY. For example, the table structure may have changed or the backup data set was deleted because a newer version exists.
Once a recovery point or a backup data set has been selected, required JCL can be generated via the ISPF interface. The JCL itself loads the required backup data sets to the Accelerator. An example of generated JCL is shown in example 3-1:
//HLOB0100 EXEC PGM=DSNUTILB,
//STEPLIB DD DISP=SHR,DSN=HLO.PRD0210.LOADLIB
// DD DISP=SHR,DSN=UB1A.SDSNEXIT
// DD DISP=SHR,DSN=DSN.VB10.SDSNLOAD
//ISRECAAA DD DSN= USER1.HLO1533.LP.FULL,DISP=SHR
//HLODUMMY DD DUMMY
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSIN DD *
LOAD DATA INDDN ISRECAAA
IDAA_ONLY ON accelerator-name
LOG NO NOCOPYPEND
In this example, DD statement ISRECAA points to the backup data set that is used for the recover process.
Restoring data using an existing batch job
As always, if you know exactly what you’re doing, you can skip using the ISPF interface to generate the RECOVER job and tailor an existing recover JCL as previously outlined to meet your needs.
Be aware that manually editing recovery JCL exposes you to the risk of restoring data to the wrong table. Also note that no checks are performed in terms of the encoding scheme. It is recommended to use the ISPF interface to generate the jobs to make sure that a backup data set always matches the table that you’re about to recover.
As a summary, DB2 Analytics Accelerator Loader provides a convenient way to backup and restore data on an Accelerator while extending restore capabilities to cover additional tables using the same definitions. You can now backup accelerator-only tables that contain the result of a complex and long-running data transformation or ETL process. Recreating this data after an unplanned data loss is now as simple as in DB2 for z/OS.