IBM DB2 to Oracle Migration

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:

  • IBM DB2 for LUW, z/OS and OS/400 11.x, 10.x, 9.x, 8.x and 7.x
  • Oracle 21c, 19c, 18c, 12c and 11g

DB2 to Oracle Migration Reference

Language Elements

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 Types

Data type mapping between DB2 and Oracle:

DB2 Oracle
1 BIGINT 64-bit integer NUMBER(19)
2 BLOB(n) Binary large object, 1 ⇐ n ⇐ 2G BLOB
3 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 254 CHAR(n), CHARACTER(n)
4 CHAR(n) FOR BIT DATA Fixed-length byte string, 1 ⇐ n ⇐ 254 RAW(n)
5 CHARACTER VARYING(n) Variable-length string, 1 ⇐ n ⇐ 32672 VARCHAR2(n)
6 CLOB(n) Character large object, 1 ⇐ n ⇐ 2G CLOB
7 DATE Date (year, month and day) DATE Includes time part
8 DBCLOB(n) UTF-16 character large object, 1 ⇐ n ⇐ 1G NCLOB
9 DECIMAL(p,s), DEC(p,s) Fixed-point number NUMBER(p,s)
10 DECFLOAT(16 | 34) IEEE floating-point number NUMBER
11 DOUBLE [PRECISION] Double-precision floating-point number BINARY_DOUBLE
12 FLOAT(p) Double-precision floating-point number BINARY_DOUBLE
13 GRAPHIC(n) Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127 NCHAR(n)
14 INTEGER, INT 32-bit integer NUMBER(10)
15 NCHAR(n) Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127 NCHAR(n)
16 NCHAR VARYING(n) Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR2(n)
17 NCLOB(n) UTF-16 character large object, 1 ⇐ n ⇐ 1G NCLOB
18 NUMERIC(p,s), NUM(p,s) Fixed-point number NUMBER(p,s)
19 NVARCHAR(n) Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR2(n)
20 REAL Single-precision floating-point number BINARY_FLOAT
21 SMALLINT 16-bit integer NUMBER(5)
22 TIME Time (hour, minute, and second) TIMESTAMP(0)
23 TIMESTAMP(p) Date and time with fraction TIMESTAMP(p)
24 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 32672 VARCHAR2(n)
25 VARCHAR(n) FOR BIT DATA Variable-length byte string, 1 ⇐ n ⇐ 32672 RAW(n)
26 VARGRAPHIC(n) Variable-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR2(n)
27 XML XML data XMLTYPE

Built-in SQL Functions

Converting functions:

