1 Reply Latest reply on Jul 18, 2016 10:48 PM by rp0428

    Error in SQL Developer Scratch Editor (DB2 SQL/PL to PL/SQL)

    user11990154

      I am looking for a tool/utility that can be used by developers to convert the DB2 Code to Oracle, as we're going through a migration effort. I thought I was lucky to see SQL Developer had a utility just for that (Scratch Editor), but it doesn't seem to convert a simple DB2 statement:

       

      select tabschema, tabname, colname, typename, length

      from syscat.columns

       

      I get an error -

      /**SQLDEV:FAILED TO TRANSLATE

      select tabschema, tabname, colname, typename, length

      **/

      /**SQLDEV:FAILED TO TRANSLATE

      from syscat.columns

      **/

       

      Is anyone aware of a tool/utility that converts DB2 SQL syntax to corresponding Oracle syntax? Does SQL Developer support this functionality?

       

      Thanks

      Raj

        • 1. Re: Error in SQL Developer Scratch Editor (DB2 SQL/PL to PL/SQL)

          Is anyone aware of a tool/utility that converts DB2 SQL syntax to corresponding Oracle syntax?

          No - in general any such tool would produce poor statements and code anyway due to the significant differences in both the functionality that is available and the way it is implemented.

          Does SQL Developer support this functionality?

          Only to a VERY limited degree

           

          I am looking for a tool/utility that can be used by developers to convert the DB2 Code to Oracle, as we're going through a migration effort. I thought I was lucky to see SQL Developer had a utility just for that (Scratch Editor), but it doesn't seem to convert a simple DB2 statement:

          For project purposes you should definitely distinguish between 'code' and 'sql'. Not only are they two different things but the implementation of 'code' (i.e. PL/SQL) is significantly different.

           

          This is a good doc to start with: Sql Developer Supplementary Implementation for IBM DB2 Migrations

          https://docs.oracle.com/cd/E39885_01/doc.40/e18460/toc.htm

           

          Pay particular attention to the datatype differences and conversion issues particularly if you use international character sets. Choosing the CORRECT database and national character sets will be critical since, except with limited exceptions, you can NOT change them later.

           

          There is a fundamental, and non-migrational, difference in the way Oracle and DB2 handle locking and transactions.

           

          In Oracle writers do NOT block readers and readers do NOT block writers. In DB2 I can lock a single row and prevent ALL other users from performing ANY operation that has to just read that row. That doesn't happen in Oracle.

           

          So most of any DB2 code that is written to work around those locking issues usually needs to be removed or significantly re-architected.

           

          Oracle implementations typically do make much, if any, use of temp tables, unlike DB2, SQL Server and other DBs which can depend on them.

           

          Unless you have an Oracle expert on-board your org I highly recommend you hire one to assist with putting your project migration plan together to make sure that ALL POSSIBLE considerations are documented properly.

           

          The first critical step, of course, is to make sure you can migrate the data itself without loss of data or precision.

           

          Another critical issue is the creation of accurate code and processes to perform data comparisons between the old and new systems.

           

          Good luck with your project!