Merging matching records from two files



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

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 7:27 pm

I tried searching for ICE201I,
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1

As it doesn't make much sense, so I putting the entire thing. Please find below,


ICE805I 1 JOBNAME: TESTJOB , STEPNAME: ICE1
ICE802I 0 BLOCKSET TECHNIQUE IN CONTROL
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE858I 0 LA=262024, DA=6144, AA=255880, BA=57, CP=0, TA=105
ICE859I 0 LB=8024, DB=1000, AB=7024, BB=0, CP=10, RS=16, TB=46
ICE860I 0 F=YN, P=2, M=N, B=2048
ICE751I 0 C5-K90025 C6-K90025 C7-K54603 C8-K62201 E9-K60823 C9-BASE E5-K62201 E6-K51707 E7-K62201
ICE143I 0 BLOCKSET SORT 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 - 13:47 ON MON JAN 02, 2012 -
OPTION EQUALS
INREC OVERLAY=(81:1,2,21,3)
SORT FIELDS=(81,5,CH,A)
OUTREC OVERLAY=(86:SEQNUM,5,ZD,RESTART=(81,5))
OUTFIL IFTHEN=(WHEN=(86,5,ZD,EQ,1),
BUILD=(C'./ ADD NAME=',81,5,/1,80)),
IFTHEN=(WHEN=NONE,
BUILD=(1,80))
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE858I 0 LA=262024, DA=6144, AA=255880, BA=57, CP=0, TA=105
ICE859I 0 LB=8024, DB=1000, AB=7024, BB=0, CP=10, RS=16, TB=46
ICE860I 0 F=YN, P=2, M=N, B=2048
ICE751I 0 C5-K90025 C6-K90025 C7-K54603 C8-K62201 E9-K60823 C9-BASE E5-K62201 E6-K51707 E7-K62201
ICE142I 0 SRTMSGE NOT FOUND - SYSOUT USED
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE088I 1 NW55TEST.ICE1 . , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,6291456,6266880)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6209520,6209520)
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=70
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=Y,LIST=Y,ERET=RC16 ,MSGDDN=SYSOUT
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=(TEMP ,006),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=Y,TEXIT=Y,LISTX=N,EFS=NONE ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=60 ,ODMAXBF=2097152,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE750I 0 DC 500000 TC 0 CS DSVNN KSZ 9 VSZ 9
ICE887I 0 CSES 0,0,0 ES 0,0,0
ICE886I 0 SYS 0 TSTG 0 FS 0 INIT 0 MAX 0 LEN 0
ICE752I 0 FSZ=6250 RC IGN=0 E AVG=92 0 WSP=747 C DYN=0 0
ICE915I 0 MOFSZ=1,MOSZ=0,MOSYS=2048(2),MOSTG=5777,MEML=2048(1)
ICE916I 0 MOFR=0402,MOVR=VV
ICE996I 0 ESM=2052864,ESO=2052864,ESR=0,ESP=4096,ESS=16384,CES=2052864,HSZ=524288
ICE997I 0 HWSP=196,HMAX=1479079,HES=1479079,ASV=1479079,EQ=I1,HN=1
ICE898I 0 OMAX=884237,NMAX=1479079,ENQT=1479079,CMAX=262144,HU=99,BUN=61408,MD=H ,DU=0,DR=10000,HN=1
ICE880I 0 QP=1 QA=1 HI=362 LI=362 MI=362 TZ=423 N1=196 N2=196 SZ=65 HN=1
ICE889I 0 CT=MAX , SB=3, L=0, D=0000, CCW=1MAM
ICE902I 0 O I AZ00
ICE231I 0 STORAGE USED FOR OUTFIL : BELOW 16M = 47104, ABOVE 16M = 107520
ICE855I 0 SORTOUT : TX=N, R=J, L=J, B= , BL=3, BR=2, DCT=200, VS=N, RU=X, SB=4
ICE210I 0 SORTOUT : BSAM USED, LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE751I 1 DE-K61785 D5-K62201 D9-K61785 CB-K90014 E8-K61438
ICE900I 0 CON=1,MUV=0,VOL=1,ENU=0,SBK=0,SRC=0,VEM=0
ICE999I 0 PWK=0 PSP=0 SWK=0 SSP=0 TWK=0 TSP=0 RWK=0 RSP=0 AWK=0 AWP=0
ICE080I 0 IN MAIN STORAGE SORT
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 16, OUT: 16
ICE227I 0 SORTOUT : DELETED = 0, REPORT = 0, DATA = 19
ICE228I 0 SORTOUT : TOTAL IN = 16, TOTAL OUT = 19
ICE134I 0 NUMBER OF BYTES SORTED: 1280
ICE253I 0 RECORDS SORTED - PROCESSED: 16, EXPECTED: 6250
ICE165I 0 TOTAL WORK DATA SET TRACKS ALLOCATED: 0 , TRACKS USED: 0
ICE199I 0 MEMORY OBJECT STORAGE USED = 0M BYTES
ICE180I 0 HIPERSPACE STORAGE USED = 0K BYTES
ICE188I 0 DATA SPACE STORAGE USED = 0K BYTES
ICE891I 1 6257280 WMAIN, 9600 CMAIN, MAX CALLOC, N SCN, B BA, 0 AZ, 0 BZ, NN QC, 0 CZ, 0 DZ, 1 PLE
ICE892I 1 80 RIN 80 BLI 0 BLO 92 RUN 61408 BUN 2097 CPU 00 CVC
ICE893I 1 200 XIN 23 WIN 15440 GIN NDEN PFP B60 CM000 CIX UPTS LMD VS RUX
ICE894I 0 1 STR 0 MOR 0 IPB 0 OPB 0 CYL I MN
ICE881I 0 EQ=I1 DX=0 D2=0 D3=1 D4=0 AS=0 SA=0 SB=0 SC=0 HN=1
ICE885I 0 DAT 00 DSR 0304 BINS 0 BSZ 0 RCP 0 AJC 0 RLC 0 DUNIT 0
ICE895I 0 120 MUNIT 42 SUNIT 31 OUNIT
ICE896I 0 0 SET 0 DEXTOT 0 BLK 6250 CSZ 0 WE
ICE804I 1 SORTWK06 EXCP COUNT: 0
ICE804I 1 SORTWK05 EXCP COUNT: 0
ICE804I 1 SORTWK04 EXCP COUNT: 0
ICE804I 1 SORTWK03 EXCP COUNT: 0
ICE804I 1 SORTWK02 EXCP COUNT: 0
ICE804I 1 SORTWK01 EXCP COUNT: 0
ICE899I 0 HSR=0,HSW=0,HRE=0,HWE=0,HRP=0,HWP=0,HWM=0,HNM=1
ICE052I 0 END OF DFSORT
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Re: Merging matching records from two files

 

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 7:30 pm

