the database with many inserts vs less reads

[Smutny30]

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

[Dmitry Tolpeko]

Re: the database with many inserts vs less reads

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

https://www.ispirer.com

"Smutny30" <smutny30@poczt a.onet.pl> wrote in message
news:crh9v9$asg $1@news.onet.pl ...[color=blue]
> 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[/color]
searched[color=blue]
> 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[/color]
that[color=blue]
> will be good for such a purpose. Any opinions are welcome !
>
> sincerely Olek
>[/color]

[Buck Nuggets]

Re: the database with many inserts vs less reads

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

[florian boldt]

Re: the database with many inserts vs less reads

Smutny30 wrote:[color=blue]
> 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
>[/color]
Olek,

this paper might help

http://www-106.ibm.com/developerworks/db2/library/tips/dm-0403wilkins/index.html?ca=dnp-311

cheers
Florian