DB2advanced
DB2 Stored Procedures
Understand why DB2 stored procedures exist, how they're written and called from COBOL, and what tradeoffs they introduce versus embedded SQL.
A stored procedure is a program registered with DB2 and called with a single statement:
A stored procedure is a shared specialist office embedded inside the courthouse itself (the database) that any department can call on for a specific well-defined service, rather than every department keeping its own in-house copy of the same specialist's knowledge and risking each copy drifting out of sync over time.
Key Concepts
1
EXEC SQL CALL GET_CUSTOMER_BALANCE
(:WS-CUST-ID, :WS-BALANCE, :WS-STATUS) END-EXEC.2
It can be written in COBOL, and it runs in an address space DB2 manages rather than in the caller's.
3
Registering it.
CREATE PROCEDURE GET_CUSTOMER_BALANCE
(IN P_CUST_ID CHAR(9),
OUT P_BALANCE DECIMAL(15,2),
OUT P_STATUS CHAR(1))
LANGUAGE COBOL
EXTERNAL NAME 'GETBAL'
WLM ENVIRONMENT WLMENV1;4
IN, OUT and INOUT give the procedure an explicit contract, which is more than embedded SQL in a
program offers.
INOUTINOUT
5
The real argument for it is remote callers. Logic living in a calling program means every caller carries
the SQL. If one of those callers is on another machine, each statement is a network round trip:
6
remote client, embedded SQL: 8 statements = 8 round trips
remote client, stored proc: 1 CALL = 1 round trip7
The eight statements still run — inside DB2, where they are local. That is a genuine and measurable gain,
and it is why distributed access is the strongest case for stored procedures.
8
The second argument is one implementation. The same procedure is called identically from CICS, from
batch, and from a Java client. The logic exists once.
9
What it costs.Debugging spans two contexts. The caller is one address space and the procedure another, so tracing a
problem across the boundary is harder than reading one program.
10
The address space is an operational concern. WLM environments need configuring and sizing. A procedure
that hangs occupies one, and enough of those exhausts the pool.
11
Deployment is now two things. The load module and the CREATE PROCEDURE definition must stay in step.
CREATE PROCEDURE
12
When it is the wrong choice. A procedure that reads one row from one table, called only from programs on
the same machine, adds the operational overhead and returns none of the benefit. That logic belongs as
embedded SQL in the caller.
13
So the balanced answer. Right for shared, data-intensive logic with several callers — especially remote
ones. Wrong as a default wrapper around ordinary single-table access.
14
What the interviewer is probing.1. "What does moving logic into a stored procedure buy you?" Probing: the argument. Stalls:
"Speed." Moves up: one call instead of many round trips, and the logic sits next to the data —
which matters most for a distributed client.
15
2. "What is the difference between native SQL and external procedures?" Probing: the
distinction. Stalls: "Language." Moves up: native SQL PL runs inside the DB2 engine; external
procedures run in a WLM-managed address space, which isolates failures but costs a hop.
16
3. "What is the maintenance concern?" Probing: honesty. Stalls: "None." Moves up: business
logic split between application code and the database is harder to trace and version, so the
boundary needs a rule.
17
4. "How do you return a result set?" Probing: the mechanics. Stalls: "A parameter." *Moves
up:* declare a cursor WITH RETURN and leave it open, which the caller then fetches from.