I am migrating our application from oracle8i to db2 v9.1, during this process all our udf oracle functions are now chaged as procedures in db2.

As oracle allows function calls in dml statements as following:

INSERT INTO TestTable(RECORDID, FIELDID, UnitsID) VALUES ( 307, 12,Masters.getUnitsID('Nos'));

here Masters.getUnitsID('Nos') is a funtion in Masters package which returns integer id.

During migration 'Masters.getUnitsID('Nos'))' is changed as procedure with name getUnitsID('Nos', ret_val) in Masters schema.

now i want to convert above oracle insert statement to db2.

Please suggest the possible ways those can be used to get that functionality.

Thanks in advace

You can either use a tool that properly converts functions to functions, rewrite the functions on DB2 (which is ultimatively the best solution in most cases), or you wrap a function around the generated procedure.

Why were those functions changed to procedures? Which tool did you use for that?

I'm asking because the solution is to use UDFs (user-defined functions) in DB2 as well. For example, create a function "getUnitsID" in schema MASTERS and you will not have to change anything in your INSERT statement.