tag:blogger.com,1999:blog-4516533711330247058.post4862634874641964947..comments2024-03-28T07:32:09.246-07:00Comments on Robert's Db2 blog: You DO Let DB2 for z/OS Allocate Utility Sort Work Data Sets, Don't You?Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-4516533711330247058.post-16301800650946620532023-10-17T03:37:17.144-07:002023-10-17T03:37:17.144-07:00Thanks much for clarification.Thanks much for clarification.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-49983203371089390772023-10-15T20:38:44.335-07:002023-10-15T20:38:44.335-07:00Db2 never does the sorting for utilities. That sor...Db2 never does the sorting for utilities. That sorting is always done by DFSORT (unless the IBM software product Db2 Sort is installed on the system). My understanding is that DFSORT can dynamically allocate sort work data sets when you have a SORTDEVT and a SORTNUM specification for a REORG job (you can read about those REORG options on this page in the Db2 documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=tablespace-syntax-options-reorg-control-statement). If you want DFSORT's dynamic allocation of sort work data sets to be directed by Db2, either leave SORTNUM out of the REORG utility control statement, or set the ZPARM parameter IGNSORTN to YES. Of course, dynamic allocation of sort work data sets, whether done by DFSORT on its own or by DFSORT as directed by Db2, means not allocating them via DD statements in the job's JCL.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-72999193427838410092023-10-15T05:14:04.574-07:002023-10-15T05:14:04.574-07:00Hi Robert,You have mentioned "regardless of w...Hi Robert,You have mentioned "regardless of whether this dynamic allocation is done by DB2 (preferred) or DFSORT." how is the dynamic allocation done by db2 & how it is done by dfsort? Do you mean specifying sortwk* dd name will cause db2 to do sort? and having parm sortdevt cause dfsort to do sort? Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-61317508884764985112020-05-13T20:30:01.891-07:002020-05-13T20:30:01.891-07:00If you do not have a SORTNUM specification in a ut...If you do not have a SORTNUM specification in a utility control statement for a utility such as LOAD or REORG, or you do have a SORTNUM specification but the parameter IGNSORTN in ZPARM is set to YES, Db2 will use real-time statistics to estimate the number of records that will be sorted, and that determines the size of dynamically allocated sort work data sets. The real-time statistics should be accurate. Yes, it could be some time since REORG or RUNSTATS was last executed for an object, but the insert and delete counters are updated when those statements are executed, so the record-count estimate should still be quite accurate.<br /><br />My understanding is that Db2 does not take availability of space into account when determining the size of sort work data sets to be used when a utility is executed. Could disk space available for sorting be exhausted? Yes, that is possible, depending on the size of the pool available for that purpose and on the number of sort-driving utilities that are executing concurrently. If running out of sort space is a problem, a possible solution is to implement the IBM Db2 Sort product. Db2 Sort does a very good job of adjusting sort resources used for Db2 utilities on the fly, and that can lead to more utility jobs running successfully to completion, versus failing due to sort space problems.<br /><br />Robert Roberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-31226106930552228652020-05-13T06:40:18.146-07:002020-05-13T06:40:18.146-07:00And one more thing, when DB2 is estimating the SOR...And one more thing, when DB2 is estimating the SORTWK* datasets , is it aware of the Storage Pool where these SORTWK* data sets will be allocated , by any chance.<br />I don't think it will have any idea, but just want to ask you.<br /><br />I ask this, because I guess DB2 will allocate the minimum number of data sets to maximise parallelism , but what that might result in is bigger sortwork data sets and thus more difficult to have them fit on Storage Volumes, particularly when many other Reorgs are running in parallel. This might result in B37 or similar errors.<br /><br />Or is this not true.<br /><br />Thanks a lot, in advance.Tusharhttps://www.blogger.com/profile/16042161247070999386noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-12181032668223862702020-05-13T04:13:29.330-07:002020-05-13T04:13:29.330-07:00Hi Robert,
Does SORTNUM elimination use solely RT...Hi Robert,<br /><br />Does SORTNUM elimination use solely RTS.<br /><br />If it does so, then would DB2 estimate very inaccuratelty, if RTS is out of sync for any reason.<br />I am asking this, since I have heard and read at a couple of places that RTS is still not 100% reliable , although it has been improved over the years.<br />I am particularly concerned for objects which don't get Runstats/Reorg often.<br /><br />Thank you.<br /><br />Regards,<br />Tushar JhaTusharhttps://www.blogger.com/profile/16042161247070999386noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-9523856794216107612017-12-20T07:06:20.376-08:002017-12-20T07:06:20.376-08:00Yes, that would be a better option, IF you are set...Yes, that would be a better option, IF you are set up to enable Db2 to direct the dynamic allocation of sort data sets (as pointed out in the blog post, that involves setting UTSORTAL in ZPARM to YES, having no DFSORT-related DD cards in the job's JCL, and not specifying SORTNUM in the utility control statement). You might want to validate this approach with a few of your REORG jobs, and then (assuming positive results, which I'd expect) set IGNSORTN in ZPARM to YES so that any SORTNUM specification in a REORG utility control statement will be ignored.<br /><br />If you provide a SORTNUM value, there's a good chance that your provided value will be sub-optimal. A too-high value can indeed reduce the degree of parallelism for a REORG job. Best to let Db2 figure this out.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-14898407666712090492017-12-15T02:20:11.342-08:002017-12-15T02:20:11.342-08:00in addition to that will it be a better option If ...in addition to that will it be a better option If I completely omit the SORTNUM parameter from the job??Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-42399317180157406582017-12-15T02:16:59.343-08:002017-12-15T02:16:59.343-08:00Hi Robert,
thanks for all the information!! I ha...Hi Robert, <br /><br />thanks for all the information!! I have one question. I am giving SORTDEVT DISK SORTNUM 90 in most of my Reorg jobs. If utility required very less sort work datasets than 90. Then specifying large value of SORTNUM will effect the degree of parallelism or not??Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-45715252086793321382014-07-17T13:22:56.006-07:002014-07-17T13:22:56.006-07:00Great clarification for newbies (like me)Great clarification for newbies (like me)Anonymousnoreply@blogger.com