The COPY Utility

 <  Day Day Up  >  

The COPY Utility

The COPY utility is used to create an image copy backup data set for a complete table space, a single partition of a table space, or a complete index space. It can be executed so that a full image copy or an incremental image copy is created. A full image copy is a complete copy of all the data stored in the table space, table space partition, or index being copied . An incremental image copy is a copy of only the table space pages that have been modified due to inserts , updates, or deletes since the last full or incremental image copy.

CAUTION

For indexes, only full image copies can be created. Incremental image copies are not permitted for indexes. Furthermore, to be able to copy an index it must have been created with the COPY YES parameter.


The COPY utility utilizes the SYSIBM.SYSCOPY table to maintain a catalog of image copies. Every successful execution of the COPY utility places in this table at least one new row that indicates the status of the image copy. Information stored in the table includes the image copy data set name , the date and time of the COPY , the log RBA at the time of the copy, and the volume serial numbers for uncataloged image copy data sets. This information is read by the RECOVER utility to enable automated table space and index recovery.

The JCL in Listing 32.1 depicts a full image copy for a DB2 table space; the JCL in Listing 32.2 is an incremental image copy. The full image copy takes dual copies, whereas the incremental takes only a single image copy data set.

Listing 32.1. Image Copy JCL
 //DB2JOBU JOB (UTILITY),'FULL IMAGE COPY',CLASS=X,MSGCLASS=X, //         NOTIFY=USER //* //**************************************************************** //* //*        DB2 COPY UTILITY::FULL COPY //* //**************************************************************** //* //COPY EXEC DSNUPROC,SYSTEM=DSN,UID='FULLCOPY',UTPROC=" //* //DSNUPROC.COPY1 DD DSN=CAT.FULLCOPY.SEQ.DATASET1(+1), //       DISP=(MOD,CATLG),DCB=SYS1.MODEL, //       SPACE=(CYL,(5,2),RLSE),UNIT=3390 //DSNUPROC.COPY2 DD DSN=CAT.FULLCOPY.SEQ.DATASET2(+1), //       DISP=(MOD,CATLG),DCB=SYS1.MODEL, //       SPACE=(CYL,(5,2),RLSE),UNIT=3390 //DSNUPROC.SYSIN    DD  *     COPY TABLESPACE DSN8D881A.DSN8S881D          COPYDDN (COPY1, COPY2)          SHRLEVEL REFERENCE          DSNUM ALL   FULL YES /* // 

Listing 32.2. Incremental Image Copy JCL
 //DB2JOBU JOB (UTILITY),'INCREMENTAL COPY',CLASS=X,MSGCLASS=X, //         NOTIFY=USER //* //**************************************************************** //* //*        DB2 COPY UTILITY :: INCREMENTAL COPY //* //**************************************************************** //* //COPY EXEC DSNUPROC,SYSTEM=DSN,UID='INCRCOPY',UTPROC=" //* //DSNUPROC.SYSCOPY DD DSN=CAT.INCRCOPY.SEQ.DATASET(+1), //       DISP=(MOD,CATLG),DCB=SYS1.MODEL, //       SPACE=(CYL,(2,2),RLSE),UNIT=3380 //DSNUPROC.SYSIN    DD  *     COPY TABLESPACE DSN8D881A.DSN8S881D SHRLEVEL REFERENCE          DSNUM ALL   FULL NO /* // 

Listing 32.3 provides sample JCL for taking a full image copy of an index. There are two options that can be used to specify an index in the COPY SYSIN ”the INDEX name or the INDEXSPACE name. The INDEX name option requires specifying the index as creator.index-name ; the INDEXSPACE option requires specifying it as database.indexspace-name . Favor using the INDEXSPACE option over the INDEX name. When using the INDEX option DB2 has to resolve the index space name from the index name. If you specify the index space name using the INDEXSPACE option, DB2 will already have the index space name.

NOTE

It is a good practice to limit the index name to 8 characters . By doing so DB2 will use the index name as the index space name, thereby simplifying administration.


