-SPBX (External SQL stored procedures)
The -SPBX command is used to create stored procedures that are written in SQL Procedures language (SPL).
The command includes the CREATE PROCEDURE statement and compile, link, and bind options.
- The SQLID option specifies the current SQLID.
- The BUILDSPROC option identifies the stored procedure that Execution must invoke to build the SPL procedure. The default is SYSPROC.DSNTPSMP.
- The PCOPTS option specifies the information that is necessary to precompile the SQL statements.
- The CMOPTS option specifies the information that is necessary to compile the SQL statements.
- The PLKDOPTS option specifies the information that is necessary to prelink the application.
- The LKEDOPTS option specifies the information that is necessary to link the application.
- The BINDOPTS option specifies the information that is necessary to bind the application.
-SPBX 000350 CREATE PROCEDURE TESTSPBX.SPSQ000A
( INOUT IOSF1CHAR3U CHAR (3) FOR SBCS DATA CCSID EBCDIC
, INOUT IOSF1CHAR10U CHAR (11) FOR SBCS DATA CCSID EBCDIC
, INOUT IOSF1INTEGERU INTEGER
, INOUT IOSF1SMALLINTU SMALLINT
, INOUT IOSF1FLOAT1U FLOAT
, INOUT IOSF1FLOAT22U FLOAT
, INOUT IOSF1REALU FLOAT
, INOUT IOSF1DOUBLEU FLOAT
, INOUT IOSF1DEC102U DECIMAL (10,2)
, INOUT IOSF1VCHAR10 VARCHAR (10) FOR SBCS DATA CCSID EBCDIC
, INOUT IOSF1DATEU DATE
, INOUT IOSF1TIMEU TIME
, INOUT IOSF1TIMESTAMPU TIMESTAMP
, INOUT IOSF1DELIMU DECIMAL (10,2)
)
DYNAMIC RESULT SET 0
LANGUAGE SQL
PARAMETER STYLE GENERAL WITH NULLS
FENCED
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
NO DBINFO
NO COLLID
WLM ENVIRONMENT DEEG1
ASUTIME NO LIMIT
STAY RESIDENT NO
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
PARAMETER CCSID EBCDIC
EXTERNAL NAME 'SPBXSP0A'
-- BEGINNING OF SPL PROGRAM
P1: BEGIN NOT ATOMIC
DECLARE MANAGER_ID CHAR(6);
DECLARE BONUSPRM DECIMAL(15,2) DEFAULT 0;
-- GIVE THE MANAGER A RAISE
UPDATE EMP
SET BONUS = BONUSPRM
WHERE EMPNO = MANAGER_ID;
END P1
SQLID=RDACRJ
BUILDSPROC=SYSPROC.DSNTPSMP
PCOPTS= SOURCE,XREF,MAR(1,80),STDSQL(NO)
CMOPTS= SOURCE LIST MAR(1,80) NOSEQ LO RENT
PLKDOPTS=
LKEDOPTS= AMODE=31,RMODE=ANY,MAP,RENT
BINDOPTS= PACKAGE(DSN8ES61) MEMBER(DSN8ES2) QUALIFIER(DSN8610) ACT(REP) ISO(CS)
-AUTH 000400 USER
( INOUT IOSF1CHAR3U CHAR (3) FOR SBCS DATA CCSID EBCDIC
, INOUT IOSF1CHAR10U CHAR (11) FOR SBCS DATA CCSID EBCDIC
, INOUT IOSF1INTEGERU INTEGER
, INOUT IOSF1SMALLINTU SMALLINT
, INOUT IOSF1FLOAT1U FLOAT
, INOUT IOSF1FLOAT22U FLOAT
, INOUT IOSF1REALU FLOAT
, INOUT IOSF1DOUBLEU FLOAT
, INOUT IOSF1DEC102U DECIMAL (10,2)
, INOUT IOSF1VCHAR10 VARCHAR (10) FOR SBCS DATA CCSID EBCDIC
, INOUT IOSF1DATEU DATE
, INOUT IOSF1TIMEU TIME
, INOUT IOSF1TIMESTAMPU TIMESTAMP
, INOUT IOSF1DELIMU DECIMAL (10,2)
)
DYNAMIC RESULT SET 0
LANGUAGE SQL
PARAMETER STYLE GENERAL WITH NULLS
FENCED
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
NO DBINFO
NO COLLID
WLM ENVIRONMENT DEEG1
ASUTIME NO LIMIT
STAY RESIDENT NO
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
PARAMETER CCSID EBCDIC
EXTERNAL NAME 'SPBXSP0A'
-- BEGINNING OF SPL PROGRAM
P1: BEGIN NOT ATOMIC
DECLARE MANAGER_ID CHAR(6);
DECLARE BONUSPRM DECIMAL(15,2) DEFAULT 0;
-- GIVE THE MANAGER A RAISE
UPDATE EMP
SET BONUS = BONUSPRM
WHERE EMPNO = MANAGER_ID;
END P1
SQLID=RDACRJ
BUILDSPROC=SYSPROC.DSNTPSMP
PCOPTS= SOURCE,XREF,MAR(1,80),STDSQL(NO)
CMOPTS= SOURCE LIST MAR(1,80) NOSEQ LO RENT
PLKDOPTS=
LKEDOPTS= AMODE=31,RMODE=ANY,MAP,RENT
BINDOPTS= PACKAGE(DSN8ES61) MEMBER(DSN8ES2) QUALIFIER(DSN8610) ACT(REP) ISO(CS)
-AUTH 000400 USER
Related topic
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*