Here the first 5 digits are account number. Then we have the table name position 6 to 8. Then we have the record content. Now aim is to remove duplicates records based on table index key.
For ASA table the key is at position from column 9 to 11.
But for ASB table the key position is column 12 to 14.
Could you please help me a solution?
Let me know if more information is required from my side.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Here's a DFSORT job that will do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but you can change the job appropriately for other attributes.
I require some more help from you. For the above case I forgot to tell the following
I have 40 tables in my input file and all the tables are having
1. different number keys
2. different data types for the keys
Hence this will increase the number of keys to be specified sort fields. For my case there are approx 20 keys in the SORT FIELDS.
My file is a VB and LRECL = 3000, since we are using the OUTREC BUILD hence 'NUMBER OF BYTES SORTED' will also increase.
My input file is having more than 20 million records. Will it substanially reduce the performance. If so is there a way to fine tune it??
My table and exact keys are given in the attachment.
Of course, you'd have to adjust that for multiple keys.
Adding 20 keys this way may have an impact on performance. The only way to know if that performance impact would be a "problem" for you would be to try it and see.
Hi,
Ekta: I dont think we have anything equivalent to IFTHEN and WHEN in Syncsort, but for some operations where we need less number of selection criteria we can use 'Include' and perform that task.
The solution is working, but i am not able to understand the solution.
Can you please explain about the column positions specified in INREC BUILD and OUTREC BUILD and what BUILD does?
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Code:
INREC IFTHEN=(WHEN=(10,3,CH,EQ,C'ASA'),BUILD=(1,4,5:13,3,8:5)),
IFTHEN=(WHEN=(10,3,CH,EQ,C'ASB'),BUILD=(1,4,5:16,3,8:5))
SORT FIELDS=(8,8,CH,A,5,3,CH,A)
SUM FIELDS=NONE
OUTREC BUILD=(1,4,5:8)
/*
The first INREC IFTHEN clause says: when an input record has 'ASA' in 10-12, create a reformatted record with the RDW in positions 1-4 followed by input positions 13-15 and input positions 5 to the end of the record. For the first ASA record, that would be:
|RDW|123|12345ASA123|
The second INREC IFTHEN clause says: when an input record has 'ASB' in 10-12, create a reformatted record with the RDW in positions 1-4 followed by input positions 16-18 and input positions 5 to the end of the record. For the first ASB record, that would be:
|RDW|456|12345ASB123456|
So now we have the account number and table name starting at position 8 and the key starting at position 5. The SORT statement sorts on those fields in that order.
SUM FIELDS=NONE eliminates duplicates.
The OUTREC statement says: create an output record with the RDW in positions 1-4 followed by positions 8 to the end of the record. This removes the key we added and gives us back the original record. For the first ASA and ASB record, that would be:
