IBM Mainframe Forum

by **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

Select all

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

Select all

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 ****************************

by **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 :-)

by **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

Select all

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

and for FL2

Select all

Å Ñø
06121670
9701990C
--------
Å Ñ-
06121660
9701990C
--------
Å Ñ
06121640
9701990C
--------
Å Ñ
06121630
9701990C

by **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?

by **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?

by **bitbybit** » Thu Oct 18, 2012 9:30 pm

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

by **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.

Select all

//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.

by **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?

by **BillyBoyo** » Thu Oct 18, 2012 9:52 pm

Select all

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.

by **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?

IBM Mainframe Forum

ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files

Re: ICETOOL - compare 2 input files