Listing 32.3. Index Copy JCL
 //DB2JOBU JOB (UTILITY),INDEX COPY',CLASS=X,MSGCLASS=X, //         NOTIFY=USER //* //**************************************************************** //* //*        DB2 COPY UTILITY :: INDEX COPY //* //**************************************************************** //* //COPY EXEC DSNUPROC,SYSTEM=DSN,UID='INDXCOPY',UTPROC=" //* //DSNUPROC.SYSCOPY DD DSN=CAT.INDXCOPY.SEQ.DATASET(+1), //       DISP=(MOD,CATLG),DCB=(SYS1.MODEL,BUFNO=20), //       SPACE=(CYL,(1,1),RLSE),UNIT=3390 //DSNUPROC.SYSIN    DD  *     COPY INDEXSPACE DSN8D881A.XPROJ1          SHRLEVEL REFERENCE /* // 

COPY Phases

The COPY utility has three phases:

UTILINIT

Sets up and initializes the COPY utility

REPORT

Reporting for the CHANGELIMIT option

COPY

Copies the table space or index data to the sequential file specified in the SYSCOPY DD statement

UTILTERM

Performs the final utility cleanup


Calculating SYSCOPY Data Set Size

To create a valid image copy, the COPY utility requires that the SYSCOPY data set be allocated. The following formula calculates the proper size for this data set:

 

 SYSCOPY = (number of formatted pages) x 4096 

NOTE

For segmented table spaces, empty formatted pages are not copied. This will reduce the size of the backup data set.


If the table space being copied uses 32K pages, multiply the result of the preceding calculation by 8. The total number of pages used by a table space can be retrieved from the VSAM LISTCAT command or from the DB2 Catalog as specified in the NACTIVEF column in SYSIBM.SYSTABLESPACE . When copying a single partition, use the NACTIVE column in SYSIBM.SYSTABSTATS to estimate the backup size.

If you use the DB2 Catalog statistics, ensure that the statistics are current by running the RUNSTATS utility (discussed in Chapter 34, "Catalog Manipulation Utilities").

After calculating the estimated size in bytes for this data set, convert the number to cylinders , rounding up to the next whole cylinder. Allocating data sets used by DB2 utilities in cylinder increments enhances the utility's performance.

graphics/v7_icon.gif

Of course, you can choose to use dynamic allocation and templates as of DB2 V7 instead of manually specifying data sets in your utility JCL.


COPY Locking Considerations

Copies running against the different partitions of the same table space can run concurrently. Many other utilities can run concurrently with COPY , as well.

COPY TABLESPACE (whether SHRLEVEL REFERENCE or SHRLEVEL CHANGE ) can run concurrently with the following utilities (each accessing the same object):

BACKUP SYSTEM

CHECK INDEX

CHECK LOB

COPY INDEXSPACE

DIAGNOSE

REBUILD INDEX

RECOVER INDEX

REORG INDEX

REPORT

REORG UNLOAD ONLY or UNLOAD EXTERNAL

RUNSTATS INDEX

RUNSTATS TABLESPACE

STOSPACE

UNLOAD

REPAIR LOCATE (KEY, RID, PAGE, DUMP or VERIFY)

 

Furthermore, the COPY TABLESPACE utility can run concurrently with REPAIR LOCATE INDEX ( PAGE REPLACE ) and QUIESCE , but only when specifying SHRLEVEL REFERENCE .

COPY INDEXSPACE (whether SHRLEVEL REFERENCE or SHRLEVEL CHANGE ) can run concurrently with the following utilities (each accessing the same object):

BACKUP SYSTEM

CHECK DATA

CHECK INDEX

CHECK LOB

COPY TABLESPACE

DIAGNOSE

RECOVER TABLESPACE

REPORT

RUNSTATS INDEX

RUNSTATS TABLESPACE

STOSPACE

UNLOAD

REORG TABLESPACE UNLOAD ONLY or EXTERNAL

 

REPAIR LOCATE ( KEY , RID , PAGE , DUMP or VERIFY )

 

REPAIR LOCATE TABLESPACE PAGE REPLACE

 

Furthermore, the COPY INDEXSPACE utility can run concurrently with QUIESCE , but only when run specifying SHRLEVEL REFERENCE .

