friedkiwi/ibm-information-center
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
master
ibm-information-center/dist/eclipse/plugins/i5OS.ic.db2_5.4.0.1/rbafzmstasequence.htm

406 lines
24 KiB
HTML
Raw Permalink Normal View History

Add readme and dist folders
2024-04-02 14:02:31 +00:00
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="dc.language" scheme="rfc1766" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow"/>
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="keywords" content="ALTER SEQUENCE statement, ALTER SEQUENCE,
SQL statements, in ALTER SEQUENCE statement, sequence-name, built-in data type,
data type for ALTER SEQUENCE, distinct-type" />
<title>ALTER 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="asequence"></a>
<h2 id="asequence"><a href="rbafzmst02.htm#ToC_755">ALTER SEQUENCE</a></h2><a id="idx1326" name="idx1326"></a><a id="idx1327" name="idx1327"></a>
<p>The ALTER SEQUENCE statement can be used to change a sequence in any of
these ways: </p>
<ul>
<li>Restarting the sequence</li>
<li>Changing the increment between future sequence values</li>
<li>Setting or eliminating the minimum or maximum values</li>
<li>Changing the number of cached sequence numbers</li>
<li>Changing the attribute that determines whether the sequence can cycle
or not</li>
<li>Changing whether sequence numbers must be generated in order of request</li></ul>
<a name="wq1056"></a>
<h3 id="wq1056"><a href="rbafzmst02.htm#ToC_756">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="wq1057"></a>
<h3 id="wq1057"><a href="rbafzmst02.htm#ToC_757">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>For the sequence identified in the statement:
<ul>
<li>The system authority *EXECUTE on the library containing the sequence</li>
<li>The ALTER privilege for the sequence</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>The following system authorities:
<ul>
<li>*USE to the Change Data Area (CHGDTAARA) command</li>
<li>*USE to the Retrieve Data Area (RTVDTAARA) 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 UPDATE 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>, <a href="rbafzmstgnt.htm#eqtablet">Corresponding System Authorities When Checking Privileges to a Table or View</a>, and <a href="rbafzmstgntudtp.htm#eqtabled">Corresponding System Authorities When Checking Privileges to a Distinct Type</a>.</p>
<a name="wq1058"></a>
<h3 id="wq1058"><a href="rbafzmst02.htm#ToC_758">Syntax</a></h3>
<a href="rbafzmstasequence.htm#synasequence"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn247.htm"
border="0" /></span><a href="#skipsyn-246"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>>>-ALTER SEQUENCE--<span class="italic">sequence-name</span>-------------------------------->
.----------------------------------------------.
V (1) |
>----+-DATA TYPE--<span class="italic">data-type</span>----------------+------+------------>&lt;
+-RESTART--+------------------------+-+
| '-WITH--<span class="italic">numeric-constant</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----' |
+-+-NO CACHE----------------+---------+
| '-CACHE--<span class="italic">integer-constant</span>-' |
'-+-NO ORDER-+------------------------'
'-ORDER----'
</pre>
<a name="skipsyn-246" id="skipsyn-246"></a>
<a name="wq1059"></a>
<div class="notelisttitle" id="wq1059">Notes:</div>
<ol type="1">
<li>The same clause must not be specified more than once.</li>
</ol>
<a name="wq1061"></a>
<div class="fignone" id="wq1061">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn248.htm"
border="0" /></span><a href="#skipsyn-247"><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-247" id="skipsyn-247"></a></div>
<a name="wq1062"></a>
<div class="fignone" id="wq1062">
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn249.htm"
border="0" /></span><a href="#skipsyn-248"><img src="c.gif" alt="Skip visual syntax diagram"
border="0" /></a>built-in-type:
|--+-+---SMALLINT---+--------------------------------------------------------------------------+--|
| +-+-INTEGER-+--+ |
| | '-INT-----' | |
| '---BIGINT-----' |
| .-(5,0)------------------------. |
+-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0--------. | |
| '-NUMERIC-----' '-(--<span class="italic">integer</span>--+-----------+--)-' |
| '-<span class="italic">, integer</span>-' |
| .-(--53--)------. |
+-+-FLOAT--+---------------+-+--------------------------------------------------------------+
| | '-(--<span class="italic">integer</span>--)-' | |
| +-REAL---------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+----' |
| .-(--1--)-------. |
+-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+
| | | '-CHAR------' '-(--<span class="italic">integer</span>--)-' | +-FOR BIT DATA---+ | |
| | '-+-+-CHARACTER-+--VARYING-+--(--<span class="italic">integer</span>--)-' +-FOR SBCS DATA--+ | |
| | | '-CHAR------' | +-FOR MIXED DATA-+ | |
| | '-VARCHAR----------------' '-<span class="italic">ccsid-clause</span>---' | |
| | .-(--1M--)-------------. | |
| '-----+-+-<span>CHARACTER</span>-+--<span>LARGE OBJECT</span>-+------+----------------------+--+----------------+-' |
| | '-<span>CHAR</span>------' | '-(--<span class="italic">integer</span>--+---+--)-' +-FOR SBCS DATA--+ |
| '-<span>CLOB</span>------------------------' +-K-+ +-FOR MIXED DATA-+ |
| +-M-+ '-<span class="italic">ccsid-clause</span>---' |
| '-G-' |
| .-(--1--)-------. |
+-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+
| | '-(--<span class="italic">integer</span>--)-' | '-<span class="italic">ccsid-clause</span>-' |
| +-+-GRAPHIC VARYING-+--(--<span class="italic">integer</span>--)---+ |
| | '-VARGRAPHIC------' | |
| | .-(--1M--)-------------. | |
| '---DBCLOB----+----------------------+-' |
| '-(--<span class="italic">integer</span>--+---+--)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
| .-(--1--)-------. |
+-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+
| | | '-(--<span class="italic">integer</span>--)-' | | |
| | '-+-BINARY VARYING-+--(--<span class="italic">integer</span>--)-' | |
| | '-VARBINARY------' | |
| | .-(--1M--)-------------. | |
| '---+-BLOB----------------+----+----------------------+-' |
| '-BINARY LARGE OBJECT-' '-(--<span class="italic">integer</span>--+---+--)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------+----------------------------------------------------------------+
| | .-(--0--)-. | |
| +-TIME--+---------+------+ |
| | .-(--6--)-. | |
| '-TIMESTAMP--+---------+-' |
| .-(--200--)-----. |
+---DATALINK--+---------------+--+--------------+-------------------------------------------+
| '-(--<span class="italic">integer</span>--)-' '-<span class="italic">ccsid-clause</span>-' |
'---ROWID-----------------------------------------------------------------------------------'
ccsid-clause:
.-NOT NORMALIZED-.
|--CCSID--<span class="italic">integer</span>--+----------------+---------------------------|
'-NORMALIZED-----'
</pre>
<a name="skipsyn-248" id="skipsyn-248"></a></div>
<a name="synasequence"></a>
<h3 id="synasequence"><a href="rbafzmst02.htm#ToC_759">Description</a></h3>
<dl class="parml">
<dt class="bold"><var class="pv">sequence-name</var></dt>
<dd>Identifies the sequence to be altered.<a id="idx1328" name="idx1328"></a> The name, including
the implicit or explicit qualifier, must identify a sequence or data area
that already exists at the current server.
</dd>
<dt class="bold"><span class="bold">DATA TYPE</span> <var class="pv">data-type</var></dt>
<dd>Specifies the new 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.
<p>Each of the existing
START WITH, INCREMENT BY, MINVALUE, and MAXVALUE attributes that are not changed
by the ALTER SEQUENCE statement must contain a value that could be assigned
to a column of the data type associated with the new data type.</p>
</dd>
<dt class="bold"><var class="pv">built-in-type</var> </dt><a id="idx1329" name="idx1329"></a>
<dd>Specifies the new 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="idx1330" name="idx1330"></a>
<dd>Specifies that the new 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">RESTART </span></dt>
<dd>Restarts the sequence. If <var class="pv">numeric-constant</var> is not specified,
the sequence is restarted at the value specified implicitly or explicitly
as the starting value on the CREATE SEQUENCE statement that originally created
the sequence.
<dl class="parml">
<dt class="bold"><span class="bold">WITH </span><var class="pv">numeric-constant</var></dt>
<dd>Restarts the sequence with the specified value. This value can be any
positive or negative value that does not exceed the value of a numeric constant
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
</dd>
</dl>After restarting a sequence or changing the sequence to allow cycling,
it is possible for sequence numbers to be duplicate values of ones generated
by the sequence previously.
</dd>
<dt class="bold"><span class="bold">INCREMENT BY </span><var class="pv">numeric-constant</var></dt>
<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.</p>
</dd>
<dt class="bold"><span class="bold">NO MINVALUE </span><span> or </span><span class="bold">MINVALUE </span></dt>
<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.
<dl class="parml">
<dt class="bold"><span class="bold">NO MINVALUE </span></dt>
<dd>For an ascending sequence, the value is the original starting value.
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>
<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.
<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 original starting value.
</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">CYCLE</span> or <span class="bold">NO CYCLE</span></dt>
<dd>Specifies whether this sequence should continue to generate values after
reaching either the maximum or minimum value of the sequence.
<dl class="parml">
<dt class="bold"><span class="bold">CYCLE</span></dt>
<dd>Specifies that values continue to be generated for this sequence
after the maximum or minimum value has been reached. If this keyword 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 sequence 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>
<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>
</dl>
</dd>
<dt class="bold"><span class="bold">CACHE</span> or <span class="bold">NO CACHE</span></dt>
<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.
<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 reduces
synchronous I/O when values are generated for the sequence.
<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 is
2.</p>
</dd>
<dt class="bold"><span class="bold">NO CACHE</span> </dt>
<dd>Specifies that values of the sequence are not to be preallocated. It
ensures that there is not a loss of values in situations, such as system failure.
When this option is specified, the values of the sequence are not stored in
the cache. In this case, every request for a new value for the sequence results
in synchronous I/O.
</dd>
</dl>
</dd>
<dt class="bold"><span class="bold">NO ORDER</span> or <span class="bold">ORDER</span></dt>
<dd>Specifies whether the identity values must be generated in order of
request.
<dl class="parml">
<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>
<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>
</dl>
</dd>
</dl>
<a name="wq1063"></a>
<h3 id="wq1063"><a href="rbafzmst02.htm#ToC_760">Notes</a></h3>
<p><span class="bold">Altering a sequence:</span> </p>
<ul>
<li>Only future sequence numbers are affected by the ALTER SEQUENCE statement.</li>
<li>All the cached values are lost when a sequence is altered.</li>
<li>After restarting a sequence or changing it to cycle, it is possible that
a generated value will duplicate a value previously generated for that sequence.</li></ul>
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
supported for compatibility to prior releases of other DB2&reg; 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></ul>
<a name="wq1064"></a>
<h3 id="wq1064"><a href="rbafzmst02.htm#ToC_761">Examples</a></h3>
<p>A possible reason for specifying RESTART without a numeric value would
be to reset the sequence to the START WITH value. In this example, the goal
is to generate the numbers from 1 up to the number of rows in a table and
then inserting the numbers into a column added to the table using temporary
tables. </p>
<pre class="xmp"> <span class="bold">ALTER SEQUENCE</span> ORG_SEQ
<span class="bold">RESTART</span>
<span class="bold">DECLARE GLOBAL TEMPORARY TABLE</span> TEMP_ORG <span class="bold">AS</span>
<span class="bold">(SELECT NEXT VALUE FOR</span> ORG_SEQ, ORG.*
<span class="bold">FROM</span> ORG<span class="bold">) WITH DATA</span></pre>
<p>Another use would be to get results back where all the resulting rows are
numbered:</p>
<pre class="xmp"> <span class="bold">ALTER SEQUENCE</span> ORG_SEQ
<span class="bold">RESTART</span>
<span class="bold">SELECT NEXT VALUE FOR</span> ORG_SEQ, ORG.*
<span class="bold">FROM</span> ORG</pre>
<hr /><br />
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstaprocedures.htm">Previous Page</a> | <a href="rbafzmstatabl.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
<a href="rbafzmstindex.htm#index">Index</a> ]
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
</body>
</html>
Reference in New Issue Copy Permalink