Automatically generated numeric sequence in the DB2 Universal Database __ Database

Two ways to generate a sequence of numbers
In DB2, you can automatically generate a sequence of numbers in two ways: Define a column with an IDENTITY attribute. Creates a SEQUENCE object. IDENTITY Column

When you define a table's column with the IDENTITY property, a value is automatically generated for that column whenever a row is inserted into the table. SEQUENCE Object

The second way to let DB2 automatically generate a sequence of numbers is to create a SEQUENCE object. You can use sequence expressions (sequence expression) to refer to a Sequence object. A sequence expression can appear in most places where an expression can occur. A sequence expression can specify whether the value to be returned is a newly generated value or a previously generated value.

If the sequence reference is for the next value (next), a numeric value is automatically generated for the sequence and returned as the result of the sequence expression. For example, if we assume that a sequence named Orders_seq has been created, the sequence expression returns the next value generated for the sequence:

	NEXT VALUE for Orders_seq

If the sequence reference is for the previous value (previous value), the number generated in the previous SQL statement is returned as the result of the sequence expression. The sequence expression returns the previous value generated by the sequence:

	PREVIOUS VALUE for Orders_seq

Note: When DB2 UDB introduces a sequence, it has supported non-SQL standard syntax nextval instead of NEXT value, and prevval instead of PREVIOUS value. These variants continue to be supported.

Make a selection between identities and sequences

Although both the identity and the sequence are used to generate values, you may want to use one instead of the other depending on the particular case.

The identity column is useful in the following scenarios: Only one column in the table requires an automatically generated value each row requires a separate value the process of generating a new value from the table's primary key with an auto generator is closely linked to the insert operation of the table, regardless of how the insert operation occurs

A Sequence object is useful when you want to store values generated from a sequence into multiple tables in which more than one column needs to be automatically generated (possibly by using the same sequence or multiple sequences to generate multiple values for each row) the process that generates the new value is independent of any reference to the table

Unlike a sequence object, an identity column is defined on a table and therefore requires some restrictions. There can be at most one identity column per table. When you create a column as an identity column, you must use the exact numeric data type for that column. Because the IDENTITY property is row livings to a value, which is similar to the default clause, you cannot specify a default clause when defining an identity column. An identity column is implicitly defined as not NULL.

Example 1. Combine customer and vendor tables

To illustrate the typical use of identity columns, consider a table that contains customer order information for a fictitious Widget company's database. The company wants to automatically generate order numbers for each row (order) inserted in the table. DDL for our example

As shown in Listing 1, we used the identity column for the order number, and they defined the order number column as part of the primary key. Note: This column, and its own IDENTITY property, does not guarantee that the generated sequence values are unique. However, the PRIMARY KEY constraint guarantees the uniqueness of the rows in the table. To ensure that only automatically generated values are inserted into the identity column, they specify the GENERATED always clause. At the end of each quarter, the Widget company used the last generated order_id to determine how many orders were received this quarter. The option no CACHE and ORDER ensures that unused identity values are not discarded in the event of a system failure. The Widget company plans to start a new quarter by using the ALTER TABLE statement and restarting the order number column from 1.

Listing 1 shows all the properties of the identity column explicitly, even if the value you set is the default value when the value is not specified. Because the default value varies by vendor implementation, specifying all options is a good coding habit.

Listing 1. Create an order table using the IDENTITY column

CREATE TABLE customer_orders_t (
   order_id   INT not NULL

      
      (START with
INCREMENT by
MINVALUE

       NO CYCLE
       NO CACHE
       ORDER),
   DATE not NULL,
   cust_id    INT not NULL,
   INT not NULL,
   Quantity   INT not NULL,
   Price      DECIMALis not NULL,
   Status     CHARnotNULL,
PRIMARY KEY (Order_date, order_id))

The following is an example of an INSERT statement that inserts rows into a table.

INSERT into VALUES
   (DEFAULT, current DATE,
    : CID,:p ID,: Qty,: Cost, ' PENDING ')

Widget Company not only manages customer orders, but also manages vendor orders. Vendor orders in a separate Vendor order table, this table is defined in a way that is very similar to the way the Customer order table is defined.

CREATE TABLE supplier_orders_t (
   order_id   INT not NULL

       As IDENTITY
      (START with
INCREMENT by
MINVALUE

       NO CYCLE
       NO CACHE
       ORDER),
   DATE not NULL,   
   supp_id    INT not NULL,
   INT not NULL,
   Quantity   INT not NULL,
   Price      DECIMALis notNULL,
   Status     CHARnotNULL,
PRIMARY KEY (Order_date, order_id))

The following is an example of an INSERT statement that inserts rows into the supplier_orders_t table:

INSERT into VALUES
   (DEFAULT, current DATE,
    : SID,:p ID,: Qty,: Cost, ' PENDING ')

Combined Tables

