By Roger Sanders for Knowledge Xpert for DB2
Originally Posted November 2007 

There are several steps to consider in coding efficient SQL statements.

The First Step: The first step, and the goal, is to simply code SQL to achieve the required result. Let the optimizer determine the required processing to achieve the result. This gives good performance in the majority of the cases and has a number of advantages:

It results in a high level of data independence and minimizes the need to change application programs when the structure of the data changes, new columns are added or deleted, a column is increased in length, a new index is added, new access strategies are introduced into DB2, enhancements to the optimizer are made, or the computer model is changed.

It reduces the amount and complexity of host-language code that must be developed, tested, and maintained.

It also enhances performance by reducing the path length. The more work that can be pushed down to DB2's level, the fewer instructions that must be executed in the host program.

The Second Step: If the first step does not produce acceptable performance, the second step is to rewrite the SQL so that a better access path can be chosen by the optimizer. A number of hints, tips, guidelines, and alternatives are included in this section of topics and throughout the product that should help you with this task.

The Third Step: If the required performance is still not achieved, the third step and last resort is to break the SQL into parts and introduce more procedural code. However, it does lose the advantages of the first step and should be avoided in a distributed environment because that means more data must be transmitted in most cases.

Extract from Knowledge Xpert for DB2
Originally Posted September 2007 

Declared Global Temporary Tables (DGTTs) support data manipulation language (DML) constructs. However, you can only retrieve data from a DGTT table using the SELECT SQL statement (or a stored procedure that issues a SELECT statement). Since the schema definition for a DGTT is always SESSION, you must refer to your DGTTs using the SESSION schema qualifier either explicitly or by setting the default schema to resolve it.

For example, you can retrieve data from the sample DGTT used in this section using the SELECT * FROM SESSION.TEMPSTAFFTABLE shown in the following figure:

select * from tempstafftable
SQL0204N "PAULZ.TEMPSTAFFTABLE" is an undefined name. SQLSTATE=42704
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     12  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   
  3 record(s) selected.

Note the error in the first transaction. It was returned because the SESSION schema qualifier was specified and it wasn't the default for the system in this example.

SELECT statements operate on DGTTs like any other tables. For example, you can perform projections (shown in the first example in the following figure), restrictions (shown in the second example), and other operations (shown in the remaining examples) on them:

select job, salary, comm from session.tempstafftable
JOB   YEARS  SALARY    COMM
----- ------ --------- ---------
Sales     12  30000.00    200.00
Mgr       24  31000.00   2000.00
ID        13  39999.00   3000.00
 3 record(s) selected
 
"select name from session.tempstafftable where salary > 31500"
NAME
---------
Melnyk
  1 record(s) selected.
  
select avg(salary) from session.tempstafftable
1
----------------------------------
 33666.333333333333333333333333333
 
  1 record(s) selected.
  
select salary+comm as TOTALCOMP from session.tempstafftable
TOTALCOMP
----------
  30200.00
  33000.00
  42999.00
  
  3 record(s) selected.

In addition to a SELECT statement, INSERT, UPDATE, and DELETE operations can also be performed on the data that resides in a DGTT. Don't forget that you must use the SESSION qualifier to perform the operation on the DGTT. This requirement sometimes eludes application developers when performing operations on specific columns of a DGTT (see the following example). An example of INSERT, UPDATE, and DELETE operations on a DGTT is shown below:

update session.tempstafftable set "Years" = 11 where
   session.tempstafftable.id=10
DB20000I The SQL command completed successfully.
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     11  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   
 3 record(s) selected.
 
insert into session.tempstafftable values (40,'Doyle',20,'ID',
15,23000,230)
DB20000I The SQL command completed successfully.
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     11  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   40 Doyle         20 ID        15  23000.00    230.00
   
 4 record(s) selected.
 
delete from session.tempstafftable where id=40
DB20000I The SQL command completed successfully.
select * from session.tempstafftable
ID    NAME      DEPT   JOB   YEARS  SALARY    COMM
----- --------- ------ ----- ------ --------- ---------
   10 Zikop...      20 Sales     11  30000.00    200.00
   20 Baklarz       20 Mgr       24  31000.00   2000.00
   30 Melnyk        20 ID        13  39999.00   3000.00
   
 3 record(s) selected.

