SERIAL and BIGSERIAL data type

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 ASE

SAP® ASE 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 cannot specify a start value
• A new number is automatically created when inserting a new row:

  INSERT INTO tab1 ( c ) VALUES ( 'aaa' ) 

• To get the last generated number, SAP ASE provides a global variable:

  SELECT @@IDENTITY 

• When IDENTITY_INSERT=ON, you can set a specific value into a IDENTITY column, but zero does not generate a new serial:

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

Informix SERIALs and SAP ASE IDENTITY columns are quite similar; the main difference is that SAP ASE does not generate a new serial when you specify a zero value for the identity column.

Solution

With SAP ASE, the SERIAL emulation can use IDENTITY columns (1) or insert triggers based on the SERIALREG table (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. You can initially use the second solution to have unmodified BDL programs working on SAP ASE, but it is recommended that you update your code to use native IDENTITY columns for performance.

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

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

The default emulation technique is "native".

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]

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 data types must be converted by hand to INTEGER IDENTITY data types, while BIGSERIAL must be converted to BIGINT IDENTITY.

Start values SERIAL(n) / BIGSERIAL(n) cannot be converted, there is no INTEGER IDENTITY(n) in SAP ASE.

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 @@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 use @@IDENTITY.

When you insert a row with zero as serial value, the serial column gets the value zero. You must review all INSERT statements using zero for the serial column. 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 

can be converted to:

INSERT INTO tab VALUES rec.* -- without parentheses, serial column is removed 

Using the regtable serial emulation

First, you must 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 )
) 

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

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.

SAP ASE does not allow you to create triggers on temporary tables. Therefore, you cannot create temp tables with a SERIAL column when using this solution.