Informix to SQL Server Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Informix to Microsoft SQL Server (MSSQL), SQL Azure, Synapse and Fabric.

Databases:

  • Informix 14.x, 12.x, 11.x, 10.x, 9.x and 7.x (Dynamic Server IDS and Extended Parallel Server XPS)
  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 and 2005

Migration Reference

SQL Language Elements

Converting SQL language elements from Informix to SQL Server:

Informix SQL Server
1 { comment } /* comment */
2 string[start, end] Substring operator [] SUBSTRING(string, start, end - start + 1)
string[start] SUBSTRING(string, start, 1)
3 DBINFO('sqlca.sqlerrd2') Get the number of affected rows @@ROWCOUNT

Data Types

Character data types:

Informix SQL Server
1 CHAR(n), CHARACTER(n) Fixed-length string, 1 <= n <= 32767 CHAR(n), CHARACTER(n) n <= 8000
VARCHAR(max) n > 8000
2 CHARACTER VARYING(n,r) Variable-length string, 1 <= n <= 255 CHARACTER VARYING(n)
3 LVARCHAR(n) Variable-length string, 1 <= n <= 32739 VARCHAR(n)
4 NCHAR(n) Fixed-length string, 1 <= n <= 32767 NCHAR(n)
5 NVARCHAR(n,r) Variable-length string, 1 <= n <= 255 NVARCHAR(n)
6 VARCHAR(n,r) Variable-length string, 1 <= n <= 255 VARCHAR(n)

Other data types:

Informix SQL Server
1 BIGINT 64-bit integer BIGINT
2 BIGSERIAL(s) Auto-increment 64-bit integer BIGINT
3 BLOB Binary large object, ⇐ 4T VARBINARY(max)
4 BOOLEAN True, false or NULL BIT
5 BYTE Binary data, ⇐ 2G VARBINARY(max)
6 CLOB Character large object, ⇐ 4T VARCHAR(max)
7 DATE Date (year, month and day) DATE
8 DATETIME unit TO unit2 Date and time with fraction DATETIME2
9 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
10 DOUBLE PRECISION Double-precision floating-point number FLOAT
11 FLOAT(p) Double-precision floating-point number FLOAT
12 INTEGER, INT 32-bit integer INTEGER, INT
13 INT8 64-bit integer BIGINT
14 INTERVAL unit TO unit2 Date and time interval VARCHAR(30)
15 MONEY(p,s) Currency amount MONEY
16 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
17 REAL Single-precision floating-point number REAL
18 SMALLFLOAT Single-precision floating-point number REAL
19 SMALLINT 16-bit integer SMALLINT
20 SERIAL(s) Auto-increment 32-bit integer INT
21 SERIAL8(s) Auto-increment 64-bit integer BIGINT
22 TEXT Character data, ⇐ 2G VARCHAR(max)

Data type attributes and options:

Informix SQL Server
1 column BYTE IN TABLE column VARBINARY(max)
2 column BYTE IN lob_space column VARBINARY(max)
3 column TEXT IN TABLE column VARCHAR(max)
4 column TEXT IN lob_space column VARCHAR(max)

Built-in SQL Functions

Converting built-in SQL functions:

Informix SQL Server
1 CURRENT Get the current date and time GETDATE()
2 DBINFO('sqlca.sqlerrd2') Get the number of affected rows @@ROWCOUNT
3 DECODE(exp, when, then, …, else) Evaluate conditions CASE exp WHEN when THEN then
ELSE else END
4 LEN(string) Get string length LEN(string)
LENGTH(string)
5 TRIM(string) Remove leading and trailing spaces RTRIM(LTRIM(string))

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

Informix SQL Server
1 Primary key columns are changed to NOT NULL NOT NULL constraint must be specified explicitly
2 PRIMARY KEY (c1, c2, …) CONSTRAINT schema.name CONSTRAINT name PRIMARY KEY (c1, c2, …) No schema

SELECT Statement

Converting SQL queries from Informix to SQL Server:

Informix SQL Server
1 SELECT FIRST n Return n rows after sorting SELECT TOP n
2 OUTER clause Outer join syntax ANSI SQL OUTER JOIN clause

GROUP BY clause:

Informix SQL Server
1 SELECT c1, c2, … FROM t GROUP BY 1, 2 Positional reference SELECT c1, c2, … FROM t GROUP BY c1, c2

CREATE PROCEDURE Statement

Converting stored procedures from Informix to SQL Server:

Informix SQL Server
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 name() When without parameters name
3 OUT | INOUT param datatype(len) DEFAULT default @param datatype(len) = default OUT
4 RETURNING datatype Scalar return value Converted to CREATE FUNCTION
RETURN WITH RESUME Multiple rows returned Converted to a table-valued function
5 No AS keyword before the statements block AS is added
6 END PROCEDURE; End of procedure block GO

For more information, see Conversion of Procedural Statements.

Procedural SQL Statements

Converting procedural SQL statements (SPL) used in stored procedures, functions and triggers from Informix to SQL Server.

Variable declaration and assignment:

Informix SQL Server
1 variable LIKE table.column Inherited data type @variable datatype
2 DEFINE var datatype(len) Variable declaration DECLARE @var datatype(len)
3 LET var = value; Assignment statement SET @var = value;
4 SELECT col INTO var FROM Select a single row SELECT @var = col FROM

Flow-of-control statements:

Informix SQL Server
1 FOREACH [cur FOR] select INTO vars
stmt END FOREACH
Query loop DECLARE cur CURSOR FOR select;
OPEN-WHILE-FETCH-CLOSE
2 IF condition THEN … END IF; IF statement IF condition BEGIN … END

Database Migration Issues