The Range-clustered Table: A New Kind of Table in DB2 UDB V8.1.4 - Page 2

by Paul C. Zikopoulos and Roman B. Melnyk

The ORGANIZE BY KEY SEQUENCE clause in DB2 UDB Version 8.1.4

Figure 2, which shows a partial syntax diagram for the CREATE TABLE statement, includes the syntax fragments that form a path to the ORGANIZE BY KEY SEQUENCE clause. You can specify one or more columns of the table that are to be included in the unique key that determines the sequence of the RCT. You can also specify a starting constant for the range of key values; this is optional, and if you do not specify a value, 1 is assumed. Whether or not you specify a starting constant, you must specify an ending constant for the range of key values.

If you specify the DISALLOW OVERFLOW clause, key values will not be allowed to exceed the defined range. If, on the other hand, you specify the ALLOW OVERFLOW clause, key values will be allowed to exceed the defined range. In this case, overflow data will be placed in the overflow area, which is dynamically allocated. If you have an RCT supporting overflow that goes past the defined range, the resulting performance will be very poor. The overflow area is an emergency mechanism that will kill performance, but might allow enough time for you to unload the data and recreate the table with a wider range.

The following example shows how to create a range-clustered table called CUSTOMERS. This table will have two columns: CUSTOMER_ID and CUSTOMER_NAME. The CUSTOMER_ID column will become the unique key that determines the sequence of the RCT; in this case, the range of possible key values will be defined as 1 to 100, and key values will not be allowed to exceed this defined range.

CREATE TABLE customers (
    customer_id INTEGER NOT NULL,
    customer_name VARCHAR(80)
    )
  ORGANIZE BY KEY SEQUENCE (customer_id STARTING FROM 1 ENDING AT 100)
    DISALLOW OVERFLOW

If an application needed to access the row with a customer ID value of 2, the database manager would look for the second row in the CUSTOMERS table. This is quite straightforward, and data access is fast, because each row in an RCT has a predetermined offset from the logical start of the table.

If a row is updated and the key column values are modified, the updated row is copied to the new location, and the old copy of the row is deleted, maintaining the clustering of data in the table.

Inserting rows whose key values do not exceed the defined range is no problem, but if you try to insert a row whose key value exceeds the range (1000, for example), the insert operation fails:

INSERT INTO customers VALUES (1,'Jones')

INSERT INTO customers VALUES (5,'Benoit')

INSERT INTO customers VALUES (1000,'Hoffmann')
SQL1870N  A row could not be inserted into a range-clustered table
because a key sequence column is out of range.  SQLSTATE=23513

The following example shows how to create a range-clustered table in which the sequence of the RCT is determined by key values from two columns. The table, called ORDERS, will have three columns: ORDER_ID, CUSTOMER_ID, and PART_NAME. In this case, the ORDER_ID column and the CUSTOMER_ID column together will form a composite unique key that determines the sequence of the RCT.

CREATE TABLE orders (
    order_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    part_name VARCHAR(80)
    )
  ORGANIZE BY KEY SEQUENCE (order_id ENDING 1000,
    customer_id ENDING 100)
    DISALLOW OVERFLOW

Here are some important points to remember about RCTs:

• Space is allocated statically for each possible key value and cannot be modified.
• The space required for an RCT must be available when the table is created, and must be sufficient to contain the number of rows in the specified key range multiplied by the row size.
• You cannot alter a key range.
• If other columns in a table are defined as type VARCHAR, sufficient space to accommodate the specified maximum length is allocated for each row.
• The order of rows in an RCT that allows overflow is not guaranteed.
• Clustering indexes are not supported because RCTs are already clustered.
• Table reorganization is not required for overflow-disallowed RCT tables because such tables are always clustered.

Conclusion

DB2 UDB range-clustered table support, which introduces a new table type in V8.1.4 through the ORGANIZE BY KEY SEQUENCE clause on the CREATE TABLE statement, can dramatically improve performance for some workloads, particularly in transaction processing environments. Be sure to consider exploiting this feature and its many advantages when you are ready to move to DB2 UDB V8.1.4.

About the Authors

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.