Db2

Db2 for z/OS - Stored & SQL Procedures Blog 

Episode #3

Here’s a brief history of Db2 for z/OS Stored Procedures – an initial view of their evolution:

Db2 for z/OS V4 – The Stored Procedures functionality was introduced.

Db2 for z/OS V5 – The caller of stored procedures can fetch rows from cursor declared and

         opened in the stored procedure.

                               WLM-managed stored procedure address spaces introduced (the

                               Db2-managed stored procedure address space went away with Db2 9 - SPAS).

                               Support for Java Stored Procedures was initiated.

Db2 for z/OS V6 - DDL support was established: CREATE, ALTER, DROP PROCEDURE.

                             The SYSPROCEDURES catalog table replaced by SYSROUTINES.

So now the IBM for z/OS development staff – managers and roll-up-your sleeves developers – decided to really enhance this “feature” of Db2 – let’s create something new and exciting! They created SQL Procedure Language (SQL PL) and it was introduced with Version 7. (COBOL programmers were envious, methinks.)

Let’s take a deeper look into what they put together – BTW, it also has an evolution.

A way to code Db2 stored procedures using only SQL, which was enabled via introduction of a new category of SQL statements, called control statements (referring to logic flow control).

Here are some examples: IF, WHILE, ITERATE, LOOP, GOTO

Here are some of the Db2 V7 SQL procedures – pros and cons:

·       Pro:

o   Expanded the pool of people who could develop Db2 for z/OS stored procedures

·       Cons:

o   As part of program preparation, a Db2 Version 7 (or 8) stored procedure written in SQL PL was converted into a C language program with embedded SQL DML statements

§  C language programs are generally not as CPU-efficient as COBOL programs in a z/OS system, so Db2 for z/OS users tended to favor COBOL stored procedures over “external” SQL procedures.

So why would anyone convert this into a C language module? That is a good question.  Since I have been in IT wearing various hats, such as  a Project Manager, Client Consultant, Mainframe Value Manager, System Designer, rolled-up-sleeves developer, etc., I would venture to say that this was a combination of a product development preference by a technical manager and a developer who knew C and wanted it to make a worldwide impression.  As we will see in my future episodes, this too will evolve, right off the charts.

Ok, Happy Easter/Spring to you all.  Comments are always welcome!!!

[I posted this on March 31st, 2024 in the "Db2 for z/OS and its ecosystem" community and am reposting it here for continuity with my other 2 "episodes" dealing with this topic.]

#ibmchampion #Data Management Global, #Db2, #Db2 for z/OS, #Db2 Tools for z/OS, #Integrated Analytics System, #Netezza Performance Server, #Informix, #Open Source Offerings