DB2 for z/OS problem - empty tablespace growing in size

[Michael Raz via DBMonster.com]

We are in SAP R/3 using z/OS DB2 V7.1 as our DB Server.

SAP uses a table called VBDATA for it's internal intermediate needs just
inserting in it's primary transaction a row and then selects and deletes
the row in it's secondary transaction.

The table is basically empty. During our peak time we have just a few tens
of rows in the table.

The problem is that the tablespace is constantly growing - goes into
extents (and it's over allocated and it's a huge chank of just empty pages).

We tryed simple and segmanted tablespaces. We tryed descending and
ascending clustering indexes - nothing helps.

Any ideas?

Thanks,
Michael.

--
Message posted via http://www.dbmonster.com

[Anton Versteeg via DBMonster.com]

Is the data tablespace or the index tablespace growing in size?
I assume it is the data tablespace.
A segmented tablespace is probably best for this type of usage.
You should REORG it on a regular basis.
Or just drop the tablespace and redefine it and its dependents (tables,
indexes etc.). You also might want to increase the primamry and secondary
space allocations a bit to avoid a large number of extents,
if you don't reorg it often.

--
Message posted via http://www.dbmonster.com

[BobTheDatabaseBoy]

Michael Raz via DBMonster.com wrote:

We are in SAP R/3 using z/OS DB2 V7.1 as our DB Server.

SAP uses a table called VBDATA for it's internal intermediate needs just inserting in it's primary transaction a row and then selects and deletes the row in it's secondary transaction.

The table is basically empty. During our peak time we have just a few tens of rows in the table.

The problem is that the tablespace is constantly growing - goes into
extents (and it's over allocated and it's a huge chank of just empty pages).
We tryed simple and segmanted tablespaces. We tryed descending and
ascending clustering indexes - nothing helps.

Any ideas?

Thanks,
Michael.

--
Message posted via http://www.dbmonster.com

couple of things (not necessarily DB2 specific)

- if a transaction is open, database engines don't reuse page slots
since they aren't really yet available. this is what the
situation sounds like.

- the engine has a maximum number of slots per page (255 last time i
looked). if you've a large bufferpool size for the tablespace,
you could be using lots of empty disc real estate. sounds like
the 4k page size (minimum) would help.

btdb