DSXchange

Dear all,

We are using DS 6, with DB2 7.2 both on AIX.

One of our job design is to read from a table, lookup the key values from a hash file, if the keys already in the hash use the value from the hash, if not found write new value to the hash and use the value (from the hash and/or the new value) to another table.

The lookup process is using a LookupRoutine.

The thing is, we are developing the jobs in a development Project (DSS_D) and development database (STGDB), and the job works perfectly.

But when we move the job to test project (DSS_T) and using test db (STGTS), the job lookup process didn't work, and the we always get the duplicate key error because the job keep writing the same key to the next table.

Both DSS_T and DSS_D, as well as STGDB and STGTS are in the same AIX machine, but STGDB in stginst1 instance, and STGTS in tstinst1 instance.
During development we make stginst1 as the main DB2 instance for DS, but since we found this problem in test environment, we change tstinst1 as the main DB2 instance for DS.

If we run the job in DSS_T or DSS_D using the same data and using STGDB, the job works perfectly. But if we run in DSS_T or DSS_D using STGTS the job always failed.

We already make the configuration of both instances and both databases to be exactly the same, but the same problem still happen.

Can anyone help me? Because I have no idea what's wrong, and I don't know what else to do. [V]

Please help me [:(] [:(] [:(]



Regards

ND

There are 2 things I would consider:

1. is it possible that STGDB does not have a primary key constraint enabled during the load, and STGTS does? You may be getting duplicates in STGDB but just don't know it.

2. you say you are using a routine for the lookup, do you have 2 hash files for the lookup: one for the DSS_D project and one for the DSS_T project and does the routine change which hash file it is working with?

Hope this helps,
John

ND

It looks like you are trying to do an incrementatal load using hash files. The hash file needs to be reloaded to match what is in the target before this job runs then the lookup will tell you if it is in the target table.

Thanks Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com