I was curious about the impact of autogrow on tablespaces and the disk.

if i were to have 5 table spaces on a single disk i assume they would start out in order on the disk
12345

if for example tablespace 1 needed to grow, on the disk i assume it would end up looking like
123451
where the addition of TS 1 would end up at the next free spot on the disk.
or does the DB push 2345 out and insert the space contiguously?

basically i have an application that has 5 table spaces that will be on one logical disk and i want to know how dangerous it is to let these autogrow vs making an attempt to estimate optimal size and fully grow out the TS and fill the disk. this is for a DR system and i personally feel that they have cut too many corners with the hardware and i want to make sure it can run as best as it possibly can.

this is for DB2 9 FP 7

Thanks
Justin

It is not going to move any existing rows. So you end up with a fragmented table, which is usually only a factor when you have a data warehouse with a lot of table scans of the entire table, and even then I doubt it is a big deal.

well the DB is roughly 800GB, data is flowing in non stop and there is about 100 hits a minute to query for data. 90% of the queries are for data in the last 6 months and even more so its for data in the last month. its used for studies and we do about 2000 studies a day and each study has about 600 related entries.

the server we tossed at it is pretty decent cpu/memory wise but they plan on putting the DB and logs on a raid 10 of 4 SAS 500GB 15k drives.

i figured it would be best to do every little thing i can to make this run as smoothly as possible.

What I mean by "table scans" are queries that access the data without indexes and read the entire table (or if partitioned, the entire partition) and therefor use prefetch (DB2 is asking for pages be added to the buffer pool in advance of needed them, in anticipation of needed them very soon). In those cases it would be best to have as much contiguous space defined for a table, but on the other hand any differences may not be noticeable in terms of performance, and often times administrative ease and disk space management are more important than very minor differences in performance.

If you have a maintenance window (or can do it online/inplace) you might want to reorg your indexes.

Also, make sure you tables have no overflow rows (typically caused by updating a VARCHAR and increasing the size of the data, and the row will not fit back in the same place). Better yet, change the application logic to prohibit that if it is happening.

Thanks for the feedback, it was very helpful.