Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Matching FILE A and FILE B based on ACCOUNT NUMBER

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

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Wed Dec 06, 2006 11:37 pm    Post subject: Matching FILE A and FILE B based on ACCOUNT NUMBER
Reply with quote

Hi All,

I have a problem which requires your help.

There is file A with fields

1. ACCOUNT NUMBER
2. STATUS
3. DATE 1
4. DATE 2

Ther is file B with fields

1. ACCOUNT NUMBER
2. STATUS
3. DATE3

I need an output file by matching FILE A and FILE B based on ACCOUNT NUMBER and STATUS and create the output file with fields

ACCOUNT NUMBER
STATUS
DATE 1
DATE 2
DATE 3

Thanks in advance for your help

Warning: Title edited and dup. post removed
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 06, 2006 11:54 pm    Post subject:
Reply with quote

What is the starting position, length and format of the ACCOUNT NUMBER, STATUS, DATE1, DATE2 and DATE3 fields? What is the RECFM and LRECL of each input file? Do you want to omit non-matching records from the output file?

Please show an example of the input records in each file and the records you want for output. If file A can have duplicates within it, show that in the example. If file B can have duplicates within it, show that in the example.
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Thu Dec 07, 2006 12:01 am    Post subject: Re: Matching FILE A and FILE B based on ACCOUNT NUMBER
Reply with quote

EXAMPLE:

FILE A

RECORD 1 => 123456789012345QW10.12.200620.12.2006
RECORD 2 => 123456789034567QE10.12.200620.12.2006

FILE B

RECORD 3 => 123456789012345QW12.12.2006

REQUIRED OUTPUT FILE

FIRST 18 character should match between FILE A and FILE B
that is 123456789012345QW
matching fields

123456789012345QW10.12200620.12.200612.12.2006
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Thu Dec 07, 2006 12:03 am    Post subject: Re: Matching FILE A and FILE B based on ACCOUNT NUMBER
Reply with quote

Please excuse...

Some more info FILE A and FILE B does not have duplicates.
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 07, 2006 2:00 am    Post subject:
Reply with quote

Quote:
FIRST 18 character should match between FILE A and FILE B
that is 123456789012345QW


That's 17 characters, not 18.

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB)
//IN2 DD DSN=...  input file2 (FB)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,17,CH) WITH(38,10)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(47:X)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(38:18,10)
/*
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Thu Dec 07, 2006 7:04 pm    Post subject: Re: Matching FILE A and FILE B based on ACCOUNT NUMBER
Reply with quote

HI Frank,

I use the following JCL. I think i did not try the correct way. Can you help please?

Code:
//S1 EXEC PGM=ICETOOL                                     
//TOOLMSG DD SYSOUT=*                                     
//DFSMSG  DD SYSOUT=*                                     
//IN1     DD *                                           
123456789012345QW10.12.200620.12.2006                     
123456789034567QE10.12.200620.12.2006                     
/*                                                       
//IN2     DD *                                           
123456789012345QW12.12.2006                               
/*                                                       
//T1      DD DSN=EORGA.T1,                               
//           DISP=(NEW,CATLG,DELETE),                     
//           UNIT=SYSDA,                                 
//           SPACE=(CYL,(50,50),RLSE)                     
//OUT     DD DSN=EORGA.STATUS.KS,                         
//           DISP=(NEW,CATLG,DELETE),                     
//           UNIT=SYSDA,                               
//           SPACE=(CYL,(50,50),RLSE)                 
//TOOLIN DD *                                         
COPY FROM(IN1) TO(T1) USING(CTL1)                     
COPY FROM(IN2) TO(T1) USING(CTL2)                     
SPLICE FROM(T1) TO(OUT) ON(1,17,CH) WITH(38,10)       
/*                                                     
//CTL1CNTL DD *                                       
  INREC OVERLAY=(47:X)                                 
/*                                                     
//CTL2CNTL DD *                                       
  INREC OVERLAY=(38:18,10)                             
/*                                                     


But the output data set is empty.
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 07, 2006 10:55 pm    Post subject:
Reply with quote

You missed one very important thing.

In my job, I have:

//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)

This is a MOD data set.

In your job, you have:

//T1 DD DSN=EORGA.T1,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(50,50),RLSE)

This is not a MOD data set. T1 must be a MOD data set. Fix that and your job will run correctly.
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Thu Dec 07, 2006 11:09 pm    Post subject: Re: Matching FILE A and FILE B based on ACCOUNT NUMBER
Reply with quote

Thanks a lot Frank, the solution is working fine,but same question again...
Can you please explain in a more detail way, how this works and What is SPLICE?
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 07, 2006 11:21 pm    Post subject:
Reply with quote

For complete details on the SPLICE operator of DFSORT's ICETOOL (with examples), see:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

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:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
Back to top
View user's profile Send private message
Prabhu Kvvsrt

New User


Joined: 06 Dec 2006
Posts: 7
Location: Hyderabad

PostPosted: Sun Dec 10, 2006 6:08 pm    Post subject: Matching File A and File B based on Account Number
Reply with quote

In this regard : I have a question

what if we have duplicates in the first file itself and we need all the dups in it
Back to top
View user's profile Send private message
Prabhu Kvvsrt

New User


Joined: 06 Dec 2006
Posts: 7
Location: Hyderabad

PostPosted: Sun Dec 10, 2006 6:19 pm    Post subject: Matching File A and File B based on Account Number
Reply with quote

Actually My Requirement is

1) File A with some fields of length 500
2) File B with some fields of lenght 600

Now in the both the files two fields exist at same position and
both the files have same number of records.

Suppose position of two fields is 50:10 then i have to compare them in both the files and get all the records from the file 1 with two more fields appended to it and those two fields are from file 2.

I tried your SPLICE logic but i am UNABLE to get all the dups from the file1
I mean i need the dups also from the file 1.

Thanks,
Prabhu
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: Sun Dec 10, 2006 8:35 pm    Post subject:
Reply with quote

Pabhu,

It's not clear what you want to do. Please show an example of the records (relevant fields) in each input file, and what you expect for the output records. Give the starting position, length and format of the relevant fields.
Back to top
View user's profile Send private message
Prabhu Kvvsrt

New User


Joined: 06 Dec 2006
Posts: 7
Location: Hyderabad

PostPosted: Mon Dec 11, 2006 12:19 pm    Post subject: Re: Matching FILE A and FILE B based on ACCOUNT NUMBER
Reply with quote

Hi Frank,

I found the solution for gettting the duplicates also from file1.

For your reference

File 1 :
Customer Number 9 Characters
Amount 6 Characters
Customer Name 6 Characters
Due Date 8 Characters

File 2:
Customer Number 9 Characters
Amount 6 Characters
Customer Address 10 Characters
Customer Pincode 6 Characters

if FILE 1 has duplicates as follows
000000001250000CLARKE20061211
000000001250000CLARKE20061213

File 2 :
000000001250000JameCircle600030
000000001250000JameCircle600030

In this case if we splice ON (CUSTOMERNUMBER,AMOUNT) WITH(CUSTOMER ADDRESS,CUSTOMER PINCODE) in the output file we get only one record from FILE1
I.E 1st record : 000000001250000CLARKE20061211 JameCircle600030
but not the 2nd record as it is a duplicate in FILE1.

You might say that you can change the ON Paramater but sometimes date might be the same in that case we get only one record from file1

My requirement was to get all the records from the FILE1.
File1 and File2 will have same number of records.

I resolved it by adding SEQNUM in OUTREC FIELDS of both the files and then SPLICING it ON(CUSTOMERNUMBER,AMOUNT,SEQNUM).

Please let me know if there is any other way to do it.

Thanks,
Prabhu
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: Mon Dec 11, 2006 9:23 pm    Post subject:
Reply with quote

Quote:
Please let me know if there is any other way to do it.


I can't because I still don't know what you're trying to do.

If you' re trying to get all of the records from file1 that have a match in file2, you can SELECT FIRST and reformat from file2 to T1 MOD, then COPY and reformat from file1 to T1 MOD, then SPLICE T1 to OUT.
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 Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts VBS and VB, COBOL syntax is the same ... natt.sut COBOL Programming 3 Sun Nov 12, 2017 6:36 am
No new posts Increment number for every group of r... srajendran2 DFSORT/ICETOOL 7 Thu Nov 09, 2017 10:31 pm
No new posts Matching and unmatched records!! Vignesh Sid SYNCSORT 3 Thu Nov 09, 2017 11:22 am
No new posts Updating data from one file to other!!! Vignesh Sid SYNCSORT 1 Mon Nov 06, 2017 2:42 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us