Key column in Hashed File

[ravij]

Hi,

I am loading the data from db2 to Hashed file. I have a key column in Hashed file. suppose if the key column is having 5 same values which value out of 5 will store in the hashed file either first or second.....fifth?

thanks in advance.

[balajisr]

The last one.

--Balaji S.R

[ravij]

thanks Balaji,

But if i want to take the first record what should I do?

thanks in advance.

[balajisr]

Think whether is it necessary to use Hashed file?

How is the value stored in DB2. Is there any primary key for the table you are loading. How does table identify unique rows? Make the primary key of DB2 table as key of hashed file.

--Balaji S.R

[ravij]

Hi Balaji,

I will give a simple scenario here. I am loading two columns into Hashed file from DB2. one is Key col. suppose the data is like

key col1 col2
10 x
10 y
10 z
when I load the data into hashed file i will get only the last record i.e 10, z but i want the value x to be loaded into my hashed file. What is the procedure to do it?

any answer can be appreciated.

[kalpna]

use an aggregator..
group by key
retreive first(col1)

kalpna

[balajisr]

As Kalpana suggested you can use aggregator to get the first row grouped by the key.

You can also use transformer to get the first row of the key using stage variables.

--Balaji S.R

[ameyvaidya]

Before loading to the Hashed file, SOrt and use a transformer to select and pass out the first row for a given key value.

[chulett]

Order your DB2 query by 'col2 descending'.

[AGStafford]

You can create a job like:

Code: Select all

               Hash File A Lkup
                        |
                        V
DB2 ---------> Transformer ---------> Hash File A

In the Transformer, the Constraint for Hash File A would be
If IsNull(Hash File A Lkup.field) Then @TRUE Else @FALSE

So if the row does not exist in the Hash file, write it, otherwise do not write the row. This way only the first row ends up being loaded.