Getting Primary key column

[phillip.small]

I'm hoping someone can help me. I'm having a simple developer issue on trying to get one field to populate in the target table.
Basically, I'm loading a flat file to a target table, but want to grab one more field(which is a key field in the target table) for the target. The input file has 6 primary keys and is not going to the join link stage because that object does not have the position nbr column.

In DB2 SQL, that query looks like this:

select a.position_nbr
from taads_stg_hash(hash file) a, taads_rmks(sequential file) b
where a.deptid = b.uic_cd
and a.effdt = b.effdt

taads_stg_hash has:

position_nbr
deptid
effdt

taads_rmks seq file has:

deptid
effdt
Rank
location
end_effdt

The target table looks like:

position_nbr
deptid
effdt
rank
location
end_effdt

I'm having trouble getting the position_nbr from a hash file(or table) to load into the target table where the deptid and effdt match.

Some questions. Should I use a hash file? How do I get the the Position Nbr to populate?
Attached in this note is a zip file of the Ascential Job and the Tranformer. I'm not sure if I'm doing it correctly or I don't know what I'm missing. It seems simple to do, but I can't seem to get it to work via DataStage. Let me know if you need clarification. I don't know if I explained it very well. Thanks! I have the print screens to better show this if that would help. My email address is phillip.t.small@hoffman.army.mil.

The Input side of the Transformer looks like this.
The main datafile (object name: DataIN) has 6 primary keys which link to DSLink27 on the left side of the Transformer. DSLink27 has more columns than that but this one has the Position Nbr. I want to link DataIN --> DSLink27 by the 6 keys to generate the Position Nbr. Then that join will output to the DataOUT stage with the Position Nbr and some other fields.


DSLink27 originally has the POSITION_NBR and EFFDT columns as PK, however, in order to see the all the data we made all the bold column PK. This may or may not be a problem. But This link which is the hash file lookup is not pulling any data or reading at all and we cannot figure out why not. There are position numbers in the Hash file that we want to read in to the DataOUT link based on the key that have linked as you can see from DataINFile object. Below is the output and how it is ran. It is only pulling 263 records out of many many thousand records, plus those 263 are not going into the database.

Thanks for your help,
Phil

[ogmios]

Can you give more explanation. e.g. an ASCII diagram of your stages.

Remember DB2 SQL is executed in DB2 so it can't access hashfiles or sequential files....

Ogmios

[ogmios]

First check is to see whether the primary key of the hash file is stored in exactly the same way as the field that you get from the other input (no trailing spaces e.g.)

Ogmios

[phillip.small]

First check is to see whether the primary key of the hash file is stored in exactly the same way as the field that you get from the other input (no trailing spaces e.g.)

Ogmios

I trimmed everything but still no data.