The Stand-alone Utilities

The DB2 stand-alone utilities are batch-oriented utilities that perform DB2 administrative activities outside the control of the DB2 subsystem (with the exception of DSN1SDMP ). This can be useful if an error makes the DB2 system inactive. For example, stand-alone utilities can be used to copy DB2 data sets and print formatted dumps of their contents without DB2 being active. Every DB2 specialist should have a working knowledge of the stand-alone utilities. The stand-alone utilities are

DSNJLOGF	Log pre-format utility
DSNJU003	Change log inventory utility
DSNJU004	Print log map utility
DSN1CHKR	DB2 Catalog and DB2 Directory verification utility
DSN1COMP	Data compression analysis utility
DSN1COPY	Offline table space copy utility
DSN1SDMP	Dump and trace utility
DSN1LOGP	Recovery log extractor utility
DSN1PRNT	Formatted table space dump utility

Only technical support personnel who understand the intricacies of DB2 logging should use these utilities. As such, only the DB2 systems programmer or DBA who installs and maintains the DB2 system should use these utilities. A brief introduction to these utilities, however, should increase your overall understanding of DB2 logging.

The DB2 Log Preformat Utility ( DSNJLOGF )

DSNJLOGF , the DB2 log preformat utility, preformats DB2 active log data sets. The execution of this utility is not mandatory for new active log data sets. However, if DSNJLOGF has not been run prior to the first write activity for the log, DB2 will preformat the log at that time, incurring a delay. Sample JCL is provided in Listing 35.1.

Listing 35.1. DSNJLOGF JCL

 //DB2JOBU  JOB (UTILITY),'DSNJLOGF',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //*       DB2 LOG PREFORMAT //**************************************************************** //* //*  Preformat the DB2 active log data sets //* //PREF11 EXEC PGM=DSNJLOGF //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSUT1  DD  DSN=DSN510.LOGCOPY1.DS01,DISP=SHR //* //PREF12 EXEC PGM=DSNJLOGF //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSUT1  DD  DSN=DSN510.LOGCOPY1.DS02,DISP=SHR //* //PREF21 EXEC PGM=DSNJLOGF //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSUT1  DD  DSN=DSN510.LOGCOPY2.DS01,DISP=SHR //* //PREF22 EXEC PGM=DSNJLOGF //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSUT1  DD  DSN=DSN510.LOGCOPY2.DS02,DISP=SHR // 

DSNJLOGF Guidelines

Use the following guidelines when running DSNJLOGF .

Use DSNJLOGF

Execute the DSNJLOGF utility instead of allowing DB2 to preformat the active log data set during processing. This will eliminate delays due to log data set preformatting.

Interpret Timestamps in the Log Map Correctly

The timestamps shown in the reports produced by DSNJLOGF can be confusing to interpret properly. Timestamps in the column labeled LTIME are in local time; all other timestamps are in Greenwich Mean Time (GMT).

The Change Log Inventory Utility ( DSNJU003 )

DSNJU003 , better known as the Change Log Inventory utility, modifies the bootstrap data set (BSDS). Its primary function is to add or delete active and archive logs for the DB2 subsystem. Sample JCL to add an archive log data set is provided in Listing 35.2.

Listing 35.2. DSNJU003 JCL (Change Log Inventory)

 //DB2JOBU  JOB (UTILITY),'DSNJU003',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //*       DB2 CHANGE LOG INVENTORY //**************************************************************** //* //DSNJU003 EXEC PGM=DSNJU003 //SYSUT1  DD  DSN=DB2CAT.BSDS01,DISP=OLD //SYSUT2  DD  DSN=DB2CAT.BSDS02,DISP=OLD //SYSIN    DD  * NEWLOG DSNAME=DB2CAT.FIRST.COPY,COPY1 NEWLOG DSNAME=DB2CAT.SECOND.COPY,COPY2 /* // 

DSNJU003 Utility Guidelines

Use the following tips when running DSNJU003 .

Be Sure DB2 Is Down Before Using DSNJU003

It is best to run the DSNJU003 utility in batch only when DB2 is not running. Although you can run DSNJU003 when DB2 is up and running, the results can be inconsistent.

Use Caution with Data Sharing

Using DSNJU003 to change a BSDS for a data sharing member can cause a log read request from another data sharing member to fail. This will happen when the second member tries to access the changed BSDS before the first member is started.

Use DSNJU003 with RESTORE SYSTEM

