Datastage Corss-Reference

[ray.wurlod]

What exactly do you mean by a cross reference table? DataStage processes data row by row. It can perform reference lookups where it obtains a value typically from the row being processed, and uses that value to request one or more rows from another table, for example SELECT col1, col2, col3 FROM table WHERE col1 = ?;
Graphically, this is achieved using a reference input link to a Transformer stage. The second and subsequent input links to such a stage type are automatically reference input links, though it is possible to change later, but only one input can stream the rows being processed. The Transformer stage generates the value (in a "key expression") which is used to search for rows in the table on the other end of the reference input link. Depending on the stage type on the other end of the link it may or may not be possible to return more than one row. Only the ODBC and UV stage types can return more than one row.

[vmcburney]

As Ray says a reference link seems to be what you are after. Another option is to cross reference your tables within the source database if they both exist in the same database. This can be done by writing a custom SQL select statement in your database stage or by writing a database view that joins the two tables in your database.

If your two sets of data exist on seperate databases or in text files you will need to set one up as an input stream and the other up as a reference link. It is a common practice to load reference data into a hash file from a database or flat file as a hash file is the fastest way to perform a reference lookup.