537 lines
32 KiB
HTML
537 lines
32 KiB
HTML
<?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 FUNCTION (SQL Scalar) statement,
|
|
CREATE FUNCTION (SQL Scalar), SQL statements, creating, function, SQL,
|
|
CCSID clause, data type for CREATE FUNCTION (SQL Scalar), 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, DATALINK, ROWID, distinct-type,
|
|
in CREATE FUNCTION (SQL Scalar), function-name, RETURNS clause, data-type,
|
|
LANGUAGE clause, SPECIFIC clause, DETERMINISTIC clause, NOT DETERMINISTIC clause,
|
|
EXTERNAL ACTION clause, NO EXTERNAL ACTION clause, CONTAINS SQL clause,
|
|
READS SQL DATA clause, MODIFIES SQL DATA clause, STATIC DISPATCH clause,
|
|
RETURNS NULL ON NULL INPUT clause, CALLED ON NULL INPUT clause, FENCED clause,
|
|
NOT FENCED clause, ALLOW PARALLEL clause, DISALLOW PARALLEL clause" />
|
|
<title>CREATE FUNCTION (SQL Scalar)</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="cfsc"></a>
|
|
<h2 id="cfsc"><a href="rbafzmst02.htm#ToC_874">CREATE FUNCTION (SQL Scalar)</a></h2><a id="idx1798" name="idx1798"></a><a id="idx1799" name="idx1799"></a><a id="idx1800" name="idx1800"></a><a id="idx1801" name="idx1801"></a><a id="idx1802" name="idx1802"></a>
|
|
<p>This CREATE FUNCTION (SQL Scalar) statement creates an SQL function at
|
|
the current server. The function returns a single result.</p>
|
|
<a name="wq1229"></a>
|
|
<h3 id="wq1229"><a href="rbafzmst02.htm#ToC_875">Invocation</a></h3>
|
|
<p>You can embed this statement in an application program, or you can issue
|
|
this statement interactively. It is an executable statement that can be dynamically
|
|
prepared.</p>
|
|
<a name="wq1230"></a>
|
|
<h3 id="wq1230"><a href="rbafzmst02.htm#ToC_876">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>The privilege to create in the schema. For more information, see <a href="rbafzmstauthown.htm#createin">Privileges necessary to create in a schema</a>.</li>
|
|
<li>Administrative authority</li></ul>
|
|
<p>The privileges held by the authorization id of the statement must include
|
|
at least one of the following: </p>
|
|
<ul>
|
|
<li>For the SYSFUNCS 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>The privileges held by the authorization ID of the statement must also
|
|
include at least one of the following:</p>
|
|
<ul>
|
|
<li>The following system authorities:
|
|
<ul>
|
|
<li>*USE to the Create Service Program (CRTSRVPGM) command or</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="rbafzmstgnt.htm#eqtablet">Corresponding System Authorities When Checking Privileges to a Table or View</a> and <a href="rbafzmstgntudtp.htm#eqtabled">Corresponding System Authorities When Checking Privileges to a Distinct Type</a>.</p>
|
|
<a name="wq1231"></a>
|
|
<h3 id="wq1231"><a href="rbafzmst02.htm#ToC_877">Syntax</a></h3>
|
|
<a href="rbafzmstcfsc.htm#synscrtfunss"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq1232"></a>
|
|
<div class="fignone" id="wq1232">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn282.htm"
|
|
border="0" /></span><a href="#skipsyn-281"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-CREATE FUNCTION--<span class="italic">function-name</span>------------------------------->
|
|
|
|
>--(--+---------------------------+--)--RETURNS--<span class="italic">data-type2</span>----->
|
|
| .-,---------------------. |
|
|
| V | |
|
|
'---<span class="italic">parameter-declaration</span>-+-'
|
|
|
|
>--LANGUAGE SQL--<span class="italic">option-list</span>--+----------------------+--<span class="italic">SQL-routine-body</span>-><
|
|
'-<span class="italic">SET OPTION-statement</span>-'
|
|
|
|
parameter-declaration:
|
|
|
|
|--<span class="italic">parameter-name</span>--<span class="italic">data-type1</span>-----------------------------------|
|
|
|
|
data-type:
|
|
|
|
|--+-<span class="italic">built-in-type</span>------+---------------------------------------|
|
|
'-<span class="italic">distinct-type-name</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-281" id="skipsyn-281"></a></div>
|
|
<a name="wq1233"></a>
|
|
<div class="fignone" id="wq1233">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn283.htm"
|
|
border="0" /></span><a href="#skipsyn-282"><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--+---------+-' |
|
|
| .-(--200--)-----. |
|
|
+---DATALINK--+---------------+--+--------------+-------------------------------------------+
|
|
| '-(--<span class="italic">integer</span>--)-' '-<span class="italic">ccsid-clause</span>-' |
|
|
'---ROWID-----------------------------------------------------------------------------------'
|
|
|
|
ccsid-clause:
|
|
|
|
.-NOT NORMALIZED-.
|
|
|--CCSID--<span class="italic">integer</span>--+----------------+---------------------------|
|
|
'-NORMALIZED-----'
|
|
|
|
</pre>
|
|
<a name="skipsyn-282" id="skipsyn-282"></a></div><a id="idx1803" name="idx1803"></a><a id="idx1804" name="idx1804"></a><a id="idx1805" name="idx1805"></a><a id="idx1806" name="idx1806"></a><a id="idx1807" name="idx1807"></a><a id="idx1808" name="idx1808"></a><a id="idx1809" name="idx1809"></a><a id="idx1810" name="idx1810"></a><a id="idx1811" name="idx1811"></a><a id="idx1812" name="idx1812"></a><a id="idx1813" name="idx1813"></a><a id="idx1814" name="idx1814"></a><a id="idx1815" name="idx1815"></a><a id="idx1816" name="idx1816"></a><a id="idx1817" name="idx1817"></a><a id="idx1818" name="idx1818"></a><a id="idx1819" name="idx1819"></a><a id="idx1820" name="idx1820"></a><a id="idx1821" name="idx1821"></a><a id="idx1822" name="idx1822"></a><a id="idx1823" name="idx1823"></a><a id="idx1824" name="idx1824"></a><a id="idx1825" name="idx1825"></a><a id="idx1826" name="idx1826"></a>
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn284.htm"
|
|
border="0" /></span><a href="#skipsyn-283"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>option-list:
|
|
|
|
(1) .-NOT DETERMINISTIC-.
|
|
|--+-------------------------+-------+-------------------+------>
|
|
'-SPECIFIC--<span class="italic">specific-name</span>-' '-DETERMINISTIC-----'
|
|
|
|
.-EXTERNAL ACTION----. .-READS SQL DATA----.
|
|
>--+--------------------+--+-------------------+---------------->
|
|
'-NO EXTERNAL ACTION-' +-CONTAINS SQL------+
|
|
'-MODIFIES SQL DATA-'
|
|
|
|
.-STATIC DISPATCH-. .-CALLED ON NULL INPUT-------.
|
|
>--+-----------------+--+----------------------------+---------->
|
|
'-RETURNS NULL ON NULL INPUT-'
|
|
|
|
.-FENCED-----.
|
|
>--+------------+--+-------------------+------------------------|
|
|
'-NOT FENCED-' +-ALLOW PARALLEL----+
|
|
'-DISALLOW PARALLEL-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-283" id="skipsyn-283"></a>
|
|
<a name="wq1234"></a>
|
|
<div class="notelisttitle" id="wq1234">Notes:</div>
|
|
<ol type="1">
|
|
<li>The optional clauses can be specified in a different
|
|
order.</li>
|
|
</ol>
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn285.htm"
|
|
border="0" /></span><a href="#skipsyn-284"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>SQL-routine-body:
|
|
|
|
|----<span class="italic">SQL-control-statement</span>--------------------------------------|
|
|
|
|
</pre>
|
|
<a name="skipsyn-284" id="skipsyn-284"></a>
|
|
<a name="synscrtfunss"></a>
|
|
<h3 id="synscrtfunss"><a href="rbafzmst02.htm#ToC_878">Description</a></h3><a id="idx1827" name="idx1827"></a>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">function-name</var></dt>
|
|
<dd>Names the user-defined function. The combination of name, schema name,
|
|
the number of parameters, and the data type of each parameter (without regard
|
|
for any length, precision, scale, or CCSID attributes of the data type) must
|
|
not identify a user-defined function that exists at the current server.
|
|
<p>For SQL naming, the function will be created in the schema specified by the
|
|
implicit or explicit qualifier.</p>
|
|
<p>For system naming, the function will
|
|
be created in the schema that is specified by the qualifier. If no qualifier
|
|
is specified:</p>
|
|
<ul>
|
|
<li>If the value of the CURRENT SCHEMA special register is *LIBL, the function
|
|
will be created in the current library (*CURLIB).</li>
|
|
<li>Otherwise, the function will be created in the current schema.</li></ul>
|
|
<p>In general, more than one function can have the same name if the
|
|
function signature of each function is unique.</p>
|
|
<p>Certain function names
|
|
are reserved for system use. For more information see <a href="rbafzmstcreatef.htm#fnamerst">Choosing the Schema and Function Name</a>.</p>
|
|
</dd>
|
|
<dt class="bold">(<var class="pv">parameter-declaration,...)</var></dt>
|
|
<dd>Specifies the number of parameters of the function and the data type
|
|
of each parameter. Although not required, you can give each parameter a name.
|
|
<p>The maximum number of parameters allowed is 90.</p>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">parameter-name</var></dt>
|
|
<dd>Names the parameter. The name is used to refer to the parameter
|
|
within the body of the function. The name cannot be the same as any other <var class="pv">parameter-name</var> in the parameter list.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">data-type1</var></dt>
|
|
<dd>Specifies the data type of the input parameter. The data
|
|
type can be a built-in data type or a distinct data 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>.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">distinct-type-name</var></dt>
|
|
<dd>Specifies a 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 function. If a CCSID is not specified, the
|
|
CCSID is determined by the default CCSID at the current server at the time
|
|
the function is invoked.</p>
|
|
</dd>
|
|
</dl>
|
|
</dd><a id="idx1828" name="idx1828"></a>
|
|
<dt class="bold">RETURNS</dt>
|
|
<dd>Specifies the output of the function.
|
|
<dl class="parml"><a id="idx1829" name="idx1829"></a><a id="idx1830" name="idx1830"></a>
|
|
<dt class="bold"><var class="pv">data-type2</var> </dt>
|
|
<dd>Specifies the data type and attributes of the output.
|
|
|
|
<p>You can specify any built-in data type (except LONG VARCHAR, or LONG VARGRAPHIC)
|
|
or a distinct type.</p>
|
|
<p>If a CCSID is specified and the CCSID of the return
|
|
data is encoded in a different CCSID, the data is converted to the specified
|
|
CCSID.</p>
|
|
<p>If a CCSID is not specified the return data is converted to the
|
|
CCSID of the job (or associated graphic CCSID of the job for graphic string
|
|
return values), if the CCSID of the return data is encoded in a different
|
|
CCSID. To avoid any potential loss of characters during the conversion, consider
|
|
explicitly specifying a CCSID that can represent any characters that will
|
|
be returned from the function. This is especially important if the data type
|
|
is graphic string data. In this case, consider using CCSID 1200 or 13488 (UTF-16
|
|
or UCS-2 graphic string data).</p>
|
|
</dd>
|
|
</dl>
|
|
</dd><a id="idx1831" name="idx1831"></a>
|
|
<dt class="bold">LANGUAGE SQL</dt>
|
|
<dd>Specifies that this is an SQL function.
|
|
</dd><a id="idx1832" name="idx1832"></a>
|
|
<dt class="bold">SPECIFIC <var class="pv">specific-name</var></dt>
|
|
<dd>Provides a unique name for the function. 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 function or procedure that exists at
|
|
the current server. If unqualified, the implicit qualifier is the same as
|
|
the qualifier of the function name. If qualified, the qualifier must be the
|
|
same as the qualifier of the function name.
|
|
<p>If specific name is not specified,
|
|
it is set to the function 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="idx1833" name="idx1833"></a><a id="idx1834" name="idx1834"></a>
|
|
<dd>Specifies whether the function is deterministic.
|
|
<dl class="parml">
|
|
<dt class="bold">NOT DETERMINISTIC</dt>
|
|
<dd>Specifies that the function will not always return the same result from
|
|
successive function invocations with identical input arguments. NOT DETERMINISTIC
|
|
should be specified if the function contains a reference to a special register,
|
|
a non-deterministic function, or a sequence.
|
|
</dd>
|
|
<dt class="bold">DETERMINISTIC</dt>
|
|
<dd>Specifies that the function will always return the same result from
|
|
successive invocations with identical input arguments.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">EXTERNAL ACTION <span class="base">or</span> NO EXTERNAL ACTION</dt><a id="idx1835" name="idx1835"></a><a id="idx1836" name="idx1836"></a>
|
|
<dd>Specifies whether the function contains an external action.
|
|
<dl class="parml">
|
|
<dt class="bold">EXTERNAL ACTION</dt>
|
|
<dd>The function performs some external action (outside the scope of the
|
|
function program). Thus, the function must be invoked with each successive
|
|
function invocation. EXTERNAL ACTION should be specified if the function contains
|
|
a reference to another function that has an external action.
|
|
</dd>
|
|
<dt class="bold">NO EXTERNAL ACTION</dt>
|
|
<dd>The function does not perform an external action. It need not be called
|
|
with each successive function invocation.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">CONTAINS SQL, READS SQL DATA, <span class="base">or</span> MODIFIES
|
|
SQL DATA</dt>
|
|
<dd>Specifies whether the function can execute any SQL statements and, if
|
|
so, what type. The database manager verifies that the SQL issued by the function is
|
|
consistent with this specification. 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="idx1837" name="idx1837"></a>
|
|
<dd>The function does not execute SQL statements that read or modify data.
|
|
</dd>
|
|
<dt class="bold">READS SQL DATA</dt><a id="idx1838" name="idx1838"></a>
|
|
<dd>The function does not execute SQL statements that modify data.
|
|
</dd>
|
|
<dt class="bold">MODIFIES SQL DATA</dt><a id="idx1839" name="idx1839"></a>
|
|
<dd>The function can execute any SQL statement except those statements that
|
|
are not supported in any function.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">STATIC DISPATCH</dt><a id="idx1840" name="idx1840"></a>
|
|
<dd>Specifies that the function is dispatched statically. All functions
|
|
are statically dispatched.
|
|
</dd>
|
|
<dt class="bold">RETURNS NULL ON NULL INPUT <span class="base">or</span> CALLED ON
|
|
NULL INPUT</dt><a id="idx1841" name="idx1841"></a><a id="idx1842" name="idx1842"></a>
|
|
<dd>Specifies whether the function is called if any of the input arguments
|
|
is null at execution time.
|
|
<dl class="parml">
|
|
<dt class="bold">RETURNS NULL ON INPUT</dt>
|
|
<dd>Specifies that the function is not invoked if any of the input arguments
|
|
is null. The result is the null value.
|
|
</dd>
|
|
<dt class="bold">CALLED ON NULL INPUT</dt>
|
|
<dd>Specifies that the function is to be invoked, if any, or all, argument
|
|
values are null, making the function responsible for testing for null argument
|
|
values. The function can return a null or nonnull value.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">FENCED <span class="base">or</span> NOT FENCED</dt><a id="idx1843" name="idx1843"></a><a id="idx1844" name="idx1844"></a>
|
|
<dd>Specifies whether the SQL function runs in an environment that is isolated
|
|
from the database manager environment.
|
|
<dl class="parml">
|
|
<dt class="bold">FENCED</dt>
|
|
<dd>The function will run in a separate thread.
|
|
<p>FENCED functions cannot
|
|
keep SQL cursors open across individual calls to the function. However, the
|
|
cursors in one thread are independent of the cursors in any other threads
|
|
which reduces the possibility of cursor name conflicts.</p>
|
|
</dd>
|
|
<dt class="bold">NOT FENCED</dt>
|
|
<dd>The function may run in the same thread as the invoking SQL statement.
|
|
<p>NOT FENCED functions can keep SQL cursors open across individual calls to
|
|
the function. Since cursors can be kept open, the cursor position will also
|
|
be preserved between calls to the function. However, cursor names may conflict
|
|
since the UDF is now running in the same thread as the invoking SQL statement
|
|
and other NOT FENCED UDFs.</p>
|
|
<p>NOT FENCED functions usually perform better
|
|
than FENCED functions.</p>
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">PARALLEL</dt><a id="idx1845" name="idx1845"></a><a id="idx1846" name="idx1846"></a>
|
|
<dd>Specifies whether the function can be run in parallel.
|
|
<dl class="parml">
|
|
<dt class="bold">ALLOW PARALLEL</dt>
|
|
<dd>Specifies that the function can be run in parallel.
|
|
</dd>
|
|
<dt class="bold">DISALLOW PARALLEL</dt>
|
|
<dd>Specifies that the function cannot be run in parallel.
|
|
</dd>
|
|
</dl>The default is DISALLOW PARALLEL, if you specify one or more of the
|
|
following clauses:
|
|
<ul>
|
|
<li>NOT DETERMINISTIC</li>
|
|
<li>EXTERNAL ACTION</li>
|
|
<li>MODIFIES SQL DATA</li></ul>Otherwise, ALLOW PARALLEL is the default.
|
|
</dd>
|
|
<dt class="bold"><span class="italic">SET OPTION-statement</span></dt>
|
|
<dd>Specifies the options that will be used to create the function. For
|
|
example, to create a debuggable function, the following statement could be
|
|
included:
|
|
<pre class="xmp"><span class="bold">SET OPTION DBGVIEW = *SOURCE</span> </pre>For
|
|
more information, see <a href="rbafzmstsoption.htm#soption">SET OPTION</a>.
|
|
<p>The options CLOSQLCSR,
|
|
CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE FUNCTION
|
|
statement.</p>
|
|
</dd>
|
|
<dt class="bold"><span class="italic">SQL-routine-body</span></dt>
|
|
<dd>Specifies a single SQL statement, including a compound statement. See <a href="rbafzmstsqlcontstmts.htm#sqlcontstmts">SQL control statements</a> for more information about defining SQL functions.
|
|
<p>A call to a procedure that issues a CONNECT, SET CONNECTION, RELEASE, DISCONNECT,
|
|
COMMIT, ROLLBACK and SET TRANSACTION statement is not allowed in a function.</p>
|
|
<p>If the <var class="pv">SQL-routine-body</var> is a compound statement, it must contain
|
|
at least one RETURN statement and a RETURN statement must be executed when
|
|
the function is called.</p>
|
|
</dd>
|
|
</dl>
|
|
<a name="wq1236"></a>
|
|
<h3 id="wq1236"><a href="rbafzmst02.htm#ToC_879">Notes</a></h3>
|
|
<p><span class="bold">General considerations for defining user-defined functions:</span> See <a href="rbafzmstcreatef.htm#createf">CREATE FUNCTION</a> for general information on defining user-defined
|
|
functions.</p>
|
|
<p><span class="bold">Function ownership:</span> If SQL names were
|
|
specified:</p>
|
|
<ul>
|
|
<li>If a user profile with the same name as the schema into which the function
|
|
is created exists, the <span class="italic">owner</span> of the function is that
|
|
user profile.</li>
|
|
<li>Otherwise, the <span class="italic">owner</span> of the function is the user
|
|
profile or group user profile of the job executing the statement.</li></ul>
|
|
<p>If system names were specified, the <span class="italic">owner</span> of the
|
|
function is the user profile or group user profile of the job executing the
|
|
statement.</p>
|
|
<p><span class="bold">Function authority:</span> If SQL names are used, functions
|
|
are created with the system authority of *EXCLUDE on *PUBLIC. If system names
|
|
are used, functions are created with the authority to *PUBLIC as determined
|
|
by the create authority (CRTAUT) parameter of the schema.</p>
|
|
<p>If the owner of the function is a member of a group profile (GRPPRF keyword)
|
|
and group authority is specified (GRPAUT keyword), that group profile will
|
|
also have authority to the function.</p>
|
|
<p><span class="bold">Creating the function:</span> When an SQL function is created, the database manager creates
|
|
a temporary source file that will contain C source code with embedded SQL
|
|
statements. A *SRVPGM object is then created using the CRTSRVPGM command.
|
|
The SQL options used to create the service program are the options that are
|
|
in effect at the time the CREATE FUNCTION statement is executed. The service
|
|
program is created with ACTGRP(*CALLER).</p>
|
|
<p>The specific name is used to determine the name of the source file member
|
|
and *SRVPGM object. If the specific name is a valid system name, it will used
|
|
as the name of member and program. If the member already exists, it will be
|
|
overlaid. If a program already exists in the specified library, a unique name
|
|
is generated using the rules for generating system table names. If the specific
|
|
name is not a valid system name, a unique name is generated using the rules
|
|
for generating system table names.</p>
|
|
<p>The function's attributes are saved in the associated service program object.
|
|
If the *SRVPGM object is saved and then restored to this or another system,
|
|
the catalogs are automatically updated with those attributes.</p>
|
|
<p>During restore of the function:</p>
|
|
<ul>
|
|
<li>If the specific name was specified when the function 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 function signature already exists in the catalog:
|
|
<ul>
|
|
<li>If the name of the service program that was created is the same as the
|
|
one registered in the catalog, the function information in the catalog will
|
|
be replaced.</li>
|
|
<li>Otherwise, the function cannot be registered, and an error is issued.</li></ul></li></ul>
|
|
<p><span class="bold">Identifier resolution:</span> If the tables specified in
|
|
a routine body exist, all references in the routine body of an SQL routine
|
|
are resolved to identify a particular column, SQL parameter, or SQL variable
|
|
at the time the SQL routine is created. If the tables do not exist, all names
|
|
that exist as SQL variables or parameters are resolved to identify the variable
|
|
or parameter when the function is created. The remaining names are assumed
|
|
to be columns bound to the tables when the function is invoked.</p>
|
|
<p>If duplicate names are used for columns and SQL variables and parameters,
|
|
qualify the duplicate names by using the table designator for columns, the
|
|
function name for parameters, and the label name for SQL variables.</p>
|
|
<p><span class="bold">Invoking the function:</span> When an SQL function is invoked,
|
|
it runs in the activation group of the calling program.</p>
|
|
<p>If a function is specified in the select-list of a select-statement and
|
|
if the function specifies EXTERNAL ACTION or MODIFIES SQL DATA, the function
|
|
will only be invoked for each row returned. Otherwise, the UDF may be invoked
|
|
for rows that are not selected.</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 IS DETERMINISTIC may be used as a synonym for DETERMINISTIC.</li></ul>
|
|
<a name="wq1237"></a>
|
|
<h3 id="wq1237"><a href="rbafzmst02.htm#ToC_880">Example</a></h3>
|
|
<p>Define a scalar function that returns the tangent of a value using the
|
|
existing SIN and COS built-in functions. </p>
|
|
<pre class="xmp"><span> <span class="bold">CREATE FUNCTION</span> TAN
|
|
(X <span class="bold">DOUBLE</span>)
|
|
<span class="bold">RETURNS DOUBLE
|
|
LANGUAGE SQL
|
|
CONTAINS SQL
|
|
NO EXTERNAL ACTION
|
|
DETERMINISTIC
|
|
RETURN SIN</span>(X)/<span class="bold">COS</span>(X)</span></pre><p class="indatacontent"> Notice
|
|
that a parameter name (X) is specified for the input parameter to function
|
|
TAN. The parameter name is used within the body of the function to refer to
|
|
the input parameter. The invocations of the SIN and COS functions, within
|
|
the body of the TAN user-defined function, pass the parameter X as input.</p>
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcfso.htm">Previous Page</a> | <a href="rbafzmstcftb.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>
|