I have 2 files and file1 has 2 fields in it.
Structure of File 1
1) 15 char ID
2) 10 char source name
Structure of File 2
1) 15 char ID
2) 10 char source name
3) 40 char long Address
Requirement :
Find a match of 1st field of File 1 against File 2, if a match is found then update the 2nd field of file 1 into the 2nd field of File 2.
If a match is not found then add the record from File 1 into File 2.
Request you to suggest me if this can be done by SORT.
I tried the below code
But not sure how to frame the condition because as per my requirement I need to compare the value of File1 against File 2 and then update or insert the record.
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
When posting a problem like this, it is best to post all of the JCL and control statements used in the problem run, along with all of the generated diagnostics.
IEF403I QQSAM2S1 - STARTED - TIME=10.44.04
+ICE143I 0 QQSAM2S1.STEP1 BLOCKSET COPY TECHNIQUE SELECTED
+ICE250I 0 QQSAM2S1.STEP1 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT
+ICE000I 1 QQSAM2S1.STEP1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R1
+ICE411I 0 QQSAM2S1.STEP1 THIS IS THE JOINKEYS MAIN TASK FOR JOINING F1 AND F
+ICE416I 0 QQSAM2S1.STEP1 JOINKEYS IS USING THE F1 SUBTASK FOR IN01 - SEE
+ICE416I 1 QQSAM2S1.STEP1 JOINKEYS IS USING THE F2 SUBTASK FOR IN02 - SEE
+ICE419I 0 QQSAM2S1.STEP1 JOINED RECORDS: TYPE=F, LENGTH=160
+ICE201I G QQSAM2S1.STEP1 RECORD TYPE IS F - DATA STARTS IN POSITION 1
+ICE751I 0 QQSAM2S1.STEP1 C5-K51707 C6-K51707 C7-K54603 C8-K51707 E9-K51707 C
+ICE193I 0 QQSAM2S1.STEP1 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 EN
+ICE089I 0 QQSAM2S1.STEP1 QQSAM2S1.STEP1 . , INPUT LRECL = 160, TYPE
+ICE093I 0 QQSAM2S1.STEP1 MAIN STORAGE = (MAX,16777216,16777216)
+ICE156I 0 QQSAM2S1.STEP1 MAIN STORAGE ABOVE 16MB = (16719856,16719856)
+ICE235I 0 QQSAM2S1.STEP1 OPTIONS: NULLOUT=RC0
+ICE231I 0 QQSAM2S1.STEP1 STORAGE USED FOR OUTFIL : BELOW 16M = 20480, ABOVE
+ICE210I 0 QQSAM2S1.STEP1 IN02 : EXCP USED, LRECL = 80, BLKSIZE = 800, TY
+ICE751I 1 QQSAM2S1.STEP1 EF-K49535 CB-K90014 F0-K49038 E8-K51707
+ICE418A 3 QQSAM2S1.STEP1 JOINKEYS F1 SUBTASK FOR IN01 TERMINATED - SEE J
+ICE052I 0 QQSAM2S1.STEP1 END OF DFSORT
IEA995I SYMPTOM DUMP OUTPUT 948
USER COMPLETION CODE=0016
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
It is not so good to actually try to update one of your input files, which you are doing with the IN02.
You don't have much code in there to carry out your requirement.
For the particular error, it is telling you to look at the message dataset from one of the JOINKEY subtasks. You'll need a couple of DDs for those, pointed to SYSOUT is convenient.
QQSAM2S1 ENDED. DESCRIP=AUTOMATION ; CPU TIME= .00 MINS; WALL TIM
JOB/QQSAM2S1/START 2012213.1044
JOB/QQSAM2S1/STOP 2012213.1044 CPU 0MIN 00.08SEC SRB 0MIN 00.00SEC
0 THIS IS THE JOINKEYS F1 SUBTASK FOR IN01
0 BLOCKSET COPY TECHNIQUE SELECTED
0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
0 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 10:44 ON TUE JUL 31, 20
MERGE FORMAT=BI,FIELDS=(1,15,A)
RECORD TYPE=F
DEBUG NOABEND,ESTAE
OPTION EQUALS,MSGPRT=ALL,LIST,NOCHECK,RESINV=0,DYNALLOC,SORTDD=JNF1,MSG*
DDN=JNF1JMSG,SORTIN=IN01,COPY
G RECORD TYPE IS F - DATA STARTS IN POSITION 1
0 C5-K51707 C6-K51707 C7-K54603 C8-K51707 E9-K51707 C9-BASE E5-K51707 E7-K5170
0 ICEAM2 INVOCATION ENVIRONMENT IN EFFECT - ICEAM2 ENVIRONMENT SELECTED
0 QQSAM2S1.STEP1 . , INPUT LRECL = 80, BLKSIZE = 800, TYPE = FB
MAIN STORAGE = (MAX,6291456,6291456)
MAIN STORAGE ABOVE 16MB = (6234096,6234096)
OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,
OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=RC16 ,
OPTIONS: VIO=N,RESDNT=ALL ,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALO
OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXI
OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=16384,CINV=Y,CFW=Y,DSA=0
OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARM
OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSI
OPTIONS: NULLOUT=RC0
EXCP ACCESS METHOD USED FOR IN01
EF-K49535 F0-K49038 E8-K51707
IN01 (F1) KEY IS OUT OF SEQUENCE
END OF DFSORT
THIS IS THE JOINKEYS F2 SUBTASK FOR IN02
BLOCKSET COPY TECHNIQUE SELECTED
VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
- CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 10:44 ON TUE JUL 31, 20
MERGE FORMAT=BI,FIELDS=(1,15,A)
RECORD TYPE=F
DEBUG NOABEND,ESTAE
OPTION EQUALS,MSGPRT=ALL,LIST,NOCHECK,RESINV=0,DYNALLOC,SORTDD=JNF2,MSG*
DDN=JNF2JMSG,SORTIN=IN02,COPY
RECORD TYPE IS F - DATA STARTS IN POSITION 1
C5-K51707 C6-K51707 C7-K54603 C8-K51707 E9-K51707 C9-BASE E5-K51707 E7-K5170
ICEAM2 INVOCATION ENVIRONMENT IN EFFECT - ICEAM2 ENVIRONMENT SELECTED
QQSAM2S1.STEP1 . , INPUT LRECL = 80, BLKSIZE = 800, TYPE = FB
MAIN STORAGE = (MAX,6291456,6291456)
MAIN STORAGE ABOVE 16MB = (6234096,6234096)
OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,
OPTIONS: SIZE=6291456,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=RC16 ,
OPTIONS: VIO=N,RESDNT=ALL ,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALO
OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXI
OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=16384,CINV=Y,CFW=Y,DSA=0
OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE ,EXITCK=S,PARM
OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSI
OPTIONS: NULLOUT=RC0
EXCP ACCESS METHOD USED FOR IN02
EF-K49535 F0-K49038 E8-K51707
OUTPUT LRECL = 80, TYPE = F
INSERT 0, DELETE 0
RECORDS - IN: 1, OUT: 0
END OF DFSORT
Specifically the position 161 which is highlighted.
Both my input files length are 80.
I modified the card and ran it with 81 and it ran fine. But now I am observing that it is neither updating nor adding a new record, rather just copying the file 2 into the output
Please find my both input files , job and the output below
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Did you try Kolusu's code, or just assume that you know better?
Please try Kolusu's code. If it didn't work with the information you had provided, how do you think Kolusu managed to show you sample output?
Your reformat record is 80+80+1. The 1 being the indicator as to the result of the join. If you change the 161 (used to get at the 1) to 81, you'll just be looking at the first byte of your second record, which is very unlikely to be any use for your task.
Could you elaborate?
My Reformat field instructions say that the length of file1 and file2 are 80
If I change my reformat instruction, will that give me the desired output?
If I consider what you have said is correct and run my job with IFTHEN having position 161 then my job abends saying maximum length reached.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Skss,
Post your complete JCL, control cards and sysout from the step which is "abending", please.
Your reformat record, again, is made of 80 bytes from F1, 80 bytes from F2 and one byte from the indicator of the result of the join. B = matched, 1 = F1 only, 2 = F2 only are the possible values, but what may be presented in particular step depends on the JOIN supplied (or defaulted to).
EDIT: After looking at Kolusu's code provided, reverse the F2 and F1 in the above.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Skss,
You weren't accurate in copying the work that Kolusu did for you. You then led me astray :-)
If you look at Kolusu's code, he has F2,1,80,F1,1,80,?
You put F1,1,80,F2,1,80,?
But didn't amend your code accordingly. Either correct the rest of your code so that it matches the REFORMAT record that you have created, or go back to Kolusu's REFORMAT record. Either method should work for you.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Skss wrote:
Thanks a lot for your help Bill and Skolusu!!
The code worked
Thanks for the feedback.
We knew it would. Be careful next time when taking code: copy/paste and it stays the same, re-tryping is up to finger-brain-eye coordination, and sometimes fails for any - which is why we copy/paste.