Sequence object solved by the DB2 sequence auto-incrementing Column

From: http://blog.csdn.net/lqb2008/article/details/6773078

I have introduced topics related to version 8 in the last two columns. We may need to design databases and applications todayProgram. We have talked about secondary indexes and additional index modifications for new data partitions. In the last issue, we learned how dssize can affect future partition policies. Now, I want to briefly introduce sequence objects and how to use them to replace the identity columns. We will also introduce some new features in version 8 that use the identity column.

Number Generation Problems

In the past, many applications must be able to generate serial numbers (for example, to obtain the next available account ). The problem is that this work is usually done by storing these numbers in a single control table, and then using the select max of Repeatable read to retrieve and use this number in subsequent insert operations. This table is often used by multiple transactions, which leads to single point of competition that cannot be solved in applications. This is because only one transaction can retrieve the next value at a time, because the transaction must lock the table to increment the number. See figure 1.

Many creative solutions have been tried over the past years, such as row-level locking, but the system overhead produced by these technologies, coupled with conflicts between thousands of concurrent transactions, will still lead to performance bottlenecks. If multiple members use the table in the data sharing environment, it also causes single point of failure and causes huge lock overhead.

 

Identity column

Therefore, the identity column appears in later versions 6 to solve the problem mentioned above. If an identity column is assigned to a table, when a row is inserted to the table, it will be filled in according to the definition of the identity column (start with and increment by values. Although the use of the identity column is far better than the use of a single control table, the use of the identity column is very limited and there is a management problem.

Before Version 8, the identity column also encountered some problems, such:

• How do I obtain the value of the identity column to fill the ri-related table?
• How can I get the value before insertion?
• How do I reset or change the value of the identity column?

This is only part of the problem. Many companies have developed a method around these issues. They delete the identity column from the table and create a table containing only the identity column and Its generated values. Therefore, the preceding problems, such as restarting the value of the identity column or filling the table related to RI, can be solved by separating the identity column from the table. This technology still allows DB2 to fill the value of the identity column, and reduces many restrictions. However, it is still necessary to fill, access, and maintain the table, and it cannot solve all problems in the identity column, and it cannot fully develop potential performance related to the number generation executed by DB2. Figure 2 shows an example of how to use the technology.

Version 8 has mitigated some of the preceding identity column problems. For example, you can obtain the value of the identity column before using the new insert statement in the select function. The following example shows how to use this function to obtain the value of the identity column during insertion.

(Assume that the created table uses the automatically generated acct_id as the identity column)

Example 1
Select acct_id
From final table
(Insert into uid1.account (name, type, balance)
Values ('master card', 'credentials', 50000 ))

You can also change other values in version 8, for example:

Cache/no cache
Cycle/NO cycle
Minvalue
Maxvalue
Increment
Restart

These functions, especially restart with, provide more flexibility for the use of the identity column. However, these features are defined on a single table, which in turn imposes some limitations on them, and applications still have limited use of them. In addition, these changes will also cause the tablespace to be put in the reorg hold state, thus the table cannot obtain the tablespace.

 

Sequence object

When I first announced sequence objects, I thought they only added another feature to DB2 to port applications from other databases to DB2. But the more I study them deeply, the more I like these new objects, because they eliminate many restrictions on the identity column, they also have many unique and useful features. It also provides us with another alternative method for generating numbers.

A sequence object is a user-defined object used to generate a numerical sequence based on its creation specifications. They provide an incremental counter generated by DB2 and are very similar to the identity column. The identity column can be treated as a special sequence object. However, the sequence column is separated from the table.

Sequence object values can be used in applications for various reasons. This method has some advantages, such:

• No need to wait for the value to increase;
• An independent continuous numeric generation object (not associated with the table );
• Generate numbers in ascending or descending order;
• It is used to transplant applications from other databases;
• Keys can be generated to coordinate keys across multiple tables (ri or related applications.

The sequence name consists of two parts: the pattern name of 128 bytes and the identifier of 128 bytes. They are created through the new create sequence statement, and all their attributes are completely user-defined (you can also use the default values ). The value in the sequence object can be any numeric data type. The initial value is defined by the start with value, while the incremental value is defined by the increment by (ascending or descending. These values can be cached or generated in the order of requests.

The following example shows how to create a sequence object and its simple usage.

Create sequence account_seq
As integer
Start with 1
Increment by 10
Cycle
Cache 20

As you can see, the sequence object is used in a similar way as many people use the identity column. But this is more effective, and the sequence object has some excellent advantages. For example, you can use next value for and previous value. Nextvalue for is generated for the sequence object and returns the next value. Previous value for is generated for the sequence object and returns the previous value. These statements can be used in the following places:

• Select and select into statements;
• Select clause of fullselect In the insert statement;
• Set clause in the update Statement (search or locate );
• Set main variables;
• Values or values;
• Create procedure, function, and trigger.

The following example (table 2 on the right) demonstrates the usage of these statements:
Assume acct_seq is start with 10 increment by 10

As you can see, using a sequence object to replace the identity column has many advantages. The following is a simple comparison between the two.

Sequence object and identity column
Sequence object	Identity column (with V8 function)
A separate sequence object is created in a user request.	Internal sequence objects generated, maintained, and filled by DB2
Can be used for any purpose they choose, and there are multiple	Associated with a specific table, and only one
Cycle will be rewound and duplicated, without the need for uniqueness	If the unique index is on the identity column and a copy is created, cycle may cause problems.
When used to fill the table, it can be updated later	If it is generated always, it cannot be updated.
You can change the attributes of a (Alter) sequence object. You can also perform comments and grant/revoke (grant/revoke) permissions.	Only the table can be changed (if the identity column is added to the filled table, it will be placed in the reorg hold state)
Deletable	Cannot be deleted from the table *
Support for next value for expression and previous value for expression	Id_val_local must be used and only the last value within the scope of user commit (COMMIT) is returned.
*If the sequence object used in future design is better than the identity column, consider carefully when selecting the identity column. If they are defined in a filled table and need to be deleted, you must delete the table and recreate it. This may be a big problem for large tables in an efficient environment.

 

Conclusion

The identity column and sequence object have a place in our design. Since they all achieve the same purpose-to generate serial numbers, you should choose which one is more suitable for you. This will depend on the flexibility you need to generate the numbers and how the application will use them.