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:
  1. Create the table with a column using SERIAL, or BIGSERIAL.
  2. 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' )
  3. 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

SAP HANA®

SAP HANA supports IDENTITY columns:

CREATE TABLE tab (
   k BIGINT GENERATED BY DEFAULT AS IDENTITY
     (START WITH 101 INCREMENT BY 1) NOT NULL,
   ...
 )
To get the last generated IDENTITY value after an INSERT, SAP HANA provides the CURRENT_IDENTITY_VALUE() function:
INSERT INTO table_with_identity_column VALUES (...)
SELECT CURRENT_IDENTITY_VALUE() FROM DUMMY

SAP HANA supports SEQUENCES:

CREATE SEQUENCE seq1 START WITH 100

To create a new sequence number, you must use the "sequence-name.NEXTVAL" expression:

INSERT INTO table VALUES ( seq1.NEXTVAL, ... )

To get the last generated sequence number, you must use the "sequence-name.CURRVAL" expression:

SELECT seq1.CURRVAL FROM DUMMY

Solution

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

To emulate Informix serials with SAP HANA, you can use IDENTITY columns (1), or insert triggers using sequences (2). 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.

Important: The trigger-based solution is provided to simplify the conversion from Informix, but it is slower than the solution using identity columns. We strongly recommend that you use native IDENTITY columns instead to get best performances.
The method used to emulate SERIAL types is defined by the ifxemul.datatype.serial.emulation FGLPROFILE parameter:
dbi.database.dbname.ifxemul.datatype.serial.emulation = {"native"|"trigseq"}
  • native: uses IDENTITY columns.
  • trigseq: uses insert triggers with sequences.

The default emulation technique is "native".

The serial types emulation can be enabled or disabled with the following FGLPROFILE entries:
dbi.database.dbname.ifxemul.datatype.serial = {true|false}
dbi.database.dbname.ifxemul.datatype.serial8 = {true|false}
dbi.database.dbname.ifxemul.datatype.bigserial = {true|false}

Disabling automatic serial retrieval for SQLCA.SQLERRD[2]

SERIAL emulation can be totally disabled by setting the ifxemul.datatype.serial FGLPROFILE entry to false:
dbi.database.dbname.ifxemul.datatype.serial = false

For Informix compatibility, after an INSERT statement, the ODI drivers automatically execute another SQL query (or do a DB client API call when possible), to get the last generated serial, and fill the SQLCA.SQLERRD[2] register. This results in some overhead that can be avoided, if the SQLCA.SQLERRD[2] register is not used by the program.

When SERIAL emulation is required (to create temp tables with a serial column during program execution), and the SQLCA.SQLERRD[2] register does not need to be filled, (typically because you use your own method to retrieve the last generated serial), you can set the ifxemul.datatype.serial.sqlerrd2 FGLPROFILE entry to false. This will avoid the automatic retrieval of last serial value to fill SQLCA.SQLERRD[2]:

dbi.database.dbname.ifxemul.datatype.serial.sqlerrd2 = false

See also db_get_last_serial().

Using the native serial emulation

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)]
Note: Unlike other database types, the syntax of SAP HANA IDENTITY options does not use the comma.

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: The database interface which performs a call to the CURRENT_IDENTITY_VALUE() 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.

The SAP HANA GENERATED ALWAYS AS IDENTITY feature 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 parentheses, serial column is removed

Using the trigseq serial emulation

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER DEFAULT 0 data types, SERIAL8/BIGSERIAL must be converted to BIGINT DEFAULT 0, 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.

Important:
  • With SAP HANA, trigger creation is not allowed on temporary tables. Therefore, the "trigseq" method cannot work with temporary tables using serials.