Offline Delete Archive Process

A batch Delete Archive Process allows you to avoid the overhead of DB2^® logging, an important consideration when deleting large quantities of data from your database.

This offline Delete Archive Process has the same effect as deleting “delete after archive” data directly from the database.

In the offline Delete Archive Process, Archive compares Archive File data with data in one or more DB2 UNLOAD-compatible files to create a new file that excludes the archived data marked “delete after archive.” Archive uses matching keys as the basis of comparison for the two files. The new file, minus the deleted data, can then be loaded into the database.

The offline Delete Archive Process requires the following steps:

Use an online or batch Archive Process to create an Archive File containing one or more tables designated for a deferred delete after archive.
Unload data from the database using DSNTIAUL, REORG, REORG UNLOAD, or an unload program. Each unload file must contain all columns of the table.

Note: Do not unload multiple tables in one process, using DSNTIAUL.

Run the offline delete utility with one or more Archive Files and the unloaded data as input. Because an unload file typically contains data from a single tablespace, which is often limited to data from a single table, multiple unload files can be used as input to this offline process. The output is in two parts:
Note:
- The Discard File contains only unloaded data that matches data in the Archive Files that is designated for delete after archive.
- The Delta File contains all other unloaded data.
  The format of these output files is the same as the format of the unloaded data.
Load the Delta File to the database using DB2 LOAD or LOADPLUS. You must include the LOAD utility REPLACE option to delete all rows in the tablespace before copying the Delta rows into the database. If it becomes necessary, the Discard File can also be reloaded to the database.

The following diagram illustrates the offline Delete Archive Process:

The JCL needed to execute the offline delete utility is:

//          EXEC PGM=FOPMAIN,REGION=0M,
//          PARM=‘CON UTILITY subsys planname sqlid userid'
//*
//PSDFDFLT  DD DSN=your.loadlib(FOPMDFLT)  DEFAULT SITE OPTIONS
//              DISP=SHR
//SYSPRINT  DD SYSOUT=*                    INTERNAL ERRORS / MESSAGES
//SYSTERM   DD SYSOUT=*                    INTERNAL ERRORS / MESSAGES 
//PSTRACE   DD SYSOUT=*                    TRACE OUTPUT
//PSDFASUM  DD SYSOUT=*                    UTILITY SUMMARY LISTING FILE
//PSDFADIR  DD SYSOUT=*                    DIRECTORY LISTING FILE
//PSDFAREP  DD SYSOUT=*                    REPORT OUTPUT
//PSDFRPRT  DD SYSOUT=*                    DELETE REPORT OUTPUT
//SYSRECnn  DD DSN=input.file.name         INPUT UNLOAD-COMPATIBLE FILES
//              DISP=SHR
//SYSNEWnn  DD DSN=output.file.name        OUTPUT FILES
//              DISP=OLD
//SYSDSCnn  DD DSN=discard.file.name       DISCARD FILES
//              DISP=OLD
//SYSIN     DD *
               control statements
//*

A sample of this JCL is provided in the sample library as member FOPJOBUT. Unique data set names are required for SYSRECnn, SYSNEWnn, and SYSDSCnn; the offline delete utility will not overwrite the input file.

OFFLINE_DELETE Statement

Use the OFFLINE_DELETE statement to compare data in one or more Archive Files with data in one or more DB2 UNLOAD-compatible files. This creates new UNLOAD-compatible files that exclude the archived data marked “delete after archive.” The new files, minus the deleted data, can then be loaded into the database.

Executing the OFFLINE_DELETE statement also generates a report of the Delete Process in the Delete Report listing file. Information in the report includes the input data set name, Discard File name, and names of any tables not processed.

OFFLINE_DELETE
NAME ( arc.file.name1 [, arc.file.name2, ... ] )
UNLD ( [ cid. ] tblname1, unld-ddn, discard-ddn, outunld-ddn,
      format, scan [, VIEWDELETE ] )
[ UNLD ( [ cid. ] tblname2, unld-ddn, discard-ddn, outunld-ddn,
      format, scan [, VIEWDELETE ] ) ]

Note: ARCHIVE_DELETE is a synonym for the OFFLINE_DELETE statement.

The keywords and operands in the OFFLINE_DELETE statement are:

NAME

The name of one or more Archive Files with data to be deleted. Multiple values can be specified in any order, but must be enclosed in parentheses and separated by commas. (Note that if you specify a single value, parentheses are optional.) This keyword and its operand must be included in the OFFLINE_DELETE statement.

arc.file.name: Specify the fully qualified name of a valid Archive File data set as an explicit value or a pattern, using DB2 LIKE syntax. An Archive entry for the file is not required.

UNLD

Processing for tables in the Archive File. This keyword and the following operands are required for each table. The operands are positional and must be specified in the following order:

cid.tblname

Specify the name of a table marked for DAA in the designated Archive File and prefix with Creator ID, if different from the default Creator ID for the Archive File. An error condition occurs if the table is not designated for DAA.

unld-ddn

DDNAME for the corresponding UNLOAD-compatible file used as input to the offline Delete Archive Process.

discard-ddn

DDNAME for the output Discard File.

outunld-ddn

DDNAME for the output Delta File.

format

Format of the UNLOAD-compatible input, Delta, and Discard files. Specify the format of the input file:

BMC: UNLOAD PLUS format.
DSNTIAUL: DSNTIAUL format, including Auto-Unload, Auto-Online Unload, and IBM^® High Performance Unload.
UNLOADONLY: IBM REORG UNLOAD ONLY
UNLOADEXT: IBM REORG UNLOAD EXTERNAL
UNLOAD: IBM REORG UNLOAD CONTINUE or UNLOAD PAUSE format.

scan

The method used to scan the Unload and Archives Files for key matches. Specify one of the following:

MATCHSCAN: If a key match exists, scan the remainder of the row to verify that no values have changed. If a change has occurred, the row is retained.
MATCHCRC: If a key match exists, compare a generated CRC value instead of the remainder of the row. If a change has occurred, the row is retained.
MATCHNONE: The user must verify that no changes occurred since the Archive Process. If a key match exists, the row is written to the Discard File. Duplicate keys are not supported and will result in a duplicate key error.

VIEWDELETE

If processing a view, use this keyword to match only the columns that are present. If you omit this keyword when processing a view or if the view applies to more than one table, an error condition occurs.

Note: The entire row is not matched; values in columns other not represented in the view may have been changed.

Example

The following is an example of OFFLINE_DELETE statement usage:

The DETAILS and ORDERS tables, in the input UNLOAD-compatible files associated with the DDNAMES SYSREC00 and SYSREC01, are designated DAA in the Archive File FOPDEMO.ARCHIVE.FILE. The DDNAMES SYSDSC00 and SYSDSC01 designate the Discard Files, and the DDNAMES SYSNEW00 and SYSNEW01 designate the Delta Files. A matchscan is performed. To purge the designated data, specify:

OFFLINE_DELETE
   NAME FOPDEMO.ARCHIVE.FILE
   UNLD (DETAILS, SYSREC00, SYSDSC00, SYSNEW00,
      UNLOAD, MATCHSCAN)
   UNLD (ORDERS, SYSREC01, SYSDSC01, SYSNEW01,
      UNLOAD, MATCHSCAN)