ICETOOL - compare 2 input files



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

Re: ICETOOL - compare 2 input files

Postby bitbybit » Thu Oct 18, 2012 7:16 pm

File-1
EDIT                                                        Columns 00051 00122
Command ===>                                                  Scroll ===> CSR 
****** ***************************** Top of Data ******************************
000001  Å  Ñø< F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
       061216740C00000C00044444444444000CFF04CDECCDDD000004006C6A006C4FFFCFECDF
       9701990C2600102650000000000000000082324947394200000000767C01760224159970
------------------------------------------------------------------------------
000002  Å  Ñ-  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
       061216620C00000C00044444444444000CFF04CDECCDDD000004006C6A006C4FFFCFECDF
       9701990C2600102650000000000000000082324947394200000000767C01760224159970
------------------------------------------------------------------------------
000003  Å  Ñ&  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
       061216520C00000C00044444444444000CFF04CDECCDDD000004006C6A006C4FFFCFECDF
       9701990C2600102650000000000000000082324947394200000000767C01760224159970
------------------------------------------------------------------------------
****** **************************** Bottom of Data ****************************
File-2

EDIT                                                       Columns 00051 00122
Command ===>                                                  Scroll ===> CSR 
****** ***************************** Top of Data ******************************
000001  Å  Ñø  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
       061216700C00000C00044444444444000CFF04CDECCDDD000004006C6A006C4FFFCFECDF
       9701990C2600102650000000000000000082324947394200000000767C01760224159970
------------------------------------------------------------------------------
000002  Å  Ñ-  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
       061216600C00000C00044444444444000CFF04CDECCDDD000004006C6A006C4FFFCFECDF
       9701990C2600102650000000000000000082324947394200000000767C01760224159970
------------------------------------------------------------------------------
000003  Å  Ñ   F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
       061216400C00000C00044444444444000CFF04CDECCDDD000004006C6A006C4FFFCFECDF
       9701990C2600102650000000000000000082324947394200000000767C01760224159970
------------------------------------------------------------------------------
000004  Å  Ñ  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
       061216300C00000C00044444444444000CFF04CDECCDDD000004006C6A006C4FFFCFECDF
       9701990C2600102650000000000000000082324947394200000000767C01760224159970
------------------------------------------------------------------------------
****** **************************** Bottom of Data ****************************

to make it simple, I have 3 records in FL1 and 4 in FL2. Both files are sorted on KEY in Desc order. Only first 2 records from FL1 has a matching key in FL2, the 3rd record doesn't, so, my O/P file shld have only rec 1& 2 from FL1.

