Hi Ray,
We are using OCI v3.0. I think that different versions might have something to do with it.
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Tuesday, 06 November, 2001 2:27 p.m.
To: datastage-users@oliver.com
Subject: RE: Reporting on rows updated in database
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
>
>
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
>>
>>
>
>
>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
>>
>>
>
>