DB2 Oracle
1 ABS(num), ABSVAL(num) Get the absolute value ABS(num)
2 ACOS(num) Get the arc cosine ACOS(num)
3 ADD_MONTHS(date, num) Add num months to datetime ADD_MONTHS(date, num)
4 ASCII(str) Get ASCII code of left-most char ASCII(str)
5 ASIN(num) Get the arc sine ASIN(num)
6 ATAN(num) Get the arc tangent ATAN(num)
7 ATAN2(x, y) Get the arc tangent of x and y ATAN2(y, x)
8 ATANH(exp) Get hyperbolic arctangent
9 BIGINT(exp) Convert to 64-bit integer TRUNC(TO_NUMBER(exp))
10 BITAND(exp1, exp2) Perform bitwise AND BITAND(exp1, exp2)
11 BITANDNOT(exp) Perform bitwise AND NOT
12 BITOR(exp) Performs bitwise OR
13 BITXOR(exp) Performs bitwise exclusive OR
14 BITNOT(exp) Performs bitwise NOT
13 BLOB(exp [,size]) Convert to BLOB TO_BLOB(exp)
14 CEILING(num) Get the smallest following integer CEIL(num)
CEIL(num)
15 CHAR(string, num) Truncate or pad string to num RPAD(SUBSTR(string, 1, num), num)
CHAR(date, USA) Convert date to string TO_CHAR(date, 'MM/DD/YYYY')
16 CHAR_LENGTH(string) Get length of string in characters LENGTH(string)
CHARACTER_LENGTH(string)
17 CHAR_LENGTH(string, units) Get length of string in units LENGTH2(string), LENGTH4(string),
LENGTHB(string)
CHARACTER_LENGTH(string, units)
18 CLOB(exp [,size]) Convert to CLOB TO_CLOB(exp)
19 CHR(num) Get character from ASCII code CHR(num)
20 COALESCE(exp1, exp2, …) Return first non-NULL expression COALESCE(exp1, exp2, …)
21 CONCAT(str1, str2) Concatenate strings CONCAT(str1, str2)
22 COS(num) Get the cosine COS(num)
23 COT(num) Get the cotangent 1 / TAN(num)
24 CURRENT DATE Get the current date TRUNC(SYSDATE)
CURRENT_DATE
25 CURRENT SCHEMA Get the current schema CURRENT_SCHEMA
CURRENT_SCHEMA
26 CURRENT SERVER Get the current database name SYS_CONTEXT('USERENV', 'DB_NAME')
CURRENT_SERVER
27 CURRENT SQLID Get current schema CURRENT_SCHEMA
CURRENT_SQLID
28 CURRENT TIMESTAMP Get the current date and time CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
29 CURRENT TIME Get the current time SYSTIMESTAMP
CURRENT_TIME
30 CURRENT USER Get the authenticated user name USER
CURRENT_USER
31 CURSOR_ROWCOUNT(cur) Get the number of fetched rows cur%ROWCOUNT
32 DATE(timestamp) Convert to DATE TRUNC(timestamp)
33 DAY(datetime) Extract day from datetime EXTRACT(DAY FROM datetime)
34 DAYNAME(datetime) Get the name of the weekday TO_CHAR(datetime, 'Day')
35 DAYOFWEEK(datetime) Get the weekday index TO_NUMBER(TO_CHAR(datetime,
'D'))
36 DAYOFWEEK_ISO(exp) Get the day of the week as int TO_NUMBER(TO_CHAR(exp, 'D'))
37 DAYOFYEAR(datetime) Get the day of the year TO_NUMBER(TO_CHAR(datetime,
'DDD'))
38 DAYS(exp) Get the number of days (exp - DATE '0001-01-02')
39 DBCLOB(exp [,size]) Convert to DBCLOB TO_CLOB(exp)
40 DECFLOAT(exp [,size]) Convert to DECFLOAT TO_NUMBER(exp)
41 DECFLOAT_FORMAT(exp [,fmt]) Convert to DECFLOAT(34) TO_NUMBER(exp [,fmt])
42 DECIMAL Convert to DECIMAL TO_NUMBER
43 DEC
44 DECODE(exp, when, then, …) Evaluate condition DECODE(exp, when, then, …)
45 DEGREES(num) Convert radians to degrees (num) * 180/3.1415926535
46 DEREF(exp) Get instance of the target type DEREF(exp)
47 DIGITS(exp) Extract digits only TRANSLATE(exp, '0-+.,', '0')
48 DOUBLE(exp) Convert to DOUBLE TO_NUMBER(exp)
49 DOUBLE_PRECISION(exp)
50 EMPTY_BLOB() Get empty BLOB EMPTY_BLOB()
51 EMPTY_CLOB() Get empty CBLOB EMPTY_CLOB()
52 EMPTY_DBCLOB() Get empty DBCLOB EMPTY_CLOB()
53 EMPTY_NCLOB()
54 EXP(n) Raise e to the nth power EXP(n)
55 EXTRACT(unit FROM datetime) Extract unit from datetime EXTRACT(unit FROM datetime)
56 FLOAT(n) Convert to DOUBLE TO_NUMBER(n)
57 FLOOR(num) Get the largest preceding int FLOOR(num)
58 GREATEST(exp, exp2, …) Get the maximum value in a set GREATEST(exp, exp2, …)
59 HEX(exp) Convert to hex string
60 HEXTORAW(exp) Convert hex string to binary HEXTORAW(exp)
61 HOUR(exp) Extract hour from datetime EXTRACT(HOUR FROM exp)
62 INITCAP(string) Capitalize words INITCAP(string)
63 INSERT(exp, start, len, ins) Replace substring User-defined function
64 INSTR(str, substr, pos, num) Get position of substring INSTR(str, substr, pos, num)
65 INSTRB(exp, search, start, num) Get position of substring in bytes INSTRB(exp, search, start, num)
66 INTEGER(exp) Convert to integer TRUNC(TO_NUMBER(exp))
67 INT(exp)
68 JULIAN_DAY(exp) Get Julian day TO_NUMBER(TO_CHAR(exp, 'J'))
69 LAST_DAY(date) Get last day of the month LAST_DAY(date)
70 LCASE(string) Lowercase string LOWER(string)
71 LEAST(exp, exp2, …) Get the minimum value in a set LEAST(exp, exp2, …)
72 LEFT(string, n) Get n leftmost characters SUBSTR(string, 1, n)
73 LENGTH(string) Get length of string in chars LENGTH(string)
74 LN(exp) Get natural logarithm LN(exp)
75 LOCATE(substring, str, start) Get position of substring INSTR(str, substring, start)
76 LOCATE_IN_STRING(exp, search, start, num) Get position of substring INSTR(exp, search, start, num)
77 LOG10(exp) Get logarithm, base 10 LOG(10, exp)
78 LONG_VARCHAR(exp) Convert to LONG VARCHAR TO_CLOB(exp)
79 LONG_VARGRAPHIC(exp) Convert to LONG VARGRAPHIC TO_CLOB(exp)
80 LOWER(string) Lowercase string LOWER(string)
81 LPAD(string, len) Pad the left-side of string LPAD(string, len)
LPAD(string, len, pad) LPAD(string, len, pad)
82 LTRIM(string) Remove leading spaces LTRIM(string)
LTRIM(string, set) Remove leading chars LTRIM(string, set)
83 MAX(exp, exp2, …) Get the maximum value in a set GREATEST(exp, exp2, …)
84 MICROSECOND(exp) Get the microsecond TO_NUMBER(TO_CHAR(exp, 'FF6'))
85 MIDNIGHT_SECONDS(exp) Get seconds since midnight TO_NUMBER(TO_CHAR(exp, 'SSSSS'))
86 MIN(exp, exp2, …) Get the minimum value in a set LEAST(exp, exp2, …)
87 MINUTE(datetime) Extract minute from datetime EXTRACT(MINUTE FROM datetime)
88 MOD(dividend, divisor) Get the remainder MOD(dividend, divisor)
89 MONTH(date) Extract month from date EXTRACT(MONTH FROM date)
90 MONTHNAME(date) Get the name of the month TO_CHAR(date, 'Month')
91 MONTHS_BETWEEN(date1, date2) Get number of months between
date1 and date2
MONTHS_BETWEEN(date1, date2)
92 MULTIPLY_ALT(exp, exp2) Get product of the 2 arguments (exp * exp2)
93 NCHAR(exp) Convert to NCHAR TO_NCHAR(exp)
94 NCLOB(exp [,size]) Convert to NCLOB TO_NCLOB(exp)
95 NVARCHAR Convert to NVARCHAR TO_NCHAR
96 NEXT_DAY(exp, weekday) Get next weekday NEXT_DAY(exp, exp2)
97 NULLIF(exp1, exp2) Return NULL if exp1 = exp2 NULLIF(exp1, exp2)
98 NVL(exp1, exp2) Replace NULL with the specified value NVL(exp1, exp2)
NVL(exp1, exp2, …) Return first non-NULL expression COALESCE(exp1, exp2, …)
99 NVL2(exp1, exp2, exp3) Return exp2 if exp1 is not NULL, otherwise exp3 NVL2(exp1, exp2, exp3)
100 OCTET_LENGTH(exp) Get length in bytes LENGTHB(exp)
101 OVERLAY(exp, ins, start, len, unit) Replace substring User-defined function
102 POSITION(substring, exp, unit) Get position of substring INSTR(exp, substring)
103 POSSTR(exp, substring) Get position of substring INSTR(exp, substring)
104 POWER(value, n) Raise value to the nth power POWER(value, n)
105 QUARTER(date) Get the quarter of the year TO_NUMBER(TO_CHAR(date, 'Q'))
106 RADIANS(numeric) Convert degrees to radians (numeric) * 3.1415926535/180
107 RAISE_ERROR(sqlstate, exp) Raise an error RAISE_APPLICATION_ERROR
108 RAND([integer]) Get random float value in (0, 1) DBMS_RANDOM.VALUE
109 REAL(exp) Convert to REAL TO_NUMBER(exp)
110 REPEAT(string, n) Repeat string n times RPAD(string, LENGTH(string) * n, string)
111 REPLACE(str, search) Remove search-string REPLACE(str, search)
REPLACE(str, search, replace) Replace search-string REPLACE(str, search, replace)
112 RIGHT(string, n) Get n rightmost characters SUBSTR(string, -n)
113 ROUND(num, integer) Get rounded value ROUND(num, integer)
114 ROUND_TIMESTAMP(exp [,format]) Get rounded datetime ROUND(exp [,format])
115 RPAD(string, len) Pad the right-side of string RPAD(string, len)
RPAD(string, len, pad) RPAD(string, len, pad)
116 RTRIM(string) Remove trailing spaces RTRIM(string)
RTRIM(string, set) Remove trailing chars RTRIM(string, set)
117 SECOND(datetime[, integer]) Extract second from datetime TRUNC(EXTRACT(SECOND FROM
datetime)[, integer])
118 SIGN(exp) Get sign of exp SIGN(exp)
119 SIN(num) Get sine SIN(num)
120 SINH(num) Get hyperbolic sine SINH(num)
121 SMALLINT(exp) Convert to SMALLINT TRUNC(TO_NUMBER(exp))
122 SOUNDEX(string) Get 4-character sound code SOUNDEX(string)
123 SPACE(integer) Get string of spaces RPAD(' ', integer)
124 SQRT(num) Get square root SQRT(num)
125 STRIP(exp [,type, character]) Remove characters TRIM([type character FROM] exp)
126 SUBSTR(string, pos, len) Get a substring of string SUBSTR(string, pos, len)
127 SUBSTR2(exp, start [,len]) Get a substring of exp SUBSTR2(exp, start [,len])
128 SUBSTRB(exp, start [,len]) Get a substring of exp SUBSTRB(exp, start [,len])
129 SUBSTRING(exp, start [,len], unit) Get a substring of exp SUBSTR2(exp, start [,len])
SUBSTR4(exp, start [,len])
SUBSTRB(exp, start [,len])
130 TAN(num) Get tangent TAN(num)
131 TANH(num) Get hyperbolic tangent TANH(num)
132 TIME(exp) Get time TO_TIMESTAMP(exp)
133 TIMESTAMP(exp) Convert to TIMESTAMP TO_TIMESTAMP(exp)
134 TIMESTAMP_FORMAT(exp) Convert to TIMESTAMP TO_TIMESTAMP(exp)
135 TIMESTAMP_ISO(exp) Convert to TIMESTAMP TO_TIMESTAMP(exp)
136 TIMESTAMPDIFF(exp, exp2) Difference between two timestamps User-defined function
137 TO_CHAR(exp [,format]) Convert to string TO_CHAR(exp [,format])
138 TO_CLOB(exp [,size]) Convert to CLOB TO_CLOB(exp)
139 TO_DATE(exp) Convert to TIMESTAMP TO_DATE(exp)
140 TO_NCHAR(exp) Convert to NCHAR TO_NCHAR(exp)
141 TO_NCLOB(exp [,size]) Convert to NCLOB TO_NCLOB(exp)
142 TO_NUMBER(exp [,fmt]) Convert to DECFLOAT(34) TO_NUMBER(exp [,fmt])
143 TO_SINGLE_BYTE(exp) Convert to single-byte character TO_SINGLE_BYTE(exp)
144 TO_TIMESTAMP(exp) Convert to TIMESTAMP TO_TIMESTAMP(exp)
145 TRANSLATE(exp, from, to) Replace characters TRANSLATE(exp, from, to)
146 TRIM([type trim FROM] string) Remove characters TRIM([type trim FROM] string)
147 TRUNC_TIMESTAMP(exp [,format] Truncate TIMESTAMP TRUNC(exp [,format]
148 TRUNC(exp, exp2) Truncate exp TRUNC(exp, exp2)
149 TRUNCATE(exp, exp2) TRUNC(exp, exp2)
150 UCASE(string) Uppercase string UPPER(string)
151 UPPER(string) Uppercase string UPPER(string)
152 VALUE(exp, exp2, …) Return first non-NULL expression COALESCE(exp, exp2, …)
153 VARCHAR(exp [,exp2]) Convert to VARCHAR TO_CHAR(exp [,exp2])
154 VARCHAR_BIT_FORMAT(exp [,fmt]) Convert hex string to binary HEXTORAW(exp)
155 VARCHAR_FORMAT(exp [,fmt]) Convert to string TO_CHAR(exp [,fmt])
156 VARCHAR_FORMAT_BIT(exp [,fmt]) Convert binary to hex string RAWTOHEX(exp)
157 VARGRAPHIC(exp [,exp2]) Convert to NCHAR TO_NCHAR(exp [,exp2])
158 WEEK(exp) Get week of the year TO_NUMBER(TO_CHAR(exp, 'WW'))
159 WEEK_ISO(exp) Get week of the year TO_NUMBER(TO_CHAR(exp, 'IW'))
160 XMLATTRIBUTES(exp, …) Construct XML attributes XMLATTRIBUTES(exp, …)
161 XMLCOMMENT(exp) Generate an XML comment XMLCOMMENT(exp)
162 XMLCONCAT(exp, exp2, …) Concatenate XML expressions XMLCONCAT(exp, exp2, …)
163 XMLDOCUMENT(exp) Get XML document
164 XMLELEMENT(NAME exp) Get an XQuery element node XMLELEMENT(NAME exp)
165 XMLFOREST(exp, exp2, …) Get a forest of XML expressions XMLFOREST(exp, exp2, …)
166 XMLNAMESPACES(uri, …) Get namespace
167 XMLPARSE(DOCUMENT exp) Parse XML document XMLPARSE(DOCUMENT exp)
168 XMLPI(NAME identifier) Get XML processing instruction XMLPI(NAME identifier)
169 XMLQUERY(exp, …) Convert XML data in SQL XMLQUERY(exp, …)
170 XMLROW(exp, …) Get XML document node
171 XMLSERIALIZE(CONTENT exp
AS datatype)
Get a serialized XML value XMLSERIALIZE(CONTENT exp
AS datatype)
172 XMLTEXT(exp, …) Get XML text
173 XMLVALIDATE(exp, …) Get XML with information
174 XMLXMLXSROBJECTID(exp) Get XSR object
175 XSLTRANSFORM(doc USING xslt) Transform XML document XMLTRANSFORM(doc, xslt)
176 YEAR(date) Extract year from date EXTRACT(YEAR FROM date)

SELECT Statement

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

CREATE TABLE Statement

Converting CREATE TABLE statement from DB2 to Oracle:

DB2 Oracle
1 GENERATED ALWAYS | BY DEFAULT
AS IDENTITY
Identity column Emulated using sequence and trigger
2 FOR COLUMN system_name System column name (OS/400) Removed
3 FOR BIT DATA Binary data encoding Removed
FOR SBCS | MIXED DATA Column data encoding (z/OS) Removed
4 CCSID ASCII | UNICODE | EBCDIC Character set Removed
CCSID num Column character set (OS/400) Removed
5 DEFAULT exp Column default DEFAULT must be specified right after
data type, before NOT NULL etc.
6 IN tablespace Tablespace name TABLESPACE tablespace
7 DATA CAPTURE NONE | CHANGES Change data capture Removed
8 AUDIT NONE | CHANGES | ALL Audit type (z/OS) Removed
9 WITH RESTRICT ON DROP Drop restriction Removed
10 [NOT] VOLATILE Table size variation (z/OS) Removed
11 APPEND NO | YES Append rows on insert or load (z/OS) Removed
12 PARTITION BY SIZE EVERY n G Size-based partitioning (z/OS) Removed
13 PARTITION BY RANGE Range-based partitioning PARTITION BY RANGE (see below)

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:

DB2 Oracle
1 DECLARE GLOBAL TEMPORARY TABLE name CREATE GLOBAL TEMPORARY TABLE name
2 AS (SELECT …) Definition and data from SELECT AS (SELECT …)
3 ON COMMIT DELETE | PRESERVE ROWS ON COMMIT DELETE | PRESERVE ROWS
4 ON ROLLBACK DELETE ROWS Default behavior, clause removed
ON ROLLBACK PRESERVE ROWS Not supported, commented
5 LOGGED | NOT LOGGED Removed
6 WITH REPLACE TRUNCATE TABLE is used
7 WITH NO DATA Data not copied at creation time WHERE 1 = 0 condition added
DEFINITION ONLY
8 Options ON COMMIT etc. specified after AS (SELECT …) Options specified before AS (SELECT …)

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), … )

CREATE TABLESPACE Statement

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

CREATE INDEX Statement

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

CREATE PROCEDURE Statement

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.

CREATE FUNCTION Statement

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.

CREATE TRIGGER Statement

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.

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;

SQL Statements

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; …

CLP Commands

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;

Error Codes and Messagges

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.