eliminate dupes and sum selected records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

eliminate dupes and sum selected records

Postby dgrimsley » Fri Jul 23, 2010 7:25 pm

I have a VB file records are 500 bytes long.

I have created a 4 step process that works but I would like to know if there is a way to combine the steps into 1 or even 2 steps?

Step 1 sums the two amounts needed for the '30' type records where position 39 is greater than 50. There are other '30' type records where position 39 is less than 50 that I do not want to touch.

//SORTIN DD DSN=file1in
//SORTOUT DD DSN=file1out
//SYSIN DD *
SORT FIELDS=(5,51,CH,A)
INCLUDE COND=(39,2,BI,GT,50,AND,54,2,CH,EQ,C'30')
SUM FIELDS=(105,6,PD,111,6,PD)

Step 2 eliminates all dupes of the '20' records where position 39 is greater than 50. There are other '20' type records where position 39 is less than 50 that I do not want to touch.

//SORTIN DD DSN=file1in
//SORTOUT DD DSN=file2out
//SYSIN DD *
SORT FIELDS=(5,51,CH,A)
INCLUDE COND=(39,2,BI,GT,50,AND,54,2,CH,EQ,C'20')
SUM FIELDS=NONE

Step 3 pulls off all records who did not get selected in either of the above two step position 39 is less than 50

//SORTIN DD DSN=file1in
//SORTOUT DD DSN=file3out
//SYSIN DD *
SORT FIELDS=COPY
INCLUDE COND=(39,2,BI,LT,50)


Step 4 combines and sorts the three datasets together

//SORTIN DD DSN=file1out
// DD DSN=file2out
// DD DSN=file3out
//SORTOUT DD DSN=file4out
//SYSIN DD *
SORT FIELDS=(5,32,CH,A,37,2,BI,A,39,2,BI,A,41,3,CH,A,
44,4,BI,A,48,4,BI,A,52,2,BI,A,54,2,CH,A)
RECORD TYPE=V,LENGTH=(500)
dgrimsley
 
Posts: 7
Joined: Fri Jun 25, 2010 1:10 am
Has thanked: 0 time
Been thanked: 0 time

Re: eliminate dupes and sum selected records

Postby Frank Yaeger » Fri Jul 23, 2010 9:17 pm

You need to tell us more about what you're trying to do exactly rather than the steps you used to do it.

Please show an example of the records in your input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field (remember that for a VB file, the RDW is in positions 1-4 and the data starts in position 5). Give the LRECL of the input file.

Also, run this job and show the //SYSOUT messages you receive, so I can see what level you're at:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
//SORTOUT DD DUMMY
//SYSIN    DD    *
    OPTION COPY
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: eliminate dupes and sum selected records

Postby dgrimsley » Sat Jul 24, 2010 12:02 am

By level do you mean V1R10?

Lrecl is 500

col 39 (35+4) is numeric and if it is greater and 50 and col 54 (50+4) is 30 then I want to summerize the two amounts in col 105 (101+4) this is 6 PD and sum 111 (107+4) this is 6 PD getting me down to one record for each set of (>50 and eq 30 records).

col 39 (35+4) is numeric and if it is greater and 50 and col 54 (50+4) is 20 (i only included 20 in the original steps), 40 or 50 then I want to eliminate all dupes and keep just 1 record for each set of (>50 and eq 20, >50 and eq 40, >50 and eq 50)

Examples below have 01 or 51 in col 39 and 10,20,30,40 or 50 in col 54. col 105 (101+4) = 1.22 and 2.22 col 111 (107+4) = 2.00 and 4.00.

000066851332010-07-092000038 01 1000006685133
000066851332010-07-092000038 01 2000006685133
000066851332010-07-092000038 01 3000006685133
000066851332010-07-092000038 01 3000006685133
000066851332010-07-092000038 01 4000006685133
000066851332010-07-092000038 01 5000006685133
000066851332010-07-092000038 51 2000006685133
000066851332010-07-092000038 51 2000006685133
000066851332010-07-092000038 51 3000006685133 1.22 2.00
000066851332010-07-092000038 51 3000006685133 2.22 4.00
000066851332010-07-092000038 51 4000006685133
000066851332010-07-092000038 51 4000006685133
000066851332010-07-092000038 51 5000006685133
000066851332010-07-092000038 51 5000006685133

Results needed: All 01 (col 39) records left unchanged. Combine the two 30 (col 54) records with 51 (col 39) and only 1 record each ot the 20, 40 & 50 (col 54) with 51 in col 39.

000066851332010-07-092000038 01 1000006685133
000066851332010-07-092000038 01 2000006685133
000066851332010-07-092000038 01 3000006685133
000066851332010-07-092000038 01 3000006685133
000066851332010-07-092000038 01 4000006685133
000066851332010-07-092000038 01 5000006685133
000066851332010-07-092000038 51 2000006685133
000066851332010-07-092000038 51 3000006685133 3.44 6.00
000066851332010-07-092000038 51 4000006685133
000066851332010-07-092000038 51 5000006685133
dgrimsley
 
Posts: 7
Joined: Fri Jun 25, 2010 1:10 am
Has thanked: 0 time
Been thanked: 0 time

Re: eliminate dupes and sum selected records

Postby Frank Yaeger » Sat Jul 24, 2010 1:01 am

By level do you mean V1R10?


No, I mean PTF level. Please run the job and show the //SYSOUT messages as requested.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: eliminate dupes and sum selected records

Postby dgrimsley » Sat Jul 24, 2010 1:24 am

Here is the SYSOUT:

1ICE143I 0 BLOCKSET COPY TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 15:51 ON FRI JUL 23, 2010 -
0 OPTION COPY
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K48846 C6-K90014 C7-K45047 C8-K46331 E9-K47759 C9-BASE E5-K48846 E7-K48846
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE088I 0 T#D1GX01.S1 . , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,6291456,6278238)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6200798,6200798)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=RC16 ,MSGDDN=SYSOUT
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=NO ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N ,ABCODE=MSG
ICE130I 0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2
ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE751I 1 EF-K49535 F0-K49038 E8-K48846
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 1, OUT: 1
ICE052I 0 END OF DFSORT
dgrimsley
 
Posts: 7
Joined: Fri Jun 25, 2010 1:10 am
Has thanked: 0 time
Been thanked: 0 time

Re: eliminate dupes and sum selected records

Postby Frank Yaeger » Sat Jul 24, 2010 2:39 am

I have created a 4 step process that works but I would like to know if there is a way to combine the steps into 1 or even 2 steps?


Well, you could combine them into one step using ICETOOL with four operators, but I suspect what you really want is to combine them into one pass over the input file and I don't see a way to do that.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post