DB2 V8 adds a new option to DSNJU003 so that it can be used in conjunction with the new RESTORE SYSTEM utility. Before running RESTORE SYSTEM , run DSNJU003 with the SYSPITR option to specify the log truncation point for the point in time that you want to use for system recovery. Specify either the log RBA (if you are not data sharing) or the log LRSN (if you are data sharing) to the SYSPITR parameter.

Consider Using DSNJU003 to Rectify CCSID Information

You can use DSNJU003 with the CCSID parameter on a DELETE statement to remove the CCSID information in the BSDS.

CAUTION

This option should only be attempted under direction from IBM technical support and only when the CCSID information in the BSDS is incorrect.

The Print Log Map Utility ( DSNJU004 )

DSNJU004 , also referred to as the Print Log Map utility, is used to list the contents of the BSDS, which includes a status of the logs. All of the following information will be output by DSNJU004 :

• Log data set name , log RBA association, and log LRSN for both copies of all active and archive log data sets

• Active log data sets that are available for new log data

• Status of all conditional restart control records in the bootstrap data set

• Contents of the queue of checkpoint records in the BSDS

• The communication record of the BSDS (if one exists)

• Contents of the quiesce history record

• System and utility timestamps

• Contents of the checkpoint queue

• Archive log command history

• BACKUP SYSTEM utility history

• System CCSID information

Sample JCL for DSNJU004 is provided in Listing 35.3.

Listing 35.3. DSNJU004 JCL (Print Log Map)

 //DB2JOBU  JOB (UTILITY),'DSNJU004',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //*       DB2 PRINT LOG MAP //**************************************************************** //* //DSNJU004 EXEC PGM=DSNJU004 //SYSUT1  DD  DSN=DB2CAT.BSDS01,DISP=SHR //SYSPRINT DD SYSOUT=* // 

DSNJU004 Utility Guideline

Use the following tip when running DSNJU004 .

Use DSNJU004 for Documentation

Run DSNJU004 , the print log map utility, before and after running the change log utility. You can use the output of DSNJU004 to document the log change being implemented.

The Catalog Integrity Verification Utility ( DSN1CHKR )

DSN1CHKR , the Catalog Integrity Verification utility, verifies the integrity of the DB2 Catalog and DB2 Directory. Sample JCL is provided in Listing 35.4.

Listing 35.4. DSN1CHKR JCL

 //DB2JOBU  JOB (UTILITY),'DSN1CHKR',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //*       DB2 CATALOG CHECK UTILITY //**************************************************************** //* //*  Verifies the integrity of the SYSPLAN table space //* //CHECK EXEC PGM=DSN1CHKR,PARM='FORMAT' //SYSUT1  DD  DSN=DB2CAT.DSNDBC.DSNDB06.SYSPLAN.I0001.A001,DISP=SHR //SYSPRINT DD SYSOUT=* // 

CAUTION

The SYSUTILX and SYSLGRNX tables are not checkable using DSN1CHKR . This is true even though the predecessors to these tables were checkable ( SYSUTIL prior to DB2 V3 and SYSLGRNG prior to DB2 V4).

DSN1CHKR Guidelines

Review the following techniques when using DSN1CHKR to verify the integrity of DB2 Catalog table spaces.

Schedule DSN1CHKR Runs Regularly

Execute the DSN1CHKR utility for the DB2 Catalog and DB2 Directory weekly to catch problems early, before they affect program development and testing in your test DB2 subsystems or business availability and production processing in your production DB2 subsystems.

Consider Starting the DB2 Catalog in Read-Only Mode

For the results of DSN1CHKR to be 100 percent accurate, DB2 must be down or the table spaces being checked must be started in read-only mode (or stopped ). To minimize the outage , consider copying the table spaces to be checked to VSAM files. The VSAM files can be checked instead of the actual DB2 Catalog table spaces. It should take less time to copy the files to VSAM than to check the actual table space data sets. You can also run DSN1CHKR against a copy created using DSN1COPY .

CAUTION

Regardless of the status of the catalog, you must be able to verify that the catalog files being checked do not have pages in the buffer pool that might have been modified.

Take Additional DB2 Catalog Verification Steps

In addition to running DSN1CHKR , consider the following steps to ensure DB2 Catalog integrity:

• Run DSN1COPY with the check option against all DB2 Catalog indexes and table spaces.
  
  
• Run the CHECK INDEX utility against all catalog indexes.
  
  

