Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

need help with join

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
rohanthengal

Active User


Joined: 19 Mar 2009
Posts: 176
Location: Pune, Maharashtra, India

PostPosted: Wed Nov 02, 2011 10:08 pm    Post subject: need help with join
Reply with quote

hi team,
i need your help in icetool splice.

INPUT FILE 1:
Code:

ACCOUNT1  10000
ACCOUNT2  10001
ACCOUNT3  10002
ACCOUNT4  10003


INPUT FILE 2:
Code:

10000 ALLEN  CUST10000A
10000 ALEX    CUST10000B
10001 JISHO  CUST10001A
10001 FRANK CUST10001B
10001 KENRE CUST10001C
10002 KEITH  CUST10002A
10002 LEMO   CUST10002B
10004 KRISH  CUST10004A
10004 AMRO   CUST10004B


here account number(key) e.g. 10001 in input file 1 should be matched with account number in input file 2, and corresponding every matched record from input file 2 should be written to the output file by setting an end flag to Y -- as shown below --

Code:

10000 ALLEN  CUST10000A    Y
10000 ALEX    CUST10000B    Y
10001 JISHO  CUST10001A    Y
10001 FRANK CUST10001B    Y
10001 KENRE CUST10001C    Y
10002 KEITH  CUST10002A    Y
10002 LEMO   CUST10002B    Y


I have written a icetool splice control card which gives me output:
Code:
10000 ALLEN  CUST10000A    Y
10001 JISHO  CUST10001A    Y
10002 LEMO   CUST10002B    Y

i am getting only first occurrence from input file 2, under that particular account number !! Please suggest me SPLICE statement with right option
Back to top
View user's profile Send private message

sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Nov 02, 2011 10:28 pm    Post subject:
Reply with quote

rohanthengal,
What is LRECL/RECFM for both the input files? Why not JOINKEYS, is that not supported in currect DFSort function level?

Is your input file1, unique at account number?

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Nov 02, 2011 10:57 pm    Post subject:
Reply with quote

rohanthengal,

You could do this with SPLICE, but it's much easier with JOINKEYS. You could use a DFSORT JOINKEYS job like the following. I assumed input file2 has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB)
//IN2 DD DSN=...  input file2 (FB/80)
//SORTOUT DD DSN=...  output file (FB/80)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(11,5,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(1,5,A),SORTED
  REFORMAT FIELDS=(F2:1,80)
  OPTION COPY
  INREC OVERLAY=(28:C'Y')
/*
Back to top
View user's profile Send private message
rohanthengal

Active User


Joined: 19 Mar 2009
Posts: 176
Location: Pune, Maharashtra, India

PostPosted: Thu Nov 03, 2011 10:14 am    Post subject:
Reply with quote

thanks a lot FRrank , its working...
Now i want to include those records from IN2 which did not satisfy the matching criteria...for whom the indicator 'Y' will not be set
so the output will look like this:
10000 ALLEN CUST10000A Y
10000 ALEX CUST10000B Y
10001 JISHO CUST10001A Y
10001 FRANK CUST10001B Y
10001 KENRE CUST10001C Y
10002 KEITH CUST10002A Y
10002 LEMO CUST10002B Y
10004 KRISH CUST10004A
10004 AMRO CUST10004B
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 03, 2011 10:29 am    Post subject: Reply to: need help in splice !
Reply with quote

Hello,

How many more "requirements" are to be added . . . icon_sad.gif

It is rather inconsiderate to ask people to keep providing more answers when you are not willing to get all of the info before posting. You have been here long wnough to know better. . .

If the client/user is so undecided, you need to get to where you are self-sufficient and not need someone else to provide the answers.

d
Back to top
View user's profile Send private message
rohanthengal

Active User


Joined: 19 Mar 2009
Posts: 176
Location: Pune, Maharashtra, India

PostPosted: Thu Nov 03, 2011 11:01 am    Post subject:
Reply with quote

you are absolutely right Dick..!! Today i learned an awesome utility of ICEMAN...

Thanks a lot... i always try on my own and in case if i dont get anything proper then only i ask in our blog...!! Requirements are highly volatile.. icon_redface.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 03, 2011 8:08 pm    Post subject: Reply to: need help in splice !
Reply with quote

Hello,

Quote:
Requirements are highly volatile..
Yup, quite often. It is not always easy to get the users trained . . . icon_wink.gif

This is why it is quite valuable to become self-sufficient.

Good Luck!

d
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Nov 03, 2011 11:20 pm    Post subject:
Reply with quote

rohanthengal,

Here's a DFSORT job that will handle your changed requirement:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB)
//IN2 DD DSN=...  input file2 (FB/80)
//SORTOUT DD DSN=...  output file (FB/80)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(11,5,A),SORTED                   
  JOINKEYS F2=IN2,FIELDS=(1,5,A),SORTED                   
  JOIN UNPAIRED,F2                                         
  REFORMAT FIELDS=(F2:1,80,?)                             
  OPTION COPY                                             
  INREC IFOUTLEN=80,                                       
    IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),OVERLAY=(28:C'Y'))     
/*
Back to top
View user's profile Send private message
rohanthengal

Active User


Joined: 19 Mar 2009
Posts: 176
Location: Pune, Maharashtra, India

PostPosted: Mon Nov 07, 2011 9:32 am    Post subject:
Reply with quote

Thanks Frank it worked fantastic... i realised JOINKEYS is easier to play with than SPLICE....
Back to top
View user's profile Send private message
rohanthengal

Active User


Joined: 19 Mar 2009
Posts: 176
Location: Pune, Maharashtra, India

PostPosted: Fri Jan 20, 2012 10:13 pm    Post subject:
Reply with quote

Frank, what is '?' in the above code ?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Fri Jan 20, 2012 10:32 pm    Post subject: Reply to: need help with join
Reply with quote

From the Fine DFSORT Application Programming Guide wrote:
? indicates a 1-byte indicator is to be included in each joined record. The indicator will be set to one of the following values in each paired or unpaired joined record, as appropriate:
v 'B' - the key was found in F1 and F2.
v '1' - the key was found in F1, but not in F2.
v '2' - the key was found in F2, but not in F1.
Only one ? can be specified in the FIELDS operand. If ? is not the last item, it must be followed by F1: or F2:.
For TYPE=F joined records, the indicator can appear anywhere in the record. For example, each of the following is valid:
* Put indicator in position 1 of each joined record.
REFORMAT FIELDS=(?,F1:1,20,F2:5,8)
* Put indicator in position 21 of each joined record.
REFORMAT FIELDS=(F1:1,20,?,F1:31,9)
* Put indicator in position 25 of each joined record.
REFORMAT FIELDS=(F2:11,20,6,4,?)
For TYPE=V joined records, the indicator must appear in the fixed part of the record, that is, after the RDW and before the position without length fields. For example, the following is valid:
* Put indicator in position 5 of each joined record.
REFORMAT FIELDS=(F1:1,4,?,F1:11)
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Jan 20, 2012 10:48 pm    Post subject:
Reply with quote

rohanthengal wrote:
Frank, what is '?' in the above code ?


Read the section 4.5 REFORMAT Statement


http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA60/4.0?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Join Keys and DB2 Query jackare SYNCSORT 2 Sat Feb 13, 2016 5:57 am
No new posts Join Keys to compare two files senthamizh DFSORT/ICETOOL 2 Fri Feb 05, 2016 8:28 am
No new posts Join and Omit records- Master File no... shajeeth DFSORT/ICETOOL 5 Tue Jan 26, 2016 8:16 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us