Stored Procedures need

[Uruj]

Hi All,

 

A very basic question. In what scenarios w.r.t. CA PPM should we go for stored procedures? I have seen scenarios where to update multiple instances there was a scheduled job to run a defined SP in database.

 

Regards,

Uruj

[Chris_Stallone]

Can someone help with this question?

 

Thank you

Uruj Pruthi wrote:

 

Hi All,

 

A very basic question. In what scenarios w.r.t. CA PPM should we go for stored procedures? I have seen scenarios where to update multiple instances there was a scheduled job to run a defined SP in database.

 

Regards,

Uruj

[Anon Anon]

Since the question mentions CA PPM, perhaps this is NOT a CA Gen question and should be moved to another community.

[dennis_itdesign]

Hi Uruj,

Stored Procedures are like Batch jobs on the database which are able to change larger batches of data depending on certain mechanisms or an applied programmability logic.

Whether you need a Stored Procedure or not depends on your need to adjust the standard CA PPM application.

It is possible to create a Stored Procedure which could then be called from CA PPM user interface using a Job, otherwise it is also possible to call Stored Procedures from custom Java Code or from other Stored Procedures.

 

A popular example for a stored procedure could be an interface with another Application:

You could apply a database logic within a stored procedure in order to access a remote database, fetch all the values you require and then update some sets of data within your CA PPM database.

 

Another possible scenario is, that you regularly need to change many datasets of a certain object type. Imagine that you have a project attribute "category" and regularly need to update the Project Manager of all projects depending on the category

You could then build a Stored procedure, make it available in CA PPM and add the parameters "Category" and "Manager". So when you need to change the Project Manager, you can start the job with the required category and the new project manager, which will trigger the stored procedure and then set the new Manager in all projects with matching category.

 

The question you should ask is not "Which scenarios should point to a Stored Procedure", but more "Is there a scenario in CA PPM which I need a Stored Procedure for?"

The latter can be any case where you need to run data-focused batch-jobs on your database.

Hope this helps you.

 

Kind Regards,

Dennis

[Uruj]

Thanks Dennis, your answer exactly clears my doubt.

[mansriva]

You can perfom most of the stufs by clarity process, the clarity process does not support well in a scenario where you have to pass parameters for batch job.

As Execute process does not have inputs parameters expect process it self.

[Doug Kalkowski]

If you are On Demand, Stored Procs are not supported.

 

Really the only time you need to do a stored proc is if you are updating a large number of instances or you need to pass parameters to the procedure.  You can do the majority of things with GEL/XOG, but it may take longer to process.