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
Lookup null value on DB2 - different for sparse and normal
Moderators: chulett, rschirm, roy
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>