Skip Headers

Oracle TopLink Developer's Guide 10g Release 3 (10.1.3) B13593-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# Understanding Sequencing in Relational Projects

In an relational project, you store persistent objects for your application in database tables that represent the class of instantiated object. As Figure 20-2 shows, each row of the VEHICLE_POOL table represents an instantiated object from that class, and the VEH_ID column holds the primary key for each object.

Figure 20-2 Sequencing Elements in a Class Database Table

Description of "Figure 20-2 Sequencing Elements in a Class Database Table"

You configure TopLink sequencing at the project or session level (see "Configuring Sequencing at the Project Level" or "Configuring Sequencing at the Session Level") to tell TopLink how to obtain values for the primary key column: that is, what type of sequencing to use (see "Sequencing Types").

You configure TopLink sequencing at the descriptor level (see "Configuring Sequencing at the Descriptor Level") to tell TopLink into which table and column to write the sequence value when an instance of a descriptor's reference class is created.

Note: When choosing a column type for a primary key value, ensure that the type provides a suitable precision. For example, if you use a `TIMESTAMP` type but your database platform's `TIMESTAMP` is defined only to the second, then identical values may be returned for objects created within the same second.

This section describes the following:

- Sequencing Configuration Options

- Sequencing Types

- Sequencing and Preallocation Size

- Sequencing With CMP Entity Beans

## Sequencing Configuration Options

You can configure sequencing using either TopLink Workbench or Java (but not both).

Using TopLink Workbench, create one sequence with a single preallocation size that applies to all descriptors that require sequencing. You can configure table sequencing (see "Table Sequencing") or native sequencing (see "Native Sequencing With an Oracle Database Platform"). If you choose table sequencing, you can either use default table and column names or specify your own (see "Default Versus Custom Sequence Table"). Oracle recommends using TopLink Workbench to configure sequencing. Using TopLink Workbench, you can easily configure the sequencing options applicable to most applications. For more information, see "Configuring Sequencing at the Project Level" or "Configuring Sequencing at the Session Level".

Using Java, you can configure any sequence type that TopLink supports (see "Sequencing Types"). You can create any number and combination of sequences per project. You can create a sequence object explicitly or use the platform default sequence (see "Default Sequencing"). You can associate the same sequence with more than one descriptor or associate different sequences (and different sequence types) to various descriptors. You can configure a separate preallocation size for each descriptor's sequence. For more information, see "Using Java".

## Sequencing Types

TopLink supports the following sequence types:

- Table Sequencing

- Unary Table Sequencing

- Query Sequencing

- Default Sequencing

- Native Sequencing With an Oracle Database Platform

- Native Sequencing With a Non-Oracle Database Platform

### Table Sequencing

With table sequencing, you create a single database table that includes sequencing information for one or more sequenced objects in the project. TopLink maintains this table to track sequence numbers for these object types.

As Figure 20-3 shows, the table may contain sequencing information for more than one class that uses sequencing. The default table is called `SEQUENCE` and contains two columns:

- `SEQ_NAME`, which specifies the class type to which the selected row refers

- `SEQ_COUNT`, which specifies the highest sequence number currently allocated for the object represented in the selected row

Figure 20-3 TopLink Table Sequence Table

Description of "Figure 20-3 TopLink Table Sequence Table"

The rows of the `SEQUENCE` table represent each sequence object: one for each class that participates in sequencing or a single sequence object across several classes so that they can benefit from the same preallocation pool. When you configure sequencing at the descriptor level (see "Configuring Sequencing at the Descriptor Level"), you specify the `SEQ_NAME` for the class. Add a row with that name to the `SEQUENCE` table and initialize the `SEQ_COUNT` column to the value `0`.

Each time a new instance of a class is created, TopLink obtains the required sequence value. For efficiency, TopLink uses preallocation to reduce the number of table accesses required to obtain sequence values (see "Sequencing and Preallocation Size").

You can create the `SEQUENCE` table on the database in one of two ways:

- Use TopLink Workbench to create the table. See "Generating Tables on the Database" for more information.

- Use the TopLink table creator to create and update the table manually. See "Generating SQL Creation Scripts" for more information.

You can configure table sequencing using TopLink Workbench or Java. Oracle recommends that you use TopLink Workbench. For more information about configuring table sequencing, see "Configuring Sequencing at the Project Level" or "Configuring Sequencing at the Session Level".

