4.11 Manual conversion for ORA_EMP database objects

Previous page
Table of content
Next page

  < Day Day Up > 


4.11 Manual conversion for ORA_EMP database objects

To this point, the conversion of all objects that are capable of being successfully converted and deployed automatically into DB2 by the IBM DB2 Migration Toolkit (MTK) is done. Indeed, the majority of the object and data conversions have been accomplished. Yet, there are still some "loose-ends" such as objects that we noted for manual conversion that must be dealt with.

The triggers and procedures in our example OR_EMP database, which will be converted manually are:

  • Triggers:

    • InsertEmployees

    • ManagersChange

    • UpdateDepartments

  • Procedures:

    • SelectRow

    • EmployeeDynamicQuery

These triggers and procedures use Oracle features, which either implemented differently in DB2 UDB, or are not supported by DB2 UDB, and can be converted by using other DB2 UDB features, or changing the logic. In this section, we show you the feature differences, how to convert these triggers and procedures from Oracle to DB2UDB, and how to deploy these objects to DB2 UDB.

4.11.1 Triggers

Both Oracle and DB2 UDB have triggers features. There are some differences in the implementation of triggers in these two database. Therefore, the manual conversion is required. In this section, we discuss the triggers conversion and deployment process using three examples:

  • InsertEmployees

  • ManagersChange

  • UpdateDepartments

Example 1: InsertEmployee

The conversion of the InsertEmployee trigger requires that the BEFORE trigger be converted to an AFTER trigger. This is necessary since DB2 does not permit any insert, update, or delete activity in a BEFORE trigger.

Oracle source

Example 4-7shows the Oracle source code of InsertEmployee. This trigger uses BEFORE INSERT which needs conversion.

Example 4-7: Trigger InsertEmployee Oracle source code

start example 
 CREATE TRIGGER InsertEmployee   BEFORE INSERT ON employees   FOR EACH ROW DECLARE   v_num_projects accounts.num_projects%TYPE; BEGIN   SELECT num_projects     INTO v_num_projects     FROM accounts     WHERE dept_code = :new.dept_code     AND acct_id = :new.acct_id;   UPDATE accounts     SET current_employees = current_employees + 1     WHERE dept_code = :new.dept_code     AND acct_id = :new.acct_id; END InsertEmployees;
end example

DB2 conversion

Example 4-8 shows the converted DB2 code of trigger InsertEmployee.

Example 4-8: Trigger InsertEmployee DB2 conversion code

start example 
 CREATE TRIGGER InsertEmployee AFTER INSERT ON employees REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL BEGIN ATOMIC     DECLARE v_num_projects SMALLINT;     SET (v_num_projects) = (SELECT "NUM_PROJECTS"           FROM ACCOUNTS           WHERE "DEPT_CODE" = NEW."DEPT_CODE"                 AND "ACCT_ID" = NEW."ACCT_ID");    UPDATE ACCOUNTS           SET "CURRENT_EMPLOYEES" = "CURRENT_EMPLOYEES" + 1           WHERE "DEPT_CODE" = NEW."DEPT_CODE"                 AND "ACCT_ID" = NEW."ACCT_ID"; END
end example

Please note that in some cases you cannot change a BEFORE trigger to an AFTER trigger. For example, if an application insert a row in table A which needs a parent row in another table B and the parent row in B has to be created/generated by the trigger, then a BEFORE trigger is needed. In this case you will need to implement through application logic.

Example 2: ManagersChange

MTK created three DB2 UDB triggers for the Oracle ManagersChange trigger. This was necessary because DB2 requires a separate trigger for each trigger activity: Insert, Update, or Delete. MTK named the triggers ManagersChange_FO1 (INSERT); ManagersChange_FO2 (DELETE); and ManagersChange_FO3 (UPDATE).

There were two alterations made on each of the three converted triggers: The first made the BEFORE triggers into AFTER triggers; the second changed the logic surrounding the update of the v_ChangeType variable. The second change was optional, but made for "cleaner" code.

Oracle source

