SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-118
Considerations for CREATE TABLE
•
an increment
•
a cycle option
See CREATE TABLE Statement on page 2-95 for more information.
SG Table
When an internal sequence generator is created, a sequence generator table (SG
Table) is created. The SG Table contains a column, CURRENT_VALUE. The
CURRENT_VALUE represents the next available value for the IDENTITY column from
the sequence generator. Each SG Table contains only one row, with a primary key
value of zero. The name space and object type for the table has a value SG.
* Indicates primary key
An SG Table name is named as a combination of a name and special characters. For
example, CAT.SCH."@@INTERNAL_SG_430744938574267_@@".
Use the sg-location clause during CREATE TABLE to specify a location for the SG
Table. If you do not specify a location, the system default location is used. Since an SG
Table has base table characteristics, it includes an entry in the metadata OBJECTS,
ACCESS_PATHS, COLS, and PARTITIONS tables, etc.
The SG Table is updated upon a request to obtain the next value from the sequence
generator. The CURRENT_VALUE from the SG Table is selected and returned as the
next value. Then, the CURRENT_VALUE is updated with CURRENT_VALUE +
INCREMENT BY value specified in SG Attributes. A new independent transaction
different from the user transaction is started and committed for the SG Table select and
update operations. A separate ESP process houses the operator that starts and
commits this new transaction.
Considerations for an IDENTITY column
•
If you define the IDENTITY column as GENERATED BY DEFAULT AS IDENTITY,
values for the IDENTITY column are generated by default. If you specify a value
for the IDENTITY column, NonStop SQL/MX uses that value and does not
generate a unique value for that row.
•
An IDENTITY column can be the primary key or part of the primary key.
•
An IDENTITY column can be the partitioning key or part of the partitioning key.
Table 2-3. SG Table
Column Number Column Name Data Type Description
*1 ZERO_PKCOL LARGEINT Primary key with
value zero
2 CURRENT_VALUE LARGEINT Contains the next
value to be inserted