#### Default Versus Custom Sequence Table

In most cases, you implement table sequencing using the default table and column names. However, you may want to specify your own table and column names if:

- You want to use an existing sequence table for sequencing.

- You do not want to use the default naming convention for the table and its columns.

### Unary Table Sequencing

Although similar to table sequencing (see "Table Sequencing"), with unary table sequencing, you create a separate sequence table for each sequenced object in the project.

As Figure 20-4 shows, sequencing information appears in the table for a single class that uses sequencing. You can name the table anything you want but it must contain only one column named (by default) `SEQUENCE`.

Figure 20-4 TopLink Unary Table Sequence Table

Description of "Figure 20-4 TopLink Unary Table Sequence Table"

When you configure sequencing at the descriptor level, you specify the sequence name for the class: this is the name of the unary table sequence table. Figure 20-4 shows a unary table sequence for the `Employee` class. The `Employee` class descriptor is configured (see "Configuring Sequencing at the Descriptor Level") with a sequence name of `EMP_SEQ` to match the unary table sequence table name. TopLink adds a row to this table and initializes the `SEQUENCE` column to the value `1`.

Each time a new class is created, TopLink obtains the required sequence value from the single row of the unary sequence table corresponding to the class. For efficiency, TopLink uses preallocation to reduce the number of table accesses required to obtain sequence values (see "Sequencing and Preallocation Size").

You can create the unary table sequence table on the database in one of two ways:

- Use TopLink Workbench to create the table. See "Generating Tables on the Database" for more information.

- Use the TopLink table creator to create and update the table manually. See "Generating SQL Creation Scripts" for more information.

If you are migrating a BEA WebLogic CMP application to OC4J and TopLink persistence (see "Migrating BEA WebLogic Persistence to OC4J TopLink Persistence"), the TopLink migration tool does not migrate BEA WebLogic single column sequence tables to TopLink unary sequence tables (see "Unary Table Sequencing"). After migration, you must manually configure your project to use TopLink unary sequence tables if your application originally used single column sequence tables in BEA WebLogic.

Currently, you can only configure unary table sequencing in Java using the `UnaryTableSequence` class (for more information, see "Using Java").

### Query Sequencing

With query sequencing, you can access a sequence resource using custom read (`ValueReadQuery`) and update (`DataModifyQuery`) queries and a preallocation size that you specify. This allows you to perform sequencing using stored procedures and allows you to access sequence resources that are not supported by the other sequencing types that TopLink provides.

Currently, you can only configure query sequencing in Java using the `QuerySequence` class (for more information, see "Configuring Query Sequencing").

### Default Sequencing

The platform owned by a login is responsible for providing a default sequence instance appropriate for the platform type. For example, by default, a `DatabasePlatform` provides a table sequence using the default table and column names (see "Table Sequencing").

You can access this default sequence directly using `DatasourceLogin` method `getDefaultSequence`, or indirectly by using the `DefaultSequence` class, a wrapper for the platform default sequence.

If you associate a descriptor with a nonexistent sequence, the TopLink runtime will create an instance of `DefaultSequence` to provide sequencing for that descriptor. For more information, see "Configuring the Platform Default Sequence".

The main purpose of the `DefaultSequence` is to allow a sequence to use a different pre-allocation size than the project default.

Currently, you can only make use of default sequencing in Java (for more information, see "Using the Platform Default Sequence").

### Native Sequencing With an Oracle Database Platform

TopLink support for native sequencing with Oracle databases is similar to table sequencing (see "Table Sequencing"), except that TopLink does not maintain a table in the database. Instead, the database contains a sequence object that stores the current maximum number and preallocation size for sequenced objects. The sequence name configured at the descriptor level identifies the sequence object responsible for providing sequencing values for the descriptor's reference class.

You can configure native sequencing using TopLink Workbenchor Java. Oracle recommends that you use TopLink Workbench. For more information about configuring table sequencing, see "Configuring Sequencing at the Project Level" or "Configuring Sequencing at the Session Level".

#### Understanding the Oracle SEQUENCE Object

