Hi,
I have done the job to run store procedure in datastage. The method I use is as follows:
1. modified the SP to return a total rows number Trows of unloading(inside
SP) rows produced by SP. this is for ODBC stage output.
2. Created a dummy datastage job:
ODBC Stage1 --------------------------------------------->
ODBC Stage 2
(call sp, output Trows ) (dummy
input)
3. from Stage1, output jobname, extraction datetime, Trows,
4. created a new stage to update the existing jobname, extraction datetime, Trows columns of a control table.
Any more advise?
By the way, could anybody please supply some reference to me?
Thanks in advance
xiong
> -----Original Message-----
> From: David Barham [SMTP:
David.Barham@Anglocoal.com.au]
> Sent: 09 March 2001 19:14
> To:
informix-datastage@oliver.com
> Subject: RE: run SP in datastage
>
> Some more food (or options) for thought.
>
> To make it more obvious, Id think about having a job whose sole
> purpose in life is to run this script and put the code to run the
> script on the job control instead of in a before-job routine.
>
> Another thought. You might like to try calling the procedure from
> ODBC, called from Basic code. I think it can handle stored
> procedures. This would involve learning a bit more about how to call
> ODBC though. (I forget which manual it is in - Im sure Ray or
> someone else can supply the reference for you.)
>
> -----Original Message-----
> From: Xiong Li [SMTP:
Xiong.Li@nzbsg.co.nz]
> Sent: Friday, March 09, 2001 9:57 AM
> To:
informix-datastage@oliver.com
> Subject: RE: run SP in datastage
>
> Hi, Stacy
>
> Good point, I know what I should do.
>
> Thanks you very much,
>
> xiong
>
> > -----Original Message-----
> > From: Stacy Scoggins [SMTP:
rsscoggins@yahoo.com]
> > Sent: 09 March 2001 09:00
> > To:
informix-datastage@oliver.com
> > Subject: RE: run SP in datastage
> >
> > Xiong
> >
> > In the specific example that I am thinking of, we were using an NT
> > Server as the DS server using DS 3.63, loading to an Oracle
> > Database. SQL + was installed on the DS Server.
> >
> > We had the DBA write a script/batch that could be called from the
> > command line of the NT Server. For example, lets say the script
> > was called SP.bat.
> >
> > We created a dummy job that had an sequential stage input, a
> > transform, and a sequential output. As a before job subroutine, we
> > called the SP.bat. The input sequential file was empty and this job
> > essentially did nothing but run the stored procedure. This job
> > could have easily included stages that accomplished something as
> > well as running the stored procedure.
> >
> > That basically it, when this job ran - it ran the stored procedure
> > by calling this SP.bat as a before job routine.
> >
> > Let me know if you need any additional information.
> >
> > Stacy
> >
> >
> > --- Xiong Li wrote:
> > > Stacy:
> > >
> > > This is exactly what I am thinking, Could you please explain it in
> > > detail?
> > >
> > > Thank you very much
> > >
> > > xiong
> > >
> > > > -----Original Message-----
> > > > From: Stacy Scoggins [SMTP:
rsscoggins@yahoo.com]
> > > > Sent: 09 March 2001 04:31
> > > > To:
informix-datastage@oliver.com
> > > > Subject: Re: run SP in datastage
> > > >
> > > > Xiong,
> > > >
> > > > There may be a more efficient way to do this, but you can add
> > > > the
> > > SP
> > > > to a script and then call that script from a before or after job
> > > > subroutine.
> > > >
> > > > Hope that helps !
> > > > Stacy Scoggins
> > > > --- Xiong Li wrote:
> > > > > Hi, all
> > > > >
> > > > > I am creating a job to run store procedure in informix
> > > database,
> > > > > This store
> > > > > procedure can unload result inside procedure into an existing
> > > table
> > > > > . so
> > > > > what I actually want is to run this procedure every night by
> > > using
> > > > > Datastage
> > > > > job. Can anyone tell me how to do it.
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > xiong
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > > __________________________________________________
> > > > Do You Yahoo!?
> > > > Get email at your own domain with Yahoo! Mail.
> > > >
http://personal.mail.yahoo.com/
> > >
> >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Get email at your own domain with Yahoo! Mail.
> >
http://personal.mail.yahoo.com/
>
>
> **********************************************************************
> ***
> This e-mail and any files transmitted with it may be confidential
> and are intended solely for the use of the individual or entity to
> whom they are addressed. If you have received this e-mail in
> error, please notify the sender by return e-mail, and delete this
> e-mail from your in-box. Do not copy it to anybody else
>
> **********************************************************************
> ***