1116 lines
66 KiB
HTML
1116 lines
66 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="data types, SQL (Structured Query Language),
|
||
|
|
description, data type, null value, defined, null value in SQL, numbers, numeric,
|
||
|
|
precision of a number, precision, SMALLINT data type, small integers, integers,
|
||
|
|
RPG, application program, COBOL, INTEGER data type, large integers,
|
||
|
|
BIGINT data type, big integers, floating point, single-precision floating-point,
|
||
|
|
double-precision floating point, decimal, in SQL, scale of data,
|
||
|
|
string representation, default decimal separator character, character strings,
|
||
|
|
definition, character string, columns, string, empty character string, empty,
|
||
|
|
character data string, length attribute, column, fixed-length, variable,
|
||
|
|
varying-length, varying-length string variables, PL/I,
|
||
|
|
varying-length string variables not allowed,
|
||
|
|
NUL-terminated string variables allowed, CLOB, bit data, SBCS data, mixed data,
|
||
|
|
UTF-8 (universal coded character set), graphic string, DBCLOB,
|
||
|
|
DBCS (double-byte character set), Unicode data, graphic data string, BINARY,
|
||
|
|
VARBINARY, BLOB, binary strings, binary string, LOB, large object (LOB), locator,
|
||
|
|
limitations on use of, dates and times, datetime, DATE, TIME, TIMESTAMP,
|
||
|
|
date and time, default date format, default time format, strings, date, EUR,
|
||
|
|
format, ISO, JIS, USA, unformatted Julian, Julian, month/day/year,
|
||
|
|
day/month/year, year/month/day, *EUR date and time format,
|
||
|
|
*ISO date and time format, *JIS date and time format, *USA date and time format,
|
||
|
|
*JUL date and time format, *MDY date and time format, *DMY date and time format,
|
||
|
|
*YMD date and time format, time, hours/minutes/seconds,
|
||
|
|
*HMS date and time format, timestamp, DataLink, Row ID,
|
||
|
|
user-defined types (UDTs), distinct types" />
|
||
|
|
<title>Data types</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="ch2data"></a>
|
||
|
|
<h2 id="ch2data"><a href="rbafzmst02.htm#ToC_97">Data types</a></h2><a id="idx304" name="idx304"></a><a id="idx305" name="idx305"></a>
|
||
|
|
<p>The smallest unit of data that can be manipulated in SQL is called a <span class="italic">value.</span> How values are interpreted depends on the data
|
||
|
|
type of their source. The sources of values are: </p>
|
||
|
|
<ul>
|
||
|
|
<li>Columns</li>
|
||
|
|
<li>Constants</li>
|
||
|
|
<li>Expressions</li>
|
||
|
|
<li>Functions</li>
|
||
|
|
<li>Special registers</li>
|
||
|
|
<li>Variables (such as host variables, SQL variables, parameter markers and
|
||
|
|
parameters of routines)</li></ul>
|
||
|
|
<p>The DB2® UDB relational database products support both built-in data types
|
||
|
|
and user-defined data types. This section describes the built-in data types.
|
||
|
|
For a description of distinct types, see <a href="rbafzmstch2data.htm#ch2udf">User-defined types</a>.</p>
|
||
|
|
<p> The following figure illustrates the various built-in data types supported
|
||
|
|
by the DB2 UDB for iSeries program.</p>
|
||
|
|
<a name="wq75"></a>
|
||
|
|
<div class="fignone" id="wq75">
|
||
|
|
<div class="mmobj">
|
||
|
|
<img src="rbafz501.gif" alt="Supported data types: Datalink, Time, Timestamp, Date, Char, Varchar, Clob, Graphic, Vargraphic, Dbclob, Binary, Varbinary, Blob, Real, Double, Rowid, Smallint, Integer, Bigint, Decimal, and Numeric." /></div></div>
|
||
|
|
<p>For more details on data types, see the following topics:</p>
|
||
|
|
<ul>
|
||
|
|
<li><a href="rbafzmstch2data.htm#ch2num">Numbers</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#charstrg">Character strings</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#charsubtype">Character encoding schemes</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#graphicstrg">Graphic strings</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#graphicsubtype">Graphic encoding schemes</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#binarystrg">Binary strings</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#lobs">Large objects</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#ch2datetime">Datetime values</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#ch2datalink">DataLink values</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#ch2rowid">Row ID values</a></li>
|
||
|
|
<li><a href="rbafzmstch2data.htm#ch2udf">User-defined types</a></li></ul>
|
||
|
|
<p>For information about specifying the data types of columns, see <a href="rbafzmsthctabl.htm#hctabl">CREATE TABLE</a>.</p>
|
||
|
|
<a name="wq76"></a>
|
||
|
|
<h3 id="wq76"><a href="rbafzmst02.htm#ToC_98">Nulls</a></h3>
|
||
|
|
<p><a id="idx306" name="idx306"></a><a id="idx307" name="idx307"></a></p>
|
||
|
|
<p>All data types include the null value. Distinct from all non-null
|
||
|
|
values, the null value is a special value that denotes the absence of a (non-null)
|
||
|
|
value. Except for grouping operations, a null value is also distinct from
|
||
|
|
another null value. Although all data types include the null value, some sources
|
||
|
|
of values cannot provide the null value. For example, constants, columns that
|
||
|
|
are defined as NOT NULL, and special registers cannot contain null values;
|
||
|
|
the COUNT and COUNT_BIG functions cannot return a null value; and ROWID columns
|
||
|
|
cannot store a null value although a null value can be returned for a ROWID
|
||
|
|
column as the result of a query.</p>
|
||
|
|
<a name="ch2num"></a>
|
||
|
|
<h3 id="ch2num"><a href="rbafzmst02.htm#ToC_99">Numbers</a></h3><a id="idx308" name="idx308"></a><a id="idx309" name="idx309"></a><a id="idx310" name="idx310"></a><a id="idx311" name="idx311"></a><a id="idx312" name="idx312"></a>
|
||
|
|
<p>The numeric data types are binary integer, floating-point, and decimal.
|
||
|
|
Binary integer includes small integer, large integer, and big integer. Floating-point
|
||
|
|
includes single precision and double precision. Binary numbers are exact representations
|
||
|
|
of integers. Decimal numbers are exact representations of real numbers. Binary
|
||
|
|
and decimal numbers are considered exact numeric types. Floating-point numbers
|
||
|
|
are approximations of real numbers and are considered approximate numeric
|
||
|
|
types.</p>
|
||
|
|
<p>All numbers have a <span class="italic">sign,</span> a <span class="italic">precision,</span> and a <span class="italic">scale</span>. <a id="idx313" name="idx313"></a>If a column value is zero,
|
||
|
|
the sign is positive. The precision is the total number of binary or decimal
|
||
|
|
digits excluding the sign. The scale is the total number of binary or decimal
|
||
|
|
digits to the right of the decimal point. If there is no decimal point, the
|
||
|
|
scale is zero.</p>
|
||
|
|
<a name="wq77"></a>
|
||
|
|
<h4 id="wq77">Small integer</h4><a id="idx314" name="idx314"></a><a id="idx315" name="idx315"></a><a id="idx316" name="idx316"></a><a id="idx317" name="idx317"></a>
|
||
|
|
<p>A <span class="italic">small integer</span> is a binary number composed of
|
||
|
|
2 bytes with a precision of 5 digits. The range of small integers is -32768
|
||
|
|
to +32767.</p>
|
||
|
|
<p>For small integers, decimal precision and scale are supported by COBOL, RPG,
|
||
|
|
and iSeries system files. For information concerning the precision and scale
|
||
|
|
of binary integers, see the <a href="../rzahg/rzahgdds.htm">DDS Reference</a> book.</p>
|
||
|
|
<a name="wq78"></a>
|
||
|
|
<h4 id="wq78">Large integer</h4><a id="idx318" name="idx318"></a><a id="idx319" name="idx319"></a>
|
||
|
|
<p>A <span class="italic">large integer</span> is a binary number composed of
|
||
|
|
4 bytes with a precision of 10 digits. The range of large integers is -2147483648
|
||
|
|
to +2147483647.</p>
|
||
|
|
<p>For large integers, decimal precision and scale are supported by COBOL, RPG,
|
||
|
|
and iSeries system files. For information concerning the precision and scale
|
||
|
|
of binary integers, see the <a href="../rzahg/rzahgdds.htm">DDS Reference</a> book.</p>
|
||
|
|
<a name="wq79"></a>
|
||
|
|
<h4 id="wq79">Big integer</h4><a id="idx320" name="idx320"></a><a id="idx321" name="idx321"></a>
|
||
|
|
<p>A <span class="italic">big integer</span> is a binary number composed of 8
|
||
|
|
bytes with a precision of 19 digits. The range of big integers is -9223372036854775808
|
||
|
|
to +9223372036854775807.</p>
|
||
|
|
<a name="wq80"></a>
|
||
|
|
<h4 id="wq80">Floating-point</h4><a id="idx322" name="idx322"></a><a id="idx323" name="idx323"></a>
|
||
|
|
<p>A <span class="italic">single-precision floating-point</span> number is a 32-bit
|
||
|
|
approximate representation of a real number. The range of magnitude is approximately
|
||
|
|
1.17549436 × 10<sup>-38</sup> to 3.40282356
|
||
|
|
× 10<sup>38</sup>.</p><a id="idx324" name="idx324"></a>
|
||
|
|
<p>A <span class="italic">double-precision floating-point</span> number is a IEEE 64-bit
|
||
|
|
approximate representation of a real number. The range of magnitude is approximately
|
||
|
|
2.2250738585072014 × 10<sup>-308</sup> to 1.7976931348623158
|
||
|
|
× 10<sup>308</sup>.</p>
|
||
|
|
<p>See <a href="rbafzmstlimtabs.htm#btable1">Table 77</a> for more information.</p>
|
||
|
|
<a name="wq81"></a>
|
||
|
|
<h4 id="wq81">Decimal</h4><a id="idx325" name="idx325"></a><a id="idx326" name="idx326"></a><a id="idx327" name="idx327"></a>
|
||
|
|
<p>A <span class="italic">decimal</span> value is a packed decimal or zoned decimal
|
||
|
|
number with an implicit decimal point. The position of the decimal point is
|
||
|
|
determined by the precision and the scale of the number. The scale, which
|
||
|
|
is the number of digits in the fractional part of the number, cannot be negative
|
||
|
|
or greater than the precision. The maximum precision is 63 digits.</p>
|
||
|
|
<p>All values of a decimal column have the same precision and scale. The range
|
||
|
|
of a decimal variable or the numbers in a decimal column is <span class="italic">-n</span> to +<span class="italic">n</span>, where the absolute value of <span class="italic">n</span> is the largest number that can be represented with the applicable precision
|
||
|
|
and scale.</p>
|
||
|
|
<p>The maximum range is negative 10<sup>63</sup>+1 to 10<sup>63</sup> minus 1.</p>
|
||
|
|
<a name="wq82"></a>
|
||
|
|
<h4 id="wq82">Numeric variables</h4>
|
||
|
|
<p>Small and large binary integer variables can be used in all host languages.
|
||
|
|
Big integer variables can only be used in C, C++, ILE COBOL, and ILE RPG.
|
||
|
|
Floating-point variables can be used in all host languages except RPG/400® and COBOL/400®. Decimal variables can be used in all supported host languages.</p>
|
||
|
|
<a name="numstrng"></a>
|
||
|
|
<h4 id="numstrng">String representations of numeric values</h4><a id="idx328" name="idx328"></a><a id="idx329" name="idx329"></a><a id="idx330" name="idx330"></a><a id="idx331" name="idx331"></a>
|
||
|
|
<p>When a decimal or floating-point number is cast to a string (for example,
|
||
|
|
using a CAST specification) the implicit decimal point is replaced by the
|
||
|
|
default decimal separator character in effect when the statement was prepared.
|
||
|
|
When a string is cast to a decimal or floating-point value (for example, using
|
||
|
|
a CAST specification), the default decimal separator character in effect when
|
||
|
|
the statement was prepared is used to interpret the string.</p>
|
||
|
|
<a name="charstrg"></a>
|
||
|
|
<h3 id="charstrg"><a href="rbafzmst02.htm#ToC_107">Character strings</a></h3><a id="idx332" name="idx332"></a><a id="idx333" name="idx333"></a><a id="idx334" name="idx334"></a><a id="idx335" name="idx335"></a><a id="idx336" name="idx336"></a><a id="idx337" name="idx337"></a>
|
||
|
|
<p>A <span class="italic">character string</span> is a sequence of bytes. The
|
||
|
|
length of the string is the number of bytes in the sequence. If the length
|
||
|
|
is zero, the value is called the <span class="italic">empty string</span>. The
|
||
|
|
empty string should not be confused with the null value.</p>
|
||
|
|
<a name="wq83"></a>
|
||
|
|
<h4 id="wq83">Fixed-length character strings</h4><a id="idx338" name="idx338"></a>
|
||
|
|
<p>All values of a fixed-length character-string column have the same length.
|
||
|
|
This is determined by the length attribute of the column. The length attribute
|
||
|
|
must be between 1 through 32766 inclusive.</p>
|
||
|
|
<a name="wq84"></a>
|
||
|
|
<h4 id="wq84">Varying-length character strings</h4>
|
||
|
|
<p>The types of varying-length character strings are:</p>
|
||
|
|
<ul>
|
||
|
|
<li>VARCHAR (or synonyms CHAR VARYING and CHARACTER VARYING)</li>
|
||
|
|
<li>CLOB (or synonyms CHAR LARGE OBJECT and CHARACTER LARGE OBJECT)</li></ul>
|
||
|
|
<p>The values of a column with any one of these string types can have different
|
||
|
|
lengths. The length attribute of the column determines the maximum length
|
||
|
|
a value can have.</p>
|
||
|
|
<p>For a VARCHAR column, the length attribute must be between 1 through 32740
|
||
|
|
inclusive. For a CLOB column, the length attribute must be between 1 through
|
||
|
|
2 147 483 647 inclusive. For more information about CLOBs, see <a href="rbafzmstch2data.htm#lobs">Large objects</a>.</p>
|
||
|
|
<p>For the restrictions that apply to the use of long varying-length strings,
|
||
|
|
see <a href="rbafzmstch2data.htm#limstr">Limitations on use of strings</a>.</p>
|
||
|
|
<a name="wq85"></a>
|
||
|
|
<h4 id="wq85">Character-string variables</h4><a id="idx339" name="idx339"></a><a id="idx340" name="idx340"></a><a id="idx341" name="idx341"></a><a id="idx342" name="idx342"></a><a id="idx343" name="idx343"></a><a id="idx344" name="idx344"></a><a id="idx345" name="idx345"></a>
|
||
|
|
<ul>
|
||
|
|
<li>Fixed-length character-string variables can be used in all host languages
|
||
|
|
except REXX. (In C, fixed-length character-string variables are limited to
|
||
|
|
a length of 1.)</li>
|
||
|
|
<li>VARCHAR varying-length character-string variables can be used in C, COBOL,
|
||
|
|
PL/I, REXX, and RPG:
|
||
|
|
<ul>
|
||
|
|
<li>In PL/I, REXX, and ILE RPG, there is a varying-length character-string
|
||
|
|
data type.</li>
|
||
|
|
<li>In COBOL and C, varying-length character strings are represented as structures.</li>
|
||
|
|
<li>In C, varying-length character-string variables can also be represented
|
||
|
|
by NUL-terminated strings.</li>
|
||
|
|
<li>In RPG/400, varying-length character-string variables can only be represented
|
||
|
|
by VARCHAR columns included as a result of an externally described data structure.</li></ul></li>
|
||
|
|
<li>CLOB varying-length character-string variables can be defined in all
|
||
|
|
host languages except REXX, RPG/400, and COBOL/400.
|
||
|
|
<ul>
|
||
|
|
<li>In ILE RPG, a CLOB varying-length character string is declared using the
|
||
|
|
SQLTYPE keyword.</li>
|
||
|
|
<li>In all other languages, an SQL TYPE IS CLOB clause is used.</li></ul></li></ul>
|
||
|
|
<a name="charsubtype"></a>
|
||
|
|
<h3 id="charsubtype"><a href="rbafzmst02.htm#ToC_111">Character encoding schemes</a></h3>
|
||
|
|
<p>Each character string is further defined as one of: </p>
|
||
|
|
<dl><a id="idx346" name="idx346"></a><a id="idx347" name="idx347"></a>
|
||
|
|
<dt class="bold">Bit data</dt>
|
||
|
|
<dd>Data that is not associated with a coded character set and is therefore
|
||
|
|
never converted. The CCSID for bit data is 65535.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">SBCS data</dt><a id="idx348" name="idx348"></a><a id="idx349" name="idx349"></a>
|
||
|
|
<dd>Data in which every character is represented by a single byte. Each
|
||
|
|
SBCS data character string has an associated CCSID. If necessary, an SBCS
|
||
|
|
string is converted before it is used in an operation with a character string
|
||
|
|
that has a different CCSID.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">Mixed data</dt><a id="idx350" name="idx350"></a><a id="idx351" name="idx351"></a>
|
||
|
|
<dd>Data that may contain a mixture of characters from a single-byte character
|
||
|
|
set (SBCS) and a double-byte character set (DBCS). Each mixed string has
|
||
|
|
an associated CCSID. If necessary, a mixed data character string is converted
|
||
|
|
before an operation with a character string that has a different CCSID. If
|
||
|
|
mixed data contains a DBCS character, it cannot be converted to SBCS data.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">Unicode data</dt><a id="idx352" name="idx352"></a>
|
||
|
|
<dd>Data that contains characters represented by one or more bytes. Each
|
||
|
|
Unicode character string is encoded using UTF-8. The CCSID for UTF-8 is 1208.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<p>The database manager does not recognize subclasses of double-byte characters,
|
||
|
|
and it does not assign any specific meaning to particular double-byte codes.
|
||
|
|
However, if you choose to use mixed data, then two single-byte EBCDIC codes
|
||
|
|
are given special meanings: </p>
|
||
|
|
<ul>
|
||
|
|
<li><span class="hex">X'0E'</span>, the "shift-out" character, is used to mark the
|
||
|
|
beginning of a sequence of double-byte codes.</li>
|
||
|
|
<li><span class="hex">X'0F'</span>, the "shift-in" character, is used to mark the end
|
||
|
|
of a sequence of double-byte codes.</li></ul>
|
||
|
|
<p>In order for the database manager to recognize double-byte characters in
|
||
|
|
a mixed data character string, the following condition must be met: </p>
|
||
|
|
<ul class="simple">
|
||
|
|
<li>Within the string, the double-byte characters must be enclosed between
|
||
|
|
paired shift-out and shift-in characters.
|
||
|
|
<p>The pairing is detected as the
|
||
|
|
string is read from left to right. The code <span class="hex">X'0E'</span> is recognized as
|
||
|
|
a shift out character if <span class="hex">X'0F'</span> occurs later; otherwise, it is invalid.
|
||
|
|
The first <span class="hex">X'0F'</span> following the <span class="hex">X'0E'</span> that is on a double-byte
|
||
|
|
boundary is the paired shift-in character. Any <span class="hex">X'0F'</span> that is not on
|
||
|
|
a double-byte boundary is not recognized.</p>
|
||
|
|
<p>There must be an even number
|
||
|
|
of bytes between the paired characters, and each pair of bytes is considered
|
||
|
|
to be a double-byte character. There can be more than one set of paired shift-out
|
||
|
|
and shift-in characters in the string.</p></li></ul>
|
||
|
|
<p>The length of a mixed data character string is its total number of bytes,
|
||
|
|
counting two bytes for each double-byte character and one byte for each shift-out
|
||
|
|
or shift-in character.</p>
|
||
|
|
<p>When the job CCSID indicates that DBCS is allowed, CREATE TABLE will create
|
||
|
|
character columns as DBCS-Open fields, unless FOR BIT DATA, FOR SBCS DATA,
|
||
|
|
or an SBCS CCSID is specified. The SQL user will see these as character fields,
|
||
|
|
but the system database support will see them as DBCS-Open fields. For a definition
|
||
|
|
of a DBCS-Open field, see the <a href="../dbp/rbafokickoff.htm">Database Programming</a> book.</p>
|
||
|
|
<a name="graphicstrg"></a>
|
||
|
|
<h3 id="graphicstrg"><a href="rbafzmst02.htm#ToC_112">Graphic strings</a></h3><a id="idx353" name="idx353"></a>
|
||
|
|
<p>A <var class="pv">graphic string</var> is a sequence of two-byte characters. The length
|
||
|
|
of the string is the number of its characters. Like character strings, graphic
|
||
|
|
strings can be empty.</p>
|
||
|
|
<a name="wq86"></a>
|
||
|
|
<h4 id="wq86">Fixed-length graphic strings</h4>
|
||
|
|
<p>All values of a fixed-length graphic-string column have the same length,
|
||
|
|
which is determined by the length attribute of the column. The length attribute
|
||
|
|
must be between 1 through 16383 inclusive.</p>
|
||
|
|
<a name="wq87"></a>
|
||
|
|
<h4 id="wq87">Varying-length graphic strings</h4>
|
||
|
|
<p>The types of varying-length graphic strings are:</p>
|
||
|
|
<ul>
|
||
|
|
<li>VARGRAPHIC (or synonym GRAPHIC VARYING)</li>
|
||
|
|
<li>DBCLOB</li></ul>
|
||
|
|
<p>The values of a column with any one of these string types can have different
|
||
|
|
lengths. The length attribute of the column determines the maximum length
|
||
|
|
a value can have.</p>
|
||
|
|
<p>For a VARGRAPHIC column, the length attribute must be between 1 through
|
||
|
|
16370 inclusive. For a DBCLOB column, the length attribute must be between
|
||
|
|
1 through 1 073 741 823 inclusive. For more information about DBCLOBs, see <a href="rbafzmstch2data.htm#lobs">Large objects</a>.</p>
|
||
|
|
<p>For the restrictions that apply to the use of long varying-length strings,
|
||
|
|
see <a href="rbafzmstch2data.htm#limstr">Limitations on use of strings</a>.</p>
|
||
|
|
<a name="wq88"></a>
|
||
|
|
<h4 id="wq88">Graphic-string variables</h4><a id="idx354" name="idx354"></a>
|
||
|
|
<ul>
|
||
|
|
<li>Fixed-length graphic-string variables can be defined in C, ILE COBOL,
|
||
|
|
and ILE RPG. (In C, fixed-length graphic-string variables are limited to a
|
||
|
|
length of 1.)
|
||
|
|
<p>Although fixed-length graphic-string variables cannot be
|
||
|
|
defined in PL/I, COBOL/400, and RPG/400, a character-string variable will
|
||
|
|
be treated like a fixed-length graphic-string variable if it was generated
|
||
|
|
in the source from a GRAPHIC column in the external definition of a file.</p></li>
|
||
|
|
<li>VARGRAPHIC varying-length graphic-string variables can be defined in C,
|
||
|
|
ILE COBOL, REXX, and ILE RPG.
|
||
|
|
<ul>
|
||
|
|
<li>In REXX and ILE RPG, there is a varying-length graphic-string data type.</li>
|
||
|
|
<li>In C and ILE COBOL, varying-length graphic strings are represented as
|
||
|
|
structures.</li>
|
||
|
|
<li>In C, varying-length graphic-string variables can also be represented
|
||
|
|
by NUL-terminated graphic strings.</li>
|
||
|
|
<li>Although varying-length graphic-string variables cannot be defined in
|
||
|
|
PL/I, COBOL/400, and RPG/400, a character-string variable will be treated like
|
||
|
|
a varying-length graphic-string variable if it was generated in the source
|
||
|
|
from a VARGRAPHIC column in the external definition of a file.</li></ul></li>
|
||
|
|
<li>DBCLOB varying-length character-string variables can be defined in all
|
||
|
|
host languages except REXX, RPG/400, and COBOL/400.
|
||
|
|
<ul>
|
||
|
|
<li>In ILE RPG, a DBCLOB varying-length character string is declared using
|
||
|
|
the SQLTYPE keyword.</li>
|
||
|
|
<li>In all other languages, an SQL TYPE IS DBCLOB clause is used.</li></ul></li></ul>
|
||
|
|
<a name="graphicsubtype"></a>
|
||
|
|
<h3 id="graphicsubtype"><a href="rbafzmst02.htm#ToC_116">Graphic encoding schemes</a></h3>
|
||
|
|
<p>Each graphic string is further defined as one of:</p>
|
||
|
|
<dl>
|
||
|
|
<dt class="bold">DBCS data</dt><a id="idx355" name="idx355"></a>
|
||
|
|
<dd>Data in which every character is represented by a character from the
|
||
|
|
double-byte character set (DBCS) that does not include the shift-out or shift-in
|
||
|
|
characters.
|
||
|
|
<p>Every DBCS graphic string has a CCSID that identifies a double-byte
|
||
|
|
coded character set. If necessary, a DBCS graphic string is converted before
|
||
|
|
it is used in an operation with a DBCS graphic string that has a different
|
||
|
|
DBCS CCSID.</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">Unicode data</dt>
|
||
|
|
<dd>Data that contains characters represented by two or more bytes. Each
|
||
|
|
Unicode graphic string is encoded using either UCS-2 or UTF-16. UCS-2 is a
|
||
|
|
subset of UTF-16.<a id="idx356" name="idx356"></a><a id="idx357" name="idx357"></a> The CCSID for
|
||
|
|
UCS-2 is 13488. The CCSID for UTF–16 is 1200.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<p>When graphic-string variables are not explicitly tagged with a CCSID, the
|
||
|
|
associated DBCS CCSID for the job CCSID is used. If no associated DBCS CCSID
|
||
|
|
exists, the variable is tagged with 65535. A graphic-string variable is never
|
||
|
|
implicitly tagged with a UTF-16 or UCS-2 CCSID. See the DECLARE VARIABLE statement
|
||
|
|
for information on how to tag a graphic variable with a CCSID.</p>
|
||
|
|
<a name="binarystrg"></a>
|
||
|
|
<h3 id="binarystrg"><a href="rbafzmst02.htm#ToC_117">Binary strings</a></h3><a id="idx358" name="idx358"></a><a id="idx359" name="idx359"></a><a id="idx360" name="idx360"></a><a id="idx361" name="idx361"></a><a id="idx362" name="idx362"></a><a id="idx363" name="idx363"></a>
|
||
|
|
<p>A <span class="italic">binary string</span> is a sequence of bytes. The length
|
||
|
|
of a binary string is the number of bytes in the sequence. A binary string
|
||
|
|
has a CCSID of 65535.</p>
|
||
|
|
<a name="wq89"></a>
|
||
|
|
<h4 id="wq89">Fixed-length binary strings</h4><a id="idx364" name="idx364"></a>
|
||
|
|
<p>All values of a fixed-length binary-string column have the same length.
|
||
|
|
This is determined by the length attribute of the column. The length attribute
|
||
|
|
must be between 1 through 32766 inclusive.</p>
|
||
|
|
<a name="wq90"></a>
|
||
|
|
<h4 id="wq90">Varying-length binary strings</h4>
|
||
|
|
<p>The types of varying-length binary strings are:</p>
|
||
|
|
<ul>
|
||
|
|
<li>VARBINARY (or synonym BINARY VARYING)</li>
|
||
|
|
<li>BLOB (or synonym BINARY LARGE OBJECT)</li></ul>
|
||
|
|
<p>The values of a column with any one of these string types can have different
|
||
|
|
lengths. The length attribute of the column determines the maximum length
|
||
|
|
a value can have.</p>
|
||
|
|
<p>For a VARBINARY column, the length attribute must be between 1 through
|
||
|
|
32740 inclusive. For a BLOB column, the length attribute must be between 1
|
||
|
|
through 2 147 483 647 bytes inclusive. For more information about
|
||
|
|
BLOBs, see <a href="rbafzmstch2data.htm#lobs">Large objects</a>.</p>
|
||
|
|
<a name="wq91"></a>
|
||
|
|
<h4 id="wq91">Binary-string variables</h4>
|
||
|
|
<p>A variable with a binary string type can be defined in all host languages
|
||
|
|
except REXX, RPG/400, and COBOL/400.</p>
|
||
|
|
<ul>
|
||
|
|
<li>BINARY fixed-length binary-string variables can be defined in all host
|
||
|
|
languages except REXX, RPG/400, and COBOL/400.
|
||
|
|
<ul>
|
||
|
|
<li>In ILE RPG, a BINARY fixed-length binary-string variable is declared using
|
||
|
|
the SQLTYPE keyword.</li>
|
||
|
|
<li>In all other languages, an SQL TYPE IS BINARY clause is used.</li></ul></li>
|
||
|
|
<li>VARBINARY varying-length binary-string variables can be defined in all
|
||
|
|
host languages except REXX, RPG/400, and COBOL/400.
|
||
|
|
<ul>
|
||
|
|
<li>In ILE RPG, a VARBINARY varying-length binary-string variable is declared
|
||
|
|
using the SQLTYPE keyword.</li>
|
||
|
|
<li>In all other languages, an SQL TYPE IS VARBINARY clause is used.</li></ul></li>
|
||
|
|
<li>BLOB varying-length binary-string variables can be defined in all host
|
||
|
|
languages except REXX, RPG/400, and COBOL/400.
|
||
|
|
<ul>
|
||
|
|
<li>In ILE RPG, a BLOB varying-length binary-string variable is declared using
|
||
|
|
the SQLTYPE keyword.</li>
|
||
|
|
<li>In all other languages, an SQL TYPE IS BLOB clause is used.</li></ul></li></ul>
|
||
|
|
<p>Although binary strings and FOR BIT DATA character strings might be used
|
||
|
|
for similar purposes, the two data types are not compatible. The BINARY, BLOB,
|
||
|
|
and VARBINARY functions can be used to change a FOR BIT DATA character string
|
||
|
|
into a binary string.</p>
|
||
|
|
<a name="lobs"></a>
|
||
|
|
<h3 id="lobs"><a href="rbafzmst02.htm#ToC_121">Large objects</a></h3><a id="idx365" name="idx365"></a><a id="idx366" name="idx366"></a><a id="idx367" name="idx367"></a><a id="idx368" name="idx368"></a><a id="idx369" name="idx369"></a><a id="idx370" name="idx370"></a>
|
||
|
|
<p>The term <span class="italic">large object</span> and the generic acronym <span class="italic">LOB</span> are used to refer to any CLOB, DBCLOB, or BLOB data
|
||
|
|
type.</p>
|
||
|
|
<a name="mlobloc"></a>
|
||
|
|
<h4 id="mlobloc">Manipulating large objects with locators</h4><a id="idx371" name="idx371"></a><a id="idx372" name="idx372"></a><a id="idx373" name="idx373"></a>
|
||
|
|
<p>Since LOB values can be very large, the transfer of these values from the
|
||
|
|
database server to client application program variables can be time consuming.
|
||
|
|
Also, application programs typically process LOB values a piece at a time,
|
||
|
|
rather than as a whole. For these cases, the application can reference
|
||
|
|
a LOB value via a large object locator (LOB locator). <sup class="fn"><a id="wq92" name="wq92" href="rbafzmstch2data.htm#wq93">19</a></sup></p>
|
||
|
|
<p>A <span class="italic">large object locator</span> or LOB locator is a variable
|
||
|
|
with a value that represents a single LOB value in the database server. LOB
|
||
|
|
locators were developed to provide users with a mechanism by which they could
|
||
|
|
easily manipulate very large objects in application programs without requiring
|
||
|
|
them to store the entire LOB value on the client machine where the application
|
||
|
|
program may be running.</p>
|
||
|
|
<p>For example, when selecting a LOB value, an application program
|
||
|
|
could select the entire LOB value and place it into an equally large variable
|
||
|
|
(which is acceptable if the application program is going to process the entire
|
||
|
|
LOB value at once), or it could instead select the LOB value into a LOB locator.
|
||
|
|
Then, using the LOB locator, the application program can issue subsequent
|
||
|
|
database operations on the LOB value by supplying the locator value as input.
|
||
|
|
The resulting output of the locator operation, for example the amount of data
|
||
|
|
assigned to a client variable, would then typically be a small subset of the
|
||
|
|
input LOB value.</p>
|
||
|
|
<p>LOB locators may also represent more than just base values; they can also
|
||
|
|
represent the value associated with a LOB expression. For example, a LOB
|
||
|
|
locator might represent the value associated with: </p>
|
||
|
|
<pre class="xmp"> <span class="bold">SUBSTR</span>(lob_value_1 <span class="bold">CONCAT</span> lob_value_2 <span class="bold">CONCAT</span> lob_value_3, 42, 6000000)</pre>
|
||
|
|
<p>For non-locator-based host variables in an application program,
|
||
|
|
when a null value is selected into that host variable, the indicator variable
|
||
|
|
is set to -1, signifying that the value is null. In the case of LOB locators,
|
||
|
|
however, the meaning of indicator variables is slightly different. Since a
|
||
|
|
LOB locator host variable itself can never be null, a negative indicator variable
|
||
|
|
value indicates that the LOB value represented by the LOB locator is null.
|
||
|
|
The null information is kept local to the client by virtue of the indicator
|
||
|
|
variable value -- the server does not track null values with valid LOB locators.</p>
|
||
|
|
<p>It is important to understand that a LOB locator represents a value, not
|
||
|
|
a row or location in the database. Once a value is selected into a LOB locator,
|
||
|
|
there is no operation that one can perform on the original row or table that
|
||
|
|
will affect the value which is referenced by the LOB locator. The value associated
|
||
|
|
with a LOB locator is valid until the transaction ends, or until the LOB locator
|
||
|
|
is explicitly freed, whichever comes first.</p>
|
||
|
|
<p>A LOB locator is only a mechanism used to refer to a LOB value during a
|
||
|
|
transaction; it does not persist beyond the transaction in which it was created.
|
||
|
|
Also, it is not a database type; it is never stored in the database and, as
|
||
|
|
a result, cannot participate in views or check constraints. However, since
|
||
|
|
a locator is a representation of a LOB type, there are SQLTYPEs for LOB locators
|
||
|
|
so that they can be described within an SQLDA structure that is used by FETCH,
|
||
|
|
OPEN, CALL, and EXECUTE statements.</p>
|
||
|
|
<p>For the restrictions that apply to the use of LOB strings, see <a href="rbafzmstch2data.htm#limstr">Limitations on use of strings</a>.</p>
|
||
|
|
<a name="limstr"></a>
|
||
|
|
<h3 id="limstr"><a href="rbafzmst02.htm#ToC_123">Limitations on use of strings</a></h3><a id="idx374" name="idx374"></a>
|
||
|
|
<p>The following varying-length string data types cannot be referenced in
|
||
|
|
certain contexts: </p>
|
||
|
|
<ul>
|
||
|
|
<li>for character strings, any CLOB string</li>
|
||
|
|
<li>for graphic strings, any DBCLOB string</li>
|
||
|
|
<li>for binary strings, any BLOB string.</li></ul><p class="indatacontent"> </p>
|
||
|
|
<a name="wq94"></a>
|
||
|
|
<table id="wq94" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
||
|
|
<caption>Table 4. Contexts for limitations on use of varying-length strings</caption>
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq95" width="50%" align="left" valign="top">Context of usage</th>
|
||
|
|
<th id="wq96" width="50%" align="left" valign="top">LOB (CLOB, DBCLOB, or BLOB)</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td valign="top" headers="wq95">A GROUP BY clause</td>
|
||
|
|
<td valign="top" headers="wq96">Not allowed</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td valign="top" headers="wq95">An ORDER BY clause</td>
|
||
|
|
<td valign="top" headers="wq96">Not allowed</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td valign="top" headers="wq95">A CREATE INDEX statement</td>
|
||
|
|
<td valign="top" headers="wq96">Not allowed</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td valign="top" headers="wq95">A SELECT DISTINCT statement</td>
|
||
|
|
<td valign="top" headers="wq96">Not allowed</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td valign="top" headers="wq95">A subselect of a UNION, EXCEPT, or INTERSECT without the
|
||
|
|
ALL keyword</td>
|
||
|
|
<td valign="top" headers="wq96">Not allowed</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td valign="top" headers="wq95">The definition of primary, unique, and foreign keys</td>
|
||
|
|
<td valign="top" headers="wq96">Not allowed</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td valign="top" headers="wq95">Parameters of built-in functions</td>
|
||
|
|
<td valign="top" headers="wq96">Some functions that allow varying-length character strings,
|
||
|
|
varying-length graphic strings, or both types of strings as input arguments
|
||
|
|
do not support CLOB or DBCLOB strings, or both as input. See the description
|
||
|
|
of the individual functions in <a href="rbafzmstch2func.htm#ch2func">Built-in functions</a> for the data types that
|
||
|
|
are allowed as input to each function.</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
<a name="ch2datetime"></a>
|
||
|
|
<h3 id="ch2datetime"><a href="rbafzmst02.htm#ToC_124">Datetime values</a></h3><a id="idx375" name="idx375"></a><a id="idx376" name="idx376"></a><a id="idx377" name="idx377"></a>
|
||
|
|
<p>Although datetime values can be used in certain arithmetic and string operations
|
||
|
|
and are compatible with certain strings, they are neither strings nor numbers.
|
||
|
|
However, strings can represent datetime values; see <a href="rbafzmstch2data.htm#dtstrng">String representations of datetime values</a>.</p>
|
||
|
|
<a name="wq97"></a>
|
||
|
|
<h4 id="wq97">Date</h4><a id="idx378" name="idx378"></a>
|
||
|
|
<p>A <span class="italic">date</span> is a three-part value (year, month, and day) designating a point
|
||
|
|
in time under the Gregorian calendar<sup class="fn"><a href="rbafzmstch2data.htm#fn11">20</a></sup>, which is assumed
|
||
|
|
to have been in effect from the year 1 A.D. The range of the year part is
|
||
|
|
0001 to 9999. The date formats *JUL, *MDY, *DMY, and *YMD can only represent
|
||
|
|
dates in the range 1940 through 2039. The range of the month part is 1 to
|
||
|
|
12. The range of the day part is 1 to <var class="pv">x</var>, where <var class="pv">x</var> is 28,
|
||
|
|
29, 30, or 31, depending on the month and year.</p>
|
||
|
|
<p>The internal representation of a date is a string of 4 bytes that contains
|
||
|
|
an integer. The integer (called the Scaliger number) represents the date.</p>
|
||
|
|
<p>The length of a DATE column as described in the SQLDA is 6, 8, or 10 bytes,
|
||
|
|
depending on which format is used. These are the appropriate lengths for string
|
||
|
|
representations for the value.</p>
|
||
|
|
<a name="wq98"></a>
|
||
|
|
<h4 id="wq98">Time</h4><a id="idx379" name="idx379"></a>
|
||
|
|
<p>A <span class="italic">time</span> is a three-part value (hour, minute, and
|
||
|
|
second) designating a time of day using a 24-hour clock. The range of the
|
||
|
|
hour part is 0 to 24, while the range of the minute and second parts is 0
|
||
|
|
to 59. If the hour is 24, the minute and second specifications are both zero.</p>
|
||
|
|
<p>The internal representation of a time is a string of 3 bytes. Each byte
|
||
|
|
consists of two packed decimal digits. The first byte represents the hour,
|
||
|
|
the second byte the minute, and the last byte the second.</p>
|
||
|
|
<p>The length of a TIME column as described in the SQLDA is 8 bytes, which
|
||
|
|
is the appropriate length for a string representation of the value.</p>
|
||
|
|
<a name="wq99"></a>
|
||
|
|
<h4 id="wq99">Timestamp</h4><a id="idx380" name="idx380"></a>
|
||
|
|
<p>A <span class="italic">timestamp</span> is a seven-part value (year, month,
|
||
|
|
day, hour, minute, second, and microsecond) that designates a date and time
|
||
|
|
as defined previously, except that the time includes a fractional specification
|
||
|
|
of microseconds.</p>
|
||
|
|
<p>The internal representation of a timestamp is a string of 10 bytes. The
|
||
|
|
first 4 bytes represent the date, the next 3 bytes the time, and the last
|
||
|
|
3 bytes the microseconds (the last 3 bytes contain 6 packed digits).</p>
|
||
|
|
<p>The length of a TIMESTAMP column as described in the SQLDA is 26 bytes,
|
||
|
|
which is the appropriate length for the string representation of the value.</p>
|
||
|
|
<a name="wq100"></a>
|
||
|
|
<h4 id="wq100">Datetime variables</h4>
|
||
|
|
<p>Character string variables are normally used to contain date, time, and
|
||
|
|
timestamp values. However, date, time, and timestamp variables can also be
|
||
|
|
specified in ILE COBOL and ILE RPG. Date, time, and timestamp variables can
|
||
|
|
also be specified in Java as java.sql.Date, java.sql.Time, and
|
||
|
|
java.sql.Timestamp respectively.</p>
|
||
|
|
<a name="dtstrng"></a>
|
||
|
|
<h4 id="dtstrng">String representations of datetime values</h4><a id="idx381" name="idx381"></a><a id="idx382" name="idx382"></a><a id="idx383" name="idx383"></a>
|
||
|
|
<p>Values whose data types are DATE, TIME, or TIMESTAMP are represented in
|
||
|
|
an internal form that is transparent to the user of SQL. Dates, times, and
|
||
|
|
timestamps, however, can also be represented by character or UTF-16 or UCS-2
|
||
|
|
graphic strings. Only ILE RPG and ILE COBOL support datetime variables. To be
|
||
|
|
retrieved, a datetime value can be assigned to a string variable. The format
|
||
|
|
of the resulting string will depend on the default date format and the default
|
||
|
|
time format in effect when the statement was prepared. The default date and
|
||
|
|
time formats are set based on the date format (DATFMT), the date separator
|
||
|
|
(DATSEP), the time format (TIMFMT), and the time separator (TIMSEP) parameters.</p>
|
||
|
|
<p>When a valid string representation of a datetime value is used in an operation
|
||
|
|
with an internal datetime value, the string representation is converted to
|
||
|
|
the internal form of the date, time, or timestamp before the operation is
|
||
|
|
performed. If the CCSID of the string represents a foreign encoding scheme
|
||
|
|
(for example, ASCII), it is first converted to the coded character set identified
|
||
|
|
by the default CCSID before the string is converted to the internal form of
|
||
|
|
the datetime value.</p>
|
||
|
|
<p>The following sections define the valid string representations of datetime
|
||
|
|
values.</p>
|
||
|
|
<a name="wq101"></a>
|
||
|
|
<h5 id="wq101">Date strings</h5>
|
||
|
|
<p>A string representation of a date is a character or a UCS-2
|
||
|
|
or UTF-16 graphic string that starts with a digit and has a length of at least
|
||
|
|
6 characters. Trailing blanks can be included. Leading zeros can be omitted
|
||
|
|
from the month and day portions when using the IBM® SQL standard formats. Each IBM SQL standard
|
||
|
|
format is identified by name and includes an associated abbreviation (for
|
||
|
|
use by the CHAR function). Other formats do not have an abbreviation to be
|
||
|
|
used by the CHAR function. The separators for two-digit year formats are
|
||
|
|
controlled by the date separator (DATSEP) parameter. Valid string formats
|
||
|
|
for dates are listed in <a href="rbafzmstch2data.htm#datstt1">Table 5</a>.</p>
|
||
|
|
<p>The database manager recognizes the string as a date when it is either: </p>
|
||
|
|
<ul>
|
||
|
|
<li>In the format specified by the default date format, or</li>
|
||
|
|
<li>In the ANSI/ISO SQL standard date format, or</li>
|
||
|
|
<li>In one of the IBM SQL standard date formats, or</li>
|
||
|
|
<li>In the unformatted Julian format</li></ul><a id="idx384" name="idx384"></a><a id="idx385" name="idx385"></a><a id="idx386" name="idx386"></a><a id="idx387" name="idx387"></a><a id="idx388" name="idx388"></a><a id="idx389" name="idx389"></a><a id="idx390" name="idx390"></a><a id="idx391" name="idx391"></a><a id="idx392" name="idx392"></a><a id="idx393" name="idx393"></a><a id="idx394" name="idx394"></a><a id="idx395" name="idx395"></a><a id="idx396" name="idx396"></a><a id="idx397" name="idx397"></a><a id="idx398" name="idx398"></a><a id="idx399" name="idx399"></a><a id="idx400" name="idx400"></a><a id="idx401" name="idx401"></a><a id="idx402" name="idx402"></a><a id="idx403" name="idx403"></a>
|
||
|
|
<a name="datstt1"></a>
|
||
|
|
<table id="datstt1" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
||
|
|
<caption>Table 5. Formats for String Representations of Dates</caption>
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq102" width="40%" align="left" valign="bottom">Format Name</th>
|
||
|
|
<th id="wq103" width="20%" align="left" valign="bottom">Abbreviation</th>
|
||
|
|
<th id="wq104" width="20%" align="left" valign="bottom">Date Format</th>
|
||
|
|
<th id="wq105" width="20%" align="left" valign="bottom">Example</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">ANSI/ISO SQL standard date format (–)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">DATE 'yyyy-mm-dd'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">DATE '1987-10-12'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">International Standards Organization (*ISO)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">ISO</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'yyyy-mm-dd'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'1987-10-12'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">IBM USA standard (*USA)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">USA</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'mm/dd/yyyy'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'10/12/1987'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">IBM European standard (*EUR)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">EUR</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'dd.mm.yyyy'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'12.10.1987'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">Japanese industrial standard Christian era
|
||
|
|
(*JIS)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">JIS</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'yyyy-mm-dd'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'1987-10-12'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">Unformatted Julian</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'yyyyddd'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'1987285'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">Julian (*JUL)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'yy/ddd'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'87/285'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">Month, day, year (*MDY)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'mm/dd/yy'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'10/12/87'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">Day, month, year (*DMY)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'dd/mm/yy'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'12/10/87'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq102">Year, month, day (*YMD)</td>
|
||
|
|
<td align="left" valign="top" headers="wq103">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq104">'yy/mm/dd'</td>
|
||
|
|
<td align="left" valign="top" headers="wq105">'87/12/10'</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
<p>The default date format can be specified through the following interfaces:
|
||
|
|
<a id="idx404" name="idx404"></a><a id="idx405" name="idx405"></a></p>
|
||
|
|
<a name="dftcol1a"></a>
|
||
|
|
<table id="dftcol1a" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
||
|
|
<caption>Table 6. Default Date Format Interfaces</caption>
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq106" width="40%" align="left" valign="bottom">SQL Interface</th>
|
||
|
|
<th id="wq107" width="60%" align="left" valign="bottom">Specification</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq106">Embedded SQL</td>
|
||
|
|
<td align="left" valign="top" headers="wq107">The DATFMT and DATSEP parameters are specified
|
||
|
|
on the Create SQL Program (CRTSQLxxx) commands. The SET OPTION statement can
|
||
|
|
also be used to specify the DATFMT and DATSEP parameters within the source
|
||
|
|
of a program containing embedded SQL.
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information
|
||
|
|
about CRTSQLxxx commands, see the <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq106">Interactive SQL and Run SQL Statements</td>
|
||
|
|
<td align="left" valign="top" headers="wq107">The DATFMT and DATSEP parameters on the Start
|
||
|
|
SQL (STRSQL) command or by changing the session attributes. The DATFMT and
|
||
|
|
DATSEP parameters on the Run SQL Statements (RUNSQLSTM) command.
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For
|
||
|
|
more information about STRSQL and RUNSQLSTM commands, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq106">Call Level Interface (CLI) on the server</td>
|
||
|
|
<td align="left" valign="top" headers="wq107">SQL_ATTR_DATE_FMT and SQL_ATTR_DATE_SEP environment
|
||
|
|
or connection variables
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information about CLI, see
|
||
|
|
the <a href="../cli/rzadpkickoff.htm">SQL Call Level Interfaces (ODBC)</a> book.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq106">JDBC or SQLJ on the server using IBM Developer Kit for Java</td>
|
||
|
|
<td align="left" valign="top" headers="wq107">Date Format and Date Separator connection
|
||
|
|
property
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information about JDBC and SQLJ, see the <a href="../rzaha/whatitis.htm">IBM Developer Kit for Java</a> topic in the iSeries Information Center.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq106">ODBC on a client using the iSeries Access Family ODBC Driver</td>
|
||
|
|
<td align="left" valign="top" headers="wq107">Date Format and Date Separator in the Advanced
|
||
|
|
Server Options in ODBC Setup
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information about ODBC,
|
||
|
|
see the <a href="../rzahg/rzahgicia.htm">iSeries™ Access</a> category in the iSeries Information Center.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq106">JDBC on a client using the IBM Toolbox for Java</td>
|
||
|
|
<td align="left" valign="top" headers="wq107">Format in JDBC Setup
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For
|
||
|
|
more information about ODBC, see the <a href="../rzahg/rzahgicia.htm">iSeries Access</a> category
|
||
|
|
in the iSeries Information Center.)
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information about the IBM Toolbox for Java,
|
||
|
|
see <a href="../rzahh/page1.htm">IBM Toolbox for Java</a> topic in the iSeries Information Center .)</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
<a name="wq108"></a>
|
||
|
|
<h5 id="wq108">Time strings</h5>
|
||
|
|
<p>A string representation of a time is a character or a UCS-2
|
||
|
|
or UTF-16 graphic string that starts with a digit and has a length of at least
|
||
|
|
4 characters. Trailing blanks can be included; a leading zero can be omitted
|
||
|
|
from the hour part of the time and seconds can be omitted entirely. If you
|
||
|
|
choose to omit seconds, an implicit specification of 0 seconds is assumed.
|
||
|
|
Thus, 13.30 is equivalent to 13.30.00.</p>
|
||
|
|
<p>Valid string formats for times are listed in <a href="rbafzmstch2data.htm#timstt1">Table 7</a>. Each IBM SQL
|
||
|
|
standard format is identified by name and includes an associated abbreviation
|
||
|
|
(for use by the CHAR function). The other format (*HMS) does not have an abbreviation
|
||
|
|
to be used by the CHAR function. The separator for the *HMS format is controlled
|
||
|
|
by the time separator (TIMSEP) parameter.</p>
|
||
|
|
<p>The database manager recognizes the string as a time when it is either: </p>
|
||
|
|
<ul>
|
||
|
|
<li>In the format specified by the default time format, or</li>
|
||
|
|
<li>In the ANSI/ISO SQL standard time format, or</li>
|
||
|
|
<li>In one of the IBM SQL standard time formats</li></ul><a id="idx406" name="idx406"></a><a id="idx407" name="idx407"></a><a id="idx408" name="idx408"></a><a id="idx409" name="idx409"></a><a id="idx410" name="idx410"></a><a id="idx411" name="idx411"></a><a id="idx412" name="idx412"></a><a id="idx413" name="idx413"></a><a id="idx414" name="idx414"></a><a id="idx415" name="idx415"></a>
|
||
|
|
<a name="timstt1"></a>
|
||
|
|
<table id="timstt1" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
||
|
|
<caption>Table 7. Formats for String Representations of Times</caption>
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq109" width="40%" align="left" valign="bottom">Format Name</th>
|
||
|
|
<th id="wq110" width="20%" align="left" valign="bottom">Abbreviation</th>
|
||
|
|
<th id="wq111" width="20%" align="left" valign="bottom">Time Format</th>
|
||
|
|
<th id="wq112" width="20%" align="left" valign="bottom">Example</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq109">ANSI/ISO SQL standard time format (–)</td>
|
||
|
|
<td align="left" valign="top" headers="wq110">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq111">TIME 'hh:mm:ss'</td>
|
||
|
|
<td align="left" valign="top" headers="wq112">TIME '13:30:05'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq109">International Standards Organization (*ISO)</td>
|
||
|
|
<td align="left" valign="top" headers="wq110">ISO</td>
|
||
|
|
<td align="left" valign="top" headers="wq111">'hh.mm.ss' <sup class="fn"><a href="rbafzmstch2data.htm#isofm">21</a></sup></td>
|
||
|
|
<td align="left" valign="top" headers="wq112">'13.30.05'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq109">IBM USA standard (*USA)</td>
|
||
|
|
<td align="left" valign="top" headers="wq110">USA</td>
|
||
|
|
<td align="left" valign="top" headers="wq111">'hh:mm AM' (or PM)</td>
|
||
|
|
<td align="left" valign="top" headers="wq112">'1:30 PM'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq109">IBM European standard (*EUR)</td>
|
||
|
|
<td align="left" valign="top" headers="wq110">EUR</td>
|
||
|
|
<td align="left" valign="top" headers="wq111">'hh.mm.ss'</td>
|
||
|
|
<td align="left" valign="top" headers="wq112">'13.30.05'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq109">Japanese industrial standard Christian era
|
||
|
|
(*JIS)</td>
|
||
|
|
<td align="left" valign="top" headers="wq110">JIS</td>
|
||
|
|
<td align="left" valign="top" headers="wq111">'hh:mm:ss'</td>
|
||
|
|
<td align="left" valign="top" headers="wq112">'13:30:05'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq109">Hours, minutes, seconds (*HMS)</td>
|
||
|
|
<td align="left" valign="top" headers="wq110">–</td>
|
||
|
|
<td align="left" valign="top" headers="wq111">'hh:mm:ss'</td>
|
||
|
|
<td align="left" valign="top" headers="wq112">'13:30:05'</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
<p>The following additional rules apply to the USA time format:</p>
|
||
|
|
<ul>
|
||
|
|
<li>The hour must not be greater than 12 and cannot be 0 except for the special
|
||
|
|
case of 00:00 AM.</li>
|
||
|
|
<li>A single space character exists between the minutes portion of the time
|
||
|
|
of day and the AM or PM.</li>
|
||
|
|
<li>The minutes can be omitted entirely. If you choose to omit the minutes,
|
||
|
|
an implicit specification of 0 minutes is assumed.</li></ul><p class="indatacontent"> In the USA format, using the ISO format of the 24-hour clock, the correspondence
|
||
|
|
between the USA format and the 24-hour clock is as follows:</p>
|
||
|
|
<a name="wq113"></a>
|
||
|
|
<table id="wq113" width="100%" summary="" border="0" frame="void" rules="none">
|
||
|
|
<caption>Table 8. USA Time Format</caption>
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq114" width="50%" align="left" valign="top"><span class="bold">USA Format</span></th>
|
||
|
|
<th id="wq115" width="50%" align="left" valign="top"><span class="bold">24-Hour Clock</span></th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq114">12:01 AM through 12:59 AM</td>
|
||
|
|
<td align="left" valign="top" headers="wq115">00.01.00 through 00.59.00</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq114">01:00 AM through 11:59 AM</td>
|
||
|
|
<td align="left" valign="top" headers="wq115">01:00.00 through 11:59.00</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq114">12:00 PM (noon) through 11:59 PM</td>
|
||
|
|
<td align="left" valign="top" headers="wq115">12:00.00 through 23.59.00</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq114">12:00 AM (midnight)</td>
|
||
|
|
<td align="left" valign="top" headers="wq115">24.00.00</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq114">00:00 AM (midnight)</td>
|
||
|
|
<td align="left" valign="top" headers="wq115">00.00.00</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
<p>The default time format can be specified through the following interfaces:
|
||
|
|
<a id="idx416" name="idx416"></a><a id="idx417" name="idx417"></a></p>
|
||
|
|
<a name="dftcol1c"></a>
|
||
|
|
<table id="dftcol1c" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
||
|
|
<caption>Table 9. Default Time Format Interfaces</caption>
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq116" width="40%" align="left" valign="bottom">SQL Interface</th>
|
||
|
|
<th id="wq117" width="60%" align="left" valign="bottom">Specification</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq116">Embedded SQL</td>
|
||
|
|
<td align="left" valign="top" headers="wq117">The TIMFMT and TIMSEP parameters are specified
|
||
|
|
on the Create SQL Program (CRTSQLxxx) commands. The SET OPTION statement can
|
||
|
|
also be used to specify the TIMFMT and TIMSEP parameters within the source
|
||
|
|
of a program containing embedded SQL.
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information
|
||
|
|
about CRTSQLxxx commands, see the <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq116">Interactive SQL and Run SQL Statements</td>
|
||
|
|
<td align="left" valign="top" headers="wq117">The TIMFMT and TIMSEP parameters on the Start
|
||
|
|
SQL (STRSQL) command or by changing the session attributes. The TIMFMT and
|
||
|
|
TIMSEP parameters on the Run SQL Statements (RUNSQLSTM) command.
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For
|
||
|
|
more information about STRSQL and RUNSQLSTM commands, see the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq116">Call Level Interface (CLI) on the server</td>
|
||
|
|
<td align="left" valign="top" headers="wq117">SQL_ATTR_TIME_FMT and SQL_ATTR_TIME_SEP environment
|
||
|
|
or connection variables
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information about CLI, see
|
||
|
|
the <a href="../cli/rzadpkickoff.htm">SQL Call Level Interfaces (ODBC)</a> book.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq116">JDBC or SQLJ on the server using IBM Developer Kit for Java</td>
|
||
|
|
<td align="left" valign="top" headers="wq117">Time Format and Time Separator connection
|
||
|
|
property object
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information about JDBC and SQLJ, see
|
||
|
|
the <a href="../rzaha/whatitis.htm">IBM Developer Kit for Java</a> topic in the iSeries Information Center.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq116">ODBC on a client using the iSeries Access Family ODBC Driver</td>
|
||
|
|
<td align="left" valign="top" headers="wq117">Time Format and Time Separator in the Advanced
|
||
|
|
Server Options in ODBC Setup
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For more information about ODBC,
|
||
|
|
see the <a href="../rzahg/rzahgicia.htm">iSeries Access Family </a> category in the iSeries Information Center.)</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq116">JDBC on a client using the IBM Toolbox for Java</td>
|
||
|
|
<td align="left" valign="top" headers="wq117">Format in JDBC Setup
|
||
|
|
<div class="lines"><br />
|
||
|
|
</div>(For
|
||
|
|
more information about the IBM Toolbox for Java, see <a href="../rzahh/page1.htm">IBM Toolbox for Java</a> topic in the iSeries Information Center .)</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
<a name="wq118"></a>
|
||
|
|
<h5 id="wq118">Timestamp strings</h5><a id="idx418" name="idx418"></a>
|
||
|
|
<p>A string representation of a timestamp is a character or a UCS-2 or UTF-16
|
||
|
|
graphic string that starts with a digit and has a length of at least 16 characters.
|
||
|
|
The complete string representation of a timestamp has one of the following
|
||
|
|
forms:</p>
|
||
|
|
<a name="timstt2"></a>
|
||
|
|
<table id="timstt2" width="100%" summary="" border="1" frame="hsides" rules="rows">
|
||
|
|
<caption>Table 10. Formats for String Representations of Timestamps</caption>
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq119" width="19%" align="left" valign="bottom">Format Name</th>
|
||
|
|
<th id="wq120" width="40%" align="left" valign="bottom">Time Format</th>
|
||
|
|
<th id="wq121" width="40%" align="left" valign="bottom">Example</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq119">ANSI/ISO SQL standard</td>
|
||
|
|
<td align="left" valign="top" headers="wq120">TIMESTAMP 'yyyy-mm-dd hh:mm:ss.nnnnnn'</td>
|
||
|
|
<td align="left" valign="top" headers="wq121">TIMESTAMP '1990-03-02 08:30:00.010000'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq119">ISO timestamp</td>
|
||
|
|
<td align="left" valign="top" headers="wq120">'yyyy-mm-dd hh:mm:ss.nnnnnn'</td>
|
||
|
|
<td align="left" valign="top" headers="wq121">'1990-03-02 08:30:00.010000'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq119">IBM SQL</td>
|
||
|
|
<td align="left" valign="top" headers="wq120">'yyyy-mm-dd-hh.mm.ss.nnnnnn'</td>
|
||
|
|
<td align="left" valign="top" headers="wq121">'1990-03-02-08.30.00.010000'</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" valign="top" headers="wq119">14–character form</td>
|
||
|
|
<td align="left" valign="top" headers="wq120">'yyyymmddhhmmss'</td>
|
||
|
|
<td align="left" valign="top" headers="wq121">'19900302083000'</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table><p class="indatacontent">Trailing blanks can be included. Leading zeros can be omitted from
|
||
|
|
the month, day, and hour part of the timestamp when using the timestamp form
|
||
|
|
with separators. Trailing zeros can be truncated or omitted entirely from
|
||
|
|
microseconds. If you choose to omit any digit of the microseconds portion,
|
||
|
|
an implicit specification of 0 is assumed. Thus, <span class="italic">1990-3-2-8.30.00.10</span> is equivalent to <span class="italic">1990-03-02-08.30.00.100000</span>.</p>
|
||
|
|
<p>A timestamp whose time part is 24.00.00.000000 is also accepted.</p>
|
||
|
|
<a name="ch2datalink"></a>
|
||
|
|
<h3 id="ch2datalink"><a href="rbafzmst02.htm#ToC_133">DataLink values</a></h3>
|
||
|
|
<p><a id="idx419" name="idx419"></a><a id="idx420" name="idx420"></a></p>
|
||
|
|
<p>A DataLink value is an encapsulated value that contains a logical reference
|
||
|
|
from the database to a file stored outside the database. The attributes of
|
||
|
|
this encapsulated value are as follows:</p>
|
||
|
|
<dl>
|
||
|
|
<dt class="bold">link type</dt>
|
||
|
|
<dd>The currently supported type of link is a URL (Uniform Resource Locator).
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">scheme</dt>
|
||
|
|
<dd>For URLs, this is a value such as HTTP or FILE. The value, no matter
|
||
|
|
what case it is entered in, is stored in the database in upper case.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">file server name</dt>
|
||
|
|
<dd>The complete address of the file server. The value, no matter what case
|
||
|
|
it is entered in, is stored in the database in upper case.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">file path</dt>
|
||
|
|
<dd>The identity of the file within the server. The value is case sensitive
|
||
|
|
and therefore it is not converted to upper case when stored in the database.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">access control token</dt>
|
||
|
|
<dd>When appropriate, the access token is embedded within the file path.
|
||
|
|
It is generated dynamically and is not a permanent part of the DataLink value
|
||
|
|
that is stored in the database.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">comment</dt>
|
||
|
|
<dd>Up to 254 bytes of descriptive information. This is intended for application
|
||
|
|
specific uses such as further or alternative identification of the location
|
||
|
|
of the data.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<p>The characters used in a DataLink value are limited to the set defined
|
||
|
|
for a URL. These characters include the uppercase (A through Z) and lower
|
||
|
|
case (a through z) letters, the digits (0 through 9) and a subset of special
|
||
|
|
characters ($, -, _, @, ., &, +, !, *, ", ', (, ), =, ;, /, #, ?, :, space,
|
||
|
|
and comma).</p>
|
||
|
|
<p>The first four attributes are collectively known as the linkage attributes.
|
||
|
|
It is possible for a DataLink value to have only a comment attribute and no
|
||
|
|
linkage attributes. Such a value may even be stored in a column but, of course,
|
||
|
|
no file will be linked to such a column.</p>
|
||
|
|
<p>It is important to distinguish between these DataLink references to files
|
||
|
|
and the LOB file reference variables described in <a href="rbafzmstch2refvar.htm#rlobref">References to LOB file reference variables</a>.
|
||
|
|
The similarity is that they both contain a representation of a file. However: </p>
|
||
|
|
<ul>
|
||
|
|
<li>DataLinks are retained in the database and both the links and the data
|
||
|
|
in the linked files can be considered as a natural extension of data in the
|
||
|
|
database.</li>
|
||
|
|
<li>File reference variables exist temporarily and they can be considered
|
||
|
|
as an alternative to a host program buffer.</li></ul>
|
||
|
|
<p>Built-in scalar functions are provided to build a DataLink value (DLVALUE)
|
||
|
|
and to extract the encapsulated values from a DataLink value (DLCOMMENT, DLLINKTYPE,
|
||
|
|
DLURLCOMPLETE, DLURLPATH, DLURLPATHONLY, DLURLSCHEME, DLURLSERVER).</p>
|
||
|
|
<a name="ch2rowid"></a>
|
||
|
|
<h3 id="ch2rowid"><a href="rbafzmst02.htm#ToC_134">Row ID values</a></h3>
|
||
|
|
<p><a id="idx421" name="idx421"></a><a id="idx422" name="idx422"></a>A <span class="italic">row ID</span> is a value that
|
||
|
|
uniquely identifies a row in a table. A column or a variable can have a row
|
||
|
|
ID data type. A ROWID column enables queries to be written that navigate directly
|
||
|
|
to a row in the table. Each value in a ROWID column must be unique. The database manager maintains
|
||
|
|
the values permanently, even across table reorganizations. When a row is inserted
|
||
|
|
into the table, the database manager generates a value for the ROWID column unless one
|
||
|
|
is supplied. If a value is supplied, it must be a valid row ID value that
|
||
|
|
was previously generated by either DB2 UDB for z/OS or DB2 UDB for iSeries.</p>
|
||
|
|
<p>The internal representation of a row ID value is transparent to the user.
|
||
|
|
The value is never subject to CCSID conversion because it is considered to
|
||
|
|
contain BIT data. The length attribute of a ROWID column is 40.</p>
|
||
|
|
<a name="ch2udf"></a>
|
||
|
|
<h3 id="ch2udf"><a href="rbafzmst02.htm#ToC_135">User-defined types</a></h3>
|
||
|
|
<p><a id="idx423" name="idx423"></a><a id="idx424" name="idx424"></a></p>
|
||
|
|
<a name="wq122"></a>
|
||
|
|
<h4 id="wq122">Distinct types</h4><a id="idx425" name="idx425"></a><a id="idx426" name="idx426"></a>
|
||
|
|
<p>A <span class="italic">distinct type</span> is a user-defined data type that
|
||
|
|
shares its internal representation with a built-in data type (its "source
|
||
|
|
type"), but is considered to be a separate and incompatible type for most
|
||
|
|
operations. For example, the semantics for a picture type, a text type, and
|
||
|
|
an audio type that all use the built-in data type BLOB for their internal
|
||
|
|
representation are quite different. A distinct type is created using <a href="rbafzmstcrtudt.htm#crtudt">CREATE DISTINCT TYPE</a>.</p>
|
||
|
|
<p>For example, the following statement creates a distinct type named AUDIO:</p>
|
||
|
|
<pre class="xmp"> <span class="bold">CREATE DISTINCT TYPE</span> AUDIO <span class="bold">AS BLOB</span> (1M)
|
||
|
|
</pre>
|
||
|
|
<p>Although AUDIO has the same representation as the built-in data type BLOB,
|
||
|
|
it is considered to be a separate type that is not comparable to a BLOB or
|
||
|
|
to any other type. This inability to compare AUDIO to other data types allows
|
||
|
|
functions to be created specifically for AUDIO and assures that these functions
|
||
|
|
cannot be applied to other data types (such as pictures or text).</p>
|
||
|
|
<p>The name of a distinct type is qualified with a schema name. The implicit
|
||
|
|
schema name for an unqualified name depends upon the context in which the
|
||
|
|
distinct type appears. If an unqualified distinct type name is used: </p>
|
||
|
|
<ul>
|
||
|
|
<li>In a CREATE DISTINCT TYPE or the object of DROP, COMMENT, GRANT, or REVOKE
|
||
|
|
statement, the database manager uses the normal process of qualification by authorization
|
||
|
|
ID to determine the schema name. For more information about qualification
|
||
|
|
rules, see <a href="rbafzmstch2nam.htm#qualun2">Unqualified function, procedure, specific, and distinct type names</a>.</li>
|
||
|
|
<li>In any other context, the database manager uses the SQL path to determine the schema
|
||
|
|
name. The database manager searches the schemas in the path, in sequence,
|
||
|
|
and selects the first schema that has a distinct type that matches. For a
|
||
|
|
description of the SQL path, see <a href="rbafzmstspecreg.htm#curpath">CURRENT PATH</a>.</li></ul>
|
||
|
|
<p>A distinct type does not automatically acquire the functions and operators
|
||
|
|
of its source type, since these may not be meaningful. (For example, the LENGTH
|
||
|
|
function of the AUDIO type might return the length of its object in seconds
|
||
|
|
rather than in bytes.) Instead, distinct types support <span class="italic">strong typing</span>. Strong typing ensures that only the functions and operators
|
||
|
|
that are explicitly defined for a distinct type can be applied to that distinct
|
||
|
|
type. However, a function or operator of the source type can be applied to
|
||
|
|
the distinct type by creating an appropriate user-defined function. The user-defined
|
||
|
|
function must be sourced on the existing function that has the source type
|
||
|
|
as a parameter. For example, the following series of SQL statements shows
|
||
|
|
how to create a distinct type named MONEY based on data type DECIMAL(9,2),
|
||
|
|
how to define the + operator for the distinct type, and how the operator might
|
||
|
|
be applied to the distinct type: </p>
|
||
|
|
<pre class="xmp"><span class="bold">CREATE DISTINCT TYPE</span> MONEY <span class="bold">AS DECIMAL(9,2) WITH COMPARISONS</span>
|
||
|
|
<span class="bold">CREATE FUNCTION</span> "+"(MONEY,MONEY)
|
||
|
|
<span class="bold">RETURNS</span> MONEY
|
||
|
|
<span class="bold">SOURCE</span> "+"(DECIMAL(9,2),DECIMAL(9,2))
|
||
|
|
<span class="bold">CREATE TABLE</span> SALARY_TABLE
|
||
|
|
(SALARY MONEY,
|
||
|
|
COMMISSION MONEY)
|
||
|
|
<span class="bold">SELECT</span> "+"(SALARY, COMMISSION) <span class="bold">FROM</span> SALARY_TABLE</pre>
|
||
|
|
<p>A distinct type is subject to the same restrictions as its source type.
|
||
|
|
For example, a table can only have one ROWID column. Therefore, a table with
|
||
|
|
a ROWID column cannot also have a column with distinct type that is sourced
|
||
|
|
on a row ID.</p>
|
||
|
|
<p>The comparison operators are automatically generated for distinct types,
|
||
|
|
except for distinct types that are sourced on a DataLink. In addition, the database manager automatically
|
||
|
|
generates functions for a distinct type that support casting from the source
|
||
|
|
type to the distinct type and from the distinct type to the source type. For
|
||
|
|
example, for the AUDIO type created above, these are the generated cast functions:
|
||
|
|
</p>
|
||
|
|
<a name="wq123"></a>
|
||
|
|
<table id="wq123" width="100%" summary="" border="0" frame="void" rules="none">
|
||
|
|
<thead valign="bottom">
|
||
|
|
<tr>
|
||
|
|
<th id="wq124" width="33%" align="left">Name of generated cast function</th>
|
||
|
|
<th id="wq125" width="33%" align="left">Parameter list</th>
|
||
|
|
<th id="wq126" width="33%" align="left">Returns data type</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody valign="top">
|
||
|
|
<tr>
|
||
|
|
<td headers="wq124">schema-name.BLOB</td>
|
||
|
|
<td headers="wq125">schema-name.AUDIO</td>
|
||
|
|
<td headers="wq126">BLOB</td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td headers="wq124">schema-name.AUDIO</td>
|
||
|
|
<td headers="wq125">BLOB</td>
|
||
|
|
<td headers="wq126">schema-name.AUDIO</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
<hr /><div class="fnnum"><a id="wq93" name="wq93" href="rbafzmstch2data.htm#wq92">19</a>.</div>
|
||
|
|
<div class="fntext">There is no ability
|
||
|
|
within a Java™ application to distinguish between a CLOB or BLOB
|
||
|
|
that is represented by a LOB locator and one that is not.</div><div class="fnnum"><a id="fn11" name="fn11">20</a>.</div>
|
||
|
|
<div class="fntext">Note that historical dates do not always follow the Gregorian
|
||
|
|
calendar. Dates between 1582-10-04 and 1582-10-15 are accepted as valid dates
|
||
|
|
although they never existed in the Gregorian calendar.</div><div class="fnnum"><a id="isofm" name="isofm">21</a>.</div>
|
||
|
|
<div class="fntext">This is an earlier version of the ISO format. JIS can be used
|
||
|
|
to get the current ISO format.</div>
|
||
|
|
<br />
|
||
|
|
<hr /><br />
|
||
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstch2auth.htm">Previous Page</a> | <a href="rbafzmstpromo.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>
|