Before installing Oracle Database Provider for DRDA software, an organization must consider several operational and resource issues. Flexibility and performance of machine and network resources is paramount when determining whether an optimal installation is as a standalone Oracle home, an Oracle home within an existing Oracle Database, or on a machine that is entirely separate from the Oracle Database. Additionally, the nature of all possible DB2 clients that must use the installation is a determining factor; in this context, DB2 is considered a client.

Before installing Oracle Database Provider for DRDA objects in the Oracle Database, one or more users must be designated as DRDA Administrators, and have the Administrator role.

Similarly, designate users who will be accessing the Oracle Database through Oracle Database Provider for DRDA or DB2 applications, and grant to them roles and privileges of DRDA User. Some aspects of setting the DRDA User's authority and configuration may need to be delayed until further in the migration process. This mostly concerns specific DRDA packages used by the application, and any specific SQL translations or datatype tuning. If the application's packages are identified before migration, these may be applied as part of the package authorization workflow.

In order to successfully access Oracle Database from DRDA or DB2 applications through Oracle Database Provider for DRDA, package authorization must be in place. At a minimum, the following information must be collected about the application and its users:

• package collection ID, such as NULLID

• package name, such as DSNPBD3

• package version name, if applicable, such as 01 or NULL

• name of the Oracle user who must access the database, such as DRDAUSR

A SQL Translation Profile Name must also be designated for the application represented by the package.

In DB2, objects may be created under an arbitrary schema, whereas schema names are not arbitrary in Oracle Database. Therefore, careful use of schemas must be considered when migrating data from DB2 to Oracle. In Oracle, all schema objects, such as tables, views, synonyms, and so on, must be allocated in a schema of an actual user. This obviously effects how these objects are named, created, and accessed.

Consider the following example: USER1 creates tables "USER1"."TABLE1" and "USER2"."TABLE2". In DB2, TABLE1 and TABLE2 are owned by USER1, because USER1 is their creator. In Oracle, the table "USER2"."TABLE2" is owned by user USER2. Additionally, USER1 could not have created TABLE2 unless USER1 has CREATE ANY TABLE privelege. Instead, USER2 must create TABLE2, and then grant USER1 access to it.

Data migrated from DB2 to Oracle must be defined also in terms of Oracle datatypes. While Oracle uses ANSI-defined datatype names, they do not necessarily have the same range limits or semantics as the DB2 implementation. To accurately model existing DB2 application datatypes, review the section Data Dictionary for Oracle Database Provider for DRDA.

After creating the schema and objects with appropriate datatypes, the data may be imported into Oracle.

Note that the following examples illustrate how to migrate DB2 z/OS applications. You would need to follow similar steps when migrating DB2/LUW or DB2/400 applications. Refer to IBM documentation for details of each product's equivalent steps.

There are two general categories of applications: native applications and remote applications.

Some applications may have DB2-specific SQL that is beyond the automatic translation mechanism of SQL translation, or may be expecting a very specific datatype for a particular column in a query. In such cases it may be necessary to manually insert SQL substitution statements, or add item-specific datatype manipulations.

For example, suppose an application has a specific SQL statement that has the following DB2-specific syntax: SELECT LOG2(COL1) FROM TABLE1. To work correctly in Oracle, the SQL needs to be translated into this statement: SELECT LOG10(COL1,2) FROM TABLE1.

Through SQL Translation's Register facility, a direct translation may be registered for this SQL statement, as shown in the section Registering a SQL Substitution Statement. Note that this must be done by the user who is executing the SQL statement; remember that the SQL Translation Profile must created as a resource for that user.

After the SQL translator is registered, when the application issues the original SQL it is implicitly translated to the new SQL and processes.

In some very specific cases, application clients require the datatypes of select items in a query to be returned in a very specific format.

Let's say that the result of the translated query SELECT LOG10(COL1,2) FROM TABLE1 returns a DECFLOAT34 datatype, but the application is unable to process it, it is possible to implicitly coerce the datatype to another, compatible type.

If the application supports the DOUBLE PRECISION datatype, it is possible to use the TYPEMAP facility to add this specific coercion described in Registering an On-Demand Datatype Conversion.

connect DRDAUSER/userpwd
execute dbms_sql_translator.register_sql_translation('DB2ZOS',
   'SELECT LOG2(COL1) FROM TABLE1',
   'SELECT LOG10(COL1,2) FROM TABLE1')

connect DRDAADM/adminpwd

execute DBMS_DRDAAS_ADMIN.SET_TYPEMAP('NULLID', 'DSNPBD3', NULL,
   'TABLE1:LOG10(COL1,2)', 'NUMBER=DOUBLE')

execute DBMS_DRDAAS_ADMIN.SET_TYPEMAP('NULLID', 'DSNPBD3', NULL,
   'TABLE1:LOG10(COL1,2)', 'NUMBER(0,-127)=DOUBLE')