ENHANCEMENT FOR REDIRECTED RECOVERY - CHECK SCHEMA ONLY

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.