Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: compressed tables doesnot have a dictionary Built.

    I have compressed all the tables in my database and checking for dict_builder in the table. Found that some of the tables have compressed option but dictionary was not built.

    executed offline reorgs and checked them again still the same.
    Loaded more data into the tables .It has million rows executed reorgs again still no dictionary was built.

    Code:
    db2 "select TABSCHEMA,TABNAME,COMPRESS_ATTR,DICT_BUILDER from SYSIBMADM.ADMINTABCOMPRESSINFO
    For some tables which has 10 to 15 rows db2 shows that dicitonary was built but for some tables which have more then million rows db2 shows that dictionary was not built all the tables have normal datatypes like varchar,int & date .

    Can someone tell me why is db2 not building dictionary fore few tables after reorg or loads.?

    My Db2 version is v9.7

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Can you show output of that command to verify that the tables are all have COMPRESS YES?

    Also, what is your fixpack level (db2level command).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Please show the exact commands that you ran to enable compression and create dictionaries.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2010
    Posts
    32
    These are the commands I am running and it's on sample database .

    Code:
    db2 "reorg table PRODUCT"
    DB20000I  The REORG command completed successfully.
    db2 "select TABNAME,COMPRESS_ATTR,DICT_BUILDER from SYSIBMADM.ADMINTABCOMPRESSINFO where tabname = 'PRODUCT'"
    
    TABNAME                                                                                                                          COMPRESS_ATTR DICT_BUILDER
    -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------------------
    PRODUCT                                                                                                                          Y             NOT BUILT
    
      1 record(s) selected.
    
    db2 "select count(*) from product"
    
    1
    -----------
              4
    
      1 record(s) selected.

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    There needs to be a certain minimum amount of data in the tables before a dictionary can make sense... 4 rows does not meet that requirement.
    In your previous post you wrote about a table with a million rows that did not have a dictionary built - can you show the details (query+results) for that table?
    Read the docs for more info about the minimum size of data that has to be loaded before the dictionary will get built.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •