| < Day Day Up > |
|
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.
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
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.
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
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;
Example 4-8 shows the converted DB2 code of trigger InsertEmployee.
Example 4-8: Trigger InsertEmployee DB2 conversion code
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
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.
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.
Example 4-9 is the Oracle source code of trigger ManagersChange.
Example 4-9: Trigger ManagersChange Oracle source
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;
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
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
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
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
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'
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.
Example 4-12 shows the Oracle source code of trigger UpdateDepartments.
Example 4-12: Trigger UpdateDepartments Oracle source code
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;
The DB2 conversion of trigger UpdateDepartments is shown in Example 4-13.
Listing 4-13: DB2 conversion of trigger UpdateDepartments
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
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.
|
The triggers InsertEmployee, ManagersChange, and UpdateDepartments are deployed into DB2 UDB from a command window. Here is the process:
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
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
Open a DB2 Command Window.
Connect to the database:
db2 connect to db2_emp
The following command is executed. The results are piped to the message.out file:
db2 -td! -vf InsertEmployee.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 than The SQL command completed successfully is returned.
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.
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.
Example 4-15 shows the Oracle source code of procedure EmployeeDynamicQuery.
Example 4-15: EmployeeDynamicQuery Oracle source code
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;
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
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
Notes
[1] | The Oracle %TYPE variables are converted to the base data types from the corresponding DB2 tables.
| |||
[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. |
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.
Example 4-17 shows the Oracle source code of procedure SelectRow.
Example 4-17: SelectRow Oracle source
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;
Example 4-18 shows the procedure DB2 conversion of procedure SelectRow. Followed the example is the explanatory note.
Listing 4-18: SelectRow DB2 conversion
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
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. |
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 > |
|