518 lines
35 KiB
HTML
518 lines
35 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 (Sourced) statement,
|
|
CREATE FUNCTION (Sourced), SQL statement, creating, function, sourced,
|
|
CCSID clause, data type for CREATE FUNCTION (Sourced), 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 (Sourced), function-name, data-type, AS LOCATOR clause,
|
|
SPECIFIC clause, specific-name" />
|
|
<title>CREATE FUNCTION (Sourced)</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="cfso"></a>
|
|
<h2 id="cfso"><a href="rbafzmst02.htm#ToC_867">CREATE FUNCTION (Sourced)</a></h2><a id="idx1762" name="idx1762"></a><a id="idx1763" name="idx1763"></a><a id="idx1764" name="idx1764"></a><a id="idx1765" name="idx1765"></a><a id="idx1766" name="idx1766"></a>
|
|
<p>This CREATE FUNCTION (Sourced) statement is used to create a user-defined
|
|
function, based on another existing scalar or aggregate function, at the current
|
|
server.</p>
|
|
<a name="wq1220"></a>
|
|
<h3 id="wq1220"><a href="rbafzmst02.htm#ToC_868">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="wq1221"></a>
|
|
<h3 id="wq1221"><a href="rbafzmst02.htm#ToC_869">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>If the source function is a user-defined function, the authorization ID
|
|
of the statement must include at least one of the following for the source
|
|
function: </p>
|
|
<ul>
|
|
<li>The EXECUTE privilege on the function</li>
|
|
<li>Administrative authority</li></ul>
|
|
<p>To create a sourced function, 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>
|
|
<li>*USE to the Create Program (CRTPGM) command</li></ul></li>
|
|
<li>Administrative authority</li></ul>
|
|
<p>If SQL names are specified and a user profile exists that has the same
|
|
name as the library into which the function is created, and that name is different
|
|
from the authorization ID of the statement, then the privileges held by the
|
|
authorization ID of the statement must include at least one of the following:
|
|
</p>
|
|
<ul>
|
|
<li>The system authority *ADD to the user profile with that name</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>, <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="wq1222"></a>
|
|
<h3 id="wq1222"><a href="rbafzmst02.htm#ToC_870">Syntax</a></h3>
|
|
<a href="rbafzmstcfso.htm#synscrtfuns"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq1223"></a>
|
|
<div class="fignone" id="wq1223">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn279.htm"
|
|
border="0" /></span><a href="#skipsyn-278"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>>>-CREATE--FUNCTION--<span class="italic">function-name</span>------------------------------>
|
|
|
|
>--(--+---------------------------+--)-------------------------->
|
|
| .-,---------------------. |
|
|
| V | |
|
|
'---<span class="italic">parameter-declaration</span>-+-'
|
|
|
|
(1)
|
|
>--RETURNS--<span class="italic">data-type2</span>--+------------+-------------------------->
|
|
'-AS LOCATOR-'
|
|
|
|
>--+-------------------------+---------------------------------->
|
|
'-SPECIFIC--<span class="italic">specific-name</span>-'
|
|
|
|
>--SOURCE--+-<span><span class="italic">function-name</span></span>--+------------------------------+-+-><
|
|
| '-<span>(</span>--+--------------------+--<span>)</span>-' |
|
|
| | .-,--------------. | |
|
|
| | V | | |
|
|
| '---<span><span class="italic">parameter-type</span></span>-+-' |
|
|
'-<span>SPECIFIC</span>--<span><span class="italic">specific-name</span></span>-------------------------'
|
|
|
|
parameter-declaration:
|
|
|
|
|--+----------------+--<span class="italic">data-type1</span>--+------------+---------------|
|
|
'-<span class="italic">parameter-name</span>-' '-AS LOCATOR-'
|
|
|
|
data-type1, data-type2, data-type3:
|
|
|
|
|--+-<span class="italic">built-in-type</span>------+---------------------------------------|
|
|
'-<span class="italic">distinct-type-name</span>-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-278" id="skipsyn-278"></a>
|
|
<a name="wq1224"></a>
|
|
<div class="notelisttitle" id="wq1224">Notes:</div>
|
|
<ol type="1">
|
|
<li>The RETURNS, SPECIFIC, and SOURCE clauses can be specified in any
|
|
order.</li>
|
|
</ol></div>
|
|
<a name="wq1226"></a>
|
|
<div class="fignone" id="wq1226">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn280.htm"
|
|
border="0" /></span><a href="#skipsyn-279"><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-279" id="skipsyn-279"></a></div>
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn281.htm"
|
|
border="0" /></span><a href="#skipsyn-280"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>parameter-type:
|
|
|
|
|--<span class="italic">data-type3</span>--+------------+-----------------------------------|
|
|
'-AS LOCATOR-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-280" id="skipsyn-280"></a><a id="idx1767" name="idx1767"></a><a id="idx1768" name="idx1768"></a><a id="idx1769" name="idx1769"></a><a id="idx1770" name="idx1770"></a><a id="idx1771" name="idx1771"></a><a id="idx1772" name="idx1772"></a><a id="idx1773" name="idx1773"></a><a id="idx1774" name="idx1774"></a><a id="idx1775" name="idx1775"></a><a id="idx1776" name="idx1776"></a><a id="idx1777" name="idx1777"></a><a id="idx1778" name="idx1778"></a><a id="idx1779" name="idx1779"></a><a id="idx1780" name="idx1780"></a><a id="idx1781" name="idx1781"></a><a id="idx1782" name="idx1782"></a><a id="idx1783" name="idx1783"></a><a id="idx1784" name="idx1784"></a><a id="idx1785" name="idx1785"></a><a id="idx1786" name="idx1786"></a><a id="idx1787" name="idx1787"></a><a id="idx1788" name="idx1788"></a><a id="idx1789" name="idx1789"></a><a id="idx1790" name="idx1790"></a>
|
|
<a name="synscrtfuns"></a>
|
|
<h3 id="synscrtfuns"><a href="rbafzmst02.htm#ToC_871">Description</a></h3><a id="idx1791" name="idx1791"></a><a id="idx1792" name="idx1792"></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 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>If the function is sourced on an existing function to enable the
|
|
use of the existing function with a distinct type, the name can be the same
|
|
name as the existing function. 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 input parameters of the function and the data
|
|
type of each parameter. Each <var class="pv">parameter-declaration</var> is an input parameter
|
|
for the function. A maximum of 90 parameters can be specified.
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">parameter-name</var></dt>
|
|
<dd>Names the parameter. Although not required, a parameter name
|
|
can be specified for each parameter. 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 parameter. The data type can be a built-in
|
|
data type or a distinct data type.
|
|
<p>Any valid SQL data type may
|
|
be used provided it is castable to the type of the corresponding parameter
|
|
of the function identifed in the SOURCE clause (for information see <a href="rbafzmstcastdt.htm#castdt">Casting between data types</a>). However, this checking does not guarantee that an error
|
|
will not occur when the function is invoked. For more information, see <a href="rbafzmstcfso.htm#considerations">Considerations for invoking a sourced user-defined function</a>.</p>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">built-in-type</var></dt>
|
|
<dd>The data type of the input parameter is a built-in data type. See <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a> for a more complete description of each built-in data type.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">distinct-type-name</var></dt>
|
|
<dd>The data type of the input parameter is 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). See <a href="rbafzmstcrtudt.htm#crtudt">CREATE DISTINCT TYPE</a> for more information.
|
|
<p>If the name of the distinct type
|
|
is specified without a schema name, the database manager resolves the schema
|
|
name by searching the schemas in the SQL path.</p>
|
|
</dd>
|
|
</dl>
|
|
<p>DataLinks are not allowed for functions sourced on external functions.</p>
|
|
<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><a id="idx1793" name="idx1793"></a>
|
|
<dt class="bold">AS LOCATOR</dt>
|
|
<dd>Specifies that the input parameter is a locator to the value rather
|
|
than the actual value. You can specify AS LOCATOR only if the input 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">RETURNS</dt>
|
|
<dd>Specifies the output of the function.
|
|
<dl class="parml"><a id="idx1794" name="idx1794"></a><a id="idx1795" name="idx1795"></a>
|
|
<dt class="bold"><var class="pv">data-type2</var> </dt>
|
|
<dd>Specifies the data type and attributes of the output. The
|
|
data type can be a built-in data type (except LONG VARCHAR, LONG VARGRAPHIC,
|
|
or a DataLink) or distinct type (that is not based on a DataLink).
|
|
|
|
<p>Any valid SQL data type can be used provided it is castable from
|
|
the result type of the source function. (For information on casting data types,
|
|
see <a href="rbafzmstcastdt.htm#castdt">Casting between data types</a>) However, this checking does not guarantee that
|
|
an error will not occur when this new function is invoked. For more information,
|
|
see <a href="rbafzmstcfso.htm#considerations">Considerations for invoking a sourced user-defined function</a>.</p>
|
|
</dd>
|
|
<dt class="bold">AS LOCATOR</dt>
|
|
<dd>Specifies that the function returns a locator to the value rather than
|
|
the actual value. You can specify AS LOCATOR only if the output from the function
|
|
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. The AS
|
|
LOCATOR clause is not allowed for functions sourced on SQL functions.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<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">SOURCE</dt>
|
|
<dd>Specifies that the new function is being defined as a sourced function.
|
|
A <span class="italic">sourced function</span> is implemented by another function
|
|
(the <span class="italic">source function</span>). The function must exist at
|
|
the current server and it must be a function that was defined with the CREATE
|
|
FUNCTION statement or a cast function that was generated by a CREATE DISTINCT
|
|
TYPE statement. The particular function can be identified by its name, function
|
|
signature, or specific name.
|
|
<p>The source function can be any aggregate
|
|
function or any built-in scalar function except COALESCE, DATAPARTITIONNAME,
|
|
DATAPARTITIONNUM, DBPARTITIONNAME, DBPARTITIONNUM, EXTRACT, HASH, HASHED_VALUE,
|
|
LAND, LOR, MAX, MIN, NODENAME, NODENUMBER, PARTITION, POSITION, RAISE_ERROR,
|
|
RRN, STRIP, SUBSTRING, TRIM, VALUE, and XOR, or any previously created user-defined
|
|
function. It can be a system-generated user-defined function (generated when
|
|
a distinct type was created).</p>
|
|
<p>The source function can be one of the following
|
|
built-in scalar functions only if one argument is specified: BINARY, BLOB,
|
|
CHAR, CLOB, DBCLOB, DECIMAL, DECRYPT_BIN, DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR,
|
|
DECRYPT_DB, GRAPHIC, TRANSLATE, VARBINARY, VARCHAR, VARGRAPHIC, and ZONED.</p>
|
|
<p>If you base the sourced function directly or indirectly on a scalar
|
|
function, the sourced function inherits the attributes of the scalar function.
|
|
This can involve several layers of sourced functions. For example, assume
|
|
that function A is sourced on function B, which in turn is sourced on function
|
|
C. Function C is a scalar function. Functions A and B inherit all of the attributes
|
|
that are specified on CREATE FUNCTION statement for function C.</p>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">function-name</var> </dt>
|
|
<dd>Identifies the function to be used as the source function by its function
|
|
name. The function may have any number of parameters defined for it. If there
|
|
is more than one function of the specified name in the specified or implicit
|
|
schema, an error is returned
|
|
</dd>
|
|
<dt class="bold"><var class="pv">function-name (parameter-type, ...)</var></dt>
|
|
<dd>Identifies the function to be used as the source function by its function
|
|
signature, which uniquely identifies the function. The <var class="pv">function-name (parameter-type,...)</var> must identify a function with the specified signature at the current
|
|
server. The specified parameters must match the data types in the corresponding
|
|
position that were specified when the function was created. The number of
|
|
data types, and the logical concatenation of the data types is used to identify
|
|
the specific function instance. Synonyms for data types are considered a match.
|
|
<p>If <var class="pv">function-name</var>() is specified, the function identified must have
|
|
zero parameters.</p>
|
|
<p>To use a built-in function as the source function,
|
|
this syntax variation must be used.</p>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">function-name</var></dt>
|
|
<dd>Identifies the name of the source function. If an unqualified name is
|
|
specified, the schemas of the SQL path are searched. Otherwise, the specified
|
|
schema is searched for the function.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">parameter-type</var>,...</dt>
|
|
<dd>Identifies the parameters of the function.
|
|
<p>If an unqualified distinct
|
|
type name is specified, the database manager searches the SQL path to resolve the schema
|
|
name for the distinct type.</p>
|
|
<p>For data types that have a length, precision
|
|
or scale attribute, you can specify a value or use a set of empty parentheses.</p>
|
|
<ul>
|
|
<li>Empty parenthesis indicates that the database manager ignores the attribute when determining
|
|
whether the data types match. For example, DEC() will be considered a match
|
|
for a parameter of a function defined with a data type of DEC(7,2).</li>
|
|
<li>If a specific value for a length, precision, or scale attribute is specified,
|
|
the value must exactly match the value that was specified (implicitly or explicitly)
|
|
in the CREATE FUNCTION statement. If the data type is FLOAT, the precision
|
|
does not have to exactly match the value that was specified because matching
|
|
is based on the data type (REAL or DOUBLE).</li>
|
|
<li>If length, precision, or scale is not explicitly specified, and empty
|
|
parentheses are not specified, the default attributes of the data type are
|
|
implied. The implicit length must exactly match the value that was specified
|
|
(implicitly or explicitly) in the CREATE FUNCTION statement.</li></ul>
|
|
<p> For data types with a subtype or CCSID attribute, specifying
|
|
the FOR DATA clause or CCSID clause is optional. Omission of either clause
|
|
indicates that the database manager ignores the attribute when determining whether the
|
|
data types match. If you specify either clause, it must match the value that
|
|
was implicitly or explicitly specified in the CREATE FUNCTION statement.</p>
|
|
</dd>
|
|
<dt class="bold">AS LOCATOR</dt>
|
|
<dd>Specifies that the function is defined to receive a locator for this
|
|
parameter. If AS LOCATOR is specified the data type must be a LOB or a distinct
|
|
type based on a LOB. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED
|
|
DATA must not be specified. If AS LOCATOR is specified and a length is explicitly
|
|
specified, the data type length is ignored.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold">SPECIFIC <var class="pv">specific-name</var> </dt><a id="idx1796" name="idx1796"></a><a id="idx1797" name="idx1797"></a>
|
|
<dd>Identifies the function to be used as the source function by its specific
|
|
name. The <var class="pv">specific-name</var> must identify a specific function that exists
|
|
in the specified or implicit schema. If an unqualified <var class="pv">specific-name</var> is
|
|
specified, the default schema is used as the qualifier.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
</dl>
|
|
<p>The number of input parameters in the function that is being created must
|
|
be the same as the number of parameters in the source function. If the data
|
|
type of each input parameter is not the same as or castable to the corresponding
|
|
parameter of the source function, an error occurs. The data type of the final
|
|
result of the source function must match or be castable to the result of the
|
|
sourced function.</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>
|
|
<a name="wq1227"></a>
|
|
<h3 id="wq1227"><a href="rbafzmst02.htm#ToC_872">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><a name="considerations"></a><span id="considerations" class="bold">Considerations for invoking a sourced user-defined function:</span> When a sourced function is invoked, each argument to the function is
|
|
assigned to the associated parameter defined for the function. The values
|
|
are then cast (if necessary) to the data type of the corresponding parameters
|
|
of the underlying function. An error can occur either in the assignment or
|
|
in the cast. For example: an argument passed on input to a function that matches
|
|
the data type and length or precision attributes of the parameter for the
|
|
function might not be castable if the corresponding parameter of the underlying
|
|
source function has a shorter length or less precision. It is recommended
|
|
that the data types of the parameters of a sourced function be defined with
|
|
attributes that are less than or equal to the attributes of the corresponding
|
|
parameters of the underlying function.</p>
|
|
<p>The result of the underlying function is assigned to the RETURNS data type
|
|
of the sourced function. The RETURNS data type of the underlying function
|
|
might not be castable to the RETURNS data type of the source function. This
|
|
can occur when the RETURNS data type of this new source function has a shorter
|
|
length or less precision than the RETURNS data type of the underlying function.
|
|
For example, an error would occur when function A is invoked assuming the
|
|
following functions exist. Function A returns an INTEGER. Function B is a
|
|
sourced function, is defined to return a SMALLINT, and the definition references
|
|
function A in the SOURCE clause. It is recommended that the RETURNS data type
|
|
of a sourced function be defined with attributes that are the same or greater
|
|
than the attributes defined for the RETURNS data type of the underlying function.</p>
|
|
<p><span class="bold">Considerations when the function is based on a user-defined
|
|
function:</span> If the sourced function is based directly or indirectly on
|
|
an external scalar function, the sourced function inherits the attributes
|
|
of the EXTERNAL clause of the external scalar function. This can involve several
|
|
layers of sourced functions. For example, assume that function A is sourced
|
|
on function B, which in turn is sourced on function C. Function C is an external
|
|
scalar function. Functions A and B inherit all of the attributes that are
|
|
specified on the EXTERNAL clause of the CREATE FUNCTION statement for function
|
|
C.</p>
|
|
<p><span class="bold">Creating the function:</span> When a sourced function is
|
|
created, a small service program object is created that represents the function.
|
|
When this service program is saved and restored to another system, the attributes
|
|
from the CREATE FUNCTION statement are automatically added to the catalog
|
|
on that system.</p>
|
|
<a name="wq1228"></a>
|
|
<h3 id="wq1228"><a href="rbafzmst02.htm#ToC_873">Examples</a></h3>
|
|
<p><span class="italic">Example 1: </span> Assume that distinct type HATSIZE is
|
|
defined and is based on the built-in data type INTEGER. An AVG function could
|
|
be defined to compute the average hat size of different departments. Create
|
|
a sourced function that is based on built-in function AVG. </p>
|
|
<a name="rgr6cfa"></a>
|
|
<pre id="rgr6cfa" class="xmp"> <span class="bold">CREATE FUNCTION</span> AVG (HATSIZE)
|
|
<span class="bold"> RETURNS</span> HATSIZE
|
|
<span class="bold"> SOURCE</span> AVG (<span class="bold">INTEGER</span>)</pre>
|
|
<p>The syntax of the SOURCE clause includes an explicit parameter list because
|
|
the source function is a built-in function.</p>
|
|
<p>When distinct type HATSIZE was created, two cast functions were generated,
|
|
which allow HATSIZE to be cast to INTEGER for the argument and INTEGER to
|
|
be cast to HATSIZE for the result of the function.</p>
|
|
<p><span class="italic">Example 2:</span> After Smith created the external scalar
|
|
function CENTER in his schema, there is a need to use this function, function,
|
|
but the invocation of the function needs to accept two INTEGER arguments instead
|
|
of one INTEGER argument and one DOUBLE argument. Create a sourced function
|
|
that is based on CENTER. </p>
|
|
<a name="rgr6cfb"></a>
|
|
<pre id="rgr6cfb" class="xmp"> <span class="bold">CREATE FUNCTION</span> MYCENTER (<span class="bold">INTEGER, INTEGER</span>)
|
|
<span class="bold">RETURNS DOUBLE</span>
|
|
<span class="bold">SOURCE</span> SMITH.CENTER (<span class="bold">INTEGER, DOUBLE);</span></pre>
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcftbe.htm">Previous Page</a> | <a href="rbafzmstcfsc.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>
|