Tuning the IBM DB2 server

Tune the registry and database manager settings and the automatic buffer pool size and auto extends.

DB2 is configured automatically if you use the Maximo® Real Estate and Facilities scripts to configure your instance and database. For more information, see the Installation and Implementation Guide. However, you can also tune the database to maximize your specific DB2 implementation.

Registry and database manager settings

Use the following DB2 registry settings so that your database performs efficiently:

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES
db2set DB2_ATS_ENABLE=YES
db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON

Use the following DB2 database manager settings:

db2 update dbm cfg using RQRIOBLK 65535

Create the database with PAGESIZE=32K.

The following DB2 database configuration settings are required:

db2 update db cfg for <dbname> using STMT_CONC OFF
db2 update db cfg for <dbname> using LOCKTIMEOUT 30

The following optional database configuration settings can improve the performance of the log file:

db2 update db cfg for <dbname> using LOGPRIMARY 23
db2 update db cfg for <dbname> using LOGFILSIZ 32768
db2 update db cfg for <dbname> using LOGSECOND 12
db2 update db cfg for <dbname> using LOGBUFSZ 8192
db2 update db cfg for <dbname> using CATALOGCACHE_SZ 2048

If you are supporting multibyte characters, configure the database with the UTF-8 code set and the CODEUNITS32 string unit:

db2 update db cfg for <dbname> using string_units CODEUNITS32

Do not turn on automatic maintenance settings, such as AUTO_MAINT, AUTO_TBL_MAINT, AUTO_RUNSTATS, and AUTO_STMT_STATS. Instead, schedule database maintenance activities during regular maintenance windows to avoid affecting user performance.

To help the query optimizer select an efficient access plan, specify the REOPT(ONCE) bind option when you run queries. When you use the REOPT(ONCE) bind option, the query optimizer selects the access plan the first time that the query is run. Each subsequent time that the query is run, the access plan is reused. To specify the REOPT(ONCE) bind option, run the following command:

db2 bind '<db2home>/bnd/db2clipk.bnd' collection NULLIDR1

The user ID that is created for Maximo Real Estate and Facilities to access the database must have DBADM, SECADM, ACCESSCTRL, and DATAACCESS privileges for the database. For information about DB2 administration tasks, see the DB2 product documentation.

DB2 automatic buffer pool size and auto extends

To avoid a performance issue, ensure that the database is set to use an AUTOMATIC buffer pool instead of a static size.

To manually change the database settings, complete the following steps:
  1. In IBM Data Studio, log in as the Maximo Real Estate and Facilities user to the database or instance that is used by Maximo Real Estate and Facilities.
  2. Run the following SQL:
    > select AUTOMATIC FROM TABLE(MON_GET_BUFFERPOOL('',-2)) where upper(bp_name) = 'TRIRIGABUFFERPOOL'
    • If the return value is 0 (zero), then continue to step 3.
    • If the return value is 1 (one), then the buffer pool is already set to AUTOMATIC.
  3. Stop the application servers that run DB2.
  4. Run the following SQL in IBM Data Studio:
    alter bufferpool TRIRIGABUFFERPOOL immediate size AUTOMATIC
  5. Run the original SQL select to make sure that the return value is now 1 (one).
  6. Start and stop the DB2 instance that is used by Maximo Real Estate and Facilities:
    • For Windows, from the DB2 Command Window - Administrator, log in as the DB2 administrator user, which is typically db2admin, and run the following commands, where XXXXX is the name of the instance that is used by Maximo Real Estate and Facilities:
      set db2instance=XXXXX

      db2stop force
      db2start
    • For Linux®, from a shell window, log in as the Maximo Real Estate and Facilities instance user and run the following command:
      db2stop force
      db2start
  7. Restart the application servers that run DB2.

DB2 diagnostic log

Ensure that the diagnostic log for errors does not become large enough to cause storage space issues. An oversize diagnostic log might cause performance problems. For information about configuring DB2 to use rotating DB2 diagnostic log files, see DB2 diagnostic (db2diag) log files.