Update vs Insert in ETL

seell · Post by **seell** » Mon Nov 17, 2003 1:11 am

hi
I seem to encoutner a curious situation when trying to update a table (key, code, desc). My intent is to update if the code exist else insert.

The table is first extracted out with the key and code as lookup.
Transformation logic as follows
Input file (code, desc) has 2 rows of the same code.
The first row is updated into the table
The 2nd row is inserted (this should still be update ??)

I have used the check in the transformation for the key : if code exist use the key for the code else insert with key = @OUTROWNUM
Processing option for the table : update existing or insert new rows.

Thank you
Regards
SLL

Amos.Rosmarin · Post by **Amos.Rosmarin** » Mon Nov 17, 2003 3:43 am

Hi,

If i'm following you correctly you have a stream input data you want to put in a database table where some of the rows exists and therefor need to be updated whiole the others are new and need to be inserted.

If so you have 2 options:
1. Mark the key column as your key in the column properties and use the 'insert new or update existing' option (or the opposite ).
2. Read only the key column from your target table into hash file.
Do a key lookup using the hash a reference. If the lookup.NOTFOUND = @TRUE goto an insert step else (found) it's an update.
If you source can have duplicate new record there is a risk of doing multiple inserts with the same key. In this case you must do a lookup on the table itself with dirty read or commit after each row.

HTH,
Amos

Peytot · Post by **Peytot** » Mon Nov 17, 2003 7:15 am

Why do you not use the Option Upsert ( = Update or Insert). If you have the same description of your two flows, you can do an Upsert. DataStage checks for you if the row already exist or not.
Like that, you are nothing to put in your constraint and you have one file.

Pey

clshore · Post by **clshore** » Mon Nov 17, 2003 1:37 pm

I didn't see that you specified what DB you are using.
Oracle 9i supports 'upsert' and so does the DS OCI9 stage.
The DS version is a bit restrictive (updates when PK's match, else inserts).
Although the actual Orcale SQL statement that implements upsert is a bit more complex (merge?, see the docs), it can be coded. In fact, I've already had to roll some custom SQL, as my requirements called for more than simple PK equality.

Carter

seell · Post by **seell** » Mon Nov 17, 2003 7:15 pm

I am using Oracle 9.2.0.4;
Where could I find the relevant documentation on upsert ?? I could not find it in the Datastage designer manual.

Thank you
Regards

Teej · Post by **Teej** » Mon Nov 17, 2003 10:10 pm

seell wrote:I am using Oracle 9.2.0.4;
Where could I find the relevant documentation on upsert ?? I could not find it in the Datastage designer manual.

Thank you
Regards

Oooooooo, 9.2.0.4. *winces* DataStage 6x is not very... perfectly compatible with Oracle 9.2.0.3 and 9.2.0.4. In our brief voyage onto that version from 9.2.0.2, we caught three individual critical issues with 6.0r3 that was only partly addressed on 6.0.1. Oracle decided to majorly change their behavior for 9.2.0.3 compared to 9.2.0.2 that goes against the general understanding that Ascential developers had when working with Oracle. The issues caused us to regress to 9.2.0.2.

I do not think Ascential currently support that particular version for 6x, although I believe it is fully addressed for 7x. So, your unusual behavior observation could be yet another bug caused by the change between 9.2.0.2 and 9.2.0.4.

-T.J.

chulett · Post by **chulett** » Tue Nov 18, 2003 7:02 am

T.J. -

Care to elaborate on the issues you found? We are running the version mix you noted (9.2.0.4 and 6.0.1) on HP/UX and now you've got me a little... scared.

Or was any of this specific to Tru64?

mhester · Post by **mhester** » Tue Nov 18, 2003 8:00 am

We have been running this combination (DS 6x and 9.2.0.4) for quite some time (August) and have not experienced any problems with DS or Oracle. This may be platform specific. We are a Sun running Solaris 8.

Regards,

Michael Hester

Teej · Post by **Teej** » Tue Nov 18, 2003 4:38 pm

chulett wrote:T.J. -

Care to elaborate on the issues you found? We are running the version mix you noted (9.2.0.4 and 6.0.1) on HP/UX and now you've got me a little... scared. Or was any of this specific to Tru64?

Now that I'm at work, I can dig out the specifics that we found. We only located it with regards to PX programming, especially with CustomOPS stages.

First of all, it was 9.2.0.3 and 9.2.0.1, so my apologies.

1. Oracle's Partitioned Table would not work. This was supposed to be fixed for 6.0.1.

2. If you use Number on Oracle, it is imported differently using 9.2.0.3 compared to 9.2.0.1. This really affect CustomOPS stages we had using Orchestrate libraries. On 9.2.0.3, they see it as Int32. On 9.2.0.1, they see it as Decimal[38,10]. This is supposed to be fixed for 7.0.

3. Related to #2, the way Oracle report NULL for length of fields, the value was changed. Well, actually, two values were supposed to be provided for 9.2.0.1, and the one that Ascential was using was removed for 9.2.0.2. At least that is what my fuzzy memory claims.

* * *

When we found out that the fix was slated for 7.0, we pretty much rolled back, without investigating other strange behaviors with PX and 9.2.0.3. We have not upgraded to either 7.0 or 9.2.0.3 since then, so I do not know if everything was perfectly addressed.

So when I saw this post, those nasty flashbacks came rushing back last night. Hehe. We spent a week trying to figure out what was going on until the DBA finally piped up, "Oh, we upgraded..."

-T.J.