SERIAL data types

Informix^® supports the SERIAL, SERIAL8 and BIGSERIAL data types to produce automatic integer sequences. SERIAL is based on INTEGER (32 bit), while SERIAL8 and BIGSERIAL can store 64 bit integers:

The table column must be of type SERIAL, SERIAL8 or BIGSERIAL.
To generate a new serial, no value or a zero value is specified in the INSERT statement:
```
INSERT INTO tab1 ( c ) VALUES ( 'aa' )
INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
```
After INSERT, the new SERIAL value is provided in SQLCA.SQLERRD[2], while the new SERIAL8 and BIGSERIAL value must be fetched with a SELECT dbinfo('bigserial') query.

Informix allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERT statements that are using a zero value:

CREATE TABLE tab ( k SERIAL); -- internal counter = 0
INSERT INTO tab VALUES ( 0 ); -- internal counter = 1
INSERT INTO tab VALUES ( 10 ); -- internal counter = 10
INSERT INTO tab VALUES ( 0 ); -- internal counter = 11
DELETE FROM tab; -- internal counter = 11
INSERT INTO tab VALUES ( 0 ); -- internal counter = 12

ORACLE sequences:

Sequences are totally detached from tables.
The purpose of sequences is to provide unique integer numbers.
Sequences are identified by a sequence name.
To create a sequence, you must use the CREATE SEQUENCE statement. Once a sequence is created, it is permanent (like a table).
To get a new sequence value, you must use the nextval keyword, preceded by the name of the sequence. The seqname.nextval expression can be used in INSERT statements:
INSERT INTO tab1 VALUES ( tab1_seq.nextval, ... )
To get the last generated number, ORACLE provides the currval keyword:
SELECT seqname.currval FROM DUAL

In order to improve performance, ORACLE can handle a set of sequences in the cache (See CREATE SEQUENCE syntax in the ORACLE documentation).

Solution

When using Oracle, the SERIAL data type can be emulated with INSERT TRIGGERs. In BDL programs, the SQLCA structure is filled as expected: After an insert,SQLCA.SQLERRD[2] holds the last generated serial value. However, SQLCA.SQLERRD[2] is defined as an INTEGER, it cannot hold values from BIGSERIAL (NUMBER(20)) auto-incremented columns. If you are using BIGSERIAL columns, you must the fetch the sequence pseudo-column CURR_VAL or fetch the LASTSERIAL column from the SERIALREG table if used.

The triggers can be created manually during the database creation procedure, or automatically from a BDL program. When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the Oracle interface automatically converts the SERIAL data type to NUMBER(10,0) and dynamically creates the trigger. For temporary tables, the trigger is dropped automatically after a "DROP TABLE temptab" or when the program disconnects from the database.

Users executing programs which create tables with SERIAL columns must have the CONNECT and RESOURCE roles assigned to create triggers and sequences.

In database creation scripts, all SERIAL[(n)] data types must be converted to NUMBER(10,0) data types, and you must create the triggers (and the sequences when using sequence-based triggers). SERIAL8[(n)] and BIGSERIAL[(n)] must be replaced by NUMBER(20,0).

With Oracle, INSERT statements using NULL for the SERIAL column will produce a new serial value:

INSERT INTO tab (col1,col2) VALUES (NULL,'data')

This behavior is mandatory in order to support INSERT statements which do not use the serial column:

INSERT INTO tab (col2) VALUES ('data')

Check whether your application uses tables with a SERIAL column that can contain a NULL value.

For SQL portability, INSERT statements should be reviewed to remove the SERIAL column from the list. For example, the following statement:

INSERT INTO tab (col1,col2) VALUES (0, p_value)

can be converted to:

INSERT INTO tab (col2) VALUES (p_value)

Static SQL INSERT using records defined from the schema file must also be reviewed:

DEFINE rec LIKE tab.* INSERT INTO tab VALUES ( rec.*) -- will use the serial column

can be converted to:

INSERT INTO tab VALUES rec.* -- without braces, serial column is removed

When using the Static SQL INSERT or UPDATE syntax using record.* without braces, make sure that you database schema files contain information about serials: This information can be lost when extracting the schema from an Oracle database. See Database Schema for more details about the serial flag in column type encoding (data type code must be 6).

Since the Informix SERIAL data type simulation is implemented in the ORACLE database, inserting rows with ORACLE tools like SQL*Plus or SQL*Loader will raise the INSERT triggers. When loading big tables, you can disable triggers with ALTER TRIGGER [ENABLE | DISABLE] (see ORACLE documentation for more details). After reactivation of the serial triggers, the SERIAL sequences must be re-initialized (use serialpkg.create_sequence('tab','col') or re-execute the PL/SQL script containing the sequence and trigger creation.

You are free to use SEQUENCE based insert triggers (1) or SERIALREG based insert triggers (2). The second solution needs the SERIALREG table to register serials.

With the following fglprofile entry, you define the technique to be used for SERIAL emulation:

dbi.database.dbname.ifxemul.datatype.serial.emulation = {"native"|"regtable"}

The 'native' value defines the SEQUENCE-based technique and the 'regtable' defines the SERIALREG-based technique.

This entry must be used with:

dbi.database.dbname.ifxemul.serial = {true|false}

If this entry is set to false, the emulation method specification entry is ignored.

When no entry is specified, the default is SERIAL emulation enabled with 'native' method (SEQUENCE-based).

Using SEQUENCES based triggers
Each table having a SERIAL column needs an INSERT TRIGGER and a SEQUENCE dedicated to SERIAL generation.

To know how to write those sequences and triggers, you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native SQL commands to create the sequence and the trigger.
Using SERIALREG based triggers
Each table having a SERIAL column needs an INSERT TRIGGER which uses the SERIALREG table dedicated to SERIAL registration.

First, you must prepare the database and create the SERIALREG table as follows:
```
CREATE TABLE serialreg (
    tablename VARCHAR2(50) NOT NULL,
    lastserial NUMBER(20,0) NOT NULL,
    PRIMARY KEY ( tablename )
)
```

Important: This table must exist in the database before creating the serial triggers.

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types and you must create one trigger for each table. SERIAL8/BIGSERIAL columns must be converted to NUMBER(20,0). To know how to write those triggers, you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native trigger creation command.

The serial production is based on the SERIALREG table which registers the last generated number for each table. If you delete rows of this table, sequences will restart at start values and you might get duplicated values.