friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.db2_5.4.0.1/rbafzmstcrtpef.htm

851 lines
48 KiB
HTML
Raw Permalink Blame History

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="dc.language" scheme="rfc1766" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow"/>
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="keywords" content="CREATE PROCEDURE (External) statement,
CREATE PROCEDURE (External), SQL statement, creating, procedure, CCSID clause,
data type for CREATE PROCEDURE (External), CHAR, DOUBLE PRECISION, DECIMAL,
FLOAT, BIGINT, INTEGER, NUMERIC, REAL, SMALLINT, VARCHAR, VARGRAPHIC, GRAPHIC,
FOR BIT DATA clause, FOR MIXED DATA clause, FOR SBCS DATA clause, BINARY,
VARBINARY, BLOB, CLOB, DBCLOB, ROWID, distinct-type, procedure-name, IN clause,
OUT clause, INOUT clause, parameter-name, data-type, AS LOCATOR clause,
LANGUAGE clause, SQL clause, DB2GENERAL clause, DB2SQL clause, GENERAL clause,
GENERAL WITH NULLS clause, JAVA clause, EXTERNAL clause, EXTERNAL NAME clause,
RESULT SETS clause, SPECIFIC clause, DETERMINISTIC clause,
NOT DETERMINISTIC clause, CONTAINS SQL clause, NO SQL clause,
READS SQL DATA clause, MODIFIES SQL DATA clause, CALLED ON NULL INPUT clause,
DEBUG MODE clause, FENCED clause, NOT FENCED clause, PROGRAM TYPE MAIN clause,
DBINFO clause, NO DBINFO clause, SAVEPOINT LEVEL clause, COMMIT ON RETURN clause" />
<title>CREATE PROCEDURE (External)</title>
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
<link rel="stylesheet" type="text/css" href="ic.css" />
</head>
<body>
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
<a name="crtpef"></a>
<h2 id="crtpef"><a href="rbafzmst02.htm#ToC_897">CREATE PROCEDURE (External)</a></h2><a id="idx1917" name="idx1917"></a><a id="idx1918" name="idx1918"></a><a id="idx1919" name="idx1919"></a>
<a name="h2crtpr"></a>
<p id="h2crtpr">The CREATE PROCEDURE (External) statement defines an external
procedure at the current server.</p>
<a name="wq1257"></a>
<h3 id="wq1257"><a href="rbafzmst02.htm#ToC_898">Invocation</a></h3>
<p>This statement can be embedded in an application program or issued interactively.
It is an executable statement that can be dynamically prepared.</p>
<a name="wq1258"></a>
<h3 id="wq1258"><a href="rbafzmst02.htm#ToC_899">Authorization</a></h3>
<p>The privileges held by the authorization ID of the statement must include
at least one of the following: </p>
<ul>
<li>For the SYSPROCS catalog view and SYSPARMS catalog table:
<ul>
<li>The INSERT privilege on the table, and</li>
<li>The system authority *EXECUTE on library QSYS2</li></ul></li>
<li>Administrative authority</li></ul>
<p>If the external program or service program exists, the privileges held
by the authorization ID of the statement must include at least one of the
following: </p>
<ul>
<li>For the external program or service program that is referenced in the
SQL statement:
<ul>
<li>The system authority *EXECUTE on the library that contains the external
program or service program.</li>
<li>The system authority *EXECUTE on the external program or service program,
and</li>
<li>The system authority *CHANGE on the program or service program. The system
needs this authority to update the program or service program object to contain
the information necessary to save/restore the procedure to another system.
If user does not have this authority, the procedure is still created, but
the program or service program object is not updated.</li></ul></li>
<li>Administrative Authority</li></ul>
<p>If a distinct type is referenced, the privileges held by the authorization ID of
the statement must include at least one of the following: </p>
<ul>
<li>For each distinct type identified in the statement:
<ul>
<li>The USAGE privilege on the distinct type, and</li>
<li>The system authority *EXECUTE on the library containing the distinct type</li></ul></li>
<li>Administrative authority</li></ul>
<p>For information on the system authorities corresponding to SQL privileges,
see <a href="rbafzmstgntprc.htm#eqtabler">Corresponding System Authorities When Checking Privileges to a Function or Procedure</a> and <a href="rbafzmstgntudtp.htm#eqtabled">Corresponding System Authorities When Checking Privileges to a Distinct Type</a>.</p>
<a name="wq1259"></a>
<h3 id="wq1259"><a href="rbafzmst02.htm#ToC_900">Syntax</a></h3>
<a href="rbafzmstcrtpef.htm#synscrtproc"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<a name="wq1260"></a>
<div class="fignone" id="wq1260">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn291.htm"
border="0" /></span><a href="#skipsyn-290"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-CREATE PROCEDURE--<span class="italic">procedure-name</span>----------------------------->
>--+-----------------------------------------+--<span class="italic">option-list</span>---->&lt;
'-(--+-------------------------------+--)-'
| .-,-------------------------. |
| V | |
'-----<span class="italic">parameter-declaration</span>---+-'
</pre>
<a name="skipsyn-290" id="skipsyn-290"></a></div>
<a name="wq1261"></a>
<div class="fignone" id="wq1261">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn292.htm"
border="0" /></span><a href="#skipsyn-291"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>parameter-declaration:
.-IN----.
|--+-------+--+----------------+--<span class="italic">data-type</span>--+------------+-----|
+-OUT---+ '-<span class="italic">parameter-name</span>-' '-AS LOCATOR-'
'-INOUT-'
data-type:
|--+-<span class="italic">built-in-type</span>------+---------------------------------------|
'-<span class="italic">distinct-type-name</span>-'
</pre>
<a name="skipsyn-291" id="skipsyn-291"></a></div>
<a name="wq1262"></a>
<div class="fignone" id="wq1262">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn293.htm"
border="0" /></span><a href="#skipsyn-292"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>option-list:
(1)
|--+-----------------------+------------------------------------>
'-LANGUAGE--+-C-------+-'
+-C++-----+
+-CL------+
+-COBOL---+
+-COBOLLE-+
+-FORTRAN-+
+-JAVA----+
+-PLI-----+
+-REXX----+
+-RPG-----+
'-RPGLE---'
.-PARAMETER STYLE SQL--------------------.
>--+----------------------------------------+------------------->
'-+-PARAMETER STYLE DB2SQL-------------+-'
+-PARAMETER STYLE GENERAL------------+
+-PARAMETER STYLE GENERAL WITH NULLS-+
+-PARAMETER STYLE JAVA---------------+
'-PARAMETER STYLE DB2GENERAL---------'
.-NOT DETERMINISTIC-. .-MODIFIES SQL DATA-.
>--+-------------------+--+-------------------+----------------->
'-DETERMINISTIC-----' +-READS SQL DATA----+
+-CONTAINS SQL------+
'-NO SQL------------'
.-CALLED ON NULL INPUT-. .-DYNAMIC RESULT SETS--<span class="italic">0</span>-------.
>--+----------------------+--+------------------------------+--->
'-DYNAMIC RESULT SETS--<span class="italic">integer</span>-'
.-NO DBINFO-. .-FENCED-----.
>--+-----------+--+---------------------+--+------------+------->
'-DBINFO----' +-<span>DISALLOW DEBUG MODE</span>-+ '-NOT FENCED-'
+-<span>ALLOW DEBUG MODE</span>----+
'-<span>DISABLE DEBUG MODE</span>--'
.-PROGRAM TYPE MAIN-.
>--+-------------------+---------------------------------------->
.-EXTERNAL-----------------------------.
>--+--------------------------------------+--------------------->
'-EXTERNAL NAME--<span class="italic">external-program-name</span>-'
.-OLD SAVEPOINT LEVEL-.
>--+-------------------------+--+---------------------+--------->
'-SPECIFIC--<span class="italic">specific-name</span>-' '-NEW SAVEPOINT LEVEL-'
.-COMMIT ON RETURN NO--.
>--+----------------------+-------------------------------------|
'-COMMIT ON RETURN YES-'
</pre>
<a name="skipsyn-292" id="skipsyn-292"></a>
<a name="wq1263"></a>
<div class="notelisttitle" id="wq1263">Notes:</div>
<ol type="1">
<li>The optional clauses can be specified in a different order.</li>
</ol></div>
<a name="wq1265"></a>
<div class="fignone" id="wq1265">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn294.htm"
border="0" /></span><a href="#skipsyn-293"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>built-in-type:
|--+-+---SMALLINT---+--------------------------------------------------------------------------+--|
| +-+-INTEGER-+--+ |
| | '-INT-----' | |
| '---BIGINT-----' |
| .-(5,0)------------------------. |
+-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0--------. | |
| '-NUMERIC-----' '-(--<span class="italic">integer</span>--+-----------+--)-' |
| '-<span class="italic">, integer</span>-' |
| .-(--53--)------. |
+-+-FLOAT--+---------------+-+--------------------------------------------------------------+
| | '-(--<span class="italic">integer</span>--)-' | |
| +-REAL---------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+----' |
| .-(--1--)-------. |
+-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+
| | | '-CHAR------' '-(--<span class="italic">integer</span>--)-' | +-FOR BIT DATA---+ | |
| | '-+-+-CHARACTER-+--VARYING-+--(--<span class="italic">integer</span>--)-' +-FOR SBCS DATA--+ | |
| | | '-CHAR------' | +-FOR MIXED DATA-+ | |
| | '-VARCHAR----------------' '-<span class="italic">ccsid-clause</span>---' | |
| | .-(--1M--)-------------. | |
| '-----+-+-<span>CHARACTER</span>-+--<span>LARGE OBJECT</span>-+------+----------------------+--+----------------+-' |
| | '-<span>CHAR</span>------' | '-(--<span class="italic">integer</span>--+---+--)-' +-FOR SBCS DATA--+ |
| '-<span>CLOB</span>------------------------' +-K-+ +-FOR MIXED DATA-+ |
| +-M-+ '-<span class="italic">ccsid-clause</span>---' |
| '-G-' |
| .-(--1--)-------. |
+-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+
| | '-(--<span class="italic">integer</span>--)-' | '-<span class="italic">ccsid-clause</span>-' |
| +-+-GRAPHIC VARYING-+--(--<span class="italic">integer</span>--)---+ |
| | '-VARGRAPHIC------' | |
| | .-(--1M--)-------------. | |
| '---DBCLOB----+----------------------+-' |
| '-(--<span class="italic">integer</span>--+---+--)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
| .-(--1--)-------. |
+-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+
| | | '-(--<span class="italic">integer</span>--)-' | | |
| | '-+-BINARY VARYING-+--(--<span class="italic">integer</span>--)-' | |
| | '-VARBINARY------' | |
| | .-(--1M--)-------------. | |
| '---+-BLOB----------------+----+----------------------+-' |
| '-BINARY LARGE OBJECT-' '-(--<span class="italic">integer</span>--+---+--)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------+----------------------------------------------------------------+
| | .-(--0--)-. | |
| +-TIME--+---------+------+ |
| | .-(--6--)-. | |
| '-TIMESTAMP--+---------+-' |
'---ROWID-----------------------------------------------------------------------------------'
ccsid-clause:
.-NOT NORMALIZED-.
|--CCSID--<span class="italic">integer</span>--+----------------+---------------------------|
'-NORMALIZED-----'
</pre>
<a name="skipsyn-293" id="skipsyn-293"></a></div>
<p><a id="idx1920" name="idx1920"></a><a id="idx1921" name="idx1921"></a><a id="idx1922" name="idx1922"></a><a id="idx1923" name="idx1923"></a><a id="idx1924" name="idx1924"></a><a id="idx1925" name="idx1925"></a><a id="idx1926" name="idx1926"></a><a id="idx1927" name="idx1927"></a><a id="idx1928" name="idx1928"></a><a id="idx1929" name="idx1929"></a><a id="idx1930" name="idx1930"></a><a id="idx1931" name="idx1931"></a><a id="idx1932" name="idx1932"></a><a id="idx1933" name="idx1933"></a><a id="idx1934" name="idx1934"></a><a id="idx1935" name="idx1935"></a><a id="idx1936" name="idx1936"></a><a id="idx1937" name="idx1937"></a><a id="idx1938" name="idx1938"></a><a id="idx1939" name="idx1939"></a><a id="idx1940" name="idx1940"></a><a id="idx1941" name="idx1941"></a><a id="idx1942" name="idx1942"></a></p>
<a name="synscrtproc"></a>
<h3 id="synscrtproc"><a href="rbafzmst02.htm#ToC_901">Description</a></h3>
<p></p>
<dl class="parml">
<dt class="bold"><var class="pv">procedure-name</var> </dt><a id="idx1943" name="idx1943"></a>
<dd>Names the procedure. The combination of name, schema name, the number
of parameters must not identify a procedure that exists at the current server.
<p>For SQL naming, the procedure will be created in the schema specified by the
implicit or explicit qualifier.</p>
<p>For system naming, the procedure will
be created in the schema specified by the qualifier. If no qualifier is specified:</p>
<ul>
<li>If the value of the CURRENT SCHEMA special register is *LIBL, the procedure
will be created in the current library (*CURLIB).</li>
<li>Otherwise, the procedure will be created in the current schema.</li></ul>
</dd>
<dt class="bold">(<var class="pv">parameter-declaration,...)</var></dt>
<dd>Specifies the number of parameters of the procedure and the data type
of each parameter. A parameter for a procedure can be used only for input,
only for output, or for both input and output. Although not required, you
can give each parameter a name.
<p>The maximum number of parameters
allowed in CREATE PROCEDURE depends on the language and the parameter style:</p>
<ul>
<li>If PARAMETER STYLE GENERAL is specified, in C and C++, the maximum is
1024. Otherwise, the maximum is 255.</li>
<li>If PARAMETER STYLE GENERAL WITH NULLS is specified, in C and C++, the
maximum is 1023. Otherwise, the maximum is 254.</li>
<li>If PARAMETER STYLE SQL or PARAMETER STYLE DB2SQL is specified, in C and
C++, the maximum is 508. Otherwise, the maximum is 90.</li>
<li>If PARAMETER STYLE JAVA or PARAMETER STYLE DB2GENERAL is specified, the
maximum is 90.</li></ul><p class="indatacontent">The maximum number of parameters is also limited by the maximum number
of parameters allowed by the licensed program used to compile the external
program or service program.</p>
<dl class="parml">
<dt class="bold">IN</dt><a id="idx1944" name="idx1944"></a>
<dd>Identifies the parameter as an input parameter to the procedure. Any
changes made to the parameter within the procedure are not available
to the calling SQL application when control is returned.<sup class="fn"><a href="rbafzmstcrtpef.htm#fn27">66</a></sup>
</dd>
<dt class="bold">OUT </dt><a id="idx1945" name="idx1945"></a>
<dd>Identifies the parameter as an output parameter that is returned by
the procedure.
<p>A DataLink or a distinct type based on a DataLink may not
be specified as an output parameter.</p>
</dd>
<dt class="bold">INOUT </dt><a id="idx1946" name="idx1946"></a>
<dd>Identifies the parameter as both an input and output parameter for the
procedure.
<p>A DataLink or a distinct type based on a DataLink may not be
specified as an input and output parameter.</p>
</dd>
<dt class="bold"><var class="pv">parameter-name</var> </dt><a id="idx1947" name="idx1947"></a>
<dd>Names the parameter. The name cannot be the same as any other <var class="pv">parameter-name</var> for the procedure.
</dd>
<dt class="bold"><var class="pv">data-type</var> </dt><a id="idx1948" name="idx1948"></a>
<dd>Specifies the data type of the parameter. The data type can
be a built-in data type or a distinct type.
<dl class="parml">
<dt class="bold"><var class="pv">built-in-type</var></dt>
<dd>Specifies a built-in data type. For a more complete description of each
built-in data type, see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>. Some data types are not supported
in all languages. For details on the mapping between the SQL data types and
host language data types, see <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book. Built-in data type specifications can be specified if they correspond
to the language that is used to write the procedure.
</dd>
<dt class="bold"><var class="pv">distinct-type-name</var></dt>
<dd>Specifies a user-defined distinct type. The length, precision, or scale
attributes for the parameter are those of the source type of the distinct
type (those specified on CREATE DISTINCT TYPE). For more information on creating
a distinct type, see <a href="rbafzmstcrtudt.htm#crtudt">CREATE DISTINCT TYPE</a>.
<p>If the name of the distinct type is
unqualified, the database manager resolves the schema name by searching the
schemas in the SQL path.</p>
</dd>
</dl>
<p>If a CCSID is specified, the parameter will be converted to that
CCSID prior to passing it to the procedure. If a CCSID is not specified, the
CCSID is determined by the default CCSID at the current server at the time
the procedure is invoked.</p>
</dd><a id="idx1949" name="idx1949"></a>
<dt class="bold">AS LOCATOR</dt>
<dd>Specifies that the parameter is a locator to the value rather than the
actual value. You can specify AS LOCATOR only if the parameter has a LOB data
type or a distinct type based on a LOB data type. If AS LOCATOR is specified,
FOR SBCS DATA or FOR MIXED DATA must not be specified.
</dd>
</dl>
</dd>
<dt class="bold">LANGUAGE </dt><a id="idx1950" name="idx1950"></a>
<dd>Specifies the language that the external program or service program
is written in. The language clause is required if the external program is
a REXX procedure.
<p>If LANGUAGE is not specified, the LANGUAGE is determined
from the attribute information associated with the external program or service
program at the time the procedure is created. If the attribute information
associated with the program or service program does not identify a recognizable
language or the program or service program cannot be found, then the language
is assumed to be C. </p>
<dl class="parml">
<dt class="bold">C</dt>
<dd>The external program is written in C.
</dd>
<dt class="bold">C++&nbsp;&nbsp;&nbsp;</dt>
<dd>The external program is written in C++.
</dd>
<dt class="bold">CL</dt>
<dd>The external program is written in CL.
</dd>
<dt class="bold">COBOL</dt>
<dd>The external program is written in COBOL.
</dd>
<dt class="bold">COBOLLE</dt>
<dd>The external program is written in ILE COBOL.
</dd>
<dt class="bold">FORTRAN</dt>
<dd>The external program is written in FORTRAN.
</dd>
<dt class="bold">JAVA</dt>
<dd>The external program is written in JAVA.
</dd>
<dt class="bold">PLI</dt>
<dd>The external program is written in PL/I.
</dd>
<dt class="bold">REXX</dt>
<dd>The external program is a REXX procedure.
</dd>
<dt class="bold">RPG</dt>
<dd>The external program is written in RPG.
</dd>
<dt class="bold">RPGLE</dt>
<dd>The external program is written in ILE RPG.
</dd>
</dl>
</dd>
<dt class="bold">PARAMETER STYLE</dt>
<dd>Specifies the conventions used for passing parameters to and returning
the values from procedures:
<dl class="parml"><a id="idx1951" name="idx1951"></a>
<dt class="bold">SQL</dt>
<dd>Specifies that in addition to the parameters on the CALL statement,
several additional parameters are passed to the procedure. The parameters
are defined to be in the following order:
<ul>
<li>The first N parameters are the parameters that are specified on the CREATE
PROCEDURE statement.</li>
<li>N parameters for indicator variables for the parameters.</li>
<li>A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates
the success or failure of the procedure. The SQLSTATE returned is assigned
by the external program.
<p>The user may set the SQLSTATE to any valid value
in the external program to return an error or warning from the procedure.</p></li>
<li>A VARCHAR(517) input parameter for the fully qualified procedure name.</li>
<li>A VARCHAR(128) input parameter for the specific name.</li>
<li>A VARCHAR(70) output parameter for the message text.</li></ul>For more information about the parameters passed, see the include <span>sqludf</span> in the appropriate source file in library QSYSINC.
For example, for C, sqludf can be found in QSYSINC/H.
<p>PARAMETER STYLE SQL
cannot be used with LANGUAGE JAVA.</p>
</dd>
<dt class="bold">DB2GENERAL </dt><a id="idx1952" name="idx1952"></a>
<dd>Specifies that the procedure will use a parameter passing convention
that is defined for use with Java&trade; methods.
<p>PARAMETER STYLE DB2GENERAL
can only be specified with LANGUAGE JAVA. For details on passing parameters
in JAVA, see the <a href="../rzaha/whatitis.htm">IBM&reg; Developer Kit for Java</a>.</p>
</dd>
<dt class="bold">DB2SQL</dt><a id="idx1953" name="idx1953"></a>
<dd>Specifies that in addition to the parameters on the CALL statement,
several additional parameters are passed to the procedure. DB2SQL is identical
to the SQL parameter style, except that the following additional parameter
may be passed as the last parameter:
<ul>
<li>A parameter for the dbinfo structure, if DBINFO was specified on the CREATE
PROCEDURE statement.</li></ul>For more information about the parameters passed, see the include <span>sqludf</span> in the appropriate source file in library QSYSINC.
For example, for C, sqludf can be found in QSYSINC/H.
<p>PARAMETER STYLE DB2SQL
cannot be used with LANGUAGE JAVA.</p>
</dd>
<dt class="bold">GENERAL </dt><a id="idx1954" name="idx1954"></a>
<dd>Specifies that the procedure will use a parameter passing mechanism
where the procedure receives the parameters specified on the CALL. Additional
arguments are not passed for indicator variables.
<p>PARAMETER STYLE GENERAL
cannot be used with LANGUAGE JAVA.</p>
</dd>
<dt class="bold">GENERAL WITH NULLS </dt><a id="idx1955" name="idx1955"></a>
<dd>Specifies that in addition to the parameters on the CALL statement as
specified in GENERAL, another argument is passed to the procedure.
This additional argument contains an indicator array with an element for each
of the parameters of the CALL statement. In C, this would be an array of short
INTs. For more information about how the indicators are handled, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book.
<p>PARAMETER STYLE GENERAL WITH NULLS
cannot be used with LANGUAGE JAVA.</p>
</dd>
<dt class="bold">JAVA </dt><a id="idx1956" name="idx1956"></a>
<dd>Specifies that the procedure will use a parameter passing
convention that conforms to the Java language and ISO/IEC FCD 9075-13:2003, <span class="italic">Information technology - Database languages - SQL - Part 13: Java Routines and Types (SQL/JRT)</span> specification. INOUT and OUT
parameters will be passed as single entry arrays to facilitate returning values.
<p>PARAMETER STYLE JAVA can only be specified with LANGUAGE JAVA. For increased
portability, you should write Java procedures that use the PARAMETER
STYLE JAVA conventions. For details on passing parameters in JAVA, see the <a href="../rzaha/whatitis.htm">IBM Developer Kit for Java</a> book.</p>
</dd>
</dl>
<p>Note that the language of the external procedure determines how
the parameters are passed. For example, in C, any VARCHAR or CHAR parameters
are passed as NUL-terminated strings. For more information, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book. For Java routines, see the <a href="../rzaha/whatitis.htm">IBM Developer
Kit for Java</a>.</p>
</dd>
<dt class="bold">EXTERNAL NAME <var class="pv">external-program-name</var> </dt><a id="idx1957" name="idx1957"></a><a id="idx1958" name="idx1958"></a>
<dd>Specifies the program or service program that will be executed when
the procedure is called by the CALL statement. The program name must identify
a program or service program that exists at the application server at the time the
procedure is called. If the naming option is *SYS and the name is not qualified:
<ul>
<li>The current path will be used to search for the program or service program
at the time the procedure is called.</li>
<li>*LIBL will be used to search for the program or service program at the
time grants or revokes are performed on the procedure.</li></ul>
<p>The validity of the name is checked at the application server. If the format
of the name is not correct, an error is returned.</p>
<p>If <var class="pv">external-program-name</var> is not specified, the external program name is assumed to be the same
as the procedure name.</p>
<p>The external program or service program need
not exist at the time the procedure is created, but it must exist at the time
the procedure is called.</p>
<p>CONNECT, SET CONNECTION, RELEASE, DISCONNECT,
and SET TRANSACTION statements are not allowed in a procedure that is running
on a remote application server. COMMIT and ROLLBACK statements are not allowed in
an ATOMIC SQL procedure or in a procedure that is running on a connection
to a remote application server.</p>
</dd>
<dt class="bold">DYNAMIC RESULT SETS <var class="pv">integer</var> </dt><a id="idx1959" name="idx1959"></a>
<dd>Specifies the maximum number of result sets that can be returned from
the procedure. <var class="pv">integer</var> must be greater than or equal to
zero and less than 32768. If zero is specified, no result sets are returned.
If the SET RESULT SETS statement is issued, the number of results returned
is the minimum of the number of result sets specified on this keyword and
the SET RESULT SETS statement. If the SET RESULT SETS statement specifies
a number larger than the maximum number of result sets, a warning is returned.
Note that any result sets from cursors that have a RETURN TO CLIENT attribute
are included in the number of result sets of the outermost procedure.
<p>The
result sets are scrollable if a cursor is used to return a result set and
the cursor is scrollable. If a cursor is used to return a result set, the
result set starts with the current position. Thus, if 5 FETCH NEXT operations
have been performed prior to returning from the procedure, the result set
will start with the 6th row of the result set.</p>
<p>Result sets are only
returned if both the following are true: </p>
<ul>
<li>the procedure is directly called or if the procedure is a RETURN
TO CLIENT procedure and is indirectly called from ODBC, JDBC, OLE DB, .NET,
the SQL Call Level Interface, or the iSeries Access Family Optimized SQL API, and</li>
<li>the external program does not have an attribute of ACTGRP(*NEW).</li></ul><p class="indatacontent">For more information about result sets see <a href="rbafzmsthsetrs.htm#hsetrs">SET RESULT SETS</a>.</p>
</dd>
<dt class="bold">SPECIFIC <var class="pv">specific-name</var> <a id="idx1960" name="idx1960"></a></dt>
<dd>Provides a unique name for the procedure. The name is implicitly or
explicitly qualified with a schema name. The name, including the schema name,
must not identify the specific name of another procedure or procedure that
exists at the current server. If unqualified, the implicit qualifier is the
same as the qualifier of the procedure name. If qualified, the qualifier must
be the same as the qualifier of the procedure name.
<p>If <var class="pv">specific-name</var> is not specified, it is the same as the procedure name. If a function
or procedure with that specific name already exists, a unique name is generated
similar to the rules used to generate unique table names.</p>
</dd>
<dt class="bold">DETERMINISTIC <span class="base">or</span> NOT DETERMINISTIC</dt><a id="idx1961" name="idx1961"></a><a id="idx1962" name="idx1962"></a>
<dd>Specifies whether the procedure returns the same results each time the
procedure is called with the same IN and INOUT arguments.
<dl class="parml">
<dt class="bold">NOT DETERMINISTIC</dt>
<dd>The procedure may not return the same result each time the procedure
is called with the same IN and INOUT arguments, even when the referenced data
in the database has not changed.
</dd>
<dt class="bold">DETERMINISTIC</dt>
<dd>The procedure always returns the same results each time the procedure
is called with the same IN and INOUT arguments, provided the referenced data
in the database has not changed.
</dd>
</dl>
</dd>
<dt class="bold">CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA, <span class="base">or</span> NO SQL</dt>
<dd>Specifies which SQL statements, if any, may be executed in the procedure
or any routine called from this procedure. See <a href="rbafzmstdiftab.htm#diftab">Appendix B. Characteristics of SQL statements</a> for a
detailed list of the SQL statements that can be executed under each data access
indication.
<dl class="parml">
<dt class="bold">CONTAINS SQL</dt><a id="idx1963" name="idx1963"></a>
<dd>Specifies that SQL statements that neither read nor modify SQL data
can be executed by the procedure.
</dd>
<dt class="bold">NO SQL</dt><a id="idx1964" name="idx1964"></a>
<dd>Specifies that the procedure cannot execute any SQL statements.
</dd>
<dt class="bold">READS SQL DATA</dt><a id="idx1965" name="idx1965"></a>
<dd>Specifies that SQL statements that do not modify SQL data can be included
in the procedure.
</dd>
<dt class="bold">MODIFIES SQL DATA</dt><a id="idx1966" name="idx1966"></a>
<dd>Specifies that the procedure can execute any SQL statement except statements
that are not supported in procedures.
</dd>
</dl>
</dd>
<dt class="bold">CALLED ON NULL INPUT</dt><a id="idx1967" name="idx1967"></a>
<dd>Specifies that the procedure is to be invoked, if any, or all, argument
values are null, making the procedure responsible for testing for null argument
values. The procedure can return a null or nonnull value.
</dd>
<dt class="bold">DISALLOW DEBUG MODE, ALLOW DEBUG MODE, <span class="base">or</span> DISABLE DEBUG MODE</dt><a id="idx1968" name="idx1968"></a>
<dd>Indicates whether the procedure is created so it can be debugged by
the Unified Debugger. If DEBUG MODE is not specified, the procedure will be created
with the debug mode specified by the CURRENT DEBUG MODE special register.
<p>DEBUG MODE can only be specified with LANGUAGE JAVA.</p>
<dl class="parml">
<dt class="bold">DISALLOW DEBUG MODE</dt>
<dd>The procedure cannot be debugged by the Unified Debugger. When
the DEBUG MODE attribute of the procedure is DISALLOW, the procedure can be
subsequently altered to change the debug mode attribute.
</dd>
<dt class="bold">ALLOW DEBUG MODE</dt>
<dd>The procedure can be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is ALLOW, the procedure can be subsequently altered
to change the debug mode attribute.
</dd>
<dt class="bold">DISABLE DEBUG MODE</dt>
<dd>The procedure cannot be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is DISABLE, the procedure cannot be subsequently
altered to change the debug mode attribute.
</dd>
</dl>
</dd>
<dt class="bold">FENCED <span class="base">or</span> NOT FENCED</dt><a id="idx1969" name="idx1969"></a><a id="idx1970" name="idx1970"></a>
<dd> This parameter is allowed for compatibility with other products and
is not used by DB2 UDB for iSeries.
</dd>
<dt class="bold">PROGRAM TYPE MAIN</dt><a id="idx1971" name="idx1971"></a>
<dd>Specifies that the procedure executes as a main routine.
</dd>
<dt class="bold">DBINFO</dt><a id="idx1972" name="idx1972"></a><a id="idx1973" name="idx1973"></a>
<dd>Specifies whether or not the procedure requires the database information
be passed.
<dl class="parml">
<dt class="bold">DBINFO</dt>
<dd>Specifies that the database manager should pass a structure containing
status information to the procedure. <a href="rbafzmstcrtpef.htm#dbinfotb2a">Table 51</a> contains a
description of the DBINFO structure. Detailed information about the DBINFO
structure can be found in include <span>sqludf</span> in the appropriate
source file in library QSYSINC. For example, for C, sqludf can be found in
QSYSINC/H.
<p>DBINFO is only allowed with PARAMETER STYLE DB2SQL.</p>
<a name="dbinfotb2a"></a>
<table id="dbinfotb2a" width="100%" summary="" border="1" frame="border" rules="all">
<caption>Table 51. DBINFO fields</caption>
<thead valign="bottom">
<tr>
<th id="wq1266" width="21%" align="left" valign="bottom">Field</th>
<th id="wq1267" width="15%" align="left" valign="bottom">Data Type</th>
<th id="wq1268" width="62%" align="left" valign="bottom">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq1266">Relational database</td>
<td align="left" valign="top" headers="wq1267">VARCHAR(128)</td>
<td align="left" valign="top" headers="wq1268">The name of the current server.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1266">Authorization ID</td>
<td align="left" valign="top" headers="wq1267">VARCHAR(128)</td>
<td align="left" valign="top" headers="wq1268">The run-time authorization ID.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1266">CCSID Information</td>
<td align="left" valign="top" headers="wq1267">
<div class="lines">INTEGER<br />
INTEGER<br />
INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">INTEGER<br />
INTEGER<br />
INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">INTEGER<br />
INTEGER<br />
INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">CHAR(8)<br />
</div></td>
<td align="left" valign="top" headers="wq1268">The CCSID information of the job. Three sets
of three CCSIDs are returned. The following information identifies the three
CCSIDs in each set:
<ul>
<li>SBCS CCSID</li>
<li>DBCS CCSID</li>
<li>Mixed CCSID</li></ul>Following the three sets of CCSIDs is an integer that indicates which
set of three sets of CCSIDs is applicable and eight bytes of reserved space.
<p>If a CCSID is not explicitly specified for a parameter on the CREATE PROCEDURE
statement, the input string is assumed to be encoded in the CCSID of the job
at the time the procedure is executed. If the CCSID of the input string is
not the same as the CCSID of the parameter, the input string passed to the
external procedure will be converted before calling the external program.</p></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1266">Target Column</td>
<td align="left" valign="top" headers="wq1267">VARCHAR(128)
<div class="lines">VARCHAR(128)<br />
</div>
<div class="lines">VARCHAR(128)<br />
</div></td>
<td align="left" valign="top" headers="wq1268">Not applicable for a call to a procedure.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1266">Version and release</td>
<td align="left" valign="top" headers="wq1267">CHAR(8)</td>
<td align="left" valign="top" headers="wq1268">The version, release, and modification level
of the database manager.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1266">Platform</td>
<td align="left" valign="top" headers="wq1267">INTEGER</td>
<td align="left" valign="top" headers="wq1268">The server's platform type.</td>
</tr>
</tbody>
</table>
</dd>
<dt class="bold">NO DBINFO</dt>
<dd>Specifies that the procedure does not require the database information
to be passed.
</dd>
</dl>
</dd>
<dt class="bold">OLD SAVEPOINT LEVEL <span class="base">or</span> NEW SAVEPOINT LEVEL</dt>
<dd>Specifies whether a new savepoint level is to be created on entry to
the procedure.
<dl class="parml"><a id="idx1974" name="idx1974"></a>
<dt class="bold">OLD SAVEPOINT LEVEL</dt>
<dd>A new savepoint level is not created. Any SAVEPOINT statements issued
within the procedure with OLD SAVEPOINT LEVEL implicitly or explicitly specified
on the SAVEPOINT statement are created at the same savepoint level as the
caller of the procedure. This is the default.
</dd>
<dt class="bold">NEW SAVEPOINT LEVEL</dt>
<dd>A new savepoint level is created on entry to the procedure. Any savepoints
set within the procedure are created at a savepoint level that is nested deeper
than the level at which this procedure was invoked. Therefore, the name of
any new savepoint set within the procedure will not conflict with any existing
savepoints set in higher savepoint levels (such as the savepoint level of
the calling program or service program) with the same name.
</dd>
</dl>
</dd>
<dt class="bold">COMMIT ON RETURN</dt>
<dd>Specifies whether the database manager commits the transaction immediately on return
from the procedure.
<dl class="parml"><a id="idx1975" name="idx1975"></a>
<dt class="bold">NO</dt>
<dd>The database manager does not issue a commit when the procedure returns. NO is
the default.
</dd>
<dt class="bold">YES</dt>
<dd>The database manager issues a commit if the procedure returns successfully. If
the procedure returns with an error, a commit is not issued.
<p>The commit
operation includes the work that is performed by the calling application process
and the procedure.<sup class="fn"><a id="wq1269" name="wq1269" href="rbafzmstcrtpef.htm#wq1270">67</a></sup></p>
<p>If the procedure returns result
sets, the cursors that are associated with the result sets must have been
defined as WITH HOLD to be usable after the commit.</p>
</dd>
</dl>
</dd>
</dl>
<a name="wq1271"></a>
<h3 id="wq1271"><a href="rbafzmst02.htm#ToC_902">Notes</a></h3>
<p><span class="bold">General considerations for defining procedures:</span> See <a href="rbafzmstcreatep.htm#createp">CREATE PROCEDURE</a> for general information on defining procedures.</p>
<p><span class="bold">Language considerations:</span> For information
needed to create the programs for a procedure, see the <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book.</p>
<p><span class="bold">Owner privileges:</span> The owner is authorized
to call the procedure (EXECUTE) and grant others the privilege to call the
procedure. See <a href="rbafzmstgntprc.htm#gntprc">GRANT (Function or Procedure Privileges)</a>. For more information on ownership of
the object, see <a href="rbafzmstauthown.htm#authown">Authorization, privileges and object ownership</a>.</p>
<p><span class="bold">Error handling considerations:</span> Values
of arguments passed to a procedure which correspond to OUT parameters are
undefined and those which correspond to INOUT parameters are unchanged when
an error is returned by the procedure.</p>
<p><span class="bold">Creating the procedure:</span> When an external procedure
associated with an ILE external program or service program is created, an
attempt is made to save the procedure's attributes in the associated program
or service program object. If the *PGM object is saved and then restored to
this or another system, the catalogs are automatically updated with those
attributes.</p>
<p>The attributes can be saved for external procedures subject to the following
restrictions:</p>
<ul>
<li>The external program library must not be QSYS.</li>
<li>The external program must exist when the CREATE PROCEDURE statement is
issued.</li>
<li>The external program must be an ILE *PGM or *SRVPGM object.</li></ul><p class="indatacontent">If the object cannot be updated, the procedure will still be created.</p>
<p>During restore of the procedure:</p>
<ul>
<li>If the specific name was specified when the procedure was originally created
and it is not unique, an error is issued.</li>
<li>If the specific name was not specified, a unique name is generated if
necessary.</li>
<li>If the same procedure name and number of parameters already exists,
<ul>
<li>If the external program name or service program name is the same as the
one registered in the catalog, the procedure information in the catalog will
be replaced.</li>
<li>Otherwise, the procedure cannot be registered, and an error is issued.</li></ul></li></ul>
<p><span class="bold">Invoking the procedure:</span> If a DECLARE PROCEDURE statement
defines a procedure with the same name as a created procedure, and a static
CALL statement where the procedure name is not identified by a variable is
executed from the same source program, the attributes from the DECLARE PROCEDURE
statement will be used rather than the attributes from the CREATE PROCEDURE
statement.</p>
<p>The CREATE PROCEDURE statement applies to static and dynamic CALL statements
as well as to a CALL statement where the procedure name is identified by a
variable.</p>
<p>When an external procedure is invoked, it runs in whatever activation group
was specified when the external program or service program was created. However,
ACTGRP(*CALLER) should normally be used so that the procedure runs in the
same activation group as the calling program.</p>
<p><span class="bold">Notes for Java procedures:</span> To be able to run Java procedures, you must have the <a href="../rzaha/whatitis.htm">IBM Developer Kit
for Java</a> installed on your system. Otherwise, an SQLCODE of -443 will
be returned and a CPDB521 message will be placed in the job log.</p>
<p>If an error occurs while running a Java procedure, an SQLCODE of -443 will
be returned. Depending on the error, other messages may exist in the job log
of the job where the procedure was run.</p>
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
supported for compatibility to prior releases. These keywords are non-standard
and should not be used:</p>
<ul>
<li>The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC
and DETERMINISTIC.</li>
<li>The keywords NULL CALL and NOT NULL CALL can be used as synonyms for CALLED
ON NULL INPUT and RETURNS NULL ON NULL INPUT.</li>
<li>The keywords SIMPLE CALL can be used as a synonym for GENERAL.</li>
<li>The value DB2GENRL may be used as a synonym for DB2GENERAL.</li>
<li>DYNAMIC RESULT SET, RESULT SETS, and RESULT SET may be used as synonyms
for DYNAMIC RESULT SETS.</li>
<li>The keywords PARAMETER STYLE in the PARAMETER STYLE clause are optional.</li></ul>
<a name="wq1272"></a>
<h3 id="wq1272"><a href="rbafzmst02.htm#ToC_903">Example</a></h3>
<p><span class="italic">Example 1:</span>&nbsp; Create the procedure definition
for a procedure, written in Java, that is passed a part number and
returns the cost of the part and the quantity that are currently available. </p>
<pre class="xmp"> <span class="bold">CREATE PROCEDURE</span> PARTS_ON_HAND (<span class="bold">IN</span> PARTNUM <span class="bold">INTEGER</span>,
<span class="bold">OUT</span> COST <span class="bold">DECIMAL</span>(7,2),
<span class="bold">OUT</span> QUANTITY <span class="bold">INTEGER</span>)
<span class="bold">LANGUAGE JAVA</span>
<span class="bold">PARAMETER STYLE JAVA</span>
<span class="bold">EXTERNAL NAME</span> 'parts.onhand'</pre>
<p><span class="italic">Example 2:</span>&nbsp; Create the procedure definition
for a procedure, written in C, that is passed an assembly number and returns
the number of parts that make up the assembly, total part cost and a result
set that lists the part numbers, quantity and unit cost of each part.</p>
<pre class="xmp"> <span class="bold">CREATE PROCEDURE</span> ASSEMBLY_PARTS (<span class="bold">IN</span> ASSEMBLY_NUM <span class="bold">INTEGER</span>,
<span class="bold">OUT</span> NUM_PARTS <span class="bold">INTEGER</span>,
<span class="bold">OUT</span> COST <span class="bold">DOUBLE</span>)
<span class="bold">LANGUAGE C</span>
<span class="bold">PARAMETER STYLE GENERAL</span>
<span class="bold">DYNAMIC RESULT SETS</span> 1
<span class="bold">FENCED</span>
<span class="bold">EXTERNAL NAME</span> ASSEMBLY</pre>
<hr /><div class="fnnum"><a id="fn27" name="fn27">66</a>.</div>
<div class="fntext">When the language type is REXX, all parameters must be input
parameters.</div><div class="fnnum"><a id="wq1270" name="wq1270" href="rbafzmstcrtpef.htm#wq1269">67</a>.</div>
<div class="fntext">If the external program or service program was created
with ACTGRP(*NEW) and the job commitment definition is not used, the work
that is performed in the procedure will be committed or rolled back as a result
of the activation group ending.</div>
<br />
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcreatep.htm">Previous Page</a> | <a href="rbafzmstcrtpsf.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
<a href="rbafzmstindex.htm#index">Index</a> ]
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
</body>
</html>
Reference in New Issue View Git Blame Copy Permalink