If you attempt to modify the contents of a DGTT using an INSERT, UPDATE, or DELETE statement, and the statement fails, what happens depends on how the DGTT was defined. . If the DGTT was created with the NOT LOGGED option, DB2 deletes all of the rows in the DGTT table, but the table still exists. If the DGTT was defined without the NOT LOGGED option, only the change made by the failed INSERT, UPDATE, or DELETE statement is rolled back.

If you modify the contents of a DGTT using an INSERT, UPDATE, or DELETE statement within the scope of a transaction, and subsequently roll back that transaction, DB2 deletes all of the rows in the DGTT.

By George Baklarz for Knowledge Xpert for DB2
Originally Posted May 2007 

Prior to range partitions, many applications were developed that used the UNION ALL view capability of DB2 to create a form of range partitioning. A separate table would be created for each range and then all tables combined into a view. In order for this to work properly, each table needed to be defined with an appropriate constraint that limited the rows that would be placed into it:

CREATE TABLE Q1_2006
  (
  TX_NUMBER  INT NOT NULL,
  TX_ITEM    CHAR(10) NOT NULL,
  TX_QUANTITY INT NOT NULL,
  TX_DATE    DATE NOT NULL,
     CHECK
       (TX_DATE BETWEEN '2006-01-01' AND '2006-03-31')
  )

The final check constraint would give DB2 information on which rows could be placed into this table. A view would be created that combines all four quarters into a single view of the year:

CREATE VIEW FOURQUARTERS AS
  (
  SELECT * FROM Q1_2006
  UNION ALL
  SELECT * FROM Q2_2006
  UNION ALL
  SELECT * FROM Q3_2006
  UNION ALL
  SELECT * FROM Q4_2006
  )

Adding new partitions to this view requires that the view be dropped and this may cause the loss of some dependent objects. Range Partitioning solves this problem and simplifies the roll-in and roll-out procedure. Only three steps are required in order to migrate existing UNION ALL views to a range partitioned table.

1. Create a partitioned table

   A partitioned table with only one partition in it needs to be defined. The first partition should be used to capture records with null values and for dates that are below the starting range. The use of the LIKE command simplifies the creation of the partitioned table since it can use the design of one of the existing tables in the UNION ALL view:

   CREATE TABLE YEAR2006 LIKE Q1_2006
     PARTITION BY (TX_DATE NULLS FIRST)
      (
       PARTITION EXCEPTION STARTING MINVALUE
         ENDING '2006-01-01' EXCLUSIVE
      )

2. Attach existing tables

   Each table that is part of the view needs to be attached to the partitioned table. The range partitioned table must have the same table space type as the tables that are being attached. The ALTER command will issue an error message if they are of different types:

   ALTER TABLE YEAR2006 ATTACH PARTITION Q1
       STARTING '2006-01-01' ENDING '2006-03-31'
       FROM TABLE Q1_2006;
   ALTER TABLE YEAR2006 ATTACH PARTITION Q2
       STARTING '2006-04-01' ENDING '2006-06-30'
       FROM TABLE Q2_2006;
   ALTER TABLE YEAR2006 ATTACH PARTITION Q3
       STARTING '2006-07-01' ENDING '2006-09-30'
       FROM TABLE Q3_2006;
   ALTER TABLE YEAR2006  ATTACH PARTITION Q4
       STARTING '2006-10-01' ENDING '2006-12-31'
       FROM TABLE Q4_2006;

3. Run SET INTEGRITY

   The SET INTEGRITY statement needs to be issued to bring the data back online. After the SET INTEGRITY completes, the YEAR2006 table contains the same contents are the original FOURQUARTERS view. The following SET INTEGRITY command does not have exception tables defined since the base tables would not have allowed records outside of the constraint ranges.

   SET INTEGRITY FOR YEAR2006 ALLOW WRITE ACCESS
     IMMEDIATE CHECKED INCREMENTAL

By Craig Mullins for Knowledge Xpert for DB2
Originally Posted February 2007 

