731 lines
43 KiB
HTML
731 lines
43 KiB
HTML
<?xml version="1.0" encoding="utf-8"?>
|
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
|
|
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
|
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="dc.language" scheme="rfc1766" content="en-us" />
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
|
<!-- US Government Users Restricted Rights -->
|
|
<!-- Use, duplication or disclosure restricted by -->
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
|
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
|
<meta name="security" content="public" />
|
|
<meta name="Robots" content="index,follow"/>
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
|
<meta name="keywords" content="CREATE DISTINCT TYPE statement,
|
|
CREATE DISTINCT TYPE, SQL statements, in CREATE DISTINCT TYPE statement,
|
|
distinct-type-name, built-in data type, data type for CREATE DISTINCT TYPE,
|
|
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, WITH COMPARISONS" />
|
|
<title>CREATE DISTINCT TYPE</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="crtudt"></a>
|
|
<h2 id="crtudt"><a href="rbafzmst02.htm#ToC_844">CREATE DISTINCT TYPE</a></h2><a id="idx1600" name="idx1600"></a><a id="idx1601" name="idx1601"></a>
|
|
<p>The CREATE DISTINCT TYPE statement defines a distinct type at the current
|
|
server. A distinct type is always sourced on one of the built-in data types.
|
|
Successful execution of the statement also generates:</p>
|
|
<ul>
|
|
<li>A function to cast from the distinct type to its source type</li>
|
|
<li>A function to cast from the source type to its distinct type</li>
|
|
<li>As appropriate, support for the use of comparison operators with the distinct
|
|
type.</li></ul>
|
|
<a name="wq1175"></a>
|
|
<h3 id="wq1175"><a href="rbafzmst02.htm#ToC_845">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="wq1176"></a>
|
|
<h3 id="wq1176"><a href="rbafzmst02.htm#ToC_846">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 SYSTYPES 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 SQL names are specified and a user profile exists that has the same
|
|
name as the library into which the distinct type 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>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>.</p>
|
|
<a name="wq1177"></a>
|
|
<h3 id="wq1177"><a href="rbafzmst02.htm#ToC_847">Syntax</a></h3>
|
|
<a href="rbafzmstcrtudt.htm#synscrtdt"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn269.htm"
|
|
border="0" /></span><a href="#skipsyn-268"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a> .-DISTINCT-.
|
|
>>-CREATE--+----------+--TYPE--<span class="italic">distinct-type-name</span>--------------->
|
|
|
|
>--AS--<span class="italic">built-in-type</span>--+------------------+---------------------><
|
|
'-WITH COMPARISONS-'
|
|
|
|
</pre>
|
|
<a name="skipsyn-268" id="skipsyn-268"></a>
|
|
<a name="wq1178"></a>
|
|
<div class="fignone" id="wq1178">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn270.htm"
|
|
border="0" /></span><a href="#skipsyn-269"><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------' | '-<span class="italic">allocate-clause</span>-' +-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>--+---+--)-' '-<span class="italic">allocate-clause</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------' '-<span class="italic">allocate-clause</span>-' | |
|
|
| | .-(--1M--)-------------. | |
|
|
| '---DBCLOB----+----------------------+--+-----------------+-' |
|
|
| '-(--<span class="italic">integer</span>--+---+--)-' '-<span class="italic">allocate-clause</span>-' |
|
|
| +-K-+ |
|
|
| +-M-+ |
|
|
| '-G-' |
|
|
| .-(--1--)-------. |
|
|
+-+-+-BINARY--+---------------+------------------------------+-----------------+---------------------------------+
|
|
| | | '-(--<span class="italic">integer</span>--)-' | | |
|
|
| | '-+-BINARY VARYING-+--(--<span class="italic">integer</span>--)--+-----------------+-' | |
|
|
| | '-VARBINARY------' '-<span class="italic">allocate-clause</span>-' | |
|
|
| | .-(--1M--)-------------. | |
|
|
| '---+-BLOB----------------+----+----------------------+--+-----------------+-' |
|
|
| '-BINARY LARGE OBJECT-' '-(--<span class="italic">integer</span>--+---+--)-' '-<span class="italic">allocate-clause</span>-' |
|
|
| +-K-+ |
|
|
| +-M-+ |
|
|
| '-G-' |
|
|
+-+-DATE-------------------+-------------------------------------------------------------------------------------+
|
|
| | .-(--0--)-. | |
|
|
| +-TIME--+---------+------+ |
|
|
| | .-(--6--)-. | |
|
|
| '-TIMESTAMP--+---------+-' |
|
|
| .-(--200--)-----. |
|
|
+---DATALINK--+---------------+--+-----------------+--+--------------+-------------------------------------------+
|
|
| '-(--<span class="italic">integer</span>--)-' '-<span class="italic">allocate-clause</span>-' '-<span class="italic">ccsid-clause</span>-' |
|
|
'---ROWID--------------------------------------------------------------------------------------------------------'
|
|
|
|
ccsid-clause:
|
|
|
|
.-NOT NORMALIZED-.
|
|
|--CCSID--<span class="italic">integer</span>--+----------------+---------------------------|
|
|
'-NORMALIZED-----'
|
|
|
|
</pre>
|
|
<a name="skipsyn-269" id="skipsyn-269"></a></div>
|
|
<a name="synscrtdt"></a>
|
|
<h3 id="synscrtdt"><a href="rbafzmst02.htm#ToC_848">Description</a></h3>
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">distinct-type-name</var> </dt><a id="idx1602" name="idx1602"></a>
|
|
<dd>Names the distinct type. The name, including the implicit or explicit
|
|
qualifier, must not be the same as a distinct type that already exists at
|
|
the current server.
|
|
<p>If SQL names were specified, the distinct type will
|
|
be created in the schema specified by the implicit or explicit qualifier.</p>
|
|
<p>If system names were specified, the distinct type will be created in
|
|
the schema that is specified by the qualifier. If not qualified: </p>
|
|
<ul>
|
|
<li>If the value of the CURRENT SCHEMA special register is *LIBL, the distinct
|
|
type will be created in the current library (*CURLIB).</li>
|
|
<li>Otherwise, the distinct type will be created in the current schema.</li></ul>
|
|
<p>If the distinct type name is not a valid system name, DB2 UDB for iSeries will
|
|
generate a system name. For information on the rules for generating a name,
|
|
see <a href="rbafzmsthctabl.htm#namrul">Rules for Table Name Generation</a>.</p>
|
|
<p><span class="italic">distinct-type-name</span> must not be the name of a built-in data type, or any of the following
|
|
system-reserved keywords even if you specify them as delimited identifiers.
|
|
</p>
|
|
<a name="wq1179"></a>
|
|
<table id="wq1179" width="100%" summary="" border="1" frame="hsides" rules="cols">
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td width="28%" align="center" valign="top">=</td>
|
|
<td width="23%" align="center" valign="top"><</td>
|
|
<td width="23%" align="center" valign="top">></td>
|
|
<td width="23%" align="center" valign="top">>=</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top"><=</td>
|
|
<td align="center" valign="top"><></td>
|
|
<td align="center" valign="top">¬=</td>
|
|
<td align="center" valign="top">¬<</td>
|
|
</tr>
|
|
<tr>
|
|
<td>¬<</td>
|
|
<td>!=</td>
|
|
<td>!<</td>
|
|
<td>!></td>
|
|
</tr>
|
|
<tr>
|
|
<td>ALL</td>
|
|
<td align="center" valign="top">DISTINCT</td>
|
|
<td align="center" valign="top">NODENUMBER</td>
|
|
<td align="center" valign="top">SOME</td>
|
|
</tr>
|
|
<tr>
|
|
<td>AND</td>
|
|
<td align="center" valign="top">EXCEPT</td>
|
|
<td align="center" valign="top">NODENAME</td>
|
|
<td align="center" valign="top">STRIP</td>
|
|
</tr>
|
|
<tr>
|
|
<td>ANY</td>
|
|
<td align="center" valign="top">EXISTS</td>
|
|
<td align="center" valign="top">NOT</td>
|
|
<td align="center" valign="top">SUBSTRING</td>
|
|
</tr>
|
|
<tr>
|
|
<td>BETWEEN</td>
|
|
<td align="center" valign="top">EXTRACT</td>
|
|
<td align="center" valign="top">NULL</td>
|
|
<td align="center" valign="top">TABLE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">BOOLEAN</td>
|
|
<td align="center" valign="top">FALSE</td>
|
|
<td align="center" valign="top">ONLY</td>
|
|
<td align="center" valign="top">THEN</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">CASE</td>
|
|
<td align="center" valign="top">FOR</td>
|
|
<td align="center" valign="top">OR</td>
|
|
<td align="center" valign="top">TRIM</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">CAST</td>
|
|
<td align="center" valign="top">FROM</td>
|
|
<td align="center" valign="top">OVERLAPS</td>
|
|
<td align="center" valign="top">TRUE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">CHECK</td>
|
|
<td align="center" valign="top">HASHED_VALUE</td>
|
|
<td align="center" valign="top">PARTITION</td>
|
|
<td align="center" valign="top">TYPE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DATAPARTITIONNAME</td>
|
|
<td align="center" valign="top">IN</td>
|
|
<td align="center" valign="top">POSITION</td>
|
|
<td align="center" valign="top">UNIQUE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DATAPARTITIONNUM</td>
|
|
<td align="center" valign="top">IS</td>
|
|
<td align="center" valign="top">RRN</td>
|
|
<td align="center" valign="top">UNKNOWN</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DBPARTITIONNAME</td>
|
|
<td align="center" valign="top">LIKE</td>
|
|
<td align="center" valign="top">SELECT</td>
|
|
<td align="center" valign="top">WHEN</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center" valign="top">DBPARTITIONNUM</td>
|
|
<td align="center" valign="top">MATCH</td>
|
|
<td align="center" valign="top">SIMILAR</td>
|
|
<td align="center" valign="top"></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p>If a qualified <span class="italic">distinct-type-name</span> is
|
|
specified, the schema name cannot be QSYS, QSYS2, QTEMP, or SYSIBM.</p>
|
|
</dd>
|
|
<dt class="bold"><var class="pv">built-in-type</var> </dt><a id="idx1603" name="idx1603"></a><a id="idx1604" name="idx1604"></a><a id="idx1605" name="idx1605"></a><a id="idx1606" name="idx1606"></a><a id="idx1607" name="idx1607"></a><a id="idx1608" name="idx1608"></a><a id="idx1609" name="idx1609"></a><a id="idx1610" name="idx1610"></a><a id="idx1611" name="idx1611"></a><a id="idx1612" name="idx1612"></a><a id="idx1613" name="idx1613"></a><a id="idx1614" name="idx1614"></a><a id="idx1615" name="idx1615"></a><a id="idx1616" name="idx1616"></a><a id="idx1617" name="idx1617"></a><a id="idx1618" name="idx1618"></a><a id="idx1619" name="idx1619"></a><a id="idx1620" name="idx1620"></a><a id="idx1621" name="idx1621"></a><a id="idx1622" name="idx1622"></a><a id="idx1623" name="idx1623"></a><a id="idx1624" name="idx1624"></a><a id="idx1625" name="idx1625"></a><a id="idx1626" name="idx1626"></a>
|
|
<dd>Specifies the built-in data type used as the basis for the internal
|
|
representation of the distinct type. See <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a> for a more
|
|
complete description of each built-in data type.
|
|
<p>For portability of applications
|
|
across platforms, use the following recommended data type names: </p>
|
|
<ul>
|
|
<li>DOUBLE or REAL instead of FLOAT.</li>
|
|
<li>DECIMAL instead of NUMERIC.</li></ul>
|
|
<p>If you do not specify a specific value for the data types that
|
|
have length, precision, or scale attributes, the default attributes of the
|
|
data type as shown in the syntax diagram are implied.</p>
|
|
<p>If the distinct
|
|
type is sourced on a string data type, a CCSID is associated with the distinct
|
|
data type at the time the distinct type is created. For more information about
|
|
data types, see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>.</p>
|
|
</dd>
|
|
<dt class="bold">WITH COMPARISONS </dt><a id="idx1627" name="idx1627"></a>
|
|
<dd>Specifies that system-generated comparison functions are to be created
|
|
for comparing two instances of the distinct type. WITH COMPARISONS is the
|
|
default. Comparison functions will be generated for all source types with
|
|
the exception of a DATALINK whether or not WITH COMPARISONS is specified.<sup class="fn"><a id="wq1180" name="wq1180" href="rbafzmstcrtudt.htm#wq1181">64</a></sup> For compatibility
|
|
with other DB2® products, WITH COMPARISONS should be specified.
|
|
<p>The comparison
|
|
functions do not support the LIKE predicate. In order to use the LIKE predicate
|
|
on a distinct type, it must be cast to a built-in type.</p>
|
|
</dd>
|
|
</dl>
|
|
<a name="wq1182"></a>
|
|
<h3 id="wq1182"><a href="rbafzmst02.htm#ToC_849">Notes</a></h3>
|
|
<p><span class="bold">Additional generated functions:</span> The successful execution
|
|
of the CREATE DISTINCT TYPE statement causes the database manager to generate the following
|
|
cast functions: </p>
|
|
<ul>
|
|
<li>One function to convert from the distinct type to the source type</li>
|
|
<li>One function to convert from the source type to the distinct type</li>
|
|
<li>One function to convert from INTEGER to the distinct type if the source
|
|
type is SMALLINT</li>
|
|
<li>One function to convert from DOUBLE to the distinct type if the source
|
|
type is REAL</li>
|
|
<li>One function to convert from VARCHAR to the distinct type if the source
|
|
type is CHAR</li>
|
|
<li>One function to convert from VARGRAPHIC to the distinct type if the source
|
|
type is GRAPHIC.</li></ul>
|
|
<p>The cast functions are created as if the following statements were executed
|
|
(except that the service programs are not created, so you cannot grant or
|
|
revoke privileges to these functions): </p>
|
|
<pre class="xmp"><span class="bold">CREATE FUNCTION</span> source-type-name (distinct-type-name)
|
|
<span class="bold">RETURNS</span> source-type-name
|
|
|
|
<span class="bold">CREATE FUNCTION</span> distinct-type-name (source-type-name)
|
|
<span class="bold">RETURNS</span> distinct-type-name
|
|
</pre>
|
|
<p><span class="bold">Names of the generated cast functions:</span> <a href="rbafzmstcrtudt.htm#gendtcf">Table 48</a> contains
|
|
details about the generated cast functions. The unqualfied name of the cast
|
|
function that converts from the distinct type to the source type is the name
|
|
of the source data type.</p>
|
|
<p>In cases in which a length, precision, or scale is specified for the source
|
|
data type in the CREATE DISTINCT TYPE statement, the unqualified name of the
|
|
cast function that converts from the distinct type to the source type is simply
|
|
the name of the source data type. The data type of the value that the cast
|
|
function returns includes any length, precision, or scale values that were
|
|
specified for the source data type on the CREATE DISTINCT TYPE statement.</p>
|
|
<p> The name of the cast function that converts from the source type to the
|
|
distinct type is the name of the distinct type. The input parameter of the
|
|
cast function has the same data type as the source data type, including the
|
|
length, precision, and scale.</p>
|
|
<p>The cast functions that are generated are created in the same schema as
|
|
that of the distinct type. A function with the same name and same function
|
|
signature must not already exist in the current server.</p>
|
|
<p>For example, assume that a distinct type named T_SHOESIZE is created with
|
|
the following statement: </p>
|
|
<pre class="xmp"><span class="bold">CREATE DISTINCT TYPE</span> CLAIRE.T_SHOESIZE <span class="bold">AS VARCHAR(</span>2<span class="bold">) WITH COMPARISONS</span></pre>
|
|
<p>When the statement is executed, the database manager also generates the following cast
|
|
functions. VARCHAR converts from the distinct type to the source type, and
|
|
T_SHOESIZE converts from the source type to the distinct type. </p>
|
|
<pre class="xmp"><span class="bold">FUNCTION</span> CLAIRE.VARCHAR <span class="bold">(</span>CLAIRE.T_SHOESIZE<span class="bold">) RETURNS VARCHAR(</span>2<span class="bold">)</span>
|
|
|
|
<span class="bold">FUNCTION</span> CLAIRE.T_SHOESIZE <span class="bold">(VARCHAR(</span>2<span class="bold">) RETURNS</span> CLAIRE.T_SHOESIZE
|
|
</pre>
|
|
<p>Notice that function VARCHAR returns a value with a data type of VARCHAR(2)
|
|
and that function T_SHOESIZE has an input parameter with a data type of VARCHAR(2).</p>
|
|
<p>A generated cast function cannot be explicitly dropped. The cast functions
|
|
that are generated for a distinct type are implicitly dropped when the distinct
|
|
type is dropped with the DROP statement.</p>
|
|
<p>For each built-in data type that can be the source data type for a distinct
|
|
type, the following table gives the names of the generated cast functions,
|
|
the data types of the input parameters, and the data types of the values that
|
|
the functions returns.</p>
|
|
<a name="gendtcf"></a>
|
|
<table id="gendtcf" width="100%" summary="" border="1" frame="border" rules="rows">
|
|
<caption>Table 48. CAST Functions on Distinct Types</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq1183" width="25%" align="left" valign="bottom">Source Type Name</th>
|
|
<th id="wq1184" width="25%" align="left" valign="bottom">Function Name</th>
|
|
<th id="wq1185" width="25%" align="left" valign="bottom">Parameter Type</th>
|
|
<th id="wq1186" width="25%" align="left" valign="bottom">Return Type</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">SMALLINT</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">SMALLINT</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">INTEGER</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">SMALLINT</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">SMALLINT</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">INTEGER</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">INTEGER</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">INTEGER</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">BIGINT</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">BIGINT</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">BIGINT</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">BIGINT</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">DECIMAL</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">DECIMAL(p,s)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">DECIMAL</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">DECIMAL(p,s)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">NUMERIC</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">NUMERIC(p,s)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">NUMERIC</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">NUMERIC(p,s)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">REAL or FLOAT(n) where n <= 24</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">REAL</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">DOUBLE</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">REAL</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">REAL</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">DOUBLE or DOUBLE PRECISION or FLOAT(n) where
|
|
n > 24</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">DOUBLE</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">DOUBLE</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">DOUBLE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">CHAR</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">CHAR(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">CHAR</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">CHAR(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">VARCHAR(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">VARCHAR</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">VARCHAR(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">VARCHAR</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">VARCHAR(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">CLOB</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">CLOB(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">CLOB</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">CLOB(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">GRAPHIC</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">GRAPHIC(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">GRAPHIC</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">GRAPHIC(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">VARGRAPHIC(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">VARGRAPHIC</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">VARGRAPHIC(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">VARGRAPHIC</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">VARGRAPHIC(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">DBCLOB</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">DBCLOB(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">DBCLOB</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">DBCLOB(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">BINARY</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">BINARY(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">BINARY</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">BINARY(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">VARBINARY(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">VARBINARY</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">VARBINARY(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">VARBINARY</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">VARBINARY(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">BLOB</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">BLOB(n)</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">BLOB</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">BLOB(n)</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">DATE</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">DATE</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">DATE</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">DATE</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">TIME</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">TIME</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">TIME</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">TIME</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">TIMESTAMP</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">TIMESTAMP</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">TIMESTAMP</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">TIMESTAMP</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">DATALINK</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">DATALINK</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">DATALINK</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">DATALINK</td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183">ROWID</td>
|
|
<td align="left" valign="top" headers="wq1184"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1185">ROWID</td>
|
|
<td align="left" valign="top" headers="wq1186"><span class="italic">distinct-type-name</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="left" valign="top" headers="wq1183"></td>
|
|
<td align="left" valign="top" headers="wq1184">ROWID</td>
|
|
<td align="left" valign="top" headers="wq1185"><span class="italic">distinct-type-name</span></td>
|
|
<td align="left" valign="top" headers="wq1186">ROWID</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p>NUMERIC and FLOAT are not recommended when creating a distinct type for
|
|
a portable application. DECIMAL and DOUBLE should be used instead.</p>
|
|
<p><span class="bold">Distinct type attributes:</span> A distinct type is created
|
|
as a *SQLUDT object.</p>
|
|
<p><span class="bold">Distinct type ownership:</span> If SQL names
|
|
were specified:</p>
|
|
<ul>
|
|
<li>If a user profile with the same name as the schema into which the distinct
|
|
type is created exists, the <span class="italic">owner</span> of the distinct
|
|
type is that user profile.</li>
|
|
<li>Otherwise, the <span class="italic">owner</span> of the distinct type 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 distinct type is
|
|
the user profile or group user profile of the job executing the statement.</p>
|
|
<p><span class="bold">Distinct type authority:</span> If SQL names are used, distinct types
|
|
are created with the system authority of *EXCLUDE on *PUBLIC. If system names
|
|
are used, distinct types 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 distinct type 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 distinct type.</p>
|
|
<p><span class="bold">Built-in functions:</span> The functions described in the
|
|
above table are the only functions that are generated automatically when distinct
|
|
types are defined. Consequently, none of the built-in functions (AVG, MAX,
|
|
LENGTH, and so on) are automatically supported for the distinct type. A built-in function
|
|
can be used on a distinct type only after a sourced user-defined function, which is
|
|
based on the built-in function, has been created for the distinct type. See "Extending
|
|
or Overriding a Built-in Function" under <a href="rbafzmstcreatef.htm#createf">CREATE FUNCTION</a>.</p>
|
|
<p>The schema name of the distinct type must be included in the distinct type for successful
|
|
use of these operators and cast functions in SQL statements.</p>
|
|
<a name="wq1187"></a>
|
|
<h3 id="wq1187"><a href="rbafzmst02.htm#ToC_850">Examples</a></h3>
|
|
<p><span class="italic">Example 1:</span> Create a distinct type named SHOESIZE
|
|
that is sourced on the built-in INTEGER data type. </p>
|
|
<pre class="xmp"> <span class="bold">CREATE DISTINCT TYPE</span> SHOESIZE <span class="bold">AS INTEGER WITH COMPARISONS</span> </pre><p class="indatacontent">The successful execution of this statement also generates two
|
|
cast functions. Function INTEGER(SHOESIZE) returns a value with data type
|
|
INTEGER, and function SHOESIZE(INTEGER) returns a value with distinct type
|
|
SHOESIZE.</p>
|
|
<p><span class="italic">Example 2:</span> Create a distinct type named MILES that
|
|
is sourced on the built-in DOUBLE data type. </p>
|
|
<pre class="xmp"><span class="bold"> CREATE DISTINCT TYPE</span> MILES
|
|
<span class="bold">AS DOUBLE WITH COMPARISONS</span></pre><p class="indatacontent"> The successful
|
|
execution of this statement also generates two cast functions. Function DOUBLE(MILES)
|
|
returns a value with data type DOUBLE, and function MILES(DOUBLE) returns
|
|
a value with distinct type MILES.</p>
|
|
<p><span class="italic">Example 3:</span> Create a distinct type T_DEPARTMENT
|
|
that is sourced on the built-in CHAR data type. </p>
|
|
<pre class="xmp"><span class="bold"> CREATE DISTINCT TYPE</span> CLAIRE.T_DEPARTMENT <span class="bold">AS CHAR</span>(3)
|
|
<span class="bold"> WITH COMPARISONS</span></pre><p class="indatacontent">The successful execution
|
|
of this statement also generates three cast functions:</p>
|
|
<ul>
|
|
<li>Function CLAIRE.CHAR takes a T_DEPARTMENT as input and returns a value
|
|
with data type CHAR(3).</li>
|
|
<li>Function CLAIRE.T_DEPARTMENT takes a CHAR(3) as input and returns a value
|
|
with distinct type T_DEPARTMENT.</li>
|
|
<li>Function CLAIRE.T_DEPARTMENT takes a VARCHAR(3) as input and returns a
|
|
value with distinct type T_DEPARTMENT.</li></ul>
|
|
<hr /><div class="fnnum"><a id="wq1181" name="wq1181" href="rbafzmstcrtudt.htm#wq1180">64</a>.</div>
|
|
<div class="fntext">Service programs are not created for these comparison functions. These comparison
|
|
functions are not registered in the SYSROUTINES catalog table.</div>
|
|
<br />
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmsthcalias.htm">Previous Page</a> | <a href="rbafzmstcreatef.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>
|