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

Microsoft™ SQL SERVER IDENTITY columns:

When creating a table, the IDENTITY keyword must be specified after the column data type:
CREATE TABLE tab1 ( k integer identity, c char(10) )
You can specify a start value and an increment with "identity(start,incr)".
CREATE TABLE tab1 ( k integer identity(100,2), ...
A new number is automatically created when inserting a new row:
INSERT INTO tab1 ( c ) VALUES ( 'aaa' )
To get the last generated number, Microsoft SQL SERVER provides following function:
SELECT SCOPE_IDENTITY()

The @@IDENTITY global T-SQL variable is not recommended, as it is scope-less.

To put a specific value into a IDENTITY column, the SET command must be used:

SET IDENTITY_INSERT tab1 ON
INSERT INTO tab1 ( k, c ) VALUES ( 100, 'aaa' )
SET IDENTITY_INSERT tab1 OFF

Informix SERIALs and MS SQL SERVER IDENTITY columns are quite similar; the main difference is that MS SQL SERVER does not allow you to use the zero value for the identity column when inserting a new row.

Starting with version 2012, Microsoft SQL SERVER supports sequences:

-- To create a sequence object:
CREATE SEQUENCE myseq START WITH 100 INCREMENT BY 1;

-- To get a new sequence value:
SELECT NEXT VALUE FOR myseq;

-- To find the current sequence value (last generated)
SELECT convert(bigint, current_value) FROM sys.sequences WHERE name = 'myseq';

-- To reset the sequence with a new start number:
ALTER SEQUENCE myseq START WITH 100;

Solution

To emulation Informix serials with SQL SERVER, you can use three different solutions:

Native SQL SERVER IDENTITY columns.
Insert triggers based on sequences (requires SQL SERVER 2012 and +).
Insert triggers based on the SERIALREG table (for SQL SERVER prior to 2012).

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"|"regtable"}

native: uses IDENTITY columns.
trigseq: uses insert triggers with SEQUENCEs.
regtable: uses insert triggers with the SERIALREG table.

The default emulation technique is "native".

This entry must be used in conjonction with:

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

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

The native IDENTITY-based solution is faster, but does not allow explicit serial value specification in insert statements; the others solution are slower but allow explicit serial value specification in INSERT statements.

Important: The trigger-based solutions are provided to simplify the conversion from Informix, but are slower as the solution using IDENTITY columns. To get best performances, we strongly recommend that you use native IDENTITY columns instead of triggers.

1. Using the `native` serial emulation

Make sure that the following FGLPROFILE entry is not defined, in order to use the default "native" emulation:

dbi.database.dbname.ifxemul.datatype.serial.emulation ...

In database creation scripts, all SERIAL[(n)] data types must be converted by hand to INTEGER IDENTITY[(n,1)] data types, while BIGSERIAL[(n)] data types must be converted by hand to BIGINT IDENTITY[(n,1)] data types.

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 IDENTITY[(n,1)]".

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 "SELECT SCOPE_IDENTITY()". 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 retrieve the last generated serial with the SCOPE_IDENTITY() SQL function.

By default (see SET IDENTITY_INSERT), MS SQL SERVER does not allow you to specify the IDENTITY column in INSERT statements; You must convert all INSERT statements to remove the identity 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 (DEFINE rec LIKE tab.*) must also be reviewed:

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

Since 2.10.06, SELECT * FROM table INTO TEMP with original table having an IDENTITY column is supported: The database driver converts the Informix SELECT INTO TEMP to the following sequence of statements:

SELECT selection-items INTO #table FROM ... WHERE 1=2
SET IDENTITY_ INSERT #table ON
INSERT INTO #table ( column-list ) SELECT original select clauses
SET IDENTITY_ INSERT #table OFF

2. Using the `trigseq` serial emulation (SQL SERVER 2012 and +)

In order to use the serial emulation based on triggers and sequences, make sure that all database users creating tables in program have permissions to create/drop sequences and triggers.

Define the FGLPROFILE entry to enable "trigseq" serial emulation:

dbi.database.dbname.ifxemul.datatype.serial.emulation = "trigseq"

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types, BIGSERIAL must be converted to BIGINT and you must create one trigger for each table. 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 using a sequence.

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 insert triggers. When using BIGSERIAL[(n)], the column is converted to a BIGINT.

Important:

SQL SERVER does not allow you to create triggers on temporary tables. Therefore, you cannot create temp tables with a SERIAL column when using this solution.
SELECT ... INTO TEMP statements using a table created with a SERIAL column do not automatically create the SERIAL triggers in the temporary table. The type of the column in the new table is INTEGER. Similarly, a BIGSERIAL column becomes BIGINT.
When a table is dropped, all associated triggers are also dropped.
INSERT statements using NULL for the SERIAL column will produce a new serial value, instead of using NULL:
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 if your application uses tables with a SERIAL column that can contain a NULL value.

3. Using the `regtable` serial emulation (SQL SERVER versions prior to 2012)

Note: This solution is supported for SQL SERVER versions prior to 2012, if your server is a SQL SERVER 2012 or +, consider using the "trigseq" emulation instead.

In order to use the serial emulation based on triggers and the SERIALREG table, make sure that all database users creating tables in program have permissions to create/drop triggers.

Then, prepare the database and create the SERIALREG table as follows:

CREATE TABLE serialreg (
    tablename VARCHAR(50) NOT NULL,
    lastserial BIGINT NOT NULL,
    PRIMARY KEY ( tablename )
)

The SERIALREG table and columns have to be created with lower case names, since the SQL SERVER database is created with case sensitive names, because triggers are using this table in lower case.

Define the FGLPROFILE entry to enable "regtable" serial emulation:

dbi.database.dbname.ifxemul.datatype.serial.emulation = "regtable"

Important:

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 1 and you will get unexpected data.
SQL SERVER does not allow you to create triggers on temporary tables. Therefore, you cannot create temp tables with a SERIAL column when using this solution.
SELECT ... INTO TEMP statements using a table created with a SERIAL column do not automatically create the SERIAL triggers in the temporary table. The type of the column in the new table is INTEGER. Similarly, a BIGSERIAL column becomes BIGINT.
When a table is dropped, all associated triggers are also dropped.
INSERT statements using NULL for the SERIAL column will produce a new serial value, instead of using NULL:
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 if your application uses tables with a SERIAL column that can contain a NULL value.

SERIAL data types

Solution

1. Using the native serial emulation

2. Using the trigseq serial emulation (SQL SERVER 2012 and +)

3. Using the regtable serial emulation (SQL SERVER versions prior to 2012)

1. Using the `native` serial emulation

2. Using the `trigseq` serial emulation (SQL SERVER 2012 and +)

3. Using the `regtable` serial emulation (SQL SERVER versions prior to 2012)