Developing External SQL Stored Procedures

To get started with external SQL stored procedure support in Db2® for z/OS® Development, follow these steps:
  • Create a file with the .spsql extension.
  • Include the CREATE PROCEDURE statement with either the FENCED or EXTERNAL keyword to indicate an external program.
  • Use the createProcedureExternal: CREATE PROCEDURE (SQL - external) statement template in the SQL Editor to scaffold the definition.

For more information, see Creating external SQL procedures (deprecated) in the Db2 for z/OS documentation.

Example of External SQL stored procedure:

CREATE PROCEDURE TABLENAMES ()
RESULT SETS 1
LANGUAGE SQL
FENCED
COLLID TEST
WLM ENVIRONMENT WLMENV1
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT NAME FROM SYSIBM.SYSTABLES;
OPEN cursor1;
END P1#