Working with Sequencing

Sequence numbers are artificial keys that uniquely identify the records in a table. When you define a sequence number field for a descriptor, the OracleAS TopLink Mapping Workbench automatically generates a new sequence number every time you insert a new record into the table.

Use the project's Sequencing tab (see Figure 2–5) or the Sequencing area of a descriptor's Descriptor Info tab (see Figure 4–1) to specify sequencing information

Database tables often use a sequence number as the primary key. The OracleAS TopLink Mapping Workbench can use the database's native support or a sequence table to maintain sequence numbers.

Tip: Oracle recommends using sequence numbers for primary keys because they are single, guaranteed, unique values.

Other data values may require composite primary keys to make up a unique value, which is less optimal. Additionally, non-artificial values may need to change, and this is not allowed for primary keys.

Using Sequence Numbers with Entity Beans

When implementing sequencing for Entity Beans, you must provide create() methods and the corresponding ejbCreate() and ejbPostCreate() methods for your bean home and bean class.

OracleAS TopLink creates the primary key value when you first insert the bean in the database. The key value is not passed as a parameter to the create() methods because they do not set the primary key value (the key is generated).

Note: Be careful when using transactions with these create methods. If you create an Entity Bean within a transaction and you use native sequencing in Sybase, SQL Server or Informix, then the bean's key is not initialized until the transaction commits and the bean is persisted to the database for the first time.

Using Native Sequencing

Oracle, Sybase, SQL Server, and Informix databases support native sequencing in which the DBMS generates the sequence numbers. However, the OracleAS TopLink Mapping Workbench must still tell the DBMS to assign sequence number values.

• For Oracle databases, create a SEQUENCE object in the database.

• For Sybase and SQL Server databases, set the primary key field to IDENTITY.

• For Informix databases, set the primary key field to use SERIAL.

  
  

  Tip: If you use native sequencing in these databases, the OracleAS TopLink Mapping Workbench cannot support pre-allocation. Oracle recommends using the sequence table instead. Oracle databases support pre-allocation, but only if the sequence increment matches the pre-allocation size. See "Sample Sequence Table" for more information.

  
  

Using Sequence Tables

If your database does not use native sequencing, you must manually create the sequence table (named SEQUENCE). Use this table to store each table, as illustrated below:

Field name	Field format	Description
SEQ_NAME	CHAR	Name of the sequence number
SEQ_COUNT	NUMERIC	Current value

After creating the table, you must initialize the table within the application. The value of the SEQ_COUNT field for each sequence should be zero (0), as in the following table.

Example 4-2 Sample Sequence Table

SEQ_NAME	SEQ_COUNT
EMP_SEQ	0
PROJ_SEQ	0

Pre-allocating Sequence Numbers

To increase the speed of database inserts, obtain a block of sequence numbers (by setting an allocation size) instead of executing a corresponding SELECT statement to obtain the newly assigned sequence number each time you create an object.

OracleAS TopLink uses a default pre-allocation size of 50 when using a sequence table and 1 when using native sequencing.

• When using native sequencing in Sybase, SQL Server, or Informix databases, pre-allocation cannot be set — it is always 1.

• When using native sequencing, you must set the pre-allocation size explicitly in the OracleAS TopLink Mapping Workbench.

• When using native sequencing in an Oracle database, you can use pre-allocation only if an INCREMENT is set on the Oracle Sequence object (not the CACHE option). This increment must match the pre-allocation size specified in the OracleAS TopLink Mapping Workbench. If the increment is set incorrectly, invalid and negative sequence numbers could be generated. The CACHE option specifies how many sequences are pre-allocated on the database server; the INCREMENT specifies the number that can be pre-allocated to the database client.

  
  

  Tip: Oracle recommends using sequence pre-allocation because of its performance and concurrency benefits.

  
  

Creating the Sequence Table on the Database

Normally, the database administrator defines the sequence table or sequencing object. However, you can use the OracleAS TopLink schema manager to define the sequence numbers using:

    SchemaManager schemaManager = new SchemaManager(session);
    schemaManager.createSequences();

You should execute this command only once. The SchemaManager creates a sequence entry for each registered descriptor.

Refer to the Oracle Application Server TopLink Application Developer's Guide for more information on using the schema manager to create number information in the database.