Memory tuning in DB2

IBM DB2 offers the db2pd utility you can use to collect statistics on your DB2 instances and databases to help you troubleshoot problems, monitor your databases, and tune database performance. For information on how to use this utility, consult the DB2 online documentation.

DB2 also offers a tool—the Configuration Advisor, provided with the Control Center—that is designed to capture specific information about your database environment and recommend changes to configuration parameters based on the information you provide.

At DB2 9, the default settings for buffer pools and the package cache size parameter may be sufficient. If you are not using the default settings, here are some recommendations on tuning the memory of your DB2 database to work with ArcSDE.

• Set up buffer pools.

  Setting up the buffer pools is critical to performance. By default, DB2 provides a single buffer pool named IBMDEFAULTBP. The DB2 9 default characteristic for IBMDEFAULTBP is to use automatic sizing.

  You should create a separate buffer pool for each table space. The database snapshot should be reviewed to check the buffer pool physical read values. The buffer pool should be large enough that a snapshot of a map redraw results in a small number of physical reads.

• Properly set the package cache size parameter (PCKCACHESIZE).

  Package cache size specifies the amount of memory allocated for caching dynamic and static SQL requests. This is allocated at database startup and freed at shutdown. If DB2 does not find a SQL statement in the package cache, the statement will need to be recompiled (dynamic SQL) or loaded from the package catalog (static SQL), which can consume considerable time. The DB2 9 default parameter value for PCKCACHESIZE is AUTOMATIC. Depending upon system configuration, you may not have to maintain this item.

  ESRI recommends you start with an initial size of 6,000 and monitor the package cache lookups and package cache insert values in a database snapshot.