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
 

 

Match Fields from Two Datasets

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

New User


Joined: 11 Dec 2006
Posts: 21
Location: India

PostPosted: Wed Dec 20, 2006 9:27 pm    Post subject: Match Fields from Two Datasets
Reply with quote

I have a flat file which has details of employee name. The longest employee name is 20 character. There are 500 records.

I have another flat file with 1500 employee name's and their date of birth. This list includes the 500 employees in the first dataset.

I want to do a comparison and get an output where I have the list of all 500 employees in the first dataset with the DOB retrieved from the 2nd Dataset.

Arun
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 Dec 20, 2006 10:21 pm    Post subject:
Reply with quote

You can use the technique discussed in the "Join fields from two files on a key " Smart DFSORT Trick at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

If you need more specific help, show an example of the records in each input file and what you want for the output records. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files.
Back to top
View user's profile Send private message
prasaar

New User


Joined: 11 Dec 2006
Posts: 21
Location: India

PostPosted: Thu Dec 21, 2006 1:09 pm    Post subject: Re: Match Fields from Two Datasets
Reply with quote

Hi Frank,

Thanks for the quick response.

The DS attributes for both the input DS's are as below:

Record format . . . : FB
Record length . . . : 132
Block size . . . . : 27984

The DS NAME.PS has the following records:


Code:

Name
-------------------
ARUNPRASAD
KRISHNAPRASAD
DIPAKJADHAV


The records starts at column-1 and the maximum record length is 20 Char

I have another DS AGE.PS which contains the following records

Code:

Name                Sex  Age
-----------------   ---  -----
ARUNPRASAD          M    25
KRISHNAPRASAD       M    46
SAMPALMISANO        M    20
DIAPKJADHAV         M    25


Name: Starts at column 1 to 20
Sex:starts at column 22 to 23
Age:Starts at column 25 to 27

OUTPUT: The AGE.PS contains many records in the prescribed format. I want an output where, I get the Name,sex & age details of only those people present in NAME.PS.

The link you'd mentioned is very good. I will defenitely learna lot from it. Thanks a lot for your assistance.

Cheers,
Arun Prasad
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 Dec 21, 2006 8:52 pm    Post subject:
Reply with quote

