SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from IBM DB2 to Oracle.
SQLines can also assist you to convert COBOL stored procedures and programs to Oracle PL/SQL or Java.
Databases:
Converting SQL language elements from DB2 to Oracle:
DB2 | Oracle | ||
1 | string1 CONCAT string 2 CONCAT … | String concatenation operator | string1 || string 2 || … |
2 | NEXTVAL | NEXT VALUE FOR seq_name | Next value for sequence | seq_name.NEXTVAL |
3 | RESULT_SET_LOCATOR | Processing result sets in procedure | SYS_REFCURSOR |
Datetime interval expressions:
DB2 | Oracle | ||
1 | num DAY | DAYS | Interval in days | INTERVAL 'num' DAY |
var DAY | DAYS | NUMTODSINTERVAL(var, 'DAY') | ||
2 | num MINUTE | MINUTES | Interval in minutes | INTERVAL 'num' MINUTE |
var MINUTE | MINUTES | NUMTODSINTERVAL(var, 'MINUTE') |
Data type mapping between DB2 and Oracle:
Converting functions:
Converting SQL SELECT statement:
DB2 | Oracle | ||
1 | SYSIBM.SYSDUMMY1 table | A single row, single column dummy table | DUAL table |
2 | WHERE (c1, c2, …) = (v1, v2, …) | Specific AND syntax | c1 = v1 AND c2 = v2 AND … |
3 | EXCEPT | Set operator | MINUS |
4 | FETCH FIRST n ROWS ONLY | Return n rows after sorting | ROWNUM and subquery |
FETCH FIRST ROW ONLY | Return 1 row only after sorting | ||
5 | WITH UR | CS | RS | RR | Isolation level for SELECT | WITH UR and CS removed |
Converting CREATE TABLE statement from DB2 to Oracle:
Implicit DEFAULT values in DB2:
DB2 | Oracle | |
1 | column CHAR(n) WITH DEFAULT | column CHAR(n) DEFAULT '' |
2 | column VARCHAR(n) WITH DEFAULT | column VARCHAR2(n) DEFAULT '' |
3 | column INTEGER WITH DEFAULT | column NUMBER(10) DEFAULT 0 |
4 | column DECIMAL(p, s) WITH DEFAULT | column NUMBER(p, s) DEFAULT 0 |
5 | column NUMERIC(p, s) WITH DEFAULT | column NUMBER(p, s) DEFAULT 0 |
6 | column DATE WITH DEFAULT | column DATE DEFAULT SYSDATE |
7 | column TIMESTAMP WITH DEFAULT | column TIMESTAMP DEFAULT SYSTIMESTAMP |
8 | column CLOB WITH DEFAULT | column CLOB DEFAULT EMPTY_CLOB() |
Converting GLOBAL TEMPORARY TABLE clauses from DB2 to Oracle:
Converting partitioning definition from DB2 to Oracle:
DB2 | Oracle | ||
1 | PARTITION BY [RANGE] (col1, …) | Range-based partitioning | PARTITION BY RANGE (col1, …) |
2 | (PARTITION num ENDING AT (limit | MAXVALUE), … ) | Partition definition | (PARTITION name VALUES LESS THAN (limit | MAXVALUE), … ) |
Converting DB2 for z/OS CREATE TABLESPACE statement to Oracle:
DB2 for z/OS | Oracle | ||
1 | CREATE TABLESPACE name | CREATE TABLESPACE name | |
2 | LOB | LOB tablespace | Removed |
3 | IN database | Database name | Removed |
4 | USING STOGROUP name | Storage group clause | DATAFILE 'tbsname.dbf' |
5 | PCTFREE num | Free space to leave in an index page | Removed |
6 | COMPRESS YES | NO | Compression enabled | Removed |
7 | FREEPAGE num | Leave a free page per num pages | Removed |
8 | BUFFERPOOL name | Bufferpool for tablespace | Removed |
9 | GBPCACHE CHANGED | ALL | SYSTEM | NONE | Pages written to global buffer pool | Removed |
10 | CLOSE NO | YES | Data set eligible for closing | Removed |
11 | COPY YES | NO | COPY utility is allowed for index | Removed |
12 | PIECESIZE size | Maximum addressability of data set | Removed |
13 | [NOT] LOGGED | Log changes | NOLOGGING and LOGGING |
14 | TRACKMOD YES | NO | Track changes | Removed |
15 | SEGSIZE num | Number of pages in segment | Removed |
16 | LOCKSIZE ANY | TABLESPACE | TABLE | PAGE | ROW | Lock escalation | Removed |
17 | LOCKMAX num | SYSTEM | Maximum number of locks | Removed |
18 | CCSID ASCII | UNICODE | EBCDIC | Data encoding | Removed |
19 | MAXROWS num | Maximum number of rows per page | Removed |
USING STOGROUP clause (DB2 for z/OS):
DB2 z/OS | Oracle | |
1 | PRIQTY num | Removed |
2 | SECQTY num | Removed |
3 | ERASE NO | YES | Removed |
Converting CREATE INDEX statement:
DB2 | Oracle | |
1 | PCTFREE num | PCTFREE num |
Additional DB2 z/OS clauses:
DB2 for z/OS | SQL Server | ||
1 | FREEPAGE num | Leave a free page per num pages | Removed |
2 | BUFFERPOOL name | Bufferpool for index | Removed |
3 | GBPCACHE CHANGED | ALL | NONE | Pages written to global buffer pool | Removed |
4 | NOT CLUSTER | Not the clustering index | Removed |
5 | CLOSE NO | YES | Data set eligible for closing or not | Removed |
6 | COPY YES | NO | COPY utility is allowed for index or not | Removed |
7 | PIECESIZE size | Maximum addressability of data set | Removed |
8 | [NOT] PADDED | Pad variable-length columns | Removed |
USING STOGROUP clause (DB2 for z/OS only):
DB2 z/OS | Oracle | |
1 | PRIQTY num | Removed |
2 | SECQTY num | Removed |
3 | ERASE NO | YES | Removed |
Converting stored procedures from IBM DB2 to Oracle:
DB2 | Oracle | ||
1 | CREATE OR REPLACE PROCEDURE | CREATE OR REPLACE PROCEDURE | |
2 | IN | OUT | INOUT param datatype(length) | param IN | OUT | IN OUT datatype | |
3 | DETERMINISTIC | Removed | |
4 | NOT DETERMINISTIC | Removed | |
5 | LANGUAGE SQL | Removed | |
6 | CONTAINS SQL | Removed | |
7 | NO SQL | Removed | |
8 | READS SQL DATA | Removed | |
9 | MODIFIES SQL DATA | Removed | |
10 | [DYNAMIC] RESULT SETS num | Removed | |
11 | SPECIFIC name | Removed | |
12 | CALLED ON NULL INPUT | Removed | |
13 | INHERIT SPECIAL REGISTERS | Removed | |
14 | [NO] EXTERNAL ACTION | Removed | |
15 | COLLID name | Package collection (z/OS) | Removed |
16 | WLM ENVIRONMENT name | Workload manager (z/OS) | Removed |
17 | RUN OPTIONS 'options' | Run-time options (z/OS) | Removed |
18 | No AS keyword before outer BEGIN END block | IS keyword added | |
19 | Optional label before outer block label: BEGIN END label | Label removed | |
20 | Declarations are inside BEGIN END block | Declarations are before BEGIN END block | |
21 | Custom delimiter at the end | / |
For more information, see Conversion of Procedural SQL Statements.
Converting user-defined functions from DB2 to Oracle:
DB2 | Oracle | ||
1 | CREATE OR REPLACE FUNCTION name | CREATE OR REPLACE FUNCTION name | |
2 | IN | OUT | INOUT param datatype(length) | param IN | OUT | IN OUT datatype | |
3 | RETURNS datatype(length) | RETURN datatype | |
4 | DETERMINISTIC | DETERMINISTIC | |
5 | NOT DETERMINISTIC | Removed | |
6 | LANGUAGE SQL | Removed | |
7 | CONTAINS SQL | Removed | |
8 | NO SQL | Removed | |
9 | READS SQL DATA | Removed | |
10 | MODIFIES SQL DATA | Removed | |
11 | SPECIFIC name | Removed | |
12 | CALLED ON NULL INPUT | Removed | |
13 | RETURNS NULL ON NULL INPUT | Removed | |
14 | INHERIT SPECIAL REGISTERS | Removed | |
15 | [NO] EXTERNAL ACTION | Removed | |
16 | No AS keyword before outer BEGIN END block | AS keyword added | |
17 | BEGIN ATOMIC | BEGIN | |
18 | Declarations are inside BEGIN END block | Declarations are before BEGIN END block | |
19 | No specific delimiter at the end | / |
For more information, see Conversion of Procedural SQL Statements.
Converting triggers from DB2 to Oracle:
DB2 | Oracle | ||
1 | CREATE OR REPLACE TRIGGER | CREATE OR REPLACE TRIGGER | |
2 | NO CASCADE BEFORE | Trigger action | BEFORE |
3 | AFTER | AFTER | |
4 | INSTEAD OF | INSTEAD OF | |
5 | INSERT | Trigger operation | INSERT |
6 | UPDATE [OF col, …] | UPDATE [OF col, …] | |
7 | DELETE | DELETE | |
8 | REFERENCING NEW [AS] new OLD [AS] old | REFERENCING NEW [AS] new OLD [AS] old | |
9 | FOR EACH ROW | FOR EACH ROW | |
10 | FOR EACH STATEMENT | FOR EACH STATEMENT | |
11 | MODE DB2SQL | Mode in DB2 | Removed |
12 | WHEN (condition) | Trigger condition | WHEN (condition) |
13 | BEGIN ATOMIC | BEGIN | |
14 | NEW correlation name | :NEW correlation name | |
15 | NEW. reference can be omitted in assignment | :NEW. must be specified | |
16 | OLD.column | OLD correlation name | :OLD.column |
17 | Optional BEGIN … END | If one statement in the body | BEGIN … END required |
18 | No specific delimiter at the end | / |
For more information, see Conversion of Procedural SQL Statements.
Converting procedural SQL statements used in stored procedures, functions and triggers from IBM DB2 to Oracle:
DB2 | Oracle | ||
1 | ALLOCATE CURSOR FOR RESULT SET | Process result set in SQL procedure | Removed, linked with SYS_REFCURSOR |
2 | ASSOCIATE RESULT SET LOCATOR | ||
3 | CALL proc(param, …) | Call a procedure | proc(param, …) |
4 | CASE WHEN THEN ELSE END CASE | CASE statement | CASE WHEN THEN ELSE END CASE |
5 | DECLARE var datatype DEFAULT value | Variable declaration | var datatype DEFAULT value |
DECLARE var, var2, … datatype | var datatype; var2 datatype; … | ||
6 | DECLARE cur CURSOR FOR sql | Cursor declaration | CURSOR cur IS sql |
DECLARE cur CURSOR WITH HOLD ... | Remains open on commit | WITH HOLD keyword removed | |
DECLARE cur CURSOR WITH RETURN FOR sql | Result set | cur OUT SYS_REFCURSOR | |
7 | DECLARE CONTINUE HANDLER FOR NOT FOUND | NOT FOUND handler | EXCEPTION WHEN NO_DATA_FOUND, cur%NOTFOUND, SQL%ROWCOUNT |
DECLARE type HANDLER FOR SQLEXCEPTION | SQL exception handler | EXCEPTION WHEN OTHERS | |
8 | DECLARE name CONDITION FOR SQLSTATE '23505' | Unique key violation | EXCEPTION WHEN DUP_VAL_ON_INDEX |
DECLARE name CONDITION FOR SQLSTATE '02000' | No data found | EXCEPTION WHEN NO_DATA_FOUND, cur%NOTFOUND, SQL%ROWCOUNT |
|
9 | DECLARE SQLCODE INTEGER DEFAULT 0 | SQLCODE declaration | Declaration is not required, removed |
DECLARE SQLSTATE CHAR(5) | SQLSTATE declaration | ||
10 | FETCH [FROM] cur INTO … | Fetch a cursor | FETCH cur INTO … |
11 | FOR var AS SELECT … DO stmts END FOR; | For each row loop | FOR var IN (SELECT …) LOOP stmts END LOOP; |
FOR var AS cur CURSOR FOR SELECT … DO stmts END FOR; |
|||
12 | GET DIAGNOSTICS EXCEPTION 1 var = MESSAGE_TEXT | Get the error message | var := SQLERRM |
GET DIAGNOSTICS var = ROW_COUNT | Get affected rows | var := SQL%ROWCOUNT | |
GET DIAGNOSTICS var = DB2_RETURN_STATUS | Procedure call status | Procedure OUT parameter | |
13 | IF THEN ELSEIF ELSE END IF | IF statement | IF THEN ELSIF ELSE END IF |
IF (SELECT COUNT(*) ... ) > 0 THEN | SELECT COUNT(*) INTO cnt ...; IF cnt > 0 THEN | ||
14 | label: | Label declaration | <<label>> |
15 | LEAVE label; | Leave a loop | EXIT label; |
16 | LOOP stmts END LOOP; | A loop statement | LOOP stmts END LOOP; |
17 | REPEAT stmts UNTIL condition END REPEAT; | Conditional loop | LOOP stmts EXIT WHEN condition; END LOOP; |
18 | RESIGNAL; | Resignal the exception | RAISE; |
19 | SET v1 = value | Assignment statement | v1 := value |
SET v1 = value, v2 = value2, … | v1 := value; v2 := value2; … | ||
SET (v1, v2, …) = (value, value2, …) | v1 := value; v2 := value2; … | ||
SET (v1, v2, …) = (SELECT c1, c2, …) | SELECT c1, c2, … INTO v1, v2, … | ||
20 | SIGNAL SQLSTATE 'num' ('text') | Raise an exception | RAISE_APPLICATION_ERROR(-num, 'text') |
21 | VALUES c1, … INTO v1, … | Assignment statement | v1 := c1; … |
22 | WHILE condition DO sql END WHILE | A loop statement | WHILE condition LOOP sql END LOOP; |
Converting SQL statements from IBM DB2 to Oracle:
DB2 | Oracle | ||
1 | COMMENT ON tab (col IS 'text') | Comment on column (z/OS) | COMMENT ON COLUMN tab.col IS 'text' |
2 | CREATE AUXILIARY TABLE name | Create a LOB table (z/OS) | Commented |
3 | CREATE DATABASE name | Create a database | Commented |
4 | CREATE STOGROUP name | Create a storage group (z/OS) | Commented |
5 | DECLARE GLOBAL TEMPORARY TABLE | Create a temporary table | CREATE GLOBAL TEMPORARY TABLE |
6 | DROP DATABASE name | Drop a database | Commented |
7 | DROP STOGROUP name | Drop a storage group (z/OS) | Commented |
8 | SET CURRENT PATH = list | Set the current path | Removed |
9 | SET CURRENT SCHEMA = name | Set the current schema | ALTER SESSION SET CURRENT_SCHEMA name |
10 | UPDATE t1 SET (c1, c2, …) = (v1, v2, …) | Update statement | UPDATE t1 SET c1 = v1, c2 = v2, … |
11 | VALUES c1, … | Single-row result set | SELECT c1, … FROM dual |
VALUES c1, … INTO v1, … | Assignment statement | v1 := c1; … |
Converting Command Line Processor (CLP) commands from IBM DB2 to Oracle:
DB2 | Oracle | ||
1 | EXPORT TO file OF DEL select_stmt | Export to delimited ASCII file | SPOOL file; select_stmt; SPOOL OFF; |
Mapping error codes and messages from DB2 to Oracle:
DB2 | Oracle | ||
1 | SQLSTATE '02000', SQLCODE 100 | Row not found | cur%NOTFOUND, SQL%NOTFOUND, SQLCODE 100 |
2 | SQLSTATE '23505' | Unique constraint violation | ORA-00001 error, DUP_VAL_ON_INDEX exception |
3 | SQLSTATE '8xxxx' | User-defined error |
Note: In Oracle you can use the SQLCODE variable in an exception handler only.