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

    Unanswered: DB2 10.5 Technology Preview

    Hello

    Has anyone tried this DB2 Technology Preview ?
    The new column-stored tables functionality looks very interresting and IBM anonce very impressive performance improvements for 10.5

    I run small test to compare fullscan performance on colmn-table vs. traditional row-stored table and get some strange results :

    After setting DB2_WORKLOAD=ANALITYC i create test database with two identical tables (one traditional adaptive compressed row store table and one with new column stored (ORGANAIZE BY COLUMN)) in one tablespace. Both tables loaded with 80M records. After loading and runstats i see what size of colum-stored table is more in size than row-stored table about 20%

    I run simple test SELECT statement with grouping to simulate fullscan on both tables and get next result :

    For executiona in one thread the query elapsed time on column-table is approximately 25% more than on the row-table, but when i enable intra_paralle mode and run test query in (2,4,6,8) threads the elapsed time for column-stored table is 5 time slowly than on row-stored table.

    Also multithread execution time is longer than single thread execution (for example 109 sec. when degree=1 and 187 sec when degree=4). According to infocenter i increase the SORTHEAP to 2GB, but this not help.

    Also, the instance is very often fail after query execution and other operations.

    On my TP VM i have 8vCPU and 8GB RAM

    On TP instance the db2level command show DB2 version of 10.2.0.0. Is this codebase same as future 10.5 ?

    Best regards, Dmitry

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is called 10.2 because it is beta test code that was built before the decision to make it 10.5. The organize by column basically creates an index for every column and there is no table per se.

    Whether or not organize by column is beneficial is highly dependent on the type of database and the type of queries. I don't want to be overly pessimistic (since it does have some benefits) but my suspicion is that organize by column is mainly going to be a selling point to potential DB2 customers (especially executives/managers who don't know much as databases) who are trying to decide on a DBMS, but not quite as useful to DBA's in the real world. Obviously, there are some tables/situations where it will have some benefits. But it is not a panacea, and similar to MDC, it creates its own set of problems even if it does have some benefits.
    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
    Apr 2013
    Posts
    1
    Dmitry, can you share the exact SQL and some info how wide was the table?

    In general column based organization should be more efficient, if you select small number of column from a large set (wide tables). If you select all column in the query the row organized tables should be faster, as you avoid heavy integration of the separate result set. Column based organization is not a panacea for every workload.

    Art

  4. #4
    Join Date
    Sep 2007
    Location
    Moscow
    Posts
    41
    Art

    The test table have 21 column and test query reference only 5 columns.

    For my described case i get some help in problem investigation from BLU developers and after check db2performance.log they said what it seems there is a problem that was recently fixed after current TP image builded.

Posting Permissions

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