If DB2_WORKLOAD is not set to =ANALYTICS,
you should create and optimally configure your database for analytic
workloads.
About this task
The way that you create a database and its configuration
influence the performance of analytic workloads and column-organized
Restrictions
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:
- 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.
- Set the dft_degree (default degree) database
configuration parameter to ANY.
- Set the dft_extent_sz (default extent size)
database configuration parameter to 4.
- Increase the value of the catalogcache_sz (catalog
cache) database configuration parameter by 20% (it is set automatically
during database creation).
- 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.
- 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.
- 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