Informatica

Guide to Migrating from Informix to SQL Server 2008
SQL Server Technical Article

Writers: Arthur Alchangian (DB Best Technologies), Galina Shevchenko (DB Best Technologies), Yuri Tumakov (DB Best Technologies), Yuri Rusakov (DB Best Technologies)
Technical Reviewer: Dmitry Balin (DB Best Technologies)

Published: August 2009
Applies to: SQL Server 2008 and SQL Server 2008 R2

Summary:This white paper explores challenges that arise when you migrate from an Informix 11 database to SQL Server 2008. It describes the implementation differences of database objects and procedural code between the two platforms. Emulation of system functions is also discussed.

Created by: DB Best Technologies LLC
P.O. Box 7461, Bellevue, WA 98008
Tel.: (408) 202-4567
E-mail: info@dbbest.comWeb: http://www.dbbest.com

Copyright
This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it shouldnot be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of theuser. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications,trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names,e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.

© 2009 Microsoft Corporation. All rights reserved.
Microsoft and SQL Server are registered trademarks of Microsoft Corporation in the United States andother countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Contents
Introduction 6
Migrating Informix Data Types 7
Type Mapping 7
Data Type Migration Issues 9
Time Data Types 9
Issue: DATETIME Field Qualifier 9
Issue: Serial Data Type Columns 9
Issue: INTERVAL Data Type and INTERVAL Field Qualifier 10
ComplexData Types 11
Issue: Collection Data Types 11
Issue: ROW Data Types 11
User-Defined Data Types 12
Issue: DISTINCT Types 12
Issue: OPAQUE Types 12
Migration of Table Structure 14
CREATE TABLE Statement 14
Issue: RAW Keyword in the CREATE TABLE Statement 14
Issue: The OPERATIONAL Keyword in the CREATE TABLE Statement 14
Issue: The STATIC Keyword in the CREATE TABLE Statement 14Issue: DISTINCT Keyword 14
Issue: Constraints Syntax 15
Issue: Default Values for the Referenced Column 17
Issue: CREATE TABLE Options 18
Issue: PUT clause 19
Issue: USING Access-Method Clause 19
Issue: LOCK MODE Options 19
Issue: OF TYPE clause 20
Issue: UNDER Clause in a Definition of a Typed Table 21
CREATE TEMP TABLE Statement 21
Issue: WITH NO LOG Option 21
Issue:...