473,554 Members | 2,946 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1378
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******@poczt a.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2818
by: democratix | last post by:
Hi, I've only got a couple years experience developing for Access but have recently been experimenting with HTML/javascript for gui and client-side scripting, mysql for database and php for server-side scripting. I've been running it all on the development machine until the application I'm building is advanced enough to start...
4
2661
by: Christopher Brandsdal | last post by:
Hi! I have a delicatg problem.... I have made a registration form for adding my friends information in a database. The problem is that I want to connect the persons with companies in the same database. I wonder how i could connect a person to several other id's in a table. I wonder if I can make a string in the database that holds all...
3
3033
by: Viswanatha Thalakola | last post by:
Hello, Can someone point me to getting the total number of inserts and updates on a table over a period of time? I just want to measure the insert and update activity on the tables. Thanks. - Vish
6
2310
by: teedilo | last post by:
We have an application with a SQL Server 2000 back end that is fairly database intensive -- lots of fairly frequent queries, inserts, updates -- the gamut. The application does not make use of performance hogs like cursors, but I know there are lots of ways the application could be made more efficient database-wise. The server code is...
6
2450
by: Michael C | last post by:
I'm writing a program that, among other things, has to create and populate 7 tables on SQL Server. It reads in TAB delimited files, generates INSERT statements and executes them. So far I've been able to optimize the program by using the built-in streamreader and string functions (Split, Replace, etc.) in C#, SqlConnection and SqlCommand for...
1
3124
by: Brian Maguire | last post by:
Can too many btree indexes cause page level locking? I read this... http://www.postgresql.org/docs/7.4/static/locking-indexes.html
3
2926
by: josh.kuo | last post by:
Sorry about the subject, I can't think of a better one. I recently wrote some PHP classes that I think might be of interest to this group. Since I have been reaping the benefits of reading news groups for years, I figure it's time for me to contribute a little bit back, maybe some people out there will find this useful. * Introduction ...
5
2708
by: Marijn | last post by:
Hello everybody, I am new to PHP and working on extending my knowledge of OOP. The posts in this group concerned with whether or not to use an OO approach when programming in PHP is not what I want to discuss in this post. Rather I would like to discuss the best way to program the following problem: I have a MySQL Database which for...
4
3079
by: Tommy Hayes | last post by:
Hello all, I want to use the SQL Server 2005 Tuning Advisor on our database, and I'm hoping someone here can just confirm the steps for me. We have a 10GB database that has a number of applications hitting it constantly, all performing many SELECTs, INSERTs and UPDATEs. Are the following the steps I need to take? 1. Stop all the...
0
7497
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7780
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8010
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7862
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5414
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3539
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1111
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
812
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.