Section 15.3. Database-Level Shared Memory

15.3. Database-Level Shared Memory

When you issue the activate database command or connect to a database for the first time, DB2 automatically allocates the database-level shared memory (see Figure 15.3). This memory caches the object pages that are being manipulated to control concurrency on the rows of data, ensure transactional integrity for all operations on the database, and so on.

Figure 15.3. Database shared memory

The components of database shared memory include:

• The database buffer pool(s)

• The lock list

• The shared sort area (if intra-partition parallelism is enabled)

• The database heap, which also includes

  - The log buffer

  - The catalog cache

• The package cache

• The utility heap, which is used by processes such as backup and restore

15.3.1. The Database Buffer Pools

The database buffer pools area is usually the largest component of the database shared memory. As discussed in Chapter 7, Working with Database Objects, this is the area of memory where DB2 manipulates all regular and index data. A database must have at least one buffer pool, and it can have a number of buffer pools depending on the workload characteristics, database page sizes used in the database, and memory available on the system. Each individual buffer pool is sized independently, but DB2 allocates the total size of all buffer pools within the database shared memory area.

15.3.2. The Database Lock List

To control access to rows that are being read and written, DB2 must lock the rows until the operation completes. Information about each lock that DB2 obtains is stored in the database lock list until the lock is released. The LOCKLIST configuration parameter specifies the amount of memory that can be used to store the lock information. If this memory pool becomes full, DB2 performs lock escalation to free up some space in the lock list.

15.3.3. The Database Shared Sort Heap Threshold

If you enable intra-partition parallelism, DB2 may choose to perform a shared sort if it thinks using that method will be more efficient than a private sort. If DB2 performs a shared sort, it allocates the sort heap for the sort in database shared memory. The SHEAPTHRES_SHR configuration parameter limits the amount of shared sort memory that can be allocated for the database. If a new shared-sort request is made, and the allocation of the sort heap would exceed the shared sort heap threshold (SHEAPTHRES_SHR), DB2 will not allocate the sort heap and will perform an overflowed sort.

15.3.4. The Package Cache

For dynamic SQL, performance can be greatly improved if DB2 is able to reuse an already compiled access plan rather than having to perform the access plan generation. The package cache stores already compiled access plans so that they can be reused. Since multiple users can run the same applications, and therefore the same SQL statements, DB2 uses a shared package cache so that you can reuse an access plan even if it was originally compiled for another user. The size of the package cache is specified by the PCKCACHESZ configuration parameter.

15.3.5. The Utility Heap Size

DB2 does not allocate the utility heap when you first connect to or activate the database, but it is allocated if you perform a backup or restore operation. You need to be aware of this and plan for this, especially if you are working on a 32-bit operating system, which has very strict limits on the amount of shared memory that can be allocated. (32-bit and 64-bit memory models are described in sections 15.7 and 15.8, respectively.)

15.3.6. The Catalog Cache

The catalog cache is used to store previously accessed information such as object descriptions and privileges. In a multi-partitioned database, the catalog cache will be allocated on all partitions, but the information is not replicated. Each partition's catalog cache will contain information that has been previously accessed on that partition.

15.3.7. Database Logging Parameters

As you change data in your database, DB2 needs to log the changes so that it can:

• Roll back the changes if you issue the ROLLBACK statement

• Perform recovery operations if the server fails for any reason

To be able to perform well, DB2 cannot log the work that it is doing directly to disk, so DB2 uses a write-ahead logging algorithm to ensure that the changes are written to the log buffer (LOGBUFSZ) before they are made to the underlying index and data pages. DB2 also ensures that the log buffer is flushed to disk whenever a commit (or MINCOMMIT commits) occurs so that it is able to reapply any committed changes and can undo any uncommitted changes. This is discussed in more detail in Chapter 13, Developing Database Backup and Recovery Solutions.

The log buffer and the catalog cache are allocated within the database heap along with some other database-level control information. In addition, for every page in the buffer pools and extended storage, DB2 uses 12 bytes of memory in the database heap to store information about the state of the page.

15.3.8. Database Memory

The total amount of database shared memory allocated at a given time is the sum of all of these components. In DB2 Version 8 there is a new configuration parameter called DATABASE_MEMORY. If you install DB2 on a 64-bit operating system, you can change these configuration parameters dynamically, and DB2 can then extend or reduce the sizes of these shared memory areas. However, in 32-bit operating systems, once a segment of shared memory is allocated it cannot be extended or reduced. To overcome this limitation you can reserve extra database shared memory up to the operating system limit using the DATABASE_MEMORY configuration parameter. This will allow you to create new buffer pools or enlarge these memory areas dynamically.

If you want to figure out a good value to use for the DATABASE_MEMORY configuration parameter, you can set it to AUTOMATIC initially (which is the default). Then connect to the database and run the command:

 get db cfg for dbname show detail 

This command will show you how much memory is currently allocated in database shared memory area, and you can then determine what value to use based on this information. The following is a partial output of this command:

 Size of database shared memory (4KB)(DATABASE_MEMORY=AUTOMATIC(177764) 

This shows that there are 177,764 pages currently allocated in the database shared memory area.

As discussed earlier for instance-level memory, you can also use the db2mtrk tool to display the database shared memory used for your databases (you must specify I on Windows; on UNIX, -i is optional):

 db2mtrk i d v Memory for database: SAMPLE    Backup/Restore/Util Heap is of size 16384 bytes    Package Cache is of size 81920 bytes    Catalog Cache Heap is of size 65536 bytes    Buffer Pool Heap is of size 4341760 bytes    Buffer Pool Heap is of size 655360 bytes    Buffer Pool Heap is of size 393216 bytes    Buffer Pool Heap is of size 262144 bytes    Buffer Pool Heap is of size 196608 bytes    Lock Manager Heap is of size 491520 bytes    Database Heap is of size 3637248 bytes    Other Memory is of size 16384 bytes    Application Control Heap is of size 327680 bytes    Application Group Shared Heap is of size 57344000 bytes    Total: 67829760 bytes 

Notice there are five buffer pool heaps allocated; the following are the four hidden buffer pools:

 Buffer Pool Heap is of size 655360 bytes Buffer Pool Heap is of size 393216 bytes Buffer Pool Heap is of size 262144 bytes Buffer Pool Heap is of size 196608 bytes 

In this case if you run the statement

 select * from syscat.bufferpools 

you will get only one row returned, because the hidden buffer pools do not have entries in the buffer pool table.

To change this value and reserve some memory so that you can add a new buffer pool, or to increase the size of the lock list or log buffer dynamically, use the command:

 update db cfg for dbname using database_memory 250000