Bookshelf v7.5: DB2 Universal Database Server Configuration

Upgrade Guide for Microsoft Windows > Preupgrade Tasks > Verifying Database Server Configuration >

DB2 Universal Database Server Configuration

This section provides guidelines for obtaining optimum performance from a DB2 Universal Database.

Before upgrading a DB2 database, verify that your development database server meets or exceeds the following OLTP parameters:

You have at least 25 primary and 100 secondary logs of at least 32-MB log size.
Locklist parameters are set to at least 5,000, and preferably 15,000.
Maxlocks should be set to 20.
DMS tablespace has at least 25% of free pages. If it does not, you must increase tablespace size by adding containers.
The file system has sufficient space to allow your DMS tablespace to grow.

In addition, verify that the tablespaces are not near their capacity. This can be done by connecting to the database and issuing the following command:

db2 list tablespaces show detail

Upgrade Instance

After upgrading your RBDMS software (for DB2 UDB, follow the instructions provided by IBM), upgrade your DB2 UDB instance.

To upgrade the instance

Run the following command on the database server to upgrade to the current version of your RDBMS software:
db2updv7 -d DB_NAME

Verifying DB2 Permissions on AIX and Solaris

If you are running DB2 UDB on the AIX or Solaris platform, perform the following steps before executing the Siebel database upgrade:

Navigate to the instance home directory.
Use the following command to verify that the directory sqllib/function/routine/sqlproc has write permission for the group:
   ls -ld sqllib/function/routine/sqlproc

To authorize group write permission, enter the following command:
chmod g+w sqllib/function/routine/sqlproc

Verifying Instance Owner Permissions on DB2 UDB for AIX and Solaris

If you are running DB2 UDB on the AIX or Solaris platforms, verify that the Siebel Database instance owner belongs to the primary group of the fenced user. If the instance owner is not part of this group, errors will occur during the Siebel Database upgrade.

Increasing the Number of ODBC Statement Handles

DB2 UDB can quickly run out of ODBC statement handles, depending on the number of business objects your enterprise uses. Because it is difficult to know how many business objects your users actually use, you should increase this number automatically each time you upgrade the DB2 UDB client, or when rebinding database utilities.

Siebel Systems recommends that you increase the number of CLI packages to six by rebinding the CLI packages, using the special DB2 CLIPKG bind option.

To rebind the CLI packages

Navigate to the following subdirectory from a DB2 Command window:
C:\sqllib\bnd

Connect to the DB2 UDB database.
Enter the following command:
db2 bind @db2cli.lst blocking all grant public clipkg 6

For more information about the DB2 bind command and the CLIPKG option, refer to IBM DB2 documentation.

Upgrade-Specific Parameters

The default settings of the parameters in this section should be adjusted for upgrading to Release 7.5. The values recommended in the following pages are guidelines only, and your environment may require adjustments to these values.

After your upgrade has been completed, and prior to running Release 7.5 in a production environment, you must adjust the DB2 parameters described in this section to those values recommended in Siebel Server Installation Guide for the operating system you are using. Otherwise, your Siebel Database Server may not provide optimal performance.

See your IBM DB2 technical documentation for additional information on DB2 parameters.

DB2 Database Manager Configuration Parameters

You can set the database configuration parameters using the update database manager configuration command of the DB2 Command Line Processor or using the DB2 Control Center.

NOTE: Refer to your IBM DB2 UDB documentation for more information on modifying the database configuration parameters.

Table 11 provides guidelines for setting DB2 Database Manager configuration parameters specifically for an optimal upgrade of your Siebel Database. Set these parameters for each DB2 instance. Use the configuration information below for the listed parameters. For parameters not listed in this table, accept the default settings.

Table 11.  DB2 Database Manager Configuration Parameters

Parameter

Explanation

Setting

MAX_QUERYDEGREE

Maximum query degree of parallelism
NOTE: Set this parameter only if you have a multiple CPU environment, and you installed the most recent DB2 fix pack identified in Siebel System Requirements and Supported Platforms.
Reset this parameter to the original value after the upgrade completes.

Any value between 1 and 6 (maximum). Use 6 if number of CPUs is 6 or greater.

INTRA_PARALLEL

Enable intra-partition parallelism
NOTE: Set this parameter only if you have a multiple CPU environment, and you installed the most recent DB2 fix pack identified in Siebel System Requirements and Supported Platforms.
Reset this parameter to the original value after the upgrade completes.

YES

SHEAPTHRES

Sort heap threshold (4 KB)

Double the value allocated for SORTHEAP. See Table 13.

ASLHEAPSZ

