4.5 The Refine task

---

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

• Oracle

  This tab displays all of the objects from Oracle source script

• DB2

  This tab displays all of the corresponding objects that have converted in DB2 UDB.

• Report

  The Report view displays the message, sorted by database object. When you click the Report tab, the right panel displays the messages, grouped by message number, in decreasing order of importance. You can expand the source file in a tree view to display objects that contain messages. Often the same message will occur in many places. You can filter the messages that appear in the tree by clicking Hide message in the tree for each message you do not want to see. Click the button again to have the message reappear.

• Messages

  The Messages view displays the messages, sorted by message category and number. When you click the Messages tab, the right panel displays the messages, grouped by message number in decreasing order of importance. You can expand a message category in a tree view to display the list of messages that occurred in the file.

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

• Translator Information

• Translator Warning

• Translator Error

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:

• View translator messages by message number

• View the corresponding location in the Oracle source code to which a message pertains

• View the corresponding location in the DB2 converted code to which a message pertains

• Obtain additional information regarding a particular message

• Search the source/converted code for a word or phrase

• Go to a specified line number in the source/converted code

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:

• Translator error

• Translator warning

• Translator information

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

• Translator Warning

• Translator Information

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. 

• Message number 20

  There are four occurrences of message 20 in this category. The message descriptions state:

   Object name has been changed to M_DEPT_CODE_ACCT_1 Object name has been changed to EMP_RESUME_UK11051 Object name has been changed to EMP_PHOTO_PK110581 Object name has been changed to ACCOUNTS_DEPT_COD1 

  Message Help indicates:

   Object names that are too long for DB2 are truncated. Names that are DB2 reserved words are enclosed in double quotes. Names that conflict with other names in DB2 (because the name is already in use) are renamed. 

  After examining the source code, we find that the names for the corresponding objects in Oracle, all of which are constraints, were:

   M_DEPT_CODE_ACCT_ID EMP_RESUME_UK11058551798461 EMP_PHOTO_PK11058611148823 ACCOUNTS_DEPT_CODE_ACCT_ID 

  For these objects, the converted truncated the constraint names to conform to the DB2 limit of 18 characters for constraints.

• Message number 34

  There is one occurrence of message 34 in this category. The message description states:

   No DB2 translation available, but statement has been taken into account 

  Message Help indicates:

   There is no DB2 translation available, but the information in the statement will be used by the converter in translating the statements that follow. 

  The Oracle source indicates that the message refers to the connection statement:

   CONNECT ORA_USR; 

  The conversion indicates that although the connection statement is not expressly converted, the implications of the connection statement will be handled by DB2.

• Message number 59

  There four occurrences of message 34 in this category. The message description states:

   Msg 59: Input ignored - not translated 

  Message Help indicates:

   This input is ignored. It is not supported in DB2. This omission should not cause the DB2 code to produce different results from the corresponding Oracle code. 

  The Oracle source indicates that the following statements contain references for creation in particular tablespaces:

   ALTER TABLE ACCOUNTS ADD CONSTRAINT CREATE INDEX IND_DEPT_NAME ALTER TABLE EMPLOYEES ADD CONSTRAINT CREATE INDEX IND_OFFICE_BLD 

  Specifying tablespaces for index and constraint creation, outside of the create table statement is not allowed in DB2. As indicated by the converter message however:

   This omission should not cause the DB2 code to produce different results from the corresponding Oracle code 

Translator Information messages

In the Translator Error category the following messages occurred:

    Message number 0    Message number 108 

• Message number 0:

  There is one occurrence of this message in this category. The message description states:

   MTK Oracle Converter. Version: <mtk version> 

  Message Help states:

   Specifies the version of the Oracle converter. 

  This is a General Message displaying the MTK Oracle converter version.

• Message number 108:

  There is one occurrence of this message in this category. The message description states:

   Translation Ratio: <percentage>% (<absolute ratio> statements were translated successfully) 

  Message Help indicates:

   This provides an assessment of the provided translation by giving the ratio of Oracle statements translated without producing any error message out of the total number of statements. This number is provides a general indication regarding the success of the automated translation and does not intend to give an exact and accurate measure. Statement here designates Oracle SQL and PL/SQL statements. For instance, in a CREATE PROCEDURE, the whole SQL statement is counted as 1 and each PL/SQL statement inside the body of the procedure are also counted as one. 

  This is a General Message displaying a ratio of Oracle statements translated without producing an error message. In this case it is 100%.

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:

• In the Oracle source the EMP_ID column is defined as INTEGER NOT NULL; the values for this column are automatically generated by a sequence (Employee_Sequence) that is activated by a trigger CreateEmployeeID. We intend to replace this functionality by creating the EMP_ID column as an IDENTITY column; this will allow the table to automatically generate values for the EMP_ID without the need for a sequence or a trigger.

Identity columns

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

• Rather than using cumbersome insert and update triggers, DB2 enables you to include generated columns in your tables using the GENERATED ALWAYS AS clause. Generated columns provide automatically updated values derived from an SQL expression.

• DB2 application developers often need to create a primary key for every row in a table. If you create a table that uses an identity column for the primary key, DB2 automatically inserts a unique value. When you use identity columns, your applications can benefit from increased performance due to a reduction in lock contention.

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:

• Preserve the original values of EMP_ID currently in the Employees table.

• Preserve the increment of the original sequence.

• Generate new values that will not conflict with the current values in the column.

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:

• GENERATED BY DEFAULT

  Indicates that DB2 will generate a value for the column when a row is inserted into the table, or updated, specifying DEFAULT for the column, unless an explicit value is specified. BY DEFAULT is the recommended value when using data propagation or doing unload or reload.

• START WITH numeric-constant

  Specifies the first value for the identity column.

• INCREMENT BY numeric-constant

  Specifies the interval between consecutive values of the identity column.

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:

• Supply a starting value that will be greater than the current maximum EMP_ID value that is currently in the table. The best/easiest way of doing this is to:

  • Execute Select MAX(EMP_ID) from the Employees table;

  • Retrieve the result and set the START WITH VALUE equal to that value plus one. In our example the result of that process is 10011.

• Duplicate the increment value (increment by 1) from the Oracle Employee_Sequence.

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

---