This is actually simpler than I thought when reading your description. Here's a DFSORT/ICETOOL job that will do what you want. Be sure to concatenate AGE.PS before NAME.PS as shown.

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//CON DD DSN=AGE.PS,DISP=SHR
//    DD DSN=NAME.PS,DISP=SHR
//OUT DD DSN=...   output file
//TOOLIN DD *
SELECT FROM(CON) TO(OUT) ON(1,20,CH) FIRSTDUP
/*


OUT would have:

Code:

ARUNPRASAD          M    25 
KRISHNAPRASAD       M    46 
Back to top
View user's profile Send private message
prasaar

New User


Joined: 11 Dec 2006
Posts: 21
Location: India

PostPosted: Thu Dec 21, 2006 9:40 pm    Post subject: Re: Match Fields from Two Datasets
Reply with quote

Hi Frank,

Thanks for the quick response & the jcl. When I try to implement the same cntl statements on other set of inputs, The sorting is not happening properly. I did change the record length and other crieteria.

Consider I have the following data in PARTS.PS(contains 50000 records)
Part-Name- Part# Qty
A50001AN 4000 02
A50001AN 0 02
A50001CT 4000 01
A50001CT 0 01
A50001MR 4000 09
A50001MR 0 09
AJ185MS912-4 0
AM50GS5014 0
892601P465 0 05
892601P466 0 05
123-456-789 1A 01

Part-Name=MaxLength=18, Some records contain "Hypen" along with letters & Numbers.
Part#=Maxlength=4, some records are alphanumeric
Qty=Maxlength=2, Number

And the DS Order.PS has the following data(Contains 10,000 records)

Part-Name
2138300-19
2138872
2162300
2241999
2281177-10
2281177-11
2281177-12
2293437-5
2293437-6

Part-Name=MaxLength=18, Some records contain "Hypen" along with letters & Numbers.

The Sorting crieteria is the same as the last example. I want all the data in ORDER.PS along with the information of part# & Qty. Some of the data in ORDER.PS may not be present in PART.PS

Cheers,
Arun Prasad
Back to top
View user's profile Send private message
prasaar

New User


Joined: 11 Dec 2006
Posts: 21
Location: India

PostPosted: Thu Dec 21, 2006 9:49 pm    Post subject: clarification
Reply with quote

HI Frank,

I apologize for the bad formating of record...for your convenience, in PARTS.PS.The part# seems mixed with part-name. From each string, the following values are part#. In teh actual DS, there are 4 blank spaces in between part-name & part#. What ever value follows part# pertains to Qty.
4000
0
4000
0
4000
0
0
0
0
1A
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 Dec 21, 2006 10:05 pm    Post subject:
Reply with quote

I don't know what you mean by "The sorting is not happening properly" for the first example. The output records will be sorted by the Name field. If you want the output records sorted some other way, you have to tell me how you want them sorted.

I can't make heads or tails out of your second example. I don't see any matches between the two files on the Part-Name. In PARTS.PS the Part-Name values start with A, 8 or 1. In ORDER.PS, the Part-Name values start with 2. So where are the matches? You need to give me a good example and explain the sort order you want before I can help.

It would really help if you would use ubb code tags around your example records. Put <code> before the example lines, but use [] instead of <>. Put </code> after the example lines, but use [] instead of <>. Example:

<code>
line 1
line 2
</code>

with [ for < and ] for >.

That will let you line up the data the way it should actually look.
Back to top
View user's profile Send private message
prasaar

New User


Joined: 11 Dec 2006
Posts: 21
Location: India

PostPosted: Fri Dec 22, 2006 3:24 pm    Post subject: Re: Match Fields from Two Datasets
Reply with quote

Code:

46-317249G25        0   09
46-317249G26        0   09
46-317249G27        0   09
46-317249G39        0   09
46-317249G41        0   09
A50001AN            400002
A50001CT            400001
A50001MR            400009
5141465-1-100       0   04
5141465-2-100       0   04

The above is the data in PART.PS

Code:

46-317249G41       
A50001AN           
A50001CT   
5141465-2-100


The above is the data in order.ps
I would like have an output as below::
Code:

46-317249G41        0   09
A50001AN            400002
A50001CT            400001
5141465-2-100       0   04


My apologies for the very bad formating of data in the last post.
Thanks .

Arun Prasad
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: Fri Dec 22, 2006 9:35 pm    Post subject:
Reply with quote

So you want the output records in the original order of the PARTS.PS file.

Here's a DFSORT/ICETOOL job for that. We just add a sequence number to the PARTS.PS records and sort the selected records on that sequence number to get them in that order.

Code:

//S1 EXEC PGM=ICETOOL                                             \
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...   PART.PS  (FB/132)
//IN2 DD DSN=...   ORDER.PS (FB/132)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...   output file (FB/132)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(T2) ON(1,20,CH) FIRSTDUP
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(133:SEQNUM,8,ZD)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(133:8X)
/*
//CTL3CNTL DD *
  SORT FIELDS=(133,8,ZD,A)
  OUTREC BUILD=(1,132)
/*
Back to top
View user's profile Send private message
prasaar

New User


Joined: 11 Dec 2006
Posts: 21
Location: India

PostPosted: Mon Dec 25, 2006 5:50 pm    Post subject: Re: Match Fields from Two Datasets
Reply with quote

Hi Frank,

Merry Christmas.

Thanks a ton for the code. When I ran it, I got some errors. Currently we are running R14 DFSORT. I found that there was a PTF introduced in Dec 2004 to accept the Overlay function.

I've asked the Sys-Prog to check if PTF UQ95213 has been installed.

Again thanks a lot for the prompt response and the for the codes.

Cheers,
Arun Prasad
MVS Admin
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 SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 5 Sat Jan 21, 2017 4:02 am
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts DUMP DATASETS VS TRACE DATASETS! lind sh CICS 4 Tue Dec 06, 2016 11:31 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am


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