Example 4-9 is the Oracle source code of trigger ManagersChange.

Example 4-9: Trigger ManagersChange Oracle source

start example 
 CREATE TRIGGER ManagersChange   BEFORE INSERT OR DELETE OR UPDATE ON Employees   FOR EACH ROW DECLARE   v_ChangeType CHAR(1); BEGIN   /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */   IF INSERTING THEN     v_ChangeType := 'I';   ELSIF UPDATING THEN     v_ChangeType := 'U';   ELSE     v_ChangeType := 'D';   END IF;   INSERT INTO manager_audit     (change_type, changed_by, timestamp,      old_employee_id, old_dept_code, old_acct_id, old_band,      new_employee_id, new_dept_code, new_acct_id, new_band)   VALUES     (v_ChangeType, USER, SYSDATE,      :old.emp_mgr_id, :old.dept_code, :old.acct_id, :old.band,      :new.emp_mgr_id, :new.dept_code, :new.acct_id, :new.band); END ManagersChange;
end example

DB2 conversion

The two alternatives of DB2 conversion:

  • First Change:

    BEFORE trigger changed to AFTER trigger; see Example 4-10.

    Example 4-10: Trigger ManagersChange DB2 code

    start example 
     CREATE TRIGGER ManagersChange_FO1   AFTER INSERT ON EMPLOYEES   REFERENCING NEW AS NEW   FOR EACH ROW MODE DB2SQL   BEGIN ATOMIC     DECLARE v_ChangeType CHAR(1);     DECLARE "EMP_MGR_ID" INTEGER DEFAULT NULL;     DECLARE "DEPT_CODE" CHAR(3) DEFAULT NULL;     DECLARE "ACCT_ID" SMALLINT DEFAULT NULL;     DECLARE "BAND" CHAR(1) DEFAULT NULL;     DECLARE INSERTING INTEGER DEFAULT 1;     DECLARE UPDATING INTEGER DEFAULT 0;     -- /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */     IF INSERTING = 1 THEN        SET v_ChangeType = 'I';     ELSEIF UPDATING = 1 THEN        SET v_ChangeType = 'U';     ELSE        SET v_ChangeType = 'D';     END IF;     INSERT INTO MANAGER_AUDIT                ("CHANGE_TYPE","CHANGED_BY","TIMESTAMP",                 "OLD_EMPLOYEE_ID","OLD_DEPT_CODE","OLD_ACCT_ID",                 "OLD_BAND","NEW_EMPLOYEE_ID","NEW_DEPT_CODE",                 "NEW_ACCT_ID","NEW_BAND")     VALUES (v_ChangeType, USER, CURRENT TIMESTAMP,             "EMP_MGR_ID","DEPT_CODE","ACCT_ID","BAND",             NEW."EMP_MGR_ID", NEW."DEPT_CODE",             NEW."ACCT_ID", NEW."BAND");   END
    end example

  • Second change

    The second set of changes focuses on the logic of the following code:

        IF INSERTING = 1 THEN            SET v_ChangeType = 'I';        ELSEIF UPDATING = 1 THEN            SET v_ChangeType = 'U';        ELSE            SET v_ChangeType = 'D';    END IF;

    Since there will be individual triggers for INSERT, UPDATE, and DELETE, we need only set the variable v_ChangeType to whatever is appropriate for each particular trigger. For example, in an INSERT trigger we need only set v_ChangeType = 'I' (Example 4-11). For the remaining triggers we set v_ChangeType = 'U' for UPDATE, and v_ChangeType = 'D' for DELETE.

    Example 4-11: Alternative DB2 conversion of trigger ManagersChange

    start example 
     CREATE TRIGGER ManagersChange_FO1   AFTER INSERT ON EMPLOYEES   REFERENCING NEW AS NEW   FOR EACH ROW MODE DB2SQL   BEGIN ATOMIC     DECLARE v_ChangeType CHAR(1);     DECLARE "EMP_MGR_ID" INTEGER DEFAULT NULL;     DECLARE "DEPT_CODE" CHAR(3) DEFAULT NULL;     DECLARE "ACCT_ID" SMALLINT DEFAULT NULL;     DECLARE "BAND" CHAR(1) DEFAULT NULL;     SET v_ChangeType = 'I';     --** Insert trigger; set v_ChangeType = I'     INSERT INTO MANAGER_AUDIT                ("CHANGE_TYPE","CHANGED_BY","TIMESTAMP","OLD_EMPLOYEE_ID",                 "OLD_DEPT_CODE","OLD_ACCT_ID","OLD_BAND","NEW_EMPLOYEE_ID",                 "NEW_DEPT_CODE","NEW_ACCT_ID","NEW_BAND")     VALUES                (v_ChangeType,USER , CURRENT TIMESTAMP, "EMP_MGR_ID",                 "DEPT_CODE","ACCT_ID","BAND",NEW."EMP_MGR_ID",                 NEW."DEPT_CODE",NEW."ACCT_ID",NEW."BAND");   END
    end example

    The changes for the above code were also incorporated into the ManagersChange_FO2 (DELETE) trigger:

        SET v_ChangeType = 'D'

    and the ManagersChange_FO3 (UPDATE) trigger:

        SET v_ChangeType = 'U'

