THat's all? :)
60x 32K Segmented
90x 4K segmented
4x 32K PBG, maxparts 4
2x 4K PBG, maxparts 4
All partitions about 44000 tracks... what's that about 2.5G apiece? IFCID 0002 is telling me 300GB sort-work and 48GB DGTT/SC space per subsystem.
Manual still states:
When your application needs to sort data,
Db2 tries to allocate each sort work file on a table space that has the following attributes:
- Is a segmented (non-universal) table space.
- Has no secondary allocation (SECQTY = 0), or is a user-managed table space, regardless of secondary space allocation.
When table spaces that have the preferred attributes are not available, the action taken depends on the value of the WFDBSEP subsystem parameter. If the value is 'YES', the sort operation fails. If the value is 'NO', another available table space is selected.
Seems incongruent for support to be telling you to go all-PBG when the manual for 13 still speaks of zero-secondaries and User-managed and WFDBSEP. We set WFDBSEP to "NO" but still keep both types of workfile to try to keep these goofballs who want to create DGTTs with millions of rows from clobbering the sort space. I asked IBM about this, and don't know if I ever received a clear reply what the plan was for WFDBSEP. I do know that, even now, the DSNTWFG REXX is circumventing the UTS-only restriction of M504 by setting APPLCOMPAT to V11R1 to get segmented tablespaces. At least in 12...not sure on 13 yet.
I would honestly love to go entirely to PBG and be done with it. I think we're at LEAST all Db2-managed now...but we still do manually allocate segmented with SECQTY 0 for sort.work.
In fact, the smarter move would be to just give Db2 a sort-work pool of DASD and have the subsystem dynamically allocate space as-needed so we don't have to pre-configure sort-works at ALL. But that would require significant changes to the code...so chance of that is near-zero...maybe they'll write a microservice for it. :/
For the original question...if your sorts aren't failing, you can ignore the EXTEND FAILED messages for sort-works. Those alerts are meant for actual data as DBA advisories/alerts...they don't really matter to work-files. Db2 can figure it out. However, if you're running out of sort space, that's another story.
DSNDQIST in IFCID0002 is very useful in tracking sort-work usage. Look into it if you get a chance.
------------------------------
Mark Wieczorkowski
------------------
Db2 Systems Programmer, SSA/DCS
Principal - Solipsistic, LLC
------------------------------
Original Message:
Sent: Jun 30, 2022 09:36 AM
From: Russell Peters
Subject: z/OS v12 DSNDB07 extend failures
I had this same exact issue in our shop. The IBM support call advised to alter all sort tablespaces to UTS PBG with a non-zero secondary extent and make them db2 controlled. There no longer is a need for the zero secondary tablespaces. Previously all of our sort tablespaces had datasets that were manually defined as this used to be a requirement, and some were defined with zero secondary. The recommendation now (v12) is to recreate the sort tablespaces as UTS PBG with just a normal create tablespace command. I set ours to MAXPARTITION 5. We haven't had any issues since, and the change was unnoticed.
The process I used was to stop the tablespace, drop and recreate, then start it. I did this one at a time with no problems.
I'm surprised you only have 10 sort tablespaces defined. I have 15 (4k) and 29 (32k) sort tablespaces in prod.
------------------------------
RussellPetersCentral Technology Services
Original Message:
Sent: Jun 29, 2022 10:57 AM
From: Mark Vickers
Subject: z/OS v12 DSNDB07 extend failures
We have one Db2 client (cloud services) who has been getting
DSNP007I -#### DSNPXTN0 - EXTEND FAILED FOR
Not too often and no application failures, but I would still like to resolve this.
Hoping for a band aid, I threw some space and a couple more DSNDB07 tablespaces at this and that did not resolve the issue.
After a few weeks of monitoring I noticed it was happening on 5 out of the original 10 sortwork tablespaces.
Digging further, these 5 were created as segmented and the other 5 are PBG.
The workload at time of failures are mainly thousands of distributed SQL threads, no long running queries and I do not see use of temporary objects like declared temp tables, merges etc., seems to be vanilla SQL.
I have not been able to find a culprit nor am I certain that changing these 5 tablespaces to PBG is the answer.
Anyone encounter this or have an explanation to guide me here ?
Thanks,
Mark V.