The DB2 MERGECOPY utility

What MERGECOPY merges

Inputs are sequential image copies from COPY or COPYTOCOPY, and inline copies from LOAD or REORG. Output is either a new incremental or a new full sequential copy. Phases: UTILINIT, MERGECOP (merge incrementals), UTILTERM. The table space remains UTRW—read-write for applications—because MERGECOPY never opens the page set. Other utilities on the same space are compatible. Partitions are separate targets: you can MERGECOPY partition 3 while COPY runs on partition 4.

MERGECOPY chooses input copies that match the current site. At the local site it reads local (COPYDDN) copies; at the recovery site it reads recovery-site copies. It will not take a FlashCopy image copy or a DFSMSdss concurrent copy as input. It will not run on DSNDB01.DBD01, DSNDB01.SYSUTILX, DSNDB06.SYSTSCPY, or DSNDB01.SYSDBDXA because those spaces cannot have incremental copies. After a PIT RECOVER to a point before pending DDL was materialized, do not MERGECOPY until REORG finishes that recovery process.

NEWCOPY YES and NEWCOPY NO

IBM recommends creating a new full copy with MERGECOPY for these reasons:

• A new full copy is a clean recovery base (one data set for RECOVER to restore)
• Extra elapsed time does not hurt SQL access—the only real contention is on SYSCOPY
• The log range RECOVER must apply is the same as if you kept the incremental chain
• If copies are on tape, recovery needs fewer tape drives

NEWCOPY YES inserts a SYSCOPY row for the new full copy. The incremental rows remain (they are not deleted the way NEWCOPY NO deletes them). NEWCOPY NO deletes SYSCOPY records of the incrementals that were merged and inserts one row for the new incremental. If you omit NEWCOPY, COPYDDN, and RECOVERYDDN, the default is NEWCOPY NO COPYDDN(SYSCOPY), which is valid at the local site only.

1
2
3
4
5
6
7
8
MERGECOPY TABLESPACE PAYDB.EMPTS
  NEWCOPY YES
  COPYDDN(SYSCOPY);
 
MERGECOPY TABLESPACE PAYDB.EMPTS
  NEWCOPY NO
  COPYDDN(SYSCOPY)
  WORKDDN(SYSUT1);

COPYDDN, RECOVERYDDN, and WORKDDN

COPYDDN names the local output; you can give a primary and a backup. RECOVERYDDN names the remote-site output the same way. Templates and DD names both work; if a name is both a DD and a TEMPLATE, the DD wins. Restrictions IBM documents:

• At the local site you cannot use RECOVERYDDN with NEWCOPY NO (you cannot build a remote incremental from local incrementals in that combination)
• At the recovery site you cannot use COPYDDN with NEWCOPY NO

WORKDDN holds intermediate merged output when you cannot allocate every input copy at once (typical with many tape incrementals). Default ddname is SYSUT1. IBM requires a work data set for MERGECOPY. Size it at least as large as the largest input copy and use the same DCB attributes as the image copies. If allocation is short and you omit a usable work file, MERGECOPY may merge only some data sets and message how many remain; rerun with a new output data set to continue.

1
2
3
4
5
6
7
8
9
10
11
12
//MERGE   EXEC DSNUPROC,SYSTEM=DB2T,UID='PAY.MRG.EMP'
//SYSCOPY DD DSN=PAY.COPY.EMPTS.FULL(+1),DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,SPACE=(CYL,(50,10),RLSE)
//SYSUT1  DD UNIT=SYSDA,SPACE=(CYL,(50,10))
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  MERGECOPY TABLESPACE PAYDB.EMPTS
       DSNUM ALL
       NEWCOPY YES
       COPYDDN(SYSCOPY)
       WORKDDN(SYSUT1)
/*

LIST, DSNUM, and CLONE

TABLESPACE db.ts names one space; database defaults to DSNDB04. DSNUM ALL (default) merges the whole space. DSNUM integer merges one partition or, for a nonpartitioned space, one data set (the Annn piece). You cannot code DSNUM and LIST on the same statement—use PARTLEVEL on LISTDEF instead. If COPY was taken by data set, MERGECOPY must merge by data set.

LIST names a LISTDEF of table spaces only (one LIST per statement). MERGECOPY is invoked once per space in the list. CLONE processes only copies taken against clone objects. CLONED YES on LISTDEF is not sufficient; you must specify CLONE on MERGECOPY.

Do not mix whole-space and data-set incrementals

MERGECOPY can merge copies of an entire table space, or of individual data sets, but not a mix of those incremental types into a new incremental. Mixing them yields DSNU460I (image copies inconsistent, request rejected) and RC 4. With NEWCOPY YES you can merge a full copy of the space with incrementals of the space and of individual data sets to make a new full copy of the space.

SYSCOPY RBA versus TIMESTAMP

MERGECOPY does not include log that was written between the last input image copy and the moment MERGECOPY ended. The new SYSCOPY row’s START_RBA is the RBA of that last input copy. TIMESTAMP is when MERGECOPY ran. If you expire archive logs by date using TIMESTAMP, you can throw away log that RECOVER still needs if you recover from an older copy, and you can misunderstand what the new copy actually contains.

IBM’s procedure: find the MERGECOPY row in SYSCOPY; read START_RBA; find the input copy with the same START_RBA; use that copy’s TIMESTAMP with MODIFY RECOVERY if you are cleaning by date. Recommendation: run MERGECOPY NEWCOPY YES soon after each incremental so calendar dates stay a reasonable (not perfect) cleanup criterion and RECOVER restores one full data set.

JES3 tape units and partial merges

In JES3, if the number of image copies to mount exceeds available units, MERGECOPY can allocate everything that exists and then wait for more drives. Plan unit counts. Input copies are dynamically allocated; you optionally preallocate them with DD names. Output COPYDDN data sets you allocate or TEMPLATE. After a partial merge, the message tells you how many data sets exist versus how many were merged—do not assume the chain is fully collapsed until that count matches.

Termination: TERM UTILITY. Restart: by default MERGECOPY restarts at the beginning of the current phase; after an out-of-space condition you can restart from the last commit point. MERGECOPY versus taking another COPY FULL YES: COPY is required after LOAD or REORG LOG NO unless an inline copy exists. In other cases, incremental COPY plus MERGECOPY NEWCOPY YES is a valid way to refresh a full copy without scanning the table space again.

Explain It Like I'm Five

Every night you photograph only the desks that changed (incremental copies). After a month, rebuilding the classroom means hanging the big class photo and then thirty sticky notes. MERGECOPY is taping those sticky notes onto the last class photo to make one new class photo (NEWCOPY YES) or taping the sticky notes onto each other into one fat sticky note (NEWCOPY NO). You never walk into the classroom to do this—you only shuffle photographs on the table. The date you taped them is today, but the “story so far” in the photo still stops at the last sticky note’s time, which is why the diary (the log) after that last photo is not inside the new picture.

Exercises

1. Write MERGECOPY NEWCOPY YES for PAYDB.EMPTS with a GDG COPYDDN and SYSUT1 WORKDDN.
2. Explain in one paragraph why NEWCOPY YES is usually better for recovery even though NEWCOPY NO produces a smaller output.
3. A shop runs MERGECOPY at the local site with NEWCOPY NO RECOVERYDDN(REM). Why does IBM reject that combination?
4. Query SYSCOPY after MERGECOPY and identify TIMESTAMP versus START_RBA. Which date would you pass to MODIFY RECOVERY?
5. List three copy types or objects MERGECOPY will not process, and what you would use instead for each.