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

1040 lines
58 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 (External Scalar) statement,
CREATE FUNCTION (External Scalar), SQL statements, creating, function, external,
data type for CREATE FUNCTION (External Scalar), CCSID clause, CHAR,
DOUBLE PRECISION, DECIMAL, FLOAT, BIGINT, INTEGER, NUMERIC, REAL, SMALLINT,
VARCHAR, VARGRAPHIC, GRAPHIC, FOR BIT DATA clause, FOR MIXED DATA clause,
FOR SBCS DATA clause, BINARY, VARBINARY, BLOB, CLOB, DBCLOB, ROWID,
distinct-type, in CREATE FUNCTION (External Scalar), function-name,
AS LOCATOR clause, RETURNS clause, data-type, LANGUAGE clause, SQL clause,
DB2GENERAL clause, GENERAL clause, GENERAL WITH NULLS clause, JAVA clause,
SPECIFIC clause, DETERMINISTIC clause, CONTAINS SQL clause, NO SQL clause,
READS SQL DATA clause, MODIFIES SQL DATA clause,
RETURNS NULL ON NULL INPUT clause, CALLED ON NULL INPUT clause,
STATIC DISPATCH clause, DBINFO clause, NO DBINFO clause, EXTERNAL ACTION clause,
NO EXTERNAL ACTION clause, FENCED clause, NOT FENCED clause, FINAL CALL clause,
NO FINAL CALL clause, ALLOW PARALLEL clause, DISALLOW PARALLEL clause,
SCRATCHPAD clause, NO SCRATCHPAD clause, EXTERNAL clause, EXTERNAL NAME clause,
CREATE FUNCTION (external scalar) statement, CREATE FUNCTION (external scalar),
SQL statement" />
<title>CREATE FUNCTION (External 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="cfsce"></a>
<h2 id="cfsce"><a href="rbafzmst02.htm#ToC_853">CREATE FUNCTION (External Scalar)</a></h2><a id="idx1635" name="idx1635"></a><a id="idx1636" name="idx1636"></a><a id="idx1637" name="idx1637"></a><a id="idx1638" name="idx1638"></a><a id="idx1639" name="idx1639"></a><a id="idx1640" name="idx1640"></a>
<p>This CREATE FUNCTION (External Scalar) statement creates an external scalar
function at the current server. A user-defined external scalar function returns
a single value each time it is invoked.</p>
<a name="wq1190"></a>
<h3 id="wq1190"><a href="rbafzmst02.htm#ToC_854">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="wq1191"></a>
<h3 id="wq1191"><a href="rbafzmst02.htm#ToC_855">Authorization</a></h3>
<p>The privileges held by the authorization id of the statement must include
at least one of the following: </p>
<ul>
<li>For the SYSFUNCS catalog view and SYSPARMS catalog table<sup class="fn"><a id="wq1192" name="wq1192" href="rbafzmstcfsce.htm#wq1193">65</a></sup>:
<ul>
<li>The INSERT privilege on the table, and</li>
<li>The system authority *EXECUTE on library QSYS2</li></ul></li>
<li>Administrative Authority</li></ul>
<p>If the external program or service program exists, the privileges held
by the authorization ID of the statement must include at least one of the
following: </p>
<ul>
<li>For the external program or service program that is referenced in the
SQL statement:
<ul>
<li>The system authority *EXECUTE on the library that contains the external
program or service program.</li>
<li>The system authority *EXECUTE on the external program or service program,
and</li>
<li>The system authority *CHANGE on the program or service program. The system
needs this authority to update the program object to contain the information
necessary to save/restore the function to another system. If user does not
have this authority, the function is still created, but the program object
is not updated.</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> and <a href="rbafzmstgntudtp.htm#eqtabled">Corresponding System Authorities When Checking Privileges to a Distinct Type</a>.</p>
<a name="wq1194"></a>
<h3 id="wq1194"><a href="rbafzmst02.htm#ToC_856">Syntax</a></h3>
<a href="rbafzmstcfsce.htm#synscrtfunes"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<a name="wq1195"></a>
<div class="fignone" id="wq1195">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn271.htm"
border="0" /></span><a href="#skipsyn-270"><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>-+-'
>--RETURNS--+-<span class="italic">data-type2</span>--+------------+------------------------+-->
| '-AS LOCATOR-' |
'-<span class="italic">data-type3</span>--CAST FROM--<span class="italic">data-type4</span>--+------------+-'
'-AS LOCATOR-'
>--<span class="italic">option-list</span>------------------------------------------------->&lt;
</pre>
<a name="skipsyn-270" id="skipsyn-270"></a></div>
<a name="wq1196"></a>
<div class="fignone" id="wq1196">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn272.htm"
border="0" /></span><a href="#skipsyn-271"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>parameter-declaration:
|--+----------------+--<span class="italic">data-type1</span>--+------------+---------------|
'-<span class="italic">parameter-name</span>-' '-AS LOCATOR-'
data-type1, data-type2, data-type3, data-type4:
|--+-<span class="italic">built-in-type</span>------+---------------------------------------|
'-<span class="italic">distinct-type-name</span>-'
</pre>
<a name="skipsyn-271" id="skipsyn-271"></a></div>
<a name="wq1197"></a>
<div class="fignone" id="wq1197">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn273.htm"
border="0" /></span><a href="#skipsyn-272"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>built-in-type:
|--+-+---SMALLINT---+--------------------------------------------------------------------------+--|
| +-+-INTEGER-+--+ |
| | '-INT-----' | |
| '---BIGINT-----' |
| .-(5,0)------------------------. |
+-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0--------. | |
| '-NUMERIC-----' '-(--<span class="italic">integer</span>--+-----------+--)-' |
| '-<span class="italic">, integer</span>-' |
| .-(--53--)------. |
+-+-FLOAT--+---------------+-+--------------------------------------------------------------+
| | '-(--<span class="italic">integer</span>--)-' | |
| +-REAL---------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+----' |
| .-(--1--)-------. |
+-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+
| | | '-CHAR------' '-(--<span class="italic">integer</span>--)-' | +-FOR BIT DATA---+ | |
| | '-+-+-CHARACTER-+--VARYING-+--(--<span class="italic">integer</span>--)-' +-FOR SBCS DATA--+ | |
| | | '-CHAR------' | +-FOR MIXED DATA-+ | |
| | '-VARCHAR----------------' '-<span class="italic">ccsid-clause</span>---' | |
| | .-(--1M--)-------------. | |
| '-----+-+-<span>CHARACTER</span>-+--<span>LARGE OBJECT</span>-+------+----------------------+--+----------------+-' |
| | '-<span>CHAR</span>------' | '-(--<span class="italic">integer</span>--+---+--)-' +-FOR SBCS DATA--+ |
| '-<span>CLOB</span>------------------------' +-K-+ +-FOR MIXED DATA-+ |
| +-M-+ '-<span class="italic">ccsid-clause</span>---' |
| '-G-' |
| .-(--1--)-------. |
+-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+
| | '-(--<span class="italic">integer</span>--)-' | '-<span class="italic">ccsid-clause</span>-' |
| +-+-GRAPHIC VARYING-+--(--<span class="italic">integer</span>--)---+ |
| | '-VARGRAPHIC------' | |
| | .-(--1M--)-------------. | |
| '---DBCLOB----+----------------------+-' |
| '-(--<span class="italic">integer</span>--+---+--)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
| .-(--1--)-------. |
+-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+
| | | '-(--<span class="italic">integer</span>--)-' | | |
| | '-+-BINARY VARYING-+--(--<span class="italic">integer</span>--)-' | |
| | '-VARBINARY------' | |
| | .-(--1M--)-------------. | |
| '---+-BLOB----------------+----+----------------------+-' |
| '-BINARY LARGE OBJECT-' '-(--<span class="italic">integer</span>--+---+--)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------+----------------------------------------------------------------+
| | .-(--0--)-. | |
| +-TIME--+---------+------+ |
| | .-(--6--)-. | |
| '-TIMESTAMP--+---------+-' |
'---ROWID-----------------------------------------------------------------------------------'
ccsid-clause:
.-NOT NORMALIZED-.
|--CCSID--<span class="italic">integer</span>--+----------------+---------------------------|
'-NORMALIZED-----'
</pre>
<a name="skipsyn-272" id="skipsyn-272"></a></div>
<a name="wq1198"></a>
<div class="fignone" id="wq1198">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn274.htm"
border="0" /></span><a href="#skipsyn-273"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>option-list:
(1)
|--+-----------------------+------------------------------------>
'-LANGUAGE--+-C-------+-'
+-C++-----+
+-CL------+
+-COBOL---+
+-COBOLLE-+
+-FORTRAN-+
+-JAVA----+
+-PLI-----+
+-RPG-----+
'-RPGLE---'
.-PARAMETER STYLE SQL--------------------.
>--+----------------------------------------+------------------->
'-+-PARAMETER STYLE DB2SQL-------------+-'
+-PARAMETER STYLE JAVA---------------+
+-PARAMETER STYLE GENERAL------------+
+-PARAMETER STYLE GENERAL WITH NULLS-+
'-PARAMETER STYLE DB2GENERAL---------'
.-NOT DETERMINISTIC-.
>--+-------------------------+--+-------------------+----------->
'-SPECIFIC--<span class="italic">specific-name</span>-' '-DETERMINISTIC-----'
.-READS SQL DATA----. .-CALLED ON NULL INPUT-------.
>--+-------------------+--+----------------------------+-------->
+-NO SQL------------+ '-RETURNS NULL ON NULL INPUT-'
+-CONTAINS SQL------+
'-MODIFIES SQL DATA-'
.-STATIC DISPATCH-. .-NO DBINFO-. .-EXTERNAL ACTION----.
>--+-----------------+--+-----------+--+--------------------+--->
'-DBINFO----' '-NO EXTERNAL ACTION-'
.-FENCED-----. .-NO FINAL CALL-.
>--+------------+--+---------------+--+-------------------+----->
'-NOT FENCED-' '-FINAL CALL----' +-ALLOW PARALLEL----+
'-DISALLOW PARALLEL-'
.-NO SCRATCHPAD-----------.
>--+-------------------------+---------------------------------->
| .-<span class="italic">100</span>-----. |
'-SCRATCHPAD--+---------+-'
'-<span class="italic">integer</span>-'
.-EXTERNAL-----------------------------.
>--+--------------------------------------+---------------------|
'-EXTERNAL NAME--<span class="italic">external-program-name</span>-'
</pre>
<a name="skipsyn-273" id="skipsyn-273"></a>
<a name="wq1199"></a>
<div class="notelisttitle" id="wq1199">Notes:</div>
<ol type="1">
<li>The optional clauses can be specified in a different order.</li>
</ol></div>
<p><a id="idx1641" name="idx1641"></a><a id="idx1642" name="idx1642"></a><a id="idx1643" name="idx1643"></a><a id="idx1644" name="idx1644"></a><a id="idx1645" name="idx1645"></a><a id="idx1646" name="idx1646"></a><a id="idx1647" name="idx1647"></a><a id="idx1648" name="idx1648"></a><a id="idx1649" name="idx1649"></a><a id="idx1650" name="idx1650"></a><a id="idx1651" name="idx1651"></a><a id="idx1652" name="idx1652"></a><a id="idx1653" name="idx1653"></a><a id="idx1654" name="idx1654"></a><a id="idx1655" name="idx1655"></a><a id="idx1656" name="idx1656"></a><a id="idx1657" name="idx1657"></a><a id="idx1658" name="idx1658"></a><a id="idx1659" name="idx1659"></a><a id="idx1660" name="idx1660"></a><a id="idx1661" name="idx1661"></a><a id="idx1662" name="idx1662"></a><a id="idx1663" name="idx1663"></a></p><a id="idx1664" name="idx1664"></a>
<a name="synscrtfunes"></a>
<h3 id="synscrtfunes"><a href="rbafzmst02.htm#ToC_857">Description</a></h3>
<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 input 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 in CREATE FUNCTION is 90.
For external functions created with PARAMETER STYLE SQL, the input and result
parameters specified and the implicit parameters for indicators, SQLSTATE,
function name, specific name, and message text, as well as any optional parameters
are included. The maximum number of parameters is also limited by the maximum
number of parameters allowed by the licensed program that is used to compile
the external program.</p>
<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 input parameter. The data
type can be a built-in data type or a distinct type.
<dl class="parml">
<dt class="bold"><var class="pv">built-in-type</var></dt>
<dd>Specifies a built-in data type. For a more complete description of each
built-in data type, see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>. Some data types are not supported
in all languages. For details on the mapping between the SQL data types and
host language data types, see <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book. Built-in data type specifications can be specified if they correspond
to the language that is used to write the user-defined function.
</dd>
<dt class="bold"><var class="pv">distinct-type-name</var></dt>
<dd>Specifies a user-defined distinct type. The length, precision, or scale
attributes for the parameter are those of the source type of the distinct
type (those specified on CREATE DISTINCT TYPE). For more information on creating
a distinct type, see <a href="rbafzmstcrtudt.htm#crtudt">CREATE DISTINCT TYPE</a>.
<p>If the name of the distinct type is
unqualified, the database manager resolves the schema name by searching the
schemas in the SQL path.</p>
</dd>
</dl>
<p>If a CCSID is specified, the parameter will be converted to that
CCSID prior to passing it to the 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="idx1665" name="idx1665"></a>
<dt class="bold">AS LOCATOR</dt>
<dd>Specifies that a locator to the value of the parameter is passed to
the function instead of the actual value. Specify AS LOCATOR only for parameters
with 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. See &quot;Specifying AS LOCATOR for a parameter&quot; in <a href="rbafzmstcreatef.htm#createf">CREATE FUNCTION</a> for more
information.
</dd>
</dl>
</dd><a id="idx1666" name="idx1666"></a>
<dt class="bold">RETURNS</dt>
<dd>Specifies the output of the function. <a id="idx1667" name="idx1667"></a><a id="idx1668" name="idx1668"></a>
<dl class="parml">
<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, LONG VARGRAPHIC,
or DataLink) or a distinct type (that is not based on a DataLink).</p>
<p>If a CCSID is specified, </p>
<ul>
<li>If AS LOCATOR is not specified, the result returned is assumed to be encoded
in that CCSID.</li>
<li>If AS LOCATOR is specified and the CCSID of the data the locator points
to is encoded in a different CCSID, the data is converted to the specified
CCSID.</li></ul><p class="indatacontent"> If a CCSID is not specified, </p>
<ul>
<li>If AS LOCATOR is not specified, the result returned is assumed to be encoded
in the CCSID of the job (or associated graphic CCSID of the job for graphic
string return values).</li>
<li>If AS LOCATOR is specified, the data the locator points to is converted
to the CCSID of the job, if the CCSID of the data the locator points to 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).</li></ul>
<dl class="parml">
<dt class="bold">AS LOCATOR</dt>
<dd>Specifies that the function returns a locator to the value rather than
the actual value. Specify AS LOCATOR only if the result of 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. See &quot;Specifying AS LOCATOR for a parameter&quot; in <a href="rbafzmstcreatef.htm#createf">CREATE FUNCTION</a> for more
information.
</dd>
</dl>
</dd>
<dt class="bold"><var class="pv">data-type3</var> CAST FROM <var class="pv">data-type4</var></dt>
<dd>Specifies the data type and attributes of the output (<var class="pv">data-type4</var>) and the data type in which that output is returned to the invoking
statement (<var class="pv">data-type3</var>). The two data types can be different. For
example, for the following definition, the function returns a CHAR(10) value,
which the database manager converts to a DATE value and then passes to the statement that
invoked the function:
<pre class="xmp"><span class="bold">CREATE FUNCTION</span> GET_HIRE_DATE <span class="bold">(CHAR</span>6<span class="bold">)</span>
<span class="bold">RETURNS DATE CAST FROM CHAR(</span>10<span class="bold">)</span></pre>The
value of <var class="pv">data-type4</var> must not be a distinct type and must be castable
to <var class="pv">data-type3</var>. The value for <var class="pv">data-type3</var> can be any built-in
data type or distinct type. (For information on casting data types, see <a href="rbafzmstcastdt.htm#castdt">Casting between data types</a>).
<p>For CCSID information, see the description of <var class="pv">data-type2</var> above.</p>
<dl class="parml">
<dt class="bold">AS LOCATOR</dt>
<dd>Specifies that the function returns a locator to the value rather than
the actual value. Specify AS LOCATOR only if the result of 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. See &quot;Specifying AS LOCATOR for a parameter&quot; in <a href="rbafzmstcreatef.htm#createf">CREATE FUNCTION</a> for more
information.
</dd>
</dl>
</dd>
</dl>
</dd><a id="idx1669" name="idx1669"></a>
<dt class="bold">LANGUAGE</dt>
<dd>Specifies the language interface convention to which the function body
is written. All programs must be designed to run in the server's environment.
<p>If LANGUAGE is not specified, the LANGUAGE is determined from the program
attribute information associated with the external program at the time the
function is created. The language of the program is assumed to be C if:</p>
<ul>
<li>The program attribute information associated with the program does not
identify a recognizable language</li>
<li>The program cannot be found</li></ul>
<dl class="parml">
<dt class="bold">C&nbsp;&nbsp;&nbsp; </dt>
<dd>The external program is written in C.
</dd>
<dt class="bold">C++</dt>
<dd>The external program is written in C++.
</dd>
<dt class="bold">CL&nbsp;&nbsp;&nbsp;</dt>
<dd>The external program is written in CL or ILE CL.
</dd>
<dt class="bold">COBOL</dt>
<dd>The external program is written in COBOL.
</dd>
<dt class="bold">COBOLLE</dt>
<dd>The external program is written in ILE COBOL.
</dd>
<dt class="bold">FORTRAN </dt>
<dd>The external program is written in FORTRAN.
</dd>
<dt class="bold">JAVA</dt>
<dd>The external program is written in JAVA. The database manager will call
the user-defined function, which must be a public static method of the specified Java&trade; class
</dd>
<dt class="bold">PLI</dt>
<dd>The external program is written in PL/I.
</dd>
<dt class="bold">RPG </dt>
<dd>The external program is written in RPG.
</dd>
<dt class="bold">RPGLE </dt>
<dd>The external program is written in ILE RPG.
</dd>
</dl>
</dd>
<dt class="bold">PARAMETER STYLE</dt>
<dd>Specifies the conventions used for passing parameters to and returning
the values from functions:
<dl class="parml">
<dt class="bold">SQL</dt><a id="idx1670" name="idx1670"></a>
<dd>All applicable parameters are passed. The parameters are defined to
be in the following order:
<ul>
<li>The first N parameters are the input parameters that are specified on
the CREATE FUNCTION statement.</li>
<li>A parameter for the result of the function.</li>
<li>N parameters for indicator variables for the input parameters.</li>
<li>A parameter for the indicator variable for the result.</li>
<li>A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates
the success or failure of the function. The SQLSTATE returned either be:
<ul>
<li>the SQLSTATE from the last SQL statement executed in the external program,</li>
<li>an SQLSTATE that is assigned by the external program.
<p>The user may
set the SQLSTATE to any valid value in the external program to return an error
or warning from the function.</p></li></ul></li>
<li>A VARCHAR(517) input parameter for the fully qualified function name.</li>
<li>A VARCHAR(128) input parameter for the specific name.</li>
<li>A VARCHAR(70) output parameter for the message text.
<p>When control is
returned to the invoking program, the message text can be found in the 6th
token of the SQLERRMC field of the SQLCA. Only a portion of the message text
is available. For information on the layout of the message data in the SQLERRMC,
see the replacement data descriptions for message SQL0443 in message file
QSQLMSG. The complete message text can be retreived using the GET DIAGNOSTICS
statement. For more information, see <a href="rbafzmstgetdiag.htm#getdiag">GET DIAGNOSTICS</a>.</p></li>
<li>Zero to three optional parameters:
<ul>
<li>A structure (consisting of an INTEGER followed by a CHAR(n)) input and
output parameter for the scratchpad, if SCRATCHPAD was specified on the CREATE
FUNCTION statement.</li>
<li>An INTEGER input parameter for the call type, if FINAL CALL was specified
on the CREATE FUNCTION statement.</li>
<li>A structure for the dbinfo structure, if DBINFO was specified on the CREATE
FUNCTION statement.</li></ul></li></ul>For more information about the parameters passed, see the include <span>sqludf</span> in the appropriate source file in library QSYSINC.
For example, for C, sqludf can be found in QSYSINC/H.
</dd>
<dt class="bold">DB2GENERAL</dt><a id="idx1671" name="idx1671"></a>
<dd>This parameter style is used to specify the conventions for passing
parameters to and returning the value from external functions that are defined
as a method in a Java class. All applicable parameters are passed.
The parameters are defined to be in the following order:
<ul>
<li>The first N parameters are the input parameters that are specified on
the CREATE FUNCTION statement.</li>
<li>A parameter for the result of the function.</li></ul>
<p>DB2GENERAL is only allowed when the LANGUAGE is JAVA.</p>
</dd>
<dt class="bold">GENERAL</dt><a id="idx1672" name="idx1672"></a>
<dd>All applicable parameters are passed. The parameters are defined to
be in the following order:
<ul>
<li>The first N parameters are the input parameters that are specified on
the CREATE FUNCTION statement.</li></ul>Note that the result is returned through as a value of a value returning
function. For example:
<pre class="xmp">return_val func(parameter-1, parameter-2, ...)</pre>
<p>GENERAL is only allowed when EXTERNAL NAME identifies a service program.</p>
</dd>
<dt class="bold">GENERAL WITH NULLS</dt><a id="idx1673" name="idx1673"></a>
<dd>All applicable parameters are passed. The parameters are defined to
be in the following order:
<ul>
<li>The first N parameters are the input parameters that are specified on
the CREATE FUNCTION statement.</li>
<li>An additional argument is passed for an indicator variable array.</li>
<li>A parameter for the indicator variable for the result.</li></ul>Note that the result is returned through as a value of a value returning
function. For example:
<pre class="xmp">return_val func(parameter-1, parameter-2, ...)</pre>
<p>GENERAL WITH NULLS is only allowed when EXTERNAL NAME identifies
a service program.</p>
</dd>
<dt class="bold">JAVA</dt><a id="idx1674" name="idx1674"></a>
<dd>Specifies that the procedure will use a parameter passing convention
that conforms to the Java language and ISO/IEC FCD 9075-13:2003, <span class="italic">Information technology - Database languages - SQL - Part 13: Java Routines and Types (SQL/JRT)</span> specification. All applicable
parameters are passed. The parameters are defined to be in the following
order:
<ul>
<li>The first N parameters are the input parameters that are specified on
the CREATE FUNCTION statement.</li></ul>Note that the result is returned through as a value of a value returning
function. For example:
<pre class="xmp">return_val func(parameter-1, parameter-2, ...)</pre>
<p>JAVA is only allowed when the LANGUAGE is JAVA.</p>
</dd>
</dl>
<p>Note that the language of the external function determines how
the parameters are passed. For example, in C, any VARCHAR or CHAR parameters
are passed as NUL-terminated strings. For more information, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book. For Java routines, see the <a href="../rzaha/whatitis.htm">IBM&reg; Developer
Kit for Java</a>.</p>
</dd><a id="idx1675" name="idx1675"></a>
<dt class="bold">SPECIFIC <var class="pv">specific-name</var></dt>
<dd>Specifies a unique name for the function. See &quot;Specifying a Specific
Name for a Function&quot; in <a href="rbafzmstcreatef.htm#createf">CREATE FUNCTION</a>.
</dd>
<dt class="bold">DETERMINISTIC <span class="base">or</span> NOT DETERMINISTIC</dt>
<dd>Specifies whether the function is deterministic.
<dl class="parml"><a id="idx1676" name="idx1676"></a>
<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">CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA, <span class="base">or</span> NO SQL</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="idx1677" name="idx1677"></a>
<dd>The function does not execute SQL statements that read or modify data.
</dd>
<dt class="bold">NO SQL</dt><a id="idx1678" name="idx1678"></a>
<dd>The function does not execute SQL statements.
</dd>
<dt class="bold">READS SQL DATA</dt><a id="idx1679" name="idx1679"></a>
<dd>The function does not execute SQL statements that modify data.
</dd>
<dt class="bold">MODIFIES SQL DATA</dt><a id="idx1680" name="idx1680"></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">RETURNS NULL ON NULL INPUT <span class="base">or</span> CALLED ON
NULL INPUT</dt><a id="idx1681" name="idx1681"></a><a id="idx1682" name="idx1682"></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">STATIC DISPATCH</dt><a id="idx1683" name="idx1683"></a>
<dd>Specifies that the function is dispatched statically. All functions
are statically dispatched.
</dd>
<dt class="bold">DBINFO</dt><a id="idx1684" name="idx1684"></a><a id="idx1685" name="idx1685"></a>
<dd>Specifies whether or not the function requires the database information
be passed.
<dl class="parml">
<dt class="bold">DBINFO</dt>
<dd>Specifies that the database manager should pass a structure containing
status information to the function. <a href="rbafzmstcfsce.htm#dbinfotb">Table 49</a> contains a description
of the DBINFO structure. Detailed information about the DBINFO structure can
be found in include <span>sqludf</span> in the appropriate source
file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.
<p>DBINFO is only allowed with PARAMETER STYLE DB2SQL or PARAMETER STYLE DB2GENERAL.</p>
<a name="dbinfotb"></a>
<table id="dbinfotb" width="100%" summary="" border="1" frame="border" rules="all">
<caption>Table 49. DBINFO fields</caption>
<thead valign="bottom">
<tr>
<th id="wq1201" width="21%" align="left" valign="bottom">Field</th>
<th id="wq1202" width="15%" align="left" valign="bottom">Data Type</th>
<th id="wq1203" width="62%" align="left" valign="bottom">Description</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td align="left" valign="top" headers="wq1201">Relational database</td>
<td align="left" valign="top" headers="wq1202">VARCHAR(128)</td>
<td align="left" valign="top" headers="wq1203">The name of the current server.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1201">Authorization ID</td>
<td align="left" valign="top" headers="wq1202">VARCHAR(128)</td>
<td align="left" valign="top" headers="wq1203">The run-time authorization ID.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1201">CCSID Information</td>
<td align="left" valign="top" headers="wq1202">
<div class="lines">INTEGER<br />
INTEGER<br />
INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">INTEGER<br />
INTEGER<br />
INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">INTEGER<br />
INTEGER<br />
INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">INTEGER<br />
</div>
<div class="lines">&nbsp;<br />
</div>
<div class="lines">CHAR(8)<br />
</div></td>
<td align="left" valign="top" headers="wq1203">The CCSID information of the job. Three sets
of three CCSIDs are returned. The following information identifies the three
CCSIDs in each set:
<ul>
<li>SBCS CCSID</li>
<li>DBCS CCSID</li>
<li>Mixed CCSID</li></ul>Following the three sets of CCSIDs is an integer that indicates which
set of three sets of CCSIDs is applicable and eight bytes of reserved space.
<p>If a CCSID is not explicitly specified for a parameter on the CREATE FUNCTION
statement, the input string is assumed to be encoded in the CCSID of the job
at the time the function is executed. If the CCSID of the input string is
not the same as the CCSID of the parameter, the input string passed to the
external function will be converted before calling the external program.</p></td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1201">Target column</td>
<td align="left" valign="top" headers="wq1202">VARCHAR(128)
<div class="lines">VARCHAR(128)<br />
</div>
<div class="lines">VARCHAR(128)<br />
</div></td>
<td align="left" valign="top" headers="wq1203">If a user-defined function is specified on
the right-hand side of a SET clause in an UPDATE statement, the following
information identifies the target column:
<ul>
<li>Schema name</li>
<li>Base table name</li>
<li>Column name</li></ul>If the user-defined function is not on the right-hand side of a SET clause
in an UPDATE statement, these fields are blank.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1201">Version and release</td>
<td align="left" valign="top" headers="wq1202">CHAR(8)</td>
<td align="left" valign="top" headers="wq1203">The version, release, and modification level
of the database manager.</td>
</tr>
<tr>
<td align="left" valign="top" headers="wq1201">Platform</td>
<td align="left" valign="top" headers="wq1202">INTEGER</td>
<td align="left" valign="top" headers="wq1203">The server's platform type.</td>
</tr>
</tbody>
</table>
</dd>
<dt class="bold">NO DBINFO</dt>
<dd>Specifies that the function does not require the database information
to be passed.
</dd>
</dl>
</dd>
<dt class="bold">EXTERNAL ACTION <span class="base">or</span> NO EXTERNAL ACTION</dt><a id="idx1686" name="idx1686"></a><a id="idx1687" name="idx1687"></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>This parameter implies that the function
</dd>
<dt class="bold">FENCED <span class="base">or</span> NOT FENCED</dt><a id="idx1688" name="idx1688"></a><a id="idx1689" name="idx1689"></a>
<dd>Specifies whether the external 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">FINAL CALL</dt><a id="idx1690" name="idx1690"></a><a id="idx1691" name="idx1691"></a>
<dd> Specifies whether the function requires special call indication. If
PARAMETER STYLE DB2SQL is specified and FINAL CALL is specified, an additional
parameter is passed to the function indicating first call, normal call, or
final call.
<dl class="parml">
<dt class="bold">NO FINAL CALL</dt>
<dd>Specifies that a final call is not made to the function.
</dd>
<dt class="bold">FINAL CALL</dt>
<dd>Specifies that a final call is made to the function. To differentiate
between final calls and other calls, the function receives an
additional argument that specifies the type of call.
<p>FINAL CALL is only
allowed with PARAMETER STYLE DB2SQL or PARAMETER STYLE DB2GENERAL.</p>
<p>The types of calls are:</p>
<dl>
<dt class="bold">First Call</dt>
<dd>Specifies the first call to the function for this reference to the function
in this SQL statement. A first call is a normal call. SQL arguments are passed
and the function is expected to return a result.
</dd>
<dt class="bold">Normal Call</dt>
<dd>Specifies that SQL arguments are passed and the function is expected
to return a result.
</dd>
<dt class="bold">Final Call</dt>
<dd>Specifies the last call to the function to enable the function to free
resources. A final call is not a normal call. If an error occurs, the database
manager attempts to make the final call.
<p>A final call occurs at these times:</p>
<ul>
<li><span class="italic">End of statement:</span> When the cursor is closed for
cursor-oriented statements, or the execution of the statement has completed.</li>
<li><span class="italic">End of a parallel task:</span> When the function is executed
by parallel tasks.</li>
<li><span class="italic">End of transaction:</span> When normal end of statement
processing does not occur. For example, the logic of an application, for some
reason, bypasses closing the cursor.</li></ul>
<p>Some functions that use a final call can receive incorrect results
if parallel tasks execute the function. For example, if a function sends a
note for each final call to it, one note is sent for each parallel task instead
of once for the function. Specify the DISALLOW PARALLEL clause for functions
that have inappropriate actions when executed in parallel.</p>
<p>If a commit
operation occurs while a cursor defined as WITH HOLD is open, a final call
is made when the cursor is closed or the application ends. If a commit occurs
at the end of a parallel task, a final call is made regardless of whether
a cursor defined as WITH HOLD is open.</p>
</dd>
</dl>
<p>Commitable operations should not be performed during a FINAL CALL,
because the FINAL CALL may occur during a close invoked as part of a COMMIT
operation.</p>
</dd>
</dl>
</dd>
<dt class="bold">PARALLEL</dt><a id="idx1692" name="idx1692"></a><a id="idx1693" name="idx1693"></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>FINAL CALL</li>
<li>MODIFIES SQL DATA</li>
<li>SCRATCHPAD</li></ul>Otherwise, ALLOW PARALLEL is the default.
</dd>
<dt class="bold">SCRATCHPAD</dt><a id="idx1694" name="idx1694"></a><a id="idx1695" name="idx1695"></a>
<dd>Specifies whether the function requires a static memory area.
<dl class="parml">
<dt class="bold"> SCRATCHPAD <var class="pv">integer</var></dt>
<dd>Specifies that the function requires a persistent memory area of length
integer. The integer can range from 1 to 16,000,000. If the memory area is
not specified, the size of the area is 100 bytes. If parameter style DB2SQL
is specified, a pointer is passed following the required parameters that points
to a static storage area. If PARALLEL is specified, a memory area is allocated
for each user-defined function reference in the statement. If DISALLOW PARALLEL
is specified, only 1 memory area will be allocated for the function.
<p>The
scope of a scratchpad is the SQL statement. For each reference to the function
in an SQL statement, there is one scratchpad. For example, assuming that function
UDFX was defined with the SCRATCHPAD keyword, three scratchpads are allocated
for the three references to UDFX in the following SQL statement:</p>
<pre class="xmp"><span class="bold">SELECT</span> A, UDFX(A)
<span class="bold">FROM</span> TABLEB
<span class="bold">WHERE</span> UDFX(A) > 103 <span class="bold">OR</span> UDFX(A) &lt; 19</pre><p class="indatacontent">If the function is run under parallel tasks, one scratchpad is allocated
for each parallel task of each reference to the function in the SQL statement.
This can lead to unpredictable results. For example, if a function uses the
scratchpad to count the number of times that it is invoked, the count reflects
the number of invocations done by the parallel task and not the SQL statement.
Specify the DISALLOW PARALLEL clause for functions that will not work correctly
with parallelism.</p>
<p>SCRATCHPAD is only allowed with PARAMETER STYLE DB2SQL
or PARAMETER STYLE DB2GENERAL.</p>
</dd>
<dt class="bold"> NO SCRATCHPAD</dt>
<dd>Specifies that the function does not require a persistent memory area.
</dd>
</dl>
</dd><a id="idx1696" name="idx1696"></a><a id="idx1697" name="idx1697"></a>
<dt class="bold">EXTERNAL NAME <var class="pv">external-program-name</var></dt>
<dd>Specifies the program, service program, or java class that will be executed
when the function is invoked in an SQL statement. The name must identify a
program, service program, or java class that exists at the application server at the
time the function is invoked. If the naming option is *SYS and the name is
not qualified:
<ul>
<li>The current path will be used to search for the program or service program
at the time the function is invoked.</li>
<li>*LIBL will be used to search for the program or service program at the
time grants or revokes are performed on the function.</li></ul>
<p>The validity of the name is checked at the application server. If the format
of the name is not correct, an error is returned.</p>
<p>If external-program-name
is not specified, the external program name is assumed to be the same as the
function name.</p>
<p>The program, service program, or java class need not
exist at the time the function is created, but it must exist at the time the
function is invoked.</p>
<p>A CONNECT, SET CONNECTION, RELEASE, DISCONNECT,
COMMIT, ROLLBACK and SET TRANSACTION statement is not allowed in the external
program of the function.</p>
</dd>
</dl>
<a name="wq1204"></a>
<h3 id="wq1204"><a href="rbafzmst02.htm#ToC_858">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">Creating the function:</span> When an external function
associated with an ILE external program or service program is created, an
attempt is made to save the function's attributes in the associated program
or service program object. If the *PGM or *SRVPGM object is saved and then
restored to this or another system, the catalogs are automatically updated
with those attributes.</p>
<p>The attributes can be saved for external functions subject to the following
restrictions:</p>
<ul>
<li>The external program library must not be QSYS.</li>
<li>The external program must exist when the CREATE FUNCTION statement is
issued.</li>
<li>The external program must be an ILE *PGM or *SRVPGM object.</li></ul><p class="indatacontent">If the object cannot be updated, the function will still be created.</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 signature is not unique, the function cannot be registered, and
an error is issued.</li>
<li>If the same function signature already exists in the catalog:
<ul>
<li>If the external program or service program name 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">Invoking the function:</span> When an external function
is invoked, it runs in whatever activation group was specified when the external
program or service program was created. However, ACTGRP(*CALLER) should normally
be used so that the function runs in the same activation group as the calling
program. ACTGRP(*NEW) is not allowed.</p>
<p><span class="bold">Notes for Java functions:</span> To be able to run Java functions, you must have the IBM Developer Kit for Java (5722-JV1) installed on your system.
Otherwise, an SQLCODE of -443 will be returned and a CPDB521 message will
be placed in the job log.</p>
<p>If an error occurs while running a Java procedure, an SQLCODE of -443 will
be returned. Depending on the error, other messages may exist in the job log
of the job where the procedure was run.</p>
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
supported for compatibility to prior releases. These keywords are non-standard
and should not be used:</p>
<ul>
<li>The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC
and DETERMINISTIC.</li>
<li>The keywords NULL CALL and NOT NULL CALL can be used as synonyms for CALLED
ON NULL INPUT and RETURNS NULL ON NULL INPUT.</li>
<li>The keywords SIMPLE CALL can be used as a synonym for GENERAL.</li>
<li>The keyword DB2GENRL may be used as a synonym for DB2GENERAL.</li>
<li>The value DB2SQL may be used as a synonym for SQL.</li>
<li>The keywords PARAMETER STYLE in the PARAMETER STYLE clause are optional.</li>
<li>The keywords IS DETERMINISTIC may be used as a synonym for DETERMINISTIC.</li></ul>
<a name="wq1205"></a>
<h3 id="wq1205"><a href="rbafzmst02.htm#ToC_859">Examples</a></h3>
<p><span class="italic">Example 1:</span> Assume an external function program
in C is needed that implements the following logic: </p>
<pre class="xmp"> rslt = 2 * input - 4</pre><p class="indatacontent"> The function should return a null value if and only if one of the input
arguments is null. The simplest way to avoid a function call and get a null
result when an input value is null is to specify RETURNS NULL ON NULL INPUT
on the CREATE FUNCTION statement. The following statement defines the function,
using the specific name MINENULL1. </p>
<a name="rgr6cfz"></a>
<pre id="rgr6cfz" class="xmp"> <span class="bold">CREATE FUNCTION</span> NTEST1 (<span class="bold">SMALLINT</span>)
<span class="bold"> RETURNS SMALLINT</span>
<span class="bold"> EXTERNAL NAME</span> NTESTMOD
<span class="bold"> SPECIFIC</span> MINENULL1
<span class="bold"> LANGUAGE C</span>
<span class="bold"> DETERMINISTIC</span>
<span class="bold"> NO SQL</span>
<span class="bold"> FENCED</span>
<span class="bold"> PARAMETER STYLE SQL</span>
<span class="bold"> RETURNS NULL ON NULL INPUT</span>
<span class="bold"> NO EXTERNAL ACTION</span></pre>
<p>The program code: </p>
<pre class="xmp"> void nudft1
(int *input, /* ptr to input argument */
int *output, /* ptr to output argument */
short *input_ind, /* ptr to input indicator */
short *output_ind, /* ptr to output indicator */
char sqlstate[6], /* sqlstate */
char fname[140], /* fully qualified function name */
char finst[129], /* function specific name */
char msgtext[71]) /* msg text buffer */
{
if (*input_ind == -1)
*output_ind = -1;
else
{
*output = 2*(*input)-4;
*output_ind = 0;
}
return;
} </pre>
<p><span class="italic">Example 2:</span> Assume that a user wants to define an
external function named CENTER. The function program will be written in C.
The following statement defines the function, and lets the database manager
generate a specific name for the function. <span>The name of the program containing
the function body is the same as the name of the function, so the EXTERNAL
clause does not include 'NAME <var class="pv">external-program-name</var>'.</span> </p>
<a name="rgr6cfy"></a>
<pre id="rgr6cfy" class="xmp"> <span class="bold">CREATE FUNCTION</span> CENTER (<span class="bold">INTEGER, FLOAT</span>)
<span class="bold">RETURNS FLOAT</span>
<span class="bold">LANGUAGE C</span>
<span class="bold">DETERMINISTIC</span>
<span class="bold">NO SQL</span>
<span class="bold">PARAMETER STYLE SQL</span>
<span class="bold">NO EXTERNAL ACTION</span></pre>
<p><span class="italic">Example 3:</span> Assume that user McBride (who has administrative
authority) wants to define an external function named CENTER in the SMITH
schema. McBride plans to give the function specific name FOCUS98. The function
program uses a scratchpad to perform some one-time only initialization and
save the results. The function program returns a value with a DOUBLE data
type. The following statement written by user McBride defines the function
and ensures that when the function is invoked, it returns a value with a data
type of DECIMAL(8,4). </p>
<a name="rgr6cfx"></a>
<pre id="rgr6cfx" class="xmp"> <span class="bold">CREATE FUNCTION</span> SMITH.CENTER (<span class="bold">DOUBLE, DOUBLE, DOUBLE</span>)
<span class="bold">RETURNS DECIMAL</span>(8,4)
<span class="bold">CAST FROM DOUBLE</span>
<span class="bold">EXTERNAL NAME</span> CMOD
<span class="bold">SPECIFIC</span> FOCUS98
<span class="bold">LANGUAGE C</span>
<span class="bold">DETERMINISTIC</span>
<span class="bold">NO SQL</span>
<span class="bold">FENCED</span>
<span class="bold">PARAMETER STYLE SQL</span>
<span class="bold">NO EXTERNAL ACTION</span>
<span class="bold">SCRATCHPAD</span>
<span class="bold">NO FINAL CALL</span></pre>
<p><span class="italic">Example 4:</span> The following example defines a Java user-defined function that returns the position of the first vowel
in a string. The user-defined function is written in Java,
is to be run fenced, and is the FINDVWL method of class JAVAUDFS. </p>
<a name="rgr7cfy"></a>
<pre id="rgr7cfy" class="xmp"> <span class="bold">CREATE FUNCTION</span> FINDV (<span class="bold">VARCHAR</span>(32000))
<span class="bold">RETURNS INTEGER</span>
<span class="bold">FENCED</span>
<span class="bold">LANGUAGE JAVA</span>
<span class="bold">PARAMETER STYLE JAVA</span>
<span class="bold">EXTERNAL NAME</span> 'JAVAUDFS.FINDVWL'
<span class="bold">NO EXTERNAL ACTION</span>
<span class="bold">CALLED ON NULL INPUT</span>
<span class="bold">DETERMINISTIC</span>
<span class="bold">NO SQL</span></pre><a id="idx1698" name="idx1698"></a><a id="idx1699" name="idx1699"></a>
<hr /><div class="fnnum"><a id="wq1193" name="wq1193" href="rbafzmstcfsce.htm#wq1192">65</a>.</div>
<div class="fntext">The GRTOBJAUT
CL command must be used to grant these privileges.</div>
<br />
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstcreatef.htm">Previous Page</a> | <a href="rbafzmstcftbe.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