Migrating a stored procedure using REF CURSOR from Oracle to DB2

Question

I'm currently migrating a package of procedures from Oracle to DB2, with the assistance of the IBM Migration Toolkit. MTK unfortunately chokes on custom types, such as:

TYPE G_reference_cursor IS REF_CURSOR
...
FUNCTION do_some_stuff
  RETURN g_reference_cursor
...

What would be the idiomatic way of migrating these statements to DB2?

Answer 1

This is analogue of DB2 table functions but without specification of column list.

E.g. something like that:

create function do_some_stuff() returns table( <column_list> ) 
language sql
begin atomic 

  return 
    select t.* from some_table t;

end@

with no <column_list> specification. Such functions may return table with different set of fields depending on parameters passed into and internal logic. At most cases this type of function result used for returning results to client side.

Declaring type based on REF_CURSOR is only way to do such thing at Oracle 8, but since Oracle 9i there are built-in type sys_refcursor (e.g. see here).

Because declaration is weak-type, and DB2 (as far as I know) don't have analogue for sys_refcursor, automatic conversion can't decide how to convert such declarations.

Only way to convert it is to look into stored procedure and manually reconstruct the set of fields returned in cursor. Then you can rewrite Oracle declaration with strong-typed cursor and process it with MTK. Or manually rewrite logic on DB2 side ...

---

Update:

1. DB2 supports weak type cursors.
2. Solutions for migration compatibility issues described in IBM book, available here (WARNING: .PDF file about 3.18MB). search for "ref_cursor" string here.