Example 3: UpdateDepartments

This trigger employs a CURSOR declaration, which is not allowed in DB2 triggers. This example demonstrates how to change the explicit cursor (c_projects) to an implicit cursor using a DB2 FOR loop. This is accomplished by putting the cursor's SELECT statement directly in the FOR loop statement.

Oracle source

Example 4-12 shows the Oracle source code of trigger UpdateDepartments.

Example 4-12: Trigger UpdateDepartments Oracle source code

start example 
 CREATE TRIGGER UpdateDepartments AFTER INSERT OR DELETE OR UPDATE ON employees DECLARE   CURSOR c_projects IS     SELECT dept_code            ,COUNT(*) AS total_employees            ,SUM(current_projects) AS total_projects       FROM employees       GROUP BY dept_code; BEGIN   FOR v_project_rec in c_projects LOOP      UPDATE departments      SET  total_projects  = v_project_rec.total_projects          ,total_employees = v_project_rec.total_employees      WHERE dept_code = v_project_rec.dept_code;   END LOOP; END UpdateDepartments;
end example

DB2 conversion

The DB2 conversion of trigger UpdateDepartments is shown in Example 4-13.

Listing 4-13: DB2 conversion of trigger UpdateDepartments

start example 
 CREATE TRIGGER UpdateDepartments1 AFTER INSERT ON EMPLOYEES REFERENCING NEW_TABLE AS new FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC   X:                                                                 -- [1]   for ROW as                                                         -- [2]       SELECT dept_code                                               -- [3]            ,COUNT(*) AS total_employees            ,SUM(current_projects) AS total_projects       FROM employees group by dept_code   DO       UPDATE departments                                             -- [4]       SET    total_projects  = row.total_projects             ,total_employees = row.total_employees       WHERE dept_code = row.dept_code;   END FOR X; END
end example

Notes®

[1] 

"X" is the specified label for the FOR statement.
[2] 

The for-loop-name (ROW) is used to qualify the column names returned by the specified select-statement.
[3] 

In a trigger, function, method, or dynamic compound statement, the SELECT statement must consist of only a FULL SELECT with optional common table expressions.
[4] 

This section specifies a statement (or statements) that will be invoked for each row of the table.

Note 

For complete information regarding FOR loop syntax and usage consult DB2 UDB V8 manual SQL Reference Volume, SC09-4845

Manual deployment of triggers

