Maintaining a WebSphere Commerce DB2 Database

Technote (troubleshooting)


Problem(Abstract)

A WebSphere Commerce site will suffer significant performance degradation if the database is not being properly maintained. This technote provides guidelines on tasks that are required to maintain a WebSphere Commerce DB2 database.


Resolving the problem

A WebSphere Commerce DB2 database is maintained using the following high-level tasks:

Database maintenance should be performed when the traffic to the WebSphere Commerce site is minimal, as there will be some performance degradation and additional locking while these tasks are being executed. For example, a standard REORG will lock for write the tables being reorganized and the db2rbind command will fail if the stored procedures are in use.


DBCLEAN

Keeping obsolete data affects runtime performance and makes the database difficult to manage. In order to keep the database consistent, policies should be defined and enforced to remove outdated information from the database.

WebSphere Commerce includes a Database Cleanup utility that can be used to delete records marked for delete and obsolete data. The utility will allow you to delete data from most of the WebSphere Commerce default tables, but you can also extend this utility to clean up your custom tables. For a list of all objects that can be deleted, see the topic on Database Cleanup utility objects in the WebSphere Commerce Information Center.

The following command can be used to record the cardinality (number of rows) of each table. Run this command on a monthly basis and save the output. Comparing the current table cardinality with that of previous months will allow you to identify those tables that require the most attention and maintenance.

db2 "EXPORT TO tablecard.csv OF DEL SELECT tabschema, tabname, definer, card, fpages FROM syscat.tables WHERE type = 'T' AND (days (current date) - days (stats_time)) < 7"


RUNSTATS

The RUNSTATS command will update the statistics that are used by the optimizer when determining access paths to the data. If the statistics are not up to date, the system will suffer performance degradation.

Most WebSphere Commerce sites update statistics on a daily or weekly basis (generally over the weekend). You should also consider updating the statistics after schema changes or massive update or load (such as refreshing the catalog). Remember to run db2rbind after RUNSTATS so the static packages can take advantage of the new statistics.

The following SQL statements can be used to identify when the statistics were last updated for all the tables and indexes on the database:

db2 "select tabschema, tabname, stats_time from syscat.tables order by stats_time asc"
db2 "select indschema, indname, tabschema, tabname, colnames, stats_time from syscat.indexes order by stats_time asc"

You can also use the following SQL statements to create a script to update the statistics for all the tables and indexes on the database:

db2 -x "SELECT 'RUNSTATS ON TABLE '||RTRIM(TABSCHEMA)||'.'||TABNAME||' WITH DISTRIBUTION AND INDEXES ALL;' FROM SYSCAT.TABLES WHERE TYPE='T' "> runstats.sql

Depending on the size of your database, you might need to use finer grained method for updating statistics such as profiling or sampling. For more information, guidelines and examples, see the topic on RUNSTATS Command in the DB2 Information Center, and the article titled, RUNSTATS in DB2 Universal Database, V8.2 on the developerWorks Web site.


REORGCHK and REORG

The REORG command reorganizes a table by compacting information and reconstructing the rows to eliminate fragmented data. The REORGCHK utility uses different algorithms to find the tables and indexes that need to be reorganized. The REORGCHK command will output a table listing all the table and indexes objects. An asterisk (*) on the REORG column will indicate that the calculated results exceed the set bounds of its corresponding formula, and that the table might need to be reorganized.

Unless you use the "CURRENT STATISTICS" specifier, the REORGCHK command will update the statistics for all the objects on the database. If you are running REORGCHK after RUNSTATS, use the "CURRENT STATISTICS" specifier to avoid updating the statics twice. You can also omit RUNSTATS and have REORGCHK update the statics, but this method provides less flexibility.

Once the tables or indexes to be reorganized have been identified using the REORGCHK command, the REORG command has to be explicitly invoked for each object. DB2 does not offer a way to automatically reorganize all the tables or indexes that were identified by REORGCHK.

Table reorganization is commonly performed in any one of the following ways:

A standard (offline) REORG will lock for write the tables being reorganized. If allow updates to the tables, Online table reorganization can be used instead.

Follow these links on the DB2 Information Center for more information on the REORGCHK Command and the REORG INDEXES/TABLE Command


Rebind Static Packages

Static packages need to be rebound after executing RUNSTATS to make use of updated statistics. Packages can be rebound one by one using the REBIND command or all at once by using the db2rbind - Rebind all Packages Command. The use of the db2rbind command is as follows:

db2rbind dbname -l db2rbind.log all

The command will not rebind a package if it is in use.

WebSphere Commerce includes 16 out-of-the-box stored procedures that will be benefited by the rebind (adjustinventory, allocateitem, allocbora, availableinv, availinvstore, availradate, availreceipts, backorderitem, currentversion, deletebackorder, expectedinv, getitems, inventoryallocation, raallocation, reverseinventory and shipitems)

The following SQL can be used to identify the last bind date for all the packages in the database:

db2 " select * from syscat.packages order by last_bind_time desc


Automatic Maintenance on DB2 8.2 (REORG and RUNSTATS)

Automatic Runstats and Reorg are available in DB2 8.2 (8.1.7).

Since the automatic maintenance feature internally schedules a classic reorganization for the table, it locks the table being reorganized for writing. Therefore, if you are using Automatic Maintenance ensure that the maintenance is only scheduled for when the traffic to the site is minimal.

Automatic maintenance is recommended for development and test databases, where maintenance is usually not done at all.

On a production system, you should script RUNSTATS, REORG and REBIND to run after dbclean, schema changes or massive data updates. In that case, you should disable Automatic Reorganization or ensure it does not conflict with the maintenance scripts.

For more information, see the topic on Automatic Maintenance in the DB2 Information Center.