The company realizes that greater efficiency and synergy can be achieved by combining the customer order form and the supplier order form into a total order form for customers and suppliers. The only difference between a customer order or a vendor order is whether the order is coming in or out, which is reflected in the STATUS field. In order to combine these tables and cause minimal disruption, they plan to introduce such changes gradually. The steps in their plan include synchronizing the order numbers, which are generated for each table, so that they are unique between the tables. Wait until all non-synchronized orders are completed. (Or, they can wait until a quarter starts, then the order number will be reset.) Gradually stop using the vendor order form and use the Customer order form to manage all orders from customers and suppliers. Clear.

1th Step: Synchronize the order number
To synchronize the generated order_id numbers used in both tables, change both tables so that the SEQUENCE object that is used to identify the column provides a value, not always a value. The value is generated by a single sequence orders_seq for the identity column in both tables. Modify the INSERT statement for each table to explicitly provide values to the identity column by referencing Orders_seq in the NEXT VALUE expression. The ORDERS_SEQ sequence is defined in the following way:

CREATE SEQUENCE  as in T
START with
INCREMENT by
MINVALUE

NO CYCLE
NO CACHE
ORDER

Use the SET GENERATED by DEFAULT clause to change the Customer order table and the Vendor Order table to allow the insert operation to explicitly provide values to the identity column.

ALTER TABLE
ALTER COLUMN SET GENERATED by DEFAULT

ALTER TABLE
ALTER COLUMN SET GENERATED by DEFAULT

When you modify all of the INSERT statements in the two order table to provide an explicit value to the order_id column and the orders_seq sequence starts with the appropriate value, the LOCK table statement is issued to restrict the insert operation to both tables.

Here's how to change the INSERT statement for the vendor and customer Orders table:

INSERT into VALUES
   (NEXT VALUEfor the currentDATE,
    : CID,:p ID,: Qty,: Cost, ' PENDING ')

INSERT into VALUES
   (NEXT VALUEfor the currentDATE,
    : SID,:p ID,: Qty,: Cost, ' PENDING ')

So here's how to modify the orders_seq sequence so that it starts with the last value of the maximum value generated by the order_id identity column of the customer order and the Vendor Order table. First, use the SELECT statement to determine the value:

SELECT MAX MAX
 from customer_orders_t C, supplier_orders_t s

For example, suppose the above query returns two values: 42331 and 57231. Then you can change the orders_seq sequence as follows:

ALTER SEQUENCE
RESTART with 57232

The COMMIT statement frees the lock on the table, and then you can perform an insert operation on the two order tables. The values that are inserted are generated from a single sequence orders_seq, and not every order_id column has a value that is independently generated by the IDENTITY property, so these values will be unique in two tables.

See Figure 1 for an emoticon of step 1th.

Figure 1. ORDER_ID sequence value overrides identity column

2nd step: Wait until the order is synchronized
The Widget company does not want to wait until the quarter begins (when orders are reset), but decides to monitor the status of the order. When the results of the following query are empty tables, they go to step 3rd:

SELECT
 from (SELECT from
WHERE  and  as X
UNION All (SELECT from
WHERE  and order_id < 57232)

See Figure 2 for an emoticon of step 2nd.

Figure 2. Elimination of all potential duplicate order_id numbers

3rd step: Gradually stop using the supplier order
To gradually stop using the supplier_orders_t table, you can make it temporarily unavailable by renaming the table as follows:

RENAME TABLE  to Supplier_orders_t_old

A view is then created to allow existing references to supplier_orders_t to continue to access the underlying data:

CREATE VIEW
   
    Price, status)
As SELECT
    
    Price, status
From

All active orders for traditional customers and suppliers are now managed in the customer_orders_t table. To make it easier and more intuitive to maintain these tables in the future, you will need to perform some cleanup work in step 4th.

See Figure 3 for an emoticon of step 3rd.

Figure 3. All new orders are entered customer_orders_t

4th Step: Clear
Because the order number must now be generated for only one column (order_id), the value can be generated by an identity column instead of using a separate sequence object. Similarly, the table is temporarily unavailable, and the identity value is reset to the next value generated by the sequence.

LOCK TABLE In EXCLUSIVE MODE

VALUES NEXT VALUE from  into: nextorder

For example, suppose the above query returns the value: 64243. Then, you can change the customer_orders_t table as follows:

ALTER TABLE customer_orders_t
ALTER COLUMN order_id

      RESTART with

Each instance (in these instances, the INSERT statement uses the ORDERS_SEQ sequence) also needs to be changed back to using DEFAULT, as previously shown in step 1th. You can now delete the sequence:

DROP SEQUENCE RESTRICT

After you have archived the data from the old Vendor Orders table, you can also delete the table.

See Figure 4 for an emoticon of step 4th.

Figure 4. Use identity column to generate values again, starting with 64243