The triggers InsertEmployee, ManagersChange, and UpdateDepartments are deployed into DB2 UDB from a command window. Here is the process:

  1. After creating the trigger, save the script file on your file system.

    In order to execute multiple commands, your script file must end with a terminating character other than the Default semi-colon (;). Some typical termination character choices are !, or @. MTK uses !.

    • Open a DB2 Command Window in the directory where the converted procedure source resides:

      • To open the DB2 Command Window on Windows:

        Click Start and select Programs --> IBM DB2 --> Command Window

      • To open the DB2 Command Window on UNIX:

        Open any operating system command window.

    • Connect to the database:

          db2 connect to your_database_name

    • Execute the following from the command window. The termination character must be specified:

          db2 -td! -vf your_script_file_name

    • We recommend to pipe the output to another file so that any messages generated during the creation may be viewed at a later time. This may be done as follows:

          db2 -td! -vf your_script file_name > your_output_file_name

Here are the steps as they would be executed for the trigger InsertEmployee. Although not shown here, the steps will be the same, except for the file names, for the remaining triggers ManagersChange_F01/_F02/_F03 and UpdateDepartments1/2/3.

  • The source code below is saved as InsertEmployee.db2

Example 4-14: InsertEmployee.db2

start example 
 CREATE TRIGGER InsertEmployee   AFTER INSERT ON employees   REFERENCING NEW AS new   FOR EACH ROW MODE DB2SQL   BEGIN ATOMIC     DECLARE v_num_projects SMALLINT;     SET (v_num_projects) = (SELECT "NUM_PROJECTS"                             FROM ACCOUNTS                             WHERE "DEPT_CODE" = NEW."DEPT_CODE"                             AND "ACCT_ID" = NEW."ACCT_ID");     UPDATE ACCOUNTS            SET "CURRENT_EMPLOYEES" = "CURRENT_EMPLOYEES" + 1            WHERE "DEPT_CODE" = NEW."DEPT_CODE"            AND "ACCT_ID" = NEW."ACCT_ID";   END ! --** the exclamation point (!) is the terminat
end example

  1. Open a DB2 Command Window.

  2. Connect to the database:

     db2 connect to db2_emp

  3. The following command is executed. The results are piped to the message.out file:

     db2 -td! -vf InsertEmployee.db2 > message.out

  4. DB2 responds with the message:

     DB20000I The SQL command completed successfully.

The message.out file should be viewed for messages, especially if any other message than The SQL command completed successfully is returned.

4.11.2 Procedures

In our example Oracle database we have five procedures. MTK has converted three of them automatically and flagged errors in procedure EmployeeDynmaicQuery and SelectRow. After examining the reports, we know that these two procedures use Oracle features that require manual intervention to convert to the format which can be accepted DB2 UDB.

Example 1: EmployeeDynamicQuery

This example shows how to convert Oracle Dynamic SQL, implemented through the DBMS_SQL package to equivalent DB2 UDB code. Here we show first the Oracle source, and then the corresponding DB2 conversion. The conversion source will be accompanied by some explanatory notes.

Oracle source

Example 4-15 shows the Oracle source code of procedure EmployeeDynamicQuery.

Example 4-15: EmployeeDynamicQuery Oracle source code

start example 
 CREATE PROCEDURE EmployeeDynamicQuery (   p_department1 IN employees.department%TYPE DEFAULT NULL,   p_department2 IN employees.department%TYPE DEFAULT NULL) AS   v_CursorID   INTEGER;   v_SelectStmt VARCHAR2(500);   v_FirstName  employees.first_name%TYPE;   v_LastName   employees.last_name%TYPE;   v_Department employees.department%TYPE;   v_Dummy      INTEGER; BEGIN    v_CursorID := DBMS_SQL.OPEN_CURSOR;    v_SelectStmt := 'SELECT first_name, last_name, department                      FROM employees                      WHERE department IN (:d1, :d2)                      ORDER BY department, last_name';   -- Parse the query.   DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.NATIVE);   -- Bind the input variables.   DBMS_SQL.BIND_VARIABLE(v_CursorID, ':d1', p_department1);   DBMS_SQL.BIND_VARIABLE(v_CursorID, ':d2', p_department2);   DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_FirstName, 20);   DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20);   DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Department, 30);   v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);   LOOP     IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN       EXIT;     END IF;     DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_FirstName);     DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName);     DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Department);     INSERT INTO temp_table (char_col)       VALUES (v_FirstName || ' ' || v_LastName || ' is a ' ||               v_Department || ' department.');   END LOOP;   DBMS_SQL.CLOSE_CURSOR(v_CursorID);   COMMIT; EXCEPTION   WHEN OTHERS THEN       DBMS_SQL.CLOSE_CURSOR(v_CursorID);     RAISE; END EmployeeDynamicQuery;
