Page 1 of 1
Converting Stored Procedure to DataStage Jobs
Posted: Tue Aug 17, 2010 9:19 am
by dganeshm
Hi ,
I have been given a new assignment to convert stored procedures to jobs. Do you guys really think its advisable to convert a MS SQL Procedure to a DataStage Job ?
Posted: Tue Aug 17, 2010 9:44 am
by chulett
Sure, why not? Once in a great while you'll find things done in a proc that are difficult to do in a job but otherwise it should all be perfectly feasible and make them (typically) much easier to maintain... or at least you can pawn it off on the ETL folks afterwards rather than having to bother the procedure writers.
Re: Converting Stored Procedure to DataStage Jobs
Posted: Tue Aug 17, 2010 9:48 am
by apierro1
dganeshm wrote:Hi ,
I have been given a new assignment to convert stored procedures to jobs. Do you guys really think its advisable to convert a MS SQL Procedure to a DataStage Job ?
It depends on the stored procedure,
Without knowing what the stored procedure does it's hard to determine if it should be converted but if your reading/writing different databases on different servers than I would advise re-writing it in DataStage as that is the purpose of ETL tools.
Obviously you could write a DataStage job to run the stored procedure or re-write the stored procedure in a DataStage job. Creating a DataStage job will be easier to maintain and document.
Posted: Tue Aug 17, 2010 9:52 am
by dganeshm
Mostly it is metric calculation done on a weekly basis used as a planning tool.. so its gonna read from and write to the same database.
Posted: Tue Aug 17, 2010 10:22 am
by apierro1
dganeshm wrote:Mostly it is metric calculation done on a weekly basis used as a planning tool.. so its gonna read from and write to the same database.
Performace wise it will probably be the same or close to it but from a documentation and maintenance standpoint you will be better off having it in DataStage.
Posted: Tue Aug 17, 2010 11:10 am
by kumar_s
ETL server could be at higher end for few transformation and Metric calculations. And hence you might achieve the performance boost.
The trick is, you ll have to find the list of statements that has to be executed in ETL box and the ones that has be executed in DB box for better performance.
Posted: Tue Aug 17, 2010 4:21 pm
by ray.wurlod
kumar_s wrote:The trick is, you ll have to find the list of statements that has to be executed in ETL box and the ones that has be executed in DB box for better performance.
Ah, that sounds like the "balanced optimization" feature coming in the next version!
Posted: Fri Aug 20, 2010 2:14 pm
by dganeshm
Started working on the assignment..going ahead with Stored Procedures..thanks for all of your opinions guys..
Posted: Wed Aug 25, 2010 8:56 am
by kumar_s
ray.wurlod wrote:kumar_s wrote:The trick is, you ll have to find the list of statements that has to be executed in ETL box and the ones that has be executed in DB box for better performance.
Ah, that sounds like the "balanced optimization" feature coming in the next version!
That sounds interesting!! Is that in 8.5?
Does Datastage going to segregate the list of SQLs that need to run on ETL server?
Posted: Wed Aug 25, 2010 5:58 pm
by ray.wurlod
The "next version" does not yet have a version number, for legal reasons.
I don't have any details about Balanced Optimizer with me - if you have access to the IOD 2009 or IOD 2010 Europe presentations you can probably get some knowledge about what's planned. Or go to IOD 2010 in Las Vegas in October.