I need to create a specific record/file based on 5 DB2 table files. Each file is 80 bytes FB and the key field is in different positions within each file.
Rather than use multiple JOINKEYS, since you can only compare/join 2 keys at a time, I decided to use SPLICE. However, I'm NOT getting the desired results.
I used INREC to move the key field to position 81 of each file and want to keep each field, from each file, when a match is found. I only get results where the key exists on each file, is there something wrong with my code? Or, would multiple JOINKEYS be better to accomplish this.
Joined: 15 Aug 2015 Posts: 1379 Location: Bamberg, Germany
I don't know what your output exactly should look like. I assume that you want to keep paired and unpaired keys. Have a glimpse at the sample if I got the request right.
Code:
//ICETOOL EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD *
111111 A
222222 A
333333 A
444444 A
/*
//FILE2 DD *
111111 B
222222 B
333333 B
444444 B
/*
//FILE3 DD *
111111 C
111111 C
111111 C
111111 C
111111 C
111111 C
222222 C
222222 C
333333 C
333333 C
333333 C
333333 C
333333 C
444444 C
444444 C
444444 C
/*
//FILE4 DD *
111111 D
333333 D
333333 D
333333 D
999999 D
/*
//FILE5 DD *
111111 E
222222 E
333333 E
333333 E
444444 E
444444 E
/*
//T1 DD DISP=(MOD,PASS),UNIT=SYSALLDA,SPACE=(CYL,(2,1))
//ALLRCDS DD SYSOUT=*
//TOOLIN DD *
COPY FROM(FILE1) TO(T1) USING(CTL1)
COPY FROM(FILE2) TO(T1) USING(CTL2)
COPY FROM(FILE3) TO(T1) USING(CTL3)
COPY FROM(FILE4) TO(T1) USING(CTL4)
COPY FROM(FILE5) TO(T1) USING(CTL5)
SPLICE FROM(T1) TO(ALLRCDS) ON(81,6,ZD) KEEPNODUPS WITHANY -
WITH(87,80) WITH(167,80) WITH(247,80) WITH(327,80) WITH(407,80) -
USING(CTL6)
/*
//CTL1CNTL DD *
INREC OVERLAY=(81:25,6,ZD,1,80,80X,80X,80X,80X)
/*
//CTL2CNTL DD *
INREC OVERLAY=(81:15,6,ZD,80X,1,80,80X,80X,80X)
/*
//CTL3CNTL DD *
INREC OVERLAY=(81:5,6,ZD,80X,80X,1,80,80X,80X)
/*
//CTL4CNTL DD *
INREC OVERLAY=(81:35,6,ZD,80X,80X,80X,1,80,80X)
/*
//CTL5CNTL DD *
INREC OVERLAY=(81:45,6,ZD,80X,80X,80X,80X,1,80)
/*
//CTL6CNTL DD *
OUTFIL BUILD=(87,400)
/*
I'm getting a WER027A error using Syncsort V3.1.6 (see below):
Code:
CTL6CNTL :
OUTFIL BUILD=(87,400)
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=DFSMSG,SORTIN=T1,SORTDD=CTL6,SORTOUT=ALLRCDS,D
NALLOC,CMP=CLC,NOVLSHRT,EQUALS,VLSCMP
SORT FIELDS=(00081,0006,ZD,A)
MODS E35=(SYNCT#35,4096,,N)
WER813I INSTALLATION OPTIONS IN MFXPRM00 WILL BE USED
WER428I CALLER-PROVIDED IDENTIFIER IS "0006"
WER580I AUTHORIZATION TURNED OFF DUE TO UNAUTHORIZED EXIT(S)
WER276B SYSDIAG= 9224778, 12859140, 12859140, 33231045
WER164B 6,996K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,100K BYTES USED
WER146B 12K BYTES OF EMERGENCY SPACE ALLOCATED
WER108I T1 : RECFM=FB ; LRECL= 80; BLKSIZE= 27920
WER073I T1 : DSNAME=SYS25072.T133800.RA000.W951AGMT.R0318074
WER027A CONTROL FIELD BEYOND RECORD
WER578I SORTL INSTRUCTION NOT USED; REASON CODE=FA
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
Joined: 15 Aug 2015 Posts: 1379 Location: Bamberg, Germany
Have you tested with my sample or with your data? Because the sample works here.
Code:
SYT020I SYNCSORT CALLED WITH IDENTIFIER "0006"
SYT031I NUMBER OF RECORDS PROCESSED: 000000000000035
SYT026I NUMBER OF SELECTED RECORDS: 000000000000005
SYT030I OPERATION COMPLETED WITH RETURN CODE 0
SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 0
SYNCSORT FOR Z/OS 3.1.0.0NI U.S. PATENTS: 4210961, 5117495
So, as a temp fix, while we research why the code isn't working. I replaced this line, OUTFIL BUILD=(87,400), with SORT FIELDS=COPY. And, the job ran successfully; however, I didn't get the output I expected, which is below:
Code:
111111 C 111111 B 111111 A 111111 D 111111 E
111111 C
111111 C
111111 C
111111 C
222222 C 222222 B 222222 A 222222 E
222222 C
333333 C 333333 B 333333 A 333333 D 333333 E
333333 C 333333 D 333333 E
333333 C 333333 D
333333 C
333333 C
444444 C 444444 B 444444 A 444444 E
444444 C 444444 E
444444 C 444444 E
We found the issue and now the code works....thanks Joerg.Findeisen!
However, I didn't get the desired output below:
Code:
111111 C 111111 B 111111 A 111111 D 111111 E
111111 C
111111 C
111111 C
111111 C
222222 C 222222 B 222222 A 222222 E
222222 C
333333 C 333333 B 333333 A 333333 D 333333 E
333333 C 333333 D 333333 E
333333 C 333333 D
333333 C
333333 C
444444 C 444444 B 444444 A 444444 E
444444 C 444444 E
444444 C 444444 E
