DB2 objects' structure comparison using ISRSUPC
My team does frequent data migrations across DB2 sub-systems, for ERP application. We use Redirected Recovery component of Recovery Expert for z/OS for data migrations. Its an acceptable situation where the objects' structures in source sub-system will not be in sync with that of target sub-system. But, data migration jobs will fail if we try to migrate data for not-in-sync objects.
Recovery Expert doesn't have in-built object structure comparison capability. So, we use DB2 Object Comparison Tool present in DB2 Administration Tool menu for comparing the objects' structures before data migration. The activities involved in generating the report of mismatches using the Tool are
- Specify compare source (new)
- Specify compare target (old)
- Specify compare masks
- Specify ignores
- Generate compare job
The Tool performs fine. But, I didn't like going through this whole process in DB2 Object Comparison Tool and felt that its complicated and time consuming. I want to get the same results in less time and with less prep work. So, I tried and built a new process for Objects' structure comparison using REXX.
The valid question is , do we really need an alternate process when there is a sophisticated Tool available already. My answer is, to keep it simple *GRIN*
The first hurdle I faced in developing the new process is, handling cross sub-systems connections.
As we do cross sub-systems data migrations, Source and Target databases can be on different sub-systems. Location transparency using three-part names is not enabled in our Mainframes setup. And, REXX can't handle connections to multiple DB2 sub-systems at same time. So, I need to open a connection to DB2 sub-system, do the processing, close the connection and then open new connection to other DB2 sub-system.
I coded separate PARAs in the REXX routine to handle the cross connections.
I coded separate PARAs in the REXX routine to handle the cross connections.
The second hurdle I faced in developing the new process is, batch job's (that executes the REXX routine) performance.
First version of my REXX routine worked as below (in the exact order).
1. REXX routine reads input (Source and Target database names)
2. Connect to Source DB2 sub-system
3. Get TS, TB, IX, Columns details into arrays for the Source Database
4. Close connection to Source DB2 sub-system
5. Connect to Target DB2 sub-system
6. Get TS, TB, IX, Columns details into arrays for the Target Database
7. Close connection to Target DB2 sub-system
8. Compare TS' details and write mismatches to output
9. Compare TB' details and write mismatches to output
10. Compare IX' details and write mismatches to output
11. Compare Columns' details and write mismatches to output
I coded simple DO loops in the COMPARE_PARA, which worked fast for TS, TB & IX comparisons, but the performance took a worst blow for Columns' comparison (there are 50,000+ Columns in every database). We have regular REXX and System REXX (that come by default with z/OS installation), but don't have a REXX compiler which could have helped in making REXX execution faster. The job started taking hours for completion, which is not at all expected.
After going through the great suggestions I got for my posts in LinkedIn, re-coded my REXX routine replacing the DO loop in COMPARE_PARA with call to ISRSUPC. Second version of my REXX routine has the below new steps included into it.
1. Allocate NEWDD, OLDDD, OUTDD datasets
2. Read the TS' details of Source database into NEWDD file
3. Read the TS' details of Target database into OLDDD file
4. Setting PARM values to DELTAL,LINECMP
5. Execute the blow statement
ADDRESS ISPEXEC "SELECT PGM(ISRSUPC) PARM("PARMS")"
6. Process OUTDD dataset to get details of mismatches.
7. Free NEWDD, OLDDD, OUTDD datasets.
The same order is followed for TB, IX and Columns details comparison.
ISRSUPC did the magic. The job that took hours for completion started completing in minutes.
The JCL used for REXX routine execution is below.
JOBLIB datasets, Input Database names and SYSUT2 dataset name change based on requirement.
JOBLIB datasets, Input Database names and SYSUT2 dataset name change based on requirement.
//PDB00000 JOB (0000,00000000),'OBJECT COMPARE',
// CLASS=E,MSGCLASS=X,NOTIFY=&SYSUID,REGION=0M
//JOBLIB DD DSN=EGP1X.SDSNEXIT,DISP=SHR
// DD DSN=DSNEGP1.SDSNLOAD,DISP=SHR
// DD DSN=EGQ1X.SDSNEXIT,DISP=SHR
// DD DSN=DSNEGQ1.SDSNLOAD,DISP=SHR
//STEP010 EXEC PGM=IKJEFT01,DYNAMNBR=10
//ISPMLIB DD DISP=SHR,DSN=ISP.SISPMENU
// DD DISP=SHR,DSN=ISF.SISFMLIB
//ISPSLIB DD DISP=SHR,DSN=ISP.SISPSENU
// DD DISP=SHR,DSN=ISP.SISPSLIB
//ISPTLIB DD DISP=SHR,DSN=ISP.SISPTENU
// DD DISP=SHR,DSN=ISF.SISFTLIB
//ISPPLIB DD DISP=SHR,DSN=ISP.SISPPENU
// DD DISP=SHR,DSN=ISF.SISFPLIB
//ISPLOG DD DCB=(RECFM=VA,LRECL=125,BLKSIZE=129),SYSOUT=*
//ISPLIST DD DCB=(RECFM=FBA,LRECL=121,BLKSIZE=1210),SYSOUT=*
//ISPPROF DD RECFM=FB,LRECL=80,SPACE=(TRK,(2,2,2))
//SYSPROC DD DISP=SHR,DSN=RFO7936.PROCAUTO.OBJCOMP
//SYSTSPRT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
ISPSTART CMD(%RXOBJCMP LPALIF LQALTAX)
//SYSUT1 DD DUMMY
//SYSUT2 DD DSN=RFO7936.SUPC.OUTP,
// DISP=(NEW,CATLG,DELETE),UNIT=DISK,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3600),
// SPACE=(CYL,(5,5),RLSE)
//*
Finally, I'm getting the desired output within minutes by submitting just a JCL *WINK*
No comments:
Post a Comment