DB2 - L

 View Only
  • 1.  z/OS v12 DSNDB07 extend failures

    Posted Jun 29, 2022 10:58 AM

    We have one Db2 client (cloud services) who has been getting



    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 ?



    Mark V.

  • 2.  RE: z/OS v12 DSNDB07 extend failures

    Posted Jun 29, 2022 11:35 AM

    If this isn't causing application failures then I suspect the messages are coming from DBM1 not the MSTR address space.

    If so, then I believe (from memory) that this is relatively normal if you have work file tablespaces with zero secondary space as recommended. You shouldn't need to worry if applications aren't failing.



  • 3.  RE: z/OS v12 DSNDB07 extend failures

    Posted Jun 29, 2022 11:40 AM


    It is in MSTR.


    Spit and polish is what I learnt in the service, so I want my clients Db2 all shiny and clean ��


    I would still like to understand why only the segmented tablespaces fail.





  • 4.  RE: z/OS v12 DSNDB07 extend failures

    Posted Jun 29, 2022 04:58 PM
    Check out my Limits IDUG presentation. There is a graphic in there from Peter Hartmann that explains exactly what you are seeing/getting. It is all about PBG and a ZPARM or two...

    Roy Boxwell 

    -Product Development-

    IBM Champion 2022

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Web  http://www.seg.de

    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

  • 5.  RE: z/OS v12 DSNDB07 extend failures

    Posted Jun 30, 2022 09:37 AM
    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

  • 6.  RE: z/OS v12 DSNDB07 extend failures

    Posted Jul 01, 2022 07:31 AM
    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