Migrating From Oracle . . . To IBM Informix Dynamic Server On Linux .
Front coverMigrating from Oracle . . . toIBM Informix Dynamic Serveron Linux, UNIX, and WindowsDeveloping a Data and ApplicationsMigration MethodologyUnderstanding IDS and OracleDBMS FunctionalityUsing the IBM MigrationTool Kit as Your GuideChuck BallardHolger KirsteinSrinivasrao MadirajuSreeni PaidiNora SokolofRenato Spironelliibm.com/redbooks
International Technical Support OrganizationMigrating from Oracle . . . to IBM Informix DynamicServer on Linux, UNIX, and WindowsJune 2009SG24-7730-00
Note: Before using this information and the product it supports, read the information in“Notices” on page ix.First Edition (June 2009)This edition applies to Oracle 10g and IBM Informix Dynamic Server 11. Copyright International Business Machines Corporation 2009. All rights reserved.Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADPSchedule Contract with IBM Corp.
ContentsNotices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ixTrademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xPreface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiThe team that wrote this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiiBecome a published author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xivComments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xivChapter 1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.1 Migrating . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.2 Positioning IDS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.3 Informix Dynamic Server editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.4 IDS functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9Chapter 2. Architectural overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152.1 The basic architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162.1.1 Memory architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172.1.2 Process architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232.1.3 Physical database structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282.1.4 Logical database structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322.1.5 Data dictionary and system catalog . . . . . . . . . . . . . . . . . . . . . . . . . 352.1.6 Database server communication. . . . . . . . . . . . . . . . . . . . . . . . . . . . 382.2 IDS licensing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402.3 Terminology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Chapter 3. Migration methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433.1 An IBM migration methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443.2 Migration preparation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453.2.1 Performing the migration assessment. . . . . . . . . . . . . . . . . . . . . . . . 453.2.2 Understanding and selecting migration tools . . . . . . . . . . . . . . . . . . 463.2.3 Estimating the effort required . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473.2.4 Environment preparation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483.2.5 Getting educated on the Informix Dynamic Server . . . . . . . . . . . . . . 493.3 Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493.3.1 Database migration and design . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493.3.2 Calibration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503.3.3 Application migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513.4 The Test Phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523.4.1 Migration refresh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Copyright IBM Corp. 2009. All rights reserved.iii
3.4.2 Data migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523.4.3 Testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533.5 Implementation and cutover phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563.6 Related information resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Chapter 4. IBM Migration Tool Kit: An introduction . . . . . . . . . . . . . . . . . . 594.1 The MTK for Oracle migrations to IDS . . . . . . . . . . . . . . . . . . . . . . . . . . . 604.2 Overview of features and functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . 614.2.1 The five step migration process . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614.3 Inside the Oracle converter component . . . . . . . . . . . . . . . . . . . . . . . . . . 664.3.1 Translating tables, indexes, and views . . . . . . . . . . . . . . . . . . . . . . . 684.3.2 Translating built-in functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684.4 How to install, configure, and execute the MTK . . . . . . . . . . . . . . . . . . . . 694.4.1 System requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 704.4.2 Installing MTK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 714.4.3 Starting MTK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Chapter 5. An MTK tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 735.1 Part 1: Core database object migration. . . . . . . . . . . . . . . . . . . . . . . . . . . 755.1.1 Create a project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755.1.2 Work with the project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775.1.3 Other useful features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 915.1.4 Additional MTK features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 995.1.5 Summary of best practices when using the MTK . . . . . . . . . . . . . . 1005.2 Part II: Database application object migration . . . . . . . . . . . . . . . . . . . . . 1015.2.1 Migration of application objects: Lessons learned . . . . . . . . . . . . . 109Chapter 6. SQL considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1116.1 DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1126.1.1 Database creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1126.1.2 Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1136.1.3 Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1296.1.4 Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1306.1.5 Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1316.1.6 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1336.1.7 DBLinks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1336.2 DML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1356.2.1 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1356.2.2 Selects . . . . . . . . . . . . . . . . . . . . . . . . . 1356.2.3 Pseudo-columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1386.2.4 Inserts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1426.2.5 Outer joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1426.2.6 Sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1456.2.7 Aliases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146ivMigrating from Oracle . . . to IBM Informix Dynamic Server on Linux, Unix and Windows
6.2.8 Truncate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1466.2.9 Hierarchical queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1476.3 SPL and PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1476.4 Concurrency and transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1546.4.1 Read concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1546.4.2 Update concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1586.5 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1596.5.1 User authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1596.5.2 Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1606.5.3 Column-level encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160Chapter 7. Data conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1657.1 Data conversion process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1667.2 Time planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1677.3 Database schema conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1677.3.1 Database schema extraction and conversion with the MTK . . . . . . 1687.3.2 Database schema extraction with Oracle database interfaces . . . . 1687.3.3 Move the database schema to the target IDS database server . . . 1717.4 Data movement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1797.4.1 Unloading the data in Oracle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1797.4.2 Load the data into the target IDS database server . . . . . . . . . . . . . 1977.4.3 Moving data using the Migration Tool Kit . . . . . . . . . . . . . . . . . . . . 2097.5 Alternative ways for moving data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2097.5.1 IBM InfoSphere Information Server. . . . . . . . . . . . . . . . . . . . . . . . . 209Chapter 8. Application conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2118.1 Heterogeneous application environments . . . . . . . . . . . . . . . . . . . . . . . . 2128.2 Client development APIs supported by IDS 11 . . . . . . . . . . . . . . . . . . . . 2128.2.1 Embedded ESQL/C. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2138.2.2 Embedded ESQL/Cobol . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2138.2.3 Informix JDBC 3.0 Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2138.2.4 IBM Informix .NET Provider . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2148.2.5 IBM Informix ODBC 3.0 Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2158.2.6 IBM Informix OLE DB Provider . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2168.2.7 IBM Informix Object Interface for C . . . . . . . . . . . . . . . . . . . . . . . 2168.2.8 Additional APIs for accessing IDS 11 . . . . . . . . . . . . . . . . . . . . . . . 2178.3 Migrating applications using unified interfaces . . . . . . . . . . . . . . . . . . . . 2188.3.1 Package applications migration planning . . . . . . . . . . . . . . . . . . . . 2188.3.2 Migrating applications based on ODBC . . . . . . . . . . . . . . . . . . . . . 2198.3.3 Migrating database applications based on JDBC . . . . . . . . . . . . . . 2218.4 Conversion considerations for common client APIs . . . . . . . . . . . . . . . . 2228.4.1 Application migration planning for source owned applications . . . . 2238.5 Introduction to programming techniques . . . . . . . . . . . . . . . . . . . . . . . . . 226Contentsv
8.5.1 Embedded SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2268.6 Migrate user-built applications. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2298.6.1 Converting Oracle Pro*C applications to Informix ESQL/C. . . . . . . 2298.6.2 Converting Oracle Java applications to IDS . . . . . . . . . . . . . . . . . . 2408.6.3 Converting Oracle Call Interface (OCI) applications . . . . . . . . . . . . 2488.6.4 Converting ODBC applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2548.6.5 Converting Perl applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2548.6.6 Converting PHP applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2598.6.7 Converting .NET applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270Chapter 9. Administration of Informix Dynamic Server. . . . . . . . . . . . . . 2799.1 Administering the Informix database server . . . . . . . . . . . . . . . . . . . . . . 2809.1.1 Configuring the database server . . . . . . . . . . . . . . . . . . . . . . . . . . . 2809.1.2 Set environment variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2809.1.3 Configure connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2829.1.4 Start and administer the database server . . . . . . . . . . . . . . . . . . . . 2839.1.5 Preparing to connect to applications . . . . . . . . . . . . . . . . . . . . . . . . 2859.1.6 Creating storage spaces and chunks . . . . . . . . . . . . . . . . . . . . . . . 2869.2 Data recovery and high availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2869.2.1 Backup and restore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2879.2.2 Fast recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2899.2.3 Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2909.2.4 Data replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2909.3 Informix Dynamic Server admin utilities . . . . . . . . . . . . . . . . . . . . . . . . . 2969.3.1 Command line utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2969.3.2 OpenAdmin tool for IDS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3029.3.3 IBM Informix Server Administrator . . . . . . . . . . . . . . . . . . . . . . . . . 3049.4 Automatic monitoring and corrective actions. . . . . . . . . . . . . . . . . . . . . . 3059.4.1 Administration API. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3059.4.2 The Scheduler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3069.4.3 The sysadmin database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3079.4.4 Query drill-down . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3079.5 IDS database server security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3089.5.1 Server utility and directory security . . . . . . . . . . . . . . . . . . . . . . . . . 3089.5.2 Network data encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3099.5.3 Connection security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3109.5.4 Label-based access control (Enterprise Edition). . . . . . . . . . . . . . . 3109.5.5 Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311Appendix A. Data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313A.1 Supported SQL data types in C/C . . . . . . . . . . . . . . . . . . . . . . . . . . . 314A.2 Supported SQL data types in Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316A.3 Mapping Oracle data types to Informix data types . . . . . . . . . . . . . . . . . 318viMigrating from Oracle . . . to IBM Informix Dynamic Server on Linux, Unix and Windows
Appendix B. Terminology mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321Appendix C. Function mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327C.1 Numeric function mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328C.2 Character function mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330C.3 Date and time function mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332C.4 Comparison and NULL-related function mapping. . . . . . . . . . . . . . . . . . 333C.5 Encoding, decoding, encryption, and decryption function mapping . . . . 335C.6 Implementation of new C-based functions in IDS. . . . . . . . . . . . . . . . . . 335Appendix D. Database server monitoring . . . . . . . . . . . . . . . . . . . . . . . . . 339D.1 Memory monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340D.2 Process utilization and configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . 342D.3 Disk space monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344D.4 Session monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346D.5 Cache monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348Appendix E. Database server utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351Appendix F. Additional material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355Locating the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355Using the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356System requirements for downloading the Web material . . . . . . . . . . . . . 356How to use the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357Abbreviations and acronyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361Related publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365IBM Redbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365Other publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365Online resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365Education support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366How to get Redbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367Help from IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369Contentsvii
viiiMigrating from Oracle . . . to IBM Informix Dynamic Server on Linux, Unix and Windows
NoticesThis information was developed for products and services offered in the U.S.A.IBM may not offer the products, services, or features discussed in this document in other countries. Consultyour local IBM representative for information on the products and services currently available in your area.Any reference to an IBM product, program, or service is not intended to state or imply that only that IBMproduct, program, or service may be used. Any functionally equivalent product, program, or service thatdoes not infringe any IBM intellectual property right may be used instead. However, it is the user'sresponsibility to evaluate and verify the operation of any non-IBM product, program, or service.IBM may have patents or pending patent applications covering subject matter described in this document.The furnishing of this document does not give you any license to these patents. You can send licenseinquiries, in writing, to:IBM Director of Licensing, IBM Corporation, North Castle Drive, Armonk, NY 10504-1785 U.S.A.The following paragraph does not apply to the United Kingdom or any other country where suchprovisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATIONPROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS ORIMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT,MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimerof express or implied warranties in certain transactions, therefore, this statement may not apply to you.This information could include technical inaccuracies or typographical errors. Changes are periodically madeto the information herein; these changes will be incorporated in new editions of the publication. IBM maymake improvements and/or changes in the product(s) and/or the program(s) described in this publication atany time without notice.Any references in this information to non-IBM Web sites are provided for convenience only and do not in anymanner serve as an endorsement of those Web sites. The materials at those Web sites are not part of thematerials for this IBM product and use of those Web sites is at your own risk.IBM may use or distribute any of the information you supply in any way it believes appropriate withoutincurring any obligation to you.Information concerning non-IBM products was obtained from the suppliers of those products, their publishedannouncements or other publicly available sources. IBM has not tested those products and cannot confirmthe accuracy of performance, compatibility or any other claims related to non-IBM products. Questions onthe capabilities of non-IBM products should be addressed to the suppliers of those products.This information contains examples of data and reports used in daily business operations. To illustrate themas completely as possible, the examples include the names of individuals, companies, brands, and products.All of these names are fictitious and any similarity to the names and addresses used by an actual businessenterprise is entirely coincidental.COPYRIGHT LICENSE:This information contains sample application programs in source language, which illustrate programmingtechniques on various operating platforms. You may copy, modify, and distribute these sample programs inany form without payment to IBM, for the purposes of developing, using, marketing or distributing applicationprograms conforming to the application programming interface for the operating platform for which thesample programs are written. These examples have not been thoroughly tested under all conditions. IBM,therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. Copyright IBM Corp. 2009. All rights reserved.ix
TrademarksIBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International BusinessMachines Corporation in the United States, other countries, or both. These and other IBM trademarkedterms are marked on their first occurrence in this information with the appropriate symbol ( or ),indicating US registered or common law trademarks owned by IBM at the time this information waspublished. Such trademarks may also be registered or common law trademarks in other countries. A currentlist of IBM trademarks is available on the Web at http://www.ibm.com/legal/copytrade.shtmlThe following terms are trademarks of the International Business Machines Corporation in the United States,other countries, or both:AIX 5L AIX C-ISAM DataBlade DataStage DB2 Universal Database DB2 Distributed Relational DatabaseArchitecture DRDA IBM Informix InfoSphere OS/390 POWER Rational Redbooks Redbooks (logo)WebSphere The following terms are trademarks of other companies:Oracle, JD Edwards, PeopleSoft, Siebel, and TopLink are registered trademarks of Oracle Corporationand/or its affiliates.ACS, Interchange, Red Hat, and the Shadowman logo are trademarks or registered trademarks of Red Hat,Inc. in the U.S. and other countries.SAP, and SAP logos are trademarks or registered trademarks of SAP AG in Germany and in several othercountries.EJB, Enterprise JavaBeans, J2EE, J2SE, Java, JavaBeans, JDBC, JDK, JRE, JVM, Solaris, and allJava-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, orboth.ActiveX, Expression, Microsoft, SQL Server, Visual C#, Visual J#, Visual Studio, Windows Server, WindowsVista, Windows, and the Windows logo are trademarks of Microsoft Corporation in the United States, othercountries, or both.Intel, Intel logo, Intel Inside logo, and Intel Centrino logo are trademarks or registered trademarks of IntelCorporation or its subsidiaries in the United States, other countries, or both.UNIX is a registered trademark of The Open Group in the United States and other countries.Linux is a trademark of Linus Torvalds in the United States, other countries, or both.Other company, product, or service names may be trademarks or service marks of others.xMigrating from Oracle . . . to IBM Informix Dynamic Server on Linux, Unix and Windows
PrefaceIn this IBM Redbooks publication, we discuss considerations, and describe amethodology, for transitioning from Oracle 10g to the Informix Dynamic Server(IDS). We focus on the basic topic areas of data, applications, andadministration, providing information about the differences in features andfunctionality in areas such as data types, data manipulation language (DML),data definition language (DDL), and Stored Procedures. Understanding thefeatures and functionality of the two products will assist in developing a migrationplan.We provide a conversion methodology and discuss the processes for installingand using the IBM Migration Toolkit (MTK) to migrate the database objects anddata from Oracle to IDS. We also illustrate, with examples, how to convert storedprocedures, functions, and triggers. Application programming and conversionconsiderations are also discussed.In addition, you will find script conversion samples for data loading, databaseadministration, and reports. There is also information regarding procedures andtips for migration testing and database tuning. The laboratory examples areperformed under Oracle 10g and IDS Version 11.5. However, the migrationprocess and examples can also be applied to Oracle 7, 8, and 9i.With this information, you can gather and document your conversionrequirements, develop your required transition methodology, and plan andexecute the conversion activities in an orderly and cost-effective manner. Copyright IBM Corp. 2009. All rights reserved.xi
The team that wrote this bookThis book was produced by a team of specialists from around the world workingwith the International Technical Support Organization, in San Jose California.The team members are depicted below, with a short biographical sketch of each:Chuck Ballard is a Project Manager at the InternationalTechnical Support organization, in San Jose, California. Hehas over 35 years experience, holding positions in the areasof Product Engineering, Sales, Marketing, TechnicalSupport, and Management. His expertise is in the areas ofdatabase, data management, data warehousing, businessintelligence, and process re-engineering. He has writtenextensively on these subjects, taught classes, andpresented at conferences and seminars worldwide. Chuckhas both a Bachelors degree and a Masters degree in Industrial Engineeringfrom Purdue University.Holger Kirstein is a resolution team engineer with theEuropean Informix support team. He joined the Informixsupport team in 1996 and has over 15 years experience inapplication development and support for Informix databaseservers and Informix clients. He holds a Masters of AppliedComputer Science from Technische Universität, Dresden.Srinivasrao Madiraju is a senior software engineer workingon the Informix Dynamic Server (IDS) integration team. Heis responsible for quality integration of new features intoIDS, particularly in the SQL area. He has recently beeninvolved in Cloud Computing Technology with IDS. Srini hasalso participated in projects focused on high availability,virtualization and cluster technologies, and working with anumber of hardware vendors. Srini holds a Masters degreein Computer Applications from Osmania University in India.xiiMigrating from Oracle . . . to IBM Informix Dynamic Server on Linux, Unix and Windows
Sreeni Paidi has over 12 years of experience working onInformix, Oracle, and DB2 database servers as aDatabase Programmer, DBA, Database IntegrationArchitect, and Partner Enablement Consultant. He workedwith technology partners and customers around the worldon various database related topics such as databaseporting, performance tuning, troubleshooting,benchmarking, training, and setting up servers for highavailability. Sreeni joined IBM US as part o
Migrating from Oracle . . . to IBM Informix Dynamic Server on Linux, UNIX, and Windows Chuck Ballard Holger Kirstein Srinivasrao Madiraju Sreeni Paidi Nora Sokolof Renato Spironelli Developing a Data and Applications Migration Methodology Understanding IDS and Oracle DBMS Functionality Using the IBM Migration Tool Kit as Your Guide Front cover
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Migrating from Oracle Business Intelligence 12c or the Previous Release of Oracle Analytics Server 3-13 Creating the Export Bundle 3-13 Upload and Restore the Export Bundle in Oracle Analytics Server 3-14 Migrating from Oracle Business Intelligence 11g 3-14 Migrating using the Console 3-14. iv. Running a Pre-Upgrade Readiness Check2-15
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Modi ed IBM IBM Informix Client SDK 4.10 03/2019 Modi ed IBM KVM for IBM z Systems 1.1 03/2019 Modi ed IBM IBM Tivoli Application Dependency Discovery Manager 7.3 03/2019 New added IBM IBM Workspace Analyzer for Banking 6.0 03/2019 New added IBM IBM StoredIQ Suite 7.6 03/2019 New added IBM IBM Rational Performance Test Server 9.5 03/2019 New .
Migrating a SQL Server Database to Amazon Aurora MySQL (p. 93) Migrating an Amazon RDS for SQL Server Database to an Amazon S3 Data Lake (p. 110) Migrating an Oracle Database to PostgreSQL (p. 130) Migrating an Amazon RDS for Oracle Database to Amazon Redshift (p. 148) Migrating MySQL-Compatible Databases (p. 179)
IBM 360 IBM 370IBM 3033 IBM ES9000 Fujitsu VP2000 IBM 3090S NTT Fujitsu M-780 IBM 3090 CDC Cyber 205 IBM 4381 IBM 3081 Fujitsu M380 IBM RY5 IBM GP IBM RY6 Apache Pulsar Merced IBM RY7
7 Messaging Server Oracle Oracle Communications suite Oracle 8 Mail Server Oracle Oracle Communications suite Oracle 9 IDAM Oracle Oracle Access Management Suite Plus / Oracle Identity Manager Connectors Pack / Oracle Identity Governance Suite Oracle 10 Business Intelligence
Advanced Replication Option, Database Server, Enabling the Information Age, Oracle Call Interface, Oracle EDI Gateway, Oracle Enterprise Manager, Oracle Expert, Oracle Expert Option, Oracle Forms, Oracle Parallel Server [or, Oracle7 Parallel Server], Oracle Procedural Gateway, Oracle Replication Services, Oracle Reports, Oracle
