Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41

    Unanswered: row compression test : 9.5 vs 9.7

    Hi, All

    On both DB2 version i create one compressed table (in it's own tablespace/DMS container).

    After LOADding 67M rows i check what DMS-container size is :

    On 9.5.3 - 12 Gb
    On 9.7.0 - 12.6 Gb

    Why 5% difference (600Mb) is presented on 9.7 ?
    Is it some change in compression algorithm on 9.7 ?

    The test table contain only VARCHAR, INTEGER and DECIMAL columns.
    No other tables are presented in databases (pure test)

    Also, i LOAD the same 67M rows in regular table on 9.7 and see what container size for its' tablespace is 14.9 Gb

    I check view SYSIBMADM.ADMINTABCOMPRESSINFO for compressed table and see what PAGES_SAVED_PERCENT and BYTES_SAVED_PERCENT is 77%

    But the real size is of tables (compressed vs noncompressed) is 12.6 Gb vs 14.9 Gb

    Where is this 77% difference ?

    WBR, Dmitry

  2. #2
    Join Date
    Jul 2009
    Posts
    10
    This probably has something to do with the way you measure compression. If you are looking at container sizes, be sure to set tablespace to AUTORESIZE YES with very small INCREASESIZE, e.g. 5M. Allocate tablespace at initial size - make that small too. Create one table in this tablespace and load it.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    And reorganize the tablespace to reclaim any free space.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by db2topgun
    This probably has something to do with the way you measure compression. If you are looking at container sizes, be sure to set tablespace to AUTORESIZE YES with very small INCREASESIZE, e.g. 5M. Allocate tablespace at initial size - make that small too. Create one table in this tablespace and load it.
    Hello, db2topgun

    The test tablespaces is created with AUTORESIZE YES, INCREASESIZE 10Mb and initial size of tablespace container with 100Mb

    For both cases (9.5 and 9.7) this is ONE database with ONE tablespace with ONE table

    Also, i check next query :
    Code:
    SELECT COMPRESSION, 
    AVGROWSIZE, 
    PCTPAGESSAVED, 
    PCTROWSCOMPRESSED,
    AVGROWCOMPRESSIONRATIO,
    AVGCOMPRESSEDROWSIZE
    FROM SYSIBM.SYSTABLES 
    WHERE NAME='ODSTAB_C'
    completed successfully.
    
    COMPRESSION  AVGROWSIZE  PCTPAGESSAVED  PCTROWSCOMPRESSED  AVGROWCOMPRESSIONRATIO  AVGCOMPRESSEDROWSIZE  
    -----------  ----------  -------------  -----------------  ----------------------  --------------------  
    R                   183             14                  5                       1                   158
    And see what PCTPAGESSAVED (14%) and PCTROWSCOMPRESSED (5%)completely different (and see as true) of results (77%) showed by ADMINTABCOMPRESSINFO view and INSPECT ROWCOMPESTIMATE command.

    Why it may be so different ?

  5. #5
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Quote Originally Posted by stolze
    And reorganize the tablespace to reclaim any free space.
    Hello, stolze

    Why reorg is needed when i just LOAD data and no other actvities like DELETE or UPDATE is performed ?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you have no other commands, then you may not need reorg. But that depends on the exact options that you used for LOAD.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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