kfogarty.com - Database Consultants

I recently dialled into a new teleconference, given by IBM called Maximising IBM DB2 V8 Using the DB2 Utilities. The conference was hosted by Bryan Smith, and is well worth an hour of anyone’s time. The link to the presentation, and the replay of the teleconference can be found here.

DSN1COPY is a DB2 for z/OS stand-alone utility that does not need DB2 active to execute. It provides function for copying table spaces from the DB2 VSAM data sets or from the DB2 image copy data sets.

All tables in the table space will be copied.

You can translate database object identifiers (OBIDs) to enable moving data sets between objects with different object identifiers in the same or between different subsystems and reset the log RBAs in the target data set. The following steps should be followed:

1. Start the table space as read-only.
2. Run the QUIESCE utility with the WRITE (YES) option to externalize all data pages and
   index pages.

DSN1COPY does not require DB2 authorization. However, usually the data set is protected by Resource Access Control Facility (RACF); if so, you need sufficient RACF authorization.

For more details, see: DB2 UDB for OS/390 and z/OS V7 Utility Guide and Reference.

DSN1COPY should only be used when you need to access the data outside DB2 or you need to do an OBID translation.

Under the direction of the IBM Support Center, the IFC Selective Dump (DSN1SDMP) utility can be used to:

• Force dumps when selected DB2 trace events occur.
• Write DB2 trace records to a user-defined MVS data set.

DSN1SDMP must be run via an MVS batch job, and be executed using the DSN TSO command processor. To execute DSN1SDMP, the DB2 subsystem has to be running.

The batch job will complete successfully only under the following conditions:

• The TRACE and any additional selection criteria started by DSN1SDMP meet the criteria specified in the FOR parameter.
• The TRACE started by DSN1SDMP is stopped using the STOP TRACE command.
• The job is canceled by the operator.

Sample JCL for using DSN1SDMP


//*
//STEP1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//*
//STEPLIB DD DSN=&DSN..SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SDMPPRNT DD SYSOUT=*
//SDMPTRAC DD DSN=&SYSUID..DSN1SDMP.TRACE.OUTPUT,
// UNIT=SYSDA,SPACE=(8192,(100,100)),
// DCB=(DSORG=PS,LRECL=8188,RECFM=VB,BLKSIZE=8192),
// DISP=(NEW,CATLG,CATLG)
//SDMPIN DD *
START TRACE(P) CLASS(32) IFCID(53,58) AUTHID(*) DEST(OPX)
FOR(1)
AFTER(1)
ACTION(ABENDTER(00E60188))
SELECT
* POSITION TO THE PRODUCT SECTION
P4,00
* INSURE QWHSIID = 58 OR 53 (NOT IFCID 4)
GE,04,X'0005'
* POSITION TO THE DATA SECTION 1
P4,08
* COMPARE SQLCODE IN QW0058SQ OR QW0053SQ
DR,74,X'FFFFFF98'
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(ssid)
RUN PROG(DSN1SDMP) PLAN(DSNEDCL)
/*

DSN1SDMP uses the following DD cards:

SDMPIN defines the control data set that specifies the input parameters to DSN1SDMP. This DD card is mandatory.
The LRECL of the file has to be 80, but only the first 72 columns are checked by DSN1SDMP.
SDMPPRNT Defines the sequential message data set used for DSN1SDMP messages. If the SDMPPRNT DD statement is omitted, no messages are written. The LRECL of SDMPPRNT is 131.
SYSABEND defines the data set to contain an ABEND dump in case DSN1SDMP abends. This DD card is optional.
SDMPTRAC defines the sequential DB2 trace record data set used for trace records returned to DSN1SDMP from DB2. This DD card is required only if trace data is written to an OPX trace destination. If the destination is anything other than an OPX buffer SDMPTRAC is ignored.

Trace records written to SDMPTRAC are of the same format as records written to SMF or GTF, except that, instead of containing the SMF or GTF headers, the SDMPTRAC trace records contain the monitor header (mapped by DSNDQWIW).

The DCB parameters are VB, BLKSIZE=32760, LRECL=32756.

SYSTSIN Defines the DSN commands to connect to DB2 and to execute an IFC selective dump:
DSN SYSTEM(subsystem name)
RUN PROG(DSN1SDMP) LIB('prefix.SDSNLOAD') PLAN(DSNEDCL)

The DB2 subsystem name must be filled in by the user. The DSN RUN command must specify a plan for which the user has execute authority.
DSN1SDMP dump does not execute the specified plan; the plan is only used to connect to DB2.

When no plan name is specified on the DSN RUN command, DSN defaults the plan name to the program. When DSN1SDMP is executed without a plan, DSN generates an error if no DSN1SDMP plan exists for which the user has execute authority.

Generating a Dump

All of the following must occur before DSN1SDMP generates a DB2 dump:

• DB2 produces a trace record that satisfies all of the selection criteria.
• An abend action (ABENDRET or ABENDTER) is specified.
• The AFTER and FOR conditions for the trace are satisfied.

If all of these three things occur, an 00E601xx abend occurs. xx is an integer between 1 and 99 that DB2 obtains from the user-specified value on the ACTION keyword.

Ensure that the generated 00E601xx abend is not suppressed by your system, by checking the contents of the SYS1.DAE dataset.

If DSN1SDMP must be stopped, use the DB2 STOP TRACE command.

The RUNSTATS utility collects statistics about DB2 objects.

These statistics can be stored in the DB2 catalog and are used during the bind process to choose the path in accessing data. If you never use RUNSTATS and subsequently rebind your packages or plans, DB2 will not have the information it needs to choose the most efficient access path. This can result in unnecessary I/O operations and excessive processor consumption.

RUNSTATS should be run against each table and its associated indexes at least once. How often the utility is rerun depends on how current the data stored in the catalog needs to be. If data characteristics of the table vary significantly over time, you should keep the catalog current with those changes. RUNSTATS is most beneficial for the following:

• Table spaces that contain frequently accessed tables.
• Tables involved in a sort.
• Tables with many rows.
• Tables against which SELECT statements having many search arguments are performed.

It is critical to execute RUNSTATS at these times:

• After a table and indexes have been created and the data loaded.
• After a tablespace or index has been reorganized and statistics have changed significantly.
• After heavy insert, update, and delete activity.
• Before binding and rebinding plans and packages.
• Before monitoring the catalog tables.

It is recommended to collect statistics during LOAD and REBUILD INDEX processing as well as during a tablespace reorganization.

See Also: