IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Matching two files to one: troubles with ICETOOL


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Tue Nov 25, 2008 5:47 pm
Reply with quote

Hi to everyone and thank you for your info. please sorry for my english icon_smile.gif

I have two input files with a key field in both. I need to make a match to get an output file with only the necessary fields: is the same structure of the second.

The file 1 contains a list of customers and his accounts, and the file 2 all data os those accounts.

The result i need is in the output file should be all the records in the second file that exist in file 1.

An example:

File 1: Customers and accounts. Length: 130
Code:
        <-----KEY-------->
11111111999988887777777777 TEXT1 TEXT2 TEXT3
22222222999966665555555555 TEXT1 TEXT2 TEXT3
55555555999944443333333333 TEXT1 TEXT2 TEXT3
66666666999922221111111111 TEXT1 TEXT2 TEXT3
 

File 2: Auxiliary data of accounts Length: 654
Code:
<-----KEY--------> 
999988887777777777 TEXT1  [...] TEXT2 TEXT3  [...] *
999911119999999999 TEXTYY [...] TEXTN TEXTH  [...]   
999966665555555555 TEXT6  [...] TEXT6 TEXT10 [...] * 
999933338888888888 TEXTZZ [...] TEXTB TEXT10 [...] 
999955557777777777 TEXTZZ [...] TEXTB TEXT10 [...] 
999944443333333333 TEXTX  [...] TEXT2 TEXT15 [...] *
999977776666666666 TEXTZZ [...] TEXTB TEXT10 [...]
999922221111111111 TEXTX  [...] TEXT? TEXT15 [...] * 
 


Being TEXTXX text or numeric fields...
Key an ON(9,18) on files 1 and key ON(1,18) on file 2.

And the exit i want must be: (recs that are on file 1 and 2)

Exit: Size 654 (all the same data of file2)
Code:
<-----KEY--------> 
999988887777777777 TEXT1 [...] TEXT2 TEXT3  [...] 
999966665555555555 TEXT6 [...] TEXT6 TEXT10 [...]
999944443333333333 TEXTX [...] TEXT2 TEXT15 [...]
999922221111111111 TEXTX [...] TEXT? TEXT15 [...] 
 

Note:In this example, records are unsorted, but in my jcl they are sorted by key.


I've been trying the sample of Splice Operator ('11 ','22' and'12 ', ...) from publibz.boulder.ibm.com but it is still not make it work.
I do not know how to use the WITH, because they have differents size and in different order ...
Someone could help me please ... thank you very much
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Tue Nov 25, 2008 6:10 pm
Reply with quote

Hi,

There are lot many previous posts on this topic. You may try searching those.

I was going thru your requirements and had a doubt regarding the keys of input files.

You have written :
Quote:
Being TEXTXX text or numeric fields...
Key an ON(9,18) on files 1 and key ON(1,18) on file 2.


Well , how can the length of keys be different in both files. If you want to match records from both the files. The position of key fields may differ but the length has to be same.

Please give correct key fields.
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Tue Nov 25, 2008 6:23 pm
Reply with quote

Thanks for yoy comments.

Key on file begins on pos 1, and have 18 positions.
And key on file 2 begins on position 1 and has 18 positions also.

I 'have try, and search examples of topic, but all post i have found, has the key's file on initial position on both, or file1 and file2 has the same size.

I'va use the serch before post this topic, sorry if this requeriment was answered, but i didn't found (and sorry for my english, please correct me, i want to learn).

Thank you
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Nov 25, 2008 6:37 pm
Reply with quote

Quote:
Key on file begins on pos 1, and have 18 positions.

the description disagrees with the sample data You posted
( I did not change it, I simply added the code tags to make it readable )
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Tue Nov 25, 2008 6:51 pm
Reply with quote

Is was an error, it's like the first example i put:

Key on file1 begins on pos 9, and have 18 positions.

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

DFSORT Developer


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

PostPosted: Tue Nov 25, 2008 11:06 pm
Reply with quote

