Defining system-generated object identifiers

This section provides information about defining system-generated object identifiers.

About this task

There are two common approaches of generating unique values, both of which can be applied to object identifiers:

with sequences
with the GENERATE_UNIQUE function

If you need to use numeric values as object identifiers, you can use a sequence. To begin, use the REF USING clause to specify that the base type of the object reference is to be a numeric type, in the following case, an INT:

  CREATE TYPE BusinessUnit_t AS
    (Name VARCHAR(20),
     Headcount INT)
     REF USING INT
     MODE DB2SQL

The typed table definition is as follows:

  CREATE TABLE BusinessUnit OF BusinessUnit_t
    (REF IS oid USER GENERATED)

The sequence to generate object identifiers can be defined as follows:

  CREATE SEQUENCE BusinessUnitOid AS REF(BusinessUnit_t)

Note that modifying data in a subtable implicitly modifies all supertables. Therefore, the trigger that invokes the sequence to generate the object identifier is best added to the root of the table hierarchy.

  CREATE TRIGGER Gen_Bunit_oid
    NO CASCADE
    BEFORE INSERT ON BusinessUnit
    REFERENCING NEW AS new
    FOR EACH ROW
    MODE DB2SQL
    SET new.oid = NEXT VALUE FOR BusinessUnitOid

Note that since the sequence is defined as REF(BusinessUnitOid), no casting is required to assign to the oid column.

A new business unit can now be added:

  INSERT INTO BusinessUnit (Name, Headcount)
    VALUES('Software', 10)

The usage of a sequence also enables you to retrieve the generated object identifier and use it in subsequent statements. For example, you can add an employee to the Software BusinessUnit assuming the Dept column is of type REF(BusinessUnit):

  INSERT INTO Employee(Name, Age, SerialNum, Salary, Dept)
    VALUES('Tom', 28, 106, 60000, PREVVAL FOR BusinessUnitOid)

As an alternative to using sequences to generate object identifiers, you can use the GENERATE_UNIQUE function. Because GENERATE_UNIQUE returns a CHAR (13) FOR BIT DATA value, ensure that the REF USING clause on the CREATE TYPE statement can accommodate a value of that type. The default of VARCHAR (16) FOR BIT DATA is suitable for this purpose. For example, assume that the BusinessUnit_t type is created with the default representation type; that is, no REF USING clause is specified, as follows:

   CREATE TYPE BusinessUnit_t AS
      (Name VARCHAR(20),
      Headcount INT)
      MODE DB2SQL;

The typed table definition is as follows:

   CREATE TABLE BusinessUnit OF BusinessUnit_t
   (REF IS Oid USER GENERATED);

Note that you must always provide the clause USER GENERATED.

An INSERT statement to insert a row into the typed table, then, might look like this:

   INSERT INTO BusinessUnit (Oid, Name, Headcount)
      VALUES(BusinessUnit_t(GENERATE_UNIQUE( )), 'Toy' 15);

To insert an employee that belongs to the Toy department, you can use a statement like the following, which issues a subselect to retrieve the value of the object identifier column from the BusinessUnit table, casts the value to the BusinessUnit_t type, and inserts that value into the Dept column:

   INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept)
      VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000, 
        BusinessUnit_t(SELECT Oid FROM BusinessUnit WHERE Name='Toy'));

Instead of inserting the generated object identifier explicitly on the INSERT statement, you can encapsulate the generation and insertion of the object identifier in a trigger. A trigger on the root of the hierarchy can automate the invocation of the GENERATE_UNIQUE function. The following trigger will generate identifiers for inserts into the Person, Employee, Architect, and Manager tables.

  CREATE TRIGGER Gen_Person_oid
    NO CASCADE
    BEFORE INSERT ON Person
    REFERENCING NEW AS new
    FOR EACH ROW
    MODE DB2SQL
    SET new.oid = Person_t (generate_unique());