converting a DB2 query into oracle query

Question

Previously we used DB2 as database, but now we are migrating to Oracle. Now, in our project we have extensively used sql's that were Db2 specific.

Is there any way to convert those DB2 specific queries to oracle supported queries.

Thanks

Answer 1

You have a lot of work ahead!

Between DB2 and Oracle, some important differences are (just an arbitrary enumeration of what I can think of):

Data types

• Number data types: DB2 has many more standard types, such as SMALLINT, INTEGER, DOUBLE, etc. Those don't exist in Oracle SQL (although some exist in PL/SQL). This is important for DDL and for casting and some other use cases, such as the correctness of predicates
• Date data types: Oracle's only difference between DATE and TIMESTAMP is the fact that TIMESTAMP has microseconds. But DATE may also contain time information. In DB2, DATE has no time information, I think.
• Character data types: Read about the difference between VARCHAR and VARCHAR2 in Oracle
• NULL. In Oracle, NULL is much more general than in DB2. Before DB2 v9.7, you had to cast NULL to any explicit type, e.g. cast(null as integer). That's not necessary in Oracle.

System objects

• SYSIBM.DUAL simply becomes DUAL
• Functions: They're all a bit different. You'll have to check case by case. For example, LOCATE becomes INSTR

Syntax

• TRUNCATE IMMEDIATE becomes TRUNCATE
• EXCEPT becomes MINUS
• DB2's FETCH FIRST n ROWS ONLY: There is no such clause in Oracle. You'll have to use ROWNUM or ROW_NUMBER() OVER() filtering (see this example)
• DB2's MERGE statement is more powerful than that of Oracle, in case you use this.
• DB2 supports INSERT INTO .. (..) VALUES (..), (..), (..). With Oracle, you'd have to write INSERT INTO .. SELECT .. UNION ALL SELECT .. UNION ALL SELECT ..

Advanced

• If you use stored procedures, they work a bit differently, especially with advanced data types involved, but that's out of scope here.

Your most efficient shot at this might be to use SQL abstraction of some sort. If you're using Java, I would recommend you wrap your SQL statements with jOOQ (Disclaimer: I work for the company behind jOOQ). jOOQ provides API-level abstraction for all of the above facts. A great deal of SQL can be executed both on DB2 and Oracle, without adaptation. We're also working on a more independent translator product: https://www.jooq.org/translate

On a higher level of abstraction, Hibernate (or other JPA implementations) can do the same for you

Answer 2

I found out that there are also some differences in the management of character strings.

• DB2 doesn't care about the trailing whitespaces when comparing:

  /* DB2 */
  SELECT CASE WHEN ('A   ' = 'A') THEN 'true' ELSE 'false' END FROM SYSIBM.SYSDUMMY1
  --> true
  
  /* Oracle */
  SELECT CASE WHEN ('A   ' = 'A') THEN 'true' ELSE 'false' END FROM DUAL
  --> false
  

• Oracle considers that '' equals NULL:

  /* DB2 */
  SELECT CASE WHEN ('' IS NULL) THEN 'true' ELSE 'false' END FROM SYSIBM.SYSDUMMY1
  --> false
  
  /* Oracle */
  SELECT CASE WHEN ('' IS NULL) THEN 'true' ELSE 'false' END FROM DUAL
  --> true