Configuring your DB2 server for shadow tables

Configuring your DB2® server for shadow tables requires that you set registry variables, database manager configuration parameters, and database configuration parameters to specific values.

To configure your DB2 server for shadow tables:

1. Save your DB2 environment configuration by running the db2support command for the databases where you are going to create shadow tables. Use the -cl 0 parameter to collect the database system catalog, database and database manager configuration parameters settings, and DB2 registry variables settings.

   db2support output-directory -d db2-database-name -cl 0

   The information that is collected is stored in the db2support.zip compressed file under the output directory. A summary report in HTML format is included. Check the optimizer.log file in db2supp_opt.zip to verify that the information was collected successfully.
2. Add OPT_SORTHEAP_EXCEPT_COL to the DB2_EXTENDED_OPTIMIZATION registry variable to indicate an override value of the sortheap database configuration parameter for queries that do not reference column-organized tables. Specify the existing value of the sortheap database configuration parameter as the override value. The following example shows how to determine the existing value of sortheap and then set the override value with DB2_EXTENDED_OPTIMIZATION:

   $ db2 get db cfg for db2-database-name | grep -i sortheap
   Sort list heap (4KB)                         (SORTHEAP) = 10000
   $ db2set DB2_EXTENDED_OPTIMIZATION="OPT_SORTHEAP_EXCEPT_COL 10000"  

3. Set the instance_memory database manager configuration parameter to AUTOMATIC. The following example shows how to set this parameter to AUTOMATIC:

   $ db2 update dbm cfg using instance_memory AUTOMATIC

4. Ensure that you have enough fast communication buffers and channels by setting the fcm_num_buffers and fcm_num_channels database manager configuration parameters to larger values and AUTOMATIC. The following example shows how to set these parameters to values that are suitable for most mixed workloads of OLTP and OLAP:

   $ db2 update dbm cfg using fcm_num_buffers 4096 AUTOMATIC
                              fcm_num_channels 2048 AUTOMATIC 

5. Set sheapthres to 0 to track sort memory consumption at the database level. The following example shows how to set this parameter to 0:

   $ db2 update dbm cfg using sheapthres 0

6. Ensure that dft_table_org is set to ROW to support primary OLTP workloads. To create shadow tables, explicitly specify column organization with the ORGANIZE BY COLUMN clause. The following example shows how to set the default table organization to ROW:

   $ db2 update db cfg for db2-database-name using dft_table_org ROW

7. Set the database_memory database configuration parameter to a specific value and AUTOMATIC to allow the database memory to grow beyond this initial value. The following example shows how to set database_memory to 20 GB (5 million 4-K pages) and AUTOMATIC:

   $ db2 update db cfg for db2-database-name
           using database_memory 5000000 AUTOMATIC

8. Ensure that the db_mem_thresh database configuration parameter is set to 100 to instruct the database manager to never release any unused database shared memory. The following example shows how to set db_mem_thresh to 100:

   $ db2 update db cfg for db2-database-name using db_mem_thresh 100

9. Ensure that sortheap and sheapthres_shr database configuration parameters are not set to AUTOMATIC. Follow these guidelines to calculate appropriate values for these parameters:
   1. Calculate the maximum amount of memory that can be allocated for the DB2 instance as 95% of the physical RAM. For example, if your DB2 server has 128 GB of physical RAM, the maximum amount of memory is approximately 120 GB.
   2. For databases with shadow tables, assign 85% of the instance memory to the database. For example, if the maximum amount of memory is 120 GB, instance memory is approximately 100 GB.
   3. Subtract 4 GB from the database memory, which is required for InfoSphere CDC software components. For example, if instance memory is 100 GB, database memory is 96 GB.
   4. Set sheapthres_shr to 50% of the database memory and set sortheap to 5% of sheapthres_shr. For example, 50% of 96 GB is 48 GB or 12 million 4K pages and 5% of 48 GB is 2.4 GB or 600 thousand 4K pages. The following command shows how to set these values:

      $ db2 update db cfg for db2-database-name	
              using sheapthres_shr 12000000 sortheap 600000 

10. Increase the value of catalogcache_sz by 10% to have enough space for synopsis tables. The following example shows how to increment catalogcache_sz by 10%:

    $ db2 get db cfg for db2-database-name | grep -i catalogcache_sz     
    Catalog cache size (4KB)                         (CATALOGCACHE_SZ) = 300     
    $ db2 update db cfg for db2-database-name using catalogcache_sz 330

11. Ensure that you set util_heap_sz to an appropriate value and AUTOMATIC. The following example shows how to set util_heap_sz to 1000000 AUTOMATIC:

    $ db2 update db cfg for db2-database-name
             using util_heap_sz 1000000 AUTOMATIC

    If the database server has less than 128 GB of memory, a good starting value is 1 million of 4 K pages. If the database server has more than 128 GB of memory, set util_heap_sz to 4 million 4 K pages. To run concurrent workloads of the LOAD command, increase util_heap_sz to accommodate higher memory requirements.
12. If the logarchmeth1 database configuration parameter is set to OFF, set it to a value other than OFF to enable archive logging. The following example shows how to enable archive logging and then perform a full database backup:

    $ db2 update db cfg for db2-database-name
             using logarchmeth1 logretain
    $ db2 backup db db2-database-name

    Use a setting that indicates the archiving method that best suits your environment.
13. If you want to enable automatic statistics collection and automatic reorganization, set auto_maint, auto_runstats, and auto_reorg database configuration parameters to ON. The following example shows how to enable automatic statistics collection and automatic reorganization:

    $ db2 update db cfg for db2-database-name
             using auto_maint ON auto_runstats ON auto_reorg ON