Use DSN1CHKR on Valid Table Spaces Only

Several of the DB2 Catalog and DB2 Directory table spaces are not able to be checked using DSN1CHKR . Therefore, be sure to execute DSN1CHKR only on the following system table spaces:

 DSNDB01.DBD01 DSNDB06.SYSDBASE DSNDB06.SYSDBAUT DSNDB06.SYSGROUP DSNDB06.SYSPLAN DSNDB06.SYSVIEWS 

The Compression Analyzer ( DSN1COMP )

The DSN1COMP utility can be used to analyze DB2 data and approximate the results of DB2 data compression. DSN1COMP can be run on a table space data set, a sequential data set containing a DB2 table space or partition, a full image copy data set, or an incremental image copy data set. It will provide the following statistics:

• Space used with compression

• Space used without compression

• Percentage of bytes saved by using compression

• Total pages required with compression

• Total pages required without compression

• Percentage of pages saved by using compression

• Number of dictionary entries

• Number of dictionary pages required

• Average size of a compressed row

CAUTION

DSN1COMP cannot be run against compressed objects. Because the compression dictionary can age, it can be difficult to determine when to replace the dictionary because DSN1COMP cannot be used for this purpose.

Sample DSN1COMP JCL is provided in Listing 35.5. This job reads the VSAM data set for the DSN8D81A.DSN8S81D table space specified in the SYSUT1 DD statement and analyzes the data producing estimated compression statistics.

Listing 35.5. DSN1COMP JCL

 //DB2JOBU  JOB (UTILITY),'DB2 DSN1COMP',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 DSN1COMP UTILITY //* //**************************************************************** //* //JOBLIB DD DSN=DSN810.DSNLOAD,DISP=SHR //DSN1COMP EXEC PGM=DSN1COMP,PARM='ROWLIMIT(20000)' //SYSPRINT DD  SYSOUT=* //SYSUDUMP DD  SYSOUT=* //SYSUT1 DD DSN=DB2CAT.DSNDBC.DSN8D81A.DSN8S81D.I0001.A001,DISP=SHR,AMP=('BUFND=181') // 

There are numerous parameters that can be supplied to the DSN1COMP utility. The following are the most commonly used parameters:

• FREEPAGE Indicates the frequency of inserting a completely blank page when calculating the percentage of pages saved. The default is 0. You should specify the same value used for FREEPAGE in the CREATE TABLESPACE DDL for the table space being analyzed .

• PCTFREE Specifies the percentage of each page to leave free when calculating the percentage of pages saved. The default is 5. Once again, you should specify the same value used for PCTFREE in the CREATE TABLESPACE DDL for the table space being analyzed.

• FULLCOPY Indicates that a full image copy is being used as input. If the table space is partitioned, you should also use the NUMPARTS parameter.

• INCRCOPY Indicates that an incremental image copy is used as input. Once again, for partitioned table spaces, you should also specify the NUMPARTS parameter.

• REORG Indicates that the estimate should be based on the compression savings achievable by the REORG utility. If REORG is not specified, the estimate is the savings that the LOAD utility would achieve.

• ROWLIMIT Specifies the maximum number of rows to evaluate to provide the compression estimate. You should use this option to limit the elapsed and processor time required by DSN1COMP .

DSN1COMP Guidelines

To ensure effective compression planning, consider the following guidelines as you execute the DSN1COMP utility.

Utilize DSN1COMP to Plan for Compression

Execute the DSN1COMP utility for table spaces that are candidates for compression. The statistics provided by this utility can be analyzed to determine whether compression will be cost-effective .

In general, contrast the percentage of pages saved when using compression against the anticipated increase in CPU time to determine whether compression is desirable. The CPU increase should be negligible when DB2 is using hardware compression.

Determine Proper Prefix

Before running DSN1COMP on a table space, first you must determine the actual data set name for the table space to be examined. This information can be found in SYSIBM.SYSTABLEPART ; for example:

 SELECT DBNAME, TSNAME, PARTITION, IPREFIX FROM   SYSIBM.SYSTABLEPART WHERE  DBNAME = ? AND    TSNAME = ?; 

Substitute the database name and table space name for the DBNAME and TSNAME predicates. Then, use the IPREFIX results to code the appropriate data set name. IPREFIX will contain either an "I" or a "J" depending on which version of the data set is currently active. If IPREFIX contains "J" then the data set should be coded as:

 //SYSUT1 DD DSN=DB2CAT.DSNDBC.dbname.tsname.  J  0001.A001,DISP=SHR 