Application support layer heap size (4 KB)

15

MON_HEAP_SZ

Database monitor heap size (4 KB)

128 (minimum)

UDF_MEM_SZ

UDF shared memory set size (4 KB)

256

RQRIOBLK

Maximum requester I/O block size (bytes)

65535

QUERY_HEAP_SZ

Query heap size (4 KB)

16384

KEEPDARI

Keep DARI process

YES

QUERY_HEAP_SZ

Query heap size (4 KB)

16384

MAX_COORDAGENTS

Maximum number of coordinating agents

MAXAGENTS

db2set Parameters

Use the db2set command to set the parameters (for example, db2set DB2_RR_TO_RS = YES) referenced in Table 12. (Under Windows, you would access this through the DB2 Command Window, accessible from the DB2 for Windows 2000 client.)

Table 12.  db2set Parameters

Parameter

Explanation

Setting

DB2_RR_TO_RS

Improves DB2 performance with Siebel eBusiness Applications. Set to YES only in production environment servers.

YES

DB2_MMAP_WRITE

Recommended setting only; you should evaluate this setting for your particular configuration and environment.

OFF

DB2_MMAP_READ

Recommended setting only; you should evaluate this setting for your particular configuration and environment.

OFF

DB2_CORRELATED_PREDICATES

When set to ON, the optimizer is able to determine whether predicates in a query are related. This permits DB2 to calculate the filter factor more accurately.

ON

DB2_INDEX_2BYTEVARLEN

This parameter must always be set to ON. Otherwise, you cannot create indexes with columns greater than 255 bytes.

ON

DB2_PIPELINED_PLANS

Tells the DB2 optimizer to favor pipeline execution plans; in other words, plans which are left deep and have no temporary result sets.

ON

DB2_INTERESTING_KEYS

Limits the number of execution plans generated by the DB2 optimizer.

ON

DB2_PARALLEL_IO

Useful when using RAID devices. For more information, refer to relevant IBM documentation.

ON

DB2_STRIPED_CONTAINERS

Useful when using RAID devices. For more information, refer to relevant IBM documentation.

ON

NOTE: After changing any of these settings, perform a db2stop/db2start to implement the changes in your DB2 database.

DB2 Database Configuration Parameters

The database configuration parameters can be set using the update database configuration command of the DB2 Command Line Processor or using the DB2 Control Center. See the IBM DB2 technical documentation for more information on modifying the database configuration parameters.

Set the parameters in Table 13 for each database within an instance on which you run your Siebel eBusiness Applications. For other parameters of the same type, accept the default settings.

Table 13.  DB2 Database Configuration Parameters

Parameter

Explanation

Setting

DFT_DEGREE

Degree of parallelism (1=turn query parallelism off)

1

DFT_QUERYOPT

Default query optimization class

3

MAX_QUERYDEGREE

Index creation

ANY

DBHEAP

Database heap (4 KB)

7429

CATALOGCACHE_SZ

Catalog cache size (4 KB)

5558

LOGBUFSZ

Log buffer size (4 KB)

512 (For Windows, set this to 256.)

UTIL_HEAP_SZ

Utilities heap size (4 KB)

5000

LOCKLIST

Maximum storage for lock list
(4 KB)

5000 (This is the minimum setting. It may be increased.)

APP_CTL_HEAP_SZ

Maximum applications control heap size (4 KB)

900 (Recommended size may increase or decrease with the number of users.)

STMTHEAP

SQL statement heap (4 KB)

8192

SORTHEAP

Sort list heap (4 KB)

20,000—40,000 Recommended size; this may increase or decrease depending on the amount of memory in the database server machine and the size of the data.
A 20,000 setting allows SORTHEAP to increase up to 80 MB.

APPLHEAPSZ

Default application heap (4 KB)

2500 (Recommended size may increase or decrease with the number of users.)

STAT_HEAP_SZ

Statistics heap size (4 KB)

8000

MAXLOCKS

Percentage of lock lists per application

5

LOCKTIMEOUT

Lock timeout (sec.)

90 to 150

CHNGPGS_THRESH

Changed pages threshold

5

NUM_IOCLEANERS

Number of asynchronous page cleaners

Number of CPUs

INDEXSORT

Index sort flag

YES

SEQDETECT

Sequential detect flag

YES

LOGRETAIN

Sequential or circular log files

NO
Setting this parameter to YES means that log files are archived. You must periodically move or archive the logs to prevent the file system containing the log files from filling up.

AVG_APPLS

Average number of active applications

1

MAXFILOP

Maximum DB files open per application

500

LOGFILSIZ

