Oracle to DB2 UDB Conversion Guide2003

4.9 Converting the remaining objects

To convert the remaining objects we return to the Convert tab. Since the converter needs the definitions for the converted tables, sequences, and views to correctly convert the remaining objects, we use them as context for the next part of the conversion. To specify objects as Context files, we do the following:

• Click Set Context on the Convert tab.

• The Set Context screen will open displaying two panels. In the left panel it reads Source Files. Under Source Files all of the .src files that have been extracted from the source will be shown. In the right pane it reads Selected context files. When first opened, there will be no files indicated under this heading.

• To select files as context files, choose one (or several) in the right hand panel and then select >. This will bring the file into the Selected context files panel. Figure 4-28 shows the screen for our conversion after having selected tabs_views_seqs.src as a context file.

  Figure 4-28: The Set Context screen after a file has been selected as Context.

Once the context selections have been made, clicking the OK button returns us to the Convert tab.

On the convert tab, we can now see that the tabs_views_seqs.src file has the indication (context) beside it. This specifies that the file is now a context file and will not be reconverted.

To begin conversion, complete the required steps:

1. Select the files in left-hand panel that will be converted. We choose the procs_pkgs_trgs.src file.

2. Enter a prefix for the generated files (i.e., enter a name for the file that will be generated from the conversion). We accept the default.

3. Click the Convert button to begin the conversion.

Figure 4-29 shows the Convert Tab, before conversion, with all required steps completed.

Figure 4-29: The Convert tab - context file indicated and the remaining file selected

4.9.1 Translator messages

In this section, we discuss the messages for the conversion of stored, procedures, functions, packages, and triggers. Although messages from the previous conversion file will still be available for viewing, our discussion will not include such messages. During examining the messages, if we find items which need to be convert manually, we will "mark" it as manual conversion items. These items will then be discuss further in this chapter.

Figure 4-30 shows the Refine tab after the conversion of the procs_pkgs_trigs.src file. We received messages in the following categories from our conversion:

• Input Script error

• Translator Error

• Translator Warning

• Translator Information

Figure 4-30: The Refine tab after the conversion of procs_pkgs_trigs.src

Input Script Error messages

The following message occurred in the Input Script Error category:

Message 11: Reference to unknown cursor: pRow

This message occurs once, and it refers to the stored procedure Selectrow. Message Help indicates:

The translator is not aware of the definition of this object. Ensure the definition exists in the data source file. Also, ensure the schema name is specified as indicated.

When the statement is investigated, we recognize that pRow is an Oracle Reference Cursor. The conversion of Reference Cursors to the corresponding DB2 UDB functionality will have to be converted manually. We mark this in project plan for manual conversion and move on.

Translator Error messages

We have three Translator Error messages:

• Message 21

  Fourteen instances of message 21 occurred in the Translator error category:

  Message 21: Call to Procedure DBMS_SQL.PARSE is not supported Message 21: Call to Procedure DBMS_SQL.DEFINE_COLUMN is not supported Message 21: Call to Procedure DBMS_SQL.COLUMN_VALUE is not supported Message 21: Call to Procedure DBMS_SQL.CLOSE_CURSOR is not supported Message 21: Call to Procedure DBMS_SQL.BIND_VARIABLE is not supported Message 21: Call to Procedure DBMS_SQL.OPEN_CURSOR is not supported Message 21: Call to Procedure DBMS_SQL.FETCH_ROWS is not supported Message 21: Call to Procedure DBMS_SQL.EXECUTE is not supported

  The fourteen instances of this message all refer to the same object, the stored procedure EmployeeDynamicQuery. Message Help indicates:

  This Oracle function or procedure call is not translated to DB2.

  When the procedure is analyzed, we recognize that it contains references to Dynamic SQL that is implemented through the Oracle DBMS_SQL package. Dynamic SQL can be easily converted into DB2 UDB functionality, but the conversion will have to be done manually. We mark this procedure for manual conversion and continue.

• Message 75

  Two instances of message 75 occur in the Translator error category:

  Message 75: This statement is not supported in a DB2 Before Trigger

  The first instance of this message occurs in relation to the trigger InsertEmployee, the second for the trigger ManagersChange. Message Help indicates:

  This statement is not supported in a DB2 Before Trigger. The following statements are not supported in this context: INSERT, DELETE and UPDATE.

  After examining the triggers, we can see that they include DML statements. DML statements are restricted in before triggers in DB2 UDB. The conversion of this is also not complicated, but will also have to be done manually.

• Message 80

  This message occurs once in the Translator error category:

  Message 80: This package item is not translated.

  This message occurs in relation to the Package object REFPKG. Message Help indicates:

  Only the following items are supported inside a package: function specifications, functions, procedure specifications, procedures, and constants. Variable declarations, cursor declarations and type definitions are not translated.

  After examining the source code, we understand that although this package item will not be converted, it will not be necessary in our conversion. First, because MTK converts Oracle schema in the recommended manner, i.e., by converting the Oracle Package name to a DB2 Schema name. Second, because the package item, a reference cursor will be managed when the procedure SelectRow is converted manually.

Translator Warning Messages

In the following are Translator Warning messages we received:

• Message 20

  15 instances of Message 20 occurred in the Translator Warning category:

  Message 20: Object name has been changed to <new name>.

  This message pertains to the following objects (Table 4-5).

  Table 4-5: Message 20 objects

  Source object name	Conversion name	Object type
  ACCT_ID	ACCT_ID1	Correlation name
  BAND	BAND	Correlation name
  c_RegisteredEmployees	c_RegisteredEmplo1	Cursor name
  DEPT_CODE	DEPT_CODE1	Correlation name
  EMP_MGR_ID	EMP_MGR_ID1	Correlation name
  EmployeesOfficesInsert	EmployeesOfficesI1	Trigger (insert)
  ManagersChange	ManagersChange_FO1	Trigger (insert)
  	ManagersChange_FO2	Trigger (delete)
  	ManagersChange_FO3	Trigger (update)
  office_summary_delete	office_summary_de1	Trigger
  UpdateDepartments	UpdateDepartments1	Trigger (insert)
  	UpdateDepartments2	Trigger (delete)
  	UpdateDepartments3	Trigger (update)
  UpdateEmployees	UpdateEmployees_F1	Trigger (update)

  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.

  Upon examination of the source code, we observed that names were altered for three types of objects:

  • Triggers

    • Reason:

      Trigger names were changed in two cases. The first case was because the trigger name exceeded the maximum number of characters (18) allowed, as in the case of EmployeesOfficesInsert and office_summary_delete. In this case, the names were truncated to conform to the standard. In the second case, additional triggers needed to be created. This occurs when an Oracle trigger specifies more than one operation (INSERT, UPDATE, DELETE) for the source trigger. In this circumstance, DB2 requires and MTK creates an individual trigger for each operation.

  • Cursors

    • Reason:

      Cursor names cannot exceed 18 characters

  • Correlation names

    • Reason:

      Renaming these variables is related to a message that is best explained by the Message Help from message 71:

      DB2 does not accept references to OLD from an inserting trigger or references to NEW from a deleting trigger. In the WHEN clause of the trigger these references are translated to NULL. In the body of the trigger they are translated to a variable generated for this purpose.

• Message 34

  Four instances of message 34 occurred in the Translator warning category:

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

  The four instances occur in relation to the following statements:

  • CONNECT ORA_USR

  • In the Create Package AccountPackage statement for:

    • Create Procedure AddEmployee

    • Create Procedure RemoveEmployee

    • Create Procedure AccountList

  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.

  After examining the source we understand that:

  • Regarding CONNECT ORA_USR, the message indicates that although the connection statement is not expressly converted, the implications of the connection statement will be handled by DB2. ORA_USR will be used as the default schema for unqualified database objects names

  • Regarding the Create Procedure statements, we recognize that Oracle packages will be converted to objects within a specified schema. This schema name will be the same as the original Oracle Package name.

