ORACLE vs DB2

IBM DB2 to Oracle Migration

SQLines provides open-source tools to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from IBM DB2 to Oracle.

We also help convert embedded SQL statements in C/C++ (ODBC, ESQL/C), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Perl, PHP, Python, Linux shell and other applications. SQLines can also assist you to convert COBOL stored procedures and programs to Oracle PL/SQL or Java.

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

DB2 to Oracle Migration Tools

SQLines tools to help you migrate from IBM DB2 to Oracle:

• SQLines Data - Data Transfer, Schema Migration and Validation Tool
• SQLines - Database and SQL Conversion Tool
• COBOL Stored Procedures and Programs Conversion
• Online SQL Conversion Tool

SQLines SQL Converter Tool

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from IBM DB2 to Oracle.

SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an IBM DB2 database use SQLines Data tool.

Try SQLines Online or download a free Desktop Version.

DB2 to Oracle Migration Reference

Technical information on migration from IBM DB2 to Oracle.

• SQL Language Elements
• Data Types
• Built-in SQL Functions
• SELECT Statement
• CREATE TABLE
• CREATE INDEX
• CREATE PROCEDURE
• CREATE FUNCTION
• CREATE TRIGGER
• Procedural SQL Statements
• SQL Statements
• Command Line Processor (CLP) Commands
• Error Codes and Messages

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. 

출처: http://www.sqlines.com/db2-to-oracle