SQLines provides tools and services 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 and Perl/PHP applications. Using SQLines tools you can convert COBOL stored procedures and programs to Oracle PL/SQL.
Technical information on migration from IBM DB2 to Oracle.
Last Update: IBM DB2 for LUW and z/OS 10.1, and Oracle 12c
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:
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 |
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) |
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:
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), … ) |
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.