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 INSERTs 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

IBM^® DB2^® has no equivalent for Informix SERIAL columns.

DB2 version 7.1 supports IDENTITY columns:

CREATE TABLE tab ( k INTEGER GENERATED ALWAYS AS IDENTITY);

To get the last generated IDENTITY value after an INSERT, DB2 provides the following function:

IDENTITY_VAL_LOCAL()

DB2 version 8.1 supports SEQUENCES:

CREATE SEQUENCE sq1 START WITH 100;

To create a new sequence number, you must use the "NEXTVAL FOR" operator:

INSERT INTO table VALUES ( NEXTVAL FOR sq1, ... )

To get the last generated sequence number, you must use the "PREVVAL FOR" operator:

SELECT PREVVAL FOR sq1 ...

Solution

The choices are:

Use IDENTITY columns
Insert triggers using SEQUENCES (works only with DB2 version 8 and higher)

The first solution is faster, but does not allow explicit serial value specification in insert statements; the second solution is slower but allows explicit serial value specification. You can start to use the second solution to make unmodified 4gl programs work on DB2, but you should update your code to use native IDENTITY columns for performance.

The serial emulation type is defined by the following FGLPROFILE entry. The 'native' value defines the IDENTITY column technique and the 'trigseq' defines the trigger technique:

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

This entry must be used with:

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

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

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

Using IDENTITY columns
In database creation scripts, all SERIAL[(n)] data types must be converted by hand to:

INTEGER GENERATED ALWAYS AS IDENTITY[( START WITH n, INCREMENT BY 1)]

while the SERIAL8 and BIGSERIAL[(n)] types must be converted to:

BIGINT GENERATED ALWAYS AS IDENTITY[( START WITH n, INCREMENT BY 1)]

Tables created from the BDL programs can use the SERIAL data type: When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to an IDENTITY specification.

In BDL, the new generated SERIAL value is available from the SQLCA.SQLERRD[2] variable. This is supported by the database interface which performs a call to the IDENTITY_VAL_LOCAL() function. However, SQLCA.SQLERRD[2] is defined as an INTEGER, it cannot hold values from BIGINT identity columns. If you are using BIGINT IDENTITY columns, you must use the IDENTITY_VAL_LOCAL() function.

Since IBM DB2 does not allow you to specify the value of IDENTITY columns, it is mandatory to convert all INSERT statements to remove the SERIAL column from the list. For example, the following statement:

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

must 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 
```
must be converted to:

INSERT INTO tab VALUES rec.* -- without braces, serial column is removed
Using triggers with the SEQUENCE
In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types, SERIAL8/BIGSERIAL must be converted to BIGINT, and you must create a sequence and a trigger for each table using a SERIAL. 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 SQL commands to create the sequence and the trigger.

Tables created from the BDL programs can use the SERIAL data type: When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to "INTEGER" and creates the sequence and the insert trigger.

Note:

IBM DB2 performs NOT NULL data controls before the execution of triggers. If the serial column must be NOT NULL (for example, because it is part of the primary key), you cannot specify a NULL value for that column in INSERT statements.

For example, the following statement:

INSERT INTO tab VALUES (NULL,p_value)

must be converted to:

INSERT INTO tab (col2) VALUES (p_value)

Important:

IBM DB2 triggers are not automatically dropped when the corresponding table is dropped. They become inoperative instead. Database administrators must take care of this behavior when managing schemas.
With IBM DB2, INSERT statements using NULL for the SERIAL column will produce a new serial value:
INSERT INTO tab ( col_serial, col_data ) VALUES ( NULL, 'data' )

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

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

Check if your application uses tables with a SERIAL column that can contain a NULL value.
With DB2, trigger creation is not allowed on temporary tables. Therefore, the "trigseq" method cannot work with temporary tables using serials.