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

393 lines
23 KiB
HTML
Raw Permalink Blame History

<?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) |
>----+------------------------------------+------+------------->&lt;
| .-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&reg;) 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>
Reference in New Issue View Git Blame Copy Permalink