ENHANCEMENT FOR REDIRECTED RECOVERY - CHECK SCHEMA ONLY
The DB2 redirected recovery functionality allows RECOVER utility to be used to redirect the recovery of an object (the source) to another object (the target).
The source and target objects must be identical physically and logically.
The IBM documentation provides a special section under RECOVER to describe considerations for "running a redirected recovery"
https://www.ibm.com/docs/en/db2-for-zos/12?topic=recover-running-redirected-recovery
The "Table 1." provides an official list of object tyep characteristics that must match between the source and target.
As a DBA who uses "redirected recovery" functionality, I have written SQL to query and compare the objects as described in the Db2 catalog.
This provides ME (the DBA) with confidence that the objects are sufficiently identical and my "redirected recovery" will work.
It turns out that RECOVER has been enhanced by IBM for "redirected recovery" to do it's own TABLESPACE and INDEXSPACE SCHEMA comparison.
If RECOVER discovers a difference between the source and target then the difference is reported with
DSNU1566I (to say the tablesapce or indexspace is different)
DSNU1567I (to say the specific thing that is different in the object)
My request for enhancement is to add a new keyword option for RECOVER when doing redirected recovery. The keyword tells RECOVER to do schema comparison only and stop. Do NOT do the recovery into the target.
This new keyword would provide an easy way to confirm and validate that the objects are sufficiently identical for actual redirected recovery without doing the actual recovery.
If I review the RECOVER syntax
https://www.ibm.com/docs/en/db2-for-zos/12?topic=recover-syntax-options-control-statement
the section "recover-from-multi-spec" could be updated - after "torba" - to allow new optional key word of 'CHECKSCHEMAONLY'
RECOVER
TABLESPACE sourcedb.ts1 FROM sourcedb.ts1
TABLESPACE targetdb.ts2 FROM sourcedb.ts2
TORBA X'00000000448ED0347D31' CHECKSCHEMAONLY
The benefit is that RECOVER can do official schema comparison and I do not have to write or maintain my own big SQL to compare objects in the catalog (as per my interpretation of the "Table 1." described above).
For example, I have a scenario where I use redirected recovery today. At month-end I use redirected recovery to put a copy of some month-end data into another database. We can then run very long running reports in the target while the source/prod continues with normal updates.
I am paranoid that my source and target db might get out of sync during the month..
> perhaps parition limits change. or something goes multi-dataset and I did not realize. or some other thing happens after a reorg...
So I run daily SQL to compare the objects between the two db.
It would be nice to run a daily redirected RECOVER with CHECKSCHEMAONLY so I have more confidence the objects remain identical.
Another example is just when I might want to do one off redirected recovery of several objects to another db. I could use CHECKSCHEMAONLY to quickly confirm the objects are identical or not.
Of course, the implementation or keyword of CHECKSCHEMAONLY can be changed by IBM to whatever is appropriate. IBM can decide. CHECKSCHEMAONLY is just my quick idea.
Ideally, IBM would simplify the auhtoriation required for redirected recovery with CHECKSCHEMAONLY. It would be easier if one could use CHECKSCHEMAONLY without needing full recover on the target and one of the permissions for the source (such as UNLOAD or DBADM on the source)
Why? using CHECKSCHEMAONLY would not actually look at the data. one is essentially only looking at the catalog/directory for comparing object structure. I could see the scenario where the user wants to "quickly" compare objects with this functionality. They may not have authority to do the actual redirected recovery. But they want to personally run a quick job to use CHECKSCHEMAONLY and compare the objects. It would be nice if they could compare the objects (without getting a special userid or arranging a oneshot, etc)
I do not know how to best minimize or specify the authority for this CHECKSCHEMAONLY functionality. I will let IBM consider and do what is best/easiest.
Brian, We will disregard references to idea 1465.
Sincerely,
The Db2 for z/OS team
please ignore my comments inside this RFE related to RFE 1465. I now understand that RFE 1465 is not needed.
thanks
as per my comment in my related idea > 1465
https://ibm-data-and-ai.ideas.ibm.com/ideas/DB24ZOS-I-1465
If using redirected recovery with this proposed new CHECKSCHEMAONLY keyword.... allow it the user has SELECT or UNLOAD or RECOVERAUTH on the soucre AND target objects. This will make it easier for anyone to use redirected recovery wiith CHECKSCHEMAONLY and not require the "big" recoverauth on the db. That will be a bottleneck for the intended casual users of this new functionality.
This change will help in reducing time and ensuring the database structures are identical before redirected recovery is applied