Failed a keylookup for record

ThilSe · Post by **ThilSe** » Wed Nov 02, 2005 1:20 am

Hi,

I am getting hte following error when executing a job
"Failed a keylookup for record"

The lookup key is of Integer type. There are 10 primary records and required 10 records key is present in the Lookup table. But still the job fails.

Can anyone throw more light on this?

Thanks
Senthil

ArndW · Post by **ArndW** » Wed Nov 02, 2005 1:27 am

hello ThilSe,

the information isn't really enought to give an answer in this case; from the comments it seems that this used to work at 7.0 and no longer does after going to 7.5.1. It really boils down to DS thinking that the key isn't present while you think it is.

Normally problems finding keys on lookups boil down the partitioning in the job - what partitioning are you using on your lookup? Is this a lookup set or a different type? Does the error happen when you use a 1-node configuration?

ThilSe · Post by **ThilSe** » Wed Nov 02, 2005 3:48 am

ArndW,

I forgot to add this previously.
All the stages in the job are set as "Sequential ".

My job looks like this:

DB2Primary
|
|
`'
Lookup---->DB2Target
^
|
|
DB2Lkup

The same job runs correctly in test environment which is also DS7.5 but it aborts in production.

In prod, I have set the primary keys properly and also manually verified the records. The lookup keys match properly.

I can also view the records in both the DB2Primary and DB2Lkup stages without any warnings. The SQL query runs properly.

But when i execute the job it aborts saying Lookup failed.

Thanks/Regards
Senthil

ArndW · Post by **ArndW** » Wed Nov 02, 2005 5:03 am

You should try running this job in production with one node and/or set the lookup stage partitioning to 'complete' as I suggested earlier. It still looks like a partitioning problem.

Madhavan VM · Post by **Madhavan VM** » Wed Nov 02, 2005 6:52 am

Hi Senthil,

Before running the jobs do the following points

1. For the lookup to work properly in DS 7.5 the lookup keys should be represented as keys. To do this check the key column in the input tab for the lookup keys. ( Bottom left corner, column name, key, SQL Type... check the key column for the Look up keys).
2. Verify if hash partitioning without sorting is done on the keys for both source and reference
3. Verify whether the datatypes of the keys are matching for both source and target.

After this run and the job and let this forum be informed of how it went through.

ThilSe · Post by **ThilSe** » Wed Nov 02, 2005 10:43 am

Madhavan,

Thanks for your inputs
I have ensured the following:

1. The key column in the input column is checked.

2. The EXECUTION MODE property in the Advanced options tab of ALL the stages is set to SEQUENTIAL (this is due to business requirements). So the partition property has been DISABLED. I cannot set it.

3. The data types are matching.

4. Additionally, there is also KEY Expression property, in the Input lookup link of lookup stage, has been set to the key column.

But the job is throwing an error.

Will there be any problem by setting the EXECUTION MODE property as SEQUENTIAL?

When Execution mode is set as sequential in all the stages, will the data be partitioned?

Thanks/Regards
Senthil

ray.wurlod · Post by **ray.wurlod** » Wed Nov 02, 2005 2:14 pm

The same job runs correctly in test environment which is also DS7.5 but it aborts in production.

You must tell us what's different between the two environments (which may be DB2 client configuration).

"Nothing" is not the correct answer.

ThilSe · Post by **ThilSe** » Wed Nov 02, 2005 10:48 pm

Ray,

There is a difference.

the db2 version in Prod is 8.2.1
In test it is 8.2.3

Thanks
Senthil

ArndW · Post by **ArndW** » Thu Nov 03, 2005 1:59 am

Senthil,

can you run your job in production but point it to the original development database and see if the error might be due to a database version or configuration issue?