Db2 Stored & SQL Procedures - Episode #8: (In the Db2 User Group – Recap from Robert Catterall’s Tridex)

9/13/2025

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

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.

Db2 for z/OS V7 - SQL Procedure Language (SQL PL) introduced.

Db2 for z/OS V9 -  native SQL procedures

§Key characteristics:

- No external-to-Db2 executable (no object or load module) – a native SQL procedure’s package

is its one and only executable

- Executes in DBM1 (Database Services Address Space – DBAS), the Db2 database services address space (as do all packages) – not in a stored procedure address space

- Runs under caller’s task (an external stored procedure runs under its own TCB in a stored

procedure address space)

- Superior functionality

• Native SQL procedures – not external stored procedures – are where we have seen greatest advances in stored procedure functionality

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.)

Security advantages of stored procedures(native and external):

Security: static SQL

·       Stored procedures provide a means of packaging static SQL in a form that can be dynamically invoked by client programs

·       When SQL DML statements are static and issued by way of a stored procedure, invoking application’s authorization ID does not require table access privileges (SELECT, INSERT, UPDATE, DELETE)

o   Instead, the application’s ID requires only EXECUTE privilege on stored procedure

o   Even more secure: do not grant package EXECUTE privilege to application’s authorization ID

o   Instead, create role, grant execute on stored procedure to role, and create trusted context that restricts use of role’s privileges to a particular application ID connecting from the IP address (or addresses) of the server on which the application runs

o   That way, if someone tries to use the application’s Db2 credentials (ID and password) from a PC (for example), they will not be able to access any data because the ID has no Db2 privileges

Security: database schema abstraction

·       If someone wants to hack into your database, he will have an easier time of it if he knows names of tables and columns

·       When “table-touching” SQL statements are packaged in stored procedures, stored procedure developers require knowledge of database details but developers of calling programs do not

o   Data security is boosted by limiting the number of people with detailed knowledge of the database schema

An SQL UDF with a scalar fullselect