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

731 lines
43 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 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>--+------------------+--------------------->&lt;
'-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">&lt;</td>
<td width="23%" align="center" valign="top">></td>
<td width="23%" align="center" valign="top">>=</td>
</tr>
<tr>
<td align="center" valign="top">&lt;=</td>
<td align="center" valign="top">&lt;></td>
<td align="center" valign="top">&not;=</td>
<td align="center" valign="top">&not;&lt;</td>
</tr>
<tr>
<td>&not;&lt;</td>
<td>!=</td>
<td>!&lt;</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&reg; 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 &lt;= 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 &quot;Extending
or Overriding a Built-in Function&quot; 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>
Reference in New Issue Copy Permalink