I think that you benefit from having a function that implements a variant of where returns a value in the range instead of — when the value from would be , it returns instead. Ignoring negative values, this does the job:
With this function available, and an 'operational' table containing the date/time values recorded in UTC:
and a separate table containing a single column for the reference date, and a single row containing, for example, today's date:
then you can run a query like this to select the relevant rows in US/Central time (UTC-06:00 at the moment):
That creates a Cartesian product of the two tables with the condition. In Informix 12.10, you can use CROSS JOIN instead:
I'm not sure when CROSS JOIN was added — try it; it'll probably work for you.
Given some partially random, partially systematic date like:
and setting the reference date as 2016-11-28, the output of the query is:
You can see precise cut-offs in the data; the times and appear in the correct places (and those rows are not present by random chance).
Given a reference date of 2016-11-27, the output is:
Oracle and Informix Dynamic Server Compared
This chapter contains information comparing the Informix Dynamic Server database and the Oracle database. It includes the following sections:
This section includes information on issues of security with Informix Dynamic Server databases and Oracle databases.
A fundamental difference between Informix Dynamic Server and Oracle is database user authentication. Informix Dynamic Server users are maintained and authenticated by the host operating system, whereas Oracle users are maintained by the database and can use several methods of authentication, usually through the database.
A user can connect to an Informix Dynamic Server database server through the operating system login information, however access to the databases the server supports is restricted by the sysuser table. The sysuser is maintained by each database and the database administrator.
Database as a Logical Partition
Multiple databases on a single Informix Dynamic Server database server are migrated to a single Oracle database. Schemas in different databases are owned by the same user.
Informix Dynamic Server has two special users, and . A description of these users is as follows:
Informix Dynamic Server software owner
Operating system super user
These users have database administrator access to all the databases supported by the Informix Dynamic Server database server. The two user names do not have to be listed in the sysusers table for any database. The Informix Dynamic Server plug-in creates the two user names in the Oracle database. Another special Informix Dynamic Server database user, which does not have to be an operating system user, is .
You can grant the database user system and object privileges and its database level privileges are entered in the sysusers table. The privileges granted to public are automatically available to every other database user. The Informix Dynamic Server plug-in migrates all the object privileges.
Oracle has the concept of a database group or role, where you can grant privileges. These privileges are made available to all other users in the database. It is also called .
The difference is that is not listed as a database user and you cannot grant connect system privilege to to enable any user logged on to the host operating system gain access to the database.
All Informix Dynamic Server object level privileges granted to are migrated to Oracle. None of the three Informix Dynamic Server database privileges granted to public are migrated.
The Informix Dynamic Server plug-in could not detect what operating system users had access to the database. The Informix Dynamic Server plug-in only creates Oracle users for the users listed in sysusers in each of the Informix Dynamic Server databases selected for migration. Therefore, if you rely on granting connect, resource, or even dba to as a method of allowing operating system users access to the database, then you must explicitly grant each of those users the appropriate database level privilege.
The schema contains the definitions of the tables, views, indexes, users, constraints, stored procedures, triggers, and other database-specific objects. Most relational databases work with similar objects.
The schema migration topics discussed here include the following:
Schema Object Similarities
There are many similarities between schema objects in Oracle and Informix Dynamic Server. However, some schema objects differ between these databases. For specific information about schema objects, see the SQL Statements topic within the Oracle9i SQL Reference, Release 1 (9.0.1).
Table 2-1 shows the differences between Oracle and Informix Dynamic Server.
Table 2-1 Schema Objects in Informix Dynamic Server and Oracle
|Oracle||Informix Dynamic Server|
Triggers for each row
Triggers for each row
SERIAL datatype for a column
Schema Object Names
Reserved words differ between Oracle and Informix Dynamic Server. Many Oracle reserved words are valid object or column names in Informix Dynamic Server. Use of reserved words as schema object names makes it impossible to use the same names across databases. The Migration Workbench appends an underscore (_) to the name of an Informix Dynamic Server object that is an Oracle reserved word.
Neither Oracle nor Informix Dynamic Server is case-sensitive with respect to object names. Object names in Informix Dynamic Server are stored as lower case, while Oracle schema object names are stored as upper case.
Choose a schema object name that is the following:
- unique by case
- by at least one other characteristic
Ensure that the object name is not a reserved word from either database.
For a list of Oracle reserved words, see the Oracle9i SQL Reference, Release 1 (9.0.1).
In non-ANSI-Compliant Informix Dynamic Server databases, schema object names are required to be unique across users. This behavior in Oracle is similar to Informix Dynamic Server ANSI-Compliant mode databases. Different users can create objects with the same name without any conflicts.
Informix Dynamic Server Database-level Privileges
For information on database-level privileges, see the Oracle Migration Workbench Release Notes.
Migrating Multiple Databases
The Migration Workbench supports the migration of multiple Informix Dynamic Server databases if they are on the same Informix Dynamic Server database server.
Table Design Considerations
This section discusses table design issues that you need to consider when converting Informix Dynamic Server databases to Oracle. This section includes the following:
This section outlines conversion considerations for the following data type:
DATETIME Data Types
The Datetime precision in Informix Dynamic Server is to 5 decimal places, 1/100000th of a second. Oracle9i has a new data type TIMESTAMP which has a precision of 1/100000000th of a second. Oracle also has a DATE data type that stores date and time values accurate to one second. The Migration Workbench has a default mapping to the DATE data type.
For applications that require finer date/time precision than seconds, the TIMESTAMP data type should be selected for the datatype mapping of date data types in Informix Dynamic Server. The database stores point-in-time values for DATE and TIME data types.
As an alternative, if an Informix Dynamic Server application uses the column to provide unique IDs instead of point-in-time values, you can replace the column with a in the Oracle schema definition.
In the following examples, the original design does not allow the precision to exceed seconds in the Oracle table. The examples assume that the column is used to provide unique IDs. If millisecond precision is not required, the table design outlined in the following example is sufficient:
Table 2-2 Original Table Design
|Informix Dynamic Server||Oracle|
|CREATE TABLE example_table (datetime_column datetime not null, text_column text null, varchar_column varchar(10) null)||CREATE TABLE example_table (datetime_column date not null, text_column clob null, varchar_column varchar2(10) null)|
The design shown in Table 2-3 allows you to insert the value of the sequence into the integer_column. This allows you to order the rows in the table beyond the allowed precision of one second for data type fields in Oracle. If you include this column in the Informix Dynamic Server table, you can keep the same table design for the Oracle database.
Table 2-3 Revised Table Design
|Informix Dynamic Server||Oracle|
|CREATE TABLE example_table (datetime_column datetime not null, integer_column int null, text_column text null, varchar_column varchar(10) null)||CREATE TABLE example_table (datetime_column date not null, integer_column number null, text_column clob null, varchar_column varchar2(10) null)|
For Informix Dynamic Server, the value in the integer_column is always NULL. For Oracle, the value for the field integer_column is updated with the next value of the sequence.
Create the sequence by issuing the following command:CREATE SEQUENCE datetime_seq
Values generated for this sequence start at 1 and increment by 1.
Many applications do not use DATETIME values as UNIQUE IDs, but still require the date/time precision to be higher than seconds. For example, the timestamp of a scientific application may have to be expressed in milliseconds, microseconds, and nanoseconds. The precision of the Informix Dynamic Server DATETIME data type is 1/100000th of a second; the precision of the Oracle DATE data type is one second. The Oracle TIMESTAMP data type has a precision to 1/100000000th of a second. However, the precision recorded is dependent on the operating system.
IMAGE and TEXT Data Types (Binary Large Objects)
The physical and logical storage methods for BYTE and TEXT data in Informix Dynamic Server is similar to Oracle BLOB data storage. A pointer to the BYTE or TEXT data is stored with the rows in the table while the IMAGE or TEXT data is stored separately. This arrangement allows multiple columns of IMAGE or TEXT data per table. Oracle may store IMAGE data in a BLOB type field and TEXT data may be stored in a CLOB type field. Oracle allows multiple BLOB and CLOB columns per table. BLOBs and CLOBs may or may not be stored in the row depending on their size. If LONG or LON appears, only one column is allowed.
If the Informix Dynamic Server column is such that the data never exceeds 4000 bytes, convert the column to an Oracle data type column instead of a column. An Oracle table can define multiple columns. This size of data is suitable for most applications.
You can define check constraints in a statement or an statement in Informix Dynamic Server. You can define multiple check constraints on a table. A table-level check constraint can reference any column in the constrained table. A column can have only one check constraint. A column-level check constraint can reference only the constrained column. These check constraints support complex regular expressions.
Oracle defines check constraints as part of the or statements. A check constraint is defined at the level and not at the level. Therefore, it can reference any column in the table. Oracle, however, does not support complex regular expressions.
Schema Migration Limitations for Informix Dynamic Server
The schema migration limitations are separated into the following categories:
The Migration Workbench captures all dbspaces on the Informix Dynamic Server, even though you may not require all dbspaces. You can delete the dbspaces, as appropriate, from the Source Model. If you delete the root dbspace, the next time you start the Migration Workbench, the sizing information shows up as zero (0).
Mapping for Informix Dynamic Server Database Level Privileges to Oracle System Privileges
Before migration ensure that the sysmaster database exists for the database server you are migration from. Oracle does not support the migration of Default literal.
The Informix Dynamic Server privilege maps to the following Oracle system privileges:
The Informix Dynamic Server privilege maps to the following Oracle system privileges:
The Informix Dynamic Server privilege maps to the Oracle system privilege.
nformix database users granted the database level privilege do not have the privilege to create tables, procedures or triggers. However, users may be the owner of these object types, created for them by more privileged users.
The Migration Workbench creates schema objects connected to the Oracle database as the owner of the object. Any attempt to create an object without the appropriate privilege generates an error. Therefore, the Informix Dynamic Server plug-in maps users, that have the Informix Dynamic Server privilege, to Oracle with the system privileges to create and . To revoke the privileges from the users after migration execute the following:
You cannot migrate Informix Dynamic Server DBA users with the for any of the system privileges. The Informix Dynamic Server DBA cannot grant the privileges to other users.
The following limitations apply to the Defaults schema object:
- An Informix Dynamic Server user name can be up to 8 characters long. Oracle users can be up to 30 characters long. The Informix Dynamic Server system function maps to the Oracle system function. However, if you use the Oracle function as the default, the addition of the default fails because the column definition, such as , in Informix Dynamic Server is too small for Oracle names. Change the length of the column in the Oracle Model to before migrating.
- You cannot delete defaults in the Source Model although it appears this is possible within the Migration Workbench. For more information, see Bug 1642519 in the Oracle Bug Database.
- Defaults for columns that are not a number and are migrated to CHAR(30) fail during migration.
- Defaults for columns that you do not specify in the YYYY-MM-DD HH:MI:SS format fail to migrate properly.
Migrate indexes, then migrate unique constraints and primary key constraints. If you do not migrate the schema objects in this order, a system generated index is created for unique constraints and primary keys. This causes the statement to fail.
Check constraints within Informix Dynamic Server are not parsed to Oracle syntax. The user should ensure that they can successfully execute all check constraints listed in the Oracle Model. For more information, see Bug 1644309 in the Oracle Bug Database.
Check Constraint Owners
If a user creates a check constraint on another users' table, the check constraint is created in the Oracle Model and the check constraint is owned by the owner of that table.
This section provides descriptions of the differences in data types used by Informix Dynamic Server and Oracle databases. This section contains the following information:
- A table showing the base Informix Dynamic Server data types available and how they are mapped to Oracle data types
- Recommendations based on the information listed in the table
Table 2-4 Data Types Summary Table
Informix Dynamic Server Description Oracle Comments
Four-byte integer, 31 bits, and a sign. Stores whole numbers in the range -2,147,483,647 to +2,147,483,647
You may wish to place a check constraint on columns of this type to enforce values between 2^31 and2^31.
Two-byte integer, 15 bits, and a sign. Stores whole numbers in the range -32,767 to +32,767.
You may wish to place a check constraint on columns of this type to enforce values between -2^15 and 2^15.
Stores a sequential INTEGER assigned automatically by the database server when a row is inserted.
A Sequence and Trigger is created automatically to update the column that was originally SERIAL.
DECIMAL(p) floating point
A floating point number with p digits of precision. If you omit p, p defaults to 16.
A floating point number with 38 digits of precision.
A fixed point number with precision p and scale s.
A fixed point number with precision p and scale s.
Stores single-precision floating-point numbers corresponding to the float datatype in C.
Stores double-precision floating-point numbers corresponding to the double datatype in C. p specifies a precision, 1.14, however it is ignored.
You may want to add a check constraint to constrain range of values. Also, you get different answers when performing operations on this type due to the fact that the Oracle type is much more precise and portable than .
Fixed-length string of exactly n 8-bit characters, blank padded.
0< n < 32768
if n <= 2000
if 2000 < n <= 4000
CLOB or LONG
if n > 4000
Oracle CHAR can only hold up to 2000 bytes of data.
Oracle VARCHAR2 can hold up to 4000 bytes of data.
Oracle LONG can hold up to 2G of data, but there are many restrictions on LONG columns.
Oracle CLOB can hold up to 4G.
Varying-length character string.
0 < n < 256.
Stores any kind of text data, up to 2G. A table can contain more than one TEXT column.
The CLOB datatype can hold up to 4G of character data. A table can have more than one CLOB column.
LONG has a limit of 2G but there are several restrictions on LONG columns.
Stores any kind of binary data, up to 2G.
A table can contain more than one BYTE column.
The BLOB datatype can hold up to 4G of binary data. A table can have more than one BYPE column.
LONG RAW can store binary data. has a limit of 2G but there are several restrictions on LONG columns.
Stores and instance in time expressed as a calendar date and time of day. It can be defined with qualifiers to specify the precision. For example:
largest_qualifier TO smallest_qualifier
FRACTION a decimal fraction of a second with up to five digits of precision.
The Informix Dynamic Server data type has higher precision, YEAR to Fraction of Second, than the Oracle DATE data type, Year to Second. The fractional second information, if specified in the Informix Dynamic Server column definition, is lost in the migration.
The Oracle TIMESTAMP data type can also be used . It has a precision of 1/10000000th of a second.
DATE is stored internally as an integer equal to the number of days since December 31,1899.
Store a date and time, the time defaults to 12:00AM midnight.
Where p is the precision of the largest qualifier value.
You can map data types from Informix Dynamic Server to Oracle with the equivalent data types listed in Table 2-4. You can define how the base type is mapped to an Oracle type in the Data Type Mappings page in the Options dialog.
The Informix Dynamic Server datatype stores any type of binary data and has a maximum limit of 2^31 bytes (2G). The comparable Oracle datatypes are and .
Oracle stores variable-length raw binary data field used for binary data up to 2G in length. Although you can insert data as a literal in an statement (a hexidecimal character represents the bit pattern for every four bits of data, 'CB' = 11001011), there are several restrictions on and columns, for example, only one columns is allowed per table and columns can not be indexed. The datatype is provided for backward compatibility with existing applications. For new applications, Oracle recommends the use of and BFILE datatypes for large amounts for binary data.
i Oracle8i, and other i Oracle8i types, , and , have a much greater storage capacity than , storing up to 4G of data and i Oracle8i tables can have multiple columns.
Oracle s support on sequential access, while i Oracle8is support random piece wise access. Although i Oracle8i SQL cannot directly manipulate s, you can access s from SQL through the i Oracle8i supplied DBMS_LOB PL/SQL package. The DBMS_LOB package provides many functions and procedures to append the contents of one to another, compares contents or parts of contents, copies contents, reads from and writes to s, and also returns part of a from a given offset and length.
For example, with Informix Dynamic Server you can select any part of a column by using subscripts:select cat_picture[1,75] from catalog where catalog_num = 10001;
A similar request in i Oracle8i follows:blob_loc BLOB; binchunk RAW; SELECT cat_picture INTO blob_loc FROM catalog WHERE catalog_num = 10001; binchunk := dbms_lob.substr(blob_loc, 75, 1);
The Informix Dynamic Server datatype stores any sequence of letters, numbers, and symbols. It can store single byte and multibyte characters. A character column has a maximum length of n bytes, where 1<=n<=32767. If n is not specified, 1 is the default. If a character string is less than n bytes, then the string is extended with spaces to make up the length. If the string value is longer than n bytes, the string is truncated without raising an error.
The comparable Oracle datatypes are:
- , fixed-length field, up to 2000 bytes in length
- , variable-length character data, up to 4000 bytes
- , variable-length character data up to 2G in length
- , character large object up to 4G in length
Informix Dynamic Server datatypes can be up to 32767 bytes in length. Columns defined as with a length <= 2000 can be migrated to the Oracle datatype and functionality contains nearly the same functionality. Both are fixed-length character strings and if you insert a shorter string, the value is blank-padded to the fixed length. If, however, a string is longer, Oracle returns an error.
Oracle compares values using blank-padded comparison semantics. For more information, see the Comparison Sematics topic.
Oracle can hold data up to 4000 bytes in length. Oracle Corporation recommends you us a migration to when you are migrating Informix Dynamic Server columns that store more than 2000 bytes of data but less than or equal to 4000. is a variable length datatype and uses non-padded comparison semantics.
If Informix Dynamic Server tables have columns defined with n > 4000 then the only option is to migrate to Oracle or .
The datatype can store variable-length character data up to 2G in length. columns can be used in lists, clauses of statements, and clause of statements. The datatype is provided for backward compatibility and should be used for storing large amounts of character data. There are several restrictions on datatypes, such as the following:
- One columns is allowed per table
- columns can not be indexed
The datatype is just one of the datatypes supported by Oracle. datatypes differ form datatypes in several ways:
- A table may contain multiple columns but only one column
- A table containing one or more columns can be partitioned, but a table containing a columns can not be partitioned
- Maximum size of a is 4G, maximum size of is 2G
- s support random access to data, but s only support sequential access
datatyes can be stored in-line within a table, or out-of-line within a tablespace, using a locator, or in an external file -- a BFILE datatype. It is not currently supported by the Migration Workbench.
Using PL/SQL to manipulate s, in PL/SQL can store up to 32767 bytes of data, so handling large Informix Dynamic Server datatypes should be reasonably efficient when stored in Oracle as .... clob_loc CLOB; some_text VARCHAR2(32767); text_len INTEGER; ... INSERT INTO page_info (page_num, page_text) VALUES (101, empty_clob); SELECT page_text INTO clob_loc FROM page_info where page_num = 101; text_len := LENGTH(some_text); DBMS_LOB.WRITE(clob_loc, text_len,1, some_text);
You can use subscripts on BYTE columns. Subscripts can also be used on , VARCHAR2, NCHAR, , and TEXT columns. The subscripts indicate the starting and ending character positions that define each column substring. With the DBMS_LOB package functions, you can choose to receive all or part of the CLOB, using READ and SUBSTR.
Data stored in columns is sorted based on the order of the code-set for the character set of the database, irrespective of the current location. Sorting in Oracle is based on the Oracle NLS settings. If the Oracle NLS specify a different sort order than the character code set, a sort-by code set can be enabled to ensure that the expected result remains the same. For more information, see the NCHAR(n) topic.
Multibyte Character Sets
Just as is the case for Informix Dynamic Server and datatypes, the length of Oracle and datatypes is specified in bytes. If the database character set is multibyte, make sure to calculate the appropriate space requirements to allow for the maximum possible number of bytes for a given number of characters.
Informix Dynamic Server comparison semantics for the datatype and the Oracle datatype are the same. If you are migrating columns that have a length where n such that 2000< n <= 4000 to then see the section for more details on comparison semantics for .
Informix Dynamic Server (and ) columns can store an empty string, i.e. no data with a length zero. Even though a column may appear blank-padded, its length is 0. The empty string is not the same as , which indicates that the value is undefined and of unknown length. However, Oracle does not have the concept of an empty string. Therefore, Oracle inserts empty strings as .
You should check the application code and logic for unexpected behavior, such as empty strings migrating to .
is a synonym for .
Informix Dynamic Server and datatypes both store the same type of data, a sequence of single-byte or multibyte letters, numbers, and symbols. The main difference between the datatypes is the order the Informix Dynamic Server database server sorts the data. columns are sorted on code set, the numeric values of the characters defined by the character encoding scheme. columns are sorted based on the locale-specific localized order.
Oracle can sort data based on both the code set and the local-specific order.
The Migration Workbench for Informix Dynamic Server migrates both the Informix Dynamic Server and datatypes to the Oracle datatype.
The Oracle NLS settings, either by default or as configured by the DBA, define the exact behavior of the sorting order. The NLS settings can be made at the database/init.ora, environment, and session levels.
A locale-specific sort is as a known as a linguistic sort in Oracle. You can use a linguistic sort by setting one of the Oracle collation parameters, NLS_SORT. The following is an example of a linguistic sort:NLS_SORT = French
A code set sort is known as a binary sort in Oracle. If an application, for some reason, needs to sort data in a column based only on the code set, then the application can set the NLS_SORT to be a binary sort. The following is an example of a binary sort:NLS_SORT = BINARY
Oracle does have a built-in datatype, as well as and datatypes. These three datatypes can be used to store fixed-width and variable-width multibyte character set data as specified by the NATIONAL CHARACTER SET setting in the CREATE DATABASE command.
The National Character Set is an alternative character set to the Database Character Set. It is particularly useful in databases with a variable-width multibyte database character set because , , and can store fixed-width multibyte characters. Storing fixed-width multibyte characters enhances performance by allowing optimized string processing on these columns. An Oracle database can not be created with a fixed-width multibyte character set but the National Language datatypes allow storage of fixed-width multibyte character set data. The properties of a fixed-width character set may be more desirable for extensive processing operations or for facilitating programming.
The Informix Dynamic Server datatype stores varying length single-byte and multibyte character strings of letters, numbers. and symbols. The maximum size of this column is m, which can range from 1 to 255. The minimum reserved space is r. This is optional and defaults to 0 if not specified. The minimum reserved space can range from 0 to 255.
The comparable Oracle datatype is that also stores variable-length character strings. An Oracle , however, can have a maximum string length of between 1 and 4000 specified for n.
Specifying a minimum reserved space is useful if the data in a row is initially small but is expected to grow at a later date. If this is the case then, when migrating Informix Dynamic Server tables that contain columns consider increasing the PCTFREE value in the storage clause for these tables in the Oracle database. If this column is used in an index, then the PCTFREE values for the corresponding index storage should also be considered. For indexes based on columns, Informix Dynamic Server allocates the maximum storage.
Informix Dynamic Server VARCHAR values are compared to other values and to character values in the same way that character values are compared. The shorter values are blank-padded until the values have equal lengths, then they are compared for the full length.
Oracle comparisons are made using non-padded comparison semantics. Trailing blanks are important and are included in the comparison. Two values are only equal if they have the same characters and are of equal length.
Oracle comparison uses blank-padded comparison semantics, similar to the way Informix Dynamic Server compares and data. If two values have different lengths, Oracle adds blanks at the end of the shorter value, until the two values are the same length. Oracle then compares the values, character by character, up to the first character that differs. So two values that are different only in the number of trailing blanks are considered equal.
This is important behavior for the migration of the applications. It is possible for some comparisons on Informix Dynamic Server columns may fail when migrated to Oracle columns where trailing blanks are involved. To offset this, you may need to use RTRIM() on all columns in a comparison to strip off the trailing blanks.
The main difference between the and datatype is the difference in collation sequencing. character collation order depends on the database server locale, while the collation of characters depends on the code set. For more information on how these collation methods are implemented in Oracle and the impact on Informix Dynamic Server, see the NCHAR(n) topic.
Oracle has a built-in datatype that is currently synonymous with the datatype. However, is reserved for future use. In a later version of Oracle, the definition of may change and since is fully supported, the datatype is used to store variable-length character strings to avoid any possible changes from the current behavior.
The Informix Dynamic Server datatype is the ANSI-compliant format for character data of varying length. The Informix Dynamic Server datatype supports the same functionality and is treated as one in the database server. This datatype is treated the same as the datatype and migrates to the Oracle datatype. For more information, see the VARCHAR(m,r) topic.
The Informix Dynamic Server NVARCHAR(m,r) datatype stores data of varying length, similar to , except that it compares data in the order that the locale specifies.
The Informix Dynamic Server NVARCHAR(m,r) datatype is migrated to the Oracle datatype.
For more information on migration issues, see the VARCHAR(m,r) and NCHAR(n) topics.
The datatype stores the calendar date and the default display format is mm/dd/yyyy where mm is the month (01-12), dd is the day of the month (01-31) and yyyy is the year (0001-9999).
The values are stored as integers thus can be used in arithmetic expressions. For example, subtracting a value from another value returns the number of days that have elapsed between the two dates.
Subtracting two Oracle datatypes from each other returns the number of days between the two dates. If only calendar dates are stored in Oracle, then the default time of 12:00:00AM (midnight) is also stored, so any subtraction results in a whole number indicating the number of days between the two dates. For the month, Informix Dynamic Server accepts a number value of either 1 or 01 for January, and so on. Similarly, for the day, Informix Dynamic Server accepts either 1 or 01 for the first day of the month. This is also true in Oracle.
The Informix Dynamic Server datatype stores an instant in time expressed as a calendar date and time of day. The precision that a value is stored can be chosen, with the precision ranging from a year to a fraction of a second. in effect is a family of 28 datatypes.
The Oracle datatype matches just one of the 28 datetime types, YEAR TO SECOND.
The Informix Dynamic Server plug-in stores values as Oracle values, losing the FRACTION part of . If you need to keep the fraction part of , before migration, add a new column to the table and store the fraction part as a DECIMAL with the appropriate precision migrated to the appropriate NUMBER datatype.
Any table columns that do not store a particular precision use the Oracle defaults. The defaults for Oracle are the first day of the current month and 12:00:00AM (midnight).
For applications that need to manipulate various precisians, the SQL code needs to be changed. For example, if a column is defined as MONTH TO DAY, and contains two values, date1: March 10 and date2: February 18 shown as (mm/dd) 03/10 and 02/18. If these values are stored in Oracle DATE (if the current year is 1999, the values would be date1: 1999/03/10 12:00:00 and date2:1999/02/18 12:00:00 respectively. The expression date1 - date2 UNITS DAY returns 20 days. However, if the year was 2000, then the expression date1 - date 2 UNITS DAY returns 21 days.
Using a combination of TO_DATE and TO_CHAR and appropriate date format masks, the year the was stored can be replaced with the current year for use in the expression. The following is an example of this combination:SQL> SELECT TO_CHAR(TO_DATE(TO_CHAR(TO_DATE('01-01-1997', 'MM-DD-YYYY'), 'MM-DD'), 'MM-DD'), 'MM-DD-YYYY') from dual; TO_CHAR(TO ---------- 01-01-2000
Oracle DATE Arithmetic
Subtraction of returns days. Because each date contains a time component, most results of date operations include a fraction. The fraction indicates a portion of one day. For example, 1.5 days is 36 hours.
The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31 day month.
You cannot add dates, but another Oracle function available for date arithmetic is ADD_MONTHS(date,n). To add days to a date, add a number constant to the date.
Evaluate the logic of the addition or subtraction. Remember you can have months that are 28, 29, 30, or 31 days and you can have years that are 365 or 366 days.
Currently, there is no corresponding Oracle datatype for the Informix Dynamic Server datatype.
The Informix Dynamic Server datatype can be defined as one of 18 different precisions, YEAR TO YEAR, YEAR TO MONTH, MONTH TO MONTH, DAY TO DAY and so on right down to FRACTION TO FRACTION(f). These are divided into two classes, YEAR TO MONTH, and DAY TO FRACTION.
YEAR TO YEAR
Informix Dynamic Server default precision for YEAR
DAY(3) TO DAY
SECOND(6) TO SECOND
If the largest qualifier value and the smallest qualifier value are not the same, then the INTERVAL column is migrated to CHAR(30).
Manipulating Oracle DATE with Informix Dynamic Server INTERVAL Values
Numeric constants can be added or subtracted from the Oracle DATE datatype -- to that Informix Dynamic Server DATE and DATETIME datatypes are mapped -- and are treated in terms of days. Therefore any operations involving the second class of Informix Dynamic Server , DAY TO FRACTION must be expressed as a fraction in terms of days. For example,CURRENT + INTERVAL (10 12) DAY TO HOUR
should be expressed asSYSDATE + 10.5
To handle addition and subtraction of the first class of INTERVAL, YEAR TO MONTH, the INTERVAL needs to be expressed in terms of months and passed as a parameter, along with the date.
The Oracle function ADD_MONTHS(date,n) can be used for arithmetic:TODAY + INTERVAL (2) YEAR TO YEAR
should be expressed asADD_MONTHS(SYSDATE, 24)
You do have the option to migrate all columns as preserving all details, including subsecond information. However, the application must manipulate this data appropriately, using TO_DATE() and others.
Informix Dynamic Server DECIMAL datatype can take two forms:
- DECIMAL(p) floating-point
- DECIMAL(p,s) fixed point
DECIMAL(p) floating point
DECIMAL(p) floating point stores decimal floating point numbers up to a maximum of 32 significant digits.
The total number of significant digits is p. This is optional, DECIMAL is treated as DECIMAL(16). DECIMAL(p) has an absolute values range of between 10-130 and 10124.
In an ANSI-compliant Informix Dynamic Server database, DECIMAL(p) defaults to DECIMAL(p,0). If only p is specified, s is actually stored as 255 in the catalog tables.
In Oracle, Informix Dynamic Server DECIMAL(p) floating point values are always stored as NUMBER. It has 38 significant digits since it is not possible to restrict the total number of significant digits for storing a floating-point number. Oracle can store negative and positive values in the range 1.0x10-130 and 9.9...9x10125, which is 38 nines followed by 88 zeros. NUMBERS are stored in scientific notation. Leading and trailing zeros are not stored.
Since Oracle can store floating-point numbers with a greater precision than Informix Dynamic Server, there should be no loss of precision after the migration to Oracle.
The precision is p with a range 1 to 32. The number of digits to the right of the decimal place is s. Numbers < 0.5x10-s have the value 0.
In Oracle, DECIMAL(p,s) maps to NUMBER(p,s).
The MONEY datatype is always a fixed-point number with a maximum 32 significant digits.
MONEY(p) = DECIMAL(P,2)
MONEY = DECIMAL(16,2)
The Informix Dynamic Server MONEY datatype is represented as DECIMAL. The Informix Dynamic Server MONEY(p,s) datatype maps to Oracle NUMBER(p,s).
The Informix Dynamic Server datatype is mapped to NUMBER(10).
The Informix Dynamic Server datatype can store values in the range -2,147,483,647 to 2,147,483,647. If a value to be inserted is outside this range, the Informix Dynamic Server database server does not store the value and returns an error. A column defined as NUMBER(10) in an Oracle database allows values in the range -9,999,999,999 to 9,999,999,999 to be inserted without raising an error. If mapped, columns should enforce the original range, then a check constraint can be added to the columns to ensure that values entered into these columns are within the range -2,147,483,647 to 2,147,483,647.
Informix Dynamic Server stores as a signed binary integer and requires 4 bytes per value.
Oracle stores numeric data in variable length format, in scientific notation. The smallest storage space Oracle uses to represent an is 2 bytes, 12 bytes is the maximum storage space required. The storage space for the value depends on the number of significant digits.
If you insert 7.2 and 7.8 into Informix Dynamic Server datatype, fractional parts are truncated, therefore the values 7 and 7 are stored.
If you insert 7.2 and 7.8 into Oracle NUMBER(10), fractional parts are rounded, therefore the values are stored as 7 and 8.
It may be necessary to check application code and logic to ensure there is no unexpected behavior. This is because it is assumed that fractional parts of any number are automatically truncated when inserted into the Informix Dynamic Server database.
The Informix Dynamic Server INT datatype is a synonym for
The Informix Dynamic Server SMALLINT datatype is mapped to NUMBER(5).
The Informix Dynamic Server datatype can store values in the range -32,767 to 32767. If a value is outside this range, the Informix Dynamic Server database server does not store the value and returns an error. A column defined as NUMBER(10) in an Oracle database allows values in the range -99,999 to 99,999 to be inserted without raising an error. If mapped, columns should enforce the original range then a check constraint can be added to the columns to ensure that values entered into these columns are within the range -32,767 to 32767.
Informix Dynamic Server SMALLINT datatype values take up 2 bytes per value.
Oracle stores a values in an NUMBER(5) datatype with a minimum of 2 bytes and a maximum of 4 bytes
For information on differences in behavior for Informix Dynamic Server and Oracle NUMBER, see the Inserting Fractions topic.
The Informix Dynamic Server datatype creates a column in a table that auto-increments an value every time a row is inserted into the table. By default, if the column is simply defined as , the column begins inserting with the value 1. Other starting values can be set by defining the column. For example, creates a column that begins inserting with the value 1000. The starting number cannot be 0 and the maximum value can reach, or be initially set to, is 2,147,483,647. After reaching the maximum value, the SERIAL column resets to 1. Only one column may be defined for an Informix Dynamic Server table. The datatype is not automatically a unique column, a unique index must be created for this column to prevent duplicate serial numbers.
The Migration Workbench for Informix Dynamic Server maps the Informix Dynamic Server datatype to an Oracle NUMBER(10) datatype and flags the column as an auto-increment column. The Migration Workbench also creates a NOT NULL CONSTRAINT on that column, as is the case with Informix Dynamic Server columns.
The Migration Workbench creates an Oracle sequence and an Oracle trigger on the table that contained the column. The trigger fires every time a row is inserted into the table. It gets the next value in the sequence and inserts it into the field.
For example, the following JOBS table was migrated to Oracle and the JOB_ID column was originally defined as an Informix Dynamic Server datatype:CREATE TABLE clerk.JOBS(JOB_ID NUMBER (10) NOT NULL, JOB_DESC VARCHAR2 (50) NOT NULL, MIN_LVL NUMBER (5), MAX_LVL NUMBER (5)) TABLESPACE PUBS; REM REM Message : Created Sequence: clerk.SEQ_11_1 REM User : system CREATE SEQUENCE clerk.SEQ_11_1 START WITH 1 / REM REM Message : Created Sequence Trigger: clerk.TR_SEQ_11_1 REM User : system CREATE TRIGGER clerk.TR_SEQ_11_1 BEFORE INSERT ON clerk.JOBS FOR EACH ROW BEGIN SELECT clerk.SEQ_11_1.nextval INTO :new.JOB_ID FROM dual; END; /
The Oracle trigger and sequence is created after a table with a column is migrated. The sequence is created using the option START WITH 1. If the data for this table is not moved automatically by the Migration Workbench, the sequence starts inserting with 1.
If the table data is selected to be moved automatically by the Migration Workbench while database table objects are created, the Migration Workbench creates the trigger and sequence after the data has been moved. Before the sequence is created the Migration Workbench selects the maximum value from the column (for example, 1231) and add 1 to this value and use it as the START WITH value in the CREATE SEQUENCE statement; as follows:CREATE SEQUENCE clerk.SEQ_11_1 START WITH 1232;
In the resulting Oracle database inserts to the table continue to auto-increment by one a value for the old serial column every time a row is inserted into the table.
Additional Oracle Sequence Options for Informix Dynamic Server SERIAL Migrations
The Migration Workbench uses the following command to create the sequence:CREATE SEQUENCE sequence_name START WITH integer;
The Oracle CREATE SEQUENCE command has several options, the only option that is used is the START WITH option.
Many of these options have defaults that are what would be required to replicate the Informix Dynamic Server datatype. However, there are a couple of settings that can be altered on the SEQUENCE to make it behave even more closely to the Informix Dynamic Server datatype.
START WITH integer
Specify the start sequence value.
For more information, see Resetting the Start Value.
INCREMENT BY integer
Specify the interval between sequence numbers. If this value is negative, then the sequence descends. For Informix Dynamic Server, use the Oracle default of 1.
NOMAXVALUE is the default setting. For Informix Dynamic Server, set this to 2147483647 to override the Oracle default value.
Specify NOMAXVALUE to indicate a maximum value of (10^27)-1, twenty eight 9's in a row, for an ascending sequence or -1 for a descending sequence. This is the default.
Specify the sequence minimum value. For Informix Dynamic Server, indicate 1 as the value so that if the sequence ever restarts, it restarts with this value.
Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(10^26) for a descending sequence. This is the default. For Informix Dynamic Server, use the default because the default INCREMENT BY value of 1, we get a default minimum value of 1.
Specify CYCLE to indicate that the sequence continues to generate values after reaching either maximum or minimum value. After an ascending sequence reaches maximum value, it generates minimum value.
For Informix Dynamic Server, the column resets to 1 after reaching 2147483647.
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching maximum or minimum value. This is the default. For Informix Dynamic Server, override this default Oracle behavior to CYCLE.
Specify how many values of the sequence Oracle preallocates and keeps in memory for faster access.
Specify NOCACHE to indicate that values of the sequence are not preallocated.
If both CACHE and NOCACHE are omitted, Oracle caches 20 sequence numbers by default. For Informix Dynamic Server, since MINVALUE is 1, MAXVALUES is at least 2,147,483,647 and INCREMENT is 1, then there should be no problems with the default. If the table is a target of high activity then, the CACHE values may have to be reviewed along with FREELISTS, INITTRANS, MAXTRANS, and others.)
In Oracle, all the options that were used to create a sequence can be altered except for START WITH.
Resetting the Start Value
To restart an Oracle sequence at a different number, you must drop and re-create it.
The following Table 2-5 shows that Informix Dynamic Server changes the next value to be used in a column, provided 1000 is not less than the current maximum for the column.
Table 2-5 Serial Column Comparison
|Informix Dynamic Server||Oracle|