After I ran that sort step, here is my o/p file
EDIT                                                       Columns 00051 00122
Command ===>                                                  Scroll ===> CSR 
****** ***************************** Top of Data ******************************
000001  Å  Ñø< F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000002  Å  Ñø< F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000003  Å  Ñø< F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000004  Å  Ñø< F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000005  Å  Ñ-  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000006  Å  Ñ-  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000007  Å  Ñ-  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000008  Å  Ñ-  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000009  Å  Ñ&  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000010  Å  Ñ&  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000011  Å  Ñ&  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
000012  Å  Ñ&  F  F               {82 âDRUGCRMK     ÅFÅР ÅF 224A5ZIP0
****** **************************** Bottom of Data ****************************
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby BillyBoyo » Thu Oct 18, 2012 7:34 pm

I asked for just the account reference and the packed bit, in hex. Reason being it is "pain" to count up to 51 even if I can get it all to line up with the Code tags.

You mention sorting Descending. You are telling the JOINKEYS Ascending. I don't know if that is significant yet, cos I don't have time to count far enough :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: ICETOOL - compare 2 input files

Postby bitbybit » Thu Oct 18, 2012 7:39 pm

The starting position is col-51 for the data I mentioned. Sorry abt that...Here is just the key fld for FL1 and FL2

 Å  Ñø<
06121674
9701990C
--------
 Å  Ñ-
06121662
9701990C
--------
 Å  Ñ&
06121652
9701990C


and for FL2
 Å  Ñø
06121670
9701990C
--------
 Å  Ñ-
06121660
9701990C
--------
 Å  Ñ 
06121640
9701990C
--------
 Å  Ñ 
06121630
9701990C
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby BillyBoyo » Thu Oct 18, 2012 8:55 pm

OK, some confusion.

At the start you talked of the first 12-bytes of a 15-byte field, COMP-3 S9(15).

That field is 8 bytes long containing 15 digits. Kolusu took account of that as the first six bytes from your start position.

However, looking more closely at what else you have written, do you perhaps have a 14-digit number in a 15-digit field, with a leading zero, and you want to compare everything except the last two digits?

Looking at your data, on the first 12 digits (including the leading zero) you have duplicates. Excluding the leading zero you don't.

Can you confirm this, or explain if I still haven't got it?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: ICETOOL - compare 2 input files

Postby bitbybit » Thu Oct 18, 2012 9:29 pm

That's true...that might be the issue...It is defined as s9(15) comp3, but the account# we use is only 14 digits. and I have to compare the first 12 bytes, that means I should be comparing on first 13 bytes of the s9(15) comp-3 that strts at pos-51 (or pos 55 for VB). Let em try that. Can you pls expalin what "REFORMAT FIELDS=(F1:1,4,5)" statement does?
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby bitbybit » Thu Oct 18, 2012 9:30 pm

got confused again...how do I reference the first 13 bytes as key in JOINKEYS commad?
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

Re: ICETOOL - compare 2 input files

Postby skolusu » Thu Oct 18, 2012 9:42 pm

bitbybit wrote:My o/p file has 49 records??, it should only have records from F1 except for 96710211969502 and 96710211969602
Your help is greatly appreciated.
Thanks.


bitbybit,

As I mentioned earlier , you might be having a many to many match by comparing just the first 6 bytes. Here is a verification job which shows you the contents at position 55 and counts the number of duplicates in each.
//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=Your Input VB File1,DISP=SHR
//SORTOUT  DD DSN=&&DS1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)   
//SYSIN    DD *                                               
  OPTION COPY                                                 
  OUTFIL VTOF,BUILD=(55,6,HEX,15:C'F1')                       
//*                                                           
//STEP0200 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD DSN=Your Input VB File2,DISP=SHR
//SORTOUT  DD DSN=&&DS2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)   
//SYSIN    DD *                                               
  OPTION COPY                                                 
  OUTFIL VTOF,BUILD=(55,6,HEX,15:C'F2')                       
//*                                                           
//STEP0300 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DSN=&&DS1,DISP=SHR                               
//         DD DSN=&&DS2,DISP=SHR                               
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                               
  SORT FIELDS=(1,16,CH,A)                                     
  INREC OVERLAY=(20:C'001')                                   
  SUM FIELDS=(20,3,ZD)                                         
//*                   


Look at the output from step0300 and check if any of the records have a total of more than 1 in pos 20.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: ICETOOL - compare 2 input files

Postby skolusu » Thu Oct 18, 2012 9:43 pm

bitbybit wrote:got confused again...how do I reference the first 13 bytes as key in JOINKEYS commad?


13? Didn't say you wanted to compare 12 bytes initially?
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: ICETOOL - compare 2 input files

Postby BillyBoyo » Thu Oct 18, 2012 9:52 pm

  REFORMAT FIELDS=(F1:1,4,5)


REFORMAT tells the JOIN what data is required from the two input files. F1: says the next fields are from File 1. 1,4 is for the Record Descriptor Word, the RDW, which is necessary for a variable-length record (which you have). 5 says "all the data from position 5 until the end of the current record.

The statement is giving you the entire record from File 1 when a match has occured.

Currently, you are getting the same record multiple times, because as they key was previously described, it is not unique.

Forget bytes. Talk about digits as you have a packed-decimal field, and you get two digits per byte, except for the right-most byte, where you get one digit and the sign.

The problem is, your "unique" part of the number ends up half-way through a byte, not at the end of a byte, as you previously described.

This will require the insertion of a new key, perhaps most easily done using data-type PD0.

You will need JNFnCNTL files to be able to do the change (control cards specifically for each individual file on the JOIN) and you will need to insert your key in the fixed part of your variable-length record. The easiest place is at the "front" of the record.

Confirm from Kolusu's latest that you are getting the multiples, and we can suggest some code to process the new interpretation of your data.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: ICETOOL - compare 2 input files

Postby bitbybit » Thu Oct 18, 2012 9:56 pm

But, BILLYBOYO pointed it out that, since the field is defined as s9(15)comp-3, we should be comparing first 13 bytes....
For example --- here is one acct# in hex

Act# 96710211969700

Å Ñø
06121670
9701990C

I should either match on first 13 (which is 0967102119697) or just first 12 digits of acct# (which is 967102119697). I think what BILLYBOY was pointing out is we were doing a match on 096710211969.
for testing I only changed last 12th byte of acct# to get a diff actt#. So it was matching on all of the records. If we do the match on first 13 bytes of s9(15) comp-3, then it will work, that's why I asked how to refer to first 13 bytes?
bitbybit
 
Posts: 29
Joined: Fri Jul 13, 2012 10:05 pm
Has thanked: 6 times
Been thanked: 0 time

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post