Consider the following recommendations when creating indexes:

Create Indexes Before Loading Tables

The LOAD utility updates indexes efficiently. Usually, the LOAD utility is more efficient than building indexes for tables that already contain data. The data being loaded should be sorted into the order of the clustering index before execution.

Consider Deferring Index Creation

The DEFER option on the CREATE INDEX statement allows the index to be created but not populated. The RECOVER INDEX utility can then be executed to populate the index. This will speed the index creation process because REBUILD INDEX usually populates index entries faster than CREATE INDEX.

Creating a STOGROUP-defined index with DEFER YES causes the underlying VSAM data set for the index to be allocated.

Additionally, the DB2 catalog is updated to record that the index exists. However, if the table being indexed currently contains data, DB2 will turn on the recover pending flag for the index space and issue a +610 SQLCODE. Subsequent execution of RECOVER INDEX will turn off the recover pending flag and populate the index.

Consider Deferring Index Data Set Definition

The DEFINE parameter can be used to control when the underlying data set(s) for the index space are created. DEFINE YES, which is the default, indicates that the data sets are created when the index is created. DEFINE NO indicates that data set creation will not occur until data is inserted into the index. The DEFINE parameter should be used only with STOGROUP-defined indexes; it will be ignored if specified for VCAT-defined indexes.

Specifying DEFINE NO can be useful to minimize the number of data sets where indexes are being created on empty tables that will remain empty for some time.

Create a Unique Index for Each Primary Key

Every primary key explicitly defined for a table must be associated with a corresponding unique index. If you do not create a unique index for a primary key, an incomplete key is defined for the table, making the table inaccessible.

Use WHERE NOT NULL to Allow Multiple Nulls in a UNIQUE Index

Specify the UNIQUE WHERE NOT NULL clause to enable multiple nulls to exist in a unique index. This is useful when an index contains at least one nullable column, but all non-null entries must be unique.

Create Indexes for Foreign Keys

Unless an index already exists for access reasons or the table is too small to be indexed, create an index for each foreign key defined for a table. Because DB2's referential integrity feature accesses data defined as a foreign key "behind the scenes," it's a good idea to enhance the efficiency of this access by creating indexes.

Specify Columns Carefully in Multicolumn Indexes

If a table has only multicolumn indexes, try to specify the high-level column in the WHERE clause of your query. This action results in an index scan with at least one matching column.

A multicolumn index can be used to scan data to satisfy a query in which the high-level column is not specified (but another column in the index is specified). However, a non-matching index scan of this sort is not as efficient as a matching index scan.

Consider Several Indexes Instead of a Multicolumn Index

Because DB2 can utilize multiple indexes in an access path for a single SQL statement, multiple indexes can sometimes be more efficient (from a global perspective) than a single multicolumn index. If access to the columns varies from query to query, multiple indexes might provide better overall performance for all your queries, at the expense of an individual query.

If you feel that multiple indexes might be of benefit for your specific situation, test their effectiveness first in a test environment by:

• Dropping the multicolumn index
   
• Creating a single index for each of the columns in the multicolumn index
   
• Updating DB2 Catalog statistics to indicate production volume
   
• Running EXPLAIN on all the affected queries and analyzing the results

Here is an example where DB2 could use two indexes to satisfy a data retrieval request. For the following query:

SELECT   DEPTNO, DEPTNAME, MGRNO
FROM     DSN8810.DEPT
WHERE    DEPTNO > 'D00'
AND      ADMRDEPT = 'D01';

Only two indexes exist for this table, one index for DEPTNO and another index for ADMRDEPT. DB2 could use either, or both of these indexes to satisfy the request. If multi-index access is used, the index on DEPTNO is used to retrieve all departments with a DEPTNO greater than 'D00', and the index on ADMRDEPT is used to retrieve only rows containing 'D01'. Then these rows are intersected and the correct result is returned.

DB2 can use multiple indexes to satisfy OR conditions as well.

Excerpt From Knowledge Xpert for DB2
Originallly Posted November 2006 

