Db2Z and Redirected Recovery - user experience – Part 1 – What it is and WHY and How
Db2 Z V12 introduced interesting new functionality to the “RECOVERY” utility. This new functionality is called ‘redirected recovery’ and it has great potential!
- This blog is one of a series on my experiences with redirected recovery.
- Read them all for a complete picture!
-
- Db2Z and Redirected Recovery - user experience – Part 1 – What it is and WHY and How
- What is redirected recovery?
- Why might we use it?
- How do we use it? Example JCL
- Db2Z and Redirected Recovery - user experience – Part 2 – Details and Caveats
- Thoughts and caveats on my experience of using redirected recovery
- Db2Z and Redirected Recovery - user experience – Part 3 – automatic schema comparison
- IBM introduced schema comparison to help us. It had bugs
- Db2Z and Redirected Recovery - user experience – Part 1 – What it is and WHY and How
- Please vote for my two RFE to improve redirected recovery. Links at bottom.
This new recover functionality was introduced after Db2 V12 went GA. In October 2020, IBM released APAR PH27043 which introduced the capability to use RECOVER and allow one to “recover” a tablespace into another tablespace within the same Db2. This functionality is now commonly referred to as “redirected recovery”. We can now use RECOVER to “recover” a tablespace into another tablespace to any normal point in time. We specify the recovery point as normal (for me, I generally use a QUIESCE RBA). It looks like RECOVER syntax. Easy-peasy! Simple! Cool.
Why might this new functionality of RECOVER be useful?
- We might have a question about data values in a table at a certain point in time in the past. We can now recover the tablespace into another tablespace to the desired point in time. Then we can run simple SQL against the target tablespace-table to answer our questions.
- We could use a “log analyzer” tool to review the logs to see how data values looked in the past but log analyzer can require experience to enter all the criteria to produce a useful report.
- One might have a requirement to produce regular copy of some set of production tables. For example, one might run long running (month-end) reports off a static production database (they might need to be static for whatever application reason – not for me to decide). This clone or reporting database allows the application to run “reports” that can run for as long as they need in this target database because the target tables is only used by the reporting. As a bonus, the target database provides a handy place to validate how data looked last month-end (or whatever was the target time).
- One might have new application functionality that would benefit by “testing” on a really good copy of production data. Redirected recovery now makes it relatively easy to make a copy of a set of prod tables for use in application “testing”.
In the past,
we have tried different methods for moving data from one db to another:
-
The classic “unload/load”
UNLOAD from IMAGE COPY or UNLOAD from tablespace. This method is relatively slow, but it is simple to understand and maintain.
-
- Alternatively, some people might use the commonly installed and used IBM supplied sample program DSNTIAUL. I call DSNTIAUL the “unload program” and I remind people (constantly) that DSNTIAUL is not to be confused with the “UNLOAD utility”.
-
We set up big DSN1COPY JCL
This required many jobs and many job steps, and it was tricky to keep the JCL perfect. The JCL would have all the input and output datasets as explicit DD in the JCL. DSN1COPY is relatively fast. DSN1COPY is hard to maintain. Tricky!
-
We tried “flashcopy” of the underlying Db2 datasets...
based upon an article written by Dan Lukesitch written for zJournal in September 2004 (link not available, I do have a PDF). This method worked, but again, it was tricky and as Db2 evolved over the years, some tablespace types would only “flash” with caveats. What are these caveats you ask? Since V10, I can still “flash” and “repair” to move the data into the target (as outlined by DanL). The SQL works in the target, but some Db2 utilities NOW notice the target objects are subtly wonky and then the utility complain/stop (subjectively, not a big deal if you refreshed the target regularly and don’t run utilities on the target)
Let us return to Redirected recovery. This functionality provides a new option for me to easily move data from my prod database to my reporting database!
The initial APAR PH27043 for redirected recovery only includes the capability to redirect the recovery of tablespaces. The JCL was easy. The recovery was relatively fast, but the overall process was slow because of the requirement to REBUILD INDEX in the target.
- IBM quickly recognized that redirected recovery of only tablespaces was limiting, and customers using this new functionality asked for the logical extension of the functionality to recover indexes. In hindsight, this extension is important to make this new functionality truly useful. In June 2021, IBM released APAR PH35266 to add the functionality for redirected recovery of indexes.
The “official” IBM documentation for recover syntax is your best reference on this functionality. As a general rule, I constantly re-read IBM documentation about utility syntax because it is the best and official reference. Basically, one never knows when the utility parameters change or something new shows up! I always say: it never hurts to reread the official reference documentation! (same reminder goes for SQL syntax diagrams -> always re-read that too)
- The IBM documentation has a good section on HOW to use redirected recovery is outlined in the recover subsection on redirected recovery. I suggest you read it.
- This IBM documentation is reference documentation. They tell you HOW. They do not clearly state WHY you would want to do it. I hope this blog helps you with the WHY!
And now let me oversimplify the IBM documentation and the instruction on HOW to do redirected recovery.
- The source and target objects (tablespaces and indexes) must be in the same Db2
- The source and target objects must be logically and physically identical.
- Fortunately, IBM provides documentation and spells out what must be identical between the objects (more on this in blog Part 2).
- The source must be recoverable. Basically, you should have an IMAGE COPY of the source
- You should know your recovery point in time.
- I think it is usually logical to pick a QUIESCE RBA.
- You could recover to current point in time.
- You could recover to a specific image copy dataset. I suppose one would choose to recover to a full image copy if the Db2 LOG was no longer available
-
You can recover multiple objects in one RECOVER statement. The RECOVER statement must list each individual source object with the corresponding target object. You cannot use LISTDEF (more on this in Part 2). Example of how a redirected recovery statement could look is in the example JCL below!
-
If you do not also use redirected recovery of the indexes, then you must rebuild them after recovering tablespaces (as one should expect). This might provide you with motivation to COPY indexes more often. Especially for tablespaces you want to regularly do redirected recovery. Redirect recover of indexes is usually MUCH faster than REBUILD INDEX.
-
After the RECOVER completes, you must run REPAIR CATALOG on all TABLESPACEs. Why? IBM does not clearly state. Just do it. Do you need to run REPAIR on the indexes. No!
EXAMPLE JCL for the simple case of redirected recovery!
/*********************************************************************
//* FOR MY OWN INFO - DISPLAY TARGET DB STATUS BEFORE REDIRECTED RECOVERY
//* COULD ALSO HAVE ANOTHER DISPLAY OF SOURCE DB IF I THINK IT IS USEFUL
//*********************************************************************
//DISPBFR1 EXEC PGM=IKJEFT01,COND=(5,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(PDBC)
-DIS UTIL(*)
-DIS DATABASE(DBTARGET) SPACENAM(*) CLAIMERS LIMIT(*)
-DIS DATABASE(DBTARGET) SPACENAM(*) RESTRICT LIMIT(*)
//*********************************************************************
//* REDIRECTED RECOVERY OF: UTS-PBR (3 PARTS) & UTS-PBG (PLUS INDEXES)
//*********************************************************************
//RRECOVER EXEC DSNUPROC,SYSTEM=PDBC,COND=(5,LT)
//SYSIN DD *
RECOVER
TABLESPACE DBTARGET.ZOBJA DSNUM 01 FROM DBSOURCE.ZOBJA DSNUM 01
TABLESPACE DBTARGET.ZOBJA DSNUM 02 FROM DBSOURCE.ZOBJA DSNUM 02
TABLESPACE DBTARGET.ZOBJA DSNUM 03 FROM DBSOURCE.ZOBJA DSNUM 03
TABLESPACE DBTARGET.ZOBJB FROM DBSOURCE.ZOBJB
INDEXSPACE DBTARGET.X01OBJA FROM DBSOURCE.X01OBJA
INDEXSPACE DBTARGET.X05OBJA FROM DBSOURCE.X05OBJA
INDEXSPACE DBTARGET.X01OBJB FROM DBSOURCE.X01OBJB
TORBA X'0000000048611D068961'
//*********************************************************************
//* FOR MY OWN INFO - DISPLAY DATABASE STATUS AFTER REDIRECTED RECOVERY
//*********************************************************************
//DISPAFT EXEC PGM=IKJEFT01,COND=(5,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(PDBC)
-DIS DATABASE(DBTARGET) SPACENAM(*) RESTRICT LIMIT(*)
-DIS DATABASE(DBTARGET) SPACENAM(*) LIMIT(*)
//*********************************************************************
//* AS REQUIRED – REPAIR TARGET
//*********************************************************************
//REPCATS EXEC PGM=IEFBR14
//REPCAT EXEC DSNUPROC,SYSTEM=PDBC,COND=(5,LT),LIB=PCX.DB2.DSNLOAD
//SYSIN DD *
REPAIR CATALOG TABLESPACE DBTARGET.ZOBJA
REPAIR CATALOG TABLESPACE DBTARGET.ZOBJB
//*********************************************************************
//* REMOVE COPYPEND STATUS FROM TARGET TABLESPACE (ALTERNATIVE IS TO COPY)
//*********************************************************************
//REPCOPP EXEC DSNUPROC,SYSTEM=PDBC,COND=(5,LT),LIB=PCX.DB2.DSNLOAD
//SYSIN DD *
REPAIR OBJECT
SET TABLESPACE DBTARGET.ZOBJA NOCOPYPEND
SET TABLESPACE DBTARGET.ZOBJB NOCOPYPEND
//*********************************************************************
//DISPAFT2 EXEC PGM=IKJEFT01,COND=(5,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(PDBC)
-DIS DATABASE(DBTARGET) SPACENAM(*) RESTRICT LIMIT(*)
-DIS DATABASE(DBTARGET) SPACENAM(*) LIMIT(*)
FINALLY, if you have read this far!
Please consider voting for my request-for-enhancements to IBM to improve redirected recovery:
ENHANCEMENT FOR REDIRECTED RECOVERY - CHECK SCHEMA ONLY
https://ibm-data-and-ai.ideas.ibm.com/ideas/DB24ZOS-I-1402
Modify redirected recovery to eliminate the need for repair catalog of target
Modify redirected | IBM Data and AI Ideas Portal for Customers