The workfile allocations at every site where I’ve ever worked have been zero. You either have enough space for a given large result set or you don’t, and leaving it as an “unallocated” quantity doesn’t seem to me to help.
If the space is unallocated, then the implication is that it might need to be used for something else. If it’s being used for something else (presumably another temporary file), your system’s capacity to handle large queries is curtailed and **you won’t know about it until a query, which used to work, fails for a refused request for more DASD in your workfile**.
If your argument is that you might need it for a surprise huge query, the question is, again, how does this help? You extend a lot, and maybe you have enough space and maybe you don’t… but after the extending, you now have the _new_ “We need this much space” value in your workfiles. Either you give back the secondaries as Gabriel has described (you do monitor your workfiles and regularly drop and recreate them, right? Anyone?), or you leave them out there and you should have been allocating all of this all along!!
What am I missing, here? What’s the benefit of sizing your workfiles as start-small-and-add, instead of start-with-as-much-space-as-you-are-allowed?
Computer Systems Manager
City of New York / FISA-OPA
[login to unmask email]<mailto:[login to unmask email]>
From: Gabriel Pelly <[login to unmask email]>
Sent: Tuesday, June 9, 2020 6:06 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 12 z/OS 2.4 - all my volumes are being ET UP by 32k workfiles!
I started having this type of issue with DB2 V10, where the DB2 workfiles gobbled up all available workfile dasd space and did not release it. It was caused by a few transactions that could not be changed.
My bandaid, was to drop the excessively sized worfile(s) and re-create them. As they are workfiles, they can be dropped without fear of losing data- assuming they are not 'in use' at the time of drop.
I eventually wrote a REXX to perform the process in batch, run weekly. Let me know if you are interested in it.
BTW: At DB2 V10 (I dont believe it has changed), you should have some workfiles defined that have 0 secondary space, and some with secondary space for each of the 4K and 32K workfiles groups.
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**