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!!

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.

Would need to see the indexes and all the SQL (DML) that hits this table to come up with an intelligent recommendation.

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'

also, thought i'd mention that we have db2 udb for linux ( centos) version 8.2 fp 11

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.

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!

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).

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..

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.

Try a unique index with the leading date, not ID.

Yes, good idea. As you still use V8.2 read this: http://www.ibm.com/developerworks/da...202zuzarte.pdf