Just to clarify what I said earlier, we avoid insert/update but have had no problems with update/insert.
If it was appropriate to your processing requirements, I would have no hesitation in recommending update/insert.
I would track down and address the jobs with insert/update but I wouldnt worry about the ones that use update/insert. In fact, the simplest solution that I would recommend is to change all your insert/update to update/insert.
Think about the update/insert for a moment. If the update fails due to some constraint and DataStage fails to report this, it will detect 0 rows processed and attempt the insert. This will most likely fail due to a primary key violation, but, nonetheless, will fail.
It may not be fair to say that DataStage never reports errors on the first of the statement pairs, but experience has shown that there certainly times when it fails to, especially with insert/update.
-----Original Message-----
From: KH Kuan [mailto:
kuan@clear.net.nz]
Sent: Thursday, October 18, 2001 7:42 AM
To:
datastage-users@oliver.com
Subject: RE: OCI Stage - Insert then Update Vs Update then Insert
We are on DS 4.0.2 with ORAOCI 2.3 on Solaris 2.6.
If you are right in DS not reporting errors on the first statement, then there are potential for rejects not reported using either. Ill test update/insert when I get a chance.
Most of my other jobs, I have a lookup and then an insert only link and an update only link. From now on, this is the only way for me! Cant image the number of jobs out there with insert/update or update/insert actions!
Cheers
> -----Original Message-----
> From: David Barham [mailto:
david@barham.hm]
> Sent: Wednesday, 17 October 2001 11:07 p.m.
> To:
datastage-users@oliver.com
> Subject: RE: OCI Stage - Insert then Update Vs Update then Insert
>
>
> I think the logic of insert/update goes something like this ...
>
> Try the insert
> If number of rows processed = 0 then
> Do the update
>
> Conversely, the update/insert is like this:
>
> Try the update
> If number of rows processed = 0 then
> Do the insert
>
>
> It is my understanding that DataStage does not report the errors on
> the first statement of each pair.
>
> Also, this is an area where there have been bugs and changes across
> different versions of the ORAOCI8 plugin. I think your situation is a
> specific example that Ascential was aware of some time ago. Which
> version of DataStage are you using?
>
> I would suggest you try the update/insert instead. We have generally
> found this to be more reliable in reporting errors. Strangely enough,
> it isnt necessarily slower, even when you are mostly inserting rows.
>
> While typing this message, I noticed the response from Riccardo where
> insert/update fails to insert rows that update/insert successfully
> inserts. We have experienced the same thing, although it was back in
> DataStage 3.5 (ORAOCI8 version 2.2). I know Ascential have been
> working on some of these
> problems so I would hope that some of them have been fixed by now.
> DataStage 3.6 through 4.1 comes with ORAOCI8 ver 2.3. DataStage 4.2 comes
> ORAOCI8 version 3, which has some significant new features (and new bugs).
>
> Bottom line, we ALWAYS use update/insert, never insert/update.
>
> The other point that I should make is that you should give
> consideration to doing a lookup first to see if the row is already
> there, and then using separate links for the insert and the update. I
> have found that in most cases, I already know if I need to do an
> insert or an update, so I dont often use update/insert. This is
> mainly because I usually want to do slightly different processing
> depending on whether or not the target row exists.
>
> Hope this helps.
>
> David
>
> -----Original Message-----
> From: KH Kuan [mailto:
kuan@clear.net.nz]
> Sent: Wednesday, 17 October 2001 7:23 PM
> To:
datastage-users@oliver.com
> Subject: OCI Stage - Insert then Update Vs Update then Insert
>
> Hi All
>
> Ive noticed something curious with update actions Insert new rows or
> update existing rows versus Update existing rows or insert new
> rows. We have a target table with all columns defined as not null.
> The source file have several rows where some columns are empty
> string/null. Therefore, Id expect the job to reject some rows.
>
> When the target OCI stage update action is set to Insert new rows or
> update... the job completed with a status Finished and no rejects!
> Checking the table, it did NOT insert all the rows as shown on the
> link counts. Changing the status to Update existing rows or insert...
> the job completes with warnings as expected.
>
> Can someone explain why Insert then Update failed to log the warnings?
> I expect this job to do mostly inserts and hence I thought it would be
> more efficient trying to insert first then update.
>
> TIA
> Hong
>
>
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************