469,644 Members | 2,008 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,644 developers. It's quick & easy.

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

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
Nov 12 '05 #1
2 2267
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
Nov 12 '05 #2

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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jens H. Hamann | last post: by
2 posts views Thread by Stanley Sinclair | last post: by
4 posts views Thread by mairhtin o'feannag | last post: by
4 posts views Thread by Alvin SIU | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.