If IPREFIX contains "I" then the data set should be coded as:

 //SYSUT1 DD DSN=DB2CAT.DSNDBC.dbname.tsname.  I  0001.A001,DISP=SHR 

Use the DSSIZE Parameter for Large Table Spaces

The DSSIZE parameter is used to specify the data set size in gigabytes. The preferred method of specifying a large table space is to use the DSSIZE parameter instead of the LARGE parameter.

So, you should specify DSSIZE(4G) or greater to DSN1COMP when you are analyzing a large table space. If you omit DSSIZE , DB2 assumes that the input data set size is 2GB. Of course, if DSSIZE is not specified and the data set is not one of the default sizes, the results of DSN1COMP will be unpredictable.

Use the PAGESIZE Parameter

The PAGESIZE parameter of DSN1COMP should be used to specify the page size of the input data set for SYSUT1 . Any of the valid page sizes4K, 8K, 16K, or 32Kare valid. If you specify an incorrect page size, the results of DSN1COMP will be unpredictable.

DSN1COMP will try to determine the proper page size if you fail to specify a PAGESIZE value. If DSN1COMP cannot determine the page size, then the utility will fail and an error message will be issued.

The Offline Copy Utility ( DSN1COPY )

The Offline Copy utility, better known as DSN1COPY , has a multitude of uses. For example, it can be used to copy data sets or check the validity of table space and index pages. Another use is to translate DB2 object identifiers for the migration of objects between DB2 subsystems or to recover data from accidentally dropped objects. DSN1COPY also can print hexadecimal dumps of DB2 table space and index data sets.

Its first function, however, is to copy data sets. DSN1COPY can be used to copy VSAM data sets to sequential data sets, and vice versa. It also can copy VSAM data sets to other VSAM data sets and can copy sequential data sets to other sequential data sets. As such, DSN1COPY can be used to

• Create a sequential data set copy of a DB2 table space or index data set.

• Create a sequential data set copy of another sequential data set copy produced by DSN1COPY .

• Create a sequential data set copy of an image copy data set produced using the DB2 COPY utility, except for segmented table spaces. (The DB2 COPY utility skips empty pages, thereby rendering the image copy data set incompatible with DSN1COPY .)

• Restore a DB2 table space or index using a sequential data set produced by DSN1COPY .

• Restore a DB2 table space using a full image copy data set produced using the DB2 COPY utility.

• Move DB2 data sets from one disk pack to another to replace DASD (such as migrating from 3380s to 3390s).

• Move a DB2 table space or index space from a smaller data set to a larger data set to eliminate extents. Or, move a DB2 table space or index space from a larger data set to a smaller data set to eliminate wasted space.

CAUTION

If you change the allocation size of a DB2 data set using DSN1COPY , be sure also to change the PRIQTY and SECQTY values for the object to reflect the change in the DB2 Catalog.

DSN1COPY runs as an MVS batch job, so it can run as an offline utility when the DB2 subsystem is inactive. It can run also when the DB2 subsystem is active, but the objects it operates on should be stopped to ensure that DSN1COPY creates valid output.

CAUTION

DSN1COPY performs a page-by-page copy. Therefore, you cannot use DSN1COPY to alter the structure of DB2 data sets. For example, you cannot copy a partitioned tablespace into a simple tablespace.

DSN1COPY does not check to see whether an object is stopped before carrying out its task. DSN1COPY does not directly communicate with DB2.

NOTE

If DB2 is operational and an object is not stopped, DSN1COPY cannot use DISP=OLD for the data set being copied .

Sample DSN1COPY JCL is provided in Listing 35.6. This job reads the VSAM data set for the DSN8D81A.DSN8S81D table space specified in the SYSUT1 DD statement and then copies it to the sequential data set specified in the SYSUT2 DD statement.

