Lookup null value on DB2 - different for sparse and normal

dohertys · Post by **dohertys** » Tue Mar 10, 2009 5:48 am

I've got a job which has tries to lookup on db2 using some nullable fields.

If I use a 'normal' lookup, the lookup correctly finds the matching row on db2 that has null values in the fields...

however, if I use a sparse lookup then it does not find the matching row.

Is there a difference between how nulls are handled for the sparse and normal lookups against db2 ? Any other ideas for what I might be doing wrong?

Thanks

ArndW · Post by **ArndW** » Tue Mar 10, 2009 6:21 am

Hello Steve,

there is nothing fundamentally different in how nulls work that I'd noticed before. I trust that both the job metadata as well as the database definitions are nullable for all those fields and are identical with each other, but I'm sure you've already double-checked that. Can you make a simple test case with just one (nullable) column as the key and see if there is a difference or perhaps it just affects the outcome with more than one column.

dohertys · Post by **dohertys** » Tue Mar 10, 2009 8:09 am

Hi Arnd.

I've set up a test job with just 1 key column and using 1 input record which should match with the one record on the table ( both have null in the field I've set as the key in the datastage lookup).

If I set the lookup type to 'normal' the lookup finds the record. If I set it to 'sparse' the lookup doesn't find the record.

Both the input and the reference links have the metadata set to allow nulls in that column.

ArndW · Post by **ArndW** » Tue Mar 10, 2009 10:58 am

Steve - that sounds like a reproduceable test case for IBM support. Do you have an Oracle instance floating around to test on; I wonder if it is DB2 specific or sparse-lookup specific.

dohertys · Post by **dohertys** » Wed Mar 11, 2009 4:45 am

I'll try and get it raised with IBM then.
I've not got easy access to another database yet, but I'll give that a go when I can.

Thank Arnd.