Is there a way to report on the actual rows updated in the database load stage.
Im using the Sybase OC and BCP plugin.
regards,
Peter
Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Ray,
This may not always correlate with the actual rows inserted/updated on the database table.
eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
This may not always correlate with the actual rows inserted/updated on the database table.
eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Create a transform that creates a value indicitive of the load eg. the date, and load that value into a new column in the target table, then query the table for that value.
Regards
Adam
Peter Oates on 30/10/2001 10:03:32 AM
Please respond to
To: "datastage-users@oliver.com"
cc:
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Regards
Adam
Peter Oates on 30/10/2001 10:03:32 AM
Please respond to
To: "datastage-users@oliver.com"
cc:
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
But even a simple UPDATE can update more than one row if the key columns are not the primary key (which is a legitimate thing to do). To really achieve this from DataStage, it would have to ask the database how many rows were actually updated for each row passed to the passive stage and then record/accumulate this somewhere.
A lot of ideas have been expressed now regarding this, so it is probably up to Peter to figure out why he wants to know and which suggestion best answers this.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 3:29 PM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
A lot of ideas have been expressed now regarding this, so it is probably up to Peter to figure out why he wants to know and which suggestion best answers this.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 3:29 PM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Assuming each row sent from DataStage is intended to update exactly one row in the target table (see David Barhams note), if REJECTEDCODE is zero the update was successful, otherwise it wasnt. Lets say your output link is called OutputLink. On another output link, lets call it FailLink, you can generate a column called RejectedCode, and derive it as the value of the link variable REJECTEDCODE on the output link OutputLink (its derivation expression will appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit output (if you wish) to just rows rejected by the OutputLink link, and you could run FailLink into an Aggregator stage to get a summary result.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
I wish I could get DBMSCODE and SQLSTATE from the Basic macros.
From the doco its only available from the C API.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 5 November 2001 1:45 PM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
From the doco its only available from the C API.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 5 November 2001 1:45 PM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Hi Ray,
I dont know what is going wrong here. I try to follow your method but it does not seem to work. The REJECTCODE returns all "0" regardless of whether the database update is successful or not.
My input data contains 3 rows, the first two records are supposed to return SQL 100 (meaning not found). The last record should have update successful.
However, when I displayed the REJECTCODE by creating another link from the transformer stage and with derivation expression of OutputLink.REJECTEDCODE, they return all zero.
FYI, I currently have two output links from the transformer stage. One is to the OCI connection to an oracle table and the other is to a flat file recording all the reject code from the oracle table update.
Just wondering what I have gone wrong here.
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 05 November, 2001 6:48 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Assuming each row sent from DataStage is intended to update exactly one row in the target table (see David Barhams note), if REJECTEDCODE is zero the update was successful, otherwise it wasnt. Lets say your output link is called OutputLink. On another output link, lets call it FailLink, you can generate a column called RejectedCode, and derive it as the value of the link variable REJECTEDCODE on the output link OutputLink (its derivation expression will appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit output (if you wish) to just rows rejected by the OutputLink link, and you could run FailLink into an Aggregator stage to get a summary result.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
I dont know what is going wrong here. I try to follow your method but it does not seem to work. The REJECTCODE returns all "0" regardless of whether the database update is successful or not.
My input data contains 3 rows, the first two records are supposed to return SQL 100 (meaning not found). The last record should have update successful.
However, when I displayed the REJECTCODE by creating another link from the transformer stage and with derivation expression of OutputLink.REJECTEDCODE, they return all zero.
FYI, I currently have two output links from the transformer stage. One is to the OCI connection to an oracle table and the other is to a flat file recording all the reject code from the oracle table update.
Just wondering what I have gone wrong here.
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 05 November, 2001 6:48 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Assuming each row sent from DataStage is intended to update exactly one row in the target table (see David Barhams note), if REJECTEDCODE is zero the update was successful, otherwise it wasnt. Lets say your output link is called OutputLink. On another output link, lets call it FailLink, you can generate a column called RejectedCode, and derive it as the value of the link variable REJECTEDCODE on the output link OutputLink (its derivation expression will appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit output (if you wish) to just rows rejected by the OutputLink link, and you could run FailLink into an Aggregator stage to get a summary result.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
I suspect that the OCI stages dont set the link variables properly (link variables were introduced after version 2.3 of the OCI stage was written). I would check this with Ascential support if I were you. It definitely works OK with ODBC stages.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Tuesday, 06 November 2001 08:35
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hi Ray,
I dont know what is going wrong here. I try to follow your method but it does not seem to work. The REJECTCODE returns all "0" regardless of whether the database update is successful or not.
My input data contains 3 rows, the first two records are supposed to return SQL 100 (meaning not found). The last record should have update successful.
However, when I displayed the REJECTCODE by creating another link from the transformer stage and with derivation expression of OutputLink.REJECTEDCODE, they return all zero.
FYI, I currently have two output links from the transformer stage. One is to the OCI connection to an oracle table and the other is to a flat file recording all the reject code from the oracle table update.
Just wondering what I have gone wrong here.
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 05 November, 2001 6:48 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Assuming each row sent from DataStage is intended to update exactly one row in the target table (see David Barhams note), if REJECTEDCODE is zero the update was successful, otherwise it wasnt. Lets say your output link is called OutputLink. On another output link, lets call it FailLink, you can generate a column called RejectedCode, and derive it as the value of the link variable REJECTEDCODE on the output link OutputLink (its derivation expression will appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit output (if you wish) to just rows rejected by the OutputLink link, and you could run FailLink into an Aggregator stage to get a summary result.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Tuesday, 06 November 2001 08:35
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hi Ray,
I dont know what is going wrong here. I try to follow your method but it does not seem to work. The REJECTCODE returns all "0" regardless of whether the database update is successful or not.
My input data contains 3 rows, the first two records are supposed to return SQL 100 (meaning not found). The last record should have update successful.
However, when I displayed the REJECTCODE by creating another link from the transformer stage and with derivation expression of OutputLink.REJECTEDCODE, they return all zero.
FYI, I currently have two output links from the transformer stage. One is to the OCI connection to an oracle table and the other is to a flat file recording all the reject code from the oracle table update.
Just wondering what I have gone wrong here.
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 05 November, 2001 6:48 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Assuming each row sent from DataStage is intended to update exactly one row in the target table (see David Barhams note), if REJECTEDCODE is zero the update was successful, otherwise it wasnt. Lets say your output link is called OutputLink. On another output link, lets call it FailLink, you can generate a column called RejectedCode, and derive it as the value of the link variable REJECTEDCODE on the output link OutputLink (its derivation expression will appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit output (if you wish) to just rows rejected by the OutputLink link, and you could run FailLink into an Aggregator stage to get a summary result.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Ive been griping about this for pushing two years now. You are right, that functionality works fine for the ODBC stage, but not at all for the OCI or OCI8 stages. Youve got absolutely no way to know if the database has rejected your row programatically. You can see the mess it makes in your log, but the whole concept of the OCI stage rejecting rows (or properly setting the link variables) is either broken or has never been implemented. I was hoping that 4.x and the 3.0 plugin would have changed all of that, but it doesnt sound like it.
-craig
"Ray Wurlod"
cc:
Subject: RE: Reporting on rows updated in database
11/05/01 02:42
PM
Please respond
to
datastage-users
I suspect that the OCI stages dont set the link variables properly (link variables were introduced after version 2.3 of the OCI stage was written). I would check this with Ascential support if I were you. It definitely works OK with ODBC stages.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Tuesday, 06 November 2001 08:35
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hi Ray,
I dont know what is going wrong here. I try to follow your method but it does not seem to work. The REJECTCODE returns all "0" regardless of whether the database update is successful or not.
My input data contains 3 rows, the first two records are supposed to return SQL 100 (meaning not found). The last record should have update successful.
However, when I displayed the REJECTCODE by creating another link from the transformer stage and with derivation expression of OutputLink.REJECTEDCODE, they return all zero.
FYI, I currently have two output links from the transformer stage. One is to the OCI connection to an oracle table and the other is to a flat file recording all the reject code from the oracle table update.
Just wondering what I have gone wrong here.
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 05 November, 2001 6:48 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Assuming each row sent from DataStage is intended to update exactly one row in the target table (see David Barhams note), if REJECTEDCODE is zero the update was successful, otherwise it wasnt. Lets say your output link is called OutputLink. On another output link, lets call it FailLink, you can generate a column called RejectedCode, and derive it as the value of the link variable REJECTEDCODE on the output link OutputLink (its derivation expression will appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit output (if you wish) to just rows rejected by the OutputLink link, and you could run FailLink into an Aggregator stage to get a summary result.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
-craig
"Ray Wurlod"
cc:
Subject: RE: Reporting on rows updated in database
11/05/01 02:42
PM
Please respond
to
datastage-users
I suspect that the OCI stages dont set the link variables properly (link variables were introduced after version 2.3 of the OCI stage was written). I would check this with Ascential support if I were you. It definitely works OK with ODBC stages.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Tuesday, 06 November 2001 08:35
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hi Ray,
I dont know what is going wrong here. I try to follow your method but it does not seem to work. The REJECTCODE returns all "0" regardless of whether the database update is successful or not.
My input data contains 3 rows, the first two records are supposed to return SQL 100 (meaning not found). The last record should have update successful.
However, when I displayed the REJECTCODE by creating another link from the transformer stage and with derivation expression of OutputLink.REJECTEDCODE, they return all zero.
FYI, I currently have two output links from the transformer stage. One is to the OCI connection to an oracle table and the other is to a flat file recording all the reject code from the oracle table update.
Just wondering what I have gone wrong here.
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 05 November, 2001 6:48 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Assuming each row sent from DataStage is intended to update exactly one row in the target table (see David Barhams note), if REJECTEDCODE is zero the update was successful, otherwise it wasnt. Lets say your output link is called OutputLink. On another output link, lets call it FailLink, you can generate a column called RejectedCode, and derive it as the value of the link variable REJECTEDCODE on the output link OutputLink (its derivation expression will appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit output (if you wish) to just rows rejected by the OutputLink link, and you could run FailLink into an Aggregator stage to get a summary result.
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 05 November 2001 13:45
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Hello,
Refer to the email that Ray had said, how exactly can I use the active stage link variables to detect whether the row was rejected by the database or successful. Mind that there was no SQLCODE in the link variables. Currently, only the following values are in the output link variables:
DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October, 2001 6:29 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
If you use the output link variables on the active stage (Transformer?) that is driving the output link, you can detect whether the row was rejected by the database or successful. This could be used in conjunction with the row count on the output link to maintain statistics on another output link. Not sure how youd implement this if your write-rule is one of the double-barrelled ones (such as "insert or update", or "update or insert", or "replace" (delete then insert)), but I steer clear of these because I believe them to be inefficient. Prefer one link for "insert only" and another link for "update only".
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:40
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Ray,
This may not always correlate with the actual rows inserted/updated on the database table. eg. 10 rows out from the active stage but only 7 rows get updated because the other 3 dont exist in the database table.
I have tried to enter sql like select @@rowcount into the AfterSQL properties of the stage but it comes back with a warning. Its probably not expecting a return from the sql.
Peter
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 30 October 2001 10:24 AM
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Its in the logged event for the active stage finishing (use DSGetLog...
functions) and its available by asking the output link (use DSGetLinkInfo) for its row count.
-----Original Message-----
From: Peter Oates [mailto:Peter.Oates@macquarie.com]
Sent: Tuesday, 30 October 2001 10:04
To: datastage-users@oliver.com
Subject: Reporting on rows updated in database
Is there a way to report on the actual rows updated in the database load stage. Im using the Sybase OC and BCP plugin.
regards,
Peter
Yes, but what version of the OCI stage are you using? And what version is Paul using?
-----Original Message-----
From: kuan@clear.net.nz [mailto:kuan@clear.net.nz]
Sent: Tuesday, 06 November 2001 12:37
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Paul
We use this technique with all our jobs and it works fine. Check that you are interrogating the right REJECTCODE, the link to your OCI stage. Try setting the transaction size to 1 to see if makes any difference. Shouldnt.
HTH
>Hi Ray,
>
>I dont know what is going wrong here. I try to follow your method but
>it
>does not seem to work. The REJECTCODE returns all "0" regardless of
whether
>the database update is successful or not.
>
>My input data contains 3 rows, the first two records are supposed to
>return
>SQL 100 (meaning not found). The last record should have update
successful.
>
>However, when I displayed the REJECTCODE by creating another link from
>the
>transformer stage and with derivation expression of
OutputLink.REJECTEDCODE,
>they return all zero.
>
>FYI, I currently have two output links from the transformer stage. One
>is
>to the OCI connection to an oracle table and the other is to a flat
>file recording all the reject code from the oracle table update.
>
>Just wondering what I have gone wrong here.
>
>Cheers,
>Paul.
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Monday, 05 November, 2001 6:48 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Assuming each row sent from DataStage is intended to update exactly one
>row
>in the target table (see David Barhams note), if REJECTEDCODE is zero
>the
>update was successful, otherwise it wasnt.
>Lets say your output link is called OutputLink.
>On another output link, lets call it FailLink, you can generate a
>column called RejectedCode, and derive it as the value of the link
>variable REJECTEDCODE on the output link OutputLink (its derivation
>expression will
>appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit
>output (if you wish) to just rows rejected by the OutputLink link, and
>you
>could run FailLink into an Aggregator stage to get a summary result.
>
>-----Original Message-----
>From: Paul Ko [mailto:PKO@clear.co.nz]
>Sent: Monday, 05 November 2001 13:45
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Hello,
>
>Refer to the email that Ray had said, how exactly can I use the active
stage
>link variables to detect whether the row was rejected by the database
>or successful. Mind that there was no SQLCODE in the link variables.
>Currently, only the following values are in the output link variables:
>
>DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
>
>Cheers,
>Paul.
>
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October, 2001 6:29 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>If you use the output link variables on the active stage (Transformer?)
that
>is driving the output link, you can detect whether the row was rejected
>by
>the database or successful. This could be used in conjunction with the
>row
>count on the output link to maintain statistics on another output link.
>Not sure how youd implement this if your write-rule is one of the
>double-barrelled ones (such as "insert or update", or "update or
>insert",
or
>"replace" (delete then insert)), but I steer clear of these because I
>believe them to be inefficient. Prefer one link for "insert only" and
>another link for "update only".
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:40
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Ray,
> This may not always correlate with the actual rows inserted/updated on
>the database table.
>eg. 10 rows out from the active stage but only 7 rows get updated because
>the other 3 dont exist in the database table.
>
>I have tried to enter sql like select @@rowcount into the AfterSQL
>properties of the stage but it comes back with a warning. Its probably
>not
>expecting a return from the sql.
>
>Peter
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October 2001 10:24 AM
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Its in the logged event for the active stage finishing (use
>DSGetLog...
>functions) and its available by asking the output link (use DSGetLinkInfo)
>for its row count.
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:04
>To: datastage-users@oliver.com
>Subject: Reporting on rows updated in database
>
>
>Is there a way to report on the actual rows updated in the database
>load stage. Im using the Sybase OC and BCP plugin.
>
>regards,
>Peter
>
>
-----Original Message-----
From: kuan@clear.net.nz [mailto:kuan@clear.net.nz]
Sent: Tuesday, 06 November 2001 12:37
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
Paul
We use this technique with all our jobs and it works fine. Check that you are interrogating the right REJECTCODE, the link to your OCI stage. Try setting the transaction size to 1 to see if makes any difference. Shouldnt.
HTH
>Hi Ray,
>
>I dont know what is going wrong here. I try to follow your method but
>it
>does not seem to work. The REJECTCODE returns all "0" regardless of
whether
>the database update is successful or not.
>
>My input data contains 3 rows, the first two records are supposed to
>return
>SQL 100 (meaning not found). The last record should have update
successful.
>
>However, when I displayed the REJECTCODE by creating another link from
>the
>transformer stage and with derivation expression of
OutputLink.REJECTEDCODE,
>they return all zero.
>
>FYI, I currently have two output links from the transformer stage. One
>is
>to the OCI connection to an oracle table and the other is to a flat
>file recording all the reject code from the oracle table update.
>
>Just wondering what I have gone wrong here.
>
>Cheers,
>Paul.
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Monday, 05 November, 2001 6:48 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Assuming each row sent from DataStage is intended to update exactly one
>row
>in the target table (see David Barhams note), if REJECTEDCODE is zero
>the
>update was successful, otherwise it wasnt.
>Lets say your output link is called OutputLink.
>On another output link, lets call it FailLink, you can generate a
>column called RejectedCode, and derive it as the value of the link
>variable REJECTEDCODE on the output link OutputLink (its derivation
>expression will
>appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit
>output (if you wish) to just rows rejected by the OutputLink link, and
>you
>could run FailLink into an Aggregator stage to get a summary result.
>
>-----Original Message-----
>From: Paul Ko [mailto:PKO@clear.co.nz]
>Sent: Monday, 05 November 2001 13:45
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Hello,
>
>Refer to the email that Ray had said, how exactly can I use the active
stage
>link variables to detect whether the row was rejected by the database
>or successful. Mind that there was no SQLCODE in the link variables.
>Currently, only the following values are in the output link variables:
>
>DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
>
>Cheers,
>Paul.
>
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October, 2001 6:29 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>If you use the output link variables on the active stage (Transformer?)
that
>is driving the output link, you can detect whether the row was rejected
>by
>the database or successful. This could be used in conjunction with the
>row
>count on the output link to maintain statistics on another output link.
>Not sure how youd implement this if your write-rule is one of the
>double-barrelled ones (such as "insert or update", or "update or
>insert",
or
>"replace" (delete then insert)), but I steer clear of these because I
>believe them to be inefficient. Prefer one link for "insert only" and
>another link for "update only".
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:40
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Ray,
> This may not always correlate with the actual rows inserted/updated on
>the database table.
>eg. 10 rows out from the active stage but only 7 rows get updated because
>the other 3 dont exist in the database table.
>
>I have tried to enter sql like select @@rowcount into the AfterSQL
>properties of the stage but it comes back with a warning. Its probably
>not
>expecting a return from the sql.
>
>Peter
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October 2001 10:24 AM
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Its in the logged event for the active stage finishing (use
>DSGetLog...
>functions) and its available by asking the output link (use DSGetLinkInfo)
>for its row count.
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:04
>To: datastage-users@oliver.com
>Subject: Reporting on rows updated in database
>
>
>Is there a way to report on the actual rows updated in the database
>load stage. Im using the Sybase OC and BCP plugin.
>
>regards,
>Peter
>
>
Paul
We use this technique with all our jobs and it works fine. Check that you are interrogating the right REJECTCODE, the link to your OCI stage. Try setting the transaction size to 1 to see if makes any difference. Shouldnt.
HTH
>Hi Ray,
>
>I dont know what is going wrong here. I try to follow your method but
>it
>does not seem to work. The REJECTCODE returns all "0" regardless of
>whether
>the database update is successful or not.
>
>My input data contains 3 rows, the first two records are supposed to
>return
>SQL 100 (meaning not found). The last record should have update
>successful.
>
>However, when I displayed the REJECTCODE by creating another link from
>the
>transformer stage and with derivation expression of
>OutputLink.REJECTEDCODE,
>they return all zero.
>
>FYI, I currently have two output links from the transformer stage. One
>is
>to the OCI connection to an oracle table and the other is to a flat
>file recording all the reject code from the oracle table update.
>
>Just wondering what I have gone wrong here.
>
>Cheers,
>Paul.
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Monday, 05 November, 2001 6:48 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Assuming each row sent from DataStage is intended to update exactly one
>row
>in the target table (see David Barhams note), if REJECTEDCODE is zero
>the
>update was successful, otherwise it wasnt.
>Lets say your output link is called OutputLink.
>On another output link, lets call it FailLink, you can generate a
>column called RejectedCode, and derive it as the value of the link
>variable REJECTEDCODE on the output link OutputLink (its derivation
>expression will
>appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit
>output (if you wish) to just rows rejected by the OutputLink link, and
>you
>could run FailLink into an Aggregator stage to get a summary result.
>
>-----Original Message-----
>From: Paul Ko [mailto:PKO@clear.co.nz]
>Sent: Monday, 05 November 2001 13:45
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Hello,
>
>Refer to the email that Ray had said, how exactly can I use the active
>stage
>link variables to detect whether the row was rejected by the database
>or successful. Mind that there was no SQLCODE in the link variables.
>Currently, only the following values are in the output link variables:
>
>DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
>
>Cheers,
>Paul.
>
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October, 2001 6:29 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>If you use the output link variables on the active stage (Transformer?)
>that
>is driving the output link, you can detect whether the row was rejected
>by
>the database or successful. This could be used in conjunction with the
>row
>count on the output link to maintain statistics on another output link.
>Not sure how youd implement this if your write-rule is one of the
>double-barrelled ones (such as "insert or update", or "update or
>insert", or
>"replace" (delete then insert)), but I steer clear of these because I
>believe them to be inefficient. Prefer one link for "insert only" and
>another link for "update only".
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:40
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Ray,
> This may not always correlate with the actual rows inserted/updated on
>the database table.
>eg. 10 rows out from the active stage but only 7 rows get updated because
>the other 3 dont exist in the database table.
>
>I have tried to enter sql like select @@rowcount into the AfterSQL
>properties of the stage but it comes back with a warning. Its probably
>not
>expecting a return from the sql.
>
>Peter
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October 2001 10:24 AM
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Its in the logged event for the active stage finishing (use
>DSGetLog...
>functions) and its available by asking the output link (use DSGetLinkInfo)
>for its row count.
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:04
>To: datastage-users@oliver.com
>Subject: Reporting on rows updated in database
>
>
>Is there a way to report on the actual rows updated in the database
>load stage. Im using the Sybase OC and BCP plugin.
>
>regards,
>Peter
>
>
We use this technique with all our jobs and it works fine. Check that you are interrogating the right REJECTCODE, the link to your OCI stage. Try setting the transaction size to 1 to see if makes any difference. Shouldnt.
HTH
>Hi Ray,
>
>I dont know what is going wrong here. I try to follow your method but
>it
>does not seem to work. The REJECTCODE returns all "0" regardless of
>whether
>the database update is successful or not.
>
>My input data contains 3 rows, the first two records are supposed to
>return
>SQL 100 (meaning not found). The last record should have update
>successful.
>
>However, when I displayed the REJECTCODE by creating another link from
>the
>transformer stage and with derivation expression of
>OutputLink.REJECTEDCODE,
>they return all zero.
>
>FYI, I currently have two output links from the transformer stage. One
>is
>to the OCI connection to an oracle table and the other is to a flat
>file recording all the reject code from the oracle table update.
>
>Just wondering what I have gone wrong here.
>
>Cheers,
>Paul.
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Monday, 05 November, 2001 6:48 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Assuming each row sent from DataStage is intended to update exactly one
>row
>in the target table (see David Barhams note), if REJECTEDCODE is zero
>the
>update was successful, otherwise it wasnt.
>Lets say your output link is called OutputLink.
>On another output link, lets call it FailLink, you can generate a
>column called RejectedCode, and derive it as the value of the link
>variable REJECTEDCODE on the output link OutputLink (its derivation
>expression will
>appear as OutputLink.REJECTEDCODE). A constraint on FailLink can limit
>output (if you wish) to just rows rejected by the OutputLink link, and
>you
>could run FailLink into an Aggregator stage to get a summary result.
>
>-----Original Message-----
>From: Paul Ko [mailto:PKO@clear.co.nz]
>Sent: Monday, 05 November 2001 13:45
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Hello,
>
>Refer to the email that Ray had said, how exactly can I use the active
>stage
>link variables to detect whether the row was rejected by the database
>or successful. Mind that there was no SQLCODE in the link variables.
>Currently, only the following values are in the output link variables:
>
>DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
>
>Cheers,
>Paul.
>
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October, 2001 6:29 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>If you use the output link variables on the active stage (Transformer?)
>that
>is driving the output link, you can detect whether the row was rejected
>by
>the database or successful. This could be used in conjunction with the
>row
>count on the output link to maintain statistics on another output link.
>Not sure how youd implement this if your write-rule is one of the
>double-barrelled ones (such as "insert or update", or "update or
>insert", or
>"replace" (delete then insert)), but I steer clear of these because I
>believe them to be inefficient. Prefer one link for "insert only" and
>another link for "update only".
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:40
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Ray,
> This may not always correlate with the actual rows inserted/updated on
>the database table.
>eg. 10 rows out from the active stage but only 7 rows get updated because
>the other 3 dont exist in the database table.
>
>I have tried to enter sql like select @@rowcount into the AfterSQL
>properties of the stage but it comes back with a warning. Its probably
>not
>expecting a return from the sql.
>
>Peter
>
>-----Original Message-----
>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>Sent: Tuesday, 30 October 2001 10:24 AM
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Its in the logged event for the active stage finishing (use
>DSGetLog...
>functions) and its available by asking the output link (use DSGetLinkInfo)
>for its row count.
>
>-----Original Message-----
>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>Sent: Tuesday, 30 October 2001 10:04
>To: datastage-users@oliver.com
>Subject: Reporting on rows updated in database
>
>
>Is there a way to report on the actual rows updated in the database
>load stage. Im using the Sybase OC and BCP plugin.
>
>regards,
>Peter
>
>
Well, there goes that theory then. Need to look further afield; version of Oracle perhaps, or some configuration issue. Getting outside my area of expertise, but others on the list may be able to help.
-----Original Message-----
From: kuan@clear.net.nz [mailto:kuan@clear.net.nz]
Sent: Tuesday, 06 November 2001 13:32
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
We are using OCI 2.3.
>What OCI plug-in are you using?
>
>Cheers,
>Paul.
>
>-----Original Message-----
>From: kuan@clear.net.nz [mailto:kuan@clear.net.nz]
>Sent: Tuesday, 06 November, 2001 2:37 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Paul
>
>We use this technique with all our jobs and it works fine. Check that
>you are interrogating the right REJECTCODE, the link to your OCI stage.
>Try setting
>the transaction size to 1 to see if makes any difference. Shouldnt.
>
>HTH
>
>>Hi Ray,
>>
>>I dont know what is going wrong here. I try to follow your method
>>but it
>
>>does not seem to work. The REJECTCODE returns all "0" regardless of
>whether
>
>>the database update is successful or not.
>>
>>My input data contains 3 rows, the first two records are supposed to
return
>
>>SQL 100 (meaning not found). The last record should have update
>successful.
>
>>
>>However, when I displayed the REJECTCODE by creating another link from
>>the
>
>>transformer stage and with derivation expression of
>OutputLink.REJECTEDCODE,
>
>>they return all zero.
>>
>>FYI, I currently have two output links from the transformer stage.
>>One is
>
>>to the OCI connection to an oracle table and the other is to a flat
>>file recording all the reject code from the oracle table update.
>>
>>Just wondering what I have gone wrong here.
>>
>>Cheers,
>>Paul.
>>
>>-----Original Message-----
>>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>>Sent: Monday, 05 November, 2001 6:48 p.m.
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Assuming each row sent from DataStage is intended to update exactly
>>one
row
>
>>in the target table (see David Barhams note), if REJECTEDCODE is zero
>>the
>
>>update was successful, otherwise it wasnt.
>>Lets say your output link is called OutputLink.
>>On another output link, lets call it FailLink, you can generate a
>>column
>>called RejectedCode, and derive it as the value of the link variable
>>REJECTEDCODE on the output link OutputLink (its derivation expression
>>will
>
>>appear as OutputLink.REJECTEDCODE). A constraint on FailLink can
>>limit output (if you wish) to just rows rejected by the OutputLink
>>link, and you
>
>>could run FailLink into an Aggregator stage to get a summary result.
>>
>>-----Original Message-----
>>From: Paul Ko [mailto:PKO@clear.co.nz]
>>Sent: Monday, 05 November 2001 13:45
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Hello,
>>
>>Refer to the email that Ray had said, how exactly can I use the active
>stage
>
>>link variables to detect whether the row was rejected by the database
>>or successful. Mind that there was no SQLCODE in the link variables.
>>Currently, only the following values are in the output link variables:
>>
>>DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
>>
>>Cheers,
>>Paul.
>>
>>
>>-----Original Message-----
>>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>>Sent: Tuesday, 30 October, 2001 6:29 p.m.
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>If you use the output link variables on the active stage
>>(Transformer?)
>that
>
>>is driving the output link, you can detect whether the row was
>>rejected by
>
>>the database or successful. This could be used in conjunction with
>>the
row
>
>>count on the output link to maintain statistics on another output
>>link. Not sure how youd implement this if your write-rule is one of
>>the double-barrelled ones (such as "insert or update", or "update or
>>insert",
>or
>
>>"replace" (delete then insert)), but I steer clear of these because I
>>believe them to be inefficient. Prefer one link for "insert only" and
>>another link for "update only".
>>
>>-----Original Message-----
>>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>>Sent: Tuesday, 30 October 2001 10:40
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Ray,
>> This may not always correlate with the actual rows inserted/updated
>>on the database table.
>>eg. 10 rows out from the active stage but only 7 rows get updated because
>>the other 3 dont exist in the database table.
>>
>>I have tried to enter sql like select @@rowcount into the AfterSQL
>>properties of the stage but it comes back with a warning. Its
>>probably
not
>
>>expecting a return from the sql.
>>
>>Peter
>>
>>-----Original Message-----
>>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>>Sent: Tuesday, 30 October 2001 10:24 AM
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Its in the logged event for the active stage finishing (use
>>DSGetLog...
>>functions) and its available by asking the output link (use
DSGetLinkInfo)
>
>>for its row count.
>>
>>-----Original Message-----
>>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>>Sent: Tuesday, 30 October 2001 10:04
>>To: datastage-users@oliver.com
>>Subject: Reporting on rows updated in database
>>
>>
>>Is there a way to report on the actual rows updated in the database
>>load stage. Im using the Sybase OC and BCP plugin.
>>
>>regards,
>>Peter
>>
>>
>
>
-----Original Message-----
From: kuan@clear.net.nz [mailto:kuan@clear.net.nz]
Sent: Tuesday, 06 November 2001 13:32
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
We are using OCI 2.3.
>What OCI plug-in are you using?
>
>Cheers,
>Paul.
>
>-----Original Message-----
>From: kuan@clear.net.nz [mailto:kuan@clear.net.nz]
>Sent: Tuesday, 06 November, 2001 2:37 p.m.
>To: datastage-users@oliver.com
>Subject: RE: Reporting on rows updated in database
>
>
>Paul
>
>We use this technique with all our jobs and it works fine. Check that
>you are interrogating the right REJECTCODE, the link to your OCI stage.
>Try setting
>the transaction size to 1 to see if makes any difference. Shouldnt.
>
>HTH
>
>>Hi Ray,
>>
>>I dont know what is going wrong here. I try to follow your method
>>but it
>
>>does not seem to work. The REJECTCODE returns all "0" regardless of
>whether
>
>>the database update is successful or not.
>>
>>My input data contains 3 rows, the first two records are supposed to
return
>
>>SQL 100 (meaning not found). The last record should have update
>successful.
>
>>
>>However, when I displayed the REJECTCODE by creating another link from
>>the
>
>>transformer stage and with derivation expression of
>OutputLink.REJECTEDCODE,
>
>>they return all zero.
>>
>>FYI, I currently have two output links from the transformer stage.
>>One is
>
>>to the OCI connection to an oracle table and the other is to a flat
>>file recording all the reject code from the oracle table update.
>>
>>Just wondering what I have gone wrong here.
>>
>>Cheers,
>>Paul.
>>
>>-----Original Message-----
>>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>>Sent: Monday, 05 November, 2001 6:48 p.m.
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Assuming each row sent from DataStage is intended to update exactly
>>one
row
>
>>in the target table (see David Barhams note), if REJECTEDCODE is zero
>>the
>
>>update was successful, otherwise it wasnt.
>>Lets say your output link is called OutputLink.
>>On another output link, lets call it FailLink, you can generate a
>>column
>>called RejectedCode, and derive it as the value of the link variable
>>REJECTEDCODE on the output link OutputLink (its derivation expression
>>will
>
>>appear as OutputLink.REJECTEDCODE). A constraint on FailLink can
>>limit output (if you wish) to just rows rejected by the OutputLink
>>link, and you
>
>>could run FailLink into an Aggregator stage to get a summary result.
>>
>>-----Original Message-----
>>From: Paul Ko [mailto:PKO@clear.co.nz]
>>Sent: Monday, 05 November 2001 13:45
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Hello,
>>
>>Refer to the email that Ray had said, how exactly can I use the active
>stage
>
>>link variables to detect whether the row was rejected by the database
>>or successful. Mind that there was no SQLCODE in the link variables.
>>Currently, only the following values are in the output link variables:
>>
>>DBMSCODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE
>>
>>Cheers,
>>Paul.
>>
>>
>>-----Original Message-----
>>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>>Sent: Tuesday, 30 October, 2001 6:29 p.m.
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>If you use the output link variables on the active stage
>>(Transformer?)
>that
>
>>is driving the output link, you can detect whether the row was
>>rejected by
>
>>the database or successful. This could be used in conjunction with
>>the
row
>
>>count on the output link to maintain statistics on another output
>>link. Not sure how youd implement this if your write-rule is one of
>>the double-barrelled ones (such as "insert or update", or "update or
>>insert",
>or
>
>>"replace" (delete then insert)), but I steer clear of these because I
>>believe them to be inefficient. Prefer one link for "insert only" and
>>another link for "update only".
>>
>>-----Original Message-----
>>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>>Sent: Tuesday, 30 October 2001 10:40
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Ray,
>> This may not always correlate with the actual rows inserted/updated
>>on the database table.
>>eg. 10 rows out from the active stage but only 7 rows get updated because
>>the other 3 dont exist in the database table.
>>
>>I have tried to enter sql like select @@rowcount into the AfterSQL
>>properties of the stage but it comes back with a warning. Its
>>probably
not
>
>>expecting a return from the sql.
>>
>>Peter
>>
>>-----Original Message-----
>>From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
>>Sent: Tuesday, 30 October 2001 10:24 AM
>>To: datastage-users@oliver.com
>>Subject: RE: Reporting on rows updated in database
>>
>>
>>Its in the logged event for the active stage finishing (use
>>DSGetLog...
>>functions) and its available by asking the output link (use
DSGetLinkInfo)
>
>>for its row count.
>>
>>-----Original Message-----
>>From: Peter Oates [mailto:Peter.Oates@macquarie.com]
>>Sent: Tuesday, 30 October 2001 10:04
>>To: datastage-users@oliver.com
>>Subject: Reporting on rows updated in database
>>
>>
>>Is there a way to report on the actual rows updated in the database
>>load stage. Im using the Sybase OC and BCP plugin.
>>
>>regards,
>>Peter
>>
>>
>
>