DB2 - L

Expand all | Collapse all

DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

  • 1.  DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted Apr 29, 2021 02:07 AM
    Hi DB2 Experts-

    we have classic partitioned table space with 151 parts ,while doing reorg it is getting abended with below message

    273 DSNU3345I 108 02:11:23.81 DSNURPCT - MAXIMUM UTILITY PARALLELISM IS 86 BASED ON NUMBER OF PARTITIONS AND INDEXES
    274 DSNU3340I 108 02:11:23.86 DSNURPCT - UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT DISK SPACE
    275 DSNU3341I 108 02:11:56.69 DSNURPCT - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT
    276 DSNU2904I 108 02:11:56.69 DSNURPCT - DATA RECORDS WILL BE UNLOADED VIA TABLE SPACE SCAN
    277 DSNU012I 108 02:12:04.27 DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8
    RETCODE FROM CALL TO DSNUTILS IS: 4

    as per IBM doc - https://www.ibm.com/docs/en/db2-for-zos/11?topic=messages-dsnu3341i (Provide more disk space for sort work data sets)


    to get more  disk space for sort work data sets what i should  do ? contact storage team?

    below is reorg paraments i am using please let me know do i need to change any thing on below parameters

    REORG TABLESPACE AAAAAAA.BBBBBBB LOG NO NOSYSREC COPYDDN(COPYDSSM)
    SHRLEVEL CHANGE FORCE READERS NOCHECKPEND KEEPDICTIONARY DEADLINE NONE DRAIN_WAIT 60 RETRY 15 RETRY_DELAY 60
    TIMEOUT TERM MAXRO 120 DRAIN ALL LONGLOG TERM DELAY 15 AUX YES UNLDDN WORK3 WORKDDN(WORK1, WORK2) SORTDEVT SYSDA

    ------------------------------
    Sue Lhowekforce
    ------------------------------


  • 2.  RE: DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted Apr 29, 2021 04:33 AM
    Might sound silly but are you REORGing for a reason? If, for example, you are just enabling some PENDING DDL then change the parms to not do any sorting...
    If you are doing the REORG to claim back space - Same
    If you are doing the REORG to fix ordering problems then do ranges of parts PART (1:50) and then PART(51:100) etc. if you are low on sort space.
    In this cased remember to also REORG the NPI(s) afterwards...

    SORTDATA NO - unload through cluster index avoids sort datasets
    RECLUSTER NO - don’t even think about sorting the indexes
    Remove the NOSYSREC as this is not allowed with SORTDATA NO parameter...

    HTH!


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

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




  • 3.  RE: DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted Apr 29, 2021 05:22 AM

    Hello Roy - thanks for checking and reply, we are doing reorg as a part of regular maintenance, as we are doing reorg at database level  common parameters are coded for  all objects in that database.

    as we are doing reorg as a part of regular maintenance, do you want to try changing any reorg paraments ?(like SORTDATA NO etc)



    ------------------------------
    Sue Lhowekforce
    ------------------------------



  • 4.  RE: DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted Apr 29, 2021 06:23 AM
    If you are doing it due to database requirements (leaf dist, unclust ins, append ins etc) then just split into multiple smaller jobs with the PART(1:xx) syntax. Works a treat. But just do not forget to reorg (if required) and NPIs...

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    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: DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted Apr 29, 2021 09:12 AM
    Or look at a Reorg tool that doesn't require sorting and the associated work files

    I'm sure there's someone from BMC on here somewhere..........

    Phil G

    Sent from my iPad





  • 6.  RE: DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted Apr 30, 2021 06:36 AM

    What Phil said.

     

    See your friendly local BMC salesperson. Sort work files? Pfft. Who needs 'em?

     

    Regards,

    Marcus Davage CEng CITP MBCS

    Lead Product Developer

    AMI-DevOps for Db2 – SQL Performance

    Direct

    +44 118 921 8517

     

    Mobile

    +44 7840 023 560

     

    Email

    marcus_davage@bmc.com

     

    E2, Eskdale Road, Winnersh, Berkshire, United Kingdom RG41 5TS

    10431 Morado Circle, Austin, Texas 78759, USA

     

    BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.





  • 7.  RE: DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted May 07, 2021 02:46 AM
    Thank you Roy for checking and reply

    ------------------------------
    Sue Lhowekforce
    ------------------------------



  • 8.  RE: DSNU3341I - CANNOT ALLOCATE SORT DATASETS WITHIN 255 LIMIT

    Posted May 12, 2021 12:27 PM
    Hi Sue

    RTS stats up to date?
    If not execute RUNSTATS SHRLEVEL REFERENCE UPDATE ALL in order to update RTS prior REORG.
    Otherwise you can update RTS table manually.
    (IBM document number: 6221304)

    Regards
    Boris

    ------------------------------
    BorisBangerterZKB
    ------------------------------