470,619 Members | 1,407 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

DB2 v7.1 for Z/OS - Need to know why I should delete and redfine DSNDB07 daily ?

I want to know what the best practices are for maintaining DSNDB07
(work database) on DB2 v7.1 for Z/OS?

A colleague swears that we need to delete and redefine DSNDB07 daily to
avoid maxing out on extents. I can not find any information on best
practices.

My belief is that it doesn't matter if we max out on extents because it
is temp data and once we reach the max, the current sql activity will
fail and rollback (if required). In fact, why do I even need to create
any secondary allocations at all? If I set it to 0, then the dataset
allocations won't grow past the primary...............
I need some sort of information to make an informed decision.

Thanks in advance,
Richard McCutcheon.

Nov 28 '06 #1
2 3152
You should refresh your working knowledge of what DSNDB07 is used for
before deciding that a fixed size with SQL failures is a good choice.

Intermediate work products are stored there when they are larger than
the buffer pool will support. Sorts may also be using space there. Your
DSNDB07 should never 'fill up' and cause SQL failures.

If you don't understand the necessity of primary and secondary
allocations, I'd recommend spending an hour or so with your site's disk
space administrator. You'll gain an appreciation of the costs and
benefits of properly managing disk space.

Managing multiple extents, especially when you have a couple hundred of
them for a single (underlying) file for a database can have significant
costs. Recreating DSNDB07, when necessary, to keep the number of extents
manageable is usually a good thing to do.

Phil Sherman
db*****@gmail.com wrote:
I want to know what the best practices are for maintaining DSNDB07
(work database) on DB2 v7.1 for Z/OS?

A colleague swears that we need to delete and redefine DSNDB07 daily to
avoid maxing out on extents. I can not find any information on best
practices.

My belief is that it doesn't matter if we max out on extents because it
is temp data and once we reach the max, the current sql activity will
fail and rollback (if required). In fact, why do I even need to create
any secondary allocations at all? If I set it to 0, then the dataset
allocations won't grow past the primary...............
I need some sort of information to make an informed decision.

Thanks in advance,
Richard McCutcheon.
Nov 28 '06 #2
Phil,

BEST practices is a relative word and the answer normally is 'IT DEPENDS'
but normally, it is considered GOOD practice to not provide secondary
extents for any of the temporary tables (keep SEC to 0) so that you (or the
DBA) get flagged by rogue dynamic SQL who hogs most of the resource and
shouldn't be allowed to - when an SQL runs out of temporary space, it
normally means it is also flooding the buffer-pools and chewing up lots of
cpu.... in whch case, you can then ask the user what his requirements are
and either bump up the allocations or ask him to eliminate the ORDER BY
clause or even ask him to work off unloaded extract files (and keep him out
of DB2) - but at least you can then make a conscious decision based on the
urgency of the matter. Changing your system resource for the sake of single
SQL is a bit of an overkill, don't you think?

HTH,
Ven

"Phil Sherman" <ps******@ameritech.netwrote in message
news:pA******************@newssvr14.news.prodigy.c om...
You should refresh your working knowledge of what DSNDB07 is used for
before deciding that a fixed size with SQL failures is a good choice.

Intermediate work products are stored there when they are larger than the
buffer pool will support. Sorts may also be using space there. Your
DSNDB07 should never 'fill up' and cause SQL failures.

If you don't understand the necessity of primary and secondary
allocations, I'd recommend spending an hour or so with your site's disk
space administrator. You'll gain an appreciation of the costs and benefits
of properly managing disk space.

Managing multiple extents, especially when you have a couple hundred of
them for a single (underlying) file for a database can have significant
costs. Recreating DSNDB07, when necessary, to keep the number of extents
manageable is usually a good thing to do.

Phil Sherman
db*****@gmail.com wrote:
>I want to know what the best practices are for maintaining DSNDB07
(work database) on DB2 v7.1 for Z/OS?

A colleague swears that we need to delete and redefine DSNDB07 daily to
avoid maxing out on extents. I can not find any information on best
practices.

My belief is that it doesn't matter if we max out on extents because it
is temp data and once we reach the max, the current sql activity will
fail and rollback (if required). In fact, why do I even need to create
any secondary allocations at all? If I set it to 0, then the dataset
allocations won't grow past the primary...............
I need some sort of information to make an informed decision.

Thanks in advance,
Richard McCutcheon.

Dec 2 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Zarrin | last post: by
2 posts views Thread by shapper | last post: by
4 posts views Thread by =?Utf-8?B?Ym9va2VyQG1ndA==?= | last post: by
reply views Thread by Hennie Coertze | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.