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
OCI Stage - Insert then Update Vs Update then Insert
Hi all,
today i have noticed similar problem.
When i use Insert new rows or Update existing rows OCI stage does not insert new rows while if i use Update existing rows or Insert new rows OCI stage inserts new rows.
This is curious!!!
I am running DS4.0 on Bull/IMB-AIX.
Regards,
Ricccardo
----- Original Message -----
From: "KH Kuan"
To:
Sent: Wednesday, October 17, 2001 11:23 AM
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
>
>
today i have noticed similar problem.
When i use Insert new rows or Update existing rows OCI stage does not insert new rows while if i use Update existing rows or Insert new rows OCI stage inserts new rows.
This is curious!!!
I am running DS4.0 on Bull/IMB-AIX.
Regards,
Ricccardo
----- Original Message -----
From: "KH Kuan"
To:
Sent: Wednesday, October 17, 2001 11:23 AM
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
>
>
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
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
I have also had problems with insert/update & update/insert. I now split the update / insert in all my jobs.
I will still create one job, but with a source file and a lookup (which reads from the target). I then have 2 outputs, one for insert and one for update. In the tranform, if the record exists, I follow the update path (with action "update existing rows"), if it doesnt exist, I follow the insert path (with action "insert without clearing").
It is a little bit more to do, but it works well for me.
Dirk
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: Wednesday, October 17, 2001 12:07 PM
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 message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
I will still create one job, but with a source file and a lookup (which reads from the target). I then have 2 outputs, one for insert and one for update. In the tranform, if the record exists, I follow the update path (with action "update existing rows"), if it doesnt exist, I follow the insert path (with action "insert without clearing").
It is a little bit more to do, but it works well for me.
Dirk
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: Wednesday, October 17, 2001 12:07 PM
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 message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
.... and just for interests sake, we are using DS 4.2.1.R2 on HP-UX 11.00
-----Original Message-----
From: Dirk Moolman [mailto:dirkm@reach.co.za]
Sent: Wednesday, October 17, 2001 12:18 PM
To: datastage-users@oliver.com
Subject: RE: OCI Stage - Insert then Update Vs Update then Insert
I have also had problems with insert/update & update/insert. I now split the update / insert in all my jobs.
I will still create one job, but with a source file and a lookup (which reads from the target). I then have 2 outputs, one for insert and one for update. In the tranform, if the record exists, I follow the update path (with action "update existing rows"), if it doesnt exist, I follow the insert path (with action "insert without clearing").
It is a little bit more to do, but it works well for me.
Dirk
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: Wednesday, October 17, 2001 12:07 PM
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 message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
-----Original Message-----
From: Dirk Moolman [mailto:dirkm@reach.co.za]
Sent: Wednesday, October 17, 2001 12:18 PM
To: datastage-users@oliver.com
Subject: RE: OCI Stage - Insert then Update Vs Update then Insert
I have also had problems with insert/update & update/insert. I now split the update / insert in all my jobs.
I will still create one job, but with a source file and a lookup (which reads from the target). I then have 2 outputs, one for insert and one for update. In the tranform, if the record exists, I follow the update path (with action "update existing rows"), if it doesnt exist, I follow the insert path (with action "insert without clearing").
It is a little bit more to do, but it works well for me.
Dirk
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: Wednesday, October 17, 2001 12:07 PM
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 message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
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
>
>
>
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
>
>
>
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
*************************************************************************
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
*************************************************************************