Log file size (4 KB)

Development environments:
8000-16000
Production environments: 64000

LOGPRIMARY

Number of primary log files

25-50
The value of LOGPRIMARY and LOGSECOND together may not exceed 128.

LOGSECOND

Number of secondary log files

Accept the DB2 UDB default value; increase this value if secondary log files are required for your deployment.

SOFTMAX

Triggers bufferpool flushing

50

NUM_IOSERVERS

Number of disks on which the database resides

Number of disks

LOCKTIMEOUT

Lock timeout (sec.)

300

DFT_PREFETCH_SZ

Default prefetch size (pages)

32

Installing the Stored Procedures and User-Defined Functions

NOTE: If you are performing an upgrade from Release 7.x to Release 7.5, skip this procedure. You already completed this step during your upgrade to Release 7.x.

If you are upgrading from Release 6.x and your RDBMS is DB2 UDB, you must install the stored procedures and user-defined functions (UDFs) on the database server. To do this, you must first transfer them to the database server, and have installed the database server components. (For information on installing database server components, refer to the chapter on installing the Siebel Database Server for DB2 Universal Database in Siebel Server Installation Guide for the operating system you are using.)

If you use a different RDBMS platform, skip to Preparing Application Data for Upgrade.

The user-defined functions (UDFs) and stored procedures must be transferred to and installed on the database server to support the Siebel product. Any method that transfers the necessary files to the correct location on the database server is acceptable.

NOTE: Compiling stored procedures in DB2 creates .dll files. You must copy these files to the database server machine's sqllib\function directory.

To copy and install the stored procedure code, follow the procedure appropriate to your operating system. You first must delete the old store procedures. Then you must install the Siebel stored procedure library on the DB2 database server host.

Before you perform this procedure, determine whether your DB2 database software requires upgrading. You might also be required to apply a DB2 fix pack. See Siebel System Requirements and Supported Platforms for more information about required versions.

To delete the old stored procedures

Delete the old stored procedures from the sqllib\function directory.

On the Siebel Database Server on the Windows platform:

nextseq.dll

nextseqd.exe

siebstat.dll

siebsdmy.dll

siebtrun.dll

siebfcst.dll

mapping.dll

updt2col.dll

updt_addr_org_sp.dll

updt_addrorg_intsp.dll

updt_addr_per_sp.dll

On the Siebel Database Server on UNIX:

nextseq

nextseqd

siebstat

siebupg1

siebsdmy

mapping

updt2col

siebtrun

siebfcst

updt_addr_org_sp

updt_addr_per_sp

updt_addrorg_intsp

To copy and install the stored procedure code

Install the Siebel stored procedure library on the DB2 database server host.
Log onto the Siebel source installation machine, and navigate to the source installation subdirectory that contains the Siebel Database installation objects.
The directory that contains the file to install (siebproc) is

DBSRVR_ROOT \DB2UDB\SIEBPROC\DBSRVR_OS

where:

DBSRVR_OS = the operating system your database server runs on; for example, aix.

Put the siebproc file (on Windows this is called siebproc.dll) into the FUNCTION subdirectory within the DB2 UDB instance directory (where DB2 UDB is installed) on the Siebel Database Server.
For example, on Windows, this location might be C:\SQLLIB\FUNCTION.

Table 11. DB2 Database Manager Configuration Parameters
Parameter	Explanation	Setting
`MAX_QUERYDEGREE`	Maximum query degree of parallelism NOTE: Set this parameter only if you have a multiple CPU environment, and you installed the most recent DB2 fix pack identified in Siebel System Requirements and Supported Platforms. Reset this parameter to the original value after the upgrade completes.	Any value between `1` and `6` (maximum). `Use 6 if number of CPUs is 6 or greater.`
`INTRA_PARALLEL`	Enable intra-partition parallelism NOTE: Set this parameter only if you have a multiple CPU environment, and you installed the most recent DB2 fix pack identified in Siebel System Requirements and Supported Platforms. Reset this parameter to the original value after the upgrade completes.	`YES`
`SHEAPTHRES`	Sort heap threshold (4 KB)	Double the value allocated for `SORTHEAP`. See Table 13.
`ASLHEAPSZ`	Application support layer heap size (4 KB)	`15`
`MON_HEAP_SZ`	Database monitor heap size (4 KB)	`128 (`minimum)
`UDF_MEM_SZ`	UDF shared memory set size (4 KB)	`256`
`RQRIOBLK`	Maximum requester I/O block size (bytes)	`65535`
`QUERY_HEAP_SZ`	Query heap size (4 KB)	`16384`
`KEEPDARI`	Keep DARI process	`YES`
`QUERY_HEAP_SZ`	Query heap size (4 KB)	`16384`
`MAX_COORDAGENTS`	Maximum number of coordinating agents	`MAXAGENTS`

