Oracle to DB2 UDB Conversion Guide2003

  < Day Day Up > 

4.5 The Refine task

Once the conversion task has completed, by default, the tool automatically shifts to the Refine tab (Figure 4-16). The Refine tab is subdivided, on the lower left hand side of the left pane, into four separate sub-tabs titled Oracle, DB2, Report and Messages.

Figure 4-16: The Refine tab

4.5.1 Message categories and migration impact

As previously stated, the Messages view displays the messages sorted by message category. The message categories are:

Input Script Error

This message category occurs when the input script or set of objects to be converted is incomplete. Most frequently, messages in this category occur when an object is missing the definition for an object on which it depends. For example, a stored procedure, which refers to a table for which the definition is missing. Sometimes an object definition does exist, but its use may require qualification with a database or owner name. Other errors in this category include PL/SQL syntax errors.

Migration impact - LOW

Since this type of message is easily understood and corrected (usually by including the missing definition in the source file), the migration impact is low and has little bearing on the final product or on the level-of-effort to achieve that product.

This type of message occurs most frequently when files are taken into the tool through IMPORT. As can be expected, it is more likely that due to human error, an incomplete DDL may be gathered as the conversion source.

Translator information

This category occurs when a correct DB2 translation exists, but when more information is necessary to describe some unusual or exceptional property of the translation. For example, messages in this category are used to highlight the fact that the name of a PL/SQL object or identifier has been changed to satisfy the DB2 restrictions on identifier formation (such a change might be relevant to client programs that refer to the object by name).

Migration impact - LOW/MEDIUM

This type of message should be examined to understand the scope of the message. If the message indicates that an object name has been changed to conform to a DB2 specification - this may have little or no impact on the migration effort. If, however, the changes generated by the converter require alterations to the client code, the effort may be more extensive.

Translator Warning

This category occurs when the translation of the PL/SQL code to which the message refers might be incomplete or incorrect in certain unusual or exceptional cases. The message typically describes the circumstances in which the translation will not be correct.

Migration impact -MEDIUM

This type of message needs to be examined to determine if the circumstances described are relevant to your application. If so, manual intervention may be required to successfully convert and deploy the object.

Translator Error

This category is used for PL/SQL statements for which no translation is possible. Most frequently, this message category is used when no equivalent DB2 functionality exists. It is also used in cases where a correct translation requires application-specific information. It also occurs for certain complex or rarely used constructs.

Migration impact -HIGH

This type of error usually indicates that some degree of manual intervention will most likely be required. It is important that the analyst review the code to understand for which objects, and to what degree, the manual intervention will be necessary.

4.5.2 The Messages sub-tab

The messages sub-tab (Figure 4-17) is divided into left and right-hand panes. From these panes it is possible to perform the following actions:

Figure 4-17: The Messages sub-tab

View Translator messages by message number

Figure 4-17 shows the Messages tab for our example. In this example, the messages are grouped into three categories:

For each category we can "drill-down" to a specific instance of a particular message. To accomplish this, follow these steps:

  1. Expand the category.

  2. Expand message number.

  3. Expand the message description.

View the source/converted code

Once the message description is expanded, if you highlight a specific instance of the message, the right-hand pane opens to the corresponding line in the Oracle source code to which the message refers. In our example (Figure 4-18), we open to the source code pertaining to message number 120, for the tabs_views_seqs file, at line numbers 9–14.

Figure 4-18: The Messages sub-tab opened to a specific line in the Oracle source

It is possible to toggle from the Oracle source code to the converted DB2 code by choosing from the right-hand pane, either the source file: <your_source_file_name.src> tab or the DB2 file: <your_conversion_file_name.db2> tab. In our example (Figure 4-19), we have toggled to the DB2 file: tabs_views_seqs.db2 from the Source file: tabs_views_seqs.src.

Figure 4-19: The DB2 file view for message 120

Additional message information

If we click the Message Help button in the upper right-hand corner of the Message sub-tab, the following screen opens (Figure 4-20). This help screen contains additional context sensitive information about the message number in question. In our example, the Message Help screen shows additional information regarding message 120.

Figure 4-20: Message Help screen displayed for message 120

4.5.3 Translator messages

The following section contains information regarding the messages that were generated for each category during our example migration. In our example, we received messages in the following categories:

Translator Error messages

In the Translator Error category the following message occurred:

Message 120: the CREATE SEQUENCE statement contains a value that is out of range

This message occurred for two objects; first for employee_sequence and again for office_sequence. Message Help indicates:

The CREATE SEQUENCE statement contains a MINVALUE, MAXVALUE or START VALUE clause with a value that is outside the range that can be handled by the converter.

When the DDL is investigated, it is discovered that the statement:

MAXVALUE 999999999999999999999999999

Contains a value that exceeds the maximum value allowed by DB2. If we toggle to the DB2 file: tabs_views_seqs.db2 we see that the converter has indeed made an adjustment so that the conversion will be accepted by DB2. The adjustment consists of changing the MAXVALUE to 9223372036854775807 - the largest possible size for a BIGINT value.

Translator warning messages

The following translator warning messages occurred:

Msg number 20: object name has been changed to <new name> Msg number 34: No DB2 translation available, but statement has been taken into account. Msg number 59: Input ignored, not translated.

Translator Information messages

In the Translator Error category the following messages occurred:

Message number 0 Message number 108

When examining messages on the refine tab it is possible to switch between the four sub-tabs (Oracle, DB2, Messages, and Report) to view varying types of information related to the messages. For example, if a message is selected on MESSAGES, switching to REPORT will show the other messages around the selected message. Likewise, switching from REPORT to MESSAGE will allow you to see the other instances of a message number. In addition, switching to ORACLE will allow you to see, and edit, the source code of the object generating the error message, and switching to DB2 will allow you to see the converted DB2 source for that same object.

4.5.4 Refining the metadata conversion

In addition to viewing the results of the conversion, the Refine step gives you the opportunity to make changes in the source code. It is possible, for example, to make changes to table or column names, stored procedure or function names or source code, or trigger source code. To apply any changes made during the refine process, however, you must return to the Convert step to apply these changes. After reconverting, the converter merges the refinement changes with the original extracted source to produce updated target DB2 code. The original source code is not changed. This convert-refine process may be repeated until you are satisfied with the results.

If issues still remain after the refine-convert process, consider the following: First, see if any further changes can be made to the source metadata. If this approach is no longer feasible, you can change the DB2 metadata.

Before making any DB2 changes, prepare a backup copy of the .DB2 file you intend to change, and rename the backup. It is recommended that you make your changes to DB2 after leaving the Refine step. Do not return to the Convert step after making any manual DB2 SQL changes. Conversion of the source metadata replaces the existing DB2 file, destroying any manual changes.

Once you have the DB2 source tuned to your satisfaction, you can either go to the Generate Data Transfer Scripts page to prepare the scripts for data transfer or go directly to the Deploy to DB2 page to deploy the DB2 metadata. Please note that If you do go directly from Refine to Deploy by skipping the Generate Data Transfer Scripts, then moving data will not be an option for you, neither online nor off-line.

Making changes to the DB2 source

In our example, we made a change to the definition of the Employees table by altering the DB2 target code. This alteration involves changing the EMP_ID column to an IDENTITY column. Here is our reasoning:

Identity columns

Here is some information on IDENTITY columns from the DB2 UDB Application Development Guide:

Identity column considerations

Since the Employees table will be loaded with data that includes EMP_ID values that were already generated by an Oracle sequence, we need to take care to:

Identity Column syntax

Here is a brief explanation of the syntax for creating an IDENTITY column. Syntax:

GENERATED BY DEFAULT START WITH numeric-constant, INCREMENT BY numeric-constant

Where:

Here is the Oracle definition of the EMP_ID column in the Employees table:

CREATE TABLE EMPLOYEES (EMP_ID INTEGER NOT NULL, …

Here is the column definition we will create for the DB2 target:

CREATE TABLE EMPLOYEES (EMP_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH ???, INCREMENT BY ???), …

To complete the column definition we need only:

Here is the completed definition for the EMP_ID column in the Employees table:

CREATE TABLE EMPLOYEES (EMP_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10011, INCREMENT BY 1), …

Editing the DB2 target code

To edit the DB2 code, we returned to the Convert tab; select tabs_views_seqs.db2 in the right-hand pane and then click View Output File button. When the Text Editor opens (Figure 4-21), enter the changes and then select File -> Save to save the changes. This edited definition will now be used when the table is created in DB2 during the Deployment phase of the conversion.

Figure 4-21: Editing the EMP_ID column in the Text Editor

 < Day Day Up > 

Related

Категории