Combine files as per matching key



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Combine files as per matching key

Postby diptisaini » Thu Oct 20, 2011 5:10 pm

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.
diptisaini
 
Posts: 90
Joined: Sun Mar 14, 2010 5:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combine files as per matching key

 

Re: Combine files as per matching key

Postby Frank Yaeger » Thu Oct 20, 2011 10:37 pm

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
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Combine files as per matching key

Postby diptisaini » Fri Oct 21, 2011 11:07 am

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.
diptisaini
 
Posts: 90
Joined: Sun Mar 14, 2010 5:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combine files as per matching key

Postby diptisaini » Fri Oct 21, 2011 11:17 am

Thanks, It's working fine now.
diptisaini
 
Posts: 90
Joined: Sun Mar 14, 2010 5:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combine files as per matching key

Postby diptisaini » Fri Oct 21, 2011 11:50 am

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?
diptisaini
 
Posts: 90
Joined: Sun Mar 14, 2010 5:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Combine files as per matching key

Postby BillyBoyo » Fri Oct 21, 2011 2:22 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Combine files as per matching key

Postby NicC » Fri Oct 21, 2011 2:45 pm

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?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Combine files as per matching key

Postby Alissa Margulies » Tue Dec 06, 2011 3:30 am

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
/*
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 1 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post