Listing 35.6. DSN1COPY JCL

 //DB2JOBU  JOB (UTILITY),'DB2 DSN1COPY',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 DSN1COPY UTILITY //* //**************************************************************** //* //JOBLIB DD DSN=DSN810.DSNLOAD,DISP=SHR //STOPDB EXEC PGM=IKJEFT01,DYNAMNBR=20 //STEPLIB DD DSN=DSN810.DSNEXIT,DISP=SHR //        DD DSN=DSN810.DSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSOUT  DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSTSIN  DD * DSN SYSTEM (DSN) -STOP DATABASE (DSN8D81A) SPACENAM(DSN8S81D) END /* //DSN1COPY EXEC PGM=DSN1COPY,PARM='CHECK' //SYSPRINT DD  SYSOUT=* //SYSUDUMP DD  SYSOUT=* //SYSUT1 DD DSN=DB2CAT.DSNDBC.DSN8D81A.DSN8S81D.I0001.A001,DISP=OLD,AMP=('BUFND=181') //SYSUT2 DD DSN=OUTPUT.SEQ.DATASET,DISP=OLD,DCB=BUFNO=20 /* //STARTRW EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=EVEN //STEPLIB DD DSN=DSN810.DSNEXIT,DISP=SHR //        DD DSN=DSN810.DSNLOAD,DISP=SHR //* //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSOUT  DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSTSIN  DD * DSN SYSTEM (DSN) -START DATABASE (DSN8D81A) SPACENAM(DSN8S81D) END /* // 

One of the best features of the DSN1COPY utility is its capability to modify the internal object identifier stored in DB2 table space and index data sets, as well as in data sets produced by DSN1COPY and the DB2 COPY utility. When you specify the OBIDXLAT option, DSN1COPY reads a data set specified by the SYSXLAT DD statement. This data set lists source and target DBIDs , PSIDs or ISOBIDs , and OBIDs .

CAUTION

The DSN1COPY utility can only translate up to 500 record OBIDs at a time.

NOTE

DBIDs , PSIDs or ISOBIDs , and OBIDs are internal identifiers used by DB2 internally to uniquely identify database objects.

Each record in the SYSXLAT file must contain a pair of integers separated by a comma. The first integer is the source ID and the second integer is the target ID. The first record in the SYSXLAT file contains the source and target DBIDs . The second record contains the source and target PSIDs or ISOBIDs for indexes. All subsequent records in the SYSXLAT data set are OBIDs for tables.

CAUTION

Be careful with type 2 indexes when using old JCL that worked with type 1 indexes. Only the first two records were required for a type 1 index. For a type 2 index, the SYSXLAT data set must contain the table OBID in addition to the DBID and ISOBID .

For example, assume that you accidentally dropped the DSN8D81A database after the JCL in Listing 35.6 was run. Because this database uses STOGROUP -defined objects, all the data has been lost. However, after re-creating the database, table spaces, tables, and other objects for DSN8D81A , you can restore the DSN8S81D table space using DSN1COPY with the OBIDXLAT option. Consider the sample JCL using this option as shown in Listing 35.7. It is operating on the sequential data set produced in Listing 35.6, copying it back to the data set for the DSN8D81A.DSN8S81D table space. This job translates the DBID for database DSN8D81A from 283 to 201, the PSID for the DSN8S81D table space from 0002 to 0003, and the OBID for the DSN8810.DEPT table from 0020 to 0008.

Listing 35.7. DSN1COPY JCL (Using the OBIDXLAT Option)

 //DB2JOBU  JOB (UTILITY),'DB2 DSN1COPY',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 DSN1COPY UTILITY //* //**************************************************************** //* //JOBLIB DD DSN=DSN810.DSNEXIT,DISP=SHR //       DD DSN=DSN810.DSNLOAD,DISP=SHR //DSN1COPY EXEC PGM=DSN1COPY,PARM='OBIDXLAT' //SYSPRINT DD  SYSOUT=* //SYSUDUMP DD  SYSOUT=* //SYSUT1 DD DSN=DB2CAT.DSNDBC.DSN8D81A.DSN8S81D.I0001.A001,DISP=OLD,AMP=(''BUFND=81') //SYSUT2 DD DSN=DB2CATP.DSNDBC.DSN8D81A.DSN8S81D.I0001.A001,DISP=OLD,AMP=('BUFND=181') //* //*  The SYSXLAT input will :: //*       Translate the DBID 283 (sending) into 201 on //*       the receiving end. //*       Translate the OBID 2 (sending) into 3 on the //*       receiving end. //*       Translate the PSID 20 (sending) into 8 on the //*       receiving end. //* //SYSXLAT DD * 283  201 2    3 20   8 /* // 

