Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Mar 27
    2013

    DB2/zOS V9 Shrlevel Change Reorg questions

    John alexander
    [Woolworths Ltd]

    Hi All,

    I have been trying unsuccessfully to get a tablespace Reorg with SHRLEVEL CHANGE to do the following;

    1 Use sort work dataset DAxxWKyy, SWxxWKyy, SORTWKxx, DATAWKxx, ST01WKxx

    2 Save the unloaded data in a dataset


    The reason for this is that we have large table & when they run they exhaust all real memory.  It seems the utility insists on using memory sorts, and unloading the data into temp system datasets, regardless how I code the parameters . 

    Our standard online Reorgs run with below SYSIN parms: 

    REORG TABLESPACE  DBXXX.TSYYY  COPYDDN(COPYA1)  KEEPDICTIONARY  SORTDATA   NOSYSREC   SORTKEYS SORTNUM 12  SORTDEVT SYSDA  SHRLEVEL   CHANGE     LOG     NO  MAXRO 100  DRAIN_WAIT 30  LONGLOG TERM  DELAY 90  TIMEOUT TERM  MAPPINGTABLE  DBA.TMAPTBL_HDBDPON2  STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 10 INDEX(ALL)  KEYCARD FREQVAL NUMCOLS 1  COUNT 10                 FREQVAL NUMCOLS 2  COUNT 10                  FREQVAL NUMCOLS 3  COUNT 10  

    I would greatly appreciate any feedback that would explain this mystery . 

    Thanking you all in advance 

    John 

    [login to unmask email] 

    Jorge Martelanz
    [Saudi Business Machines (SBM)]

    John

    1 What is the value of your UTSORTAL and IGNSORTN ZPARMs? Did you include DD statements in the job or DB2 are allocating them dynamically?

    2 Your utility parameters include NOSYSREC, that means no unload dataset is generated. Additionally, the Utilities manual says under "Omitting the output data set" when describing the REORG utility:

    "For REORG TABLESPACE SHRLEVEL CHANGE, REORG omits the unload data set, even if you do not specify NOSYSREC."

    Take care

    Jorge


    In Reply to John alexander:

    Hi All,

    I have been trying unsuccessfully to get a tablespace Reorg with SHRLEVEL CHANGE to do the following;

    1 Use sort work dataset DAxxWKyy, SWxxWKyy, SORTWKxx, DATAWKxx, ST01WKxx

    2 Save the unloaded data in a dataset


    The reason for this is that we have large table & when they run they exhaust all real memory.  It seems the utility insists on using memory sorts, and unloading the data into temp system datasets, regardless how I code the parameters . 

    Our standard online Reorgs run with below SYSIN parms: 

    REORG TABLESPACE  DBXXX.TSYYY  COPYDDN(COPYA1)  KEEPDICTIONARY  SORTDATA   NOSYSREC   SORTKEYS SORTNUM 12  SORTDEVT SYSDA  SHRLEVEL   CHANGE     LOG     NO  MAXRO 100  DRAIN_WAIT 30  LONGLOG TERM  DELAY 90  TIMEOUT TERM  MAPPINGTABLE  DBA.TMAPTBL_HDBDPON2  STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 10 INDEX(ALL)  KEYCARD FREQVAL NUMCOLS 1  COUNT 10                 FREQVAL NUMCOLS 2  COUNT 10                  FREQVAL NUMCOLS 3  COUNT 10  

    I would greatly appreciate any feedback that would explain this mystery . 

    Thanking you all in advance 

    John 

    [login to unmask email] 




    Jorge Martelanz

    John alexander
    [Woolworths Ltd]

    Hi Jorge,

    many thanks for the reply.

    The Zparm has the following values, and yes I did have DDs in the JCL for all the SORT work DDs mentioned.

    DB2SORT=DISABLE

    IGNSORTN=NO

    UTSORTAL=NO

    Please note that the SYSIN parm quoted was just a sample we use for normal SHRLEVEL CHANGE, but for my testing i tried SYSREC

    which did not work, basically I tried every allowable combination of the relevant parms (ie. for SORTKEYS, SORTNUM, SYSREC etc.) but it kept doing the unload to system temp datasets, and the sort was using real memory .

     

    Regards

    John

    Adam Baldwin
    [DB2 Consultant]

    John, a couple of questions. How many indexes are there? When you say that you've tried all combinations of parms, have you run without SORTKEYS?

    Also, how many work files are actually being allocated in the job?

    Cheers, Adam

    John alexander
    [Woolworths Ltd]

    Hi Adam,

    Sorry for the late reply, i'v been running a few tests & found that;

    Test-1 With both SORTKEYS and UNLDDN UNLD specified,

    and with  10 DDs for each DA01WKxx, DA02WKxx, SW01WKxx, SW02WKxx,  DATAWKxx, SORTWKxx (size 50,50 cyls)

    Result: The Reorg used DATAWKxx, SW01WKxx, & SW02WKxx, and the unload dataset specifed by //UNLD DD.

    Test-1 SYSIN used:

    REORG TABLESPACE DBxx.TSxx  COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN   UNLD SORTDATA SORTKEYS                   SORTNUM 10  SORTDEVT SYSDA SHRLEVEL   CHANGE     LOG     NO    MAPPINGTABLE  DBA.TMAPTBL1 MAXRO 100 DRAIN_WAIT 30 LONGLOG TERM  DELAY 90  TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1  COUNT 10         FREQVAL NUMCOLS 2  COUNT 10         FREQVAL NUMCOLS 3 COUNT 10

    Test-2 With no SORTKEYS , and with UNLDDN UNLD specified,

    and with  10 DDs for each DA01WKxx, DA02WKxx, SW01WKxx, SW02WKxx,  DATAWKxx, SORTWKxx (size 150,150 cyls)

    Result: The Reorg used only  SW01WKxx, but & SW02WKxx and DATAWKxx just got allocated but were not used,

    also the unload dataset specifed by //UNLD DD also did not get used. 

    Test-2 SYSIN used:

    REORG TABLESPACE DBxx.TSxx  COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN   UNLD SORTDATA

    SORTNUM 10  SORTDEVT SYSDA SHRLEVEL   CHANGE     LOG     NO    MAPPINGTABLE  DBA.TMAPTBL1 MAXRO 100 DRAIN_WAIT 30 LONGLOG TERM  DELAY 90  TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1  COUNT 10         FREQVAL NUMCOLS 2  COUNT 10         FREQVAL NUMCOLS 3 COUNT 10

    In both tests real memory was heavily used, and for unloads temp system datasets were used.

    The table in this test was partitioned & had 3 NPIs. But I'v noticed the same behaviour regardless.

     

    ps. had a few DFSORT abends along the way so I kept increasing the work dataset sizes & numbers, as well as emptying 1/2 the parts just to get the test through.

     

    Regards

    John

     

     

     

    Adam Baldwin
    [DB2 Consultant]

    John, my understanding of DFSORT (and this really needs an MVS Sysprog's knowledge) is that it will use available real memory when it can. I think that this can be controlled to a certain extent my the parameters that control DFSORT. What abends was DFSORT giving you? Were they out of space abends on the work datasets that you defined? One thing to be aware of is trying to over allocate for DFSORT. With a high value for sortnum - and allowing for the total number of allocations for the various indexes / tasks - you can end up with DFSORT reverting to it's default because it can't find enough candidate volumes.

    Maybe if you could post some of the reorg job output showing what got allocated, what got used in the sort(s) and any abend messages, somebody will be able to explain what exactly is going on.

    Adam

    Adam Baldwin
    [DB2 Consultant]

    John, a bit more info re DFSORT's memory usage:

    Data size  Estimated main memory use per DFSORT task
        1 GB     10 MB
      10 GB     30 MB
    100 GB     70 MB

    This gives you a very rough idea of how much memory DFSORT may use. You've got record length * record count * sort tasks.

    Check that you have PK41899 applied for V9 - it's old so you probably have. This apar provides for better estimating by dfsort and removes the necessity for Sortnum.

    Adam

    John alexander
    [Woolworths Ltd]

    Hi Adam,

    I checked the APAR, and it has been superceeded by the FMID HDB9910, and the SORT  abends i was getting during the test was 

    ICE046A 0 SORT CAPACITY EXCEEDED - RECORD COUNT 65912717

    After that I loaded null in parts 33-64, to reduce the size of the table and increased the all the sort work DDs from 50,50 cyls to 150,150, as well as adding another 5 sort DDs, ie. went from 5 to 10 DDs for all the sort works.

    ps. i'v attached the 2 job outputs.

     

    Regards

    John

    Attachments
    • reorgtest.txt (76.5k)
    • reorgtest2.txt (56.9k)
    Christian Michel
    [IBM Deutschland Research & Development GmbH]
    John,

    you really should consider removing hard coded sort work data sets from
    your JCL, If you want to use that in your everyday production, it will
    mean a lot of headaches to come up with the right allocation sizes, as
    you're currently figuring out. Also you currently have allocated data sets
    that will not be used or may be inhibiting parallelism. Since you have
    already specified SORTNUM 10 and SORTDEVT on your REORG, why don't you try
    to remove ALL sort work DD cards and rerun and have DFSORT allocate the
    necessary sort work data sets based on the estimates that are passed (and
    look reasonable based on the last output)?

    Also as others have stated before in this forum about the amount of memory
    needed by DFSORT, you may soon run out of memory in your region as soon as
    you attempt to sort some larger table spaces which also try to run
    multiple sorts in parallel, as those 100 MB have to be divided by the
    number of sort tasks and then it soon gets very small. Of course that all
    depends on the maximum size of your data.

    Mit freundlichen Grüßen / Kind regards
    Christian Michel

    Christian Michel
    E-mail: [login to unmask email]
    Phone (49)-7031-16-4293
    DB2 for z/OS Utilities Development
    Information Management Development Boeblingen, Germany



    IBM Deutschland Research & Development GmbH
    Vorsitzende des Aufsichtsrats: Martina Koederitz
    Geschäftsführung: Dirk Wittkopp
    Sitz der Gesellschaft: Böblingen / Registergericht: Amtsgericht Stuttgart,
    HRB 243294
    John alexander
    [Woolworths Ltd]

    Hi Christian

    Many thanks for the feedback.

    The puzzle that I am still trying to understand is how can I code a Shrlevel Change Reorg so that it does use real storage for its sorts. The second issue is capturing the unload in a dataset, which again I have not been able to do, but thats not an important issue, I just wanted to see if it can be done.

    After all the testing, I am beginning to think that you can not get a Shrlevel Change Reorg, not to use real storage for its sorts, thats just how it works. The sort DDs being in the JCL is just for test purposes, to prove its actually using them, regardless how I code the various SYSIN parms, the utility is always using both the sort work DDs, as well as real storage. 

     

    Regards

    John

    Nick CIANCI
    [IBM]
    John,
    forgive me if I'm missing something but assuming that the ReOrg is
    in a batch job, the REGION= JCL parms on the job and step, should enable
    you to throttle the amount of memory DFSORT can use, at least that's the
    way I've always operated.

    Having said that I've always embraced the philosophy that memory is good,
    and that so long as you SysProg are happy for you to do (ie paging isn't
    an issue) then use it (especially if you are ReOrging at "off-peak" times

    In our shop we run REGION=256M for small objects and REGION=512M for large
    objects so with 3x || ReOrgs can chew up about 1.5Gb, in the wee hours of
    the morning.

    As far as capturing the Unload, I think that only works with SHRLEVEL
    NONE, but could be wrong there. there used to be an UNLOAD PAUSE option,
    but again I think that is only with SHR-NONE.


    Regards,

    Nick CIANCI
    Senior Database Administrator (DB2 for z/OS)
    Federal Data Management DBA team
    SO Delivery

    Phone : +61 2) 6212-1214
    Mobile: 0408-64 06 01
    E-mail: [login to unmask email]


    8 Brisbane Avenue
    Barton, ACT 2600
    Australia




    From: John alexander <[login to unmask email]>
    To: [login to unmask email],
    Date: 08-04-2013 13:53
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions



    Hi Christian
    Many thanks for the feedback.
    The puzzle that I am still trying to understand is how can I code a
    Shrlevel Change Reorg so that it does use real storage for its sorts. The
    second issue is capturing the unload in a dataset, which again I have not
    been able to do, but thats not an important issue, I just wanted to see if
    it can be done.
    After all the testing, I am beginning to think that you can not get a
    Shrlevel Change Reorg, not to use real storage for its sorts, thats just
    how it works. The sort DDs being in the JCL is just for test purposes, to
    prove its actually using them, regardless how I code the various SYSIN
    parms, the utility is always using both the sort work DDs, as well as real
    storage.

    Regards
    John


    Site Links: View post online View mailing list online Send new post
    via email Unsubscribe from this mailing list Manage your subscription
    ** ** ** Attend the 2013 IDUG DB2 Tech Conference EMEA ** ** **
    ---> Princesa Sofia Gran Hotel, Barcelona, Spain, 13 - 18 October, 2013
    <---
    http://www.idug.org/p/cm/ld/fid=273

    Use of this email content is governed by the terms of service at:
    http://www.idug.org/p/cm/ld/fid=2

    Attachments
    • blankfilename.txt (1.8k)
    John alexander
    [Woolworths Ltd]

    Correction! I meant to say .. The puzzle that I am still trying to understand is how can I code a Shrlevel Change Reorg so that it does NOT use real storage for its sorts.

    Adam Baldwin
    [DB2 Consultant]

    John, for DFSORT the use of memory is controlled by various parameters and run-time calculations. The parms include: EXPMAX, EXPOLD, HIPRMAX, MOSIZE, DSPSIZE and DSA (Dynamic Storage Adjustment). You need to speak to your MVS sysprogs to see which of these can be over ridden at run time and which are installation level. The DFSORT Tuning Guide has a lot of inforamtion re this. Controlling DB2 sort is another matter. In both cases, I think that it will be impossible to avoid all memory usage.

    Adam.

    Christian Michel
    [IBM Deutschland Research & Development GmbH]
    Adam is right, that those parameters can be used to throttle the memory
    use of DFSORT. HIPRMAX and MOSIZE will be the most promising options and
    you should be able to override them with a DFSPARM DD card in your test
    job. As I mentioned before the REGION should not get too small because
    that kind of memory is used to keep intermediate information on where all
    the sort work stuff is stored and if that amount of memory is too small,
    then performance can be severly degraded.

    Mit freundlichen Grüßen / Kind regards
    Christian Michel

    Christian Michel
    E-mail: [login to unmask email]
    Phone (49)-7031-16-4293
    DB2 for z/OS Utilities Development
    Information Management Development Boeblingen, Germany



    IBM Deutschland Research & Development GmbH
    Vorsitzende des Aufsichtsrats: Martina Koederitz
    Geschäftsführung: Dirk Wittkopp
    Sitz der Gesellschaft: Böblingen / Registergericht: Amtsgericht Stuttgart,
    HRB 243294
    Christian Michel
    [IBM Deutschland Research & Development GmbH]
    Hi John, REORG SHRLEVEL CHANGE always executes with NOSYSREC, so we're not
    writing anything out to the UNLOAD data set. What would you like to
    achieve with this unloaded data set?

    Mit freundlichen Grüßen / Kind regards
    Christian Michel

    Christian Michel
    E-mail: [login to unmask email]
    Phone (49)-7031-16-4293
    DB2 for z/OS Utilities Development
    Information Management Development Boeblingen, Germany



    IBM Deutschland Research & Development GmbH
    Vorsitzende des Aufsichtsrats: Martina Koederitz
    Geschäftsführung: Dirk Wittkopp
    Sitz der Gesellschaft: Böblingen / Registergericht: Amtsgericht Stuttgart,
    HRB 243294
    John alexander
    [Woolworths Ltd]

    Hi All,
    fyi. I ran a BMC Shrlevel Reference for the same table, without any sort work DDs coded, & the max central frames it used was 8k. 

    I reran another IBM Shrlevel Change Reorg, & commented out all the sort work DDs, and it used 142k central frames.

    It seems that either IBM Reorg is very memory hungry regardless, or its required sort work memory calculation is incorrect (the stats 

    are current for the table being tested).

    BMC Reorg Sysin

    REORG       TABLESPACE SRPDB01.SINSTRP SHRLEVEL    REFERENCE ORDER       YES UNLDDN      SYSRE1 LOG         NO DELETEFILES NO COPY        YES REGISTER NONE  COPYDDN(BMCCPY) ANALYZE     NO  UNLOAD CONTINUE BMCSTATS    YES UPDATEDB2STATS YES  REDEFINE YES SORTNUM     15  MAXSORTS 4 WORKDDN     (SYSUT1)

    IBM Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP  COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN   UNLD SORTDATA                        SORTNUM 10  SORTDEVT SYSDA SHRLEVEL   CHANGE     LOG     NO    MAPPINGTABLE  DBA.TMAPTBL1 MAXRO 100  DRAIN_WAIT 30 LONGLOG TERM  DELAY 90  TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1  COUNT 10         FREQVAL NUMCOLS 2  COUNT 10         FREQVAL NUMCOLS 3  COUNT 10

    BMC Reorg O/Mon storage stats

                  Central                                 Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 188,589 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5TST IN NSW 8776 3473 0 0 0 0 0
    IBM Reorg O/Mon storage stats
    
                   Central                                 Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 19,782 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5ORT IN NSW 142K 2041 0 0 283977 0 0

    Cheers
    
    john
    
    Charles Brown
    [BMI]
    John,



    Your test scripts are not identical - that makes it difficult to comment on
    or give an account for the high frame usage when using the IBM supplied.

    There are several contributing factors and lack of several documentation -
    here's a list

    - Why are you still coding SORTWK DD card on your JCL. I noticed
    you're z/os 1.9 or above. You no longer need SORTWRK ddcard on your JCL.

    - What is your Zparm setting for UTSORTAL and IGNSORTN

    - Provide us you SORT output - ICE???? For both REORGs

    - Which version of db2 are you running

    - Which SORT utility is installed in your shop

    It's pretty hard to comment on your HI-frame usage when using IBM supplied.
    Therefore I'd encourage you to run an identical test - if you can, so we can
    compare orange to orange.



    Thx!!

    Charles/b





    From: John alexander [mailto:[login to unmask email]
    Sent: Tuesday, April 09, 2013 7:32 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions



    Hi All,
    fyi. I ran a BMC Shrlevel Reference for the same table, without any sort
    work DDs coded, & the max central frames it used was 8k.

    I reran another IBM Shrlevel Change Reorg, & commented out all the sort work
    DDs, and it used 142k central frames.

    It seems that either IBM Reorg is very memory hungry regardless, or its
    required sort work memory calculation is incorrect (the stats

    are current for the table being tested).

    BMC Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP SHRLEVEL REFERENCE ORDER YES
    UNLDDN SYSRE1 LOG NO DELETEFILES NO COPY YES REGISTER
    NONE COPYDDN(BMCCPY) ANALYZE NO UNLOAD CONTINUE BMCSTATS YES
    UPDATEDB2STATS YES REDEFINE YES SORTNUM 15 MAXSORTS 4 WORKDDN
    (SYSUT1)

    IBM Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN
    UNLD SORTDATA SORTNUM 10 SORTDEVT SYSDA SHRLEVEL
    CHANGE LOG NO MAPPINGTABLE DBA.TMAPTBL1 MAXRO 100 DRAIN_WAIT 30
    LONGLOG TERM DELAY 90 TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL)
    SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL
    NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10

    BMC Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0
    Offline frames. . . : 0 Offline frames . . . : 0
    Total frames. . . . : 2,621,440 Total frames . . . . : 0
    Unref interval count: 65,535 Migration age. . . . : n/a
    Available frames. . : 188,589 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC
    Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5TST IN NSW 8776 3473 0 0 0 0 0
    IBM Reorg O/Mon storage stats
    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0
    Offline frames. . . : 0 Offline frames . . . : 0
    Total frames. . . . : 2,621,440 Total frames . . . . : 0
    Unref interval count: 65,535 Migration age. . . . : n/a
    Available frames. . : 19,782 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC
    Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5ORT IN NSW 142K 2041 0 0 283977 0 0

    Cheers
    john



    -----End Original Message-----

    Steen Rasmussen
    [CA technologies]
    This is an INTERESTING TOPIC - BENCHMARKING !!

    Every vendor - being BMC, IBM, CA, CDB etc. are all using different techniques and all are using different techniques to get the job done. You will have to look at what your goals are and what you want to accomplish. Based on these terms you can decide which solution works the best.

    CDB, BMC and CA works VERY differently than IBM Utilities - so make sure (please) you measure what your goals are and whet you want to accomplish. From my part (being a little CA'ish) - I know what our strengths are but every vendor has their own strengths.

    Please contact me off list if you need specifics ;-) HAPPY SUNDAY

    Steen Rasmussen
    CA Technologies
    Sr Engineering Services Architect
    IBM Certified Database Associate - DB2 9 Fundamentals
    IBM Certified Database Administrator - DB2 9 DBA for z/OS
    IBM Certified Database Administrator - DB2 10 DBA for z/OS

    Tel: +1-630-505-6673
    Mobile: +1-708-382-1363
    [login to unmask email]
    <mailto:[login to unmask email]>[Description: C:\Program Files\CA\GIS\CASig\CA_email.gif]http://www.ca.com

    From: charles brown [mailto:[login to unmask email]
    Sent: Sunday, April 14, 2013 6:38 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    John,

    Your test scripts are not identical - that makes it difficult to comment on or give an account for the high frame usage when using the IBM supplied.
    There are several contributing factors and lack of several documentation - here's a list

    - Why are you still coding SORTWK DD card on your JCL. I noticed you're z/os 1.9 or above. You no longer need SORTWRK ddcard on your JCL.

    - What is your Zparm setting for UTSORTAL and IGNSORTN

    - Provide us you SORT output - ICE???? For both REORGs

    - Which version of db2 are you running

    - Which SORT utility is installed in your shop
    It's pretty hard to comment on your HI-frame usage when using IBM supplied. Therefore I'd encourage you to run an identical test - if you can, so we can compare orange to orange.

    Thx!!
    Charles/b


    From: John alexander [mailto:[login to unmask email]
    Sent: Tuesday, April 09, 2013 7:32 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions


    Hi All,
    fyi. I ran a BMC Shrlevel Reference for the same table, without any sort work DDs coded, & the max central frames it used was 8k.

    I reran another IBM Shrlevel Change Reorg, & commented out all the sort work DDs, and it used 142k central frames.

    It seems that either IBM Reorg is very memory hungry regardless, or its required sort work memory calculation is incorrect (the stats

    are current for the table being tested).

    BMC Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP SHRLEVEL REFERENCE ORDER YES UNLDDN SYSRE1 LOG NO DELETEFILES NO COPY YES REGISTER NONE COPYDDN(BMCCPY) ANALYZE NO UNLOAD CONTINUE BMCSTATS YES UPDATEDB2STATS YES REDEFINE YES SORTNUM 15 MAXSORTS 4 WORKDDN (SYSUT1)

    IBM Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN UNLD SORTDATA SORTNUM 10 SORTDEVT SYSDA SHRLEVEL CHANGE LOG NO MAPPINGTABLE DBA.TMAPTBL1 MAXRO 100 DRAIN_WAIT 30 LONGLOG TERM DELAY 90 TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10

    BMC Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 188,589 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5TST IN NSW 8776 3473 0 0 0 0 0

    IBM Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 19,782 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5ORT IN NSW 142K 2041 0 0 283977 0 0



    Cheers

    john

    -----End Original Message-----

    -----End Original Message-----
    Ted MacNeil
    There's bench-marking and there's bench-marketting!
    With over 35 years experience, guess which I believe is done by vendors?

    -
    Ted MacNEIL
    [login to unmask email]
    Twitter: @TedMacNEIL

    -----Original Message-----
    From: "Rasmussen, Steen" <[login to unmask email]>
    Date: Mon, 15 Apr 2013 00:29:19
    To: [login to unmask email]<[login to unmask email]>
    Reply-To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    This is an INTERESTING TOPIC - BENCHMARKING !!

    Every vendor - being BMC, IBM, CA, CDB etc. are all using different techniques and all are using different techniques to get the job done. You will have to look at what your goals are and what you want to accomplish. Based on these terms you can decide which solution works the best.

    CDB, BMC and CA works VERY differently than IBM Utilities - so make sure (please) you measure what your goals are and whet you want to accomplish. From my part (being a little CA'ish) - I know what our strengths are but every vendor has their own strengths.

    Please contact me off list if you need specifics ;-) HAPPY SUNDAY

    Steen Rasmussen
    CA Technologies
    Sr Engineering Services Architect
    IBM Certified Database Associate - DB2 9 Fundamentals
    IBM Certified Database Administrator - DB2 9 DBA for z/OS
    IBM Certified Database Administrator - DB2 10 DBA for z/OS

    Tel: +1-630-505-6673
    Mobile: +1-708-382-1363
    [login to unmask email]
    <mailto:[login to unmask email]>[Description: C:\Program Files\CA\GIS\CASig\CA_email.gif]http://www.ca.com

    From: charles brown [mailto:[login to unmask email]
    Sent: Sunday, April 14, 2013 6:38 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    John,

    Your test scripts are not identical - that makes it difficult to comment on or give an account for the high frame usage when using the IBM supplied.
    There are several contributing factors and lack of several documentation - here's a list

    - Why are you still coding SORTWK DD card on your JCL. I noticed you're z/os 1.9 or above. You no longer need SORTWRK ddcard on your JCL.

    - What is your Zparm setting for UTSORTAL and IGNSORTN

    - Provide us you SORT output - ICE???? For both REORGs

    - Which version of db2 are you running

    - Which SORT utility is installed in your shop
    It's pretty hard to comment on your HI-frame usage when using IBM supplied. Therefore I'd encourage you to run an identical test - if you can, so we can compare orange to orange.

    Thx!!
    Charles/b


    From: John alexander [mailto:[login to unmask email]
    Sent: Tuesday, April 09, 2013 7:32 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions


    Hi All,
    fyi. I ran a BMC Shrlevel Reference for the same table, without any sort work DDs coded, & the max central frames it used was 8k.

    I reran another IBM Shrlevel Change Reorg, & commented out all the sort work DDs, and it used 142k central frames.

    It seems that either IBM Reorg is very memory hungry regardless, or its required sort work memory calculation is incorrect (the stats

    are current for the table being tested).

    BMC Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP SHRLEVEL REFERENCE ORDER YES UNLDDN SYSRE1 LOG NO DELETEFILES NO COPY YES REGISTER NONE COPYDDN(BMCCPY) ANALYZE NO UNLOAD CONTINUE BMCSTATS YES UPDATEDB2STATS YES REDEFINE YES SORTNUM 15 MAXSORTS 4 WORKDDN (SYSUT1)

    IBM Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN UNLD SORTDATA SORTNUM 10 SORTDEVT SYSDA SHRLEVEL CHANGE LOG NO MAPPINGTABLE DBA.TMAPTBL1 MAXRO 100 DRAIN_WAIT 30 LONGLOG TERM DELAY 90 TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10

    BMC Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 188,589 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5TST IN NSW 8776 3473 0 0 0 0 0

    IBM Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 19,782 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5ORT IN NSW 142K 2041 0 0 283977 0 0



    Cheers

    john

    -----End Original Message-----

    -----End Original Message-----

    -----End Original Message-----

    Raymond Bell
    [The John Lewis Partnership]
    A) presumably that doesn't include the vendor you work for.

    B) there's only one 't' in 'marketing'. I know how accurate you like to be.

    Cheers,


    Raymond
    Sent from my BB; I couldn't care less about any typos.

    From: Ted MacNeil [mailto:[login to unmask email]
    Sent: Sunday, April 14, 2013 10:06 PM
    To: DB2-L <[login to unmask email]>
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    There's bench-marking and there's bench-marketting!
    With over 35 years experience, guess which I believe is done by vendors?
    -
    Ted MacNEIL
    [login to unmask email]
    Twitter: @TedMacNEIL
    ________________________________
    From: "Rasmussen, Steen" <[login to unmask email]>
    Date: Mon, 15 Apr 2013 00:29:19 +0000
    To: [login to unmask email]<[login to unmask email]>
    ReplyTo: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    This is an INTERESTING TOPIC – BENCHMARKING !!

    Every vendor – being BMC, IBM, CA, CDB etc. are all using different techniques and all are using different techniques to get the job done. You will have to look at what your goals are and what you want to accomplish. Based on these terms you can decide which solution works the best.

    CDB, BMC and CA works VERY differently than IBM Utilities – so make sure (please) you measure what your goals are and whet you want to accomplish. From my part (being a little CA’ish) – I know what our strengths are but every vendor has their own strengths.

    Please contact me off list if you need specifics ;-) HAPPY SUNDAY

    Steen Rasmussen
    CA Technologies
    Sr Engineering Services Architect
    IBM Certified Database Associate - DB2 9 Fundamentals
    IBM Certified Database Administrator - DB2 9 DBA for z/OS
    IBM Certified Database Administrator - DB2 10 DBA for z/OS

    Tel: +1-630-505-6673
    Mobile: +1-708-382-1363
    [login to unmask email]
    <mailto:[login to unmask email]>[file:///C:\Program%20Files\CA\GIS\CASig\CA_email.gif]http://www.ca.com

    From: charles brown [mailto:[login to unmask email]
    Sent: Sunday, April 14, 2013 6:38 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    John,

    Your test scripts are not identical – that makes it difficult to comment on or give an account for the high frame usage when using the IBM supplied.
    There are several contributing factors and lack of several documentation – here’s a list

    - Why are you still coding SORTWK DD card on your JCL. I noticed you’re z/os 1.9 or above. You no longer need SORTWRK ddcard on your JCL.

    - What is your Zparm setting for UTSORTAL and IGNSORTN

    - Provide us you SORT output – ICE???? For both REORGs

    - Which version of db2 are you running

    - Which SORT utility is installed in your shop
    It’s pretty hard to comment on your HI-frame usage when using IBM supplied. Therefore I’d encourage you to run an identical test – if you can, so we can compare orange to orange.

    Thx!!
    Charles/b


    From: John alexander [mailto:[login to unmask email]
    Sent: Tuesday, April 09, 2013 7:32 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions


    Hi All,
    fyi. I ran a BMC Shrlevel Reference for the same table, without any sort work DDs coded, & the max central frames it used was 8k.

    I reran another IBM Shrlevel Change Reorg, & commented out all the sort work DDs, and it used 142k central frames.

    It seems that either IBM Reorg is very memory hungry regardless, or its required sort work memory calculation is incorrect (the stats

    are current for the table being tested).

    BMC Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP SHRLEVEL REFERENCE ORDER YES UNLDDN SYSRE1 LOG NO DELETEFILES NO COPY YES REGISTER NONE COPYDDN(BMCCPY) ANALYZE NO UNLOAD CONTINUE BMCSTATS YES UPDATEDB2STATS YES REDEFINE YES SORTNUM 15 MAXSORTS 4 WORKDDN (SYSUT1)

    IBM Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN UNLD SORTDATA SORTNUM 10 SORTDEVT SYSDA SHRLEVEL CHANGE LOG NO MAPPINGTABLE DBA.TMAPTBL1 MAXRO 100 DRAIN_WAIT 30 LONGLOG TERM DELAY 90 TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10

    BMC Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 188,589 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5TST IN NSW 8776 3473 0 0 0 0 0

    IBM Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 19,782 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5ORT IN NSW 142K 2041 0 0 283977 0 0



    Cheers

    john

    -----End Original Message-----

    -----End Original Message-----

    -----End Original Message-----

    -----End Original Message-----
    Ted MacNeil
    A) I do not work for a vendor.
    B) 2 T's are valid for the spelling of marketting in countries of the British Commonwealth (I looked it up after your post). Canada is a member of said organisation (the S is valid, as well).
    -
    Ted MacNEIL
    [login to unmask email]
    Twitter: @TedMacNEIL

    -----Original Message-----
    From: "Bell, Raymond" <[login to unmask email]>
    Date: Mon, 15 Apr 2013 01:53:44
    To: '[login to unmask email]'<[login to unmask email]>
    Reply-To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    A) presumably that doesn't include the vendor you work for.

    B) there's only one 't' in 'marketing'. I know how accurate you like to be.

    Cheers,


    Raymond
    Sent from my BB; I couldn't care less about any typos.

    From: Ted MacNeil [mailto:[login to unmask email]
    Sent: Sunday, April 14, 2013 10:06 PM
    To: DB2-L <[login to unmask email]>
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    There's bench-marking and there's bench-marketting!
    With over 35 years experience, guess which I believe is done by vendors?
    -
    Ted MacNEIL
    [login to unmask email]
    Twitter: @TedMacNEIL
    ________________________________
    From: "Rasmussen, Steen" <[login to unmask email]>
    Date: Mon, 15 Apr 2013 00:29:19 +0000
    To: [login to unmask email]<[login to unmask email]>
    ReplyTo: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    This is an INTERESTING TOPIC – BENCHMARKING !!

    Every vendor – being BMC, IBM, CA, CDB etc. are all using different techniques and all are using different techniques to get the job done. You will have to look at what your goals are and what you want to accomplish. Based on these terms you can decide which solution works the best.

    CDB, BMC and CA works VERY differently than IBM Utilities – so make sure (please) you measure what your goals are and whet you want to accomplish. From my part (being a little CA’ish) – I know what our strengths are but every vendor has their own strengths.

    Please contact me off list if you need specifics ;-) HAPPY SUNDAY

    Steen Rasmussen
    CA Technologies
    Sr Engineering Services Architect
    IBM Certified Database Associate - DB2 9 Fundamentals
    IBM Certified Database Administrator - DB2 9 DBA for z/OS
    IBM Certified Database Administrator - DB2 10 DBA for z/OS

    Tel: +1-630-505-6673
    Mobile: +1-708-382-1363
    [login to unmask email]
    <mailto:[login to unmask email]>[file:///C:\Program%20Files\CA\GIS\CASig\CA_email.gif]http://www.ca.com

    From: charles brown [mailto:[login to unmask email]
    Sent: Sunday, April 14, 2013 6:38 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions

    John,

    Your test scripts are not identical – that makes it difficult to comment on or give an account for the high frame usage when using the IBM supplied.
    There are several contributing factors and lack of several documentation – here’s a list

    - Why are you still coding SORTWK DD card on your JCL. I noticed you’re z/os 1.9 or above. You no longer need SORTWRK ddcard on your JCL.

    - What is your Zparm setting for UTSORTAL and IGNSORTN

    - Provide us you SORT output – ICE???? For both REORGs

    - Which version of db2 are you running

    - Which SORT utility is installed in your shop
    It’s pretty hard to comment on your HI-frame usage when using IBM supplied. Therefore I’d encourage you to run an identical test – if you can, so we can compare orange to orange.

    Thx!!
    Charles/b


    From: John alexander [mailto:[login to unmask email]
    Sent: Tuesday, April 09, 2013 7:32 PM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: DB2/zOS V9 Shrlevel Change Reorg questions


    Hi All,
    fyi. I ran a BMC Shrlevel Reference for the same table, without any sort work DDs coded, & the max central frames it used was 8k.

    I reran another IBM Shrlevel Change Reorg, & commented out all the sort work DDs, and it used 142k central frames.

    It seems that either IBM Reorg is very memory hungry regardless, or its required sort work memory calculation is incorrect (the stats

    are current for the table being tested).

    BMC Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP SHRLEVEL REFERENCE ORDER YES UNLDDN SYSRE1 LOG NO DELETEFILES NO COPY YES REGISTER NONE COPYDDN(BMCCPY) ANALYZE NO UNLOAD CONTINUE BMCSTATS YES UPDATEDB2STATS YES REDEFINE YES SORTNUM 15 MAXSORTS 4 WORKDDN (SYSUT1)

    IBM Reorg Sysin

    REORG TABLESPACE SRPDB01.SINSTRP COPYDDN(COPYA1) KEEPDICTIONARY UNLDDN UNLD SORTDATA SORTNUM 10 SORTDEVT SYSDA SHRLEVEL CHANGE LOG NO MAPPINGTABLE DBA.TMAPTBL1 MAXRO 100 DRAIN_WAIT 30 LONGLOG TERM DELAY 90 TIMEOUT TERM STATISTICS TABLE(ALL) UPDATE(ALL) SAMPLE 15 INDEX(ALL) KEYCARD FREQVAL NUMCOLS 1 COUNT 10 FREQVAL NUMCOLS 2 COUNT 10 FREQVAL NUMCOLS 3 COUNT 10

    BMC Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 188,589 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5TST IN NSW 8776 3473 0 0 0 0 0

    IBM Reorg O/Mon storage stats

    Central Expanded
    Online frames . . . : 2,621,440 Online frames. . . . : 0 Offline frames. . . : 0 Offline frames . . . : 0 Total frames. . . . : 2,621,440 Total frames . . . . : 0 Unref interval count: 65,535 Migration age. . . . : n/a Available frames. . : 19,782 Migration rate . . . : /sec
    View of Selected Storage Users
    Swap Central Frames Exp. Frames Slots DSPC Jobname Status Total Fixed Total Hiper NVIO VIO User Key
    UJAR5ORT IN NSW 142K 2041 0 0 283977 0 0



    Cheers

    john

    -----End Original Message-----

    -----End Original Message-----

    -----End Original Message-----

    -----End Original Message-----

    -----End Original Message-----

    John alexander
    [Woolworths Ltd]

    Thankyou all for the feedback.

    Hope below replies will answer some the questions;

    - The reason  I was exploring to unload  to my own dataset, was because I wanted to test whether or not I could direct the Reorg to use my unload dataset instead of  the SMS temp pool, and it seems I can not

    - The sort work datasets in the JCL was just for test purposes

    - The Reorg scripts are different because we can not do BMC Shrlevel Change, as we have not installed all the required softwares. However Shrlevel Ref is still a good comparison as it creates/renames shadow datasets as well

    - In relation to the Dfsort ICE messages, please refer to the full job logs that I attached on Apr 8th.

    Appreciate all the feedback, it definitely has given me a few clues, I will pass some of  your suggestions about Dfsort parms to our sysprog, and incidently last weekend we had an issue where 2 large Shrlevel Change IBM Reorg jobs were running in parallel, and they exhausted real memory, which the the very issue I am trying to avoid,  in one of the jobs we had region 50M , and  0M in the other, which i changed to 200M, but testing has shown that region has will not change the behaviour of the utility, it  will still use real memory exactly in the same way,  even if you reduce region size from 0M to 50M, 100M or 200M etc. . 

     

    Regards

    John

     

     

    John alexander
    [Woolworths Ltd]

    Hi Charles,

    The sort utility used is DFSORT, and the sort work DDs being in JCL was just for testing purposes.

    The Zparm vales are,  UTSORTAL & IGNSORTN are set to NO, and DB2SORT = DISABLE.

    ps. please refer to  attached Reorg joblogs on the 8th for more details.

     

    Regards

    John

    Adam Baldwin
    [DB2 Consultant]

    John, as Christian has said, you can control the memory usage to some extent my overriding the sort parameters on the sort control statement. As also stated, you need to be careful that you don't constrict memory too much as this will impact the performance. DFSORT by design will attempt to use available memory in order to maximize performance. You can throttle this back but it needs to be done carefully.

    Adam.

    Christian Michel
    [IBM Deutschland Research & Development GmbH]
    Hi John,

    there are many ways to influence the behavior of your REORG job, all
    depending on the goal you want to achieve, which you have not yet told us
    (at least I am not clear about it). Basically the use of memory is a good
    thing as long as it is freely available and does not hurt other processes,
    as it will help to improve performance. If DFSORT however uses too much
    memory and other processes are starting to page, then EXPOLD=0 is a first
    good starting point. There are many more options to look into, and we can
    also take this offline if you don't want to disclose all details to the
    public.

    Mit freundlichen Grüßen / Kind regards
    Christian Michel

    Christian Michel
    E-mail: [login to unmask email]
    Phone (49)-7031-16-4293
    DB2 for z/OS Utilities Development
    Information Management Development Boeblingen, Germany



    IBM Deutschland Research & Development GmbH
    Vorsitzende des Aufsichtsrats: Martina Koederitz
    Geschäftsführung: Dirk Wittkopp
    Sitz der Gesellschaft: Böblingen / Registergericht: Amtsgericht Stuttgart,
    HRB 243294
    John alexander
    [Woolworths Ltd]

    Hi All,

    This exercise has been really helpful, I now feel a bit more confident as to how to control DFSORT real memory usage thro the use of overrides. A big thankyou to all who shared their experience & recommendations, especially to Christian & his colleagues from IBM Germany.

    The final outcome in relation this has come down to adjusting DFSORT parms EXPOLD to 50% and EXPRES to 10%, as per IBM's recommendation, which our sysprog is testing, this should prevent DB2 from using all available memory, as currently we have EXPOLD set to MAX, which is allowing DB2 to use as much as is available during large Shrlevel Change table Reorgs.

    Many thanks to all again.

     

    Regards

    John

    Lockwood Lyon
    [Fifth Third Bankcorp]
    Esteemed List,

    Question: What configuration of objects, utility parms, etc., are required in order to concurrently Load Replace one partition of a table while accessing another with SQL?

    Specifically: How to do a "LOAD RESUME LOG YES SHRLEVEL NONE ... INTO TABLE A PART 4 REPLACE" against one partition of a table-based-partitioned table, while concurrently accessing rows (via DSNTIAUL) in Partition 1.

    The Oddity: The Load drains all claim classes, particularly against TS Partition #4 (expected); However, SQLs against rows in Partition #1 are timing out due to inability to get a Claim on TS Partition #4.

    (Yes, I have checked & checked & checked. The SQL accesses only rows in Partition #1).


    Background: We have query jobs (executing DSNTIAUL) that abend with the following message:

    03.56.22 STC10913 DSNT376I +DBP2 PLAN=DSNTIAUL WITH
    CORRELATION-ID=P0DWDA63 CONNECTION-ID=BATCH
    IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNUTILB
    CORRELATION-ID=P0DWDG55 CONNECTION-ID=UTILITY

    03.56.22 STC10913 DSNT501I +DBP2 DSNILMCL RESOURCE UNAVAILABLE
    REASON 00C900BA TYPE 00002006
    NAME DDW203P1.SDW311P1.00000004

    Seems pretty clear. The LOAD Utility is draining TS partition claim classes, particularly the one for Partition #4. However, ODDLY, the SQL that accesses *only* rows in Partition #1 times out because of the drain on the claim classes on Part #4.

    Anyone have suggestions?

    - Lock Lyon
    Fifth Third Bancorp


    This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.
    Daniel Luksetich
    [DanL Database Consulting]
    Lock,
    Try removing the word "RESUME" after the word "LOAD".
    Dan

    On Tue, 23 Apr 2013 14:43:37 +0000, "Lyon, Lockwood" <[login to unmask email]>
    wrote:
    > Esteemed List,
    >
    > Question: What configuration of objects, utility parms, etc., are
    > required in order to concurrently Load Replace one partition of a table
    > while accessing another with SQL?
    >
    > Specifically: How to do a "LOAD RESUME LOG YES SHRLEVEL NONE ... INTO
    > TABLE A PART 4 REPLACE" against one partition of a
    > table-based-partitioned table, while concurrently accessing rows (via
    > DSNTIAUL) in Partition 1.
    >
    > The Oddity: The Load drains all claim classes, particularly against TS
    > Partition #4 (expected); However, SQLs against rows in Partition #1 are
    > timing out due to inability to get a Claim on TS Partition #4.
    >
    > (Yes, I have checked & checked & checked. The SQL accesses only rows in
    > Partition #1).
    >
    >
    > Background: We have query jobs (executing DSNTIAUL) that abend with the
    > following message:
    >
    > 03.56.22 STC10913 DSNT376I +DBP2 PLAN=DSNTIAUL WITH
    > CORRELATION-ID=P0DWDA63 CONNECTION-ID=BATCH
    > IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNUTILB
    > CORRELATION-ID=P0DWDG55 CONNECTION-ID=UTILITY
    >
    > 03.56.22 STC10913 DSNT501I +DBP2 DSNILMCL RESOURCE UNAVAILABLE
    > REASON 00C900BA TYPE 00002006
    > NAME DDW203P1.SDW311P1.00000004
    >
    > Seems pretty clear. The LOAD Utility is draining TS partition claim
    > classes, particularly the one for Partition #4. However, ODDLY, the SQL
    > that accesses *only* rows in Partition #1 times out because of the drain
    on
    > the claim classes on Part #4.
    >
    > Anyone have suggestions?
    >
    > - Lock Lyon
    > Fifth Third Bancorp
    >
    >
    > This e-mail transmission contains information that is confidential and
    may
    > be privileged. It is intended only for the addressee(s) named above.
    If
    > you receive this e-mail in error, please do not read, copy or
    disseminate
    > it in any manner. If you are not the intended recipient, any disclosure,
    > copying, distribution or use of the contents of this information is
    > prohibited. Please reply to the message immediately by informing the
    sender
    > that the message was misdirected. After replying, please erase it from
    your
    > computer system. Your assistance in correcting this error is
    appreciated.
    >
    > -----End Original Message-----
    Lockwood Lyon
    [Fifth Third Bankcorp]
    Dan,

    Sorry, typo, my fault. The "RESUME" doesn't appear in our Load statement.

    - Lock

    -----Original Message-----
    From: Daniel Luksetich [mailto:[login to unmask email]
    Sent: Tuesday, April 23, 2013 10:49 AM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: [DB2z V10] Concurrent Load and Select against different partitions

    Lock,
    Try removing the word "RESUME" after the word "LOAD".
    Dan

    On Tue, 23 Apr 2013 14:43:37 +0000, "Lyon, Lockwood" <[login to unmask email]>
    wrote:
    > Esteemed List,
    >
    > Question: What configuration of objects, utility parms, etc., are
    > required in order to concurrently Load Replace one partition of a
    > table while accessing another with SQL?
    >
    > Specifically: How to do a "LOAD RESUME LOG YES SHRLEVEL NONE ... INTO
    > TABLE A PART 4 REPLACE" against one partition of a
    > table-based-partitioned table, while concurrently accessing rows (via
    > DSNTIAUL) in Partition 1.
    >
    > The Oddity: The Load drains all claim classes, particularly against TS
    > Partition #4 (expected); However, SQLs against rows in Partition #1
    > are timing out due to inability to get a Claim on TS Partition #4.
    >
    > (Yes, I have checked & checked & checked. The SQL accesses only rows
    > in Partition #1).
    >
    >
    > Background: We have query jobs (executing DSNTIAUL) that abend with
    > the following message:
    >
    > 03.56.22 STC10913 DSNT376I +DBP2 PLAN=DSNTIAUL WITH
    > CORRELATION-ID=P0DWDA63 CONNECTION-ID=BATCH
    > IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNUTILB
    > CORRELATION-ID=P0DWDG55 CONNECTION-ID=UTILITY
    >
    > 03.56.22 STC10913 DSNT501I +DBP2 DSNILMCL RESOURCE UNAVAILABLE
    > REASON 00C900BA TYPE 00002006
    > NAME DDW203P1.SDW311P1.00000004
    >
    > Seems pretty clear. The LOAD Utility is draining TS partition claim
    > classes, particularly the one for Partition #4. However, ODDLY, the
    > SQL that accesses *only* rows in Partition #1 times out because of the
    > drain
    on
    > the claim classes on Part #4.
    >
    > Anyone have suggestions?
    >
    > - Lock Lyon
    > Fifth Third Bancorp
    >
    >
    > This e-mail transmission contains information that is confidential and
    may
    > be privileged. It is intended only for the addressee(s) named above.
    If
    > you receive this e-mail in error, please do not read, copy or
    disseminate
    > it in any manner. If you are not the intended recipient, any
    > disclosure, copying, distribution or use of the contents of this
    > information is prohibited. Please reply to the message immediately by
    > informing the
    sender
    > that the message was misdirected. After replying, please erase it from
    your
    > computer system. Your assistance in correcting this error is
    appreciated.
    >
    > -----End Original Message-----

    -----End Original Message-----


    This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.
    Lockwood Lyon
    [Fifth Third Bankcorp]
    So, the ACTUAL syntax (sorry everyone) we are using is the following (table name removed):

    LOAD DATA LOG YES SORTKEYS SORTDEVT (DISK) SORTNUM(5)
    INDDN SYSREC00 INTO TABLE A PART 04
    REPLACE

    - Lock Lyon
    Fifth Third Bancorp

    -----Original Message-----
    From: Daniel Luksetich [mailto:[login to unmask email]
    Sent: Tuesday, April 23, 2013 10:49 AM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: [DB2z V10] Concurrent Load and Select against different partitions

    Lock,
    Try removing the word "RESUME" after the word "LOAD".
    Dan

    On Tue, 23 Apr 2013 14:43:37 +0000, "Lyon, Lockwood" <[login to unmask email]>
    wrote:
    > Esteemed List,
    >
    > Question: What configuration of objects, utility parms, etc., are
    > required in order to concurrently Load Replace one partition of a
    > table while accessing another with SQL?
    >
    > Specifically: How to do a "LOAD RESUME LOG YES SHRLEVEL NONE ... INTO
    > TABLE A PART 4 REPLACE" against one partition of a
    > table-based-partitioned table, while concurrently accessing rows (via
    > DSNTIAUL) in Partition 1.
    >
    > The Oddity: The Load drains all claim classes, particularly against TS
    > Partition #4 (expected); However, SQLs against rows in Partition #1
    > are timing out due to inability to get a Claim on TS Partition #4.
    >
    > (Yes, I have checked & checked & checked. The SQL accesses only rows
    > in Partition #1).
    >
    >
    > Background: We have query jobs (executing DSNTIAUL) that abend with
    > the following message:
    >
    > 03.56.22 STC10913 DSNT376I +DBP2 PLAN=DSNTIAUL WITH
    > CORRELATION-ID=P0DWDA63 CONNECTION-ID=BATCH
    > IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNUTILB
    > CORRELATION-ID=P0DWDG55 CONNECTION-ID=UTILITY
    >
    > 03.56.22 STC10913 DSNT501I +DBP2 DSNILMCL RESOURCE UNAVAILABLE
    > REASON 00C900BA TYPE 00002006
    > NAME DDW203P1.SDW311P1.00000004
    >
    > Seems pretty clear. The LOAD Utility is draining TS partition claim
    > classes, particularly the one for Partition #4. However, ODDLY, the
    > SQL that accesses *only* rows in Partition #1 times out because of the
    > drain
    on
    > the claim classes on Part #4.
    >
    > Anyone have suggestions?
    >
    > - Lock Lyon
    > Fifth Third Bancorp
    >
    >
    > This e-mail transmission contains information that is confidential and
    may
    > be privileged. It is intended only for the addressee(s) named above.
    If
    > you receive this e-mail in error, please do not read, copy or
    disseminate
    > it in any manner. If you are not the intended recipient, any
    > disclosure, copying, distribution or use of the contents of this
    > information is prohibited. Please reply to the message immediately by
    > informing the
    sender
    > that the message was misdirected. After replying, please erase it from
    your
    > computer system. Your assistance in correcting this error is
    appreciated.
    >
    > -----End Original Message-----

    -----End Original Message-----


    This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.
    Joe Geller
    [Moody's]

    Lock,

     You say that the SQL is only selecting rows from Partition 1.  Does it have a predicate on the partitioning column to restrict it to part 1, or is it other predicates that happen to limit it to that one partition?  Without predicates on the partitioning column, DB2 wouldn't know that it didn't need the others.

    Joe

    In Reply to Lockwood Lyon:

    So, the ACTUAL syntax (sorry everyone) we are using is the following (table name removed):

    LOAD DATA LOG YES SORTKEYS SORTDEVT (DISK) SORTNUM(5)
    INDDN SYSREC00 INTO TABLE A PART 04
    REPLACE

    - Lock Lyon
    Fifth Third Bancorp

    -----Original Message-----
    From: Daniel Luksetich [mailto:[login to unmask email]
    Sent: Tuesday, April 23, 2013 10:49 AM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: [DB2z V10] Concurrent Load and Select against different partitions

    Lock,
    Try removing the word "RESUME" after the word "LOAD".
    Dan

    On Tue, 23 Apr 2013 14:43:37 +0000, "Lyon, Lockwood" <[login to unmask email]>
    wrote:
    > Esteemed List,
    >
    > Question: What configuration of objects, utility parms, etc., are
    > required in order to concurrently Load Replace one partition of a
    > table while accessing another with SQL?
    >
    > Specifically: How to do a "LOAD RESUME LOG YES SHRLEVEL NONE ... INTO
    > TABLE A PART 4 REPLACE" against one partition of a
    > table-based-partitioned table, while concurrently accessing rows (via
    > DSNTIAUL) in Partition 1.
    >
    > The Oddity: The Load drains all claim classes, particularly against TS
    > Partition #4 (expected); However, SQLs against rows in Partition #1
    > are timing out due to inability to get a Claim on TS Partition #4.
    >
    > (Yes, I have checked & checked & checked. The SQL accesses only rows
    > in Partition #1).
    >
    >
    > Background: We have query jobs (executing DSNTIAUL) that abend with
    > the following message:
    >
    > 03.56.22 STC10913 DSNT376I +DBP2 PLAN=DSNTIAUL WITH
    > CORRELATION-ID=P0DWDA63 CONNECTION-ID=BATCH
    > IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNUTILB
    > CORRELATION-ID=P0DWDG55 CONNECTION-ID=UTILITY
    >
    > 03.56.22 STC10913 DSNT501I +DBP2 DSNILMCL RESOURCE UNAVAILABLE
    > REASON 00C900BA TYPE 00002006
    > NAME DDW203P1.SDW311P1.00000004
    >
    > Seems pretty clear. The LOAD Utility is draining TS partition claim
    > classes, particularly the one for Partition #4. However, ODDLY, the
    > SQL that accesses *only* rows in Partition #1 times out because of the
    > drain
    on
    > the claim classes on Part #4.
    >
    > Anyone have suggestions?
    >
    > - Lock Lyon
    > Fifth Third Bancorp
    >
    >
    > This e-mail transmission contains information that is confidential and
    may
    > be privileged. It is intended only for the addressee(s) named above.
    If
    > you receive this e-mail in error, please do not read, copy or
    disseminate
    > it in any manner. If you are not the intended recipient, any
    > disclosure, copying, distribution or use of the contents of this
    > information is prohibited. Please reply to the message immediately by
    > informing the
    sender
    > that the message was misdirected. After replying, please erase it from
    your
    > computer system. Your assistance in correcting this error is
    appreciated.
    >
    > -----End Original Message-----

    -----End Original Message-----


    This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.
    Jay Reavill
    Just a thought... Could it be that it's due to the access path not limiting it to just partition 1 (page range)? So even tho the data is only in part 1, DB2 doesn't know that and thinks that it could potentially hit all parts.


     
    Jay Reavill | Debit IT | Database Administrator
    Office: 727.227.2144 | Cell: 727.215.5794
    : [login to unmask email]

    -----Original Message-----
    From: Lyon, Lockwood [mailto:[login to unmask email]
    Sent: Tuesday, April 23, 2013 11:04 AM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: [DB2z V10] Concurrent Load and Select against different partitions

    So, the ACTUAL syntax (sorry everyone) we are using is the following (table name removed):

    LOAD DATA LOG YES SORTKEYS SORTDEVT (DISK) SORTNUM(5)
    INDDN SYSREC00 INTO TABLE A PART 04
    REPLACE

    - Lock Lyon
    Fifth Third Bancorp

    -----Original Message-----
    From: Daniel Luksetich [mailto:[login to unmask email]
    Sent: Tuesday, April 23, 2013 10:49 AM
    To: [login to unmask email]
    Subject: [DB2-L] - RE: [DB2z V10] Concurrent Load and Select against different partitions

    Lock,
    Try removing the word "RESUME" after the word "LOAD".
    Dan

    On Tue, 23 Apr 2013 14:43:37 +0000, "Lyon, Lockwood" <[login to unmask email]>
    wrote:
    > Esteemed List,
    >
    > Question: What configuration of objects, utility parms, etc., are
    > required in order to concurrently Load Replace one partition of a
    > table while accessing another with SQL?
    >
    > Specifically: How to do a "LOAD RESUME LOG YES SHRLEVEL NONE ... INTO
    > TABLE A PART 4 REPLACE" against one partition of a
    > table-based-partitioned table, while concurrently accessing rows (via
    > DSNTIAUL) in Partition 1.
    >
    > The Oddity: The Load drains all claim classes, particularly against TS
    > Partition #4 (expected); However, SQLs against rows in Partition #1
    > are timing out due to inability to get a Claim on TS Partition #4.
    >
    > (Yes, I have checked & checked & checked. The SQL accesses only rows
    > in Partition #1).
    >
    >
    > Background: We have query jobs (executing DSNTIAUL) that abend with
    > the following message:
    >
    > 03.56.22 STC10913 DSNT376I +DBP2 PLAN=DSNTIAUL WITH
    > CORRELATION-ID=P0DWDA63 CONNECTION-ID=BATCH
    > IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNUTILB
    > CORRELATION-ID=P0DWDG55 CONNECTION-ID=UTILITY
    >
    > 03.56.22 STC10913 DSNT501I +DBP2 DSNILMCL RESOURCE UNAVAILABLE
    > REASON 00C900BA TYPE 00002006
    > NAME DDW203P1.SDW311P1.00000004
    >
    > Seems pretty clear. The LOAD Utility is draining TS partition claim
    > classes, particularly the one for Partition #4. However, ODDLY, the
    > SQL that accesses *only* rows in Partition #1 times out because of the
    > drain
    on
    > the claim classes on Part #4.
    >
    > Anyone have suggestions?
    >
    > - Lock Lyon
    > Fifth Third Bancorp
    >
    >
    > This e-mail transmission contains information that is confidential and
    may
    > be privileged. It is intended only for the addressee(s) named above.
    If
    > you receive this e-mail in error, please do not read, copy or
    disseminate
    > it in any manner. If you are not the intended recipient, any
    > disclosure, copying, distribution or use of the contents of this
    > information is prohibited. Please reply to the message immediately by
    > informing the
    sender
    > that the message was misdirected. After replying, please erase it from
    your
    > computer system. Your assistance in correcting this error is
    appreciated.
    >
    > -----End Original Message-----

    -----End Original Message-----


    This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

    -----End Original Message-----


    _____________
    The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
    Daniel Luksetich
    [DanL Database Consulting]
    I guess I should have read the whole message, but that stuck out like a
    sore thumb. Your SQL is desiring a lock on part 4 for some reason. You
    supplying a range predicate to the query to eliminate partitions? I've seen
    this before but with successive partitions. It was a situation in which the
    query was not using the complete partitioning key. For example, the
    partitioning was on two columns, x and y

    where x = 1 qualified 10 parts, but wanted an IS lock on the other parts
    where x = 1 and y between 'A' and 'J' qualified the same 10 parts, but did
    not want an IS lock on the other parts

    I hope you find this useful.

    Dan

    On Tue, 23 Apr 2013 14:59:58 +0000, "Lyon, Lockwood" <[login to unmask email]>
    wrote:
    > Dan,
    >
    > Sorry, typo, my fault. The "RESUME" doesn't appear in our Load
    statement.
    >
    > - Lock
    >
    > -----Original Message-----
    > From: Daniel Luksetich [mailto:[login to unmask email]
    > Sent: Tuesday, April 23, 2013 10:49 AM
    > To: [login to unmask email]
    > Subject: [DB2-L] - RE: [DB2z V10] Concurrent Load and Select against
    > different partitions
    >
    > Lock,
    > Try removing the word "RESUME" after the word "LOAD".
    > Dan
    >
    > On Tue, 23 Apr 2013 14:43:37 +0000, "Lyon, Lockwood"
    <[login to unmask email]>
    > wrote:
    >> Esteemed List,
    >>
    >> Question: What configuration of objects, utility parms, etc., are
    >> required in order to concurrently Load Replace one partition of a
    >> table while accessing another with SQL?
    >>
    >> Specifically: How to do a "LOAD RESUME LOG YES SHRLEVEL NONE ... INTO
    >> TABLE A PART 4 REPLACE" against one partition of a
    >> table-based-partitioned table, while concurrently accessing rows (via
    >> DSNTIAUL) in Partition 1.
    >>
    >> The Oddity: The Load drains all claim classes, particularly against TS
    >> Partition #4 (expected); However, SQLs against rows in Partition #1
    >> are timing out due to inability to get a Claim on TS Partition #4.
    >>
    >> (Yes, I have checked & checked & checked. The SQL accesses only rows
    >> in Partition #1).
    >>
    >>
    >> Background: We have query jobs (executing DSNTIAUL) that abend with
    >> the following message:
    >>
    >> 03.56.22 STC10913 DSNT376I +DBP2 PLAN=DSNTIAUL WITH
    >> CORRELATION-ID=P0DWDA63 CONNECTION-ID=BATCH
    >> IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=DSNUTILB
    >> CORRELATION-ID=P0DWDG55 CONNECTION-ID=UTILITY
    >>
    >> 03.56.22 STC10913 DSNT501I +DBP2 DSNILMCL RESOURCE UNAVAILABLE
    >> REASON 00C900BA TYPE 00002006
    >> NAME DDW203P1.SDW311P1.00000004
    >>
    >> Seems pretty clear. The LOAD Utility is draining TS partition claim
    >> classes, particularly the one for Partition #4. However, ODDLY, the
    >> SQL that accesses *only* rows in Partition #1 times out because of the
    >> drain
    > on
    >> the claim classes on Part #4.
    >>
    >> Anyone have suggestions?
    >>
    >> - Lock Lyon
    >> Fifth Third Bancorp
    >>
    >>
    >> This e-mail transmission contains information that is confidential and
    > may
    >> be privileged. It is intended only for the addressee(s) named above.
    > If
    >> you receive this e-mail in error, please do not read, copy or
    > disseminate
    >> it in any manner. If you are not the intended recipient, any
    >> disclosure, copying, distribution or use of the contents of this
    >> information is prohibited. Please reply to the message immediately by
    >> informing the
    > sender
    >> that the message was misdirected. After replying, please erase it from
    > your
    >> computer system. Your assistance in correcting this error is
    > appreciated.
    >>
    >> -----End Original Message-----
    >
    > -----End Original Message-----
    >
    >
    > This e-mail transmission contains information that is confidential and
    may
    > be privileged. It is intended only for the addressee(s) named above.
    If
    > you receive this e-mail in error, please do not read, copy or
    disseminate
    > it in any manner. If you are not the intended recipient, any disclosure,
    > copying, distribution or use of the contents of this information is
    > prohibited. Please reply to the message immediately by informing the
    sender
    > that the message was misdirected. After replying, please erase it from
    your
    > computer system. Your assistance in correcting this error is
    appreciated.
    >
    > -----End Original Message-----

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact