2
Informix Dynamic Server Questions

This chapter contains frequently asked questions about using the Oracle Migration Workbench to migrate an Informix Dynamic Server 7.3 database to an Oracle Server. It contains the following sections:

• Installation and Configuration
  
  
• Data Migration

Installation and Configuration

This section contains Informix Dynamic Server installation and configuration questions.

Are there any schema migration limitations for Informix Dynamic Server?

Yes. The following list includes the schema migration limitations for Informix Dynamic Server:

• Dbspaces
  
  
• Database Privileges
  
  
• Order Specifying Schema Objects for Migration
  
  
• User Privileges
  
  
• Defaults
  
  
• Roles
  
  
• Public Synonyms
  
  
• Indexes
  
  
• Foreign Key Constraints
  
  
• Check Constraints
  
  
• Check Constraint Owners

What do I use to connect to an Informix Dynamic Server?

Use Informix Dynamic Server JDBC Driver 1.4 to connect to an Informix Dynamic Server. The Informix JDBC Driver 1.4 is available from the Informix Dynamic Server 7.3 CD.

To connect to an Informix Dynamic Server, you must copy the ifxjdbc.jar Informix Dynamic Server JDBC driver file to the %ORACLE_HOME%\Omwb\drivers directory before running the Migration Workbench. Otherwise, when you attempt to capture a Migration Workbench database, you receive the following error message:

  No Suitable Driver Found.

If you receive this error message, copy the ifxjdbc.jar file to the %ORACLE_HOME%\Omwb\drivers directory, then restart the Migration Workbench.

Data Migration

This section contains Informix Dynamic Server data migration questions.

Why do I have problems migrating the Defaults schema when I use the default repository?

If you use the default repository, the Defaults schema objects are not migrated from an Informix Dynamic Server source database to an Oracle destination database. To ensure that schema objects are migrated, select an Oracle9i or Oracle8i repository in the Oracle Migration Workbench Repository Login dialog box.

What must I do when the stored procedure parameters that are declared using the LIKE syntax are incorrectly parsed to type CLOB?

This is a known problem with the stored procedure parsing. To avoid it, manually edit the code. You can do this by editing the stored procedures schema object of the Oracle Model. Alternatively, you can edit the stored procedure in the Oracle database after the migration process is complete.

The following table displays the incorrect parsing output and the correct output for the following stored procedure variable definition:

create procedure myProc(aParameter LIKE atable.column)

CREATE OR REPLACE PROCEDURE 
myProc(
/* SPCONV-ERR[---]:(REFERENCES 
TEXT) Converted to CLOB 
datatype.
Insert and Update restrictions 
apply. */
aParameter CLOB) AS

CREATE OR REPLACE PROCEDURE 
myProc(aParameteraTable.column%TYPE)

Why does the stored procedure parser fail in the Migration Workbench?

The stored procedure parser can fail if you use hard coded decimal numbers with missing leading or trailing zeros. This is a known problem with the Stored Procedure parser in the Migration Workbench. To prevent it from happening, insert leading or trailing zeros to the numbers in the stored procedure text. You can do this in either in the Informix Dynamic Server database or in the Source Model of the Migration Workbench. The following table shows examples of decimal numbers:

Does the Informix Dynamic Server Stored Procedures Parser have any limitations?

Yes. The limitations of the Informix Dynamic Server parser are:

• The parser moves all user-defined comments within the original Informix Dynamic Server stored procedure from their original position to the top of the Oracle stored procedure where they display in a single unit.
  
  
• You must place a date format mask in front of any variable or literal that is assigned to a DATETIME column.
  
  
• A single Informix Dynamic Server trigger can contain both BEFORE and AFTER constructs. Triggers of this type generate two distinct triggers, a BEFORE and an AFTER trigger, within the Oracle destination database. The stored procedure parser for this release displays code for both of the resulting Oracle destination database triggers in one screen. Access these triggers through the Triggers Property Sheet within the Oracle Model. However, the parser only creates the first trigger in the destination Oracle database. You must create the second trigger manually. To do this, copy the text from the Triggers Property Sheet within the Oracle Model.
  
  
• The parser ignores all SET statements, with the exception of SET DEBUG FILE. You might need to manually convert the SET statements.
  
  
• The Migration Workbench does not parse the Informix Dynamic Server generic CASE statements, for example CASE WHEN e.sal > 2000 THEN e.sal ELSE 2000 END CASE. You might need to manually convert these types of statements.
  
  
• The in-built Informix Dynamic Server system procedure DBINFO is only partially translated during conversion. You might need to manually convert this procedure.
  
  
• The parser ignores all DDL statements within stored procedures, with the exception of the CREATE TABLE statement.
  
  
• Informix Dynamic Server enables you to use keywords as identifiers within stored procedures. However, you cannot use all Informix Dynamic Server keywords as identifiers.

  See Also: For more information on the keywords that you cannot use, see the Oracle Migration Workbench Reference Guide for Informix Dynamic Server 7.3 Migrations.

Does the Migration Workbench fully support the migration of LOB data using SQL*Loader scripts?

No. The Migration Workbench does not fully support the migration of LOB data using SQL*Loader scripts.

The unload command dumps binary data as a series of hexadecimal characters. Within a SQL*Loader control file, you can apply the HEXTORAW SQL function to this field of hexadecimal character data to produce binary data. The Migration Workbench can then write binary data to a LONG RAW column. The Migration Workbench will generate a control file to do this automatically.

You can use the Migration Workbench to generate the SQL*Loader control file for the table which stores binary data.

To generate the SQL*Loader control file:

1. After mapping the source database to the Oracle database with the Oracle Migration Workbench, navigate to the table in the Oracle Model, right-click on the icon for the table, and select Generate SQL*Loader Scripts. This generates the Informix Dynamic Server extraction script and the Oracle SQL*Loader script and control file for the table.
   
   
2. In the SQL*Loader control file generated by the Oracle Migration Workbench, verify that the maximum CHAR size is large enough for the dumped hexadecimal data, in the line for the column which stores the binary data.

Because the column in the Oracle database table is defined as LONG, you must use a length specifier on the CHAR keyword to specify a maximum length. The maximum length is 2 gigabytes for LONG datatypes. Doing this guarantees that a large enough buffer is allocated for the value.