Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Need help with design of this table in our db

    Hi,

    In our database, we have 287 tables.. the largest table is in its own tablespace and has almost 1 billion records. New records are added to this table 24-7 so am concerned about it's ever growing size.. basically it has columns defined like this --


    RTABLE --

    COL1 INTEGER NOT NULL
    COL2 TIMESTAMP NOT NULL
    COL3 DOUBLE NOT NULL
    COL4 SMALLINT NOT NULL WITH DEFAULT <value>
    COL5 TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP
    COL6 INTEGER WITH DEFAULT <value>


    PRIMARY KEY ( COL1, COL2, COL4)

    COL1 is the id of the entry, COL2 is the timestamp of the entry, COL4 is a value from -1 to 3.


    What do you think is the ideal way to split this table up? Would it be a good idea to split up the table based on year ie have a 2000 table, 2001 table, etc and put them in separate tablespaces? I'm looking to redesign it so that queries are executed faster as well.. currently we have queries that are hitting this table and because of its size, execution time is slow. Thanks!!
    Last edited by db2user24; 06-09-11 at 16:53.

  2. #2
    Join Date
    May 2007
    Posts
    5

    Range Partitioning by Date is one way

    You just do an ADD PARTITION, ATTACH PARTITION or DETACH PARTITION into tablename so you could have your "tablename" in another tablespace as you mention.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2user24 View Post
    What do you think is the ideal way to split this table up? Would it be a good idea to split up the table based on year ie have a 2000 table, 2001 table, etc and put them in separate tablespaces? I'm looking to redesign it so that queries are executed faster as well.. currently we have queries that are hitting this table and because of its size, execution time is slow. Thanks!!
    Would need to see the indexes and all the SQL (DML) that hits this table to come up with an intelligent recommendation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    Hey Marcus,

    I don't have any extra indexes created.. am just using the index based on the primary key...

    Here are the queries we have that are hitting the table.. I also renamed the columns to make it easier to read..thanks for the help!!

    RTABLE --

    RIDENTIFIER INTEGER NOT NULL
    RDATETIME TIMESTAMP NOT NULL
    VALUE DOUBLE NOT NULL
    FIXEDVAL SMALLINT NOT NULL WITH DEFAULT <value>
    DATEADDED TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP
    RAWCOUNT INTEGER WITH DEFAULT <value>


    PRIMARY KEY ( RID, RDATETIME, FIXEDVAL)

    RIDENTIFIER is the id of the entry, RDATETIME is the timestamp of the entry, FIXEDVAL is a value from -1 to 3.


    SQL Queries ---

    INSERT INTO RTABLE (RIDENTIFIER, RDATETIME, VALUE, FIXEDVAL, RAWCOUNT) values ( 1234, '2011-01-01-00.00.00.000000', 200, 0, 900)

    INSERT INTO RTABLE (RIDENTIFIER, RDATETIME, VALUE, FIXEDVAL) VALUES (1234, '2011-01-01-00.00.00.000000', 200, 0)

    UPDATE RTABLE SET VALUE =200 WHERE RIDENTIFIER =1234 AND RDATETIME ='2011-01-01-00.00.00.000000' AND FIXEDVAL =0

    SELECT VALUE, RDATETIME FROM RTABLE WHERE FIXEDVAL = 0 AND RIDENTIFIER = 1234 AND RDATETIME > '2011-01-01-00.00.00.000000' AND RDATETIME <= '2011-01-05-00.00.00.000000' order by RDATETIME

    SELECT RDATETIME , VALUE FROM RTABLE WHERE RIDENTIFIER =1234 AND RDATETIME > '2011-01-01-00.00.00.000000' AND RDATETIME <= '2011-01-05-00.00.00.000000' AND FIXEDVAL=0

    SELECT VALUE, RDATETIME FROM RTABLE WHERE RIDENTIFIER =1234 AND FIXEDVAL =0 AND RDATETIME > (SELECT CDATETIME from CTABLE WHERE CIDENTIFIER= 1234) ORDER BY RDATETIME

    SELECT VALUE FROM RTABLE WHERE RIDENTIFIER =1234 AND FIXEDVAL =0 AND RDATETIME > 2011-01-01-00.00.00.000000' AND RDATETIME <= '2011-01-05-00.00.00.000000' AND FIXEDVAL =0 and VALUE > 0 ORDER BY RDATETIME ASC

    SELECT RDATETIME, VALUE, FIXEDVAL, DATEADDED, RAWCOUNT FROM RTABLE WHERE RIDENTIFIER =1234 AND RDATETIME BETWEEN '2011-01-01-00.00.00.000000' AND '2011-01-05-00.00.00.000000'

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    also, thought i'd mention that we have db2 udb for linux ( centos) version 8.2 fp 11

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How many different timestamps would you have, on average, for each ID? If not too many, you may want to consider adding an index with RDATETIME as the leading column.

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    the data varies based on interval size but a lot of the data is 5-min or 1-min interval only ( mostly ids have 5-min data) .. here are a few examples..

    5 - min interval :
    ID, RDATETIME
    1, '2011-01-01-00.00.00.000000'
    1, '2011-01-01-00.05.00.000000'
    1, '2011-01-01-00.10.00.000000'
    1, '2011-01-01-00.15.00.000000'
    1, '2011-01-01-00.20.00.000000'

    1-min interval :

    ID, RDATETIME
    2, '2011-01-01-00.00.00.000000'
    2, '2011-01-01-00.01.00.000000'
    2, '2011-01-01-00.02.00.000000'
    2, '2011-01-01-00.03.00.000000'
    2, '2011-01-01-00.04.00.000000'
    2, '2011-01-01-00.05.00.000000'


    do u think it would be still be useful? don't want to slow down the current queries.. and from what I understand, adding too many indexes may just do that? thanks!
    Last edited by db2user24; 06-10-11 at 15:33.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Adding indexes can slow down inserts, updates, and deletes (which have to maintain the extra indexes), but does not affect select statements (unless DB2 somehow chooses the wrong index).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    thanks, considering the above scenarios that I've mentioned do you two think it would help to add an index on the table.. maybe just with the columns rdatetime, id ?

    can i even partition the table in version 8.2? seems like it might be only for version 9..

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You did not answer to this question: How many different timestamps would you have, on average, for each ID?

    Also, how big are the RDATETIME ranges that you are selecting? 5 records? 5 million records?

    Table partitioning is available since DB2 9.1. Prior to that the common solution was to use a UAV (union all view) to mimic the behaviour: you would have a separate table for each partition, and a view defined as "select * from part1 union all select * from part2..."

  11. #11
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by n_i View Post
    You did not answer to this question: How many different timestamps would you have, on average, for each ID?

    Also, how big are the RDATETIME ranges that you are selecting? 5 records? 5 million records?

    Table partitioning is available since DB2 9.1. Prior to that the common solution was to use a UAV (union all view) to mimic the behaviour: you would have a separate table for each partition, and a view defined as "select * from part1 union all select * from part2..."
    Each id has data with unique timestamps.. no timestamp is ever repeated per id. The number of records per id is increasing every day and the count varies per id as well.. some have data back to 2000 and others have data starting this year.... checked a couple.. found one that has 810535 records ( unique timestamps) and found another that has 484916 records.. hopefully that answers your question..

    The records are selected via our application so the ranges vary based on the enduser's form selection... typically it can be anywhere from 288 to 105080 records at a time.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try a unique index with the leading date, not ID.

  13. #13
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2user24 View Post
    What do you think is the ideal way to split this table up? Would it be a good idea to split up the table based on year ie have a 2000 table, 2001 table, etc and put them in separate tablespaces?
    Yes, good idea. As you still use V8.2 read this: http://www.ibm.com/developerworks/da...202zuzarte.pdf

Posting Permissions

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