Introduction
Let's take a look at this simple Oracle
stored procedure.
CREATE OR REPLACE FUNCTION EMP_SEARCH(
p_empname VARCHAR2 DEFAULT NULL,
p_deptname VARCHAR2 DEFAULT NULL,
p_mgmname VARCHAR2 DEFAULT NULL)
RETURN SYS_REFCURSOR AS
v_refcursor SYS_REFCURSOR;
v_cmd VARCHAR2(32767 CHAR);
v_cur INTEGER;
v_aux NUMBER;
BEGIN
-- first part of the statement
v_cmd := 'SELECT ENAME,JOB,DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO';
-- parameters enhancement
IF (p_empname IS NOT NULL) THEN
v_cmd := v_cmd || ' AND ENAME = :p_empname';
END IF;
IF (p_deptname IS NOT NULL) THEN
v_cmd := v_cmd || ' AND DNAME = :p_deptname';
END IF;
IF (p_mgmname IS NOT NULL) THEN
v_cmd := v_cmd || ' AND MGR IN (SELECT EMPNO FROM EMP WHERE ENAME= :p_mgmname)';
END IF;
v_cmd := v_cmd || ' ORDER BY ENAME';
-- preparation
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(c => v_cur, statement => v_cmd, language_flag => dbms_sql.native);
-- parameter substitution
IF (p_empname IS NOT NULL) THEN
dbms_sql.bind_variable(c => v_cur,name => ':p_empname',value => p_empname);
END IF;
IF (p_deptname IS NOT NULL) THEN
dbms_sql.bind_variable(c => v_cur,name => ':p_deptname',value => p_deptname);
END IF;
IF (p_mgmname IS NOT NULL) THEN
dbms_sql.bind_variable(c => v_cur,name => ':p_mgmname',value => p_mgmname);
END IF;
-- final ivocation
v_aux := dbms_sql.execute(v_cur);
v_refcursor:= dbms_sql.to_refcursor(cursor_number => v_cur);
RETURN v_refcursor;
END EMP_SEARCH;
The procedure is executed against
Oracle sample data set. Depending on the parameters, it yields result set containing a list of employees working in the same department, having the same manager or the data of the single employee with the name specified. If all parameters are null the procedure returns the list of all employees.
Example
SELECT EMP_SEARCH(p_mgmname=>'JONES') FROM DUAL
is expanded as
SELECT ENAME,JOB,DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND MGR IN (SELECT EMPNO FROM EMP WHERE ENAME='JONES')
What is more, we want to launch this procedure from JDBC and Hibernate client code.
Migration to DB2
While migrating to DB2 there are several problems to overcome.
- DBMS_SQL package is implemented in DB2. It is only necessary to replace bind_variable method with appropriate bind_variable_varchar, bind_variable_number etc. because DB2 supports routine overloading only by a number of parameters, not by parameters type.
- to_refcursor method. This method casts cursor id used in DBMS_SQL package to ref_cursor. It is not supported in DB2 and there is no way to overcome this issue. We must forget about DBMS_SQL implementation in DB2.
- RETURN SYS_REFCURSOR. In DB2 SYS_REFCURSOR can be returned from a function. Nevertheless, it is not supported by DB2 JDBC client, there is no way to pick up this cursor and make usage of it as a standard ResultSet. The solution is to transform EMP_SEARCH to a procedure and return SYS_REFCURSOR in the OUT parameter.
- Hibernate. It is another killer, from bad to worse. If DB2 stored procedure is called through Hibernate we cannot intercept the result set returned as OUT parameter. The only way is to call DB2 stored procedure having DYNAMIC RESULT SETS clause. But DYNAMIC RESULT SETS is not available in Oracle syntax, PL/SQL stored procedures have this parameter set to 0 meaning that they do not return any result set.
Problem
Judging from arguments provided above we are at a dead end. The only solution is to forget about
Oracle Compatibility Mode and rewrite the procedure from scratch using native DB2 syntax. It is not a problem for a single and simple procedure as above. But what to do if the procedure is much more complicated, with more parameters and more complicated SQL statement to generate. And how to proceed if we have hundreds or thousands of procedures like that ? If we do not want to spend the rest of our life on it or do not have resources to hire the Mongolian horde of DB2 SQL developers and testers to do it we have to find another solution.
Solution highlights
- Although we cannot avoid modification of the stored procedure body, keep the main logic generating the SQL statement and passing parameters to the statement without modification. This way the risk of injecting regression errors is minimalized.
- Replace DBMS_SQL package with a similar package having the same methods and implementing the same logic.
- The stored procedure launched by JDBC or Hibernate client should be native DB2 routine having DYNAMIC RESULTS SETS clause.
Solution, DBMSJ_SQL package
The solution is available
here. The JDBC and Hibernate code for Oracle and DB2 and Junit tests are available
here.
To avoid name conflict the package name is DBMSJ_SQL. It comprises
Java code and
DB2 SQL code. Java and DB2 SQL wrapper covers part of DBMS_SQL package.
- OPEN_CURSOR New cursor, return cursor id to be reused in later calls
- PARSE Unlike original DBMS_SQL routine it simply keeps SQL statement.
- BIND_VARIABLE Binds string value with position marker in SQL statement.
- PREPARE_S_JAVA Used internally. Returns modified SQL statement ready to use by DB2 PREPARE command and list of values set in the order equivalent to the position of the variable marker in SQL statement.
- EXECUTE Prepares the statement and opens the cursor to be returned to the client application. This method should be invoked by native DB2 stored procedure.
Stored procedure after migration to DB2
The
stored procedure is split into two parts. The first one is the original Oracle SP. The procedure is almost identical to the Oracle version. The only difference is replacement DBMS_SQL with DBMSJ_SQL and removing closing DBMS_SQL.EXECUTE and DBMS_SQL.TO_REFCURSOR. The main logic is the same.
CREATE OR REPLACE FUNCTION EMP_SEARCH_INT(
p_empname VARCHAR2 DEFAULT NULL,
p_deptname VARCHAR2 DEFAULT NULL,
p_mgmname VARCHAR2 DEFAULT NULL)
RETURN INTEGER
AS
v_cmd VARCHAR2(32672);
v_cur INTEGER;
v_aux NUMBER;
BEGIN
-- first part of the statement
v_cmd := 'SELECT ENAME,JOB,DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO';
-- parameters enhancement
IF (p_empname IS NOT NULL) THEN
v_cmd := v_cmd || ' AND ENAME = :p_empname';
END IF;
IF (p_deptname IS NOT NULL) THEN
v_cmd := v_cmd || ' AND DNAME = :p_deptname';
END IF;
IF (p_mgmname IS NOT NULL) THEN
v_cmd := v_cmd || ' AND MGR IN (SELECT EMPNO FROM EMP WHERE ENAME= :p_mgmname)';
END IF;
v_cmd := v_cmd || ' ORDER BY ENAME';
-- preparation
v_cur := dbmsj_sql.open_cursor;
dbmsj_sql.parse(c => v_cur, statement => v_cmd);
-- parameter substitution
IF (p_empname IS NOT NULL) THEN
dbmsj_sql.bind_variable(c => v_cur,name => ':p_empname',value => p_empname);
END IF;
IF (p_deptname IS NOT NULL) THEN
dbmsj_sql.bind_variable(c => v_cur,name => ':p_deptname',value => p_deptname);
END IF;
IF (p_mgmname IS NOT NULL) THEN
dbmsj_sql.bind_variable(c => v_cur,name => ':p_mgmname',value => p_mgmname);
END IF;
RETURN v_cur;
END EMP_SEARCH;
@
The second is a native DB2 SQL procedure having DYNAMIC RESULT SETS clause and SQL code to prepare and open SQL cursor. This procedure is called directly by JDBC or Hibernate client. Its signature is identical to Oracle procedure except returning SYS_REFCURSOR
CREATE OR REPLACE PROCEDURE EMP_SEARCH(
p_empname VARCHAR2(32672) DEFAULT NULL,
p_deptname VARCHAR2(32672) DEFAULT NULL,
p_mgmname VARCHAR2(32672) DEFAULT NULL)
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE V_CUR INTEGER;
SET V_CUR = EMP_SEARCH_INT(p_empname,p_deptname,p_mgmname);
CALL dbmsj_sql.execute(v_cur);
END P1
@
Java JDBC client
The JDBC client code should be modified with respect to picking up the result set.
Oracle
CallableStatement stmt = connection.prepareCall("{ ? = call EMP_SEARCH ( ?, ?, ?)");
int i = 0;
stmt.registerOutParameter(++i, OracleTypes.CURSOR);
stmt.setString(++i, empName);
stmt.setString(++i, depName);
stmt.setString(++i, mgmName);
stmt.execute();
ResultSet rset = (ResultSet) stmt.getObject(1);
DB2
CallableStatement stmt = connection.prepareCall("{ call db2inst1.EMP_SEARCH ( ?, ?, ?) } ");
int i = 0;
stmt.setString(++i, empName);
stmt.setString(++i, depName);
stmt.setString(++i, mgmName);
ResultSet rset = stmt.executeQuery();
Java Hibernate client
The only difference is Hibernate mapping file.
Oracle
<![CDATA[
{ ? = call EMP_SEARCH( :p_empname, :p_deptname, :p_mgmname )}
]]>
DB2
<![CDATA[
{ call EMP_SEARCH( :p_empname, :p_deptname, :p_mgmname )}
]]>
JUnit test
A project containing Junit test is available
here. The same test suite covers all four versions: Oracle JDBC, Oracle Hibernate, DB2 JDBC and DB2 Hibernate.
Conclusion
Although modification is necessary, the majority of Oracle code can be migrated almost as-is. What is more, the main logic of the application, creating SQL SELECT statement and argument bindings, is preserved thereby lowering the risk of injecting regression errors. The wrapping DB2 stored procedure is very simple and can be created almost automatically.
The stored procedure migrated here is very simple but this pattern can be applied also to a much more complicated example.
Looking back we can identify several migration milestones:
- Enthusiastic. DBMS_SQL package, the core of the SP migrated here, is supported in DB2 Oracle Compatibility Mode, the migration seems trivial.
- Dead end. DBMS_SQL.TO_REFCURSOR is not implemented and there is no way to overcome it. The grim reality seems to lurk, rewrite everything from scratch in native DB2 SQL with gritted teeth.
- Realistic. By means of DBMSJ_SQL package modifications are necessary but seems feasible. Also, client code requires only small adjustments.