The Oracle `SEQUENCE` object implements a strategy that closely resembles TopLink sequencing: it implements an `INCREMENT` construct that parallels the TopLink preallocation size, and a `sequence.nextval` construct that parallels the `SEQ_COUNT` field in the TopLink `SEQUENCE` table in table sequencing. This implementation enables TopLink to use the Oracle `SEQUENCE` object as if it were a TopLink `SEQUENCE` table, but eliminates the need for TopLink to create and maintain the table.

As with table sequencing, TopLink creates a pool of available numbers by requesting that the Oracle `SEQUENCE` object increment the `sequence.nextval` and return the result. Oracle adds the value, `INCREMENT`, to the `sequence.nextval`, and TopLink uses the result to build the sequencing pool.

The key difference between this process and the process involved in table sequencing is that TopLink is unaware of the `INCREMENT` construct on the `SEQUENCE` object. TopLink sequencing and the Oracle `SEQUENCE` object operate in isolation. To avoid sequencing errors in the application, set the TopLink preallocation size and the Oracle `SEQUENCE` object `INCREMENT` to the same value. Note that the Oracle sequence object must have a starting value equal to the preallocation size because when TopLink gets the next sequence value, it assume it has the previous preallocation size of values.

#### Using SEQUENCE Objects

Your database administrator (DBA) must create a `SEQUENCE` object on the database for every sequencing series your application requires. If every class in your application requires its own sequence, the DBA creates a `SEQUENCE` object for every class; if you design several classes to share a sequence, the DBA need create only one `SEQUENCE` object for those classes.

For example, in Figure 20-5, consider the case of a sporting goods manufacturer that manufactures three styles of tennis racquet. The data for these styles of racquet are stored in the database as follows:

- Each style of racquet has its own class table.

- Each manufactured racquet is an object represented by a line in the class table.

- The system assigns serial numbers to the racquets that use sequencing.

Figure 20-5 Example of Database Tables—Racquet Information

Description of "Figure 20-5 Example of Database Tables—Racquet Information"

The manufacturer can do either of the following:

- Use separate sequencing for each racquet style. The DBA builds three separate `SEQUENCE` objects, perhaps called `ATTACK_SEQ`, `VOLLEY_SEQ`, and `PROX_SEQ`. Each different racquet line has its own serial number series, and there may be duplication of serial numbers between the lines (for example: all three styles may include a racquet with serial number 1234).

- Use a single sequencing series for all racquets. The DBA builds a single `SEQUENCE` object (perhaps called `RACQUET_SEQ`). The manufacturer assigns serial numbers to racquets as they are produced, without regard for the style of racquet.

### Native Sequencing With a Non-Oracle Database Platform

Several databases support a type of native sequencing in which the database management system generates the sequence numbers.

When you create a database table for a class that uses native sequencing, include a primary key column, and set the column type as follows:

- For Sybase and Microsoft SQL Server databases, set the primary key field to the type `IDENTITY`.

- For IBM Informix databases, set the primary key field to the type `SERIAL`.

Note: TopLink does not support native sequencing in IBM DB2 databases.

When you insert a new object into the table, TopLink populates the object before insertion into the table, but does not include the sequence number. As the database inserts the object into its table, the database automatically populates the primary key field with a value equal to the primary key of the previous object plus `1`.

At this point, and before the transaction closes, TopLink reads back the primary key for the new object so that the object has an identity in the TopLink cache.

Note: This type of sequencing does not support preallocation, so the preallocation size must be set to 1. To take advantage of sequence preallocation, Oracle recommends that you use table sequencing on these databases instead of native sequencing.

If your database provides native sequencing, but TopLink does not directly support it, you may be able to access the native sequence object using a query sequence and stored procedures. For more information, see "Query Sequencing".

You can configure native sequencing using TopLink Workbenchor Java. Oracle recommends that you use TopLink Workbench. For more information about configuring table sequencing, see "Configuring Sequencing at the Project Level" or "Configuring Sequencing at the Session Level".

## Sequencing and Preallocation Size

To improve sequencing efficiency, TopLink lets you preallocate sequence numbers. Preallocation enables TopLink to build a pool of available sequence numbers that are assigned to new objects as they are created and inserted into the database. TopLink assigns numbers from the sequence pool until the pool is empty.

The preallocation size specifies the size of the pool of available numbers. Preallocation improves sequencing efficiency by substantially reducing the number of database accesses required by sequencing. By default, TopLink sets preallocation size to `50`. You can specify preallocation size either in TopLink Workbench or as part of the session login.

