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.
