SERIAL and BIGSERIAL data types

Informix®

Informix supports the SERIAL, BIGSERIAL data types to produce automatic integer sequences:

SERIAL can produce 32 bit integers (INTEGER)
BIGSERIAL can produced 64 bit integers (BIGINT)
SERIAL8 is a synonym for BIGSERIAL

Steps to use serials with Informix:

Create the table with a column using SERIAL, 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 value of a SERIAL column is provided in SQLCA.SQLERRD[2], while the new value of a 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

Netezza®

Netezza does not have a SERIAL data type.

Netezza Version 6 supports sequences, but not triggers.

Solution

The lack of trigger support in Netezza makes it impossible to emulate Informix SERIALs.

Note: For best SQL portability when using different types of databases, consider using sequences as described in Solution 3: Use native SEQUENCE database objects.

If you are using Informix SERIAL or BIGSERIAL columns, you must review the application logic and database schema to replace these columns with INTEGER/BIGINT columns, and generate the new keys from a SEQUENCE, as described in the SQL Programming page.