Maching with ICETOOL



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Maching with ICETOOL

Postby pulcinella » Tue Nov 04, 2008 8:40 pm

First excuse if this question has been answered.

I need matching two files (T01, T02) with 124 length both and obtain a third file with 128 length. The two files
are ordered by the first 31 position.

If two records are the same (if the keys of both files are the same) I don't want to make nothing. Must be ignored

If the key of first file is greater than the key of second file, I write the output file with this record put at the finish AT01
If the key of first file is less than the key of second file, I write the output file with this record put at the finist BT02

file 1 (T01 with 124 positions)

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CCCCCCCCAAAAAAAAAAAAAAAAAAAAAAFxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBAxxxxxxxxxxxxxxxxxxxxxxxxxxxx
CCCCCCCCBBBBBBBBBBBBBBBBBBBBBBBxxxxxxxxxxxxxxxxxxxxxxxxxxxx
DDDDDDDDBBBBBBBBBBBBBBBBBBBBBBFxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAAAAAACCCCCCCCCCCCCCCCCCCCCCDxxxxxxxxxxxxxxxxxxxxxxxxxxxx

file 2 (T02 with 124 positions)

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxxxxxxxxxxxx
BBBBBBBBAAAAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBAxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBBxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBExxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBGxxxxxxxxxxxxxxxxxxxxxxxxxxxx
AAAAAAAACCCCCCCCCCCCCCCCCCCCCCDxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Outfile (128 positions)

BBBBBBBBAAAAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFxxxxxxxxxxxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBExxxxxxxxxxxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBFxxxxxxxxxxxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBGxxxxxxxxxxxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)

The sames record don't must be appair in the output file

the position after the key are not relevant

Thank you
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with ICETOOL

Postby pulcinella » Tue Nov 04, 2008 9:13 pm

Excuse me.
The key are 31 position but, the order is 9-30 (first by this position),1-8 (second by this position),31 (third by this position). The first rellevance position are since 9 until 30; after since position 1 until position 8 and the finished the position 31
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with ICETOOL

Postby Frank Yaeger » Tue Nov 04, 2008 10:52 pm

It's not clear what you want to do. You didn't really explain the rules for comparing a record from file1 to a record from file2. I thought you wanted to compare the records from file1 and file2 one-by-one (record 1 to record 1, record 2 to record 2, etc). In your example, that seems to work until we get to the 6th record, but it doesn't work after that.

For File1, you have:

...
DDDDDDDDBBBBBBBBBBBBBBBBBBBBBBFxxxxxxxxxxxxxxxxxxxxxxxxxxxx (record 5)
AAAAAAAACCCCCCCCCCCCCCCCCCCCCCDxxxxxxxxxxxxxxxxxxxxxxxxxxxx (record 6)

For File2, you have:

...
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBExxxxxxxxxxxxxxxxxxxxxxxxxxxx (record 5)
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBGxxxxxxxxxxxxxxxxxxxxxxxxxxxx (record 6)
AAAAAAAACCCCCCCCCCCCCCCCCCCCCCDxxxxxxxxxxxxxxxxxxxxxxxxxxxx (record 7)

So if we compare record 5 in both files, the File2 key is less than the File key, so the output you show for those two records makes sense:

AAAAAAAABBBBBBBBBBBBBBBBBBBBBBExxxxxxxxxxxxxxxxxxxxxxxxxxxxBT02
AAAAAAAABBBBBBBBBBBBBBBBBBBBBBFxxxxxxxxxxxxxxxxxxxxxxxxxxxxAT01

But record 6 from File1 does NOT match record 6 from File2 (it does match record 7 from File2), but you only show record 6 from File2 in the output. I can't figure out what the "rule" is that gives you that output.

So if your example is correct, you need to do a better job of explaining the rules. If your example is incorrect, you need to correct it, and do a better job of explaining the rules.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Maching with ICETOOL

Postby pulcinella » Wed Nov 05, 2008 3:49 pm

Thank you Frank. I will treat to explain better because my example is very confused... Forget my first example

I need compare the records of both files but don't one to one. I want to do a process that:

If the key1 (record of file1) is greater than key2 (record of file2) write in the output file the record of the file2 and read the next record of file2 until the key2 is greater or equal that the key1 add AT01 at the finish of record

If the key1 (record of file1) is equal to key2 (record of file2) don't make nothing

If the key1 (record of file1) is less than key2 (record of file2) write in the output file the record of file1 and read the next record of file1 until the key1 is greater than or equal to that key2 add BT02 at the finish of record.

The file1 and file2 haven't the same record's (the file1 may have 4 records and the file2 may have 20 records of file1 may have 6 records and file2 may have 6 records or file1 may have 20 records and the file2 may have 4 records)

When i said that the key is 31 position, really is ordered by: first order by 9-22 position; second order by 1-8 position and third by the last position. All fields (the 31 position) are the keys.

Ideally is that the utility ICETOOL make compare the positions of file1 (first 9-22, second 1-8, third 31) with the same positions (same order) of file2. I ordered the columns at preliminary step... Forget my first example. I have:

File1 (124 positions T01)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAAGxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAAHxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBFxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBGxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCAxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCBxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCCxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCDxxxxxxxxxxxxxxxxxx

File2 (124 positions T02)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAAHxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBAxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBDxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBFxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBGxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCAxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCBxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCCxxxxxxxxxxxxxxxxxx
CCCCCCCCCCCCCCCCCCCCRxxxxxxxxxxxxxxxxxx

Output file

AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAAAAAAAAAAAAAAGxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
BBBBBBBBBBBBBBBBBBBBAxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
BBBBBBBBBBBBBBBBBBBBDxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
CCCCCCCCCCCCCCCCCCCCDxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
CCCCCCCCCCCCCCCCCCCCRxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)

Thank and I feel enormously my incorrect example
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with ICETOOL

Postby Frank Yaeger » Thu Nov 06, 2008 12:19 am

It seems like an alternate, easier to understand set of rules would be:

1) Eliminate any records that have the same key in both files.
2) Add AT01 to the remaining records from file1.
3) Add BT02 to the remaing records from file2.

Here's a DFSORT/ICETOOL job that implements those rules:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/124)
//IN2 DD DSN=...  input file2 (FB/124)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/128)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SELECT FROM(T1) TO(OUT) ON(9,22,CH) ON(1,8,CH) ON(31,1,CH) -
  NODUPS
/*
//CTL1CNTL DD *
  INREC OVERLAY=(125:C'AT01')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(125:C'BT02')
/*


This gives the output you wanted for your example.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Maching with ICETOOL

Postby pulcinella » Thu Nov 06, 2008 2:40 pm

Great!!

seems that I don't to preliminary step to outrec the file in this column's. The same icetool utility can compare fields are not in positions correlative. I'll prove your example, and I said if I have problems. Thank you very much
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with ICETOOL

Postby pulcinella » Thu Nov 06, 2008 7:56 pm

By the way, excuse my ignorance. One question... Where are your specific if the maching is one to one or It must be read the first record at file1 and find the record greater, less or equal at file2?. I want to said, in my example the maching is not 1 to 1; the matching can be 1 to n or n to 1... Icetool utility make automatically?. As I can not prove it until next week I do not want to bother unnecessarily
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with ICETOOL

Postby Frank Yaeger » Thu Nov 06, 2008 10:15 pm

seems that I don't to preliminary step to outrec the file in this column's. The same icetool utility can compare fields are not in positions correlative


I'm not sure what you mean by this. Are you asking if ICETOOL can handle having the fields to be compared in different places in the two files (you never mentioned this in your previous posts)? It can, but to show you how, I'd need to know the details.

Where are your specific if the maching is one to one or It must be read the first record at file1 and find the record greater, less or equal at file2?.


My job uses the alternate rules I described earlier. The alternate rules are easier to implement than the rules you gave, but still give the requested output for your example. If you have a case you didn't mention for which the alternate rules don't work, show me an example of that case.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Maching with ICETOOL

Postby pulcinella » Fri Nov 07, 2008 2:52 pm

Dear Frank,

When i said: "seems that I don't to preliminary step to outrec the file in this column's. The same icetool utility can compare fields are not in positions correlative" I wanted to say that If I have the key ordered by 9-22, 1-8 and 31 columns,

1.- I Can do a previous step to traspose the columns (moving the columns until the key are 1-31 position) and used: SELECT FROM(T1) TO(OUT) ON(1,31,CH) NODUPS

2.- I can't do a previous step and used: SELECT FROM(T1) TO(OUT) ON(9,22,CH) ON(1,8,CH) ON(31,1,CH) NODUPS


When i said "Where are your specific if the maching is one to one or It must be read the first record at file1 and find the record greater, less or equal at file2?" I want to say that explain at wednesday: "If the key1 (record of file1) is greater than key2 (record of file2) write in the output file the record of the file2 and read the next record of file2 until the key2 is greater or equal that the key1 add AT01 at the finish of record. If the key1 (record of file1) is equal to key2 (record of file2) don't make nothing. If the key1 (record of file1) is less than key2 (record of file2) write in the output file the record of file1 and read the next record of file1 until the key1 is greater than or equal to that key2 add BT02 at the finish of record."

I I have:

A)

File1 (124 positions T01)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAATxxxxxxxxxxxxxxxxxx

File2 (124 positions T02)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAARxxxxxxxxxxxxxxxxxx

With your example I obtain?

Output file

AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
AAAAAAAAAAAAAAAAAAAARxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAAAAAAAAAAAAAATxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)


B)

File1 (124 positions T01)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAARxxxxxxxxxxxxxxxxxx

File2 (124 positions T02)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAATxxxxxxxxxxxxxxxxxx

With your example I obtain?

Output file

AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
AAAAAAAAAAAAAAAAAAAARxxxxxxxxxxxxxxxxxxAT01 (the first 31 position are not in the second file)
AAAAAAAAAAAAAAAAAAAATxxxxxxxxxxxxxxxxxxBT02 (the first 31 position are not in the first file)
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Maching with ICETOOL

Postby pulcinella » Fri Nov 07, 2008 5:46 pm

I don't know if I explain very well.

Basic, Seems that Icetool really compare one to one but I don't know If this is true. It's the same that I do a program with perform until?... I have fraid because if for example I have:


File1 (124 positions T01)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBAxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBDxxxxxxxxxxxxxxxxxx

File2 (124 positions T02)

AAAAAAAAAAAAAAAAAAAAAxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAABxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxx
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxx
BBBBBBBBBBBBBBBBBBBBAxxxxxxxxxxxxxxxxxx

obtain?

AAAAAAAAAAAAAAAAAAAACxxxxxxxxxxxxxxxxxxBT02
AAAAAAAAAAAAAAAAAAAADxxxxxxxxxxxxxxxxxxBT02
BBBBBBBBBBBBBBBBBBBBDxxxxxxxxxxxxxxxxxxAT01

Because If i compare one to one If the records are not the same (file1 = 20 and file2 = 5 or file1 = 5 and file2 = 20) I don't know how compare ICETOOL really
pulcinella
 
Posts: 114
Joined: Mon Dec 10, 2007 10:18 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post