end example

DB2 conversion

Example 4-16 shows the procedure EmployeeDynamicQuery converted into DB2. We number some statements and provide the explanation found in Example 4-16.

Listing 4-16: Converted DB2 code of Procedure EmployeeDynamicQuery

start example 
 CREATE PROCEDURE EmployeeDynamicQuery (IN p_department1 VARCHAR(30),                        IN p_department2 VARCHAR(30) )                    --[1]   LANGUAGE SQL   BEGIN     DECLARE v_FirstName VARCHAR(20);     DECLARE v_LastName VARCHAR(20);     DECLARE v_Department VARCHAR(30);     DECLARE at_end SMALLINT DEFAULT 0;                                   --[2]     DECLARE v_SelectStmt VARCHAR(500);                                   --[3]     DECLARE v_Cursor_stmt STATEMENT;                                     --[4]     DECLARE v_Cursor cursor for v_Cursor_stmt;     DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND         --[5]     BEGIN       set at_end =1;                                                     --[6]       RESIGNAL;     END;     SET v_SelectStmt = 'SELECT first_name, last_name, department                         FROM employees                         WHERE department IN (?, ?)                         ORDER BY department, last_name';                 --[7]     PREPARE v_Cursor_stmt FROM v_SelectStmt;                             --[8]     OPEN v_Cursor USING p_department1, p_department2;                    --[9]     WHILE (at_end = 0) DO                                                --[10]       FETCH v_cursor into v_FirstName,v_lastName, v_Department;          --[11]       INSERT INTO TEMP_TABLE ("CHAR_COL")       VALUES (COALESCE(v_FirstName, '') ||' ' ||               COALESCE(v_LastName, '') || ' is in the ' ||               COALESCE(v_Department, '') || ' department.');     END WHILE;     COMMIT; END
end example

Notes

[1] 

The Oracle %TYPE variables are converted to the base data types from the corresponding DB2 tables.

Note 

When MTK encounters NUMBER as a parameter, FLOAT is automatically used (by default) for DB2. This may or may not be the desired conversion, and analysis is required. Often, the appropriate type should be INTEGER or BIGINT as they refer to some type of ID value. FLOAT is an imprecise data type and as numbers get very large or very small, they may get rounded.
[2] 

The variable at_end is declared to hold a value that will be set when the EXIT Handler is executed at [5].
[3] 

The v_SelectStmt variable as varchar(500) so that it will be large enough to hold the SQL statement for the declared cursor at [7].
[4] 

A statement object is declared to hold prepared form of v_SelectStmt at [8].
[5] 

An Exit Handler is declared. This will execute when NO DATA FOUND during the Fetch statement at [11].

[6] 

When it executes, the Exit Handler will set the value of at_end to 1. This value will be checked to determine if the WHILE loop at [10] should continue.
[7] 

Before opening the cursor at [9] the v_CursorStmt object is prepared.

Example 2: SelectRow

The conversion of SelectRow presents the issue of converting Oracle cursor variables. When the cursor variable is an OUT parameter, it can usually be converted to DB2 using a Dynamic Result Set. In general, although manual, this is a very simple conversion.

Oracle source

Example 4-17 shows the Oracle source code of procedure SelectRow.

Example 4-17: SelectRow Oracle source

start example 
 CREATE PROCEDURE SELECTROW     (pEmp_ID  IN  EMPLOYEES.EMP_ID%TYPE,         pRow    OUT REFPKG.RCT1)     IS     BEGIN         OPEN pRow FOR         SELECT FIRST_NAME, LAST_NAME, DEPARTMENT, BAND         FROM EMPLOYEES         WHERE Emp_ID = pEmp_ID;     END;
end example

DB2 conversion

