2
Oracle and IBM DB2/400 V4R5 Compared

This chapter describes Oracle and IBM DB2/400 V4R5 database concepts, including similarities and differences between the two database. It includes information on the following:

• Data Storage Concepts
  
  
• Schema Migration
  
  
• Data Types
  
  
• Database Security

Data Storage Concepts

This section provides an overview of the data storage concepts and methods used by IBM DB2/400 V4R5, and the similarities or differences between these methods and concepts and those used by Oracle.

Oracle Data Storage Concepts

An Oracle database consists of one or more tablespaces. Tablespaces provide logical storage space that links a database to the physical disks which 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 diskspace allocated to it. Each tablespace is made up of one or more datafiles.

IBM DB2/400 V4R5 Data Storage Concepts

IBM DB2/400 V4R5 is an integrated part of OS/400, the AS/400 operating system. Everything in the OS/400 operating system, including the database, is organized as objects. OS/400 contains over 80 types of objects, including programs, database files, and user profiles. OS/400 stores program instructions, application data, and other system components on disk and loads them into main memory as required. However, OS/400 does not permit direct access to the memory on disk or to memory directly. Instead, you must always use specific commands or system interfaces that are valid for each type of object.

The IBM DB2/400 V4R5 Library

OS/400 controls how you use an object by storing some descriptive information with the content of the object. All objects have a header, which consists of two parts, a standard part and a type-specific part. The type-specific part specifies the object. For example: *PGM equals program and *FILE equals file type. The standard part of the header contains the following information:

• The library that contains the object
  
  
• The object's name
  
  
• The object's type and subtype
  
  
• The user profile that owns the object

An IBM DB2/400 V4R5 library is an object that contains other objects, but a library cannot contain another library object. As the containment properties are similar, the Migration Workbench maps library names to Oracle tablespaces.

Object Name

IBM DB2/400 V4R5 object names can be up to 10 alphanumeric characters in length, beginning with a letter or a national character. Libraries also have names, for example the name, custdata might be used for a library that contains data relating to a company's customers. An object's qualified name is the combination of the name of the library that contains it, and the object's unqualified name, separated by a forward slash, for example custdata/abcproductions.

Object Type and Subtype

Each object type in AS/400 is designated by a special value, for example, *PGM for program and *FILE for file. Some object types also have subtypes. The *FILE object includes physical files, logical files, printer files, display files, and communication files. An OS/400 object is uniquely identified by the combination of its qualified name and its object type, which means OS/400 allows only one object on an AS/400 database that has a given combination of library, name and object type. You cannot have two files with the same name in the same library, however, you can have two different object types of the same name in the same library.

This concept of unique names for objects is similar to the Oracle criteria that no two objects in a namespace can have the same name. Figure 2-1 shows the namespace for schema objects in Oracle. The tables and views are in the same namespace within a schema and therefore cannot have the same name, whereas tables and indexes which are not in the same namespace can have the same name within a schema.

Figure 2-1 Namespace for Schema Objects in Oracle

Text description of the illustration 2_1.gif

During the capture phase of the migration, if the Migration Workbench finds object names that do not conform to the Oracle schema-naming convention, it substitutes the original schema names with names allowed by Oracle by introducing an underscore to the original name. For example, as Oracle does not allow whitespaces in schema names, the Workbench would convert COLUMN NAME to COLUMN_NAME. You can see this in the Migration Workbench after the Capture phase, by comparing schema names in the Source Model with the schema names in the Oracle Model.

User Profiles

Each OS/400 object is owned by a user profile. A user profile is another type of OS/400 object. Each user profile stores information about a system user, including the user name, password, and authority to access data or use system functions. Whenever you log in, you supply a user profile name and password, allowing AS/400 to control your use of the system, including access to the database. This security system is similar to the Oracle security system, where each user has a password that allows access to the database. Each user also has a set of privileges for objects in the database. In Oracle, the creator or owner of an object is automatically given full access rights to that object. The owner of an object can, in turn, grant privileges such as select and update for this object to other users. In Oracle, the database catalogs store the privilege information for each user.

IBM DB2/400 V4R5 File Types

There are two types of IBM DB2/400 V4R5 files, physical files and logical files.

Physical Files

Application data is stored in physical files. Each record, or entry in a database file, occupies a unique location in a physical file. The records are not necessarily in any order based on their content. A record's location is identified by its relative record number (RRN), which starts at 1 for the first record in the member and increases by 1 for each location. When you delete a record IBM DB2/400 V4R5 sets an internal deleted record flag in the record's location. When you insert a new record, IBM DB2/400 V4R5 either puts it in the first available location with a deleted record flag, or after the last record in the file.

A physical file always has just one record format, and all records in the same physical file have the same record layout.

Logical Files

Logical files provide an alternative way to access data in one or more physical files. You can use a logical file to:

• Select a subset of the records in a physical file
  
  
• Merge the records from multiple physical file members
  
  
• Select a subset of the fields in a physical files record format
  
  
• Combine related records in two or more physical files
  
  
• Provide an index so records can be retrieved in particular order
  
  
• Perform a Backup and Recovery operation

IBM DB2/400 V4R5 Backup Facility

The IBM DB2/400 V4R5 system has backup facilities for everything on the system. The following table shows the save and restore commands for database files and associated objects such as journals:

IBM DB2/400 V4R5 Command	Description
SAVLIB	Saves one or more libraries and all objects in them
SAVOBJ	Saves one or more specific objects in one or more libraries
SAVCHGOBJ	Saves one or more objects that have been changed since the last time they were saved by a SAVLIB command or since a specified date and time
RSTLIB	Restores database library
RSTOBJ	Restores database objects

In Oracle, each database has a set of two or more redo log files. All changes made to a database are recorded in these files. You can use redo log files to restore corrupt Oracle databases. Oracle allows mirrored redo log files, allowing you to maintain two or more copies of these files. By using mirrored redo log files you can protect them from loss, for example, from a hardware failure.

Schema Migration

The schema contains the definitions of the tables, views, indexes, and other database-specific objects.

This section includes information on the following:

• Schema Object Similarities
  
  
• Schema Object Names
  
  
• Table Design Considerations
  
  
• Schema Migration Limitations for IBM DB2/400 V4R5

Schema Object Similarities

There are many similarities between schema objects in Oracle and IBM DB2/400 V4R5. However, some schema objects differ between these databases. For specific information about schema objects within Oracle8i, refer to the SQL Statements topic in the Oracle8i SQL Reference. Table 2-1 shows the similarities and differences between the schema objects in the two databases.

Table 2-1 Schema Objects in Oracle and IBM DB2/400 V4R5

Oracle	IBM DB2/400 V4R5
Database	Database
Tablespace	Collection
User	User Profile
Role	Group Profile/Authorization list (not migrated)
Table	Table
Temporary Table	Not applicable
Index	Index
Check constraint	Check constraint (not migrated)
Column default	Column default
Unique key	Unique key
Primary key	Primary key
Foreign key	Foreign key
PL/SQL procedure	External procedure/CL procedure (not migrated)
PL/SQL function	SQL/400 function (not migrated)
Packages	Not applicable
Triggers	Triggers (not migrated)
Private synonyms	Table alias
Sequences	Not applicable
Snapshot	Not applicable
View	View (migrated but not parsed)

Schema Object Names

Reserved words differ between Oracle and IBM DB2/400 V4R5. Some Oracle reserved words are valid object or column names in IBM DB2/400 V4R5. 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 end of the name of an IBM DB2/400 V4R5 object that is an Oracle reserved word.

Object names are not case sensitive in Oracle or IBM DB2/400 V4R5. For a list of Oracle reserved words, refer to the Oracle8i SQL Reference.

Table Design Considerations

This section describes table design issues that you must consider when converting IBM DB2/400 V4R5 databases to Oracle. It contains information on the following:

• Referential Integrity Constraints
  
  
• Collection Mappings
  
  
• Alias Mappings

Referential Integrity Constraints

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/400 V4R5 databases, but there are some differences. Both Oracle and IBM DB2/400 V4R5 use the ON DELETE clause in referential integrity constraints, but, in addition, IBM also uses the ON UPDATE clause.

The ON UPDATE clause determines what action is taken if an insertion in the designating table creates an unmatched, non-null foreign key value. There is no direct mapping of the IBM DB2/400 V4R5 ON UPDATE clause to Oracle, so this clause is not migrated to Oracle.

The ON DELETE clause determines what action is taken if a primary key value in the target table is deleted. The deletion of a primary key value could potentially leave orphan rows in the designating table that no longer reference an existing row in the target table. There are a number of possible On Delete clauses in IBM DB2/400 V4R5. However, only the ON DELETE CASCADE clause is migrated to from IBM DB2/400 V4R5 to Oracle

.

Collection Mappings

A library is an OS/400 object that contains other objects, such as files and programs. In SQL/400 terminology, this container is called a collection. The Oracle equivalent of an IBM DB2/400 V4R5 collection is a tablespace. An Oracle tablespace is used to group related logical structures together, so collection names in IBM DB2/400 V4R5 are mapped to tablespaces in Oracle.

Alias Mappings

An alias in IBM DB2/400 V4R5 is an alternative name for a table or view. An 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.

The Oracle equivalent of an IBM DB2/400 V4R5 alias is a synonym. A synonym is an alternative name for any table, view, snapshot, sequence, procedure, function, or package. As it is only an alternative name, it does not require storage, other than the storage of its definition in the data dictionary.

You can create both public and private synonyms in Oracle. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is owned by a specific user who has control over its availability to others. Table aliases in IBM DB2/400 V4R5 are mapped to private synonyms in Oracle.

Schema Migration Limitations for IBM DB2/400 V4R5

This section describes IBM DB2/400 V4R5 schema migration limitations. It contains information on the following:

• Parsable Objects
  
  
• Privileges

Parsable Objects

Except for view, the migration of parsable objects such as stored procedures, triggers and check constraints is not supported by the Migration Workbench. However, in IBM DB2/400 V4R5, views are defined using SQL/400, which does not map directly to PL/SQL. When the SQL text for IBM DB2/400 V4R5 views is migrated to Oracle, this SQL text is not converted to PL/SQL.

Privileges

The Migration Workbench migrates all user names, including their table-level privileges. The access privilege system in IBM DB2/400 V4R5 is integrated into the OS/400 operating system, and is therefore not available in IBM DB2/400 V4R5 catalogs. You can use special add-in programs to determine the users of the system and their table-level privileges. These add-in programs are installed in the $ORACLE_HOME/Omwb/addins/as400/v4r3 directory. The Migration Workbench automatically transfers these programs to the source AS/400 system during the migration using File Transfer Protocol (FTP). They are then executed on the AS/400 system. The Migration Workbench does not support the privileges defined in IBM DB2/400 V4R5 Groups or Authorization lists.

Data Types

This section describes the differences between the data types used in IBM DB2/400 V4R5 and Oracle databases. Specifically, this section contains information on the following:

• Data Types in Oracle and IBM DB2/400 V4R5
  
  
• Character Strings
  
  
• Graphic Strings
  
  
• Numeric Types
  
  
• Date Data Types

Data Types in Oracle and IBM DB2/400 V4R5

Table 2-2 shows the data types available in IBM DB2/400 V4R5 and their Oracle equivalents.

Table 2-2 Data Types in Oracle and IBM DB2/400 V4R3 

DB2400 V4R3 Data type	Description	Oracle Data type
CHAR(length)	Fixed-length character string with a length between 1 and 32766 characters (if the length is omitted, it defaults to 1)	CHAR
CHARACTER(length)	Fixed-length character string with a length between 1 and 32766 characters (if the length is omitted, it defaults to 1)	CHAR
CHAR FOR BIT DATA(length)	Fixed-length character string with a length between 1 and 32766 characters (if the length is omitted, it defaults to 1) To be treated as binary data.	CHAR
CHARACTER FOR BIT DATA(length)	Fixed-length character string with a length between 1 and 32766 characters (if the length is omitted, it defaults to 1) To be treated as binary data.	CHAR
VARCHAR(length)	Variable-length character string with a maximum length of 4000 bytes	VARCHAR2
CHAR VARYING(length)	Variable-length character string with a maximum length of 4000 bytes	VARCHAR2
CHARACTER VARYING(length)	Variable-length character string with a maximum length of 4000 bytes	VARCHAR2
VARCHAR FOR BIT DATA(length)	Variable-length character string with a maximum length of 4000 bytes. To be treated as binary data.	VARCHAR2
CHAR VARYING FOR BIT DATA(length)	Variable-length character string with a maximum length of 4000 bytes. To be treated as binary data.	VARCHAR2
CHARACTER VARYING FOR BIT DATA(length)	Variable-length character string with a maximum length of 4000 bytes. To be treated as binary data.	VARCHAR2
LONG VARCHAR(length)	Variable-length character string with a maximum length of 32700 bytes	VARCHAR2
GRAPHIC(length)	Fixed-length graphic string with a length between 1 and 127 double-byte characters	VARCHAR2
VARGRAPHIC(length)	Variable-length graphic string with a maximum length of 2,000 double-byte characters	VARCHAR2
LONG VARGRAPHIC(length)	Variable-length graphic string with a maximum length of 16,350 double-byte characters.	CLOB
BLOB	Variable-length binary large object string that can be up to 2GB long.	BLOB
CLOB	Variable-length character large object string that can be up to 2GB long. A CLOB can store single-byte character strings or multibyte, character-based data.	CLOB
	Variable-length, double-byte character string that can store up to 1,073,741,823 characters.	BLOB
DBCLOB	Variable-length, double-byte character string that can store up to 1,073,741,823 characters.	BLOB

IBM DB2/400 V4R5 User-Defined Data Types

IBM DB2/400 V4R5 provides a method for a user to declare specialized usages of datatypes and the rules which apply to them. You can use a user-defined or distinct data type as the data type for any column in the database. Defaults and rules (check constraints) are also bound to these user-defined data types, and are applied automatically to the individual columns of these user-defined data types.

When migrating to Oracle using the Oracle Migration Workbench, the base data type for each user-defined type is determined, and the PL/SQL equivalent of that base type is applied to the column definitions of the target database's tables, in place of the user-defined data types.

Character Strings

There are several types of character strings. Each string can be categorized further into one of the types shown in the following table:

Type	Description
Bit data	Data not associated with a coded character set and is never converted.
Single byte character set (SBCS) data	Data in which every character is represented by a single character set. Each SBCS data character string has an associated coded character set identifier (CCSID).
Mixed data	Data that can contain a mixture of characters for the SBCS and the double byte character set (DBCS).

The Database Manager in IBM DB2/400 V4R5 recognizes DBCS strings by enclosing them in two EBCIDIC codes:

• X'0E' marks the beginning of a sequence of double-byte codes
  
  
• X'0F' marks the end of a sequence of double byte-codes

The length of a mixed-data character string 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 string should not be confused with NULL.

Fixed-Length Character Strings

All values of a fixed-length character string column have the same length. This is determined by the length attribute of the column. The length attribute must be between 1 and 32766.

Varying-Length Character Strings

All values of a varying-length character string column have the same maximum length, which is determined by the length attribute of the column. The length attribute must be from 1 and 32740.

Table 2-3 provides definitions of each character string.

Table 2-3 Character String Definitions

String	Definition
CHAR or CHARACTER	A single character.
CHAR FOR BIT DATA or CHARACTER FOR BIT DATA	A single character that is to be 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 that must be 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 that is to be treated as binary data ranging in length between 1 and 32,672.

Large Object Data Types

Large object data types store data ranging in size from zero bytes to 15 megabytes. There are three large object data types:

Character Large Objects (CLOBs)

A character string comprised of single-byte characters with an associated code page. This data type holds text-oriented information, where the amount of information could grow beyond the limits of a regular VARCHAR data type (upper limit of 32K bytes). The Migration Workbench supports code page conversion of the information and compatibility with the other character types.

Double-Byte Character Large Objects (DBCLOBs)

A character string comprised of double-byte characters with an associated code page. This data type holds text-oriented information where double-byte character sets are used. This data type has a maximum length of 7864320.

Binary Large Objects (BLOBs)

A binary string comprised of bytes with no associated code page.

Graphic Strings

A graphic string is a sequence of two-byte characters. The length of the string is the number of its characters. 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 must be between 1 and 16383. The values of a column with a varying-length graphic string, such as a VARGRAPHIC or DBCLOB, can have different lengths. The length attribute of the column determines the maximum length that a value can have.

For a VARGRAPHIC column, the length attribute must be between 1 and 16370. For a DBCLOB column, the length attribute must be between 1 and 7864320.

Each graphic data type can be further categorized into either DBCS data or UCS-2 data.

DBCS Data

A data type where every character is represented by a character from the double-byte character set. Every DBCS graphic has a Coded Character Set Identifier (CCSID) that identifies a double-byte coded character set.

UCS-2 Data

A data type where every character is represented by a character from the Universal Coded Character Set (UCS-2).

Table 2-4 provides definitions of each graphic string.

Table 2-4 Graphic String Definitions 

String	Definition
GRAPHIC	This is a single graphic character.
GRAPHIC(length)	This is a fixed length graphic string of Length graphic characters. Length can range between 1 and 127.
VARGRAPHIC(length)	This is a varying length graphic string of up to Length graphic characters. Length can range between 1 and 16,336.
LONG VARGRAPHIC	This is a varying length graphic string of up to 16,350 characters.

Numeric Types

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.

Table 2-5 describes the numeric types available in IBM DB2/400 V4R5 and their Oracle equivalents.

Table 2-5 Numeric Types in Oracle and IBM DB2/400 V4R5  

DB2400 V4R3 Data type	Description	Oracle Data type
SMALLINT	Two-byte binary integer with a precision of 5 bits. The range of this data type is -32, 768 to +32, 767	NUMBER (6, 0)
INTEGER	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	NUMBER (11, 0)
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	NUMBER (19, 0)
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.	FLOAT
DOUBLE	A double-precision floating-point number, which is a 64-bit approximation of a real number. The range of this data type is 2.2250738585072014 x 10-308 to 1.7976931348623158 x 10+308.	FLOAT(53)
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.	FLOAT (24)
REAL	A single-precision floating-point number.	FLOAT (24)
NUMERIC(precision, scale)	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

Date Data Types

Although date data types are used in certain arithmetic and string operations and they are compatible with certain strings, they are neither strings nor numbers.

Table 2-6 Date Data Types in Oracle and IBM DB2/400 V4R5

DB2400 V4R3 Data type	Description	Oracle Data type
DATE	This type consists of three parts: year, month, and day, and 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.	DATE
TIME	This type is made up of three parts - hour, minute, and second 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. Minute and second must be set to 0 if hour is set to 24.	CHAR(8)
TIMESTAMP	This type consists of seven parts: year, month, day, hour, minute, second, and millisecond 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, and the ranges for the hour, minute, and second are the same as the time data type. Milliseconds can range between 0 and 999. The TIMESTAMP data type maps to the DATE data type in Oracle by default.	DATE

Database Security

This section includes information on IBM DB2/400 V4R5 and Oracle database security.

IBM DB2/400 V4R5 Privilege System

In OS/400, everything is an object. OS/400 objects include the following types that are essential to IBM DB2/400 V4R5 operations:

• User profiles (*USRPRF)
  
  
• Libraries (*LIB)
  
  
• Database files (*FILE)
  
  
• Programs (*PGM)
  
  
• Authorization lists (*AUTL)

These types are described in the following User Profiles and Authorities sections.

User Profiles

A user profile object represents a user of an AS/400 system. A user profile has a name, password, and a set of values that control various aspects of security. The user profile has a unique password that the user must enter to sign-on to an AS/400 system. The security officer (QSECOFR), or individual in charge of an organization's applications, grants the user profile the appropriate authority to access various OS/400 objects so that the user can work with application programs and data. After a user has signed on with a particular user profile, that user profile governs all access to other objects, including libraries, programs, and files.

OS/400 security is conceptually simple. When a user profile attempts an operation, OS/400 checks to ensure that the user profile has adequate authority to perform the operation on the target object.

Authorities for Libraries, Programs, and Database Files

To access any object, a user profile must have *EXECUTE authority for the library containing the object. Without this authority, the user profile cannot perform any actions with objects in the library. To enable a user to access one or more of the objects in a library, the user profile *USE authority (which includes *EXECUTE authority) must normally be granted to the library.

To access data through an open physical file member, a user profile must have *OBJOPR authority for the field, and one or more of the *READ, *ADD, *UPD, or *DLT data authorities for the physical file. The data authorities control access to records by the read, add, update, and delete operations.

To access data in a physical file member by opening a logical file member, a user profile must have *OBJOPR authority and one or more of the *READ, *ADD, *UPD, or *DLT data authorities for the logical file. In addition, the user profile must have the required data authorities for the physical file. For example, to read and update records through a logical file, the user must have *OBJOPR, *READ, and *UPD authorities for the logical file and *READ and *UPD authorities to the physical file.

Public Authority

Public authority lets you grant authority to more than one user profile at a time. Every object has public authority, which controls access by user profiles that are not otherwise authorized to the object.

In Oracle, there is a special user group named PUBLIC. Every user's security domain includes the privileges and roles granted to the PUBLIC user group.

Group Profiles and Authorization Lists

Groups provide a way to identify an individual user profile as a member of one or more groups. The migration of groups is not supported by this release of the Migration Workbench for IBM DB2/400 V4R5.

Authorization lists provide a somewhat comparable feature for groups, allowing you to organize sets of objects that have been granted identical authorities. The migration of authorization lists is not supported by this release of the Migration Workbench for IBM DB2/400 V4R5.

Object Ownership

Every object is owned by a user profile. The owner of an object normally has all authorities for an object.

For every table object in the source database, the Migration Workbench determines the owner of the table object, and the users granted privileges for that table object. The Migration Workbench then determines if the user has SELECT, INSERT, UPDATE, DELETE, and EXECUTE privileges for the table object. It also ensures that these privileges are reinstated during migration, to ensure the user has the same table-level privileges in the destination Oracle database as in the source IBM DB2/400 V4R5 database. Therefore, a large portion of the original privilege system is maintained during the migration process.

Migration Workbench Add-In Programs

As stated in Chapter 1, IBM DB2/400 V4R5 is integrated with to OS/400, the operating system for AS/400. The database catalogs for IBM DB2/400 V4R5 do not contain the user information (names, IDs, and privileges) because of this integration with the operating system. To migrate this information, the Migration Workbench requires add-in programs, which are copied to, and run on, the source AS/400 system. The Migration Workbench uses FTP to send a SaveFile containing programs that collect this information. During the loading phase of the migration, these programs are copied to the OMWB library on the AS/400 system.

The SaveFile contains a progam called GETAUTHOBJ that contains the object authorities and permissions, and a program that obtains the users of the system. These programs run under the authority of the owner of these programs, that is under the authority of the OS/400 user profile, which ran the DORST program on the SaveFile. After the Migration Workbench has successfully migrated data, the library containing these two programs can be deleted.