Tuning performance
Since the use of the customer_orders_t table is almost twice times the original, the Widget company decided to place the table in a parallel environment. To take advantage of the parallel insert operations that can now be performed, they decide to tune the performance of the table's identity columns with cache values. They determined the cache size 50 is appropriate for the number of orders created per hour and the frequency of restarting the database system for any reason. They also changed how to calculate the number of orders actually created in a quarter, so there is no reason to force sequence values to be generated sequentially. Still continues to meet the primary requirements to generate unique values in a quarter, the following adjustments have been made to enhance the performance of the sequence generation used to identify columns:

ALTER TABLE
ALTER COLUMN
SET CACHE
SET NO ORDER

Example 2. Collecting satellite readings

As shown in Example 1, the sequence allows unique values to be generated across multiple tables. Sequences also allow for the automatic generation of their values for multiple columns in a table.

For this example, consider a orbiting satellite orbiting a planet or moon. This particular satellite SAT1 is designed to capture data readings from 16 different points on its orbit. The table that collects the data has three columns, and the values are automatically generated in these columns: One column uses the IDENTITY property to generate the reading ID, and the other two to get their values from the sequence. See Figure 5 for instructions.

Figure 5. A satellite and 16 data points on its orbit.

DDL for our example
Because the number of readings can be very large, the identity column uses the DECIMAL (31) data type.

CREATE TABLE Sat1_readings (
   DECIMAL Not NULL PRIMARY KEY
GENERATED always as IDENTITY
      (START with
INCREMENT by
MINVALUE

       NO CYCLE
       NO CACHE
       ORDER),
   SMALLINT not NULL,
   SMALLINT not NULL,
   BLOB M))

The orbit_location has a value from 0 to 15, which represents 16 points on the track to get readings. Create the following sequence to generate a loop sequence of these 16 values:

CREATE SEQUENCE
As Smallin T
START with
INCREMENT by
MINVALUE
MAXVALUE

   
   ORDER

The Horizon_adjustment value indicates where the satellite is related to the horizontal plane. A value of 0 indicates that it is on a horizontal plane, and a value of +4 means that it is at the highest point on the horizontal plane, and a value of 4 means it is below the horizontal level. These sequences start with a value of 4, which is neither the minimum nor the maximum because it is used in the equation to calculate the horizontal adjustment value. The sequence loops periodically for each track. The following sequence is used when calculating the Horizon_adjustment value:

CREATE SEQUENCE
As SMALLINT
START with
INCREMENT by
MINVALUE
MAXVALUE

   
   ORDER

Fill Reading Tables

Each time a reading is generated, the following INSERT statement fills the table:

INSERT into VALUES
   (DEFAULT, NEXT VALUE for
ABS (NEXT VALUE for Horizon_adjustment_seq)-4,
    :p lanet_image)

After the first 17 reads and the corresponding INSERT statements, the values that are automatically generated for the columns reading_id (R_i), Orbit_location (o_l), and Horizon_adjustment (h_a) are:

R_i	1.	2.	3.	4.	5.	6.	7.	8.	9.	10.	11.	12.	13.	14.	15.	16.	17.
O_l	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	0
H_a	0	-1	-2	-3	-4	-3	-2	-1	0	1	2	3	4	3	2	1	0

Capturing additional Readings
Because the instrument is sensitive enough, the planets can be read before the satellites actually reach the orbit. The range of the instrument allows 10 readings to be made before the satellite enters its orbit and when it approaches the planet. To capture these additional readings outside of the normal orbital ring, the sequence values begin outside the range of MINVALUE and MAXVALUE values that define the cycle limit.

The following statement alters the identity value and sequence value to allow an additional 10 readings before reading 16 points on a track. The RESTART option restarts the identity column or sequence from the specified value, or restarts with the value specified with the start with value when the identity column or sequence is created when no value is specified.

ALTER TABLE Sat1_readings
ALTER COLUMN reading_id
RESTART

ALTER SEQUENCE
RESTART with-10

ALTER SEQUENCE
RESTART with-14

The table below shows what the first 17 readings would be if the above three ALTER statements were executed before the corresponding INSERT statement. These 17 readings include 10 readings obtained prior to the orbit and the first seven readings obtained on the track:

0

r_i	1.	2.	3.	4.	5.	6.	7.	8.	9.	Ten.	one.	.	.	.	.	.	.
o_l	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6
h_a		9	8	7	6	5	4	3	2	1	-1	-2	-3	-4	-3	-2

Conclusion

DB2 supports two flexible ways to automatically generate numbers: Identity columns (closely linked to a table) and Sequence objects (which generate values independently of any table reference). The above example shows how powerful and flexible it is to identify columns and sequence objects for automatic generation of numeric values. These examples illustrate the following features that identify columns and sequences: Use the identity column in the primary key to explicitly specify a value for the identity column in the case of two tables, instead of generating a value from a predetermined value starting with a sequence of values from the specified value to start the identity column value cache identity value to improve performance define a sequence or identity column To allow a value loop to specify a start value that is greater than the minimum value defined for the sequence to specify a start value for the sequence, which is a negative value that defines a sequence to generate a value that increases and decreases first to ensure that the values are returned in the order in which they were generated

We want you to find that sequences and identities are useful for your application.