Informix Migration - How to Get Next SERIAL Value

In Informix you can use a SERIAL data type to assign auto-incrementing IDs to a column:

Informix:

  CREATE TABLE teams
  (
     id SERIAL,
     name VARCHAR(70)
  );
 
  -- Let's insert a few rows
  INSERT INTO teams VALUES (0, 'Baltimore Ravens');
  INSERT INTO teams VALUES (0, 'San Francisco 49ers');

When 0 value is inserted into a SERIAL column, Informix inserts the next ID (a 32-bit integer value):

Table data

id name
1 Baltimore Ravens
2 San Francisco 49ers

Define Next ID

When you migrate an Informix database into another database such as Oracle, SQL Server i.e you have to start sequence/identity column with appropriate values.

The easiest way is to use MAX aggregate function, but it requires a separate query (subquery) for each table having a SERIAL column:

Informix:

  SELECT MAX(id) + 1 
  FROM teams;
  # Returns: 3

Another way to get the next SERIAL value is to use a catalog query:

  SELECT serialv
  FROM sysmaster:sysptnhdr p, systables t
  WHERE p.partnum = t.partnum AND tabname = 'teams'
  # Returns: 3

This single catalog query allows you to get SERIAL values for multiple tables.

Note. These approaches can be used only if there are no inserts to the table in other concurrent sessions.

See also:

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.

You could leave a comment if you were logged in.