Table 12. db2set Parameters
Parameter	Explanation	Setting
`DB2_RR_TO_RS`	Improves DB2 performance with Siebel eBusiness Applications. Set to YES only in production environment servers.	`YES`
`DB2_MMAP_WRITE`	Recommended setting only; you should evaluate this setting for your particular configuration and environment.	`OFF`
`DB2_MMAP_READ`	Recommended setting only; you should evaluate this setting for your particular configuration and environment.	`OFF`
`DB2_CORRELATED_PREDICATES`	When set to ON, the optimizer is able to determine whether predicates in a query are related. This permits DB2 to calculate the filter factor more accurately.	`ON`
`DB2_INDEX_2BYTEVARLEN`	This parameter must always be set to ON. Otherwise, you cannot create indexes with columns greater than 255 bytes.	`ON`
`DB2_PIPELINED_PLANS`	Tells the DB2 optimizer to favor pipeline execution plans; in other words, plans which are left deep and have no temporary result sets.	ON
`DB2_INTERESTING_KEYS`	Limits the number of execution plans generated by the DB2 optimizer.	ON
`DB2_PARALLEL_IO`	Useful when using RAID devices. For more information, refer to relevant IBM documentation.	ON
`DB2_STRIPED_CONTAINERS`	Useful when using RAID devices. For more information, refer to relevant IBM documentation.	ON

Table 13. DB2 Database Configuration Parameters
Parameter	Explanation	Setting
`DFT_DEGREE`	Degree of parallelism (1=turn query parallelism off)	`1`
`DFT_QUERYOPT`	Default query optimization class	`3`
`MAX_QUERYDEGREE`	Index creation	`ANY`
`DBHEAP`	Database heap (4 KB)	`7429`
`CATALOGCACHE_SZ`	Catalog cache size (4 KB)	`5558`
`LOGBUFSZ`	Log buffer size (4 KB)	`512` (For Windows, set this to `256.`)
`UTIL_HEAP_SZ`	Utilities heap size (4 KB)	`5000`
`LOCKLIST`	Maximum storage for lock list (4 KB)	`5000` (This is the minimum setting. It may be increased.)
`APP_CTL_HEAP_SZ`	Maximum applications control heap size (4 KB)	`900` (Recommended size may increase or decrease with the number of users.)
`STMTHEAP`	SQL statement heap (4 KB)	`8192`
`SORTHEAP`	Sort list heap (4 KB)	`20,000—40,000` Recommended size; this may increase or decrease depending on the amount of memory in the database server machine and the size of the data. A 20,000 setting allows `SORTHEAP` to increase up to 80 MB.
`APPLHEAPSZ`	Default application heap (4 KB)	`2500` (Recommended size may increase or decrease with the number of users.)
`STAT_HEAP_SZ`	Statistics heap size (4 KB)	`8000`
`MAXLOCKS`	Percentage of lock lists per application	`5`
`LOCKTIMEOUT`	Lock timeout (sec.)	`90` to `150`
`CHNGPGS_THRESH`	Changed pages threshold	`5`
`NUM_IOCLEANERS`	Number of asynchronous page cleaners	Number of CPUs
`INDEXSORT`	Index sort flag	`YES`
`SEQDETECT`	Sequential detect flag	`YES`
`LOGRETAIN`	Sequential or circular log files	`NO` Setting this parameter to `YES` means that log files are archived. You must periodically move or archive the logs to prevent the file system containing the log files from filling up.
`AVG_APPLS`	Average number of active applications	`1`
`MAXFILOP`	Maximum DB files open per application	`500`
`LOGFILSIZ`	Log file size (4 KB)	Development environments: `8000-16000` Production environments: `64000`
`LOGPRIMARY`	Number of primary log files	25-50 The value of `LOGPRIMARY` and `LOGSECOND` together may not exceed 128.
`LOGSECOND`	Number of secondary log files	Accept the DB2 UDB default value; increase this value if secondary log files are required for your deployment.
`SOFTMAX`	Triggers bufferpool flushing	`50`
`NUM_IOSERVERS`	Number of disks on which the database resides	Number of disks
`LOCKTIMEOUT`	Lock timeout (sec.)	`300`
`DFT_PREFETCH_SZ`	Default prefetch size (pages)	`32`

Upgrade Guide for Microsoft Windows
Published: 20 October 2003