Performance Tuning for Content Manager

232 Performance Tuning for Content Manager

8.6.24 Statement heap size (STMTHEAP)

Impact

DB - Medium

Description

The statement heap is used as a work space for the SQL compiler during

compilation of an SQL statement. This parameter specifies the size of this work

space.

This area does not stay permanently allocated, but is allocated and released for

every SQL statement handled. Note that for dynamic SQL statements, this work

area will be used during execution of your program. For static SQL statements, it

is used during the bind process, but not during program execution.

Default values (range)

2,048 pages (128 - 65,535) 4-KB pages

To update

Run the following DB2 command:

db2 update db cfg for <database name> using STMTHEAP <parameter value>

Our recommendation

The Content Manager installation program changes this value to 16,384 for the

Library Server database.

We recommend using this default value. If your application uses very large SQL

statements and an error occurs when optimizing a statement (stating that the

statement is too complex), increase this value in increments (such as 256 or

1024) until the error situation is resolved.

8.6.25 Query heap size (QUERY_HEAP_SZ)

Impact

DBM - Medium

Description

This parameter specifies the maximum amount of memory that can be allocated

for the query heap. A query heap is used to store each query in the agent’s

private memory. The information for each query consists of the input and output

SQLDA, the statement text, the SQLCA, the package name, creator, section

number, and consistency token. When an application (either local or remote)

connects to the database, query heap is allocated. When the application

Chapter 8. Tuning DB2 for Content Manager 233

disconnects from the database or detaches from the instance, it is freed. This

parameter ensures that an application does not consume unnecessarily large

amounts of virtual memory within an agent.

The query heap is also used for the memory allocated for blocking cursors. This

memory consists of a cursor control block and a fully resolved output SQLDA.

The initial allocated query heap will be the same size as the application support

layer heap, as specified by the ASLHEAPSZ parameter. The query heap size

must be greater than or equal to 2, and must be greater than or equal to the

ASLHEAPSZ parameter. If this query heap is not large enough to handle a given

request, it will be reallocated to the size required by the request (not exceeding

QUERY_HEAP_SZ). If this new query heap is more than 1.5 times larger than

ASLHEAPSZ, the query heap will be reallocated to the size of ASLHEAPSZ

when the query ends.

Default values (range)

1,000 (2 – 524,288) 4-KB pages

To update

Run the following DB2 command:

db2 update dbm cfg using QUERY_HEAP_SZ <parameter value>

Our recommendation

The Content Manager installation program changes this value to 32,768 during

the Library Server installation, and the program changes the value to 16,384

during the Resource Manager installation. If you install both servers on the same

machine and on the same instance, the value set by the Resource Manager

installation will be used for both servers. You should change the value back to

32,768.

Use this value initially. If you have memory constraints, reduce the value — but

not lower than five times the value of the ASLHEAPSZ

. This will allow for queries

larger than ASLHEAPSZ and provides additional memory for three or four

blocking cursors to be open at a given time.

If you have very large LOBs, you might need to increase the value of this

parameter for the query heap to be large enough to accommodate those LOBs.