MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Joining 2 dataset

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Thu May 31, 2007 6:42 am    Post subject: Joining 2 dataset Reply with quote
Hello Guys,

I have a small problem joining 2 dataset. Probably many have made a similar question but I don't find it... if it so..sorry!!

I want to "decode" a field od a dataset from an other, but I've some problem.

Input1:

Code:
AAA 111
BBB 222
CCC 333
DDD 111


Input 2

Code:
111 KKKK
222 ZZZZ
333 WWW


I would like to "decode" the seconf fiels of the first file with what I find in the second file. So my output should be:
Code:

AAA KKKK
BBB ZZZZ
CCC WWW
DDD KKKK


I thought I could work with SPLICE. Moreower there is a lack of PRT here that I can't use the JOINKEYS....

Can you give me some help?

Thank a lot!!
_________________
Maxisnowhere
Back to top
View user's profile Send private message
CICS Guy
Intermediate


Joined: 30 Apr 2007
Posts: 292
Topics: 3
PostPosted: Thu May 31, 2007 6:56 am    Post subject: Reply with quote
Quote:

Moreower there is a lack of PRT here that I can't use the JOINKEYS

Could you explain what you mean?
Back to top
View user's profile Send private message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Thu May 31, 2007 10:21 am    Post subject: Reply with quote
I mean that I tried to use a SORT with a JOINKEYS for the two files, but eather it didn't function because on our sxsten there are not installed all PTF nor i foud an explanation on how to use it Sad

Wish you a good one!!!!
_________________
Maxisnowhere
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Thu May 31, 2007 10:51 am    Post subject: Reply with quote
Maxisnowhere,

JOIN feature is introduced in Syncsort v 1.2. Check what version of syncsort you are using. If you are using Syncsort v 1.1 - then you can probably try with SPLICE, but please note that SPLICE is an undocumented feature in Syncsort.

Thanks,
Phantom
Back to top
View user's profile Send private message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Thu May 31, 2007 11:11 am    Post subject: Reply with quote
How do i know witch version I have? Sorry.....
_________________
Maxisnowhere
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Thu May 31, 2007 11:13 am    Post subject: Reply with quote
Just code a simple SORT job, execute it and look at your Sysout message - first line. It will show you the version

Code:

********************************* TOP OF DATA **********************************
 SYNCSORT FOR Z/OS  1.1DR   TPF3   U.S. PATENTS: 4210961, 5117495  (C) 2002 SYNC


My shop has Syncsort for Z/OS Version 1.1.

Thanks,
Phantom
Back to top
View user's profile Send private message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Thu May 31, 2007 11:16 am    Post subject: Reply with quote
I get somethink like:

Code:
CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 18:15 ON THU MAY 31, 2007

_________________
Maxisnowhere
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Thu May 31, 2007 11:19 am    Post subject: Reply with quote
JOIN function is a feature of Syncsort. You need to use SPLICE for DFSORT. There are hundreds of posts in this board to match 2 files using DFSORT SPLICE and the manual is also available for free - online.

Check this link first
http://www.mvsforums.com/helpboards/viewtopic.php?t=5399

Hope this helps,

Thanks,
Phantom
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Thu May 31, 2007 11:20 am    Post subject: Reply with quote
Quote:
I thought I could work with SPLICE


Max,

Yes, you can do it with SPLICE.

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed that your input files have RECFM=FB and LRECL=10, but the job can be changed appropriately for other attributes. I also assumed that you wanted the output sorted by the first field in input file1.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/10)
//IN2 DD DSN=...  input file2 (FB/10)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/10)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(11,3,ZD) -
  WITHALL WITH(1,3)
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(5:5,4,11:1,3)
/*
//CTL2CNTL DD *
  INREC BUILD=(1:1,3,11:5,3)
/*
//CTL3CNTL DD *
  INREC BUILD=(1,10)
  SORT FIELDS=(1,3,CH,A)
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Fri Jun 01, 2007 1:22 am    Post subject: Reply with quote
Thank you all guys!!!!

You are all wonderfull 8)
_________________
Maxisnowhere
Back to top
View user's profile Send private message
maxisnowhere
Beginner


Joined: 25 May 2005
Posts: 59
Topics: 20
PostPosted: Fri Jun 01, 2007 8:55 am    Post subject: Reply with quote
mmm guys is it not yet finisched...

some "keys" from input 1 have no "decode" in file 2...how can I do to do to put these on an other file?

For example I take these input:

input 1:

Code:
AAA 111   
BBB 222   
CCC 333   
DDD 111   
EEE 444   


input 2

Code:
111 KKKK 
222 ZZZZ 
333 WWW 


The ioutput I get is ok:

Code:
AAA KKKK
BBB ZZZZ
CCC WWW 
DDD KKKK


I just would like to get the record
Code:

EEE 444   


in an other file, because there is not the recort 444 QQQQ in the second file.

Sorry to continue bother you...

(I used Frank Yaeger's step)

Wish you a good week end!!
_________________
Maxisnowhere
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Fri Jun 01, 2007 9:20 am    Post subject: Reply with quote
maxisnowhere,

Just change the toolin (last 2 statements)

from

Code:

SPLICE FROM(T1) TO(T2) ON(11,3,ZD) -
  WITHALL WITH(1,3)
SORT FROM(T2) TO(OUT) USING(CTL3)


to

Code:

SPLICE FROM(T1) TO(T2) ON(11,3,ZD) -
  WITHALL WITH(1,3) KEEPNODUPS       
SORT FROM(T2) USING(CTL3)


and
Code:

//CTL3CNTL DD *
  INREC BUILD=(1,10)
  SORT FIELDS=(1,3,CH,A)
/*


to

Code:

//CTL3CNTL DD *                                 
  INREC BUILD=(1,10)
  SORT FIELDS=(1,3,CH,A)                       
  OUTFIL FNAMES=OUT1,INCLUDE=(5,4,CH,EQ,C' ')   
  OUTFIL FNAMES=OUT2,SAVE                       
/*                                 


Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Jun 01, 2007 12:35 pm    Post subject: Reply with quote
Actually, that would give:

EEEE

in OUT2 instead of:

EEEE 444

Here's a DFSORT/ICETOOL job that will handle the changed requirement and will also throw away a record in file2 without a match in file1 (e.g. 5555 SSS).

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/10)
//IN2 DD DSN=...  input file2 (FB/10)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT1 DD DSN=...  output file1 (FB/10)
//OUT2 DD DSN=...  output file2 (FB/10)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(11,3,ZD) -
  WITHALL WITH(1,3) WITH(14,1) KEEPNODUPS
SORT FROM(T2) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(5:5,4,11:1,3,14:C'BB')
/*
//CTL2CNTL DD *
  INREC BUILD=(1:1,7,11:5,3,14:C'VV')
/*
//CTL3CNTL DD *
  SORT FIELDS=(1,3,CH,A)
  OUTFIL FNAMES=OUT1,INCLUDE=(14,2,CH,EQ,C'VB'),
    BUILD=(1,10)
  OUTFIL FNAMES=OUT2,INCLUDE=(14,2,CH,EQ,C'VV'),
    BUILD=(1,10)
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group