kfogarty.com - Database Consultants

It is best enforce a standard whereby SELECT * is not used in any host programs.

The SELECT * statement is a temptation because it allows the programmer to avoid having to explicitly name each of the required columns. However, this convienience can be costly in terms of CPU time, elapsed time and maintenance of host programs.

When an SQL statement SELECTs data for use by a program, the program must have a host variable to receive a value from each column selected. If a column were added to the table, the embedded SELECT * would automatically pass its value to the program. If there is no host variable defined for the new column, there is no place to store the returned value for access by the program.

Similarly, if a column is deleted from the table, SELECT * would not return the values expected by the program, as one of the columns is no longer present.

If the columns required by the program are listed explicitly, any changed and/or deleted columns would not affect the program unless the program explicitly references a column that has been removed.

As stated above, SELECT * is a temptation to code because it avoids the programmer having to name each of the columns required by the program. However, this convenience can have a detrimental effect on CPU time, elapsed time, and maintenance of host programs (see above).

If SELECT * is used within the code of a host program, there could be instances where an unnecessary number of columns are fetched from the database and passed back to the host program. (The more columns that are processed, the more CPU is required). Also, if there is a large number of columns returned, it is less likely that a sort can be done in a sort pool in central storage, and will increase the number of work files that must be written to DASD and then merged, before the rows are returned to the host program.

If the rows returned by the SELECT * is from a distributed process, the rows must be returned over the network, which can be expensive, and cause bottlenecks and other network/performance issues.

It increases the likelihood that a compiled column assignment procedure is greater than 4 KB at bind time and thus not stored in the plan or package. A compiled assignment procedure is used rather than an interpretative process to move columns to host variable output area. If more than 100 rows are fetched, the procedure is compiled each time the SQL is executed rather than only at bind time, which increases the CPU time.

Here are some general rules of thumb that can be used in a DB2 environment:

• Never use SELECT *:
  As a general rule, a query should never ask DB2 for anything more than is required to satisfy the desired task. For maximum flexibility and efficiency, each query should access only the columns needed for the function that will be performed.

• Singleton SELECT versus the cursor:
  To return a single row, an application program can use a cursor or a singleton SELECT. A cursor requires an OPEN, FETCH, and CLOSE to retrieve one row, whereas a singleton SELECT requires only SELECT… INTO. Usually, the singleton SELECT outperforms the cursor.

  When the selected row must be updated after it is retrieved, however, using a cursor with the FOR UPDATE OF clause is recommended over a singleton SELECT. The FOR UPDATE OF clause ensures the integrity of the data in the row because it causes DB2 to hold an exclusive lock on the page containing the row to be updated. The singleton select provides no such locking.

• Use FOR FETCH ONLY:
  When a SELECT statement will be used only for retrieval, use the FOR FETCH ONLY clause.
• Avoid using DISTINCT:

  The DISTINCT verb removes duplicate rows form an answer set. If duplicates will not cause a problem, do not code distinct, because it adds overhead by invoking a sort to remove the duplicates.

• Limit the data selected:
  Return the minimum number of columns and rows needed by your application program by making efficient use of the WHERE (SQL predicate) clause. It is almost always more efficient to allow DB2 to use the WHERE clause to limit the data returned.
• Code predicates on indexed columns:

  DB2 usually performs more efficiently when it can satisfy a request using an existing index rather than no index. Design all SQL statements to take advantage of indexes.

• Multicolumn indexes:
  If a table has only multicolumn indexes, try to specify the high-level column in the WHERE clause of your query. This results in an index scan with at least one matching column.
• Use ORDER BY only when sequence is important:
  Code the ORDER BY clause when the sequence of rows being returned is important. Order only those columns that are absolutely necessary in order to improve efficiency.

  Use equivalent data types: Use the same data types and lengths when comparing column values to host variables or literals. This eliminates the need for data conversion and allows for the use of an index. For example, comparing a column defined as CHAR(6) to a field which is CHAR(4) or CHAR(7) will cause data conversion and should be avoided at all costs. The easiest way to ensure datatype consistency is to use the DCLGEN fields whenever possible.

• Use BETWEEN rather than <= and >= :
  BETWEEN allows the optimizer to select a more efficient access path.
• Use IN instead of LIKE:
  If you know that only a certain number of occurrences exist, using IN with the specific list is more efficient than using LIKE. The functionality of LIKE can be imitated using a range of values. For example, if you want to retrieve all employees with a last name starting with “M,” use BETWEEN ‘maaaaaaaaaaaaaaa’ and ‘mzzzzzzzzzzzzzzz’ instead of LIKE ‘m%’
• Avoid using NOT (except with EXISTS):
  Not should only be used as an alternative to very complex predicates.

Informational APAR on migrating from DB2 V8 to DB2 V9 on z/OS. The text can be viewed by clicking here.

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.