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/rbafzmstcfso.htm

518 lines
35 KiB
HTML
Raw Permalink Normal View History

Add readme and dist folders
2024-04-02 14:02:31 +00:00
<?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>--+------------------------------+-+->&lt;
| '-<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>
Reference in New Issue Copy Permalink