Db2 z/os v11 stored proc: native or WLM?

Art McEwen

Db2 z/os v11 stored proc: native or WLM?
So we've got a COBOL IMS 7x24 MPP that we're going to replicate as a DB2 stored procedure and my sp knowledge is a little dated (v9?) so I'm reaching out.

The application is purely a DB2 business lookup with logging to a table so it would be easy to rewrite it as a native SQL stored procedure but as the MPP will live on for some time it means 2 versions of the code in 2 different languages for the foreseeable future. The alternative would be to have a WLM based external stored proc call the same COBOL business routines, 1 set of code (that's already written) called from 2 places. Sweet.

But from what I remember the versioning and change management isn't as strong with the WLM syntax, it's difficult not to have a brief outage when promoting new code because there's only 1 collid in the proc syntax and 1 WLM library. Are Workload managed stored procs on their way out? That's making me lean towards recommending duplicating the code in native.

Any thoughts?


BTW is there an update to this excellent but dated redbook? http://www.redbooks.ibm.com/abstracts/sg247604.html?Open


Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903

James Campbell

Db2 z/os v11 stored proc: native or WLM?
(in response to Art McEwen)
Provided you do not have a fixed DBRM VERSION there isn't a problem.

V WLM,APPLENV=<environment>,REFRESH
starts a new set of WLM started tasks (a 'set of tasks' because more than one actual task
might be needed). These load a new copy of the application code - if that has a new
VERSION you'll get a new package.

The existing, old, tasks, with the old code, VERSION and package continue to execute for
the threads that used them. So both old and new continue side by side. When the old
threads terminate, the old tasks terminate. Leaving just the new ones active.

No outage.

Native SPs, however, have other benefits. It might be better to rewrite the MPPs to use an
SQL CALL to a native SP. So the one set of code is the native SP.

James Campbell


On 3 May 2018 at 17:55, McEwen, Art (MOHLTC) wrote:

>
> So we´ve got a COBOL IMS 7x24 MPP that we´re going to replicate as a DB2 stored procedure
> and my sp knowledge is a little dated (v9?) so I´m reaching out.  
>  
> The application is purely a DB2 business lookup with logging to a table so it would be easy to
> rewrite it as a native SQL stored procedure but as the MPP will live on for some time it means 2
> versions of the code in 2 different languages for the foreseeable future.    The alternative would be
> to have a WLM based external stored proc call the same COBOL business routines, 1 set of code
> (that´s already written) called from 2 places.  Sweet.
>  
> But from what I remember the versioning and change management isn´t as strong with the
> WLM syntax, it´s difficult not to have a brief outage when promoting new code because there´s
> only 1 collid in the proc syntax and 1 WLM library.   Are Workload managed stored procs on their
> way out?    That´s making me lean towards recommending duplicating the code in native.
>  
> Any thoughts?
>  
>  
> BTW is there an update to this excellent but dated redbook?
> http://www.redbooks.ibm.com/abstracts/sg247604.html?Open
>  
>  
> Art McEwen
>

---
This email has been checked for viruses by AVG.
http://www.avg.com

Art McEwen

RE: Db2 z/os v11 stored proc: native or WLM?
(in response to James Campbell)

Thanks James,

Yes we can start a new set of WLM tasks with a different steplib but that impacts every other sp running in the same wlm environment and also assumes the parm list never changes.  

Rewriting the existing MPP isn't on the cards.

James Campbell

Db2 z/os v11 stored proc: native or WLM?
(in response to Art McEwen)
Why change the steplib?

If you want to change the parameters, you have to create a procedure with a different name.
Native and WLM procedures are the same in this aspect.

James Campbell

On 4 May 2018 at 5:57, Art McEwen wrote:

>
> Thanks James,
> Yes we can start a new set of WLM tasks with a different steplib but that impacts every other sp
> running in the same wlm environment and also assumes the parm list never changes.  
> Rewriting the existing MPP isn't on the cards.
>

---
This email has been checked for viruses by AVG.
http://www.avg.com

J&#248;rn Thyssen

RE: Db2 z/os v11 stored proc: native or WLM?
(in response to Art McEwen)

Hi Art,

From where will the stores procedure be called?

if called over DRDA the native SPs will be offload partially to zIIP (60% AFAIK)


In Reply to Art McEwen:

So we've got a COBOL IMS 7x24 MPP that we're going to replicate as a DB2 stored procedure and my sp knowledge is a little dated (v9?) so I'm reaching out.

The application is purely a DB2 business lookup with logging to a table so it would be easy to rewrite it as a native SQL stored procedure but as the MPP will live on for some time it means 2 versions of the code in 2 different languages for the foreseeable future. The alternative would be to have a WLM based external stored proc call the same COBOL business routines, 1 set of code (that's already written) called from 2 places. Sweet.

But from what I remember the versioning and change management isn't as strong with the WLM syntax, it's difficult not to have a brief outage when promoting new code because there's only 1 collid in the proc syntax and 1 WLM library. Are Workload managed stored procs on their way out? That's making me lean towards recommending duplicating the code in native.

Any thoughts?


BTW is there an update to this excellent but dated redbook? http://www.redbooks.ibm.com/abstracts/sg247604.html?Open


Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Michael Hannan

RE: Db2 z/os v11 stored proc: native or WLM?
(in response to Art McEwen)

In Reply to Art McEwen:

So we've got a COBOL IMS 7x24 MPP that we're going to replicate as a DB2 stored procedure and my sp knowledge is a little dated (v9?) so I'm reaching out.

The application is purely a DB2 business lookup with logging to a table so it would be easy to rewrite it as a native SQL stored procedure but as the MPP will live on for some time it means 2 versions of the code in 2 different languages for the foreseeable future. The alternative would be to have a WLM based external stored proc call the same COBOL business routines, 1 set of code (that's already written) called from 2 places. Sweet.

Lets assume that you have a Client Server type situation and you want to use the SP to keep the communications overheads to a minimum for an architectural different use of the process. Could be a web site, with unknown workload. LOL

I have seen a Client Server "Logging to a Table" of end user transaction  times, architected very poorly with very high cost as Distserv Dynamic SQL INSERT and 1 COMMIT for every row inserted, with large rows per day. So makes sense to think carefully how things should be done based on workload.

If the new new Stored Proc will not be called a very large number of times, then you can wear the overheads of the Stored Proc (non native). i.e. when operational cost is not an issue. Overhead of calling a Stored Proc is quite significant.

When operational cost is an issue and usage is heavy, then the Native Stored Proc could well be worth the effort. Overheads much lower and possibility to get zIIP offload.

Look at MIPS used by your current process CICS process in the peak hours, and consider if new usage will be larger, smaller, or unknown. Then decide is usage is so low, it just does not matter. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 06, 2018 - 01:18 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 06, 2018 - 01:20 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 06, 2018 - 01:22 PM (Europe/Berlin)

Art McEwen

RE: Db2 z/os v11 stored proc: native or WLM?
(in response to James Campbell)

Yeah I guess they wouldn't.   Our COBOL promotion isn't well tied to our operations but that's an operational issue not a technical one.

Thanks,

Art.


In Reply to James Campbell:

Why change the steplib?

If you want to change the parameters, you have to create a procedure with a different name.
Native and WLM procedures are the same in this aspect.

James Campbell

On 4 May 2018 at 5:57, Art McEwen wrote:

>
> Thanks James,
> Yes we can start a new set of WLM tasks with a different steplib but that impacts every other sp
> running in the same wlm environment and also assumes the parm list never changes.  
> Rewriting the existing MPP isn't on the cards.
>

---
This email has been checked for viruses by AVG.
http://www.avg.com