469,647 Members | 1,730 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

the database with many inserts vs less reads

Hello,

I need to implement a solution where db2 Workgroup v. 8.1 is used for
storing quite huge amount of data. The data will be rarely read and searched
and those operations do not need to be fast. In opposite many writes
(inserts , not updates) should work fast.

I am open for any suggestions on techniques and database configurations that
will be good for such a purpose. Any opinions are welcome !

sincerely Olek

Nov 12 '05 #1
3 1267
Hello Olek,

There are various non-database specific techniques like using less frequent
commits and large log buffer. One DB2 specific thing also helped us is
APPEND ON option in ALTER TABLE. DB2 will spend less resources looking for
space to insert rows.

--
Best regards, Dmitry Tolpeko
SQLWays - Data, Schema transfer for DB2, Oracle, SQL Server, Sybase,
Informix and MySQL
www.ispirer.com

"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:cr**********@news.onet.pl...
Hello,

I need to implement a solution where db2 Workgroup v. 8.1 is used for
storing quite huge amount of data. The data will be rarely read and searched and those operations do not need to be fast. In opposite many writes
(inserts , not updates) should work fast.

I am open for any suggestions on techniques and database configurations that will be good for such a purpose. Any opinions are welcome !

sincerely Olek

Nov 12 '05 #2
Olek,

A little more info about your problem would be helpful.

For example, if the data doesn't need to be inserted real-time then the
solution may be pretty easy. Assuming hourly insertions:
- use loads to get 50,000 + events/second
- util-heap-sz large enough to allow load parallelism
- backup strategy could rely on zipped load files

If it has to be real-time, then you'll want:
- beefed up log buffers
- append on
- infrequent commits
- parameter markers
- and you may need a backup strategy - which could pose significant
performance impacts.
- may need to be careful with locking
- many more details could be found in a handy db2 doc I read this year.
But the db2 website has been continually improved to the point where I
can't find anything useful on it anymore.

In any event,
- pay attention to tablespace layout & pagesize: a 32k pagesize gives a
max tablespace of 512 GB. So, you may need to be careful about
multiple tables/ tablespace.
- indexes slow down insert performance
- MDC slows down insert performance
- union-all views slow down insert performance
- storage subsystem can certainly have a huge impact - raid5 would be a
bad choice here.

buck

Nov 12 '05 #3
Smutny30 wrote:
Hello,

I need to implement a solution where db2 Workgroup v. 8.1 is used for
storing quite huge amount of data. The data will be rarely read and searched
and those operations do not need to be fast. In opposite many writes
(inserts , not updates) should work fast.

I am open for any suggestions on techniques and database configurations that
will be good for such a purpose. Any opinions are welcome !

sincerely Olek

Olek,

this paper might help
http://www-106.ibm.com/developerwork...tml?ca=dnp-311

cheers
Florian
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by democratix | last post: by
4 posts views Thread by Christopher Brandsdal | last post: by
6 posts views Thread by Michael C | last post: by
1 post views Thread by Brian Maguire | last post: by
3 posts views Thread by josh.kuo | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.