The DB2 REPORT utility

What REPORT is for

Online utilities such as COPY, RECOVER, and QUIESCE change recovery state. REPORT only reads. Phases are UTILINIT, the report collection phase, and UTILTERM. Because it does not drain writers or rewrite pages, it can run concurrently with other utilities on the same objects. That makes it the job you run at 2 a.m. when RECOVER is about to start and you want to confirm the last full copy, the incremental chain, the quiesce points, and whether archive logs still exist on the volumes the BSDS remembers.

REPORT is in the base utilities set (with QUIESCE, REPAIR, LISTDEF, TEMPLATE, CATMAINT, and the DSN stand-alone programs). You do not need the Db2 Utilities Suite license to run it. Authorization is SYSCTRL or SYSADM, System DBADM, DBADM or DBCTRL on the database, or installation SYSOPR for catalog and directory spaces in DSNDB01 and DSNDB06.

REPORT RECOVERY

REPORT RECOVERY is the recovery planner’s listing. IBM documents that the output includes:

• Recovery history from SYSIBM.SYSCOPY (full and incremental image copies, inline copies, quiesce points ICTYPE Q, LOAD/REORG events that affect recoverability)
• Log ranges from the directory table SYSIBM.SYSLGRNX (when the object was open for update, which drives how much log RECOVER must apply)
• Volume serial numbers of archive log data sets from the BSDS
• Indexes on the table space that are in informational COPY-pending (ICOPY) status, which affects whether you can RECOVER the index or must REBUILD it
• System-level backups from BACKUP SYSTEM when those copies are eligible as a base for object-level recoveries (SYSCOPY ROWS AND SYSTEM-LEVEL BACKUPS section)

In a data sharing group the report also surfaces member-specific facts: status of deactivated or destroyed members, system-level backups taken from each member’s BSDS, which member deleted a SYSCOPY row, SYSLGRNX ranges from before data sharing was enabled that cannot be used after enablement, and the high/low RBA of a migrated member. FlashCopy image copies can appear out of chronological order compared with sequential copies in the SYSCOPY section—read the START_RBA, not just the printed sequence.

Objects you name

Name a TABLESPACE, an INDEXSPACE, an INDEX, or a LIST. Unlike COPY, REPORT must know the object type when you use LISTDEF: write REPORT RECOVERY TABLESPACE LIST L1, not a bare LIST. INDEX ALL after a table space adds every index on that space so you can see which indexes are COPY YES (recoverable from image copy) and which are ICOPY (rebuild). DSNUM limits the report to one partition; use it when you recover by partition and do not want the whole space’s history mixed in.

1
2
3
4
5
6
7
8
9
10
REPORT RECOVERY TABLESPACE PAYDB.EMPTS
  INDEX ALL
  LOCALSITE;
 
REPORT RECOVERY TABLESPACE PAYDB.EMPTS PART 3
  CURRENT SUMMARY;
 
REPORT RECOVERY TABLESPACE LIST PAYCRIT
  INDEX ALL
  LOCALSITE ARCHLOG ALL;

LOCALSITE, RECOVERYSITE, and ARCHLOG

COPY can write local copies (COPYDDN) and recovery-site copies (RECOVERYDDN). RECOVER picks the chain that matches the site where it runs. REPORT RECOVERY with LOCALSITE shows the local chain; RECOVERYSITE shows the remote chain. If your DR copies are older or less frequent, the recovery-site report is the one that tells you how much extra log a remote restart would apply.

ARCHLOG 1 (the default) lists volume serials for the first archive-log copy in the BSDS. ARCHLOG 2 lists the dual copy. ARCHLOG ALL lists both. Dual archiving is useless at recovery time if you only ever look at copy 1 and that tape is the one that went missing—REPORT is how you confirm both copies still have volume serials.

Recovery RBAs (the REPORT RECOVERYRBA idea)

There is no separate IBM statement named REPORT RECOVERYRBA. The checklist topic means the recovery RBA and LRSN information inside REPORT RECOVERY. Read these fields as a beginner would:

• START_RBA on a SYSCOPY row is the log point associated with that copy or quiesce. RECOVER uses it to know where log apply starts (or, for a fuzzy SHRLEVEL CHANGE copy, pairs it with PIT_RBA).
• PIT_RBA on a SHRLEVEL CHANGE copy is the point IBM recommends for TORBA / TOLOGPOINT rather than TOCOPY alone, because pages could change while COPY ran.
• SYSLGRNX ranges are the intervals the page set was open for update. Gaps and overlapping ranges are why RECOVER sometimes needs more archive logs than you expected from the copy timestamp alone.
• In data sharing the same ideas are LRSN values. A non-data-sharing subsystem uses RBA.