The COPY utility with the SHRLEVEL REFERENCE option drains the write claim class for the table space, partition, or index. This enables concurrent SQL read access. When SHRLEVEL CHANGE is specified, the COPY utility will claim the read claim class. Concurrent read and write access is permitted with one exception. A DELETE with no WHERE clause is not permitted on a table in a segmented table space while COPY SHRLEVEL CHANGE is running.

COPY Guidelines

You can use the following tips and techniques to ensure that the COPY utility is used effectively at your organization.

Increase Performance Using Inline Copies

The IBM DB2 LOAD and REORG utilities can take inline image copies during regular utility processing. By taking advantage of this capability, overall performance is enhanced because fewer scans of the data are required to produce the image copy data sets.

Balance the Use of Incremental and Full Image Copies

For most application table spaces, favor the creation of full image copies over incremental image copies. The time saved by incremental copying is often minimal, but the additional work to recover using incremental copies is usually burdensome.

To reduce the batch processing window, use incremental image copies for very large table spaces that incur only a small number of modifications between image copy runs. However, base the decision to use incremental image copies rather than full image copies on the percentage of table space pages that have been modified, not on the number of rows that have been modified. The image copy utility reports on the percentage of pages modified, so you can monitor this number. Consider using incremental image copies if this number is consistently small (for example, less than 20%).

You should consider incremental copying as the table space becomes larger and the batch window becomes smaller.

Take Full Image Copies to Encourage Sequential Prefetch

Remember that DB2 utilities requiring sequential data access use sequential prefetch, thereby enhancing utility performance. Thus, full image copies can be quicker than incremental image copies. A full image copy sequentially reads every page to create the image copy. An incremental image copy must check page bits to determine whether data has changed, and then access only the changed pages.

When incremental image copying does not use sequential prefetch, full image copying can be more efficient. Extra time is used because of the additional MERGECOPY step and the potentially inefficient processing (that is, without sequential prefetch). Compare the performance of incremental and full image copies before deciding to use incremental image copies.

Take Full Image Copies for Active and Smaller Table Spaces

Take full image copies for table spaces in which 40% or more of the pages are modified between executions of the COPY utility.

Always take full image copies of table spaces that contain less than 50,000 4K pages. For table spaces with larger page sizes, factor in the page size to arrive at a lower limit ”for example, 25,000 8K pages.

Specify SHRLEVEL REFERENCE to Reduce Recovery Time

COPY specifying SHRLEVEL REFERENCE rather than SHRLEVEL CHANGE . This reduces the time for table space recovery. See the section titled " RECOVER TABLESPACE Guidelines" later in this chapter.

Running COPY with SHRLEVEL CHANGE can cause uncommitted data to be recorded on the copy. For this reason, recovering to a SHRLEVEL CHANGE copy using the TOCOPY option is not recommended.

An additional reason to avoid SHRLEVEL CHANGE is the impact on the performance of the COPY utility. Because other users can access the table space being copied, the performance of the COPY utility may degrade because of concurrent access. Note, however, that SHRLEVEL REFERENCE has only a performance advantage ”not an integrity advantage ”over SHRLEVEL CHANGE .

CAUTION

The integrity of SHRLEVEL REFERENCE and SHRLEVEL CHANGE backups are the same if the archive logs exist. In practice, test archive logs are not kept for long periods of time. At any rate, if you are using SHRLEVEL CHANGE be sure to institute a proper retention period for your archive logs to maintain the viability of the backups.


Code JCL Changes to Make COPY Restartable

To make the COPY utility restartable, specify the SYSCOPY DD statement as DISP=(MOD,CATLG,CATLG) . When restarting the COPY utility, change the data set disposition to DISP=(MOD,KEEP,KEEP) .

Create a Consistent Recovery Point

Be sure to QUIESCE all table spaces in the table space set before copying. Do this even when some table spaces do not need to be copied so you can provide a consistent point of recovery for all referentially related table spaces. Do so by creating a batch job stream with the following steps:

  1. START all table spaces in the table space set using ACCESS(UT) or ACCESS(RO) . Starting the table spaces in RO mode enables concurrent read access while the COPY is running.

  2. QUIESCE all table spaces in the table space set.

  3. Execute the COPY utility for all table spaces to be copied.

  4. START all table spaces in the table space set using ACCESS(RW) .

