10-02-07, 12:11 #1Registered User
- Join Date
- Oct 2007
Unanswered: help reg usage of procedure ouput in insert statement during process of oracle to db2
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
10-02-07, 14:37 #2Registered User
- Join Date
- Jan 2007
- Jena, Germany
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.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development