-----Original Message-----
From: tom.peter.nielsen@nordea.com [mailto:tom.peter.nielsen@nordea.com]
Sent: Thursday, October 16, 2003 6:05 AM
To: datastage-users@oliver.com
Subject: How to check for 0 update on DB2 stage
Hi,
I'm using a user defined SQL on a DB2 stage, AIX 5.2, DS 6.0.1R5.
Is there anyway to check if i'm not finding any rows to update ?
DB2 return values:
SQLCODE of +100 or an SQLSTATE of '02000'
Any idea how i can check for these values from DataStage ?
br
Tom
How to check for 0 update on DB2 stage
How to check for 0 update on DB2 stage
<b>PLEASE READ</b>
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
How to check for 0 update on DB2 stage
-----Original Message-----
From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
Sent: Thursday, October 16, 2003 8:38 AM
To: datastage-users@oliver.com
Subject: Re: How to check for 0 update on DB2 stage
No. There is no inherent mechanisms for feedback as
to how SQL in performing, with the exception of
truncation/warning/reject messages from any database.
It behooves you greatly to predetermine the path your
row will take, insert or update, and partition the
data into those two groups. You greatly enhance
restart capability if you first spooled your data to
load ready files. This is the great difference
between restart and reload if your jobstream should
fail. In addition, you collect the appropriate
metadata, as well as open up the possibility of bulk
loading inserts (highly desirable).
Good luck!
-Ken
--- tom.peter.nielsen@nordea.com wrote:
> Hi,
>
> I'm using a user defined SQL on a DB2 stage, AIX
> 5.2, DS 6.0.1R5.
>
> Is there anyway to check if i'm not finding any rows
> to update ?
> DB2 return values:
> SQLCODE of +100 or an SQLSTATE of '02000'
>
> Any idea how i can check for these values from
> DataStage ?
>
> br
> Tom
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
Sent: Thursday, October 16, 2003 8:38 AM
To: datastage-users@oliver.com
Subject: Re: How to check for 0 update on DB2 stage
No. There is no inherent mechanisms for feedback as
to how SQL in performing, with the exception of
truncation/warning/reject messages from any database.
It behooves you greatly to predetermine the path your
row will take, insert or update, and partition the
data into those two groups. You greatly enhance
restart capability if you first spooled your data to
load ready files. This is the great difference
between restart and reload if your jobstream should
fail. In addition, you collect the appropriate
metadata, as well as open up the possibility of bulk
loading inserts (highly desirable).
Good luck!
-Ken
--- tom.peter.nielsen@nordea.com wrote:
> Hi,
>
> I'm using a user defined SQL on a DB2 stage, AIX
> 5.2, DS 6.0.1R5.
>
> Is there anyway to check if i'm not finding any rows
> to update ?
> DB2 return values:
> SQLCODE of +100 or an SQLSTATE of '02000'
>
> Any idea how i can check for these values from
> DataStage ?
>
> br
> Tom
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
<b>PLEASE READ</b>
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
How to check for 0 update on DB2 stage
-----Original Message-----
From: Kim Duke [mailto:Kim_G_Duke@Yahoo.com]
Sent: Thursday, October 16, 2003 9:06 AM
To: datastage-users@oliver.com
Subject: RE: How to check for 0 update on DB2 stage
Tom
After the job finished you can check the row counts. If you search
http://www.dsxchange.com/ then you can find code to show you how.
Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com
-----Original Message-----
From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
Sent: Thursday, October 16, 2003 7:38 AM
To: datastage-users@oliver.com
Subject: Re: How to check for 0 update on DB2 stage
No. There is no inherent mechanisms for feedback as
to how SQL in performing, with the exception of
truncation/warning/reject messages from any database.
It behooves you greatly to predetermine the path your
row will take, insert or update, and partition the
data into those two groups. You greatly enhance
restart capability if you first spooled your data to
load ready files. This is the great difference
between restart and reload if your jobstream should
fail. In addition, you collect the appropriate
metadata, as well as open up the possibility of bulk
loading inserts (highly desirable).
Good luck!
-Ken
--- tom.peter.nielsen@nordea.com wrote:
> Hi,
>
> I'm using a user defined SQL on a DB2 stage, AIX
> 5.2, DS 6.0.1R5.
>
> Is there anyway to check if i'm not finding any rows
> to update ?
> DB2 return values:
> SQLCODE of +100 or an SQLSTATE of '02000'
>
> Any idea how i can check for these values from
> DataStage ?
>
> br
> Tom
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
From: Kim Duke [mailto:Kim_G_Duke@Yahoo.com]
Sent: Thursday, October 16, 2003 9:06 AM
To: datastage-users@oliver.com
Subject: RE: How to check for 0 update on DB2 stage
Tom
After the job finished you can check the row counts. If you search
http://www.dsxchange.com/ then you can find code to show you how.
Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com
-----Original Message-----
From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
Sent: Thursday, October 16, 2003 7:38 AM
To: datastage-users@oliver.com
Subject: Re: How to check for 0 update on DB2 stage
No. There is no inherent mechanisms for feedback as
to how SQL in performing, with the exception of
truncation/warning/reject messages from any database.
It behooves you greatly to predetermine the path your
row will take, insert or update, and partition the
data into those two groups. You greatly enhance
restart capability if you first spooled your data to
load ready files. This is the great difference
between restart and reload if your jobstream should
fail. In addition, you collect the appropriate
metadata, as well as open up the possibility of bulk
loading inserts (highly desirable).
Good luck!
-Ken
--- tom.peter.nielsen@nordea.com wrote:
> Hi,
>
> I'm using a user defined SQL on a DB2 stage, AIX
> 5.2, DS 6.0.1R5.
>
> Is there anyway to check if i'm not finding any rows
> to update ?
> DB2 return values:
> SQLCODE of +100 or an SQLSTATE of '02000'
>
> Any idea how i can check for these values from
> DataStage ?
>
> br
> Tom
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
<b>PLEASE READ</b>
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
How to check for 0 update on DB2 stage
-----Original Message-----
From: Kim Duke [mailto:Kim_G_Duke@Yahoo.com]
Sent: Thursday, October 16, 2003 10:29 AM
To: datastage-users@oliver.com
Subject: RE: How to check for 0 update on DB2 stage
Kenneth
I am sorry. I meant you could use DSGetJobInfo() and all the routines in
BASIC to get the row counts of a job after the fact. I don't know of any
SQL to do what they wanted.
Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com
-----Original Message-----
From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
Sent: Thursday, October 16, 2003 8:42 AM
To: datastage-users@oliver.com
Subject: RE: How to check for 0 update on DB2 stage
Respectfully, Kim, I think your answer is not correct
for the stated question. Tom asked if he could check
the return value from DB2 for each row passed. Even
using custom SQL, as he stated, will not help you.
You have NO mechanism for accumulating information
with custom SQL as to how each row is being handled by
the target and then publishing that information,
outside of using a stored procedure. Link statistics
only state how many rows went down a link.
I repeat my original statement: The only way to know
if a row is going to insert or update is to first
query the table to see if it's already there and
branch your output.
If you attempt an update when a row is not there,
DataStage is oblivious to this. You can pass 100
billion rows down a link with update existing rows
only SQL and it will look like you updated 100 billion
rows, even if the rows aren't there.
-Ken
--- Kim Duke wrote:
> Tom
>
> After the job finished you can check the row counts.
> If you search
> http://www.dsxchange.com/ then you can find code to
> show you how.
>
> Thanks Kim.
> Kim Duke
> DsWebMon - Safely Monitor DataStage from the Web
> www.Duke-Consulting.com
>
>
> -----Original Message-----
> From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
> Sent: Thursday, October 16, 2003 7:38 AM
> To: datastage-users@oliver.com
> Subject: Re: How to check for 0 update on DB2 stage
>
> No. There is no inherent mechanisms for feedback as
> to how SQL in performing, with the exception of
> truncation/warning/reject messages from any
> database.
>
> It behooves you greatly to predetermine the path
> your
> row will take, insert or update, and partition the
> data into those two groups. You greatly enhance
> restart capability if you first spooled your data to
> load ready files. This is the great difference
> between restart and reload if your jobstream should
> fail. In addition, you collect the appropriate
> metadata, as well as open up the possibility of bulk
> loading inserts (highly desirable).
>
> Good luck!
> -Ken
>
>
>
> --- tom.peter.nielsen@nordea.com wrote:
> > Hi,
> >
> > I'm using a user defined SQL on a DB2 stage, AIX
> > 5.2, DS 6.0.1R5.
> >
> > Is there anyway to check if i'm not finding any
> rows
> > to update ?
> > DB2 return values:
> > SQLCODE of +100 or an SQLSTATE of '02000'
> >
> > Any idea how i can check for these values from
> > DataStage ?
> >
> > br
> > Tom
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site
> design software
> http://sitebuilder.yahoo.com
>
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
From: Kim Duke [mailto:Kim_G_Duke@Yahoo.com]
Sent: Thursday, October 16, 2003 10:29 AM
To: datastage-users@oliver.com
Subject: RE: How to check for 0 update on DB2 stage
Kenneth
I am sorry. I meant you could use DSGetJobInfo() and all the routines in
BASIC to get the row counts of a job after the fact. I don't know of any
SQL to do what they wanted.
Thanks Kim.
Kim Duke
DsWebMon - Safely Monitor DataStage from the Web
www.Duke-Consulting.com
-----Original Message-----
From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
Sent: Thursday, October 16, 2003 8:42 AM
To: datastage-users@oliver.com
Subject: RE: How to check for 0 update on DB2 stage
Respectfully, Kim, I think your answer is not correct
for the stated question. Tom asked if he could check
the return value from DB2 for each row passed. Even
using custom SQL, as he stated, will not help you.
You have NO mechanism for accumulating information
with custom SQL as to how each row is being handled by
the target and then publishing that information,
outside of using a stored procedure. Link statistics
only state how many rows went down a link.
I repeat my original statement: The only way to know
if a row is going to insert or update is to first
query the table to see if it's already there and
branch your output.
If you attempt an update when a row is not there,
DataStage is oblivious to this. You can pass 100
billion rows down a link with update existing rows
only SQL and it will look like you updated 100 billion
rows, even if the rows aren't there.
-Ken
--- Kim Duke wrote:
> Tom
>
> After the job finished you can check the row counts.
> If you search
> http://www.dsxchange.com/ then you can find code to
> show you how.
>
> Thanks Kim.
> Kim Duke
> DsWebMon - Safely Monitor DataStage from the Web
> www.Duke-Consulting.com
>
>
> -----Original Message-----
> From: Kenneth Bland [mailto:kcbland_2000@yahoo.com]
> Sent: Thursday, October 16, 2003 7:38 AM
> To: datastage-users@oliver.com
> Subject: Re: How to check for 0 update on DB2 stage
>
> No. There is no inherent mechanisms for feedback as
> to how SQL in performing, with the exception of
> truncation/warning/reject messages from any
> database.
>
> It behooves you greatly to predetermine the path
> your
> row will take, insert or update, and partition the
> data into those two groups. You greatly enhance
> restart capability if you first spooled your data to
> load ready files. This is the great difference
> between restart and reload if your jobstream should
> fail. In addition, you collect the appropriate
> metadata, as well as open up the possibility of bulk
> loading inserts (highly desirable).
>
> Good luck!
> -Ken
>
>
>
> --- tom.peter.nielsen@nordea.com wrote:
> > Hi,
> >
> > I'm using a user defined SQL on a DB2 stage, AIX
> > 5.2, DS 6.0.1R5.
> >
> > Is there anyway to check if i'm not finding any
> rows
> > to update ?
> > DB2 return values:
> > SQLCODE of +100 or an SQLSTATE of '02000'
> >
> > Any idea how i can check for these values from
> > DataStage ?
> >
> > br
> > Tom
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site
> design software
> http://sitebuilder.yahoo.com
>
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
<b>PLEASE READ</b>
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
How to check for 0 update on DB2 stage
-----Original Message-----
From: CUTCHE, Colleen [mailto:Colleen.CUTCHE@suncorp.com.au]
Sent: Thursday, October 16, 2003 6:42 PM
To: 'datastage-users@oliver.com'
Subject: RE: How to check for 0 update on DB2 stage
I had a similar query and asked the Ascential Technical Support - here is
their answer:
It's possible to capture database return values from the Database.
In the transform derivation select Link Variables --> Outputs -->
Link_to_DB --> DBMSCODE, SQLSTATE,LASTERR, REJECTED, REJECTEDCODE.
The available Output link variables are:
LinkName.REJECTED. Is set according to whether the write of an
output row to the named link was successful or not, i.e., if the write was
successful LinkName.REJECTED is set FALSE.
Linkname.REJECTEDCODE. Returns an error code number if the write
fails, or 0 if either the write succeeds or is rejected because a link
constraint was not met. You can set return error codes for
linkname.REJECTEDCODE by selecting from the Expression Editor Link
Variables > Constants... menu options.
Linkname.SQLSTATE. The SQL error code.
Linkname.DBMSCODE. The DBMS error code.
Linkname.LASTERR. The last error code on that link.
On mainframe jobs only REJECTEDCODE & DBMSCODE are available.
These appear in the expression editor under Variable when adding a reject
file to capture the Database codes.
> -----Original Message-----
> From: tom.peter.nielsen@nordea.com [SMTP:tom.peter.nielsen@nordea.com]
> Sent: Thursday, October 16, 2003 9:05 PM
> To: datastage-users@oliver.com
> Subject: How to check for 0 update on DB2 stage
>
> Hi,
>
> I'm using a user defined SQL on a DB2 stage, AIX 5.2, DS 6.0.1R5.
>
> Is there anyway to check if i'm not finding any rows to update ?
> DB2 return values:
> SQLCODE of +100 or an SQLSTATE of '02000'
>
> Any idea how i can check for these values from DataStage ?
>
> br
> Tom
----------------------------------------------------------------------------
-------
The contents of this message are the views of the Author and do not
necessarily reflect the views of SUNCORP METWAY LTD ABN 66 010 831 722.
The content of this e-mail, including attachments is a confidential
communication between the Suncorp Metway Group and the intended addressee.
Any unauthorised use of the contents is expressly prohibited. If you have
received this e-mail in error please contact the sender immediately and then
delete the message and any attachment(s).
http://www.suncorp.com.au
From: CUTCHE, Colleen [mailto:Colleen.CUTCHE@suncorp.com.au]
Sent: Thursday, October 16, 2003 6:42 PM
To: 'datastage-users@oliver.com'
Subject: RE: How to check for 0 update on DB2 stage
I had a similar query and asked the Ascential Technical Support - here is
their answer:
It's possible to capture database return values from the Database.
In the transform derivation select Link Variables --> Outputs -->
Link_to_DB --> DBMSCODE, SQLSTATE,LASTERR, REJECTED, REJECTEDCODE.
The available Output link variables are:
LinkName.REJECTED. Is set according to whether the write of an
output row to the named link was successful or not, i.e., if the write was
successful LinkName.REJECTED is set FALSE.
Linkname.REJECTEDCODE. Returns an error code number if the write
fails, or 0 if either the write succeeds or is rejected because a link
constraint was not met. You can set return error codes for
linkname.REJECTEDCODE by selecting from the Expression Editor Link
Variables > Constants... menu options.
Linkname.SQLSTATE. The SQL error code.
Linkname.DBMSCODE. The DBMS error code.
Linkname.LASTERR. The last error code on that link.
On mainframe jobs only REJECTEDCODE & DBMSCODE are available.
These appear in the expression editor under Variable when adding a reject
file to capture the Database codes.
> -----Original Message-----
> From: tom.peter.nielsen@nordea.com [SMTP:tom.peter.nielsen@nordea.com]
> Sent: Thursday, October 16, 2003 9:05 PM
> To: datastage-users@oliver.com
> Subject: How to check for 0 update on DB2 stage
>
> Hi,
>
> I'm using a user defined SQL on a DB2 stage, AIX 5.2, DS 6.0.1R5.
>
> Is there anyway to check if i'm not finding any rows to update ?
> DB2 return values:
> SQLCODE of +100 or an SQLSTATE of '02000'
>
> Any idea how i can check for these values from DataStage ?
>
> br
> Tom
----------------------------------------------------------------------------
-------
The contents of this message are the views of the Author and do not
necessarily reflect the views of SUNCORP METWAY LTD ABN 66 010 831 722.
The content of this e-mail, including attachments is a confidential
communication between the Suncorp Metway Group and the intended addressee.
Any unauthorised use of the contents is expressly prohibited. If you have
received this e-mail in error please contact the sender immediately and then
delete the message and any attachment(s).
http://www.suncorp.com.au
<b>PLEASE READ</b>
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.