Creating and setting up your database configuration for analytic workloads

If DB2_WORKLOAD is not set to =ANALYTICS, you should create and optimally configure your database for analytic workloads.

Before you begin

You must have an instance that was created on Db2® Version 10.5.
Ensure that your system configuration is supported. For more information, see Restrictions, limitations, and unsupported database configurations for column-organized tables.

About this task

The way that you create a database and its configuration influence the performance of analytic workloads and column-organized

Restrictions

Check the restrictions that are listed in Restrictions, limitations, and unsupported database configurations for column-organized tables.

Procedure

To manually create and configure your database for analytic workloads:

Create the database with a 32K page size, a UNICODE code set (default), and an IDENTITY or IDENTITY_16BIT collation. The following example shows how to create a database that is called DMART with such characteristic:
```
CREATE DATABASE DMART COLLATE USING IDENTITY PAGESIZE 32 K
```
Ensure that the sheapthres database manager configuration parameter is set to 0 (default value). Note that this setting applies to all databases in the instance.
Update the database configuration as follows:
1. Set the dft_table_org (default table organization for user tables) database configuration parameter to COLUMN so that new tables are created as column-organized tables by default; otherwise, the ORGANIZE BY COLUMN clause must be specified on each CREATE TABLE statement.
2. Set the dft_degree (default degree) database configuration parameter to ANY.
3. Set the dft_extent_sz (default extent size) database configuration parameter to 4.
4. Increase the value of the catalogcache_sz (catalog cache) database configuration parameter by 20% (it is set automatically during database creation).
5. Ensure that the sortheap (sort heap) and sheapthres_shr (sort heap threshold for shared sorts) database configuration parameters are not set to AUTOMATIC. Consider increasing these values significantly for analytics workloads. A reasonable starting point is setting sheapthres_shr to the size of the buffer pool (across all buffer pools). Set sortheap to some fraction (for example, 1/20) of sheapthres_shr to enable concurrent sort operations.
6. Set the util_heap_sz (utility heap size) database configuration parameter to 1,000,000 pages and AUTOMATIC to address the resource needs of the LOAD command. If the database server has at least 128 GB of memory, set util_heap_sz to 4,000,000 pages. If concurrent load operations are running, increase the value of util_heap_sz to accommodate higher memory requirements.
7. Set the auto_reorg (automatic reorganization) database configuration parameter to ON.
Note: This database configuration increases the overall database memory requirements. If the database_memory configuration parameter is set to AUTOMATIC, consider increasing its value.
Ensure that intraquery parallelism is enabled to be able to access column-organized tables. You can enable intraquery parallelism at the instance level, database level, workload level, or application level. For details, see Enabling parallel processing for column-organized tables.
Enable concurrency control on the SYSDEFAULTMANAGEDSUBCLASS service subclass by issuing the following statement:
```
ALTER THRESHOLD SYSDEFAULTCONCURRENT ENABLE
```