Hi All

I was wondering why and when would someone use the "auto-resize" feature in DB2 UDB (for LUW) TS.

The usage notes on the "auto-resize" feature has lot of implications.
The following one was the most striking implication:
"Because the maximum size limits how DB2 UDB automatically increases a table space, the maximum size also limits how users can increase a table space. In other words, when performing an operation that adds space to a table space, the resulting size must be less than or equal to the maximum size. Space can be added using the ADD, EXTEND, RESIZE, or BEGIN NEW STRIPE SET clauses of the ALTER TABLESPACE statement".

No why would one use a feature that would limit his/her ability to ALTER TABLESPACE RESIZE ...
Any thoughts/experience with the DMS table space "auto-resize" feature ?

Thanks
Anil

The "maximum" size is user defined, so you are only limiting it to the maximum size that you specify. This is important because you may not have enough free space available on a drive, or don't want one tablespace to monopolize all the free space.

Hi Marcus,

automatic resizing is used in the context of DMS tablespaces. In DMS tablespace, you pre-allocate the number of pages to be used by the table space. So the decision to extend the allocation is a decision under DBA control . What's the point in first having it as "automatic", in a way relinquish control and give it to DB2, and then restrict it by maximum size. It sounds like a round about way for something that can be easily accomplised using the DMS table space without the auto-resize feature anyway.

Anil

An application is usually unusable if a tablespace becomes full. For those of us that have 24 x 7 applications, it is preferable that the size be automatically increased in order to provide continuous availability.

True, DBA’s should be monitoring the tablespace size so that they never run out of space, but mistakes do happen. Also, without automatic resizing, DBA's tend over-allocate DMS tablespaces because they know that the application will fail when the tablespace is full.

To be honest, I am flabbergasted that someone is even asking these questions. In any case, you are not required to use automatic resizing if you don’t see the benefit.