Page 1 of 1

Combine files as per matching key

PostPosted: 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

PostPosted: 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

PostPosted: 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 ACTIVE
So what should i do in this case? Thanks for the assistance.

Re: Combine files as per matching key

PostPosted: Fri Oct 21, 2011 11:17 am
by diptisaini
Thanks, It's working fine now.

Re: Combine files as per matching key

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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
/*