DB2 stored procedure types on z/OS

Stored procedure fundamentals

IBM’s definition: a compiled program that can execute SQL statements and is stored at a local or remote Db2 server. You call it from an application (embedded SQL, JDBC, ODBC, CLP) with the SQL CALL statement. Db2 also ships supplied procedures (utilities, admin, performance) that you typically create during install or migration.

Without a procedure, a workstation program sends each SQL statement separately. Each send/receive costs CPU and network time, and design mistakes hold locks while the client thinks. With a procedure, the client sends one CALL; the server runs the series; locks are not held across those network transmissions.

Other reasons shops use them:

• Static SQL from a dynamic client — the package has the table privileges; the user only needs EXECUTE on the procedure
• Encapsulation — one maintained copy of a business rule instead of the same UPDATE in ten COBOL programs
• Result sets — the procedure opens cursors and the client fetches rows (next page in this section)
• Integrity of SQL text — the workstation never sees the host variables or the statement text

The three CREATE PROCEDURE families

Native SQL procedures

A native SQL procedure is written entirely in SQL, including SQL PL control statements. The body sits in the CREATE PROCEDURE statement. IBM’s rule: if CREATE PROCEDURE does not specify FENCED or EXTERNAL, the procedure is native.

Preparation: one SQL statement. No precompile, compile, or link-edit of a host program. Db2 implicitly binds a package that contains the body (and sometimes generated statements). Each CALL executes that package one or more times.

1
2
3
4
5
6
7
8
9
10
11
CREATE PROCEDURE HR.RAISE_SALARY
     (IN  P_EMPNO  CHAR(6),
      IN  P_RATE   DECIMAL(6,2))
     LANGUAGE SQL
     MODIFIES SQL DATA
     COMMIT ON RETURN NO
   BEGIN
     UPDATE HR.EMP
        SET SALARY = SALARY * (1 + P_RATE)
      WHERE EMPNO = P_EMPNO;
   END

Native procedures usually perform better than the old external SQL kind, support versions (VERSION, ACTIVATE VERSION, CURRENT ROUTINE VERSION), and can run as zIIP-eligible SQL work. They do not have an associated external load module.

You still configure the stored-procedure environment if the native procedure is defined with ALLOW DEBUG MODE or DISALLOW DEBUG MODE (not needed for DISABLE DEBUG MODE), or if it calls an external procedure, external SQL procedure, or user-defined function.

SQL PL procedures

SQL PL is the structured language IBM documents for native SQL procedure bodies, SQL functions, and advanced triggers: compound statements, variables, IF/WHILE/LOOP, handlers, SIGNAL/RESIGNAL, GET DIAGNOSTICS. A “SQL PL procedure” on this site means a native SQL procedure. The next tutorial section walks BEGIN/END, ATOMIC, and control flow in detail.

SQL PL is not COBOL. There are no Area A/B columns. Parameters are SQL variables (no colon prefix inside the procedure). The body must be a single SQL statement, which is almost always a compound BEGIN … END.

External stored procedures

An external procedure’s body is a host-language program: Assembler, C, C++, COBOL, Java, PL/I, or REXX. The source is separate from CREATE PROCEDURE. You prepare it like any Db2 program (precompile, compile, link, bind a package if it contains SQL). CREATE PROCEDURE names the load module with EXTERNAL NAME and the language with LANGUAGE.

All of these programs run using Language Environment. COBOL and C++ may use object-oriented extensions. The procedure can be PROGRAM TYPE MAIN or SUB. Each CALL, the procedure logic decides whether and how often its SQL package runs.

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE HR.UPDATESALARY1
     (IN EMPNUMBR CHAR(10),
      IN RATE     DECIMAL(6,2))
     LANGUAGE COBOL
     EXTERNAL NAME UPDSAL
     WLM ENVIRONMENT PAYROLL
     PARAMETER STYLE GENERAL WITH NULLS
     DYNAMIC RESULT SETS 0
     COMMIT ON RETURN NO;

Notes from IBM’s sample: UPDATESALARY1 is the SQL name; UPDSAL is the load module; LANGUAGE COBOL marks it external. You still GRANT EXECUTE on the procedure. The COBOL program receives the parameters according to PARAMETER STYLE (SQL, GENERAL, GENERAL WITH NULLS, JAVA, and so on).

WLM stored procedures

On modern Db2, WLM-established address spaces run external stored procedures (and UDFs, and native SQL when debugging or calling out). You define a WLM application environment (JCL procedure, STEPLIB, NUMTCB, RACF). CREATE PROCEDURE … WLM ENVIRONMENT name routes the CALL to that environment. WLM ENVIRONMENT (name, *) can mean “this environment when called from an application; nested calls may stay with the caller’s space” — check the exact clause you use.

Operations that matter:

• VARY WLM,APPLENV=name,REFRESH after you replace a load module — START/STOP PROCEDURE do not refresh modules already in the SPAS
• NUMTCB is a max task count; too high makes the address space look heavy to WLM
• APF-authorized STEPLIB is required if the procedure must run authorized
• STAY RESIDENT YES/NO and PROGRAM TYPE MAIN vs SUB affect whether LE storage persists between CALLs — do not assume variables survive

Native SQL procedures are not “WLM stored procedures” in the everyday sense: they run as SQL packages. People still say “WLM procedures” for the whole external ecosystem.

External SQL procedures (deprecated)

These also put SQL PL in CREATE PROCEDURE, but Db2 generates a C program and a package. They need the WLM environment. IBM’s guidance: native SQL procedures are more fully supported, easier to maintain, and typically faster. Do not start new ones. Migrate remaining external SQL procedures to native when you can.

Choosing a type

• New SQL-only logic — native SQL procedure (SQL PL)
• Existing COBOL that already talks to Db2 — wrap it as LANGUAGE COBOL EXTERNAL, or call it from a thin native SQL shell
• Java / JDBC shops — LANGUAGE JAVA EXTERNAL (JAR, WLM)
• Need APIs SQL PL cannot express — external in the language that has the API

Explain It Like I'm Five

A stored procedure is a recipe kept in the restaurant kitchen instead of emailed step-by-step from your house. You shout one order (CALL) and the kitchen does all the chopping. Native SQL is a recipe written on the kitchen whiteboard in the kitchen’s own language (SQL PL). External is hiring a COBOL chef who works in a separate room that WLM unlocks. External SQL was a translator who rewrote the whiteboard into C — IBM does not want new chefs using that translator. WLM is the manager who opens the extra rooms and decides how many chefs stand in each one.

Exercises

1. List three network round trips a client would make without a procedure to SELECT, UPDATE, and INSERT, and explain how one CALL replaces them.
2. Write a five-line native CREATE PROCEDURE that updates one column. Which clause proves it is native rather than EXTERNAL?
3. A shop has a 20-year COBOL load module. Which CREATE PROCEDURE clauses are required, and which WLM operator command is needed after a relink?
4. Why is GRANT EXECUTE on the procedure a security win compared with GRANT UPDATE on the base table to every client ID?
5. When must a native SQL procedure still have a WLM environment configured?