Db2Z and Redirected Recovery - user experience – Part 3 – Automatic Schema Comparison
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.
As was explained in Part 1 of this blog series on redirected recovery, the initial APAR to introduce redirected recovery was APAR PH27043 for tablespaces only (it was available in 2020). This was great. But soon afterwards, the fact that redirected recovery only worked for tablespaces was recognized by everyone to be teasing and insufficient. One can recover an index, but not redirect the recovery of an index? Why not? This is a logical and a necessary extension of “redirected recovery” functionality. It was introduced by IBM in June 2021 in APAR PH35266 . This APAR adds to the redirected recovery to include indexes.
When we applied this APAR in our Db2, we could then use redirected recovery of indexes! Great!
BUT we found some amusing bugs that you might experience as well.
Somewhere along the way, perhaps in the very APAR for indexes, IBM introduced extra functionality to redirected recovery to do schema comparison before beginning the recovery. In theory, this is great and helpful. Love the idea! But it turns out the schema comparison now built into redirected recovery reported erroneous differences in subtle cases. There is a workaround. And later, IBM introduced more APAR to fix schema comparison.
It is great that redirected recovery now does schema comparison on our behalf. Before, we had to manually ensure that the source and target were identical. 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. As I mentioned in Part 2 of this blog series, one must carefully read the table to understand how the objects must be identical. And YOU must ensure that they were identical. The initial release of the redirected recovery would apparently blindly recover into the target objects and if they were wrong… then subsequent usage of the objects was likely to fail!
Erroneous reporting of tablespace difference
When Db2 started automatic schema comparison, it reported that one of our tablespaces had column DEFAULT differences
DSNU1567I - 13:26:39.07 DSNUCASV - DEFINITION OF TARGET TABLE SCHEMAT.TOBJA DIFFERS FROM SOURCE TABLE SCHEMAS.TBOJA: TABLE COLUMN 13 DEFAULT MISMATCH
But I looked and looked inside the catalog. The columns were the same according to the Db2 catalog tables! The defaults were the same.
Root cause: Basically, the redirected recovery functionality uses the DBD from the directory for the comparison. I use the Db2 catalog for my manual comparison. The source table was created before V10 and the definition of the table inside the directory DBD recorded defaults differently back in V10 compared with how they are today. The redirected recover schema comparison functionality was not aware of this subtle case and it thus reported the error.
Solution options:
- Use secret utility control statement of DIAGNOSE (8106) before the redirected recovery RECOVER statement. Basically, this DIAGNOSE statement tells the subsequent RECOVER to not do schema comparison of tablespaces!
- Apply the corrective PTF for the APAR Ph42726 which documents the problem. Basically, the fix for schema comparison was for IBM to enhance RECOVER yet again to be aware of this case and do the comparison properly.
- Use REPAIR utility of REPAIR DBD REBUILD to rebuild the DBD inside the directory which will refresh the OBDREC with the current setting.
Initially, we chose to use DIAGNOSE (8106) to tell RECOVER to NOT do tablespace schema comparison. After all, I knew that the objects were good. A few months later, we got the PTF for APAR PH42726 and we took away the DIAGNOSE, the redirected recovery no longer experienced that problem!
Erroneous reporting of indexspace differences
When Db2 started automatic schema comparison, it also reported that one of our indexes had a DECIMAL column with different SCALE between target and source
DSNU1567I + 307 13:28:58.30 DSNUCASV - DEFINITION OF TARGET INDEX SCHEMAT.X01OBJA DIFFERS FROM SOURCE INDEX SCHEMAS.X01OBJa: INDEX KEY COLUMN 6 SCALE - TARGET: 2, SOURCE: 0
But I looked and looked inside the catalog. The columns were the same. The SCALE of the columns inside these columns were the same.
Root cause: AGAIN, the source table and indexes were created before V10 and the definition of the index inside the DBD directory was subtly different compared how the object would be recorded today. The redirected recovery schema comparison did not know this difference thought it was a real difference.
Solution options:
- Use secret utility control statement of DIAGNOSE (8107) before the redirected recovery RECOVER statement. Basically, this DIAGNOSE statement tells the subsequent RECOVER to NOT do schema comparison of indexes!
- Apply the corrective PTF for the APAR PH50675 which documents the problem. Basically, the fix for schema comparison is to enhance RECOVER yet again to be aware of this case and do the comparison properly in this subtle case!
- Use REPAIR utility of REPAIR DBD REBUILD to rebuild the DBD inside the directory.
Initially, we chose to use DIAGNOSE (8107) to tell RECOVER to NOT do index schema comparison. After all, I knew that the objects were good. A few months later, we got the PTF for APAR PH50675 and we took away the DIAGNOSE, the redirected recovery no longer experienced that problem!
What is the DBD?
The DBD is the “database definition”. The database definition is inside the Db2 catalog tables that we all know and query. A copy of the DBD is also inside the Db2 directory. The Db2 directory is the hidden set of tables (and files) that Db2 uses behind the scenes at execution time for certain actions. Db2 is normally very good at keeping the DBD inside the catalog identical with the DBD inside the directory. If they ever get out of sync, then there are serious problems and IBM support will be required.
- Remember, my problems with redirected recovery and schema comparison are not due to the DBD being inconsistent between catalog and directory. The schema comparison was just not aware of how certain objects were recorded in the directory DBD before V10.
Did you know. If the DBD ever did become inconsistent we do have IBM reference documentation on how to diagnose DBD inconsistencies. This documentation is very technical and I think it less common for DBAs to have to do this type of diagnosis in 2023. In fact, I have never needed to do it (knock on wood).
Did you know that you can use Db2 command of REPAIR DBD to TEST or DIAGNOSE DBD differences and report on the differences of the DBD between the catalog and directory. You can even use REPAIR DBD to REBUILD the DBD of a database inside the directory. But the documentation is very clear that this DBD REBUILD should only be done after consultation with IBM support.
Apparently, using REPAIR DBD REBUILD is a drastic measure and can have surprising impacts so IBM prefers we do not perform that specific command willy-nilly. Be very careful. Instead, identify the root cause that caused you to even consider REPAIR DBD REBUILD and address that root cause instead of doing REPAIR DBD REBUILD.
Other APAR for Db2 redirected recovery
The redirected recovery functionality of the native IBM recover utility is relatively new. There will undoubtably be other subtle cases where it does not work as expected. Therefore, I recommend that you be as current as possible when beginning to use this new functionality.
I searched the IBM SUPPORT website for other APAR for Db2 Z and found other APAR. They were for cases that were not in my usage scenario, but it is interesting to know:
Reporting of object differences and stopping.
An annoying thing about redirected recovery schema comparison is that it stops after the first difference is discovered. It would be nice if it compared all the objects in the RECOVER so you would know at once everything that is different! And then stop.
- Hint, see my RFE 1402 (link at bottom) where I ask IBM to consider enhancing schema comparison!
- RFE 1402 ask IBM to enhance redirected recovery to only do schema comparison and then stop. This would provide a way to check your objects in advance of actually doing the redirected recovery.
- RFE 1402 also asks IBM to enhance redirected recovery to report all differences. Not just the first difference. Do not stop reporting after the first difference. Report all schema differences!
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