One wayI found I can get output is writing an extra step with just keyfields match condition, and then adding Enrico solution in next Step. But I am not suppose to use 2 steps. Has to be done in 1 step.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 7:37 pm

Hi Enrico,

Is it possible to embedded this code with ICETOOL or SORT with your solution(Enrico), to have one step. I am trying this but still couldn't. Just a snap view.


//SRTWKLY1 EXEC PGM=SORT
//SORTJNF1 DD DISP=SHR,
//SORTJNF2 DD DISP=SHR,DSN=Input1
//SORTOUT DD DISP=SHR,DSN=Input2
//SYSIN DD *
OPTION COPY
JOINKEYS FILE=F1,FIELDS=(key position)
JOINKEYS FILE=F2,FIELDS=(key position)
REFORMAT FIELDS=(.......)
//SYSPRINT DD SYSOUT=*
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby BillyBoyo » Mon Jan 02, 2012 7:39 pm

The H in the ICE201I means you are up-to-date.Refer to this for explanation: http://ibmmainframes.com/about33389.html

If you can post the two-step code that you have, we may be able to suggest ways to get it into one step.

Ah. You've already done part of it. I assume you Input1 is actually on SORTJNF1 and input2 on SORTJNF2?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 7:43 pm

BillyBoyo wrote:The H in the ICE201I means you are up-to-date.Refer to this for explanation: http://ibmmainframes.com/about33389.html

