Create a DB2 stored procedure

Choose the procedure type

A native SQL procedure stores its SQL PL body in Db2 and is usually the shortest route for transaction-oriented SQL logic. Parameters use IN, OUT, or INOUT mode, and callers use CALL. Its definition, privileges, and dependency information are cataloged, making it easy to deploy with SQL migration tooling.

An external stored procedure has separately compiled and linked source code. Db2 registers it with CREATE PROCEDURE, but the load module must be accessible at invocation. External routines can be written in Assembler, C, C++, COBOL, Java, REXX, or PL/I and normally run under Language Environment and a configured WLM environment.

Prerequisites

Confirm the target subsystem, schema owner, CREATEIN privilege on the schema, required table privileges, and EXECUTE governance. Agree on parameter data types, nullability, result-set behavior, transaction boundaries, security mode, and failure contract before writing the procedure.

For external routines, also confirm the compile, link-edit, package bind, load-library, WLM application environment, and RACF access plan. A successful CREATE PROCEDURE only registers metadata; it does not prove that the WLM address space can load the module.

Create a native SQL procedure

Use a labeled compound statement for the body. Declare variables and handlers before executable statements. State MODIFIES SQL DATA when the procedure changes data, READS SQL DATA for read-only work, or the most restrictive correct classification. Keep COMMIT ownership explicit: callers and procedures must agree on who owns the unit of work.

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE SALES.CREATE_ORDER
  (IN P_CUSTOMER BIGINT, IN P_AMOUNT DECIMAL(11,2), OUT P_ORDER_ID BIGINT)
  LANGUAGE SQL
  MODIFIES SQL DATA
  BEGIN
    SET P_ORDER_ID = NEXT VALUE FOR SALES.ORDER_SEQ;
    INSERT INTO SALES.ORDERS (ORDER_ID, CUSTOMER_ID, AMOUNT)
    VALUES (P_ORDER_ID, P_CUSTOMER, P_AMOUNT);
  END;

Create an external procedure

The CREATE PROCEDURE definition supplies the linkage contract: LANGUAGE, EXTERNAL NAME, PARAMETER STYLE, SQL data-access level, package collection, WLM ENVIRONMENT, and security. PARAMETER STYLE SQL with DBINFO can pass Db2 environment information when appropriate. Match the clause exactly to the compiled program interface.

WLM isolates routine execution from the Db2 database-services address space. Work with systems programmers to size the WLM environment, control STEPLIB libraries, and define operational restart and dump handling. Do not use STAY RESIDENT or authorized execution merely as performance folklore; make a documented, reviewed decision.

1
2
3
4
5
6
7
8
CREATE PROCEDURE HR.GET_EMPLOYEE_NAME
  (IN P_EMPNO CHAR(6), OUT P_NAME VARCHAR(128))
  LANGUAGE COBOL
  READS SQL DATA
  EXTERNAL NAME 'HREMPGET'
  PARAMETER STYLE GENERAL WITH NULLS
  WLM ENVIRONMENT HRWLM
  SECURITY DB2;

Verify results and grant use

Verify the definition in the routine catalog, CALL it with normal and null/error inputs, inspect SQLCODE and output parameters, and test rollback behavior. For external work, test through the intended WLM environment and confirm the package collection and load module match the deployed version.

Grant EXECUTE to application roles, not broad users. A procedure can be a useful API boundary only when its SQL security model is understood. Document whether it runs with invoker, definer, or Db2 security semantics, and audit changes like any other executable database object.

Common errors

SQLCODE failures at CREATE time commonly mean missing CREATEIN or table privileges, invalid parameter syntax, duplicate routine signatures, or an incorrect SQL data-access classification. CALL failures may mean EXECUTE is missing, a parameter type does not match, or a result-set expectation differs from the definition.

External failures frequently point to missing or incompatible load modules, package bind issues, WLM environment configuration, wrong PARAMETER STYLE, or Language Environment runtime options. Start with the Db2 and WLM messages, verify the deployment artifact, then reproduce with the smallest CALL possible.