Building better quality dictionaries while doing CTAS in Warehouse SaaS / CP4D SaaS

Product affected: Db2 Warehouse on Cloud

Feature affected: CTAS is a Db2 statement - CREATE TABLE AS functionality (CTA)

Overview

GEICO sees very bad compression as compared to Netezza.

Its been observed that currently CTAS uses only the first part of data (default 1 million rows or 500K rows per partition) to build the dictionary. This results in poor quality dictionaries for large tables and poor compression.

The customer (GEICO) wants CTAS to build better quality dictionaries (i.e., through sampling the whole data of the source table).

Please refer to the case -TS011321960 that was opened in relates to this to get more details.

Description: Basically GEICO is using CTAS statements in their day to day operations ...almost daily ...when they use CTAS on very large tables, by default the dictionary that gets built is not providing good compression results on to the newly built table & leads to a lot of space consumption and is a big problem currently in GEICO env. When the case was opened, developers / team looked at it and suggested that CTAS can be enhanced further to build good compression dictionary (for example making use of Bernoulli algo ??) so that good compression dictionary can be built which provide more compression ratio and avoiding to use large space built out of CTAS. U can connect with @gopalv for more details on to it who is working with GEICO team day to day basis.

Point of contact : Venkatesh Gopal
GEICO DATA&AI CSM : GANESH GOSAVI