Assume that you are experiencing memory constraints, either overall or at certain peak times. The options presented here can reduce memory usage, but may negatively affect the use of another resource (CPU, disk usage, application throughput, data availability).

Most of the DB2-specific memory issues disappear in DB2 V8. This is because the DBM1 address space is 64-bit, running on 64-bit hardware. There is no longer a virtual storage constraint, that being the limit of 2GB on an address space.

However, DB2 V8 still uses real memory, and there is (by definition) a fixed amount of real memory available on the hardware. DB2 competes with other subsystems for that memory, and if there are more requests for memory than is available, the result is paging (the movement of memory pages to and from disk). This should be avoided if possible, although some paging is not necessarily a bad thing.

Most of the suggestions below apply to possible virtual storage constraints (particularly of the DBM1 address space) in a DB2 z/OS V7 environment.

Decrease Use of Data Compression

Dictionaries used for data compression are loaded into the DBM1 address space upon first use of a table. They may take up to 64 KB of memory. While this is not a large amount, it can add up if you have a lot of tablespaces that are defined with COMPRESS YES. This is especially true if you have installed one or more instances of software packages that require DB2 tables. ERP packages such as PeopleSoft or SAP may contain thousands of tablespaces.

Consider removing data compression (alter the tablespace to COMPRESS NO, then Reorg) from highly volatile tables, from tables with mostly numeric data (which does not compress well), or from tables that are frequently updated.

Reallocate Bufferpools

Bufferpools are by default allocated in the DBM1 address space. Bufferpool tuning is beyond the scope of these recommendations; however, you can review the DB2PM Statistics Detail and Summary reports (or their third-party equivalents) for bufferpool activity. Pools with low activity may (perhaps) be decreased a bit in order to address a serious virtual storage constraint.

Another option (V7 or V8) is to reduce the size of a bufferpool and allocate an additional hiperpool (using the HPSIZE parameter). Hiperpools are implemented using hiperspaces, which are address spaces of their own. Consider this for tables assigned to a bufferpool where access is mostly reference (i.e., little update). Some of the pages in this bufferpool, when stolen, will be written to the hiperpool instead.

With DB2 V8 one can assign bufferpools to dataspaces which, like hiperpools, reside address spaces of their own.

Re-size the EDM Pool

The Environment Descriptor Pool (EDM Pool) is an area of memory used for storing different DB2 control blocks and objects. Apart from simply re-sizing the EDM Pool (by changing the EDMPOOL ZParm), in DB2 V8 certain objects such as DBDs are no longer stored in the EDM Pool. Thus, after migrating to DB2 V8 you can reduce the size of the EDM Pool proper while allocating the other areas separately. For example, with the EDMDBDC parameter, you allocate a separate area of memory for DBDs.

Decrease Sort Pool

The sort pool is an area of memory in the DBM1 address space used for in-memory sorts. These can occur because of SQL requirements (for example ORDER BY or GROUP BY), or for utility processing (for example REBUILD INDEX, or REORG). Its size is controlled by the SRTPOOL ZParm value. Reducing it will increase the probability that sorts will require DASD to complete, thereby increasing elapsed time to complete processes.

Decrease Maximum Number of Open Datasets

The DSMAX ZParm value controls the maximum number of datasets (tablespace and index datasets) that DB2 will allow to be open at one time. As this limit is reached, DB2 schedules some of the datasets to be "pseudo-closed". Each open dataset requires a small control block of memory to be allocated in the DBM1 address space. While these blocks are rather small (1-3 KB), it is possible that the total space used may get rather large in environments where there are a lot of partitioned tablespaces (with separate datasets for each partition and each partitioning index partition). Another possibility is environments having installations of ERP packages such as PeopleSoft or SAP. These installations may contain several thousand tables and indexes.

Generally, decreasing DSMAX will not help a virtual storage constraint to any great extent. Still, it is one aspect.

Reduce Size of the Dynamic SQL cache

In DB2 V7 the dynamic SQL cache is contained in the EDM Pool. Its size is controlled by the ZParm values CACHEDYN and EDMSTMTC (In DB2 V8). Reducing the size of the cache may relieve a virtual storage constraint (in DB2 V7).