The object identifiers for the old objects can be found in two ways. First, you can scan old DBID / PSID / OBID reports. Second, you can use DSN1PRNT to list the first three pages of the copy data set. The object identifiers are shown in the formatted listing produced for those pages. Obtain the new object identifiers using the DB2 Catalog reports listed in Chapter 26, "DB2 Object Monitoring Using the DB2 Catalog and RTS."

DSN1COPY Guidelines

When planning your DSN1COPY jobs, be sure to consult the following tips and guidelines.

Issue the Stop Command Before Running DSN1COPY

Never run the DSN1COPY utility for a DB2 object until it has been explicitly stopped for all access in the appropriate DB2 subsystem. This advice can be ignored if DB2 is not active.

Use DSN1PRNT Instead of DSN1COPY for Hex Dumps

Although DSN1COPY can be used to obtain a hex dump of a DB2 data set, favor the use of DSN1PRNT because it produces a listing that is formatted, and thus easier to use.

Estimate the Size of SYSUT2 Based on 4KB Pages

When the SYSUT2 data set is a sequential data set, estimate its size using the following formula:

 (Number of pages) x 4096 

Specify the space parameter in cylinders by rounding this number up to the next whole cylinder. If the object being copied uses a page size other than 4KB, use the following formulas:

For 8KB pages, multiply the number by two

For 16KB pages, multiply the number by four

For 32KB pages multiply the number by eight

Also, remember to specify the appropriate PAGESIZE option of DSN1COPY : 4KB, 8KB, 16KB, or 32KB.

The total number of pages used by a table space can be retrieved from the VSAM LISTCAT command or the DB2 Catalog as specified in the NACTIVE column of SYSIBM.SYSTABLESPACE . If you are using the DB2 catalog method, ensure that the statistics are current by running the RUNSTATS utility.

Optimize the BUFND Parameter

The default for the BUFND parameter is 2, which is too low. In order to boost I/O performance, change the BUFND parameter to a larger value. BUFND specifies the number of I/O buffers that VSAM will use for data records.

When using 3390 disk devices, consider coding BUFND=181 which will hold a complete cylinder.

CAUTION

Be sure to provide an adequate region size for your job to use 181 buffers. If the region size for your job is too small to allocate 181 data buffers, your job will fail. The message returned will be a DSN1996I indicating VSAM open error for the data set.

Use the PAGESIZE Parameter

The PAGESIZE parameter of DSN1COPY should be used to specify the page size of the input data set for SYSUT1 . Any of the valid page sizes4K, 8K, 16K, or 32Kare valid. If you specify an incorrect page size, the results of DSN1COPY will be unpredictable.

DSN1COPY will try to determine the proper page size if you fail to specify a PAGESIZE value. If DSN1COPY cannot determine the page size then the utility will fail and an error message will be issued.

Determine Proper Prefix

Before running DSN1COPY on a table space, first you must determine the actual data set name for the table space to be examined. This information in SYSIBM.SYSTABLEPART . For example,

 SELECT DBNAME, TSNAME, PARTITION, IPREFIX FROM   SYSIBM.SYSTABLEPART WHERE  DBNAME = ? AND    TSNAME = ?; 

Substitute the database name and table space name for the DBNAME and TSNAME predicates. Then, use the IPREFIX results to code the appropriate data set name. IPREFIX will contain either an "I" or a "J" depending on which version of the data set is currently active. If IPREFIX contains "J" then the data set should be coded as

 //SYSUT1 DD DSN=DB2CAT.DSNDBC.dbname.tsname.  J  0001.A001,DISP=SHR 

If IPREFIX contains "I" then the data set should be coded as

 //SYSUT1 DD DSN=DB2CAT.DSNDBC.dbname.tsname.  I  0001.A001,DISP=SHR 

You must specify the correct fifth level qualifier in the data set name for DSN1COPY to successfully copy the table space data.

Do Not Use DSN1COPY on Log Data Sets

Avoid using the DSN1COPY utility on DB2 log data sets because certain options can invalidate the log data.

Use Appropriate Options with LOB Table Spaces

You can use DSN1COPY on LOB table spaces, but you cannot specify the SEGMENT or INLCOPY options. Use the LOB keyword to use DSN1COPY with a LOB table space.

Use Appropriate Options with Large Table Spaces

Use the DSSIZE parameter to specify the size of data sets that exceed 2GB (4GB for LOB table spaces). If you fail to specify this parameter, DB2 will assume that the size of the input data set is 2GB. DSN1COPY results will be unpredictable if the DSSIZE parameter is not coded for data sets that exceed 2GB.

