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

8/19/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.)

The performance advantages of native SQL procedures:

zIIP offload:

·       A native SQL procedure’s execution is up to 60% zIIP-offload-able when the procedure is called through the Db2 distributed data facility (DDF)

·       CALL could be issued by client program through IBM Data Server Driver (or Db2 Connect), or could be executed by client program using Db2’s REST interface

·       SQL procedure is up to 60% zIIP eligible when called through DDF because SQL that runs under a preemptable SRB in the DDF address space gets that level of zIIP offload

·       External stored procedure (e.g., one written in language such as COBOL or C) always executes under a TCB in a stored procedure address space, and so is NOT zIIP-eligible even when called via DDF

·       Note: if DDF-using application called external stored procedure and that procedure calls native SQL procedure, latter will NOT be zIIP-eligible because it will execute under external stored procedure’s TCB (a native SQL procedure never has its own task – it always runs under the task of its caller)

That zIIP offload advantage is shared by other SQL PL routines (compiled SQL

scalar functions or advanced triggers) executed by DDF-using applications

Elimination of TCB wait time:

·       When Db2-accessing program calls an external stored procedure, the external stored procedure has to be scheduled for execution and calling program’s Db2 thread has to be switched from caller’s task to external stored procedure’s task

-       That can lead to two performance-impacting issues:

§  If the z/OS system is really busy, there can be a delay in scheduling external stored procedure for execution (especially if stored procedure address space has a too-low priority – more on that to come)

§  Switching Db2 thread from caller’s task to external stored procedure’s task can introduce some delay

·       No scheduling, no thread-switch delays for native SQL procedure, because it never has its own task (always runs under task of caller – it is just a package) This advantage of native SQL procedures extends to compiled SQL scalar functions

-       Consider UDF that is referenced in inner query of a correlated subquery – if outer query qualifies a large number of rows, UDF could be invoked thousands of times, leading to thousands of task switches and elevated “TCB wait” time if UDF is external (no “TCB wait” time if UDF written in SQL PL)

·       Another performance booster: high-performance DBATs (Distributed Database Access Thread)

-       These DBATs improve performance for external and native SQL procedures

-       A DBAT becomes a high-performance DBAT when a package bound with

-       RELEASE(DEALLOCATE) is allocated for execution to the DBAT

§  The package in question could be associated with a stored procedure

·       At transaction completion, a high-performance DBAT will stay dedicated to the connection through which it was instantiated, and can be reused 200 times

-       CPU efficiency advantage: RELEASE(DEALLOCATE) packages (and associated table space locks) remain allocated to DBAT until it is terminated (versus being released at each COMMIT)

§  Table space-level locks are almost always non-exclusive

-       In addition to RELEASE(DEALLOCATE) packages, need DDF to be enabled for high-performance DBATs (can be done via Db2 command -MODIFY DDF PKGREL(BNDOPT))

§  MODIFY DDF PKGREL(COMMIT) can be used to temporarily turn high-performance DBATs off – might need to do that if high-performance DBATs could interfere with BIND/REBIND/ALTER work

Another performance booster: high-performance DBATs (Distributed Database Access Thread)

·       These DBATs improve performance for external and native SQL procedures

·       A DBAT becomes a high-performance DBAT when a package bound with RELEASE(DEALLOCATE) is allocated for execution to the DBAT

-       The package in question could be associated with a stored procedure

·       At transaction completion, a high-performance DBAT will stay dedicated to the connection through which it was instantiated, and can be reused 200 times

-       CPU efficiency advantage: RELEASE(DEALLOCATE) packages (and associated table space locks) remain allocated to DBAT until it is terminated (versus being released at each COMMIT)

§  Table space-level locks are almost always non-exclusive

-       In addition to RELEASE(DEALLOCATE) packages, need DDF to be enabled for high-performance DBATs (can be done via Db2 command -MODIFY DDF PKGREL(BNDOPT))

§  MODIFY DDF PKGREL(COMMIT) can be used to temporarily turn high-performance DBATs off – might need to do that if high-performance DBATs could interfere with BIND/REBIND/ALTER work

Example of a “native” SQL UDF