NOTE

The consistent backup created by this series of steps is ideal for populating a test environment (using DSN1COPY ).


Consider Creating DASD Image Copies

Consider using disk rather than tape for image copy SYSCOPY data sets that will remain at the local site for recovery. This speeds the COPY process; disk is faster than tape, and you eliminate the time it takes the operator (or the automated robot tape loader) to load a new tape on the tape drive.

Be sure to verify that the image copy is placed on a separate DASD volume from the table space or index being copied. Failure to do so can result in a disk failure that affects both the data and its image copy. Also, as a failsafe, consider taking dual image copies where one is made to disk and the other is made to tape.

Consider Copying Indexes

The REBUILD process can take a long time to complete for large amounts of data or when multiple indexes exist. As of V6, you can take a full image copy or a concurrent copy of an index. Instead of rebuilding indexes during recovery, you use the RECOVER utility to restore the image copy and apply log records.

NOTE

You must specify the COPY YES parameter when creating an index to be able to use the COPY utility to make image copy backups for the index. The default is COPY NO . Existing indexes can be altered to specify the COPY YES parameter. If the index is defined using COPY YES you can use either the REBUILD method or the COPY and RECOVER method for index recovery.


The following utilities can place an index that was defined with the COPY YES attribute in the informational COPY pending ( ICOPY ) status:

LOAD TABLE ( LOG YES or NO )

REBUILD INDEX

REORG TABLESPACE ( LOG YES or NO )

REORG INDEX


To remove the ICOPY status, create a full image copy of the index after running these utilities.

Synchronize Data and Index Copies

If you decide to use COPY and RECOVER for indexes, instead of rebuilding indexes after recovering table spaces, be sure to keep the data and index backups synchronized. When you COPY a table space, be sure to also COPY any associated indexes defined with COPY YES .

Buffer the SYSCOPY Data Set Appropriately

For large image copies set the BUFNO parameter in the JCL for the SYSCOPY DD statement to a number greater than 20. The BUFNO parameter creates read and write buffers in main storage for the data set, thereby enhancing the performance of the COPY utility. Ensure that sufficient memory (real or expanded) is available, however, before increasing the BUFNO specification for your SYSCOPY data sets.

Favor Dual Image Copies

Take dual image copies for every table space (and index) being copied to eliminate the possibility of an invalid image copy due to an I/O error or damaged tape.

Prepare for disasters by sending additional image copies off-site. It is a wise course of action to take dual offsite copies, in addition to dual local image copies.

Compress Image Copies

To conserve tapes, consider compressing image copies. Use the silo compression if it's available. Additionally, third-party tools are available to compress data on tape cartridges.

Compressing image copy data sets not only saves tapes, but can improve performance. If a backup requires fewer tapes, fewer tape mounts will be required, which should reduce overall elapsed time. The same can be said for the recovery process. If fewer tapes are required to RECOVER , elapsed time should improve.

If your shop does not compress cartridges by default, add the following parameter to the DCB specification for the SYSCOPY DD :

 

 DCB=TRTCH=COMP 

Use CHANGELIMIT to Help with Copies

The CHANGELIMIT parameter can be specified on the COPY utility. When CHANGELIMIT is specified, COPY analyzes the number of changed pages since the last copy.

CHANGELIMIT accepts one or two integers (from 0 to 100) as input. Each integer is a percentage. If only one value is specified, an incremental image copy will be created if the percentage of changed pages is greater than 0 and less than the specified value. A full image copy will be created if the percentage of changed pages is greater than or equal to the specified percentage, or if CHANGELIMIT(0) is specified. No image copy will be created if there were no changed pages, unless 0 was specified for the CHANGELIMIT .

If two values are specified, an incremental image copy will be created if the percentage of changed pages is greater than the lowest value specified and less than the highest value specified. A full image copy will be created if the percentage of changed pages is equal to or greater than the highest value specified. No image copy will be created if the percentage of changed pages is outside the range of the low percentage and high percentage specified. If the two percentages happen to be the same, it will follow the rules as if one value was specified, as stated previously. When CHANGELIMIT is specified with COPY , return codes are set as indicated in Table 32.1.