Preallocation size configuration applies to table sequencing and Oracle native sequencing. In Oracle native sequencing, the sequence preallocation size must match the Oracle sequence object increment size. Preallocation is not available for native sequencing in other databases as they use an auto-assigned sequence column. Oracle recommends that you use table sequencing in non-Oracle databases to allow preallocation.

For table sequencing, TopLink maintains a pool of preallocated values for each sequenced class. When TopLink exhausts this pool of values, it acquires a new pool of values, as follows:

1. TopLink accesses the database, requesting that the `SEQ_COUNT` for the given class (identified by the `SEQ_NAME`) be incremented by the preallocation size and the result returned.

   For example, consider the `SEQUENCE` table in Figure 20-3. If you create a new purchase order and TopLink has exhausted its pool of sequence numbers, then TopLink executes a SQL statement to increment `SEQ_COUNT` for `SEQ_PURCH_ORDER` by the preallocation size (in this case, the TopLink default of `50`). The database increments `SEQ_COUNT` for `SEQ_PURCH_ORDER` to `1600` and returns this number to TopLink.

2. TopLink calculates a maximum and a minimum value for the new sequence number pool, and creates the pool of values.

3. TopLink populates the object sequence attribute with the first number in the pool and writes the object to the class table.

As you add new objects to the class table, TopLink continues to assign values from the pool until it exhausts the pool. When the pool is exhausted, TopLink again requests new values from the table.

Using TopLink Workbench, you specify a preallocation size when you choose a sequencing type at the project or session level. That preallocation size applies to all descriptors.

Using Java, you can specify a different preallocation size for each sequence that you create.

For more information about configuring preallocation size, see "Configuring Sequencing at the Project Level" or "Configuring Sequencing at the Session Level".

## Sequencing With CMP Entity Beans

To implement sequencing for CMP entity beans, use a sequencing strategy that implements preallocation, such as table sequencing or Oracle native sequencing. Preallocation ensures that the entity bean primary key is available at the `ejbPostCreate` method. If you use non-Oracle native sequencing (for example, Sybase, Microsoft SQL Server, or Informix database native sequencing), be aware that:

- Non-Oracle native sequencing does not strictly conform to any EJB specification, because it does not initialize the primary key for a created object until you commit the transaction that creates the object. EJB specifications expect that the primary key is available at `ejbPostCreate` method.

- TopLink CMP integration for IBM WebSphere application server does not support native sequencing other than Oracle native sequencing.

- OC4J and BEA WebLogic Server supports native sequencing; however, this type of native sequencing does not assign or return a primary key for a created object until you commit the transaction in which the object is created. Because of this, if you use native sequencing, commit a transaction immediately after calling the `ejbCreate` method to avoid problems with object identity in the TopLink cache and the container.

TopLink CMP Integration With IBM WebSphere Application Server

The TopLink CMP integration with IBM WebSphere application server does not automatically provide the primary key after calling the `ejbCreate` method. If you deploy to a IBM WebSphere application server, explicitly set the primary key in the `ejbCreate` method. Example 20-3 illustrates this call in a WebSphere integration.

Example 20-3 Setting Primary Key in IBM WebSphere

    public Integer ejbCreate() throws CreateException {
        oracle.toplink.ejb.cmp.was.SessionLookupHelper.getHelper().getSession(this)
          .getActiveUnitofWork().assignSequenceNumber(this);
        return null
    }

This example uses the TopLink `SessionLookupHelper` to look up the correct session and uses the session to assign a sequence number to the bean.

TopLink CMP Integration With OC4J and BEA WebLogic Server

In the TopLink CMP integration with OC4J and BEA WebLogic Server, TopLink automatically sets the primary key field on the bean. You do not pass the key value as a parameter to the `create` method, nor set them in the `create` method. Example 20-4 illustrates this call in a WebLogic integration.

Example 20-4 Setting Primary Key in OC4J and BEA WebLogic

    public Integer ejbCreate() throws CreateException {
        return null;
    }

If you are migrating a BEA WebLogic CMP application to OC4J and TopLink persistence (see "Migrating BEA WebLogic Persistence to OC4J TopLink Persistence"), the TopLink migration tool automatically configures your project to use unary table sequencing (see "Unary Table Sequencing") if your application originally used single-column sequence tables in BEA WebLogic.