DOMINO AND DB2

Modifying the database sort heap and locklist parameter values
You can improve IBM® DB2 Universal Database™ Enterprise Server Edition performance by increasing or decreasing the amount of memory allocated to the sort heap and locklist database configuration parameters.

Sortheap configuration parameter

The sort heap is the maximum number of private memory pages used for private sorts, or the maximum number of shared memory pages used for shared sorts. For private sorts, sort heap affects agent private memory. For shared sorts, sort heap affects the database shared memory. Each sort has a separate sort heap that is allocated as needed by the database manager. The sort heap is the area where data is sorted. If directed by the optimizer, a smaller sort heap than the one specified by this parameter is allocated using information provided by the optimizer.

The sort heap value is too small if you see this Command Line Interface message:


The sort heap value is too large if you see this Command Line Interface message:
To check the SORTHEAP value, from the CLI enter this command:
To check the value of SHEAPTHRES, from the CLI enter this command:
If you see several of the CLI message "sort statement overflows," a sort overflow is occurring with your application. Review the access plan and try to remove the sort.

Locklist configuration parameter

The locklist database configuration parameter designates the amount of storage that is allocated to the lock list. There is one lock list per database and it contains the locks held by all applications concurrently connected to the database. Locking is the mechanism that the database manager uses to control concurrent access to data in the database by multiple applications. Both rows and tables can be locked. The database manager can also acquire locks for internal use.

Caution If the locklist parameter is too small, DB2 may consolidate multiple row locks into an exclusive table lock. If the DB2 monitor snapshots show numerous row locks escalated to table locks, consider increasing the size of the locklist parameter. Negative locking behavior has a severe impact on performance.

For information about using a DB2 monitor snapshot to detect negative locking behavior, see the topic Using a snapshot to detect negative locking behavior.

When the DB2-enabled IBM® Lotus® Domino™ server creates and configures the DB2 database, LOCKLIST is set to 2500 (at 4K pages this is 10 MB of space). For very active servers, you may need to increase this value to 5000 or even 10000. You can also run the autoconfigure command to see what autoconfigure recommends.

For information about the autoconfigure command, see Using the autoconfigure command to automatically set database configuration parameters.

The locklist database configuration parameter can be increased online but not decreased. If you want to decrease the locklist value, you must reactivate the database.

1. Open the DB2 Control Center.

2. Click All Databases, and then locate the initial DB2 database you created. If you did not change the default database name, the name is DOMINO.

3. Select the database name, and then right click the mouse.

4. Choose Configure Parameters. The Database Configuration dialog box appears.


5. In the Performance section, select the parameter keyword Sort Heap. In the value field, enter a greater value than the value that is displayed.

6. Select the parameter keyword Locklist, specify a value that is 2X greater than that which is displayed.

Note For information about the parameters on the Database Configuration dialog box, see the DB2 Information Center at http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/db2idxA.htm.

7. Click OK.

See also