393 lines
23 KiB
HTML
393 lines
23 KiB
HTML
|
<?xml version="1.0" encoding="utf-8"?>
|
||
|
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
|
||
|
|
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="dc.language" scheme="rfc1766" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow"/>
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="keywords" content="CREATE SEQUENCE statement, CREATE SEQUENCE TYPE,
|
||
|
|
SQL statements, in CREATE SEQUENCE statement, sequence-name, built-in data type,
|
||
|
|
data type for CREATE SEQUENCE, distinct-type, START WITH clause,
|
||
|
|
INCREMENT BY clause, MINVALUE clause, MAXVALUE clause, CYCLE clause,
|
||
|
|
CACHE clause, ORDER clause" />
|
||
|
|
<title>CREATE SEQUENCE</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="csequence"></a>
|
||
|
|
<h2 id="csequence"><a href="rbafzmst02.htm#ToC_918">CREATE SEQUENCE</a></h2><a id="idx2034" name="idx2034"></a><a id="idx2035" name="idx2035"></a>
|
||
|
|
<p>The CREATE SEQUENCE statement creates a sequence at the application server.</p>
|
||
|
|
<a name="wq1292"></a>
|
||
|
|
<h3 id="wq1292"><a href="rbafzmst02.htm#ToC_919">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="wq1293"></a>
|
||
|
|
<h3 id="wq1293"><a href="rbafzmst02.htm#ToC_920">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>The following system authorities:
|
||
|
|
<ul>
|
||
|
|
<li>*USE to the Create Data Area (CRTDTAARA) command</li></ul></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 SYSSEQOBJECTS 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 a distinct type is referenced, the privileges held by the authorization ID of
|
||
|
|
the statement must include at least one of the following: </p>
|
||
|
|
<ul>
|
||
|
|
<li>For the distinct type identified in the statement:
|
||
|
|
<ul>
|
||
|
|
<li>The USAGE privilege on the distinct type, and</li>
|
||
|
|
<li>The system authority *EXECUTE on the library containing the distinct type</li></ul></li>
|
||
|
|
<li>Administrative authority</li></ul>
|
||
|
|
<p>For information on the system authorities corresponding to SQL privileges,
|
||
|
|
see <a href="rbafzmstgntseqp.htm#eqtables">Corresponding System Authorities When Checking Privileges to a Sequence</a> and <a href="rbafzmstgntudtp.htm#eqtabled">Corresponding System Authorities When Checking Privileges to a Distinct Type</a>.</p>
|
||
|
|
<a name="wq1294"></a>
|
||
|
|
<h3 id="wq1294"><a href="rbafzmst02.htm#ToC_921">Syntax</a></h3>
|
||
|
|
<a href="rbafzmstcsequence.htm#synscsequence"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
||
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn301.htm"
|
||
|
|
border="0" /></span><a href="#skipsyn-300"><img src="c.gif" alt="Skip visual syntax diagram"
|
||
|
|
border="0" /></a>>>-CREATE SEQUENCE--<span class="italic">sequence-name</span>------------------------------->
|
||
|
|
|
||
|
|
.---------------------------------------------.
|
||
|
|
V (1) |
|
||
|
|
>----+------------------------------------+------+-------------><
|
||
|
|
| .-INTEGER---. |
|
||
|
|
+-AS--+-<span class="italic">data-type</span>-+------------------+
|
||
|
|
+-START WITH--<span class="italic">numeric-constant</span>-------+
|
||
|
|
| .-<span class="italic">1</span>----------------. |
|
||
|
|
+-INCREMENT BY--+-<span class="italic">numeric-constant</span>-+-+
|
||
|
|
| .-NO MINVALUE----------------. |
|
||
|
|
+-+-MINVALUE--<span class="italic">numeric-constant</span>-+-----+
|
||
|
|
| .-NO MAXVALUE----------------. |
|
||
|
|
+-+-MAXVALUE--<span class="italic">numeric-constant</span>-+-----+
|
||
|
|
| .-NO CYCLE-. |
|
||
|
|
+-+-CYCLE----+-----------------------+
|
||
|
|
| .-CACHE--<span class="italic">20</span>---------------. |
|
||
|
|
+-+-NO CACHE----------------+--------+
|
||
|
|
| '-CACHE--<span class="italic">integer-constant</span>-' |
|
||
|
|
| .-NO ORDER-. |
|
||
|
|
'-+-ORDER----+-----------------------'
|
||
|
|
|
||
|
|
</pre>
|
||
|
|
<a name="skipsyn-300" id="skipsyn-300"></a>
|
||
|
|
<a name="wq1295"></a>
|
||
|
|
<div class="notelisttitle" id="wq1295">Notes:</div>
|
||
|
|
<ol type="1">
|
||
|
|
<li>The same clause must not be specified more than once.</li>
|
||
|
|
</ol>
|
||
|
|
<a name="wq1297"></a>
|
||
|
|
<div class="fignone" id="wq1297">
|
||
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn302.htm"
|
||
|
|
border="0" /></span><a href="#skipsyn-301"><img src="c.gif" alt="Skip visual syntax diagram"
|
||
|
|
border="0" /></a>data-type:
|
||
|
|
|
||
|
|
|--+-<span class="italic">built-in-type</span>------+---------------------------------------|
|
||
|
|
'-<span class="italic">distinct-type-name</span>-'
|
||
|
|
|
||
|
|
built-in-type:
|
||
|
|
|
||
|
|
|--+-+---SMALLINT---+---------------------------+---------------|
|
||
|
|
| +-+-INTEGER-+--+ |
|
||
|
|
| | '-INT-----' | |
|
||
|
|
| '---BIGINT-----' |
|
||
|
|
| .-(5,0)-----------------. |
|
||
|
|
'-+-+-DECIMAL-+-+--+-----------------------+-'
|
||
|
|
| '-DEC-----' | | .-,0-. |
|
||
|
|
'-NUMERIC-----' '-(--<span class="italic">integer</span>--+----+--)-'
|
||
|
|
|
||
|
|
</pre>
|
||
|
|
<a name="skipsyn-301" id="skipsyn-301"></a></div>
|
||
|
|
<a name="synscsequence"></a>
|
||
|
|
<h3 id="synscsequence"><a href="rbafzmst02.htm#ToC_922">Description</a></h3>
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><var class="pv">sequence-name</var></dt>
|
||
|
|
<dd>Names the sequence. <a id="idx2036" name="idx2036"></a> The name, including the implicit
|
||
|
|
or explicit qualifier, must not identify a sequence or data area that already
|
||
|
|
exists at the current server. If a qualified function name is specified, the <var class="pv">schema-name</var> cannot be QSYS2, QSYS, or SYSIBM.
|
||
|
|
<p>If SQL names were specified,
|
||
|
|
the sequence will be created in the schema specified by the implicit or explicit
|
||
|
|
qualifier.</p>
|
||
|
|
<p>If system names were specified, the sequence 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 sequence
|
||
|
|
will be created in the current library (*CURLIB).</li>
|
||
|
|
<li>Otherwise, the sequence will be created in the current schema.</li></ul>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">AS </span><var class="pv">data-type</var></dt>
|
||
|
|
<dd>Specifies the data type to be used for the sequence value. The data
|
||
|
|
type can be any exact numeric type (SMALLINT, INTEGER, BIGINT, DECIMAL, or
|
||
|
|
NUMERIC) with a scale of zero, or a user-defined distinct type for which the
|
||
|
|
source type is an exact numeric type with a scale of zero. The default is
|
||
|
|
INTEGER.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><var class="pv">built-in-type</var> </dt><a id="idx2037" name="idx2037"></a>
|
||
|
|
<dd>Specifies the built-in data type used as the basis for the internal
|
||
|
|
representation of the sequence. If the data type is DECIMAL or NUMERIC, the
|
||
|
|
precision must be less than or equal to 63 and the scale must be 0. 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 DECIMAL instead of a
|
||
|
|
NUMERIC data type.</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><var class="pv">distinct-type-name</var> </dt><a id="idx2038" name="idx2038"></a>
|
||
|
|
<dd>Specifies that the data type of the sequence is a distinct type (a user-defined
|
||
|
|
data type). If the source type is DECIMAL or NUMERIC, the precision of the
|
||
|
|
sequence is the precision of the source type of the distinct type. The precision
|
||
|
|
of the source type must be less than or equal to 63 and the scale must be
|
||
|
|
0. If a distinct type name is specified without a schema name, the distinct
|
||
|
|
type name is resolved by searching the schemas on the SQL path.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">START WITH </span><var class="pv">numeric-constant</var></dt><a id="idx2039" name="idx2039"></a>
|
||
|
|
<dd>Specifies the first value that is generated for the sequence. The value
|
||
|
|
can be any positive or negative value that could be assigned to a column of
|
||
|
|
the data type associated with the sequence, without non-zero digits to the
|
||
|
|
right of the decimal point. If a value is not explicitly specified when the
|
||
|
|
sequence is defined, the default is the MINVALUE for an ascending sequence
|
||
|
|
and the MAXVALUE for a descending sequence.
|
||
|
|
<p>This value is not necessarily
|
||
|
|
the value that a sequence would cycle to after reaching the maximum or minimum
|
||
|
|
value of the sequence. The START WITH clause can be used to start a sequence
|
||
|
|
outside the range that is used for cycles. The range used for cycles is defined
|
||
|
|
by MINVALUE and MAXVALUE.</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">INCREMENT BY </span><var class="pv">numeric-constant</var></dt><a id="idx2040" name="idx2040"></a>
|
||
|
|
<dd>Specifies the interval between consecutive values of the sequence. The
|
||
|
|
value must not exceed the value of a large integer constant without any non-zero
|
||
|
|
digits existing to the right of the decimal point. The value must be assignable
|
||
|
|
to the sequence.
|
||
|
|
<p>If the value is zero or positive, the sequence of values
|
||
|
|
for the sequence ascends. If the value is negative, the sequence of values
|
||
|
|
descends. The default is 1.</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">NO MINVALUE </span><span> or </span><span class="bold">MINVALUE </span></dt><a id="idx2041" name="idx2041"></a>
|
||
|
|
<dd>Specifies the minimum value at which a descending sequence either cycles
|
||
|
|
or stops generating values, or an ascending sequence cycles to after reaching
|
||
|
|
the maximum value. The default is NO MINVALUE.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><span class="bold">NO MINVALUE </span></dt>
|
||
|
|
<dd>For an ascending sequence, the value is the START WITH value, or 1 if
|
||
|
|
START WITH is not specified. For a descending sequence, the value is the minimum
|
||
|
|
value of the data type (and precision, if DECIMAL or NUMERIC) associated with
|
||
|
|
the sequence.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">MINVALUE </span><var class="pv">numeric-constant</var></dt>
|
||
|
|
<dd>Specifies the numeric constant that is the minimum value that is generated
|
||
|
|
for this sequence. This value can be any positive or negative value that could
|
||
|
|
be assigned to a column of the data type associated with the sequence and
|
||
|
|
without non-zero digits to the right of the decimal point. The value must
|
||
|
|
be less than or equal to the maximum value.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">NO MAXVALUE </span><span> or </span><span class="bold">MAXVALUE </span></dt><a id="idx2042" name="idx2042"></a>
|
||
|
|
<dd>Specifies the maximum value at which an ascending sequence either cycles
|
||
|
|
or stops generating values, or a descending sequence cycles to after reaching
|
||
|
|
the minimum value. The default is NO MAXVALUE.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><span class="bold">NO MAXVALUE</span></dt>
|
||
|
|
<dd>For an ascending sequence, the value is the maximum value of the data
|
||
|
|
type (and precision, if DECIMAL or NUMERIC) associated with the sequence.
|
||
|
|
For a descending sequence, the value is the START WITH value, or -1 if START
|
||
|
|
WITH is not specified.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">MAXVALUE</span> <var class="pv">numeric-constant</var></dt>
|
||
|
|
<dd>Specifies the numeric constant that is the maximum value that is generated
|
||
|
|
for this sequence. This value can be any positive or negative value that could
|
||
|
|
be assigned to a column of the data type associated with the sequence and
|
||
|
|
without non-zero digits to the right of the decimal point. The value must
|
||
|
|
be greater than or equal to the minimum value.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">NO CYCLE</span> or <span class="bold">CYCLE</span></dt><a id="idx2043" name="idx2043"></a>
|
||
|
|
<dd>Specifies whether this sequence should continue to generate values after
|
||
|
|
reaching either the maximum or minimum value of the sequence. The default
|
||
|
|
is NO CYCLE.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><span class="bold">NO CYCLE</span></dt>
|
||
|
|
<dd>Specifies that values will not be generated for the sequence once the
|
||
|
|
maximum or minimum value for the sequence has been reached.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">CYCLE</span></dt>
|
||
|
|
<dd>Specifies that values continue to be generated for this column after
|
||
|
|
the maximum or minimum value has been reached. If this option is used, after
|
||
|
|
an ascending sequence reaches the maximum value of the sequence, it generates
|
||
|
|
its minimum value. After a descending sequence reaches its minimum value of
|
||
|
|
the sequence, it generates its maximum value. The maximum and minimum values
|
||
|
|
for the column determine the range that is used for cycling.
|
||
|
|
<p>When CYCLE
|
||
|
|
is in effect, duplicate values can be generated for a sequence by the database manager.</p>
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">CACHE</span> or <span class="bold">NO CACHE</span></dt><a id="idx2044" name="idx2044"></a>
|
||
|
|
<dd>Specifies whether to keep some preallocated values in memory. Preallocating
|
||
|
|
and storing values in the cache improves the performance of the NEXT VALUE
|
||
|
|
sequence expression. The default is CACHE 20.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><span class="bold">CACHE</span> <var class="pv">integer-constant</var></dt>
|
||
|
|
<dd>Specifies the maximum number of sequence values that are preallocated
|
||
|
|
and kept in memory. Preallocating and storing values in the cache improves
|
||
|
|
performance.
|
||
|
|
<p>In certain situations, such as system failure, all
|
||
|
|
cached sequence values that have not been used in committed statements are
|
||
|
|
lost, and thus, will never be used. The value specified for the CACHE option
|
||
|
|
is the maximum number of sequence values that could be lost in these situations.</p>
|
||
|
|
<p>The minimum value that can be specified is 2, and the maximum is the
|
||
|
|
largest value that can be represented as an integer.</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">NO CACHE</span> </dt>
|
||
|
|
<dd>Specifies that values for the sequence are not preallocated. If NO CACHE
|
||
|
|
is specified, the performance of the NEXT VALUE sequence expression will be
|
||
|
|
worse than if CACHE is specified.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">ORDER</span> or <span class="bold">NO ORDER</span></dt><a id="idx2045" name="idx2045"></a>
|
||
|
|
<dd>Specifies whether the sequence values must be generated in order of
|
||
|
|
request. The default is NO ORDER.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><span class="bold">ORDER</span></dt>
|
||
|
|
<dd>Specifies that the values are generated in order of request. If ORDER
|
||
|
|
is specified, the performance of the NEXT VALUE sequence expression will be
|
||
|
|
worse than if NO ORDER is specified.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><span class="bold">NO ORDER</span></dt>
|
||
|
|
<dd>Specifies that the values do not need to be generated in order of request.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<a name="wq1298"></a>
|
||
|
|
<h3 id="wq1298"><a href="rbafzmst02.htm#ToC_923">Notes</a></h3>
|
||
|
|
<p><span class="bold">Sequence attributes:</span> A sequence is created as a *DTAARA
|
||
|
|
object. The *DTAARA objects should not be changed with the Change Data Area
|
||
|
|
(*CHGDTAARA) or any other similar interface because doing so may cause unexpected
|
||
|
|
failures or unexpected results when attempting to use the SQL sequence through
|
||
|
|
SQL.</p>
|
||
|
|
<p><span class="bold">Sequence ownership:</span> The <span class="italic">owner</span> of the sequence is the user profile or group user profile of the job
|
||
|
|
executing the statement.</p>
|
||
|
|
<p><span class="bold">Sequence authority:</span> If SQL names are used, sequences
|
||
|
|
are created with the system authority of *EXCLUDE on *PUBLIC. If system names
|
||
|
|
are used, sequences 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 sequence 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 sequence.</p>
|
||
|
|
<p><span class="bold">Relationship of MINVALUE and MAXVALUE:</span> Typically,
|
||
|
|
MINVALUE will be less than MAXVALUE, but this is not required. MINVALUE could
|
||
|
|
be equal to MAXVALUE. If START WITH was the same value as MINVALUE and MAXVALUE,
|
||
|
|
and CYCLE is implicitly or explicitly specified, this would be a constant
|
||
|
|
sequence. In this case a request for the next value appears to have no effect
|
||
|
|
because all the values generated by the sequence are in fact the same.</p>
|
||
|
|
<p>MINVALUE must not be greater than MAXVALUE</p>
|
||
|
|
<p><span class="bold">Defining constant sequences:</span> It is possible to define
|
||
|
|
a sequence that would always return a constant value. This could be done by
|
||
|
|
specifying an INCREMENT value of zero and a START WITH value that does not
|
||
|
|
exceed MAXVALUE, or by specifying the same value for START WITH, MINVALUE
|
||
|
|
and MAXVALUE. For a constant sequence, each time a NEXT VALUE expression is
|
||
|
|
processed the same value is returned. A constant sequence can be used as a
|
||
|
|
numeric global variable. ALTER SEQUENCE can be used to adjust the values that
|
||
|
|
will be generated for a constant sequence.</p>
|
||
|
|
<p><span class="bold">Defining Sequences That Cycle:</span> A sequence can be
|
||
|
|
cycled manually by using the ALTER SEQUENCE statement. If NO CYCLE is implicitly
|
||
|
|
or explicitly specified, the sequence can be restarted or extended using the
|
||
|
|
ALTER SEQUENCE statement to cause values to continue to be generated once
|
||
|
|
the maximum or minimum value for the sequence has been reached.</p>
|
||
|
|
<p>A sequence can be explicitly defined to cycle by specifying the CYCLE keyword.
|
||
|
|
Use the CYCLE option when defining a sequence to indicate that the generated
|
||
|
|
values should cycle once the boundary is reached. When a sequence is defined
|
||
|
|
to automatically cycle (for example CYCLE was explicitly specified), then
|
||
|
|
the maximum or minimum value generated for a sequence may not be the actual
|
||
|
|
MAXVALUE or MINVALUE specified, if the increment is a value other than 1 or
|
||
|
|
-1. For example, the sequence defined with START WITH=1, INCREMENT=2, MAXVALUE=10
|
||
|
|
will generate a maximum value of 9, and will not generate the value 10.</p>
|
||
|
|
<p>When defining a sequence with CYCLE, then any application conversion tools
|
||
|
|
(for converting applications from other vendor platforms to DB2®) should also
|
||
|
|
explicitly specify MINVALUE, MAXVALUE and START WITH.</p>
|
||
|
|
<p><span class="bold">Caching sequence numbers:</span> A range of sequence numbers
|
||
|
|
can be kept in memory for fast access. When an application accesses a sequence
|
||
|
|
that can allocate the next sequence number from the cache, the sequence number
|
||
|
|
allocation can happen quickly. However, if an application accesses a sequence
|
||
|
|
that cannot allocate the next sequence number from the cache, the sequence
|
||
|
|
number allocation will require an update to the *DTAARA object.</p>
|
||
|
|
<p>Choosing a high value for CACHE allows faster access to more successive
|
||
|
|
sequence numbers. However, in the event of a failure, all sequence values
|
||
|
|
in the cache are lost. If the NO CACHE option is used, the values of the sequence
|
||
|
|
are not stored in the sequence cache. In this case every access to the sequence
|
||
|
|
requires an update to the *DTAARA object. The choice of the value for CACHE
|
||
|
|
should be made keeping the trade-off between performance and application requirements
|
||
|
|
in mind.</p>
|
||
|
|
<p><span class="bold">Persistence of the most recently generated sequence value:</span> The database manager remembers the most recently generated value for
|
||
|
|
a sequence within the SQL-session, and returns this value for a PREVIOUS VALUE
|
||
|
|
expression specifying the sequence name. The value persists until either the
|
||
|
|
next value is generated for the sequence, the sequence is dropped or altered,
|
||
|
|
or until the end of the application session. The value is unaffected by COMMIT
|
||
|
|
and ROLLBACK statements.</p>
|
||
|
|
<p>PREVIOUS VALUE is defined to have a linear scope within the application
|
||
|
|
session. Therefore, in a nested application:</p>
|
||
|
|
<ul>
|
||
|
|
<li>on entry to a nested function, procedure, or trigger, the nested application
|
||
|
|
inherits the most recently generated value for a sequence. That is, specifying
|
||
|
|
an invocation of a PREVIOUS VALUE expression in a nested application will
|
||
|
|
reflect sequence activity done in the invoking application, routine, or trigger
|
||
|
|
prior to entering the nested application. An invocation of PREVIOUS VALUE
|
||
|
|
expression in a nested application results in an error if a NEXT VALUE expression
|
||
|
|
for the specified sequence had not yet been done in the invoking application,
|
||
|
|
routine, or trigger.</li>
|
||
|
|
<li>on return from a function, procedure, or trigger, the invoking application,
|
||
|
|
routine or trigger will be affected by any sequence activity in the function,
|
||
|
|
procedure, or trigger. That is, an invocation of PREVIOUS VALUE in the invoking
|
||
|
|
application, routine, or trigger after returning from the nested application
|
||
|
|
will reflect any sequence activity that occurred in the lower level applications.</li></ul>
|
||
|
|
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
|
||
|
|
supported for compatibility to prior releases of other DB2 UDB products.
|
||
|
|
These keywords are non-standard and should not be used:</p>
|
||
|
|
<ul>
|
||
|
|
<li>The keywords NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER can
|
||
|
|
be used as synonyms for NO MINVALUE, NO MAXVALUE, NO CYCLE, NO CACHE, and
|
||
|
|
NO ORDER.</li>
|
||
|
|
<li>A comma can be used to separate multiple sequence options.</li></ul>
|
||
|
|
<a name="wq1299"></a>
|
||
|
|
<h3 id="wq1299"><a href="rbafzmst02.htm#ToC_924">Examples</a></h3>
|
||
|
|
<p>Create a sequence called ORG_SEQ that starts at 1, increments by 1, does
|
||
|
|
not cycle, and caches 24 values at a time:</p>
|
||
|
|
<pre class="xmp"> <span class="bold">CREATE SEQUENCE</span> ORG_SEQ
|
||
|
|
<span class="bold">START WITH</span> 1
|
||
|
|
<span class="bold">INCREMENT BY</span> 1
|
||
|
|
<span class="bold">NO MAXVALUE
|
||
|
|
NO CYCLE
|
||
|
|
CACHE</span> 24</pre>
|
||
|
|
<hr /><br />
|
||
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstxcschema.htm">Previous Page</a> | <a href="rbafzmsthctabl.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>
|