Page 1 of 1

DB2 Stored Procedure In WLM.

PostPosted: Sun Jan 31, 2010 12:51 pm
by tosaurabh20
Hi,

My client is planning to develop an application. Currently architects are finalising the model and approach.

They are suggesting to create several stored procedures which will run in WLM environment in order to implement multi threading and real time updates. however i am bit doubtful on this approach due to below reasons:

1) It is not advisable to incorporate whole business logic in stored procedures.
2) These stored procedures will be calling several batch cobol db2 programs in order to accomplish whole business logic. I am doubtful that since multiple threads of stored procedures will be running at same time, is it possible to make a call to same batch pgm through multiple threads at same time.
3) there could be performance issues too.

Please guide me on above points.

Thanks
Saurabh

Re: DB2 Stored Procedure In WLM.

PostPosted: Sun Jan 31, 2010 11:21 pm
by Robert Sample
several stored procedures which will run in WLM environment
I'm not sure what this means. WLM manages workloads, including batch initiators if so configured, but as far as I know there is no WLM environment to execute in -- there are batch programs and online programs but no WLM programs.

Re: DB2 Stored Procedure In WLM.

PostPosted: Mon Feb 01, 2010 5:28 am
by dick scherrer
Hello and welcome to the forum,

It sounds like there is a mis-communication between your group and the client. . . Everyone needs to have the same terminology and a correct understanding.

It is very common for multiple batch process to access the same tables concurrently. And yes, performance can be an issue when multiple processes are run together. Also, if the code is not well designed, there will be timeouts and deadlocks when the "tiny testing" is done and full volume producton is run.

Why are stored procedures seen as something other than programs?

Re: DB2 Stored Procedure In WLM.

PostPosted: Mon Feb 01, 2010 11:11 am
by tosaurabh20
Hi Dick,

Thanks for clarifications. But i will require some more from your side.

I will explain whole thing with an example : Suppose there is an stored procedure A. This stored procedures will be control by WLM and will get triggered by a Replication set on a DB2 trigger table. This means that each insert in trigger table will initiate a separate thread of stored procedure A.

Now each thread of stored procedure which got intiated through trigger table, will call series of batch programs say B1 to B10. Now my concern is, whether this is possible to call same series of batch programs through different threads of Stored procedure A at the same time.

Initially we did face a problem of -927 while calling a batch program through stored procedure A but we fixed it by link-editing the batch program twice so that it get link edit wit DSNALI as well as with DSNRLI. so that if that batch program is called by any other program outside the new application, it will continue to work.

I hope i am able to make u understand. I just wanted to know the implications of this architecture.


Thanks
Saurabh

Re: DB2 Stored Procedure In WLM.

PostPosted: Mon Feb 01, 2010 10:19 pm
by tosaurabh20
Please provide your inputs on above post.

Thanks
Saurabh

Re: DB2 Stored Procedure In WLM.

PostPosted: Thu Feb 11, 2010 8:15 pm
by Anuj Dhawan
Now my concern is, whether this is possible to call same series of batch programs through different threads of Stored procedure A at the same time.
Yes that possible. And -927 should not be related to calling multiple COBOL programs from a stored-procedure.

so that if that batch program is called by any other program outside the new application
I'm not sure what this means?

Re: DB2 Stored Procedure In WLM.

PostPosted: Fri Feb 12, 2010 4:35 am
by dick scherrer
Hello,

Please provide your inputs on above post.
I've not replied as i wasn't sure what else to add. . .

I would however suggest that the batch jobs be scheduled to run serially rather than concurrently. . . An easy task for the scheduling software. . .