Db2Z and Redirected Recovery - user experience – Part 2 – Details and Cavaets
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
Links at bottom.
Caveat: Universal TableSpaces
The tablespaces involved in redirected recovery must be universal tablespaces of type partition-by-range or partition-by-growth.
- But we are in V12 now. All your tablespaces should be universal by now! You know that all new functionality for Db2 is only for universal tablespace types.
Caveat: The source and target objects must be in the same Db2.
Logically, if one thinks about it, it is impossible (or nearly impossible) for IBM to change RECOVER to redirect the recover into an object in another Db2. It would require the one utility to have access to both Db2. That is not normal. As it is, redirected recovery functionality does lots of looking into the catalog and directory to validate and update info about the objects. Also, imagine the work required by RECOVER when recovering to the RBA point (which introduces the potential for the RECOVER to read the Db2 LOG and dealing with compression dictionaries). I do not think redirected recovery will ever be enhanced for recovery into another Db2. If that is your requirement, then you might need to consider a vendor tool to assist or automate that process (such tools do exist from several vendors. I am sure they are slick and helpful)
Cavaet on how source and target objects must be physically the same:
The IBM documentation notes on running redirected outlines HOW the source and target objects must be identical. Obviously, physical dataset characteristics must be the same. And partition limits must be the same. And the logical table attributes must be the same. And the index attributes must all be the same. This is outlined by IBM in their documentation in the Table 1. Characteristics that must match in the source and target objects for redirected recovery.
- Unfortunately, from my perspective, the IBM documentation is not as clear as it could be with regards to HOW the objects need to be identical. I wish they clearly stated Db2 catalog tables and column names that need to have the same values. Then it would be “obvious” about how to compare the source and target using SQL and queries to your catalog. In my opinion, the words in the documentation were not always obvious about where I needed to go when I manually compared the objects.
- I did spend some time to create a SQL script to compare the objects between the two databases. It is possible. In my world, we run the SQL script daily in an automated job to keep track of when something surprisingly changes. If differences are noted by the job then it sends an email and fails so I am alerted to investigate. Basically, I want the daily job to tell me if something surprising happens like a partition limit change or DSSIZE change.
- In a future IDUG BLOG, I might share my SQL and JCL for comparing the objects.
Caveat: After RECOVERY, the targets are in COPY-pending
As usual for any RECOVER, you must deal with the recovered object (the target object) being in COPY-pending status. You must run COPY on the target. At the very least, you must turn off the COPY-pending flag (use REPAIR .. NOCOPYPEND … or START to force away the flag). What should you do? It depends upon your situation. Do you just constantly recover into this target? Then you might not need a COPY of the target. Will the target itself ever need recovery? Think about it.
Caveat: After RECOVERY, the targets might be in CHECK-pending
Ideally, you should RECOVER all your objects in one RECOVER statement. Or at least, all the related objects should be in one statement. BUT, if you recovered related objects in distinct RECOVER statements (the distinct RECOVER statements can be in the same job or even parallel jobs) then you will be left with target objects in CHECK-pending status. You can run CHECK DATA on the target objects. Or simply turn off the CHECK-pending flag with REPAIR or START to force away the flags. Again, the decision is up to YOU. Do you trust that they were consistent in the source database? If yes, then trust the source and get rid of that flag! Otherwise, spend the time with CHECK DATA.
Caveat: authority required for redirected recovery (not same for source and target)
The redirected recovery functionality of RECOVER has distinct authorization requirements for the source object and the target object (link to IBM documentation on RECOVER and required authority). As one would expect, you need some recover authority on the target (usually one would have RECOVERDB for the target database). On the source database object, the required authority is different!
This difference in authority requirements between source and target was not immediately obvious to me (although, I should know better). It took trial and error to realize I need proper authority on the source.
- Mere SELECT authority on the source table is not sufficient.
- You probably want to have the (newish) UNLOAD authority on the source table.
- Or you would need a “stronger” authority like DBADM on the database, DATAACCESS authority or SYSADM authority.
Minor digression:
UNLOAD privilege for the UNLOAD utility (new in v12) was introduced by IBM to separate the ability to use UNLOAD from having classic SELECT permission! UNLOAD can extract data for all columns from a table. You get the whole thing with UNLOAD. If the table has columns masks or complex row permissions, then you can sneak around how SELECT applies the rules of column masks or row permissions by using UNLOAD! And that is why UNLOAD authority is a new thing as of V12. It makes auditors happy.
- If you were not careful and unprepared by this new V12 distinction for UNLOAD then you might have been forced to set the v12 ZPARM of AUTH_COMPATIBILITY to value SELECT_FOR_UNLOAD when you went to V12 to make V12 UNLOAD behave like it always did. If you set this ZPARM to this value then you should be making a plan to use UNLOAD authority and undo the ZPARM. It will make your auditor happy.
- This V12 UNLOAD authority issue was discussed here in this IDUG Blog (by me) on UNLOAD authority and by Craig Mullens and his blog on new Priv for UNLOAD
REDIRECTED RECOVERY with LISTDEF?
When you think about it. You cannot use LISTDEF with REDIRECTED RECOVERY. You need to identify to RECOVER what are the pairs of objects. That cannot be done with a LISTDEF! It is not logical!
Is this limitation a problem? No. Think about it. You probably use REDIRECTED RECOVERY in specific scenarios. You probably build the JCL once or in few situations. So just deal with it. Besides the logical way to build target objects is probably in another database with the same object name. In that scneario, it is not a big deal to build this RECOVERY statement. In a pinch, you can run some queries against the Db2 catalog to help you build some SQL SELECT results that look like RECOVER syntax and you can tweak for your scenario.
- Personally, I really like to use SQL against catalog to build results that look Db2 commands or utility statements or LISTDEFS or whatever. I use SQL SELECT with generous amounts of CHAR and concatenation to produce nice looking result sets. The SQL is run through DSNTIAUL and the result output is then tweaked or just sent directly to the next step.
Is redirected recovery available via new function level?
NO. Redirected recovery is not available via new function level. It is available immediately after the APAR is applied. Easy!
In my over-simplification, function level controls the changes to Db2 that have the potential to impact how Db2 behaves for applications. The obvious first example is when IBM added my favorite new SQL function of LISTAGG, it only comes after FL501 is activated. You can apply the APAR for LISTAGG, but it is only available for use when FL501 (and of course, the application compatibility of the program/package executing the LISTAGG needs to FL501 or higher). This allows you to refresh your Db2 software regularly with patches and worry less about developers using new functionality before the software patch is confirmed and comfortable. If the software patching causes a problem, it can be backed out before the important new functionality is used.
But changes that do not impact functionality of existing Db2, like changes or enhancements to utilities, are not controlled by function level. You apply the patch, and you can do things (like redirected recovery) right away. Easy!
One can keep track of all new functions added to Db2 by keeping up to date with IBM documentation and reading the online documentation:
- link to IBM documentation on new function APARs for Db2 12
- link to IBM documentation on Db2 function levels
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
https://ibm-data-and-ai.ideas.ibm.com/ideas/DB24ZOS-I-1464
Example of JCL using DSNTIAUL to generate TORBA for later REDIRECTED RECOVERY
Is this example JCL necessary? Maybe not. But I think it is a useful example of how to use redirected recovery to a specific QUIESCE RBA of the source. This is probably required in most real life cases.
//TCLXCLRB JOB (),'GEN TORBA',CLASS=F,MSGCLASS=A,TIME=290,
//* RESTART=TEST,
// NOTIFY=&SYSUID
//*********************************************************************
//* JCL SUMMARY:
//* THIS SAMPLE JCL QUERIES SYSIBM.SYSCOPY TO FIND A RECENT RBA
//* AND PRODUCE A TORBA STATEMENT TO BE USED IN LATER
//* RECOVERY (REDIRECTED) UTILITY
//*********************************************************************
//*********************************************************************
//* STEP S05: IN THIS EXAMPLE. THIS VERY JOB WILL QUIESCE SOURCE DB
//*********************************************************************
//S05 EXEC DSNUPROC,SYSTEM=PDBC,COND=(5,LT)
//SYSIN DD *
LISTDEF LIST_THIS INCLUDE TABLESPACE DBSOURCE.ZOBJA
INCLUDE TABLESPACE DBSOURCE.ZOBJB
QUIESCE LIST LIST_THIS
//*********************************************************************
//* STEPS10: USE IBM SUPPLIED DSNTIAUL SAMPLE PROGRAM DSNTIAUL
//* TO QUERY SYSCOPY AND PRODUCE RESULT THAT LOOKS LIKE TORBA
//* > BECAUSE I KNOW MY QUIESCE JOB NAME AND WHEN AND OTHER CRITERIA
//*********************************************************************
//S10 EXEC PGM=IKJEFT01,COND=(5,LT)
//STEPLIB DD DISP=SHR,DSN=PCX.DB2.DSNLOAD -- FIND DSN COMMAND HERE
// DD DISP=SHR,DSN=PCX.DB2.RUNLIB.LOAD -- FIND DSNTIAUL HERE
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=TCL.TCLCOPY1.CL2050.P1.RBA,
// DISP=(,CATLG,DELETE),
// UNIT=PROD,SPACE=(TRK,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=0)
//SYSPUNCH DD DUMMY -- NO NEED TO LOOK AT THE SYSPUNCH
//SYSTSIN DD *
DSN SYSTEM(PDBC)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')
//SYSIN DD *
SELECT CHAR(' TORBA X'||''''||HEX(START_RBA)||''''||
' -- ' || JOBNAME || ' ' || CHAR(TIMESTAMP)
,80)
FROM SYSIBM.SYSCOPY
WHERE 1=1
AND DBNAME = 'DBSOURCE'
AND JOBNAME = 'TCLCOPY1'
AND ICTYPE = 'Q'
AND TIMESTAMP > (CURRENT TIMESTAMP - 99 DAYS)
ORDER BY TIMESTAMP DESC
FETCH FIRST 1 ROWS ONLY
;
//*
//*********************************************************************
//* STEP S20: FOR MY OWN SOOTHING COMFORT, DISPLAY THE PREVIOUS OUTPUT
//*********************************************************************
//SL20 EXEC PGM=IEBGENER,COND=(5,LT)
//SYSUT1 DD DISP=SHR,DSN=TCL.TCLCOPY1.CL2050.P1.RBA
//SYSUT2 DD SYSOUT=*
//*********************************************************************
//* STEP S30: REDIRECTED RECOVERY USING THE GENEREATED TORBA DATASET
//*********************************************************************
//S30 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
// DD DISP=SHR,DSN=TCL.TCLCOPY1.CL2050.P1.RBA
//*
//*********************************************************************
//* S40: FOR MY OWN INFO - DISPLAY DB STATUS AFTER REDIRECTED RECOVERY
//*********************************************************************
//S40 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
//*********************************************************************
//S45 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 TS (ALTERNATIVE IS TO DB2 COPY)
//*********************************************************************
//S48 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
//*********************************************************************