Converting Stored Procedure to DataStage Jobs

[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 ?

[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.

[apierro1]

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.

[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.

[apierro1]

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.

[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.

[ray.wurlod]

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!

[dganeshm]

Started working on the assignment..going ahead with Stored Procedures..thanks for all of your opinions guys..

[kumar_s]

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?

[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.