When specifying the DSSIZE parameter, the size specified must match exactly the value used when the table space was defined.

You can specify LARGE instead of DSSIZE if the table space was defined with the LARGE parameter. However, it is better to use DSSIZE(4G) instead of LARGE because the LARGE parameter is being phased out in favor of DSSIZE .

Use Caution When Copying Data with an Identity Column

When you are using DSN1COPY to copy table data where an identity column exists, you must take extra steps to set up the identity values appropriately. To accomplish this, the following steps should be taken:

1. Be sure to stop the table space on the source DB2 subsystem.
   
   
2. Find the sequence information in the DB2 Catalog for the identity column being copied. That information can be retrieved from SYSIBM.SYSSEQUENCES . Add the INCREMENT value to the MAXASSIGNEDVAL to let DSN1COPY assign the next value.
   
   
3. Create the table on the target subsystem. On the identity column specification, specify that previously calculate the next value for the START WITH value. Also, be sure to code all of the other attributes of the identity column exactly as coded for the table on the source subsystem.
   
   
4. Stop the table space on the target subsystem.
   
   
5. Copy the data using DSN1COPY .
   
   
6. Restart the table space on the source subsystem with the appropriate level of access.
   
   
7. Start the table space on the target subsystem.
   
   

The DB2 Dump and Trace Program ( DSN1SDMP )

DSN1SDMP is the IFC selective dump utility. Although technically defined by IBM to be a service aid utility, DSN1SDMP is actually a DB2 application program. It must be run under the TSO terminal monitor program, IKJEFT01 . DSN1SDMP , unlike the other service aids, can be run only when DB2 is operational.

Using the Instrumentation Facility Interface, DSN1SDMP can write DB2 trace records to a sequential data set named in the SDMPTRAC DD statement. It can also force system dumps for DB2 utilities or when specific DB2 events occur. For shops without a DB2 performance monitor, DSN1SDMP can come in handy in trying to resolve system problems. Sample JCL is shown in Listing 35.8.

Listing 35.8. DSN1SDMP JCL

 //DB2JOBU  JOB  (UTILITY),'DSN1SDMP',MSGCLASS=X,CLASS=X, //        NOTIFY=USER //* //**************************************************************** //* //*       DB2 FORCE DUMP UTILITY  : : //*         CONSULT IBM BEFORE RUNNING //* //**************************************************************** //* //JOBLIB DD DSN=DSN810.DSNLOAD,DISP=SHR //DUMPER EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD  SYSOUT=* //SYSPRINT DD  SYSOUT=* //SYSUDUMP DD  SYSOUT=* //SDMPPRNT DD SYSOUT=* //SDMPTRAC DD DSN=CAT.TRACE.SEQ.DATASET, //        DISP=(MOD,CATLG,CATLG),SPACE=(8192,(100,100)),UNIT=SYSDA, //        DCB=(DSORG=PS,RECFM=VB,LRECL=8188,BLKSIZE=8192) //SYSTSIN  DD  * DSN SYSTEM(DSN) RUN PROGRAM(DSN1SDMP)  PLAN(DSN1SDMP)  - LIB('DSN810.RUNLIB.LOAD') END /* //SDMPDD  * CONSULT IBM BEFORE USING IBM SUPPORT CENTER WILL PROVIDE OPTIONS /* // 

DSN1SDMP Data Sets

SDMPIN	Input parameters to the DSN1SDMP utility
SDMPPRNT	DSN1SDMP output messages
SYSABEND	System dump if DSN1SDMP abends
SDMPTRAC	Output trace records

DSN1SDMP Guidelines

You can use the following guidelines as a blueprint for effective DSN1SDMP usage.

Use DSN1SDMP Only As Directed

DSN1SDMP should be used only under instructions from the IBM Support Center.

Be Sure That the User Has the Authority to Run DSN1SDMP

To execute the DSN1SDMP service aid, the requester must have the requisite authority to start and stop the DB2 traces, as well as the MONITOR1 or MONITOR2 privilege.

The Recovery Log Extractor ( DSN1LOGP )

DSN1LOGP , otherwise known as the Recovery Log Extractor, produces a formatted listing of a specific DB2 recovery log. When an active log is operated on by DSN1LOGP , an active DB2 subsystem must not be currently processing the log. Any archive log can be processed at any timewhether DB2 is operational or not.

