Understanding DB2: Learning Visually with Examples (2nd Edition)

12.9. DB2 Maintenance Utilities

Performing maintenance activities on your databases is essential to ensure that they are optimized for performance and recoverability. In this section, we introduce a few utilities that you should use regularly to ensure the database is healthy and optimized.

12.9.1. The RUNSTATS Utility

DB2 utilizes a sophisticated cost-based optimizer to determine how data is being accessed. Its decisions are heavily influenced by statistical information about the size of the database tables and indexes. Therefore, it is important to keep the database statistics up to date so that an efficient data access plan can be chosen. The RUNSTATS utility updates statistics about the physical characteristics of a table and the associated indexes. Characteristics include the number of records (cardinality), the number of pages, the average record length, and so on.

Since runstats command supports many different options, the syntax diagram shown in Figure 12.34 is not a complete one. Refer to the DB2 Command Reference for details on each option.

Figure 12.34. Partial syntax diagram of the runstats command

>>-RUNSTATS--ON TABLE--table name--+-USE PROFILE------------+---> '-| Statistics Options |-' >--+------------------------------------+---------------------->< '-UTIL_IMPACT_PRIORITY--+----------+-' '-priority-'

The following examples illustrate how to use this command.

• This command collects statistics on the table db2user.employee while letting readers and writers access the table while the statistics are being calculated.

  runstats on table db2user.employee allow write access

• This command collects statistics on the table db2user.employee, as well as on the columns empid and empname with distribution statistics. While the command is running, the table is only available for read-only requests.

  runstats on table db2user.employee with distribution on columns ( empid, empname ) allow read access

• The following command collects statistics on the table db2user.employee and detailed statistics on all its indexes.

  runstats on table db2user.employee and detailed indexes all

• This command collects statistics on the table db2user.employee with distribution statistics on only 30 percent of the rows.

  runstats on table db2user.employee with distribution tablesmple bernoulli(30)

12.9.2. The REORG and REORGCHK Utilities

As data is inserted, deleted, and updated in the database, the data might not be physically placed in a sequential order, which means that DB2 must perform additional read operations to access data. This usually requires more disk I/O operations, and we all know such operations are costly. To minimize I/O operations, you should consider physically reorganizing the table to the index so that related data are located close to each other.

An index is said to have a high cluster ratio when the data with equal or near key values is physically stored close together. The higher the cluster ratio, the better rows are ordered in index key sequence. Figure 12.35 shows the difference between indexes with high and low cluster ratio.

An index's cluster ratio is part of the database statistics. You will learn in Chapter 16, Database Performance Considerations, that keeping database statistics is very important when it comes to performance. REORGCHK is a data maintenance utility that has an option to retrieve current database statistics or update the database statistics. It generates a report on the statistics with indicators identifying tables and indexes that should be reorganized (or defragmented). Using the statistics formulae, reorgchk marks the tables or indexes with asterisks (*) if there is a need to REORG. Figure 12.36 shows the syntax diagram of the reorgchk command.

Figure 12.36. Syntax diagram of the reorgchk command

.-UPDATE STATISTICS--. >>-REORGCHK--+--------------------+-----------------------------> '-CURRENT STATISTICS-' .-ON TABLE USER-----------------. >--+-------------------------------+--------------------------->< '-ON--+-SCHEMA--schema-name---+-' | .-USER-------. | '-TABLE--+-SYSTEM-----+-' +-ALL--------+ '-table-name-'

For example, the following command generates a report of the current statistics on all tables that are owned by the runtime authorization ID:

reorgchk current statistics on table user

This command updates the statistics and generates a report on all the tables created under the schema smith:

reorgchk update statistics on schema smith

Figure 12.37 shows a sample output of a reorgchk command. You can see that the report contains table and index statistics. Every table and index defined in the database is listed. If statistics are not collected for the table or index, a dash () is displayed.

To reorganize tables or indexes, use the REORG utility. It reorganizes data for a table and/or index. Although data is physically rearranged, DB2 provides the option of performing this online or offline. By default, offline REORG lets other users read the table. You can restrict table access by specifying the allow no access option. Online REORG (also called inplace REORG) does not support read or write access to the table. Since data pages are rearranged, concurrent applications have to wait for REORG to complete with the current pages. You can easily stop, pause, or resume the process with the appropriate options. Figure 12.38 illustrates the syntax diagram of the reorg command.

Figure 12.38. Syntax diagram of the reorg command

>>-REORG--------------------------------------------------------> >--+-TABLE--table-name--| Table Clause |-----------------+------> '-INDEXES ALL FOR TABLE--table-name--| Index Clause |-' >--+-------------------------------+--------------------------->< '-| Database Partition Clause |-' Table Clause: |--+-------------------+----------------------------------------> '-INDEX--index-name-' .-ALLOW READ ACCESS-. >--+-+-------------------+--+--------------+--+-----------+--+-------------+-+--| | '-ALLOW NO ACCESS---' '-USE--tbspace-' '-INDEXSCAN-' '-LONGLOBDATA-' | | .-ALLOW WRITE ACCESS-. .-START--. | '-INPLACE--+-+--------------------+--+------------------+--+--------+-+---' | '-ALLOW READ ACCESS--' '-NOTRUNCATE TABLE-' '-RESUME-' | '-+-STOP--+------------------------------------------------' '-PAUSE-' Index Clause: .-ALLOW READ ACCESS--. |--+--------------------+--+-------------------------+----------| +-ALLOW NO ACCESS----+ | .-ALL---. | '-ALLOW WRITE ACCESS-' +-CLEANUP ONLY--+-------+-+ | '-PAGES-' | '-CONVERT-----------------'

The following command reorganizes table db2user.employee and its index db2user.idxemp. The operation lets others perform writes to the same table.

reorg table db2user.employee index db2user.idxemp inplace allow write access

To pause a REORG operation, issue the command with the same options but specify the pause option:

reorg table db2user.employee index db2user.idxemp inplace pause

NOTE

The REORG utility rearranges the data physically but does not update the database statistics. Therefore, it is important to always execute a RUNSTATS upon completion of a REORG.

12.9.3. The REBIND Utility and the FLUSH PACKAGE CACHE Command

Before a database application program or any SQL statement can be executed, DB2 precompiles it and produces a package. A package is a database object that contains compiled SQL statements used in the application source file. DB2 uses the packages to access data referenced in the SQL statements. How does the DB2 optimizer choose the data access plan for these packages? It relies on database statistics at the time the packages are created.

For static SQL statements, packages are created and bound to the database at compile time. If statistics are updated to reflect the physical database characteristics, existing packages should also be updated. The REBIND utility lets you recreate a package so that the current database statistics can be used. The command is very simple:

rebind package package_name

When you execute dynamic SQL statements, they are not known until the application is run. They are precompiled at runtime and stored in the package cache. If statistics are updated, you can flush the cache so that dynamic SQL statements are compiled again to pick up the updated statistics. Use the command:

flush package cache dynamic

12.9.4. Database Maintenance Process

You have just learned about a few database maintenance utilities: RUNSTATS, REORG, REORGCHK, REBIND, and FLUSH PACKAGE. Figure 12.39 summarizes the maintenance process that you should perform regularly against your database.