The appropriate configuration of
your DB2® server ensures that
your database can support shadow tables.
Review this
information to understand what changes are required in your environment.
Follow the steps in Configuring your DB2 server for shadow tables to implement
these changes.
Registry Variables
The following registry
variables that are associated with shadow tables must have specific
values to support them.
- DB2_WORKLOAD
- In a typical BLU Acceleration environment that is optimized for
online analytic processing (OLAP) queries, this registry variable
is set to ANALYTICS to help enable workload management
and configure memory, table organization, page size, and extent size.
In
an online transaction processing (OLTP) environment with shadow tables,
the predominant workload is still OLTP. Therefore, setting the DB2_WORKLOAD registry
variable to ANALYTICS for environments that use shadow
tables is detrimental to OLTP performance.
For shadow tables
environments with predominantly OLTP workloads, ensure that DB2_WORKLOAD is
not set to ANALYTICS and explicitly set the database
manager and database configuration parameters that are described in
the following sections.
- DB2_EXTENDED_OPTIMIZATION
- The requirements for sort heap memory when you use shadow tables
are higher than you would normally have for databases in OLTP environments.
To increase the sort heap memory without affecting existing access
plans for OLTP queries, add OPT_SORTHEAP_EXCEPT_COL to DB2_EXTENDED_OPTIMIZATION to
override the value of the sortheap database configuration
parameter.
The override affects only compiler query optimization
for generating plans that do not access column-organized tables
and does not dictate the amount of actual memory that is available
at run time. If the query accesses a column-organized table,
this override is ignored to allow the query compiler to use the current
value of the sortheap database configuration
parameter that benefits access plans for column-organized tables
as well as shadow tables.
Database manager configuration
Reaching
optimal performance for shadow tables depends on the appropriate configuration
of memory resources at the DB2 instance
level.
- Instance memory
- Setting the instance_memory database manager
configuration parameter to AUTOMATIC results in a
computed value at database activation that specifies the maximum amount
of memory that you can allocate for a DB2 instance.
This value can range 75 - 95% of the physical RAM on the DB2 server and is a good option for shadow tables.
- Sort heap threshold
- For shadow tables, set the tracking of sort memory consumption
at the database level by setting the sheapthres database
manager configuration parameter to 0.
For more
information about limiting the memory allocation for sorts at the
database level, see Shared sort heap
size.
Database configuration
To
create shadow tables in a database, the configuration of this database
must support
column-organized tables
and certain configuration characteristics for shadow tables.
- Supported database configurations for column-organized tables
- To create shadow tables in your existing database, it must conform
with requirements for column-organized tables.
- Default table organization
- In database configurations that support column-organized tables,
the dft_table_org database configuration parameter
controls the behavior of the CREATE TABLE statement without the ORGANIZE
BY COLUMN or the ORGANIZE BY ROW clause. If you specify either of
these clauses, the value in dft_table_org is
ignored.
If the dft_table_org parameter is
set to ROW, the CREATE TABLE statement without an
organization clause creates row-organized tables
in the same fashion as in previous releases. If the dft_table_org parameter
is set to COLUMN, you must specify the ORGANIZE BY
ROW clause with the CREATE TABLE statement to create row-organized tables.
To maintain the behavior of previous releases, set the dft_table_org parameter
to ROW.
Databases that support shadow tables
have mixed workloads. These databases were previously configured for
online transaction processing (OLTP). To preserve this behavior and
to create new tables as row-organized tables
by default, ensure that the dft_table_org (default
table organization for user tables) database configuration parameter
is set to ROW.
- Page size for buffer pools and table spaces
- Shadow tables benefit from having a larger page size for buffer
pools and table spaces such as 32 K for column-organized tables.
Specify
32 K or an adequate page size value when you create table spaces for
shadow tables. For more information about using 32 K page size, see Memory management for shadow tables.
- Extent size for table spaces
- Using an extent size of 4 pages in table spaces
where you create shadow tables can improve performance. However, do
not change the database default extent size because that change impacts
your existing database configuration. Instead, specify an adequate
extent size (such as 4 pages) only for the table
spaces where you have or plan to have shadow tables.
- Database shared memory size
- For optimal database performance, you must specify an amount of
the database memory large enough to accommodate for the following
configurable memory pools: buffer pools, the database heap, the lock
list, the utility heap, the package cache, the catalog cache, the
shared sort heap, and a minimum overflow area of 5%.
Set the database_memory database
configuration parameter to a specific value and AUTOMATIC to allow
the database memory to grow beyond its initial size. This setting
accommodates any unforeseen requirements beyond what the overflow
area provides. The initial size depends on the amount of memory that
is allocated for the DB2 instance
and the number of databases that are managed by the DB2 instance.
- Database memory threshold
- The default setting of the db_mem_thresh database
configuration parameter is set to 100, which instructs the database
manager to never release any unused database shared memory. This setting
is suitable for most workloads, including OLTP and OLAP (mixed) workloads.
- Fast communications manager (FCM)
- To run mixed workloads in environments in which intrapartition
parallelism is enabled, 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.
- Shared sort heap size
- Processing data for column-organized tables
requires larger values for the sortheap and sheapthres_shr database
configuration parameters than the values you might have for your OLTP
environment.
For a database with shadows tables, set these parameters
to an adequate starting value instead of using
AUTOMATIC.
- A good starting value for the sheapthres_shr parameter
is 40 - 50% of the database_memory parameter.
Specify a higher percentage for higher analytics concurrency.
- A good starting value for the sortheap parameter
is 5 - 20% of the sheapthres_shr parameter to
enable concurrent sort operations. Specify a lower percentage for
higher analytics concurrency.
- Catalog cache size
- Extra space is required in the catalog cache size to accommodate
synopsis tables. For every shadow table or column-organized table,
the database manager creates a synopsis table to store metadata for
the column-organized table.
A
good starting increment is adding 10% of the current catalogcache_sz parameter
value.
- Utility heap size
- InfoSphere® CDC uses
the LOAD command for the refresh operation on shadow
tables. To address the resource needs of the LOAD command,
set the util_heap_sz database configuration parameter
to an appropriate starting value and AUTOMATIC.
A good starting
value is 1 million of 4 K pages. If the database server has at least
128 GB of memory, set the util_heap_sz parameter
to 4 million 4 K pages. If you are going to run concurrent workloads
of the LOAD command, increase the util_heap_sz parameter
to accommodate higher memory requirements. If memory resources are
limited, you can increase the util_heap_sz parameter
dynamically only when you run the LOAD command
for shadow tables.
- Primary log archive method
- IBM® InfoSphere Change Data Capture requires
that the DB2 database is enabled
for archive logging for capturing transactions.
Set the logarchmeth1 database
configuration parameter to enable archive logging. This parameter
specifies that active log files are retained and become online archive
log files for use in rollforward recovery. Choose the DB2 supported
log archival method that aligns better with your IT disaster recovery
guidelines. If the logarchmeth1 parameter is
set to OFF, InfoSphere CDC cannot
capture transactions for the source table.
- Automatic maintenance
- Performing maintenance activities such as updating statistics
and reorganizing tables and indexes on your databases is essential
for optimal performance and recoverability of your database, especially
shadow tables.
If you choose to take advantage of automatic maintenance
capabilities, you can enable automatic statistics collection and automatic
reorganization by setting up the automatic maintenance configuration
parameters.
If automatic statistics collection and automatic
reorganization does not suit your environment, you might want to use
the following guidelines for manually performing these maintenance
tasks:
- Specify the ON ALL COLUMNS WITH DISTRIBUTION
ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL clause with
the RUNSTATS command to collect both basic statistics
and distribution statistics on columns, and extended statistics on
all table indexes.
- Specify the RECLAIM EXTENTS clause with the REORG command
on indexes and tables to reclaim extents that are not being used.
Use the value of the RECLAIMABLE_SPACE column that is returned by
a query to the ADMINTABINFO administrative view or the ADMIN_GET_TAB_INFO
table function. This value is the amount of free space that was determined
the last time that you ran the RUNSTATS command.
After a refresh is perform on a shadow table,
manually issue the RUNSTATS command on the shadow
table to ensure that the statistics are fully updated immediately
after the refresh even when automatic statistics collection is enabled.
Settings at the application level
- Intrapartition parallelism
- Although intrapartition parallelism is required for accessing column-organized tables
and shadow tables, you might not be able to change this default without
affecting your existing OLTP workloads.
To enable intrapartition
parallelism:
- You can set the intra_parallel database manager
parameter to YES for a DB2 instance.
- You can set MAXIMUM DEGREE to a value greater than 1 for a particular
workload at the database level.
- You can call the SYSPROC.ADMIN_SET_INTRA_PARALLEL procedure with YES as
parameter value for a database application.
For shadow tables, enabling intrapartition parallelism
at the application level is the best option.
DB2 10.5 for Linux, UNIX, and Windows