If neither file has duplicate records within it, as shown in your example, you don't need SPLICE for this ... you can do it more simply with a DFSORT/ICETOOL SELECT job like the following:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/130)
//IN2 DD DSN=...  input file2 (FB/654)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/654)
//TOOLIN DD *
COPY FROM(IN2) TO(T1)
COPY FROM(IN1) TO(T1) USING(CTL1)
SELECT FROM(T1) TO(OUT) ON(1,18,CH) FIRSTDUP
/*
//CTL1CNTL DD *
  INREC BUILD=(1:9,18,654:X)
/*


For more information on different techniques for matching, see the "Create files with matching and non-matching records" Smart DFSORT Trick in the my revised paper at:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Thu Nov 27, 2008 5:00 pm
Reply with quote

I have re-studied my problem and have reached this conclusion:

File1 130 bytes
<------KEY------->
XXXXXXXX999911112233333333Q... [DATA1] ...
XXXXXXXX999911112233333333w... [DATA1] ...
YYYYYYYY999911112233333333E... [DATA1] ...
XXXXXXXX999944445566666666R... [DATA1] ...
ZZZZZZZZ999977778899999999T... [DATA1] ...
AAAAAAAA999977778899999999Y... [DATA1] ...
ZZZZZZZZ999933334455555555U... [DATA1] ...
UUUUUUUU888877776600000000I... [DATA1] ...

File2 1000 bytes
<------KEY------->
9999111122333333331119955EUR199991111 M.. [DATA2] ...
9999111122333333331119955USD199991111 M.. [DATA2] ...
9999444455666666662228844PRO299994444 N.. [DATA2] ...
9999777788999999993337733PRO399997777 B.. [DATA2] ...
9999333344555555554446622PRO499993333 V.. [DATA2] ...
8888777722888888885551155PRO988882222 c.. [DATA2] ...


Out (solution1). must be tha same estructure than original, but inserting 8 first positions of File1 in pos 38 (i think) of File2.
File1 and File2 has duplicates, and file

Out
<------KEY------->
9999111122333333331119955EUR199991111XXXXXXXXM.. [DATA2] ...
9999111122333333331119955EUR199991111YYYYYYYYM.. [DATA2] ...
9999111122333333331119955USD199991111XXXXXXXXM.. [DATA2] ...
9999111122333333331119955USD199991111YYYYYYYYM.. [DATA2] ...
9999444455666666662228844PRO299994444XXXXXXXXN.. [DATA2] ...
9999777788999999993337733PRO399997777ZZZZZZZZB.. [DATA2] ...
9999777788999999993337733PRO399997777AAAAAAAAB.. [DATA2] ...
9999333344555555554446622PRO499993333ZZZZZZZZV.. [DATA2] ...

I've been trying move to the end of file data i need, use all the examples of 'VV', 'BB', '11', '22' and tricks of your "Smart DFSORT tricks" (more nearby is "Key in different places, duplicates" but i don't know how to make it work.

Can you help me? thank you for all your advices.

I expect not to have committed mistakes to post the requeriment now. icon_smile.gif
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Thu Nov 27, 2008 5:02 pm
Reply with quote

Code:

File1 130 bytes
        <------KEY------->
XXXXXXXX999911112233333333Q... [DATA1] ...
XXXXXXXX999911112233333333w... [DATA1] ...
YYYYYYYY999911112233333333E... [DATA1] ...
XXXXXXXX999944445566666666R... [DATA1] ...
ZZZZZZZZ999977778899999999T... [DATA1] ...
AAAAAAAA999977778899999999Y... [DATA1] ...
ZZZZZZZZ999933334455555555U... [DATA1] ...
UUUUUUUU888877776600000000I... [DATA1] ...


File2 1000 bytes
<------KEY------->
9999111122333333331119955EUR199991111        M.. [DATA2] ...
9999111122333333331119955USD199991111        M.. [DATA2] ...
9999444455666666662228844PRO299994444        N.. [DATA2] ...
9999777788999999993337733PRO399997777        B.. [DATA2] ...
9999333344555555554446622PRO499993333        V.. [DATA2] ...
8888777722888888885551155PRO988882222        c.. [DATA2] ...

Out
<------KEY------->
9999111122333333331119955EUR199991111XXXXXXXXM.. [DATA2] ...
9999111122333333331119955EUR199991111YYYYYYYYM.. [DATA2] ...
9999111122333333331119955USD199991111XXXXXXXXM.. [DATA2] ...
9999111122333333331119955USD199991111YYYYYYYYM.. [DATA2] ...
9999444455666666662228844PRO299994444XXXXXXXXN.. [DATA2] ...
9999777788999999993337733PRO399997777ZZZZZZZZB.. [DATA2] ...
9999777788999999993337733PRO399997777AAAAAAAAB.. [DATA2] ...
9999333344555555554446622PRO499993333ZZZZZZZZV.. [DATA2] ...

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

DFSORT Developer


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

PostPosted: Thu Nov 27, 2008 9:19 pm
Reply with quote

Do both files have duplicates within them as you've shown? For example, file1 has three 999911112233333333 keys and file2 has two 999911112233333333 keys. If so, the matching you want isn't clear. For those dup keys, you show two output EUR records, one with Xs and one with Ys, but since there are three file1 records with that key, shouldn't there be three EUR records instead of two EUR records in the output? Please explain the "rules" you're using to go from input to output.
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Thu Nov 27, 2008 10:02 pm
Reply with quote

Code:

File1 130 bytes: Customer and accounts: An account can have same customers Related
        <------KEY------->
XXXXXXXX999911112233333333Q... [DATA1] ... (Account1 - Customer1)
XXXXXXXX999911112233333333w... [DATA1] ... (Account1 - Customer1) but differen relationship with account
YYYYYYYY999911112233333333E... [DATA1] ... (Account1 - Customer2)
XXXXXXXX999944445566666666R... [DATA1] ... (Account2 - Customer1)
ZZZZZZZZ999977778899999999T... [DATA1] ... (Account3 - Customer3)
AAAAAAAA999977778899999999Y... [DATA1] ... (Account3 - Customer4)
ZZZZZZZZ999933334455555555U... [DATA1] ... (Account4 - Customer3)
UUUUUUUU888877776600000000I... [DATA1] ... (Account5 - Customer6) - No relationship on File2


File2 1000 bytes: Accounts, can be related with multiple Currencies. Same account in EUR, USD, ...
<------KEY------->
9999111122333333331119955EUR199991111        M.. [DATA2] ... (Account1 - EUR)
9999111122333333331119955USD199991111        M.. [DATA2] ... (Account1 - USD) Same account as Account1 but different currency
9999444455666666662228844PRO299994444        N.. [DATA2] ... (Account2 - EUR)
9999777788999999993337733PRO399997777        B.. [DATA2] ... (Account3 - EUR)
9999333344555555554446622PRO499993333        V.. [DATA2] ... (Account4 - EUR)
8888777722888888885551155PRO988882222        c.. [DATA2] ... (Account6 - EUR) - No relationship on File2
"
Out: Match between File1 and File2. If account is in File1 and File2, must be one record for each currency on File2.
We have 3 recored on File1.
<------KEY------->
9999111122333333331119955EUR199991111XXXXXXXXM.. [DATA2] ...(Account1 - Customer1 - EUR)
9999111122333333331119955EUR199991111YYYYYYYYM.. [DATA2] ...(Account1 - Customer2 - EUR)
9999111122333333331119955USD199991111XXXXXXXXM.. [DATA2] ...(Account1 - Customer1 - USD)
9999111122333333331119955USD199991111YYYYYYYYM.. [DATA2] ...(Account1 - Customer2 - USD)
9999444455666666662228844PRO299994444XXXXXXXXN.. [DATA2] ...(Account2 - Customer1 - PRO)
9999777788999999993337733PRO399997777ZZZZZZZZB.. [DATA2] ...(Account3 - Customer3 - PRO)
9999777788999999993337733PRO399997777AAAAAAAAB.. [DATA2] ...(Account3 - Customer4 - PRO)
9999333344555555554446622PRO499993333ZZZZZZZZV.. [DATA2] ...(Account4 - Customer3 - PRO)

OUT: Same solution but sorted by Customer.
<------KEY------->
9999111122333333331119955EUR199991111XXXXXXXXM.. [DATA2] ...(Account1 - Customer1 - EUR)
9999111122333333331119955USD199991111XXXXXXXXM.. [DATA2] ...(Account1 - Customer1 - USD)
9999111122333333331119955EUR199991111YYYYYYYYM.. [DATA2] ...(Account1 - Customer2 - EUR)
9999111122333333331119955USD199991111YYYYYYYYM.. [DATA2] ...(Account1 - Customer2 - USD)
9999444455666666662228844PRO299994444XXXXXXXXN.. [DATA2] ...(Account2 - Customer1 - PRO)
9999777788999999993337733PRO399997777ZZZZZZZZB.. [DATA2] ...(Account3 - Customer3 - PRO)
9999777788999999993337733PRO399997777AAAAAAAAB.. [DATA2] ...(Account3 - Customer4 - PRO)
9999333344555555554446622PRO499993333ZZZZZZZZV.. [DATA2] ...(Account4 - Customer3 - PRO)

Quote:
"since there are three file1 records with that key, shouldn't there be three EUR records instead of two EUR records in the output?"

Is correct, but with the relationship of same account-customer-currency, this record isn't needed, This is why I have omitted.
But if isn't possible, i'll omitt it in a program or in a next step.

I hope my explanation was more clear.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Fri Nov 28, 2008 11:18 pm
Reply with quote

So you're saying that if multiple file1 records have the same key (e.g. 999911112233333333) and the same value in positions 1-8 (XXXXXXXX), you can process the file1 records as if there was only one record with that key and value (e.g. process the first record with 999911112233333333 and XXXXXXXX and ignore the second) - right?
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Mon Dec 01, 2008 3:00 pm
Reply with quote

Ok.
Yes, it is.

For each record in File1 Account - Customer, must be in File2, with Data of File2.
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Mon Dec 01, 2008 3:56 pm
Reply with quote

Hi,

you may try using the below job:
Code:

//SS010 EXEC PGM=ICETOOL
//IN1 DD DSN=Input1,DISP=SHR
//IN2 DD DSN=Input2,DISP=SHR
//OUTP DD DSN=Output,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(20,50),RLSE)
//TMP1 DD DSN=&&TEMP,DISP=(MOD,PASS),
// SPACE=(CYL,(10,10),RLSE),
// UNIT=SYSDA
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN1) TO(TMP1) USING(CP01)
SORT FROM(IN2) TO(TMP1) USING(CP02)
SPLICE FROM(TMP1) TO(OUTP) ON(1,18,CH) WITH(1,1001)-
  WITHALL USING(CP03)
/*
//CP01CNTL DD *
  OUTREC BUILD=(9,18,1001:C'11')
/*
//CP02CNTL DD *
  OUTREC BUILD=(1,1000,1001:C'22')
/*
//CP03CNTL DD *
  OUTFIL FNAMES=OUTP,INCLUDE=(1001,2,CH,EQ,C'21'),BUILD=(1,1000)
/*

Let me know if you face any problem with this.
Thanks,
-Kapil.
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Mon Dec 01, 2008 9:07 pm
Reply with quote

Thanks hikaps1, but doesn't work.

I've changed sort for copy.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Mon Dec 01, 2008 10:42 pm
Reply with quote

This is a cartesian join. Kolusu might be able to show you how to do this with SPLICE, but he's on vacation this week.

I could show you how to do it with an Assembler program. If you want me to do that, send me a note offline (yaeger@us.ibm.com) referring to this thread.
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Tue Dec 02, 2008 12:04 pm
Reply with quote

Hi Warp,

If you r still stuck with this, can you tell me. What prob did you face while executing the job given by me.

Frank,

I have gone thru the whole thread and; it seems as if the requirement is to ignore the duplicates in file1 and do not ignore dups of file2.

Well, it doesn't looks a cartesian join. its 1-N join. This can be done with splice. Although I have code to execute cartesian join , given by Kolusu.

Please let me know if I have missed something.

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

DFSORT Developer


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

PostPosted: Tue Dec 02, 2008 10:12 pm
Reply with quote

hikaps14,

Did you even run your job? When I run your job, it gets the error message

ICE010A 0 NO SORT OR MERGE CONTROL STATEMENT

because you don't have a SORT statement in CP01CNTL or CP02CNTL.
So either you didn't run it or you aren't showing the job you actually ran.

If I change the SORT operators to COPY operators, the job runs but gets the wrong output:

Code:

9999111122333333331119955EUR199991111        M..  DATA2  ...     
9999111122333333331119955USD199991111        M..  DATA2  ...     
9999333344555555554446622PRO499993333        V..  DATA2  ...     
9999444455666666662228844PRO299994444        N..  DATA2  ...     
9999777788999999993337733PRO399997777        B..  DATA2  ...     


Quote:
Well, it doesn't looks a cartesian join. its 1-N join.


It looks like an N-N join to me.

File1 has:

XXXXXXXX999911112233333333
YYYYYYYY999911112233333333E

File2 has:

9999111122333333331119955EUR199991111
9999111122333333331119955USD199991111

The output file has:

9999111122333333331119955EUR199991111XXXXXXXX
9999111122333333331119955EUR199991111YYYYYYYY
9999111122333333331119955USD199991111XXXXXXXX
9999111122333333331119955USD199991111YYYYYYYY

That's NOT a 1-N join.

Quote:
This can be done with splice. Although I have code to execute cartesian join , given by Kolusu.


Yes, I know. I said that in my previous post. But Kolusu is on vacation this week and I don't have the time to adapt his trick for this instance. That's why I offered to supply an Assembler Program ( already have one I can adapt and it's also more efficient than the SPLICE trick).
Back to top
View user's profile Send private message
warp22

New User


Joined: 13 Nov 2008
Posts: 11
Location: ALICANTE - Spain

PostPosted: Wed Dec 10, 2008 8:01 pm
Reply with quote

Quote:
That's why I offered to supply an Assembler Program ( already have one I can adapt and it's also more efficient than the SPLICE trick).

Firts of all, thank's for the offer. But i don't know if i can user asm.. icon_sad.gif

Quote:
The output file has:

9999111122333333331119955EUR199991111XXXXXXXX
9999111122333333331119955EUR199991111YYYYYYYY
9999111122333333331119955USD199991111XXXXXXXX
9999111122333333331119955USD199991111YYYYYYYY

It's ok.

Can you help me whit this N-N or refer me to any doc?

Thanks.

If i try to do this in a program... is this a mathing? what's the "master" file? What order I must follow in the comparison?

Thanks
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Dec 11, 2008 2:16 am
Reply with quote

The following DFSORT/ICETOOL JCl will give you the desired results. you can add another sort pass depending on how you want the final output to be.

Code:

//STEP0100 EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD DSN=YOUR 130 BYTE FILE,DISP=SHR
//IN2      DD DSN=YOUR 1000 BYTE FILE,DISP=SHR
//TEMP     DD DSN=&&TEMP,DISP=(MOD,PASS),SPACE=(CYL,(25,25),RLSE)         
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
  SORT FROM(IN1) USING(CTL1)                                           
  SORT FROM(IN2) USING(CTL2)                                           
  SPLICE FROM(TEMP) TO(OUT) ON(1001,26,CH) -                           
  WITH(1,1000) WITHALL USING(CTL3)                                     
//CTL1CNTL DD *                                                         
  OPTION EQUALS                                                         
  SORT FIELDS=(9,18,CH,A,1,8,CH,A)                                     
  SUM FIELDS=NONE                                                       
  OUTREC OVERLAY=(131:SEQNUM,8,ZD,RESTART=(9,18))                       
  OUTFIL FNAMES=CTL2CNTL,REMOVECC,NODETAIL,BUILD=(80X),                 
  TRAILER1=(' OPTION EQUALS',/,                                         
            ' SORT FIELDS=(1,18,CH,A)',/,                               
            ' OUTREC OVERLAY=(1001:1,18,SEQNUM,8,ZD,RESTART=(1,18))',/,
            ' OUTFIL FNAMES=TEMP,',/,                                   
            ' BUILD=(1,1018,SEQNUM,8,ZD,RESTART=(1001,26),8X),',/,     
            ' REPEAT=',MAX=(131,8,ZD,M11,LENGTH=8))                     
  OUTFIL FNAMES=TEMP,BUILD=(1001:9,18,131,8,1,8)                       
//CTL2CNTL DD DSN=&&C1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)             
//CTL3CNTL DD *                                                         
  OUTFIL FNAMES=OUT,BUILD=(1,37,1027,8,46,955)                         
/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Shift left VB record without x00 endi... DFSORT/ICETOOL 11
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
Search our Forums:

Back to Top