This chapter compares the MySQL database and the Oracle database. It includes the following major sections:
This section provides an overview of the data storage concepts and methods used by IBM DB2, and the similarities or differences between these methods and concepts and those used by Oracle.
An Oracle database consists of one or more tablespaces. Tablespaces provide logical storage space that links a database to the physical disks hold the data. A tablespace is created from one or more datafiles. Datafiles are files in the file system or an area of disk space specified by a device. A tablespace can be enlarged by adding more datafiles.
A basic Oracle database consists of a SYSTEM tablespace, where the Oracle data dictionary tables are stored. It can also consist of user-defined tablespaces. A tablespace is the logical storage location for database objects. For example, you can specify where a particular table or index is created in the tablespace. The size of a tablespace is determined by the amount of disk space allocated to it. Each tablespace is made up of one or more data files.
The database schema contains the definitions of the tables, indexes, and other database-specific objects.
This section includes information about the following:
There are many similarities between schema objects in Oracle and IBM DB2. However, some schema objects differ between these databases. For more information about schema objects in Oracle, see Oracle Database SQL Language Reference. For more information about the IBM DB2 schema types that do not have a direct mapping in Oracle, see "Design Considerations".
Table 2-1 lists the similarities and differences between the schema objects in the two databases, and specifies if SQL Developer supports the migration of these schema objects.
Table 2-1 Schema Objects in Oracle and IBM DB2
IBM DB2 | Oracle | Status |
---|---|---|
Check Constraint |
Check Constraint |
MigratedFoot 1 |
Column Default |
Column Default |
MigratedFoot 2 |
Database |
Database |
Migrated |
Datalink |
Binary File |
Not Migrated |
Foreign Key |
Foreign Key |
Migrated |
Function |
Function |
Not Migrated |
Group |
Role |
Migrated |
Identity Column |
Auto Increment Column |
Migrated |
Index |
Index |
Migrated |
Primary Key |
Primary Key |
Migrated |
Schema |
User |
MigratedFoot 3 |
Sequence |
Sequence |
Not Migrated |
Stored Procedure |
Stored Procedure |
Not Migrated |
Structured Data Type |
Abstract Datatype |
Not Migrated |
Synonym Table |
Alias |
Migrated |
Table |
Table |
Migrated |
Table Alias |
Public Synonym |
Migrated |
Table Space |
Tablespace |
Migrated |
Tables containing structured data type |
Table containing abstract datatype |
Not Migrated |
Temporary Table |
Temporary Table |
Not Migrated |
Typed Table |
Object Table |
Not Migrated |
Typed View |
Object View |
Not Migrated |
UDB Package |
PL/SQL Package |
Not Migrated |
UDB SQL Function |
PL/SQL Function |
Not Migrated |
UDB SQL Procedure |
PL/SQL Procedure |
Not Migrated |
UDB Trigger |
PL/SQL Trigger |
Not Migrated |
Unique Key |
Unique Key |
Migrated |
User |
User |
MigratedFoot 4 |
View |
View |
Not Migrated |
View containing structured data type |
View containing abstract datatype |
Not Migrated |
Footnote 1 This schema object is migrated but not parsed by SQL Developer
Footnote 2 This schema object is migrated but not parsed by SQL Developer
Footnote 3 For more information about IBM DB2 schema mappings, see Chapter 4, "Offline Data Loading"
Footnote 4 SQL Developer maps user with the prefix USR_, such as USR_GUEST
Reserved words differ between Oracle and IBM DB2. Some Oracle reserved words are valid object or column names in IBM DB2. Use of reserved words as schema object names makes it impossible to use the same names across databases. SQL Developer appends an underscore and a numeric digit (_n) to the end of the name of an IBM DB2 object that is an Oracle reserved word.
Object names are not case sensitive in Oracle or IBM DB2. For a list of Oracle reserved words, see Oracle Database SQL Language Reference
This section describes table design issues that you must consider when converting IBM DB2 databases to Oracle. It contains information about the following:
You can create multiple databases to group segments of related data in Oracle and IBM DB2. If you choose to migrate a number of IBM DB2 databases using SQL Developer at one time, all the IBM DB2 databases are migrated to a single, specified Oracle database.
A referential integrity constraint is the rule that governs the relationship between columns in different tables. Integrity constraints are very similar in the Oracle and IBM DB2 databases. The differences are described in this section.
IBM DB2 supports the ON DELETE and ON UPDATE clauses in referential integrity constraints. Oracle only supports the ON DELETE clause in referential integrity constraints.
Table 2-2 summarizes the delete integrity constraint mappings between Oracle and IBM DB2, and specifies if SQL Developer supports the migration of these clauses.
In IBM DB2 each table is assigned to a table space that contains the primary data for that table. However, a table may optionally store the indexes in a second table space, and large objects in a third table space. You can assign more than one table to the same table space. Each table space consists of a collection of containers, each of which is a directory either in the file system, physical file, or device, such as a hard disk.
IBM DB2 provides system managed space (SMS) and database managed space (DMS). An SMS table space uses the facilities provided by the operating system to manage physical space. IBM DB2 directly manages the physical space in a DMS table space.
Oracle uses dictionary-managed tablespaces and locally-managed tablespaces methods to keep track of their free and used space. The dictionary-managed tablespaces are extents that are managed by the data dictionary, while locally-managed tablespaces are extents that are managed by the tablespace. Oracle recommends locally-managed tablespaces for the following reasons:
Logical management of extents avoids recursive space management operations. This can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation, which consumes or releases space in a rollback segment or data dictionary table.
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
SQL Developer automatically maps the DMS and SMS table spaces to Oracle locally-managed tablespaces. You can use SQL Developer to reset this to dictionary-managed tablespaces, on a per tablespace basis.
An alias in IBM DB2 is an alternative name for a table alias or view. A table alias can be created or dropped. No authority is required to use an alias. However, access to the tables and views referred to by an alias still require the appropriate authorization. An alias can be up to 30 characters long.
SQL Developer migrates a table alias to a public synonym. All user accounts created in Oracle by SQL Developer are granted the CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM system-level privileges. For more information about these privileges, see Chapter 4, "Offline Data Loading".
In IBM DB2 identity columns provide a way to automatically generate unique, sequential and recoverable values for each row in a table. Each table can have only one identity column assigned to it. The column defined as an identity column must be an arithmetic and exact data type, for example SMALLINT, INTEGER, BIGINT, or DECIMAL, where a scale of zero can be used. An identity column has the following characteristics:
It is defined as part of a table only when you create the table. Once you create a table, you cannot alter it to add an identity column.
It generates values for a single table automatically.
When you define it with the GENERATED ALWAYS command, the database manager always generates the values used.
Oracle always maps identity columns to AUTO INCREMENT columns. Oracle uses sequences and triggers to emulate identity columns. To explain this further, assume that you want to create an AUTO INCREMENT column called Auto_Val on a table called MyTable in Oracle. First, you have to create a sequence as in the following example:
CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1 NO MAX VALUE;
In this example, the START WITH value is 1 and this value is incriminated by 1 after every INSERT operation on the table. The NO MAX VALUE specifies that this value is incriminated and is not reset at a certain point. A trigger is then required that inserts the value created by this sequence into the Auto_Val column. You create this as follows:
CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable FOR EACH ROW Begin SELECT MySequence.NEXTVAL INTO :MyTable.Auto_Val FROM DUAL; END;
SQL Developer automatically generates this solution for identity columns. The GENERATED_ALWAYS attribute of identity columns is always obeyed by the AUTO_INCREMENT migration solution. Therefore, all identity columns are generated as if they have the GENERATED_ALWAYS attribute. SQL Developer warns the user that identity columns that do not specify the GENERATED_ALWAYS attribute are migrated with this attribute.
This section describes the differences between the data types used in IBM DB2 and Oracle databases. This section contains information about the following:
Table 2-3 lists the data types available in IBM DB2 and their Oracle equivalents.
All of the IBM DB2 data types support the NULL value. The NULL value is defined as a missing, unknown, or inapplicable value. Although all data types include the NULL value, columns defined as NOT NULL cannot contain NULL values.
Table 2-3 Data Types in Oracle and IBM DB2
IBM DB2 Data Type | Description | Oracle Data type |
---|---|---|
BLOB |
Variable-length binary large object string that can be up to 2GB (2,147,483,647) long. Primarily intended to hold non-traditional data, such as voice or mixed media. BLOB strings are not associated with a character set, as with FOR BIT DATA strings. |
|
CHAR FOR BIT DATA(length) |
Fixed-length character string with a length between 1 and 124 inclusive. If the length is omitted, it defaults to 1. Treated as binary data. |
|
CHAR VARYING FOR BIT DATA(length) |
Variable-length character string with a maximum length of 32,672 bytes. Treated as binary data. |
|
CHAR VARYING(length) |
Variable-length character string with a maximum length of 32,672 bytes |
|
Fixed-length character string with a length between 1 and 124. If the length is omitted, it defaults to 1. |
||
CHARACTER FOR BIT DATA(length) |
Fixed-length character string with a length between 1 and 124 inclusive. If the length is omitted, it defaults to 1. Treated as binary data. |
|
CHARACTER VARYING FOR BIT DATA(length) |
Variable-length character string with a maximum length of 32,672 bytes. Treated as binary data. |
|
CHARACTER VARYING(length) |
Variable-length character string with a maximum length of 32,672 bytes |
|
CHARACTER(length) |
Fixed-length character string with a length between 1 and 124. If the length is omitted, it defaults to 1. |
|
CLOB |
Variable-length character large object string that can be up to 2GB (2,147,483,647) long. A CLOB can store single-byte character strings or multibyte, character-based data. A CLOB is considered a character string. |
CLOB |
DBCLOB |
Variable-length character string that can store up to 1,073,741,823 double-byte characters. It has a DBCS CCSID associated with it. A DBCLOB is considered a graphic string. |
CLOB |
GRAPHIC(length) |
Fixed-length character string with a length between 1 and 127 inclusive. If the length is omitted, it defaults to 1. |
|
LONG VARCHAR(length) |
Variable-length character string with a maximum length of 32,700 bytes |
|
LONG VARGRAPHIC(length) |
Variable-length graphic string with a maximum length of 16,350 double-byte characters. |
|
VARCHAR FOR BIT DATA(length) |
Variable-length character string with a maximum length of 32,672 bytes. Treated as binary data. |
|
VARCHAR(length) |
Variable-length character string with a maximum length of 32,672 bytes |
|
VARGRAPHIC(length) |
Variable-length graphic string with a maximum length of 16,336 double-byte characters |
There are several types of character data types. Each character data type in IBM DB2 can be categorized further into one of the types listed in the Table 2-4. The length of a mixed-data character data type is its total number of bytes, counting two bytes for each double-byte character and one byte for each single-byte character.
A character string is a sequence of bytes. The length of the string is the number of bytes in the sequence. If the length of the string is zero, then this is called the empty string. This is different from a NULL value.
Table 2-4 Categories of character strings
All values of a fixed-length character string column have the same length. The length attribute of the column determines this. The length attribute must be between 1 and 32,766.
All values of a varying-length character string column have the same maximum length. The length attribute of the column determines this. The length attribute must be between 1 and 32,740. Table 2-5 provides definitions of each character string.
Table 2-5 Character String Definitions
String | Definition |
---|---|
CHAR or CHARACTER |
A single character. |
CHAR FOR BIT DATA or CHARACTER FOR BIT DATA |
A single character. Treated as binary data. |
CHAR(length) or CHARACTER(length) |
A fixed-length sequence of characters ranging in length between 1 and 254. |
CHAR(length) FOR BIT DATA or CHARACTER(length) FOR BIT DATA |
This is a fixed-length sequence of characters. Treated as binary data ranging in length between 1 and 254. |
VARCHAR(length) or CHARACTER VARYING(length) or CHAR VARYING(length) |
This is a varying-length sequence of characters ranging in length between 1 and 32,672. |
VARCHAR(length) FOR BIT DATA or CHARACTER VARYING(length) FOR BIT DATA or CHAR VARYING(length) FOR BIT DATA |
This is a varying length sequence of characters. Treated as binary data ranging in length between 1 and 32,672. |
Large object data types store data ranging in size from zero bytes to two gigabytes. There are three large object data types:
A character string comprised of single-byte characters with an associated code page. This data type contains text-oriented information, where the amount of information could grow beyond the limits of a regular VARCHAR data type. SQL Developer supports code page conversion of the information and compatibility with the other character types.
A character string comprised of double-byte characters with an associated code page. This data type contains text-oriented information where double-byte character sets are used. This data type has a maximum length of 1,073,741,823.
A binary string comprised of bytes with no associated code page.
A graphic string is a sequence of double-byte characters. The length of the string is the number of its characters in the sequence. Like character strings, graphic strings can be empty. All values of a fixed-length graphic string column have the same length, which is determined by the length attribute of the column.
The length attribute of the column determines the maximum length that a value can have. Table 2-6 above provides a description for each graphic data type. Each graphic data type can be further categorized into either DBCS data or UCS-2 data.
Two bytes represent every character in this data type. Every DBCS graphic has a Coded Character Set Identifier (CCSID) that identifies a double-byte coded character set.
A character from the Universal Coded Character Set (UCS-2) represent every character of this data type.
Table 2-6 Graphic String Definitions
String | Definition |
---|---|
GRAPHIC |
This is a single graphic character. |
GRAPHIC(length) |
This is a fixed length graphic string of up to 127 characters. |
VARGRAPHIC(length) |
This is a varying length graphic string of up to 16,336 characters. |
LONG VARGRAPHIC |
This is a varying length graphic string of up to16,350 characters. |
All numbers have a sign and a precision. The precision is the total number of binary or decimal digits, excluding the sign. The sign is positive if the value is zero or above. Table 2-7 describes the numeric types available in IBM DB2 and their Oracle equivalents.
Table 2-7 Numeric Types in Oracle and IBM DB2
DB2 UDB Data Type | Description | Oracle Data Type |
---|---|---|
BIGINT |
An eight-byte large binary integer with a precision of 19 digits. The range of this data type is -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807 |
|
DECIMAL(precision, scale) |
A packed-decimal number. The precision is the number of digits and can range between 1 and 31. The scale is the number of digits to the right of the decimal point, and can range from 0 to the value specified for precision. You can use DECIMAL(precision) for DECIMAL(precision, 0). You can also use DECIMAL by itself for DECIMAL(5, 0). The position of the decimal point is determined by the precision and scale (number of digits in the fractional part of the number) of the number. All values of a decimal column have the same precision and scale. The range of a decimal variable or the numbers in a decimal column is -n to +n, where the absolute value of n is the largest number that can be represented with the applicable precision and scale. The maximum range is -1031 + 1 to 1031 - 1. |
|
DOUBLE |
A synonym for FLOAT. |
|
FLOAT(precision) |
A floating-point number. The precision is the number of digits, which can range between 1 and 53. A precision value between 1 and 24 indicates a single-precision, floating-point number. A precision value between 25 and 53 indicates a double-precision, floating point number. If you do not specify a precision value then this is a double-precision, floating-point number. A single-precision, floating-point number is a 32-bit approximation of a real number with a range of 1.17549436 x 10-38 to 3.40282356 x 10+38. A double-precision, floating-point number is a 64-bit approximation of a real number with a range of 2.2250738585072014 x 10-308 to 1.7976931348623158 x 10308. |
|
INTEGER |
A four-byte binary integer. With a precision of 10 digits. The range of this data type is –2,147,483,648 to +2,147,483,647 |
|
NUMERIC(precision, scale) |
A zoned-decimal number. The precision is the number of digits and can range between 1 and 31. The scale is the number of digits to the right of the decimal point, and can range from 0 to the value specified for precision. You can use DECIMAL(precision) for DECIMAL(precision, 0). You can also use DECIMAL by itself for DECIMAL(5, 0). |
NUMBER |
REAL |
A single-precision floating-point number. The number can be zero, and can range from -3.402E+38 to -1.175E-37, or from 1.175E-37 to 3.402E+38. |
|
SMALLINT |
A two-byte binary integer with a precision of 5 digits. The range of this data type is -32,768 to +32,767 |
The base IBM DB2 date data types available and how they are mapped to Oracle are described in Table 2-8.
Table 2-8 Date Data Types in Oracle and IBM DB2
IBM DB2 Data type | Description | Oracle Data type |
---|---|---|
DATE |
This type consists of: year, month, and day. It represents a calendar date. Year can range between 0001 and 9999, month can range between 1 and 12, and day can range between 1 and n, which can be 28, 29, 30, or 31, depending on the value of month and year. The length of a DATE column is 10 bytes, which is the appropriate length for a character string representation of the value. |
DATE |
TIME |
This type consists of: hour, minute, and second. It represents a 24-hour clock time value. Hour can range between 0 and 24, minute can range between 0 and 59, and second can range between 0 and 59. You must set minute and second to 0 if hour is set to 24. The internal representation of a time is a string of 3 bytes. Each byte is two packed-decimal digits. The first byte represents the hour, the second byte the minute, and the last byte represents the second. The length of a TIME column is eight bytes, which is the appropriate length for a character representation of the value. |
DATE |
TIMESTAMP |
This type consists of: year, month, day, hour, minute, second, and microsecond. It represents a calendar date and 24-hour clock time value. The ranges of year, month, and day are the same as the date data type. The ranges for hour, minute, and second are the same as the time data type. Milliseconds can range between 0 and 999. The internal representation of a TIMESTAMP is a string of 10 bytes, each of which consists of two packed-decimal digits. The first four bytes represent date, the next three bytes represent time, and the last three bytes represent microseconds. The length of a TIMESTAMP column is 26 bytes, which is the appropriate length for the character string representation of the value. |
TIMESTAMP |
The values of the data types DATE, TIME and TIMESTAMP are represented by an internal format. However, these data types can also be represented by character strings. There are no constants or variables with Datetime data types. You must assign a datetime value to a character string variable, in order to retrieve it. You cannot use a LOB string or LONG VARCHAR as the string to represent a datetime value, regardless of its length.
The valid string representations for DATE values in IBM DB2 are listed in Table 2-9.
Table 2-9 Valid string representations for DATE values in IBM DB2
Format Name | Format | Example |
---|---|---|
International Standards Organization (ISO) |
yyyy-mm-dd |
2002-05-30 |
IBM USA Standard (USA) |
mm/dd/yyyy |
05/30/2002 |
IBM European Standard (EUR) |
dd.mm.yyyy |
30.05.2002 |
Japanese Industrial Standard Christian era (JIS) |
yyyy-mm-dd |
2002-05-30 |
Site-defined (LOC) |
Depends on database country code |
Not Applicable |
The string representation of DATE begins with a digit and has a length of at least eight characters. Trailing blanks may be included, and you can omit leading zeroes from the month and day sections.
Table 2-10 lists the valid string representations for TIME values in IBM DB2.
Table 2-10 Valid string representations for TIME values in IBM DB2
Format Name | Format | Example |
---|---|---|
International Standards Organization (ISO) |
hh.mm.ss |
17.30.01 |
IBM USA Standard (USA) |
hh:mm AM or PM |
5:30 PM |
IBM European Standard (EUR) |
hh.mm.ss |
17.30.01 |
Japanese Industrial Standard Christian era (JIS) |
hh:mm:ss |
17:30:01 |
Site-defined (LOC) |
Depends on database country code |
The string representation of a TIME starts with a digit and has a length of at least four characters. You can include trailing blanks, and a leading zero may be omitted. If you omit seconds, an implicit specification of zero seconds is assumed.
The string representation of a TIMESTAMP starts with a digit and has a length of at least 16 characters. And has the form: yyyy-mm-dd-hh.mm.ss.nnnnnn. You can include trailing blanks, and you can omit leading zeroes from month. Day and hour and microseconds may be truncated or entirely omitted. If you omit any trailing zeroes in microseconds, an implicit specification of 0 is applied for the missing digits.
You can omit the minutes specification for the USA standard time string format. If minutes are omitted, an implicit specification of 00 minutes is assumed, so 5p.m. is 5:00 PM. In addition, the hour must be between 1 and 12 inclusive in the case of 00:00 AM. There is a single space before the AM or PM.
Note:
The ISO changed the time format so that it is identical to the JIS standard. You should use the JIS standard if an application requires the most up-to-date ISO format.The .ss or :ss part in the ISO, EUR and JIS standards is optional.
Date, time and timestamp strings must contain only single-byte characters and digits in IBM DB2.
SQL Developer does not support the migration of IBM DB2 datalinks. Where it is necessary to migrate IBM DB2 datalinks, Oracle recommends creating an Oracle binary file (BFILE). For more information about Oracle binary files, see Oracle9i SQL Reference.
IBM DB2 provides the capability to define new data types based on built-in data types. These data types are called user-defined distinct data types.
SQL Developer does not support the migration of user-defined distinct data types. Any column in any table that uses a user-defined distinct type (defined in IBM DB2) is defined with the base type in the Oracle Model.
A structured data type is a user-defined data type, and its structure defined in the database. This data type comprises a sequence of named attributes, each of which has a data type. A structured data type may also be defined as a subtype of another structured type, called its supertype. A subtype inherits all the attributes of its supertype and can have additional attributes defined.
You can use a structured type as the data type of a table or view. The names and data types of the structured type become the names and data types of the columns of the table (typed table). Rows of the typed table represent instances of the structured type.
A structured data type instance can also be stored in the database as a value in a column. To store objects in table columns, columns are defined using the structured data type.
The logical mapping for an IBM DB2 structured data type in Oracle is an abstract datatype. SQL Developer does not support the migration of structured data types.