Combine files as per matching key
Posted:
Thu Oct 20, 2011 5:10 pm
by diptisaini
Hi,
I have two files :
File1 :-
----+----1----+----2----+----3----+----
***************************** Top of Da
ACA0056 AU10010000880CLASEC
ACA0082 AU10010000880CLASEC
KOP0114 AU10010000830CLAOPR
WGS0202 AU10010000830CLASUP
KOP0140 AU10010000830CLAOPR
File 2 :-
----+----1----+----2----+----3----+----4----+
USERID: WGS0196 VIG, VIVEK WGS0BB
USERID: WGS0202 PRATHIGUDUPU, RAJ KI WGS0BB
USERID: WGS0207 TONG, WING YIN WGS0HK
Now my requirement is to combine these two files into a new file according to KEY that is userid E.g WGS02202. The matching record should be copy into a new file as shown below:-
Ouptput file:
WGS0202 AU10010000830CLASUP PRATHIGUDUPU, RAJ
and non-matching should be rejected.
Re: Combine files as per matching key
Posted:
Thu Oct 20, 2011 10:37 pm
by Frank Yaeger
You can use a DFSORT job like the following to do what you asked for. Adjust as needed.
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD *
ACA0056 AU10010000880CLASEC
ACA0082 AU10010000880CLASEC
KOP0114 AU10010000830CLAOPR
WGS0202 AU10010000830CLASUP
KOP0140 AU10010000830CLAOPR
//IN2 DD *
USERID: WGS0196 VIG, VIVEK WGS0BB
USERID: WGS0202 PRATHIGUDUPU, RAJ KI WGS0BB
USERID: WGS0207 TONG, WING YIN WGS0HK
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,7,A)
JOINKEYS F2=IN2,FIELDS=(9,7,A)
REFORMAT FIELDS=(F1:1,40,F2:18,18)
OPTION COPY
/*
If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:
http://www.ibm.com/support/docview.wss? ... g3T7000080
Re: Combine files as per matching key
Posted:
Fri Oct 21, 2011 11:07 am
by diptisaini
Hi,
I have tried the above JCL and also pasted below for your refernce :-
//WGS0227D JOB (ACCT),'AACA023',
// CLASS=Z,MSGCLASS=X,
// REGION=5000K,NOTIFY=&SYSUID
//BBOX EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=WGS0227.IMVS.CLA.DATA,DISP=SHR
//IN2 DD DSN=WGS0227.IMVS.CLA.DATA2,DISP=SHR
//SORTOUT DD DSN=WGS0227.IMVS.CLA.DATA1,DISP=SHR
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(1,7,A)
JOINKEYS F2=IN2,FIELDS=(9,7,A)
REFORMAT FIELDS=(F1:1,40,F2:18,18)
OPTION COPY
/*
But after submitting i am getting Maxcc=16. Please see below error message :-
SYNCSORT FOR Z/OS 1.3.2.2R U.S. PATENTS: 4210961, 5117495 (C) 2007 SYNCSO
JPMORGAN CHASE z/OS 1.12.0
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 9E772, MODEL 2097 707 LICEN
SYSIN :
JOINKEYS F1=IN1,FIELDS=(1,7,A)
*
JOINKEYS F2=IN2,FIELDS=(9,7,A)
*
REFORMAT FIELDS=(F1:1,40,F2:18,18)
OPTION COPY
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER268A JOINKEYS STATEMENT: SYNTAX ERROR
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVESo what should i do in this case? Thanks for the assistance.
Re: Combine files as per matching key
Posted:
Fri Oct 21, 2011 11:17 am
by diptisaini
Thanks, It's working fine now.
Re: Combine files as per matching key
Posted:
Fri Oct 21, 2011 11:50 am
by diptisaini
One more scenario forgot to mention :-
File1 :-
----+----1----+----2----+----3----+----
***************************** Top of Da
ACA0056 AU10010000880CLASEC
ACA0082 AU10010000880CLASEC
WGS0202 AU10010000830CLASUP
File 2 :-
----+----1----+----2----+----3----+----4----+
USERID: WGS0196 VIG, VIVEK WGS0BB
USERID: WGS0202 PRATHIGUDUPU, RAJ KI WGS0BB
Output file ;-
ACA0056 AU10010000880CLASEC
ACA0082 AU10010000880CLASEC
WGS0202 AU10010000830CLASUP PRATHIGUDUPU, RAJ
My output file should contain the all userid which is present in file1 even there is no corresponding userid is present on file 2. Could you please help me on that?
Re: Combine files as per matching key
Posted:
Fri Oct 21, 2011 2:22 pm
by BillyBoyo
You are using SYNCSORT, from your error messages.
If you can describe your current problem more exactly, maybe someone can step in and help, as Frank Yaeger works for IBM on DFSORT.
Re: Combine files as per matching key
Posted:
Fri Oct 21, 2011 2:45 pm
by NicC
If you are to copy all records regardless of whether the userid field is on the second file then is it not a staraight copy of the first file?
Re: Combine files as per matching key
Posted:
Tue Dec 06, 2011 3:30 am
by Alissa Margulies
Here is the proper syntax for SyncSort:
//STEP1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJFN1 DD DSN=WGS0227.IMVS.CLA.DATA,DISP=SHR
//SORTJNF2 DD DSN=WGS0227.IMVS.CLA.DATA2,DISP=SHR
//SORTOUT DD DSN=WGS0227.IMVS.CLA.DATA1,DISP=SHR
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,7,A)
JOINKEYS FILES=F2,FIELDS=(9,7,A)
REFORMAT FIELDS=(F1:1,40,F2:18,18)
SORT FIELDS=COPY
/*