SERIAL and BIGSERIAL data types

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

SQLite

SQLite supports the AUTOINCREMENT attribute for columns:

• Only one column must be declared as INTEGER PRIMARY KEY AUTOINCREMENT.
• To get the last generated number, SQLite provides the sqlite_sequence table:

  SELECT seq FROM sqlite_sequence WHERE name='table_name'

• When inserting a zero in the auto-increment column, SQLite will not generate a new sequence like Informix does.
• When inserting a NULL in the auto-increment column, SQLite generates a new sequence; Informix denies NULLs in SERIALs.

Solution

When using SQLite, the SERIAL data type is converted to INTEGER PRIMARY KEY AUTOINCREMENT.

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

For more details see IBM Informix emulation parameters in FGLPROFILE.

Disabling automatic serial retrieval for SQLCA.SQLERRD[2]

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 (only option)

The SQLCA.SQLERRD[2] register is filled automatically after each INSERT with the last generated number, by fetching the value from the sqlite_sequence table.

SQLite (V 3.6) does not support auto-incremented BIGINT columns. Therefore, BIGSERIAL or SERIAL8 cannot be converted.

Because SQLite does not behave like Informix regarding zero and NULL value specification for auto-incremented columns, all INSERT statements must 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 parentheses, serial column is removed