DB2 Stored Procedure In WLM.



IBM's flagship relational database management system

DB2 Stored Procedure In WLM.

Postby tosaurabh20 » Sun Jan 31, 2010 12:51 pm

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
tosaurabh20
 
Posts: 3
Joined: Sun Jan 31, 2010 12:44 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Stored Procedure In WLM.

 

Re: DB2 Stored Procedure In WLM.

Postby Robert Sample » Sun Jan 31, 2010 11:21 pm

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.
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: East Dubuque, Illinois
Has thanked: 1 time
Been thanked: 222 times

Re: DB2 Stored Procedure In WLM.

Postby dick scherrer » Mon Feb 01, 2010 5:28 am

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: DB2 Stored Procedure In WLM.

Postby tosaurabh20 » Mon Feb 01, 2010 11:11 am

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
tosaurabh20
 
Posts: 3
Joined: Sun Jan 31, 2010 12:44 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Stored Procedure In WLM.

Postby tosaurabh20 » Mon Feb 01, 2010 10:19 pm

Please provide your inputs on above post.

Thanks
Saurabh
tosaurabh20
 
Posts: 3
Joined: Sun Jan 31, 2010 12:44 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Stored Procedure In WLM.

Postby Anuj Dhawan » Thu Feb 11, 2010 8:15 pm

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?
Anuj
Anuj Dhawan
 
Posts: 274
Joined: Mon Feb 25, 2008 3:53 am
Location: Mumbai, India
Has thanked: 6 times
Been thanked: 4 times

Re: DB2 Stored Procedure In WLM.

Postby dick scherrer » Fri Feb 12, 2010 4:35 am

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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post