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 |
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:
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.