Yes, you could say that, but there are trade-offs. When dealing with the EQUALS option, a field is internally added to each record by the sort. That is more data that has to be carried around internally and on the SORTWK files.
Consider that to accommodate over 4 billion rows, that increased the DASD requirements by at least 8G. Please note - I made an error in my previous post. DB2 Sort and SyncSort can accommodate 2**48 records, not 2**40 records. When the amount of data approaches 256 trillion records, I am sure we will accommodate it. Will we jump to a full 2**64? Probably, but that remains to be seen. When I started with Syncsort, a 100 MegaByte sort was BIG and a 4 billion record sort was unthinkable. Now a 100M sort can be an in-core sort. What will the future bring? I certainly don't know, but at the rate data is growing, in the not too distant future we will look at a 4 billion record sort and consider it normal. Chris Blaicher Senior Software Engineer, Software Services Syncsort Incorporated 50 Tice Boulevard, Woodcliff Lake, NJ 07677 P: 201-930-8260 | M: 512-627-3803 E: cblaic...@syncsort.com -----Original Message----- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Rob Schramm Sent: Tuesday, October 09, 2012 9:50 AM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Reorganizing Partition Table Space With REBALANCE Option Problem. Considering how data is growing, don't these limitations seem a bit arbitrary? 2**32 or 2**40? Rob Schramm On Oct 9, 2012 10:01 AM, "Blaicher, Christopher Y." <cblaic...@syncsort.com> wrote: > DB2 Sort and SyncSort do not have that limitation. Their limitation > is > 2**40 rather than 2**32. > > Chris Blaicher > Senior Software Engineer, Software Services Syncsort Incorporated > 50 Tice Boulevard, Woodcliff Lake, NJ 07677 > P: 201-930-8260 | M: 512-627-3803 > E: cblaic...@syncsort.com > > > -----Original Message----- > From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On > Behalf Of Mehrshad Manshadi > Sent: Tuesday, October 09, 2012 1:39 AM > To: IBM-MAIN@LISTSERV.UA.EDU > Subject: Re: Reorganizing Partition Table Space With REBALANCE Option > Problem. > > Hi David, > > Thanks for your useful information. > > So The APAR PM43006 will solve our problem? Am I right? > > Best regards > Manshadi > > > > > ________________________________ > From: David Betten <bet...@us.ibm.com> > To: IBM-MAIN@LISTSERV.UA.EDU > Sent: Monday, October 8, 2012 6:01 PM > Subject: Re: Reorganizing Partition Table Space With REBALANCE Option > Problem. > > Unfortunately, you are exceeding a DFSORT limitation on the number of > records that can be sorted. DFSORT has the following limitations > > 1. Variable-length records - 4294967295 records 2. EQUALS in effect - > 4294967295 records 3. VLSHRT in effect - 2147483647 records 4. Blockset > technique not selected - 2147483647 records > > > Number 4 will not be a problem since Blockset is always used for sorts > invoked by DB2 Utilities. DB2 APAR PM43006 causes the utilities to pass > NOVLSHRT and NOEQUALS which would resolve numbers 2 and 3 but since this is > a vaiable length sort, you're still going to have that first limitation of > 4,294,967,295 records. Normally you'd want to break up the reorg by > partitions but since you're doing a rebalance, I guess that's why it's > trying to sort the entire file at once. I suggest you open PMR and see if > DB2 Utilities support can provide a solution. > > Also two additional notes about this. > > 1. It looks like your DFSORTinstallation default for DSA is 64 and that's > causing the utility to limit the mainsize it passes. You probably want > something much larger for a sort this size. I'd suggest changing your > installation default to something much larger like DSA=256. > 2. It appears you are running DFSORT V1R5. That has been out of service > for quite some time. > > > > Have a nice day, > Dave Betten > DFSMS Performance Engineer > IBM Corporation > email: bet...@us.ibm.com > 1-301-240-3809 > DFSORT/MVSontheweb at http://www.ibm.com/storage/dfsort/ > > IBM Mainframe Discussion List <IBM-MAIN@listserv.ua.edu> wrote on > 10/08/2012 10:10:10 AM: > > > From: Mehrshad Manshadi <m_mansh...@yahoo.com> > > To: IBM-MAIN@listserv.ua.edu, > > Date: 10/08/2012 10:11 AM > > Subject: Re: Reorganizing Partition Table Space With REBALANCE Option > Problem. > > Sent by: IBM Mainframe Discussion List <IBM-MAIN@listserv.ua.edu> > > > > Hi Dave, > > > > > > > > The attached is sort message of the output job. > > > > > > 1ICE143I 0 BLOCKSET SORT TECHNIQUE SELECTED > > > > ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, > > EXAMPLES A > > ICE000I 0 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 11: > > 18 ON MON OC > > 0 OPTION MOSIZE=0 > > ICE140I 0 END OF PARAMETERS FROM DFSPARM - SYSIN OR SORTCNTL/ > > PARAMETER LIST CO > > SORT FIELDS=(00005.0,00028. > > 0,A),FORMAT=BI,FILSZ=E000004979426640,DYNA > > OC=(SYSDA,04) > > RECORD TYPE=V,LENGTH=(00070,00070,00070) > > OPTION > MSGPRT=ALL,MSGDDN=UTPRINT,AVGRLEN=00070,MAINSIZE=065536K > > ICE201I 0 RECORD TYPE IS V - DATA STARTS IN POSITION 5 > > ICE751I 0 C5-BASE C6-BASE C7-BASE C8-Q83041 E4-BASE C9-BASE > > E5-Q90312 > > ICE193I 0 ICEAM2 ENVIRONMENT IN EFFECT - ICEAM2 INSTALLATION MODULE > SELECTED > > ICE089I 0 ROINHORB.REORG .DSNUPROC, INPUT LRECL = 70, TYPE = V > > ICE092I 0 MAIN STORAGE = (67108864,67108864,67108864) > > ICE156I 0 MAIN STORAGE ABOVE 16MB = (66060288,66060288) > > ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 > > ,SPANINC=RC16,VLSCMP=N,SZERO=Y > > ICE128I 0 OPTIONS: > > SIZE=67108864,MAXLIM=1048576,MINLIM=450560,EQUALS=Y,LIST=Y,E > > ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO > ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHA > > ICE130I 0 OPTIONS: RESALL=0,RESINV=0,SVC=109 > > ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N, > > ICE131I 0 OPTIONS: > > TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=16384,CINV=Y,CFW > > ICE132I 0 OPTIONS: > > VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXIT > > ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX > > ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAM > > ICE235I 0 OPTIONS: NULLOUT=RC0 > > ICE750I 0 DC 0 TC 0 CS DSVXX KSZ 32 VSZ 32 > > ICE752I 0 FSZ=4979426640 RE IGN=0 C AVG=70 70 WSP=452719355 E > > DYN=0 > 0 > > ICE121A 7 FILE SIZE IS TOO LARGE > > ICE751I 1 D8-Q83041 D4-Q84357 E8-BASE > > ICE052I 0 END OF DFSORT > > > > > > Best regards > > Manshadi > > > > > > > > ________________________________ > > From: David Betten <bet...@us.ibm.com> > > To: IBM-MAIN@LISTSERV.UA.EDU > > Sent: Monday, October 8, 2012 4:05 PM > > Subject: Re: Reorganizing Partition Table Space With REBALANCE Option > Problem. > > > > You need to look at the sort messages to determine why the sort failed. > If > > you want, send the entire joblog to the DFSORT hot line > (dfs...@us.ibm.com) > > and we can look it over for you. > > > > > > Have a nice day, > > Dave Betten > > DFSMS Performance Engineer > > IBM Corporation > > email: bet...@us.ibm.com > > 1-301-240-3809 > > DFSORT/MVSontheweb at http://www.ibm.com/storage/dfsort/ > > > > IBM Mainframe Discussion List <IBM-MAIN@listserv.ua.edu> wrote on > > 10/08/2012 08:03:35 AM: > > > > > From: Mehrshad Manshadi <m_mansh...@yahoo.com> > > > To: IBM-MAIN@listserv.ua.edu, > > > Date: 10/08/2012 08:06 AM > > > Subject: Reorganizing Partition Table Space With REBALANCE Option > > Problem. > > > Sent by: IBM Mainframe Discussion List <IBM-MAIN@listserv.ua.edu> > > > > > > Hi, > > > > > > We have a table space with 250 partition and 4,600,000,000 records > > > which it's have to re partition. > > > > > > Because of lack space on the DASD we are trying to use Tape on some > > > DD cards as follows : > > > > > > //SYSREC DD DSN=TEMPDS.TAPE1,DISP=(MOD,CATLG), > > > // UNIT=CART,RETPD=36,BLKSIZE=0,LABEL=(,SL) > > > //SYSUT1 DD DSN=TEMPDS.TAPE2,DISP=(NEW,CATLG), > > > // UNIT=CART,RETPD=36,BLKSIZE=0,LABEL=(,SL) > > > //SORTOUT DD > > DSN=TEMPDS.TAPE3,DISP=(NEW,CATLG), > > > // UNIT=CART,RETPD=36,BLKSIZE=0,LABEL=(,SL) > > > //SYSCOPY DD DSN=TEMPDS.TAPE4,DISP=(NEW,CATLG), > > > // UNIT=CART,RETPD=36,BLKSIZE=0,LABEL=(,SL) > > > > > > We are using 123 work data sets as follows : > > > > > > //SORTWK01 DD DISP=(NEW,DELETE),UNIT=SYSDA, // > > > SPACE=(CYL,(4300,1000),RLSE), // > > > VOL=(,,,180),STORCLAS=SCLARGE > > > //SORTWK02 DD DISP=(NEW,DELETE),UNIT=SYSDA, // > > > SPACE=(CYL,(4300,1000),RLSE), // > > > VOL=(,,,180),STORCLAS=SCLARGE > > > //SORTWK03 DD DISP=(NEW,DELETE),UNIT=SYSDA, // > > > SPACE=(CYL,(4300,1000),RLSE), // > > VOL=(,,,180),STORCLAS=SCLARGE > > > > > > . > > > . > > > . > > > . > > > . > > > . > > > //SORTWK4F DD DISP=(NEW,DELETE),UNIT=SYSDA, // > > > SPACE=(CYL,(4300,1000),RLSE), // > > > VOL=(,,,180),STORCLAS=SCLARGE > > > > > > And the reorg options as follows : > > > > > > //DFSPARM DD * > > > OPTION MOSIZE=0 > > > //DSNUPROC.SYSIN DD * > > > REORG TABLESPACE QQBANCS.TSINHO REBALANCE > > > LOG NO SORTDATA SORTDEVT SYSDA SORTNUM 4 > > > COPYDDN(SYSCOPY) > > > /* > > > > > > The problem is the unload phase cont the number of records which we > > > expected and after this we assume that it should be switch to next > > > step , but the job failed with following error: > > > > > > DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = ROINHORB DSNUGTIS - > > PROCESSING SYSIN AS EBCDIC > > > DSNUGUTC - REORG TABLESPACE QQBANCS.TSINHO REBALANCE LOG NO > > > SORTDATA > > > > > > DSNUGSRT - ERROR FROM SORT COMPONENT RC=16, UTILITY STOPPED DSNUGBAC > > > - UTILITY BATCH MEMORY EXECUTION ABENDED, REASON=X'00E40005' > > > > > > Please help if you have any idea about it. > > > > > > Best regards > > > Manshadi > > > > > > -------------------------------------------------------------------- > > > -- For IBM-MAIN subscribe / signoff / archive access instructions, > > > send email to lists...@listserv.ua.edu with the message: INFO > > > IBM-MAIN > > > > > ---------------------------------------------------------------------- > > For IBM-MAIN subscribe / signoff / archive access instructions, send > > email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > > > > ---------------------------------------------------------------------- > > For IBM-MAIN subscribe / signoff / archive access instructions, send > > email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > > > ---------------------------------------------------------------------- > For IBM-MAIN subscribe / signoff / archive access instructions, send email > to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > > ---------------------------------------------------------------------- > For IBM-MAIN subscribe / signoff / archive access instructions, send email > to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > > > > ATTENTION: ----- > > The information contained in this message (including any files transmitted > with this message) may contain proprietary, trade secret or other > confidential and/or legally privileged information. Any pricing > information contained in this message or in any files transmitted with this > message is always confidential and cannot be shared with any third parties > without prior written approval from Syncsort. This message is intended to > be read only by the individual or entity to whom it is addressed or by > their designee. If the reader of this message is not the intended > recipient, you are on notice that any use, disclosure, copying or > distribution of this message, in any form, is strictly prohibited. If you > have received this message in error, please immediately notify the sender > and/or Syncsort and destroy all copies of this message in your possession, > custody or control. > > ---------------------------------------------------------------------- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN