JOIN KEYS, is it possible to specify varying key length?



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

JOIN KEYS, is it possible to specify varying key length?

Postby letmelive » Tue Oct 09, 2012 12:03 am

I have two input files and I need to compare them and get the matched records in one file and unmatched in another output file.

There are two key fields -=
Keys (start position , length)
file1 - key1 - (1,128)
file1 - key2 - (136,128)

file2 - key1 - (1,128)
file2 - key2 - (129,128)

I face problems in acheiving this task due to data in the key fields.
File1 is output of a DB2 query. Key1 & key2 are table name and creator name respectively. These fields are of variable length (max 128 characters) in database and therefore whenever the length is less than 128, x'00' is appended at the end. And another important thing is that trailing spaces in table name and creator name are considered as valid characters meaning that length of the table name or creator name includes trailing spaces and x'00' will be appended from length+1 position onwards.

For eg: if the table name is 'employee ' (2 trailing spaces) this is how my input file 1 will be

=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
****** ***************************** Top of Data ******************************
000001 EMPLOYEE                                                               
       CDDDDECC4400000000000000000000000000000000000000000000000000000000000000
       547368550000000000000000000000000000000000000000000000000000000000000000


File2 is imported from a spreadsheet. Trailing spaces are not found in table names or creator names. I'm appending x'00' to the end to make them 128 characters long. Hence my file2 will be like this
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
****** ***************************** Top of Data ******************************
000001 EMPLOYEE                                                               
       CDDDDECC0000000000000000000000000000000000000000000000000000000000000000
       547368550000000000000000000000000000000000000000000000000000000000000000


Though I can replace x'40' with x'00' in file1 and then do a compare, I dont prefer it as I need those records in my output without being altered. If I have their length, ignoring trailing spaces in file 2 then how can I use this varying field to indicate the key length in JOIN KEYS syntax?
letmelive
 
Posts: 20
Joined: Thu Oct 04, 2012 1:27 am
Has thanked: 6 times
Been thanked: 0 time

Re: JOIN KEYS, is it possible to specify varying key length?

Postby BillyBoyo » Tue Oct 09, 2012 5:27 pm

You can't have a variable-length key.

What you are going to have to do is "normalise" the keys. Meaning get them into the same format. One or other is changed, or both are changed, depends what you can arrange. You don't have to mess with the originals, you can append/prepend a new area for the key. If you have your data untouched (key now seperate) is it enough for you to go with the x'40' to x'00'?

To know whether to append (for fixed-length records) or preprend (for varying-length records) you need to know the RECFM :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: JOIN KEYS, is it possible to specify varying key length?

Postby letmelive » Wed Oct 10, 2012 10:13 am

Thank you very much.. I will try this out.. It never strike me that I shall append keys themselves.. I accomplished the task in a round-about way, wrote a cobol program to introduce a new key 'record number' in file1. Then changed spaces in key columns to null, writing the output to a temp file & went on to compare the temp file with my file2, writing the matched and unmatched records into separate files. Finally did a compare with file1(output of cobol program) with record number as key in oder to reconstruct the original record.. I'd complicated the solution :)

is it enough for you to go with the x'40' to x'00'?

yes.. thats pretty much enough.. I dont see any problem with other characters..

To know whether to append (for fixed-length records) or prepend (for varying-length records) you need to know the RECFM

Both the files are FB files.. That way I could save time spent on adjusting the positions in sort cards..
letmelive
 
Posts: 20
Joined: Thu Oct 04, 2012 1:27 am
Has thanked: 6 times
Been thanked: 0 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post