DB2 Server for VSE & VM: Performance Tuning Handbook

DASD Balancing

How well you balance the demand for DASD I/O across several DASD volumes can affect how fast the database manager can retrieve information from DASD.

Do not spend a lot of time and effort balancing the utilization of your DASD channels and controller. Instead, concentrate on balancing the utilization of your DASD volumes. You can then simply allocate an even number of volumes to each controller.

Evenly Distributing Workload across Physical Volumes

Moving Dbextents

To evenly distribute your workload across all volumes of DASD, use the following method as a guide:

1. Measure the current utilization of your DASD volumes.
2. Select the highest utilized volume. While DASD balancing based on utilization may not necessarily give optimal performance (it assumes all your volumes perform equally well), it is an excellent place to start. (You can also select a volume based on average service time. Choose the volume with the highest average service time. Balancing this way ensures that you will drive faster DASD harder.)
3. If there is more than one dbextent on the volume, move one dbextent to the lowest utilized volume. In VM use DDR, and in VSE use VSAM backup and restore. (While you can use the copy dbextent facility that is supplied with the DB2 Server for VSE & VM product to move a dbextent, DDR and VSAM are much faster.)
4. If there is only one dbextent, examine the assignment of dbspaces to pools to dbextents. Refer to Reassigning Dbspaces.
5. Measure the current utilization of your DASD volumes again.
   1. If you find a significant improvement, return to step 2.
   2. If you do not find a significant improvement, return to step 3 and select a different dbextent to move.
   3. If there is no significant difference between the utilization of the highest and the lowest utilized volumes they are balanced. Occasionally, measure the utilization of your DASD volumes to ensure that they are still balanced.

Reassigning Dbspaces

To reassign your dbspaces, first determine which storage pool the dbextent belongs to, then choose one of the following options:

• Move a dbspace from one storage pool to another.
• Move a table from one dbspace to another. This choice is not valid if this is already the only table in the dbspace. Also make sure that if you move the table you do not put more than one highly used table in the same dbspace.
• Change the dbextent(s) in the storage pool to which the dbspace is allocated.

Moving Dbspaces

1. Select a dbspace to move. While you can use the SHOW DBSPACE operator command to see how many pages from the storage pool have been allocated to a dbspace, you cannot easily determine how utilized the dbspace is. You must rely on your knowledge of how the table(s) in the dbspace are used.
2. Unload all the tables in that dbspace.
3. Acquire a dbspace in a new storage pool. This storage pool should have dbextents on the lowest utilized volumes. To accomplish this, you may have to add a dbextent or dbspace or both.
4. Reload the tables.
5. If a table in the dbspace has field procedures associated with it, the table should be dropped and recreated to include the field procedures and reloaded using the PURGE parameter.
6. Drop the old dbspace.
7. Recreate the indexes, views, and authorities.
8. Recreate any referential integrity constraints.

Moving Tables

1. Select a table to move. While you can use the NPAGES column in the SYSTEM.SYSCATALOG table to see how many pages from the dbspace have been allocated to a table, you cannot easily determine how utilized the table is. You must rely on your knowledge of how the table(s) in the dbspace are used.
2. Unload the table.
3. Select a dbspace in a new storage pool.
4. If the table has field procedures associated with it, recreate the table to include the field procedures.
5. Reload the tables.
6. Drop the old table.
7. Recreate the indexes, views, and authorities.
8. Recreate any referential integrity constraints.

Change Dbextents

You can either let the database manager do most of the work for you, or you can do it yourself:

Let the Database Manager Do it

1. Add dbextents to the storage pool until there is more free space in the pool than on the dbextent to be deleted (allowing sufficient space for shadow pages and an adequate SOSLEVEL).
2. Delete the dbextent on the most used volume. The database manager will automatically move data from the extent to be deleted onto the remaining dbextents in the pool.

Do it Yourself

1. Unload all the tables in all the dbspaces in a storage pool.
2. Drop all the dbspaces in the storage pool.
3. Re-assign dbextents to the storage pool.
   • One simple technique is to split one dbextent into two smaller dbextents on two separate volumes. One dbextent remains on the highly utilized volume and the other is allocated to a low utilized volume. You cannot use the DB2 Server for VSE & VM copy dbextent facility to do this.
   • Unless you are using the DB2 Server DSS Feature with striping turned on, do not just add a new dbextent to the pool. That will not result in any usage of the new dbextent until the previous dbextents are full.
4. Acquire dbspaces in the storage pool.
5. Reload the tables.

   Note:

   Unless you are using striping, data is added to the dbextents in the order that they were created. The database manager will fill the first dbextent before it proceeds to the next one.

6. If a table in the dbspace has field procedures associated with it, the table should be dropped and recreated to include the field procedures and reloaded using the PURGE parameter.
7. Recreate the indexes, views, and authorities.
8. Recreate any referential integrity constraints.

General Considerations

There are other things to consider when you organize your dbspaces, storage pools, dbextents and physical DASD.

Place the database catalog tables into their own pool. At database generation time, the catalog is placed in pool number one. All other, non-catalog tables, should be moved to different pools.

Place internal dbspaces in their own pool. Performance should benefit greatly for large complex queries if you use data spaces with this pool. Assign the pool to a set of dbextents that includes a virtual disk. Refer to Virtual Disk Support for VSE/ESA for Internal Dbspaces or to Virtual Disk Support for VM/ESA for Internal Dbspaces. In VM/ESA, if you have DB2 VM Data Spaces Support, use unmapped data spaces support for internal dbspaces. Refer to Chapter 6, Data Spaces Support for VM/ESA.

Caching is best used where data is frequently reused. For example, the database directory is primarily read from and will benefit from caching, while the log is primarily written to and will not benefit from it. Any highly utilized dbextent disk that contains tables that are primarily used for read only transactions will benefit from caching.

Attention: The amount of frequently-reused-data should not exceed the size of the cache.

If you have faster storage devices available, use them for your highest utilized dbextents.

Place the database directory on a separate volume from your storage pool dbextents. Because you may use all of these at the same time, if you do not separate them you may create a bottleneck. You can place the directory and the log(s) in the same volume, but it is better to separate them. If you use dual logging, be sure to put each log on a different physical device (and controller and channel, if possible).

If you are using the DB2 Server DSS Feature with striping turned on, make sure that each dbextent in a storage pool is on a separate volume. Refer to Striping.

If you are not using DB2 Server DSS striping, place dbextents consecutively on the same physical volume. This avoids unnecessary head movement. In a VM system, you can control exactly where a minidisk is placed. However, if you want to place dbextents consecutively in a VSE system, you need to backup all VSAM datasets on a particular disk and then reallocate them consecutively.