View previous topic :: View next topic
Author
Message
kalyan.v New User Joined: 04 Feb 2008Posts: 65 Location: Hyd
Hi All,
Here i am trying to join two files using below sort card.
As per my requirement i should get all records from file2.
If file1 key = file2 key then i should get file2 rec + some file1 fields.
If file1 key not = file2 key then i should get just file2 record.
In my case i am getting all required fields from file1 and file2 but
i am not getting all file2 records.
Sort Card:
Code:
//IN1 DD DSN=file1,DISP=SHR ---LRECL=859
//IN2 DD DSN=file2,DISP=SHR ---LRECL=94
//T1 DD DSN=&&T1,
// DISP=(MOD,PASS),
// UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),
// DCB=(RECFM=FB)
//OUT DD DSN=output file,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSID,SPACE=(CYL,(50,50),RLSE)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,6,CH) WITHALL WITH(1,94)
/*
//CTL1CNTL DD *
INREC BUILD=(1:1,6,7:88X,95:7,35,207,1)
/*
//CTL2CNTL DD *
INREC BUILD=(1:1,94,95:36X)
/*
//SYSOUT DD SYSOUT=*
Please let me know the modifications, the above sort card needed.
Thanks,
Kalyan V
Back to top
Frank Yaeger DFSORT Developer Joined: 15 Feb 2005Posts: 7129 Location: San Jose, CA
Showing a job that doesn't do what you want really doesn't help anyone help you.
Please show an example of the records in each 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. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.
Back to top
kalyan.v New User Joined: 04 Feb 2008Posts: 65 Location: Hyd
Hi,
Please find the example here.
FILE-1 fields:LRECL=859
Code:
1(6) 7(35) 201(1)
001122 PIGGOTT HOSPITAL 3
004017 FT. SMITH RIM & BOW CO. 6
004951 BANK OF LAKE VILLAGE 5
005143 MEDICAL CTR OF CALICO ROCK 7
005547 RANDOLPH CO CLERKS OFFICE 1
008034 1ST PARAGOULD BANKSHARE 2
008877 SEAARK MARINE, INC.
009935 WARREN BANK AND TRUST CO. 2
FILE2 FIELDS:: LRECL=94
Code:
1(6) - Key
000384990086870 02NADINE FARISH ---------XXXXXX
000384990045444 02KATHRYN PURCELL ---------XXXXXX
000384980142903 01CLANCI TAYLOR ---------XXXXXX
000702V89564 01VICTOR SMITH JR ---------XXXXXX
001001960262464 01MICHIAL HALTOM ---------XXXXXX
001122980008456 03KRISTI LAMBERT ---------XXXXXX
001122U66812 02REGINA MAURICIO ---------XXXXXX
001122980381543 03VANESSA FULLER ---------XXXXXX
001122980019691 01ALICE BRADSHAW ---------XXXXXX
001122980019691 01ALICE BRADSHAW ---------XXXXXX
001122980008456 01KELLY LAMBERT ---------XXXXXX
001122980349736 01CHARLES LAMBORN ---------XXXXXX
001122980008456 01KELLY LAMBERT ---------XXXXXX
001122980397464 01BRITTNEY BELCHER ---------XXXXXX
001122980019691 01ALICE BRADSHAW ---------XXXXXX
001122L90442 01TANIYA CARPENTER ---------XXXXXX
001122980019691 01ALICE BRADSHAW ---------XXXXXX
OUTPUT FILE:
Code:
1(6) – Key 94:95(36)
000384990086870 02NADINE FARISH ---------XXXXXX
000384990045444 02KATHRYN PURCELL ---------XXXXXX
000384980142903 01CLANCI TAYLOR ---------XXXXXX
000702V89564 01VICTOR SMITH JR ---------XXXXXX
001001960262464 01MICHIAL HALTOM ---------XXXXXX
001122980008456 03KRISTI LAMBERT ---------XXXXXX PIGGOTT HOSPITAL 3
001122U66812 02REGINA MAURICIO ---------XXXXXX PIGGOTT HOSPITAL 3
001122980381543 03VANESSA FULLER ---------XXXXXX PIGGOTT HOSPITAL 3
001122980019691 01ALICE BRADSHAW ---------XXXXXX PIGGOTT HOSPITAL 3
001122980019691 01ALICE BRADSHAW ---------XXXXXX PIGGOTT HOSPITAL 3
001122980008456 01KELLY LAMBERT ---------XXXXXX PIGGOTT HOSPITAL 3
001122980349736 01CHARLES LAMBORN ---------XXXXXX PIGGOTT HOSPITAL 3
001122980008456 01KELLY LAMBERT ---------XXXXXX PIGGOTT HOSPITAL 3
001122980397464 01BRITTNEY BELCHER ---------XXXXXX PIGGOTT HOSPITAL 3
001122980019691 01ALICE BRADSHAW ---------XXXXXX PIGGOTT HOSPITAL 3
001122L90442 01TANIYA CARPENTER ---------XXXXXX PIGGOTT HOSPITAL 3
001122980019691 01ALICE BRADSHAW ---------XXXXXX PIGGOTT HOSPITAL 3
The above is my required output....
Thanks,
Kalyan V
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
use the following DFSORT/ICETOOL JCL which will give you the desired results
Code:
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FB895 DD DSN=Your FB 895 byte file,DISP=SHR
//FB094 DD DSN=Your FB 094 byte file,DISP=SHR
//TEMP DD DSN=&&TEMP,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(FB895) USING(CTL1)
COPY FROM(FB094) USING(CTL2)
SORT FROM(TEMP) USING(CTL3)
//CTL1CNTL DD *
OUTFIL FNAMES=TEMP,BUILD=(1,6,95:7,35,201,1,C'1')
//CTL2CNTL DD *
OUTFIL FNAMES=TEMP,OVERLAY=(95:36X,C'2')
//CTL3CNTL DD *
SORT FIELDS=(1,6,CH,A),EQUALS
OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(133:SEQNUM,8,ZD,RESTART=(1,6))),
IFTHEN=(WHEN=GROUP,BEGIN=(133,8,ZD,EQ,1),PUSH=(95:95,36,132:131,1))
OUTFIL FNAMES=OUT,INCLUDE=(131,2,ZD,GE,21),BUILD=(1,130)
//*
Back to top
Please enable JavaScript!