After QUIESCE, a new SYSCOPY row with ICTYPE Q appears with the quiesce RBA/LRSN. After COPY, you see ICTYPE F (full) or I (incremental). After MERGECOPY NEWCOPY YES, you see a new full copy whose START_RBA is the RBA of the last incremental that was merged, while TIMESTAMP is when MERGECOPY ran—do not delete archive logs by MERGECOPY’s date without reading that RBA story (the MERGECOPY page covers the trap).

REPORT TABLESPACESET

A table space set is every table space Db2 treats as one recoverability family for referential integrity and auxiliary objects:

• Table spaces related by referential constraints
• The base table space and all of its LOB table spaces
• The base table space and all of its XML table spaces

The output names those table spaces, the tables in them, and tables that are dependent on those tables. That is the list you feed to QUIESCE TABLESPACESET or to a single RECOVER list so a point-in-time recover does not leave a parent committed and a child rolled back. The TABLESPACE keyword is optional on TABLESPACESET, matching QUIESCE. SHOWDSNS adds the underlying VSAM data set names, which is useful when you are matching image-copy data set names or DSN1COPY targets.

1
2
3
4
REPORT TABLESPACESET TABLESPACE PAYDB.ORDTS
  SHOWDSNS;
 
REPORT TABLESPACESET PAYDB.ORDTS;

Run REPORT TABLESPACESET when you add a new production table space, when you add referential constraints, and periodically for spaces that catalog queries show as having RI. Informational referential constraints are included in the set the same way QUIESCE TABLESPACESET includes them—if your shop uses those, the report is how you discover the extra spaces that must share a quiesce point.

CURRENT, SUMMARY, LIST, and clones

CURRENT is the “what would RECOVER to current use right now” view: latest copies and current log-range context, not every SYSCOPY row since the object was created. Use it when the full history is huge after years without MODIFY RECOVERY.SUMMARY shortens the printout. You can combine them. Neither option changes catalog data.

One LIST per REPORT statement. LISTDEF partition ranges are valid; REPORT is invoked with the object type so it knows whether it is walking table spaces or indexes. If base objects have clones, TABLESPACESET and RECOVERY reports include both base and clone information. Specify CLONE when you need the utility to treat clone data as the target the same way COPY and MERGECOPY do—CLONED YES on LISTDEF alone is not enough for those utilities, and REPORT follows the same clone rule when you are targeting clone copies.

1
2
3
4
5
6
7
8
//RPTREC  EXEC DSNUPROC,SYSTEM=DB2T,UID='PAY.RPT.EMP'
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  REPORT RECOVERY TABLESPACE PAYDB.EMPTS
       INDEX ALL
       LOCALSITE
       ARCHLOG ALL
/*

JCL needs SYSIN and SYSPRINT. There is no image-copy DD. After the job, keep the SYSPRINT with the change ticket for the recover. If REPORT shows a missing archive volume or an ICOPY index you forgot to copy, fix that before RECOVER, not after RECOVER-pending is on.

Explain It Like I'm Five

Imagine a library with a card catalog (SYSCOPY), a diary of when each book was off the shelf for writing (SYSLGRNX), and a list of which basement boxes hold old diary pages (BSDS archive volumes). REPORT RECOVERY is the librarian printing those three lists so you know whether you can rebuild a damaged book. REPORT TABLESPACESET is asking “which other books are glued to this one?” so you rewind the whole series together. Nobody photocopies a page and nobody rewinds the diary—REPORT only prints the lists. Recovery RBAs are the page numbers stamped on those cards. LOCALSITE is the catalog at this library; RECOVERYSITE is the catalog at the backup library across town.

Exercises

1. Write REPORT RECOVERY for PAYDB.ORDTS including all indexes, local site, both archive-log copies.
2. Write REPORT TABLESPACESET for the same space with SHOWDSNS. Compare the space list to a QUIESCE TABLESPACESET job.
3. From a sample SYSCOPY row, explain the difference between TIMESTAMP, START_RBA, and PIT_RBA for a SHRLEVEL CHANGE full copy.
4. Why might CURRENT SUMMARY be better than a full REPORT RECOVERY the night before a planned recover, and when would you still want the full history?
5. A DR test will run RECOVER at the recovery site. Which REPORT option shows the copies that test will use, and what happens if that chain is older than the local chain?