• Message 47

  Four instances of message 47 occurred in the translator warning category:

  Message 47: BEFORE translated to NO CASCADE BEFORE

  The four instances of this message occur for the following triggers:

  • CreateEmployeeID

  • InsertEmployee

  • ManagersChange

  • UpdateEmployees

  Message Help indicates:

  Oracle triggers using the BEFORE event type are translated in DB2 by NO CASCADE BEFORE triggers. This type of trigger does not allow other triggers to fire from the trigger body, which might lead to incorrect behavior.

• Message 50

  Two occurrences of message 50 occurred in this category:

  Message 50: This statement is not supported in a DB2 dynamic compound statement

  The two occurrences of this message are both in regard to a cursor created in the trigger UpdateDepartments. Message Help indicates:

  This statement is not supported in a DB2 dynamic compound statement. Dynamic compound statements are used as bodies for top-level anonymous blocks, user-defined functions, and triggers. Procedures use DB2 compound statements as bodies that are less restrictive. Some statements that are not allowed inside a DB2 dynamic compound statement are: - Nested blocks - Statements containing CASE expressions - GOTO - Procedure calls - Cursors - COMMIT - Exception handlers

  Since this type of cursor statement is not allowed inside of a trigger in DB2, we will need to manually convert the cursor logic. We marked the object for manual conversion and continued.

• Message 61

  Two occurrences of message 61 occurred in this category:

  Message 61: Parameter defaults are not supported in DB2 procedure definitions. Calls to the procedure are adjusted accordingly.

  The two occurrences of message 61 relate to the procedure EmployeeDynamicQuery. Message Help indicates:

  In procedure and function declarations, the optional DEFAULT value of a parameter is not translated, but the translator will use the value as necessary through the remainder of the translation.

  Additional information adds the following explanation:

  DB2 does not support default values for procedures and function parameters. Default values for parameters are not translated in the parameter list, but the converter remembers them and adds them to each procedure call when the corresponding argument is missing.

• Message 71

  There are two occurrences of message 71 in this category:

  Message 71: Reference to OLD or NEW column translated to <NULL or variable>

  The two occurrences of this message relate to the ManagersChange trigger. Message Help indicates:

  DB2 does not accept references to OLD from an inserting trigger or references to NEW from a deleting trigger. In the WHEN clause of the trigger these references are translated to NULL. In the body of the trigger they are translated to a variable generated for this purpose

  This message is related to warning message 20 that was also received in this category.

• Message 94

  There are two occurrences of message 94 in this category:

  Message 94: The function <function_name> is translated to DB2 as a Procedure.

  This message refers to the functions MaxProjects and AverageBand. Message Help indicates:

  This Oracle user-defined function is translated to DB2 as a procedure. This happens with functions with parameters in OUT mode. Since this feature is not available in DB2, the translator uses a DB2 procedure instead. The calls to the Oracle function will be translated accordingly, they will become procedure-calls.

Translator Information

The Translator Information received in our conversion example are:

• Message 0

  There is one occurrence of this message in this category:

  Message 0: MTK Oracle Converter. Version: <mtk version>

  Message Help indicates:

  Specifies the version of the Oracle converter.

  The version number of the converter is specified.

• Message 108

  There is one occurrence of this message in this category:

  Message 108: 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.

  The translation ratio is reported as 85.94% - 110 of 128 statements were translated successfully.

4.9.2 Status

Before beginning the deployment task, let us take the time to evaluate the information that was just gathered. The most important aspect at this point of the conversion is to make sure that we understand all of the messages and all of the implications of the messages from each Translator message category. This evaluation will give us a fair idea of what we can expect as our final result, such as how many objects will successfully deploy into DB2.

Given the information we have collected about the objects from our conversion, we expect that most of the objects will deploy successfully. We expect however that after the completion of deployment that the Verification Report will indicate several objects did not deploy successfully. We expect that the following objects will be in that category, and that manual intervention will be required before they can be successfully deployed:

• Stored procedures:

  • SelectRow

  • EmployeeDynamicQuery

• Triggers:

  • InsertEmployee

  • ManagersChange

  • UpdateDepartments