Column-organized table variables

You use column-organized table variables to control the default behavior of a column-organized tables.

Important: These variables are only available from Db2® 11.5.4 and later.
DB2_COL_INSERT_GROUPS
Note: This variable is available in Db2 Warehouse 11.5.6 and later.
  • Operating system: All
  • Default=YES, Values:YES or NO
  • This variable lets you control whether Db2 can group multiple columns onto data pages for column-organized tables.
  • Setting this variable to "YES" results in improved processing time when inserting data into column-organized tables using a trickle-feed process. In addition to improved performance, consumption of memory, storage, and log space is also reduced. A trickle-feed process uses SQL statements to insert a small number of new rows into a table.
  • Changes to this variable do not take effect until the database instance is restarted. To benefit from reduced storage requirements for small tables, existing small tables need to be unloaded and reinserted after this feature is enabled. After enabling this feature, enhanced compression techniques, such as page-based string compression, only apply to newly inserted data. Existing tables need to be unloaded and reinserted to take full advantage of the enhanced compression techniques.
    Important: This is a fallback-incompatible feature. Setting this variable to "YES" results in tables in the database being incompatible with any releases prior to 11.5.6 because this feature changes the on-disk table structure. The 11.5.6 release contains code to handle the on-disk table structure changes. If enabled, this feature must be explicitly disabled prior to falling back from 11.5.7. Falling back to a release prior to 11.5.6 after setting this variable to "YES" can trigger an SQL1784N error when accessing column-organized tables.
DB2_COL_STRING_COMPRESSION
  • Operating system: All
  • Default=UNENCODED_STRING:YES, Values: UNENCODED_STRING:YES or NO
  • This variable lets you control whether Db2 can use the page-based string compression algorithms for string data.
  • Setting this variable to "UNENCODED_STRING:YES" will result in better compression for unencoded string data for new data insertion. However, it will also result in increased query application heap memory usage, and CPU utilization due to decompression.
  • Changes to this variable will not take effect until the database instance is restarted.
  • Existing tables will need to be unloaded and reinserted to take advantage of this feature. For instruction, see section 3 Best Practices to Compress Db2 Column-Organized Tables.
    Important: This is a fallback-incompatible feature. Setting this variable to "UNENCODED_STRING:YES" will cause tables in the database to be incompatible with any releases prior to Db2 11.5.4 because this feature changes the on-disk table structure. Falling back to any prior release after setting this variable to "UNENCODED_STRING:YES" may trigger a SQL1784N error when accessing column-organized tables.
DB2_COL_SYNOPSIS_SETTINGS
  • Operating system: All
  • Default=DEFER_FIRST_SYNOPSIS_TUPLE:YES, Values: DEFER_FIRST_SYNOPSIS_TUPLE: YES or NO
  • This variable lets you control whether the first tuple of the synopsis table will be deferred or not.
  • Setting this variable to "DEFER_FIRST_SYNOPSIS_TUPLE:YES" will result in less storage space consumption from small tables.
  • Changes to this variable will not take effect until the database instance is restarted.
  • Existing tables will need to be unloaded and reinserted to take advantage of this feature. For instructions, see section 3 in Best Practices to Compress Db2 Column-Organized Tables.
    Important: This is a fallback-incompatible feature. Setting this variable to "DEFER_FIRST_SYNOPSIS_TUPLE:YES" will cause tables in the database to be incompatible with any releases prior to Db2 11.5.4 because this feature changes the on-disk table structure. Falling back to any prior release after setting this variable to "DEFER_FIRST_SYNOPSIS_TUPLE:YES" may trigger an assertion when working with any tables used before the fallback.