If you can post the two-step code that you have, we may be able to suggest ways to get it into one step.

Ah. You've already done part of it. I assume you Input1 is actually on SORTJNF1 and input2 on SORTJNF2?



Thanks BillyBoyo. Yes you are right, Input1 is actually on SORTJNF1 and input2 on SORTJNF2. But has to be done in one step.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 7:44 pm

Efficient soln is required now.[:)]
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby enrico-sorichetti » Mon Jan 02, 2012 7:49 pm

Your soln. looks good.


my snippet was a solution for a slightly different requirement ,
just a POF ( proof of concept ) of generating a stream with the ./ ADD stuff in front of each group of records with the same <key>
( without any selection/match concern )

I asked what about ignoring the file1 stuff, and the snippet was the solution for the just in case
reread my replies !
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 8:11 pm

Hi Errico,

I didn't get what you are just said.
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby zhinghur » Mon Jan 02, 2012 8:15 pm

This is working, I ran just now. But need in 1 step.



//testjob JOB 1,'test',MSGCLASS=Y,CLASS=5,REGION=7M,
// NOTIFY=&SYSUID
//SRTW EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
./ ADD NAME=AB123
./ ADD NAME=BN132
./ ADD NAME=BC234
./ ADD NAME=AB125
./ ADD NAME=MS345
//SORTJNF2 DD *
AB000000 07 0000001 123
AB000000 07 0000002 123
AB000000 07 0000003 123
BC000000 07 0000003 130
BC000000 08 0000001 234
BC000000 08 0000002 234
BC000000 08 0000003 234
BC000000 08 0000004 234
AB000000 09 0000001 125
MS000000 09 0000001 345
MS000000 09 0000002 345
MS000000 09 0000003 345
AB000000 07 X000002 123
AB000000 07 X000003 123
MS000000 09 0000004 345
MS000000 09 0000005 345
MS000000 09 0000006 345
MS000000 09 0000007 345
//SORTOUT DD DISP=SHR,DSN=output.file
//SYSIN DD *
OPTION COPY
JOINKEYS FILE=F1,FIELDS=(13,2,A,15,3,A)
JOINKEYS FILE=F2,FIELDS=(1,2,A,21,3,A)
REFORMAT FIELDS=(F2:1,80)
/*
//ICE1 EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SORTIN DD DSN=output.file,DISP=SHR
//SORTOUT DD SYSOUT=*,DCB=(RECFM=FB,LRECL=80)
//SYSIN DD *
OPTION EQUALS
INREC OVERLAY=(81:1,2,21,3)
SORT FIELDS=(81,5,CH,A)
OUTREC OVERLAY=(86:SEQNUM,5,ZD,RESTART=(81,5))
OUTFIL IFTHEN=(WHEN=(86,5,ZD,EQ,1),
BUILD=(C'./ ADD NAME=',81,5,/1,80)),
IFTHEN=(WHEN=NONE,
BUILD=(1,80))
/*
zhinghur
 
Posts: 48
Joined: Sun Nov 20, 2011 12:25 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Merging matching records from two files

Postby enrico-sorichetti » Mon Jan 02, 2012 8:48 pm

quoting the question that produced the snippet I posted
keep following on...
wouldn' t it be faster to get rid of file1
and build from scratch a ./ ADD NAME=XXYYY whenever the <key changes> on file 2
?
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2643
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post