Results 1 to 2 of 2
  1. #1
    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

  2. #2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts