kfogarty.com - Database Consultants

This informational APAR documents known DB2 storage problems. It can be viewed by clicking 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.

Some performance monitoring rules of thumb:

Do not overdo monitoring and tracing
DB2 performance monitoring can consume a tremendous amount of resources. Sometimes the associated overhead is worthwhile because the monitoring (problem determination or exception notification) can help alleviate or avoid a problem. However, absorbing a large CPU overhead for monitoring a DB2 subsystem that is already performing within the desired scope of acceptance is not worthwhile.

Excessive overhead caused by monitoring and tracing can turn a reasonably well performing DB2 subsystem into a poor one.

Plan and implement two types of monitoring strategies

1. Ongoing performance monitoring to eliminate exceptions
2. Develop procedures for monitoring exceptions after they have been observed.

use the correct tool for the job
It would be unwise to turn on a trace that causes 200 percent CPU overhead to solve a production problem that could be solved just as easily by other types of monitoring (using EXPLAIN or DB2 Catalog reports, for example).

Tuning should not be the be all and end all
Establish the goals of performance tuning in advance and stop when they have been achieved.
Tuning can go beyond the point at which reasonable gains can be realized for the amount of effort exerted.

Remember the 80/20 rule

80% of performance tuning results will come from 20% of your efforts.

Active Logs

DB2 records all data changes and various other significant events in a log as they occur. This log is stored on DASD, and is known as the ACTIVE LOG. It is recommended that a DB2 subsystem have at least three active log datasets, one in use, one offloading to archive and one more available to use.

The active logs are usually duplexed in order to reduce the possibility of integrity issues in the event of a DASD failure.

Archive Logs
Archive logs are off-loaded copies of an active log dataset and are registered in DB2 BSDS. The DB2 log offload process is initiated after the following events:

• When an active log dataset becomes full.
• DB2 starts and there is a full active log
• -ARCHIVE LOG command is issued
• An error occurs writing to an active log
• As the last active log dataset fills up

If dual logging is enabled, there are two archive logs.

The archive logs are sequential datasets that reside on DASD, MSS or tape. They can be managed by DFSMS and DFHSM to control migration levels, retention periods, management classes and so on.

The name of the archive log is chosen by DB2.

The DB2 BSDS keeps a specified number of archive logs, and the retention period can be specified.
Deleting an archive log does not remove the entry from the DB2 BSDS. The Change Log Inventory utility (DSNJU003) must be used.

There are no reliable methods to find out which plans and packages are not being used in a DB2 subsystem. However, some changes to DB2 objects will force a plan or package into invalid or inoperative status, and any objects found in this state can be candidates for removal.

The following query will find all plans and packages that are marked as invalid or inoperative in the DB2 catalog, and generate REBIND commands for each plan or package returned:


SELECT 'REBIND PLAN ('
CONCAT STRIP(NAME)
CONCAT ') EXPLAIN (NO) FLAG (E)'
FROM SYSIBM.SYSPLAN
WHERE VALID <> 'Y'
OR
OPERATIVE <> 'Y'
ORDER BY NAME
WITH UR;



SELECT 'REBIND PACKAGE ('
CONCAT STRIP('COLLID')
CONCAT '.'
CONCAT STRIP('NAME')
CONCAT '.(*) ) EXPLAIN (NO) FLAG (E) '
FROM SYSIBM.SYSPACKAGE
WHERE VALID <> 'Y'
OR
OPERATIVE <> 'Y'
ORDER BY COLLID, NAME
WITH UR;


After running the generated REBIND commands, check the SYSPLAN and SYSPACKAGE tables, and anything in those tables that has VALID = ‘N’ -and- OPERATIVE = ‘N’ is clearly not valid any more and those plans can be freed without any potential issues.

The following query will generate REBIND commands which will disable plans that are invalid or inoperative. This adds an extra level of security before the plans are actually freed.


SELECT 'REBIND PLAN ('
CONCAT STRIP(NAME)
CONCAT ') EXPLAIN (NO) FLAG (E) '
CONCAT 'DISABLE (BATCH,DLIBATCH,DB2CALL,CICS,IMS,IMSBMP,IMSMPP,RRSAF)'
FROM
SYSIBM.SYSPLAN
WHERE
VALID <> 'Y'
OR
OPERATIVE <> 'Y'
ORDER BY NAME
WITH UR;



SELECT 'REBIND PACKAGE ('
CONCAT STRIP("COLLID") CONCAT '.'
CONCAT STRIP("NAME") CONCAT '.(*) )', 'EXPLAIN (NO) FLAG (E)',
CONCAT 'DISABLE (BATCH,DLIBATCH,DB2CALL,CICS,IMS,IMSBMP,IMSMPP,REMOTE,RRSAF)'
FROM SYSIBM.SYSPACKAGE
WHERE
VALID <> 'Y'
OR
OPERATIVE <> 'Y'
ORDER BY COLLID, NAME
WITH UR;


The following queries will construct the necessary FREE PLAN statements:


SELECT 'FREE PLAN ('
CONCAT STRIP(NAME)
CONCAT ')'
FROM SYSIBM.SYSPLAN
WHERE VALID = 'N'
AND
OPERATIVE = 'N'
ORDER BY NAME
WITH UR;



SELECT 'FREE PACKAGE ('
CONCAT STRIP("COLLID")
CONCAT '.'
CONCAT STRIP("NAME")
CONCAT '.(' CONCAT VERSION CONCAT ') )'
FROM SYSIBM.SYSPACKAGE
WHERE
VALID = 'N'
AND
OPERATIVE = 'N'
AND
VERSION <> ' '
ORDER BY COLLID, NAME
WITH UR;



SELECT 'FREE PACKAGE ('
CONCAT STRIP("COLLID")
CONCAT '.'
CONCAT STRIP("NAME")
CONCAT ')'
FROM SYSIBM.SYSPACKAGE
WHERE
VALID = 'N'
AND
OPERATIVE = 'N'
AND
VERSION = ' '
ORDER BY COLLID, NAME
WITH UR;


It is important that anything related to DB2 itself is discarded from these generated lists. (Anything beginning with DSN, DSQ or QMF, for example).

Rebinding the plans with the DISABLE parameter means that the plans can be left in this status for a set period of time, and if no errors are returned after the set period of time, the plans can be freed.

Invalid Plans and Packages

This is DB2 R610 R710 R810 and IRLM R101 R220 storage usage error informational apar. It can be viewed by clicking here.

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.

Identity columns were available in DB2 Version 7 (but came into being after the V6 code refresh). The identity property is applied to a column using the IDENTITY parameter. A column defined with the identity property will cause DB2 to automatically generate a sequential value for that column, when a row is added to the table.

For example, identity columns might be used to generate primary key values.

Using identity columns helps to avoid some of the concurrency and performance problems that can occur when application programs are used to populate sequential values for a “counter” column.

When inserting data into a table that uses an identity column, the program or user will not provide a value for the identity column. Instead, DB2 automatically generates the appropriate value to be inserted.

Notes

• Only one identity column can be defined per DB2 table.
• The data type of the column must be SMALLINT, INTEGER or DECIMAL.

An example creating a table with an identity column follows:

CREATE TABLE SAMPLE
(ID_COL INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
START WITH 10
INCREMENT BY 1
...);

In this example, the identity column is named ID_COL. The initial value stored in the table is 10, and each insert will add 1 to the last value. After five inserts, the values in the identity column will be 10,11,12,13,14 and 15.

The GENERATED parameter indicates how DB2 generates values for the column. GENERATED must be specified if the column is to be considered an identity column or the data type of the column is ROWID. This means that DB2 must be permitted to generate values for all identity columns. There are two options for the GENERATED parameter: ALWAYS and BY DEFAULT.

• GENERATED ALWAYS indicates that DB2 will always generate a value for the column when a row is inserted into the table. ALWAYS is specified for identity columns unless data propagation is being used.
• GENERATED BY DEFAULT indicates that DB2 will generate a value for the column when a row is inserted into the table unless a value is specified. If an explicit value is to be inserted into an identity column GENERATED BY DEFAULT must be specified.

When the maximum value of the column is reached, there are options that need to be considered to allow insertion to continue.

The CYCLE keyword will cause DB2 to begin generating values from the minimum value all over again. This can generate duplicate values in the column, and should only be used when unique values in the identity column are not a requirement.

To ensure uniqueness in an identity column, a unique index needs to be created on the identity column. The IDENTITY property alone does not guarantee uniqueness.

Without identity columns, an application program can implement similar functionality, (there is a performance consideration to account for, as an application program cannot do this as efficiently as DB2).

A common technique is to maintain a one-row table that contains the sequence number. Each transaction locks that table, increments the number, and then commits the change to unlock the table. Only one transaction at a time can increment the sequence number, which could lead to a bottleneck.

Experience of Identity Columns at a Client Site

At one client site, identity columns were not used because of operational restrictions and problems in managing the different environments.

On request of the development teams, data would be copied from PROD(uction) to DEV(elopment) and ACC(eptance) using fast load/DSN1COPY utilities instead of using SQL inserts to refresh some databases, and the data propagation mechanism needed to be taken into account. (See general notes above).

Other site standards meant that all DB2 tables had to have a natural key as a primary key, not an “artificial” (generated by DB2) key. Most applications at the client site managed this by keeping a “highest used key” table. (See notes above).

In DB2 terms, a plan is an executable module containing the access path logic produced by the DB2 optimizer.

A plan can be composed of one or more DBRMs and packages.

Before a DB2 for z/OS program (with static SQL) can be run, it must have a plan associated with it.

Plans are created by the DB2 BIND command. The plan is stored in the DB2 directory and accessed when its program is run. Information about the plan is stored in the DB2 catalog.

A package is a single, bound DBRM with optimized access paths.

By using packages, the table access logic is “packaged” at a lower level of granularity than a plan — at the package (or program) level.

To execute a package, you first must include it in the package list of a plan (usually, there are some exceptions, such as for triggers and user-defined functions). In general, packages are not directly executed, they are only indirectly executed when the plan in which they are contained executes (UDFs and triggers are exceptions to this rule).

A plan can consist of one or more DBRMs, one or more packages or, a combination of packages and DBRMs.