Example 4-18 shows the procedure DB2 conversion of procedure SelectRow. Followed the example is the explanatory note.

Listing 4-18: SelectRow DB2 conversion

start example 
 CREATE PROCEDURE SELECTROW (IN pEmp_ID INTEGER)   LANGUAGE SQL   DYNAMIC RESULT SETS 1                                          -- [1]   BEGIN     DECLARE v_Cursor cursor WITH RETURN                          -- [2]       TO CALLER for                                              -- [3]       SELECT first_anme,              last_name,              department,              band       FROM employees       WHERE emp_id = pEmpID;     OPEN v_Cursor ;                                              --[4]   END
end example

Notes

[1] 

DYNAMIC RESULT SETS: This clause specifies the maximum number of result to be returned.
[2] 

WITH RETURN: This clause indicates that the cursor is intended for use as a result set from a stored procedure.
[3] 

TO CALLER: Specifies that the cursor can return a result set to the caller. For example, if the caller is another stored procedure, the result set is returned to that stored procedure. If the caller is a client application, the result set is returned to the client application.
[4] 

The cursor v_Cursor is opened, and stays open, to return the Result set.

4.11.3 Manual deployment of stored procedures

The stored procedures EmployeeDynamicQuery and SelectRow will be deployed into DB2 UDB from a Command Window. Here is the process:

  • After creating the procedure, save the script file on your file system:

    In order to execute multiple commands, as in a stored procedure, your script file must end with a terminating character other than the default semi-colon (;). Some typical termination character choices are !, or @.

  • Open a DB2 Command Window in the directory where the converted procedure source resides:

    • To open the DB2 Command Window on Windows:

      Click Start and select Programs -> IBM DB2 -> Command Window

    • To open the DB2 Command Window on UNIX, open any operating system command window.

  • Connect to the database using command:

     db2 connect to your_database_name

  • Execute the following from the command window (termination character must be specified):

     db2 -td! -vf your_script_file_name

  • We recommend to pipe the output to another file so that any messages generated during the creation may be viewed at a later time. This may be done as follows:

     db2 -td! -vf your_script file_name > your_output_file_name

    Here are the steps implemented for the procedure SelectRow. Although not shown here, the steps will be the same except for the file names for the procedure EmployeeDynamicQuery:

  • The following script is created and saved as SelectRow.db2:

        CREATE PROCEDURE SELECTROW (IN pEmp_ID INTEGER)      LANGUAGE SQL      DYNAMIC RESULT SETS 1      BEGIN        DECLARE v_sqlStmt VARCHAR (200);        DECLARE v_stmt STATEMENT;        DECLARE v_Cursor CURSOR WITH RETURN for v_stmt;        SET v_sqlStmt = 'SELECT FIRST_NAME, LAST_NAME, DEPARTMENT, BAND                         FROM EMPLOYEES                         WHERE Emp_ID = ?';        PREPARE v_stmt FROM v_sqlStmt;        OPEN v_Cursor USING pEmp_ID;      END !   ** the exclamation point (!) is the termination character.

  • Open a DB2 Command Window.

  • Connect to the database:

     db2 connect to db2_emp

    Execute the following command to pipe the output to the message.out file:

     db2 -td! -vf Selectrow.db2 > message.out

  • DB2 responds with the message:

     DB20000I The SQL command completed successfully.

    The message.out file should be viewed for messages, especially if any other message other than The SQL command completed successfully is returned.

To this point, all the objects in our example Oracle database ORA_EMP have been converted successfully into DB2 UDB.


  < Day Day Up > 


Previous page
Table of content
Next page
Oracle to DB2 UDB Conversion Guide2003
Oracle to DB2 UDB Conversion Guide2003
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 132
BUY ON AMAZON
Adobe After Effects 7.0 Studio Techniques
Snort Cookbook
FileMaker Pro 8: The Missing Manual
Service-Oriented Architecture (SOA): Concepts, Technology, and Design
Microsoft VBScript Professional Projects
Comparing, Designing, and Deploying VPNs
flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net
Privacy policy