I notice in the documentation that when using an ODBC stage to call a stored procedure (in our case a call to Oracle running on a DEC Alpha), that the ODBC stage does not get/receive anything back from the remote machine. Were in DS 3.1.1r3 on an NT Server, BTW. I understand there is an option to receive return codes, etc. in later releases.
Regardless, I was wondering if the DS job will complete after issuing the call via ODBC or will it wait until the called stored procedure finishes? Based on the above, I imagine its the first scenario.
The reason I ask is that we plan on issuing a call in the final job in our DS jobstream to start some processes on the DEC Alpha. But we dont want the DS job to wait until the called procedure is done before it finishes (The procedure will take a few hours to run).
Thanks.
Brad Vincent
Compuware Corporation
c/o The Detroit Medical Center
Data Warehousing with a "health"-y spin
(313) 966-2176
Calling stored procedures via ODBC
Brad
The true use of stored procedures within DataStage ODBC stage is to extract data from an ODBC Source or to pass a row of data to a stored procedure to be processed. So when used as an extract DS expects a result set to be returned. When used as a target it expects the stored procedure to process each row of data sent to it.
In my experience the cleanest way to execute a stored procedure of this nature is to create a SQL script file that has all the commands needed to be execute the procedure if you were logged into SQLPlus. Then from DataStage you can execute a SQLPlus command line to actually perform the script.
Now if you put this in job by itself then you can from a different job call this job from the job control and remove the lines after the run job command. This way the other job will be kicked off but the calling job will not wait for it to finish and will continue on. You will also have the called job with the SQLPlus command in it still running so that from the director you can still monitor. The nice thing about this approach is you do not loose the metadata of what is being done. When the script finishes there will be an entry within the job that shows all of the characters that the stored procedure would have echoed to the screen. So it is handy that when you are creating a stored procedure place some comments to be displayed that show some progress points. Can be very handy in debugging.
*************************************************************
Rick Schirm
Application Performart Ltd.
Vice President of Engineering
mailto:rick.schirm@performart.com
web: www.performart.com
phone: 817.291.6177
*************************************************************
Have you downloaded your copy of Version Control for DataStage? Check it out at www.performart.com!
*************************************************************
-----Original Message-----
From: Vincent, Brad [mailto:BVincent@dmc.org]
Sent: Wednesday, July 26, 2000 7:23 AM
To: informix-datastage@oliver.com
Subject: Calling stored procedures via ODBC
I notice in the documentation that when using an ODBC stage to call a stored procedure (in our case a call to Oracle running on a DEC Alpha), that the ODBC stage does not get/receive anything back from the remote machine. Were in DS 3.1.1r3 on an NT Server, BTW. I understand there is an option to receive return codes, etc. in later releases.
Regardless, I was wondering if the DS job will complete after issuing the call via ODBC or will it wait until the called stored procedure finishes? Based on the above, I imagine its the first scenario.
The reason I ask is that we plan on issuing a call in the final job in our DS jobstream to start some processes on the DEC Alpha. But we dont want the DS job to wait until the called procedure is done before it finishes (The procedure will take a few hours to run).
Thanks.
Brad Vincent
Compuware Corporation
c/o The Detroit Medical Center
Data Warehousing with a "health"-y spin
(313) 966-2176
The true use of stored procedures within DataStage ODBC stage is to extract data from an ODBC Source or to pass a row of data to a stored procedure to be processed. So when used as an extract DS expects a result set to be returned. When used as a target it expects the stored procedure to process each row of data sent to it.
In my experience the cleanest way to execute a stored procedure of this nature is to create a SQL script file that has all the commands needed to be execute the procedure if you were logged into SQLPlus. Then from DataStage you can execute a SQLPlus command line to actually perform the script.
Now if you put this in job by itself then you can from a different job call this job from the job control and remove the lines after the run job command. This way the other job will be kicked off but the calling job will not wait for it to finish and will continue on. You will also have the called job with the SQLPlus command in it still running so that from the director you can still monitor. The nice thing about this approach is you do not loose the metadata of what is being done. When the script finishes there will be an entry within the job that shows all of the characters that the stored procedure would have echoed to the screen. So it is handy that when you are creating a stored procedure place some comments to be displayed that show some progress points. Can be very handy in debugging.
*************************************************************
Rick Schirm
Application Performart Ltd.
Vice President of Engineering
mailto:rick.schirm@performart.com
web: www.performart.com
phone: 817.291.6177
*************************************************************
Have you downloaded your copy of Version Control for DataStage? Check it out at www.performart.com!
*************************************************************
-----Original Message-----
From: Vincent, Brad [mailto:BVincent@dmc.org]
Sent: Wednesday, July 26, 2000 7:23 AM
To: informix-datastage@oliver.com
Subject: Calling stored procedures via ODBC
I notice in the documentation that when using an ODBC stage to call a stored procedure (in our case a call to Oracle running on a DEC Alpha), that the ODBC stage does not get/receive anything back from the remote machine. Were in DS 3.1.1r3 on an NT Server, BTW. I understand there is an option to receive return codes, etc. in later releases.
Regardless, I was wondering if the DS job will complete after issuing the call via ODBC or will it wait until the called stored procedure finishes? Based on the above, I imagine its the first scenario.
The reason I ask is that we plan on issuing a call in the final job in our DS jobstream to start some processes on the DEC Alpha. But we dont want the DS job to wait until the called procedure is done before it finishes (The procedure will take a few hours to run).
Thanks.
Brad Vincent
Compuware Corporation
c/o The Detroit Medical Center
Data Warehousing with a "health"-y spin
(313) 966-2176