CAUTION

You cannot specify CHANGELIMIT when copying a table space or partition defined as TRACKMOD NO .


Table 32.1. COPY/CHANGELIMIT Return Codes

Return Code

Description

1

No CHANGELIMIT percentage is met; no image copy is recommended or taken.

2

The percentage of changed pages is greater than the low CHANGELIMIT value, but less than the high CHANGELIMIT value; incremental copy is recommended or taken.

3

The percentage of changed pages is greater than or equal to the high CHANGELIMIT value; full image copy is recommended or taken.

8

The COPY step failed.


If REPORTONLY is specified, a report of the number of changed pages is produced. Further action can be taken after reviewing the report or checking the return code in the JCL.

Without the REPORTONLY parameter, the COPY utility automatically decides whether or not to take an image copy ”if it does take an image copy, the COPY utility determines if the image is to be incremental or full. Consider using the return code to check the type of COPY that was created, and run a MERGECOPY step only if the return code indicates an incremental copy was created.

Consider Using LISTDEF to Copy RI-Related Objects

The LISTDEF option can be very useful when used with the COPY utility. This is especially so when you need to copy a complete set of referentially related table spaces. You can use the RI parameter on the INCLUDE statement of the LISTDEF to cause all tables that are referentially connected to tables specified in the list to also be included. For example:

 

 LISTDEF LSTR1 INCLUDE TABLESPACES         TABLESPACE DSN8D81A.DSN8S81E  RI 

This list will include all table spaces that include tables referentially related to any tables in the DSN8S81E table space of the sample database. This is much easier to manage than a job built to explicitly copy each of these table spaces ”and it will include any new related table space as soon as it is created.

Control System Page Placement

As of DB2 V8, you can use the SYSTEMPAGES parameter to exert some control over the placement of system pages in your image copy data sets. System pages are necessary when using the UNLOAD utility to unload data from an image copy. To ensure that the header, dictionary, and DB2 version system pages are copied at the beginning of the image copy data set, simply code SYSTEMPAGES YES on your COPY utility control card.

Consider Using DFSMS to Make Backup Copies

DFSMS can be utilized in the backup and recovery strategy for DB2 table spaces and indexes. DB2 provides the capability to recover from backup copies of DB2 data sets taken using the concurrent copy feature of DFSMS.

DFSMS is invoked under the control of DB2 using the COPY utility (as of DB2 V4). This simplifies the process of utilizing DFSMS within your DB2 backup and recovery plan. DFSMS is invoked by specifying the CONCURRENT parameter on the COPY utility. The image copy data sets created by the COPY utility and DFSMS are stored in the DB2 Catalog ( SYSIBM.SYSCOPY ) with an ICTYPE of F and an STYPE of C .

NOTE

An output data set for DFSMS messages must be specified to the DSSPRINT DD card when CONCURRENT copy is specified and the SYSPRINT DD card is defined to a data set.


CAUTION

graphics/v8_icon.gif

You cannot use SHRLEVEL CHANGE with CONCURRENT COPY for table spaces having a page size greater than 4K, unless the page size exactly matches the control interval size of the underlying VSAM data set.


Consider Log Suspension

As of DB2 V7 (or the V6 refresh), you can issue a SET LOG SUSPEND command to effectively suspend all data modification within a DB2 subsystem. The SET LOG SUSPEND command externalizes log buffers, takes a system checkpoint, updates the BSDS with the highest written RBA, and takes a log write latch to prevent updates. Subsequent writes from the buffer pool are not externalized until the SET LOG RESUME command is issued. This causes the write latch to be released thereby enabling logging and data modification to ensue.

By taking a snapshot of the entire system after issuing the SET LOG SUSPEND command, you will have created a snapshot that can be used for local or remote site recovery with consistent data. Use this technique only in conjunction with external copying technology such as that provided by RVA SnapShot or ESS FlashCopy.

The snapshot copy can be used in the event of a disaster to recover the system to a point of consistency. Alternatively, you can use it to provide a quick, consistent copy of your data point-in-time querying.

However, keep in mind that careful planning is required to minimize application impact of a log suspension. Avoid suspending log activity during periods of heavy data modification.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net