Unable to INSERT and UPDATE

[shamshad]

DataStage Version: 5.2
I have a CSV file that contains data (Both old as well as new). I need to UPDATE the existing records and INSERT the NEW records. Below is my CSV Data
ProdCode Name Type
90 Shirts U
91 Pants T
92 Shoes M
93 Tie K

I am creating a HASH file from the DB2 table and making "ProductCode" as KEY Column and for LOOKUP. Then I am taking two different links from the Transformer, One for UPDATE and other for INSERT.
For update I am using "Updating existing Rows only" in my target Table. For insert I am using "Insert Rows without clearing" and
doing a lookup for ProductCode from CSV to Hash File. In the Insert link, I am putting "IsNull(LookupProdCodes.ProdCode)" as constraint.

Update is working fine, but for "INSERT" its inserting all the ROWS from the CSV, I wanted to insert only NEW RECORDS. Please help me. I need this to be done today itself.

[ogmios]

Quick and easy fix for you: use one link to the database and set it to update/insert with the correct primary key et voila. No need to do a lookup, all of your rows will be processed (either as update or insert), and this is what you want in your case I think.

For the rest your job as it is now looks ok to me at first sight, but you would need to give additional information as order of links...

Ogmios

[chulett]

More than likely - if you are sure you are correctly populating the hash file with the current business keys in the table - you are facing a trim problem. Perhaps the key field has trailing spaces and your csv data does not, or vice versa? That would cause every lookup to fail.

Also, when you insert new records do you also write those key values to the same lookup hash file? You'd need to do that if duplicates could exist in the source data.

[shamshad]

OK, here is what I am doing step by step

(1) Get ProductKey and ProductCode from Target Table (Only Distinct
values)

(2) Sending it to a HASH file through a transformer after removing any
spaces using TRIM for both ProductKey and ProductCode.
ProductCode is defined as "KEY"

(3) Get the Values from CSV. In CSV there is NO PRODUCTKEY as it is
generated using "KeyMgmtNextValue" while loading the FIRST TIME.
The values from CSV are PRODUCT CODE, NAME, TYPE.

(4) Using an Aggregator to get Distinct ProductCode,Name,Type. Here
Product is defined as "KEY"

(5) Now lookup in the (2) and send one link to UPDATE where the action
type is "Update existing Rows only". This works fine. Here I am
updating ProductName, and Type.

(6) Send another link as "Insert" with a constraint "IsNull
(LookupSegCodes.PRODUCTCODE)". In this link the database action
is "Insert new rows without clearing". Here I am inserting
Product Key as "keyMgmtNext..", ProductCode and Type from CSV.

Update works fine, but insert INSERTS ALL RECORDS from CSV.

[KeithM]

Your lookup to the product code hash file is not working. You said that when you build the hash you are trimming the product code. Make sure that the value you send to lookup the code is also trimmed. If there are any trailing spaces you are not going to get a match and everything will be reinserted as you are seeing.

Also, you say that the updates stream is working. How would you know that if everything is going through the insert stream? Shouldn't the record either be going through the insert or update and not both?

[shamshad]

I used different methods and this seems to work fine. Instead of checking for NULLS I am checking if it returns any Values or not.

I REPLACED IsNull(LookupProdCodes.ProdCode) with
Len(Trim(LookupProdCodes.ProdCode))=0 for INSERT
Len(Trim(LookupProdCodes.ProdCode))>0 for UPDATES

It works!!! Thanks for all your email. I greatly appreciate it.

[ray.wurlod]

Luckily for you, LEN(@NULL) returns zero!