164 lines
10 KiB
HTML
164 lines
10 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 lang="en-us" xml:lang="en-us">
|
||
<head>
|
||
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
<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="DC.Type" content="reference" />
|
||
<meta name="DC.Title" content="Parameter style DB2SQL" />
|
||
<meta name="abstract" content="With the DB2SQL parameter style, the same parameters and same order of parameters are passed into the external program or service program as are passed in for parameter style SQL. However, DB2SQL allows additional optional parameters to be passed along as well." />
|
||
<meta name="description" content="With the DB2SQL parameter style, the same parameters and same order of parameters are passed into the external program or service program as are passed in for parameter style SQL. However, DB2SQL allows additional optional parameters to be passed along as well." />
|
||
<meta name="DC.subject" content="UDFs (User-defined functions), parameter style DB2SQL, passing scratchpad, passing call-type, call-type, passing to UDF, passing argument with DBINFO, CREATE FUNCTION statement, DBINFO keyword, functions" />
|
||
<meta name="keywords" content="UDFs (User-defined functions), parameter style DB2SQL, passing scratchpad, passing call-type, call-type, passing to UDF, passing argument with DBINFO, CREATE FUNCTION statement, DBINFO keyword, functions" />
|
||
<meta name="DC.Relation" scheme="URI" content="rbafypassarguments.htm" />
|
||
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
<meta name="DC.Format" content="XHTML" />
|
||
<meta name="DC.Identifier" content="rbafyparamdb2sql" />
|
||
<meta name="DC.Language" 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. -->
|
||
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
<title>Parameter style DB2SQL</title>
|
||
</head>
|
||
<body id="rbafyparamdb2sql"><a name="rbafyparamdb2sql"><!-- --></a>
|
||
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
<h1 class="topictitle1">Parameter style DB2SQL</h1>
|
||
<div><p>With the DB2SQL parameter style, the same parameters and same order
|
||
of parameters are passed into the external program or service program as are
|
||
passed in for parameter style SQL. However, DB2SQL allows additional optional
|
||
parameters to be passed along as well.</p>
|
||
<div class="section"><p>If more than one of the optional parameters below is specified
|
||
in the UDF definition, they are passed to the UDF in the order defined below.
|
||
Refer to parameter style SQL for the common parameters. This parameter style
|
||
can be used for both scalar and table UDFs.</p>
|
||
</div>
|
||
<div class="section"><p>For scalar functions:</p>
|
||
</div>
|
||
<div class="section"> <pre><span><img src="./c.gif" border="0" alt="Read syntax diagram" longdesc="rbafyparamdb2sqlsyn1.htm" /></span><a href="#d0e45"><img src="./c.gif" border="0" alt="Skip visual syntax diagram" /></a>
|
||
>>-+------------------+--<i>SQL-result</i>--+----------------------+--->
|
||
| .--------------. | | .------------------. |
|
||
| V | | | V | |
|
||
'---<i>SQL-argument</i>-+-' '---<i>SQL-argument-ind</i>-+-'
|
||
|
||
>--<i>SQL-result-ind</i>--<i>SQL-state</i>--<i>function-name</i>--<i>specific-name</i>------>
|
||
|
||
>--<i>diagnostic-message</i>--+------------+--+-----------+------------>
|
||
'-<i>scratchpad</i>-' '-<i>call-type</i>-'
|
||
|
||
>--+--------+--------------------------------------------------><
|
||
'-<i>dbinfo</i>-'
|
||
|
||
</pre><a name="d0e45"></a></div>
|
||
<div class="section"><p>For table functions:</p>
|
||
</div>
|
||
<div class="section"> <pre><span><img src="./c.gif" border="0" alt="Read syntax diagram" longdesc="rbafyparamdb2sqlsyn2.htm" /></span><a href="#d0e90"><img src="./c.gif" border="0" alt="Skip visual syntax diagram" /></a>
|
||
.------------.
|
||
V |
|
||
>>-+------------------+----<i>SQL-result</i>-+------------------------->
|
||
| .--------------. |
|
||
| V | |
|
||
'---<i>SQL-argument</i>-+-'
|
||
|
||
.----------------.
|
||
V |
|
||
>--+----------------------+----<i>SQL-result-ind</i>-+--<i>SQL-state</i>------>
|
||
| .------------------. |
|
||
| V | |
|
||
'---<i>SQL-argument-ind</i>-+-'
|
||
|
||
>--<i>function-name</i>--<i>specific-name</i>--<i>diagnostic-message</i>------------->
|
||
|
||
>--+------------+--<i>call-type</i>--+--------+-----------------------><
|
||
'-<i>scratchpad</i>-' '-<i>dbinfo</i>-'
|
||
|
||
</pre><a name="d0e90"></a></div>
|
||
<div class="section"> <dl><dt class="dlterm"><em>scratchpad</em></dt>
|
||
<dd>This argument is set by DB2<sup>®</sup> before calling the UDF. It is only present if the
|
||
CREATE FUNCTION statement for the UDF specified the SCRATCHPAD keyword. This
|
||
argument is a structure with the following elements: <ul><li>An INTEGER containing the length of the scratchpad. </li>
|
||
<li>The actual scratchpad, initialized to all binary 0's by DB2 before the
|
||
first call to the UDF.</li>
|
||
</ul>
|
||
The scratchpad can be used by the UDF either as working storage or as
|
||
persistent storage, since it is maintained across UDF invocations. <p>For
|
||
table functions, the scratchpad is initialized as above before the FIRST call
|
||
to the UDF if FINAL CALL is specified on the CREATE FUNCTION. After this call,
|
||
the scratchpad content is totally under control of the table function. DB2 does
|
||
not examine or change the content of the scratchpad thereafter. The scratchpad
|
||
is passed to the function on each invocation. The function can be re-entrant,
|
||
and DB2 preserves
|
||
its state information in the scratchpad.</p>
|
||
<p>If NO FINAL CALL was specified
|
||
or defaulted for a table function, then the scratchpad is initialized as above
|
||
for each OPEN call, and the scratchpad content is completely under control
|
||
of the table function between OPEN calls. This can be very important for a
|
||
table function used in a join or subquery. If it is necessary to maintain
|
||
the content of the scratchpad across OPEN calls, then FINAL CALL must be specified
|
||
in your CREATE FUNCTION statement. With FINAL CALL specified, in addition
|
||
to the normal OPEN, FETCH, and CLOSE calls, the table function will also receive
|
||
FIRST and FINAL calls, for the purpose of scratchpad maintenance and resource
|
||
release.</p>
|
||
</dd>
|
||
<dt class="dlterm"><em>call-type</em></dt>
|
||
<dd>This argument is set by DB2 before calling the UDF. For scalar functions, it
|
||
is only present if the CREATE FUNCTION statement for the UDF specified the
|
||
FINAL CALL keyword. However, for table functions it is <em>always</em> present.
|
||
It follows the <em>scratchpad</em> argument; or the <em>diagnostic-message</em> argument
|
||
if the scratchpad argument is not present. This argument takes the form of
|
||
an INTEGER value. <p>For scalar functions:</p>
|
||
<dl><dt class="dlterm">-1</dt>
|
||
<dd> This is the <em>first call</em> to the UDF for this statement. A first
|
||
call is a <em>normal call</em> in that all SQL argument values are passed.</dd>
|
||
<dt class="dlterm">0</dt>
|
||
<dd>This is a <em>normal call</em>. (All the normal input argument values are
|
||
passed).</dd>
|
||
<dt class="dlterm">1</dt>
|
||
<dd>This is a <em>final call</em>. No <em>SQL-argument</em> or <em>SQL-argument-ind</em> values
|
||
are passed. A UDF should not return any answer using the SQL-result, SQL-result-ind
|
||
arguments, SQL-state, or diagnostic-message arguments. These arguments are
|
||
ignored by the system when returned from the UDF.</dd>
|
||
</dl>
|
||
<p>For table functions:</p>
|
||
<dl><dt class="dlterm">-2</dt>
|
||
<dd> This is the <em>first call</em> to the UDF for this statement. A first
|
||
call is a <em>normal call</em> in that all SQL argument values are passed.</dd>
|
||
<dt class="dlterm">-1</dt>
|
||
<dd> This is the <em>open call</em> to the UDF for this statement. The scratchpad
|
||
is initialized if NO FINAL CALL is specified, but not necessarily otherwise.
|
||
All SQL argument values are passed.</dd>
|
||
<dt class="dlterm">0</dt>
|
||
<dd>This is a <em>fetch call</em>. DB2 expects the table function to return
|
||
either a row comprising the set of return values, or an end-of-table condition
|
||
indicated by SQLSTATE value '02000'.</dd>
|
||
<dt class="dlterm">1</dt>
|
||
<dd>This is a <em>close call</em>. This call balances the OPEN call, and can
|
||
be used to perform any external CLOSE processing and resource release.</dd>
|
||
<dt class="dlterm">2</dt>
|
||
<dd>This is a <em>final call</em>. No <em>SQL-argument</em> or <em>SQL-argument-ind</em> values
|
||
are passed. A UDF should not return any answer using the SQL-result, SQL-result-ind
|
||
arguments, SQL-state, or diagnostic-message arguments. These arguments are
|
||
ignored by the system when returned from the UDF.</dd>
|
||
</dl>
|
||
</dd>
|
||
<dt class="dlterm"><em>dbinfo</em></dt>
|
||
<dd>This argument is set by DB2 before calling the UDF. It is only present if the
|
||
CREATE FUNCTION statement for the UDF specifies the DBINFO keyword. The argument
|
||
is a structure whose definition is contained in the <samp class="codeph">sqludf</samp> include.</dd>
|
||
</dl>
|
||
</div>
|
||
</div>
|
||
<div>
|
||
<div class="familylinks">
|
||
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafypassarguments.htm" title="DB2 provides storage for all parameters passed to a UDF. Therefore, parameters are passed to the external function by address.">Pass arguments from DB2 to external functions</a></div>
|
||
</div>
|
||
</div>
|
||
</body>
|
||
</html>
|