Memory management for shadow tables

You can help ensure that there are sufficient memory resources for queries against shadow tables by checking the statement heap size, by using dedicated buffer pools, and by using concurrency control.

Ensure that the statement heap size is adequate

The statement heap is used as a work space for the SQL compiler during compilation of an SQL statement. Typically, OLAP queries are complex and require a larger amount of memory for compilation. If you start receiving an SQL0437W warning for your queries that involve shadow tables, double the size of the statement heap by updating the stmtheap database configuration parameter:

$ db2 get db cfg for <db2-database-name>  | grep -i stmtheap 
SQL statement heap (4KB)             (STMTHEAP) = AUTOMATIC(8192) 
$ db2 update db cfg for <db2-database-name> using stmtheap 16384 automatic

Use dedicated buffer pools

For performance reasons, keep row-organized tables and shadow tables in separate buffer pools.

A reasonable starting point for the buffer pool size (across all buffer pools) is to use some fraction of database_memory database configuration parameter (25-40% of database shared memory) and then split it between the row-organized tables and shadow tables. Because shadow tables have a higher compression ratio, the total amount of memory that is allocated for the shadow table buffer pool can be smaller than the amount allocated for row-organized tables. A rule of thumb is to use 5-10% of the buffer pool size for the shadow table buffer pool, and the rest for buffer pools that are used for row-organized tables.

For example, if the database shared memory size is 96 GB and the total amount of memory that is allocated for all buffer pools is 38 GB (40% of the database shared memory), then in order to create a 3.8 GB shadow table buffer pool (118,750 32K pages or 10% of the buffer pool size), run the following command:

$ db2 create bufferpool bp32kcol size 118750 pagesize 32k

The remaining 90% of the overall buffer pool size can be used for row-organized tables buffer pools.

Tip: The recommended value for the size of pages that are used for buffer pools for column-organized tables (like shadow tables) is 32K, with the extent size for table spaces set to 4. To create a table space that is used for shadow tables, run the following command:

$ db2 create tablespace tbsp32kcol pagesize 32k extentsize 4 bufferpool 
bp32kcol

Use concurrency control

Many concurrently running high-cost queries can have an impact on system performance and stability. When running in an adaptive workload manager environment, concurrency control is automatically managed. When not running in an adaptive workload manager environment, use concurrency control to regulate the admission of heavyweight queries (which are controlled and monitored in the SYSDEFAULTMANAGEDSUBCLASS service subclass) by issuing the following statement:

  $ db2 alter threshold SYSDEFAULTCONCURRENT enable