Query on Lookup file set

[devidotcom]

Hi All,

I never used a lookup fileset. Tried doing a sample job...

I created a lookup fileset using 5 columns with entire partition on it.
Lets say the columns in lookup fileset our
record {LUTVersion="1"}
( EMP_ID: int32;
EMP_NAME: string[max=15] {dropped};
EMP_AGE: int32;
EMP_ADDRESS: string[max=40];
EMP_DESG: string[max=10];
EMP_DEPT: string[max=15];
fileNameColumn: string;
)


Later used this lookup fileset to match one of the column Emp_Name.
I created an input file with Emp_Name column which has two values that matches and one column that does not match.
I received the output as required.

Ihave a query in the lookup stage that uses the lookup fileset. It did not allow me match the lookup column. It gave this window with message...

"Key expressions cannot be set on the key column of this link. The connected stage defines the key lookup."

1. Does the name of the input link match that of the reference link.
2. What if my input link name is different should it be changed before the lookup stage.

Inputs apprepricated.

Thanks
Devi

[ray.wurlod]

It's the name of the COLUMN that must match - one of the rules in DataStage designer is that the links connecting to a stage must be uniquely named. Therefore, yes, if the column names are different in the column(s) used for matching, you need to effect a name change. The Copy stage is the least cost stage type for this, unless you already have a stage in place that could implement the name change.

[devidotcom]

Thank you for the reply.

Noticed one more thing. When I saw the partitioning type of the reference link from the lookup fileset it says:

"The currently selected link is a reference link from a lookup fileset stage or a stage thats uses a sparse lookup. There is no partitioning or collection properties that can be set for this type of input link, since this stage and the related stage operate as a single stage when the job is run"

I had created the lookup file set with an entire partition.

Any inputs appreciated.

Thanks
Devi

[ray.wurlod]

Partitioning of a Lookup File Set is "locked in" when the Lookup File Set is created - the index is based upon the indexed data. So whatever you add to the design is ignored. If this is something other than Auto, DataStage issues the alert to tell you that your instruction will not be heeded.

[devidotcom]

Thank you for the reply.