|
View previous topic :: View next topic
|
| Author |
Message |
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
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.
Any assistance would be greatly appreciated.
Thanks!
| Code: |
//STEP1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SPYSET DD SYSOUT=*
//FILE1 DD DISP=SHR,DSN=TABLE1
//FILE2 DD DISP=SHR,DSN=TABLE2
//FILE3 DD DISP=SHR,DSN=TABLE3
//FILE4 DD DISP=SHR,DSN=TABLE4
//FILE5 DD DISP=SHR,DSN=TABLE5
//T1 DD DSN=&&TX,UNIT=SYSDA,SPACE=(CYL,(5,5)),
// DISP=(MOD,PASS)
//ALLRCDS DD DSN=ALLRCDS,
// DISP=(,CATLG,DELETE),
// UNIT=DISK,DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
// SPACE=(CYL,(500,500),RLSE)
//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,PD) WITHEACH -
WITH(81,80) WITH(161,80) WITH(241,80) WITH(321,80) -
USING(CTL6)
/*
//CTL1CNTL DD *
INREC OVERLAY=(81:25,6,PD)
/*
//CTL2CNTL DD *
INREC OVERLAY=(81:15,6,PD)
/*
//CTL3CNTL DD *
INREC OVERLAY=(81:5,6,PD)
/*
//CTL4CNTL DD *
INREC OVERLAY=(81:35,6,PD)
/*
//CTL5CNTL DD *
INREC OVERLAY=(81:45,6,PD)
/*
//CTL6CNTL DD *
OUTFIL FNAMES=ALLRCDS,OUTREC=(1,400)
/*
|
Input Table 1:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
111111
222222
333333
444444
|
Input Table 2:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
111111
222222
333333
444444
|
Input Table 3:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
111111
111111
111111
111111
111111
111111
222222
222222
333333
333333
333333
333333
333333
444444
444444
444444
|
Input Table 4:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
111111
333333
333333
333333
|
Input Table 5:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
111111
222222
333333
333333
444444
444444
|
|
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1401
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)
/* |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2230
Location: USA
|
|
|
|
| chillmo wrote: |
| I need to create a specific record/file based on 5 DB2 table files. |
Please clarify: what are those "DB2 table files"?
I've never heard of those.
Your example deals with regular flat physically sequential files/datasets; no DB2 anywhere. |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Joerg.Findeisen,
Yes, I want to keep the paired records.
I'll try the solution and let you know by next week.
Sergeyken,
Yes, I unloaded DB2 tables into flat files. Sorry for the confusion. |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Joerg.Findeisen,
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 |
I'm going to debug this and get back to everyone. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1401
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 |
|
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Joerg.Findeisen,
I tested with your sample; but I we're running different versions of Syncsort. You're running version 3.1.0 and I'm running version 3.1.6.
I'll reach out to my system admin on Monday.
Have a great weekend. |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
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 |
|
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
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 |
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