DSN1LOGP produces a detailed or a summary report. The detailed report displays entire log records. The summary report condenses the log records, displaying only the information necessary to request a partial recovery. As such, the detailed report is rarely used. Sample JCL is shown in Listing 35.9.

Listing 35.9. DSN1LOGP JCL

 //DB2JOBU  JOB (UTILITY),'DSN1LOGP',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 RECOVERY LOG EXTRACTOR //* //**************************************************************** //* //DSN1LOGP PGM=DSN1LOGP //SYSPRINT DD SYSOUT=* //SYSABEND DD SYSOUT=* //SYSSUMRY DD SYSOUT=* //BSDS DD DSN=DB2CAT.BSDS01,DISP=SHR //SYSIN DD * RBASTART(E300F4) RBAEND(F40000) SUMMARY(YES) /* // 

DSN1LOGP Guidelines

The following techniques can be used to produce effective log extract reports using the DSN1LOGP service aid.

Do Not Run DSN1LOGP on the Active Log

DSN1LOGP cannot be run on the active log that DB2 is currently using for logging. It can be run on the other active logs as well as on the archive logs. Given this caveat, DSN1LOGP can be run while DB2 is operational.

Use the DSN1LOGP Output to Assist in Recovery

You can use the output report produced by the DSN1LOGP service aid utility to determine an appropriate log RBA for partial recovery by the RECOVER TORBA utility. This method should be used only when an appropriate log RBA is available in the SYSIBM.SYSCOPY table as the result of running the QUIESCE utility.

The DB2 Data Set Dump Creator ( DSN1PRNT )

The program name for the DB2 Data Set Dump Creator is DSN1PRNT . It can be used to print hexadecimal and formatted dumps of DB2 table space, indexspace, and image copy data sets. It is useful for searching for values and dumping only the pages containing the specified value. Sample JCL is in Listing 35.10.

Listing 35.10. DSN1PRNT JCL

 //DB2JOBU  JOB (UTILITY),'DSN1PRNT',MSGCLASS=X,CLASS=X, //          NOTIFY=USER //* //**************************************************************** //* //*       DB2 DATA SET DUMP SERVICE AID //* //**************************************************************** //* //DSN1PRNT PGM=DSN1PRNT,PARM='PRINT,FORMAT' //SYSPRINT DD SYSOUT=* //SYSUT1 DD DSN=DB2CAT.DSNDBC.DSN8D81A.DSN8S81D.I0001.A001,DISP=SHR,AMP=('BUFND=181') // 

DSN1PRNT Guidelines

Consider the following guidelines when using DNS1PRNT to dump DB2 data sets.

Analyze Problems Using DSN1PRNT Output

Use DSN1PRNT to track down data problems and page errors. By scanning the dump of a DB2 data set, you can view the format of the page and the data on the page.

Be Aware of Potential Errors

If DSN1PRNT encounters an error on a page of a DB2 data set, an error message is printed. If you specified the FORMAT option, the output is not formatted. All pages without errors are formatted.

Use DSN1PRNT for All DB2 Data Set Dumps

Favor the use of DSN1PRNT over other data set dump utilities (such as DSN1COPY ) because of the formatting feature of DSN1PRNT .

Run DSN1PRNT Only for Stopped DB2 Objects

When running DSN1PRNT when DB2 is active, be sure that the data set being dumped has been stopped. This ensures that the data being dumped is accurate and unchanging.

Of course, if your shop requires 24x7 availability stopping DB2 is not an option. You can run DSN1PRNT with DB2 up and running, but you will have to live with the possibility of data anomalies.

Be Aware of Data Set Page Sizes

If the object being dumped uses non-4KB pages, remember to specify the PAGESIZE option of DSN1PRNT . Specify the appropriate page size for the data set being printed: 4KB, 8KB, 16KB, or 32KB.

Use the PAGESIZE Parameter

The PAGESIZE parameter of DSN1PRNT should be used to specify the page size of the input data set for SYSUT1 . Any of the valid page sizes4K, 8K, 16K, or 32Kare valid. If you specify an incorrect page size, the results of DSN1PRNT will be unpredictable.

DSN1PRNT will try to determine the proper page size if you fail to specify a PAGESIZE value. If DSN1PRNT cannot determine the page size, then the utility will fail and an error message will be issued.

Use Appropriate Options for LOB Table Spaces

You can use DSN1PRNT with LOB table spaces. To do so, be sure to specify the LOB parameter, and do not specify the INLCOPY parameter.