Db2

 View Only

Db2 z/OS Stored Procedures & SQL Procedures/Evolution - Introduction (Episode #1)

By Thomas Halinski posted Sun December 31, 2023 08:30 PM

  

Hi everyone - Happy New Year's Eve, and when you read this in 2024, then, Happy New Year!

I am working on a niche area of Db2 - i.e. Db2 Stored Procedures (SPs) and, something similar but with a different name, Db2 SQL Procedures (SQLPs) - definitions to follow in subsequent BLOG episodes.  This started when I presented at IDUG EMEA 2022 in Edinburgh, Scotland.  We were discussing Stored Procedure Address Spaces, why they are important to know and how they evolved.  In that presentation, I simply highlighted their creation and  high level functionality. For example the initial SP address space was SPAS and then it used the Workload Manager ones in which SPs were executed. 

Attendees asked more detail questions and my presentation was not set up to answer them - e.g. "what's the difference between Native and External SPs?".  "what's an SQL Procedure, both External and Native?"  Eye opening questions, to be sure.

In this blog and also in my new presentation that I am preparing for IDUG (hoping it gets accepted), I plan on explaining the differences and giving the evolution of these 2 "Procedure" types (SPs & SQLPs).

So Happy New Year for now and please feel free to chime in on your experiences with these as I go forward with future "Blog Episodes" on this niche subject area of Db2 for z/OS.

Here's a hint at some of the differences between External SPs vs. Native SQLPs, graphically speaking:

#Db2Z#db2z/os#IBMDb2forz/OS#db2z13#Db2World

4 comments
49 views

Permalink

Comments

Thomas Lieser
Mon September 02, 2024 09:23 AM

Thomas Halinsky:

We are using StPs/UdFs (native SQL) in DB2/zOS quite heavily - it really works wonders - a bit of a shame though that Datastudio is phased out before the Developer Extension can replace it.

Thomas Lieser
Mon September 02, 2024 09:21 AM

Jay Munnangi:

AFAIK there is no "SLEEP" procedure in DB2/zOS - if you need that you will need to code a procedure that queries the current timestamp - be sure to set a proper ASU TIME LIMIT so as not let it run away (and best check the parameters as not to block DB2 too long) - this proc will be "running" not waiting, "actively waiting", which probably is not what you want anyway.

Thomas Halinski
Fri January 05, 2024 04:28 PM

Hello Jay Munnangi - My apologies for getting back to you so late.

I have looked into this (Yay Google) and have yet to come up with a good answer.  However, a couple of questions please:

1) Are you creating a Native SQL Procedure or an External SQL Procedure or an External Stored Procedure? (The difference is important.)

2) If you are creating an external Stored Procedure - you are using a source language and DML.  You can use the source language (e.g. COBOL) to do the "sleeping" you want embedded into the DML logic.

3) SQL Procedures are written in PL SQL.  Thus - I have difficulty locating the right syntax/function for Db2 z/OS - similar to Oracle's "SLEEP" example you give.  

So fellow SQL GURUs, can anyone help us out here with a simple and direct way of "pausing/sleeping/waiting" an SQL Procedure using PL SQL?

Hope this helps us getting a good reply.

Jay Munnangi
Tue January 02, 2024 09:28 AM

Perfect Explanation, perfect timing. I am working on building native DB2 sp's and this is a perfect example for understanding the core differences.

Thomas, i need help in identifying a syntax to implement the logic in one of my SP, hope you can help me.

i want syntax for wait/sleep/pause in SP. example dbms_lock.sleep(3).

my SP should pause for few seconds before it execute further SQL statements.

TIA.