ORACLE vs DB2

DB 2017. 5. 30. 14:41
728x90
728x170

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, PerlPHP, 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 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

Language Elements

Converting SQL language elements from DB2 to Oracle:

DB2Oracle
1string1 CONCAT string 2 CONCAT …String concatenation operatorstring1 || string 2 || …
2NEXTVAL | NEXT VALUE FOR seq_nameNext value for sequenceseq_name.NEXTVAL
3RESULT_SET_LOCATORProcessing result sets in procedureSYS_REFCURSOR

Datetime interval expressions:

DB2Oracle
1num DAY | DAYSInterval in daysINTERVAL 'num' DAY
var DAY | DAYSNUMTODSINTERVAL(var, 'DAY')
2num MINUTE | MINUTESInterval in minutesINTERVAL 'num' MINUTE
var MINUTE | MINUTESNUMTODSINTERVAL(var, 'MINUTE')

Data Types

Data type mapping between DB2 and Oracle:

DB2Oracle
1BIGINT64-bit integerNUMBER(19)
2BLOB(n)Binary large object, 1 ⇐ n ⇐ 2GBLOB 
3CHAR(n), CHARACTER(n)Fixed-length string, 1 ⇐ n ⇐ 254CHAR(n), CHARACTER(n)
4CHAR(n) FOR BIT DATAFixed-length byte string, 1 ⇐ n ⇐ 254RAW(n)
5CHARACTER VARYING(n)Variable-length string, 1 ⇐ n ⇐ 32672 VARCHAR2(n)
6CLOB(n)Character large object, 1 ⇐ n ⇐ 2GCLOB 
7DATEDate (year, month and day)DATEIncludes time part 
8DBCLOB(n)UTF-16 character large object, 1 ⇐ n ⇐ 1GNCLOB
9DECIMAL(p,s), DEC(p,s)Fixed-point numberNUMBER(p,s)
10DECFLOAT(16 | 34)IEEE floating-point numberNUMBER
11DOUBLE [PRECISION]Double-precision floating-point numberBINARY_DOUBLE
12FLOAT(p)Double-precision floating-point numberBINARY_DOUBLE
13GRAPHIC(n)Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127NCHAR(n)
14INTEGER, INT32-bit integerNUMBER(10)
15NCHAR(n)Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127NCHAR(n)
16NCHAR VARYING(n)Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR2(n)
17NCLOB(n)UTF-16 character large object, 1 ⇐ n ⇐ 1GNCLOB 
18NUMERIC(p,s), NUM(p,s)Fixed-point numberNUMBER(p,s)
19NVARCHAR(n)Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR2(n)
20REALSingle-precision floating-point numberBINARY_FLOAT
21SMALLINT16-bit integerNUMBER(5)
22TIMETime (hour, minute, and second)TIMESTAMP(0) 
23TIMESTAMP(p)Date and time with fractionTIMESTAMP(p)
24VARCHAR(n)Variable-length string, 1 ⇐ n ⇐ 32672 VARCHAR2(n)
25VARCHAR(n) FOR BIT DATAVariable-length byte string, 1 ⇐ n ⇐ 32672 RAW(n)
26VARGRAPHIC(n)Variable-length UTF-16 string, 1 ⇐ n ⇐ 16336 NVARCHAR2(n)
27XMLXML dataXMLTYPE

Built-in SQL Functions

Converting functions:

DB2Oracle
1ABS(num), ABSVAL(num)Get the absolute valueABS(num)
2ACOS(num)Get the arc cosineACOS(num)
3ADD_MONTHS(date, num)Add num months to datetimeADD_MONTHS(date, num)
4ASCII(str)Get ASCII code of left-most charASCII(str)
5ASIN(num)Get the arc sineASIN(num)
6ATAN(num)Get the arc tangentATAN(num)
7ATAN2(x, y)Get the arc tangent of x and yATAN2(y, x
8ATANH(exp)Get hyperbolic arctangent
9BIGINT(exp)Convert to 64-bit integerTRUNC(TO_NUMBER(exp))
10BITAND(exp1, exp2)Perform bitwise ANDBITAND(exp1, exp2)
11BITANDNOT(exp)Perform bitwise AND NOT
12BITOR(exp)Performs bitwise OR
13BITXOR(exp)Performs bitwise exclusive OR
14BITNOT(exp)Performs bitwise NOT
13BLOB(exp [,size])Convert to BLOBTO_BLOB(exp)
14CEILING(num)Get the smallest following integerCEIL(num)
CEIL(num)
15CHAR(stringnum)Truncate or pad string to numRPAD(SUBSTR(string, 1, num), num)
CHAR(date, USA)Convert date to stringTO_CHAR(date, 'MM/DD/YYYY')
16CHAR_LENGTH(string)Get length of string in charactersLENGTH(string)
CHARACTER_LENGTH(string)
17CHAR_LENGTH(string, units)Get length of string in unitsLENGTH2(string), LENGTH4(string), 
LENGTHB(string)
CHARACTER_LENGTH(string, units)
18CLOB(exp [,size])Convert to CLOBTO_CLOB(exp)
19CHR(num)Get character from ASCII codeCHR(num)
20COALESCE(exp1exp2, …)Return first non-NULL expressionCOALESCE(exp1exp2, …)
21CONCAT(str1, str2)Concatenate stringsCONCAT(str1, str2)
22COS(num)Get the cosineCOS(num)
23COT(num)Get the cotangent1 / TAN(num)
24CURRENT DATEGet the current dateTRUNC(SYSDATE)
CURRENT_DATE
25CURRENT SCHEMAGet the current schemaCURRENT_SCHEMA
CURRENT_SCHEMA
26CURRENT SERVERGet the current database nameSYS_CONTEXT('USERENV', 'DB_NAME')
CURRENT_SERVER
27CURRENT SQLIDGet current schemaCURRENT_SCHEMA
CURRENT_SQLID
28CURRENT TIMESTAMPGet the current date and timeCURRENT_TIMESTAMP
CURRENT_TIMESTAMP
29CURRENT TIMEGet the current timeSYSTIMESTAMP
CURRENT_TIME
30CURRENT USERGet the authenticated user nameUSER
CURRENT_USER
31CURSOR_ROWCOUNT(cur)Get the number of fetched rowscur%ROWCOUNT
32DATE(timestamp)Convert to DATETRUNC(timestamp)
33DAY(datetime)Extract day from datetimeEXTRACT(DAY FROM datetime)
34DAYNAME(datetime)Get the name of the weekdayTO_CHAR(datetime, 'Day')
35DAYOFWEEK(datetime)Get the weekday indexTO_NUMBER(TO_CHAR(datetime
'D'))
36DAYOFWEEK_ISO(exp)Get the day of the week as intTO_NUMBER(TO_CHAR(exp, 'D')) 
37DAYOFYEAR(datetime)Get the day of the yearTO_NUMBER(TO_CHAR(datetime
'DDD'))
38DAYS(exp)Get the number of days(exp - DATE '0001-01-02')
39DBCLOB(exp [,size])Convert to DBCLOBTO_CLOB(exp)
40DECFLOAT(exp [,size])Convert to DECFLOATTO_NUMBER(exp)
41DECFLOAT_FORMAT(exp [,fmt])Convert to DECFLOAT(34)TO_NUMBER(exp [,fmt])
42DECIMALConvert to DECIMALTO_NUMBER
43DEC
44DECODE(expwhenthen, …)Evaluate conditionDECODE(expwhenthen, …)
45DEGREES(num)Convert radians to degrees(num) * 180/3.1415926535
46DEREF(exp)Get instance of the target typeDEREF(exp)
47DIGITS(exp)Extract digits onlyTRANSLATE(exp, '0-+.,', '0')
48DOUBLE(exp)Convert to DOUBLETO_NUMBER(exp)
49DOUBLE_PRECISION(exp)
50EMPTY_BLOB()Get empty BLOBEMPTY_BLOB()
51EMPTY_CLOB()Get empty CBLOBEMPTY_CLOB()
52EMPTY_DBCLOB()Get empty DBCLOBEMPTY_CLOB()
53EMPTY_NCLOB()
54EXP(n)Raise e to the nth powerEXP(n)
55EXTRACT(unit FROM datetime)Extract unit from datetimeEXTRACT(unit FROM datetime)
56FLOAT(n)Convert to DOUBLETO_NUMBER(n)
57FLOOR(num)Get the largest preceding intFLOOR(num)
58GREATEST(exp, exp2, …)Get the maximum value in a setGREATEST(exp, exp2, …)
59HEX(exp)Convert to hex string
60HEXTORAW(exp)Convert hex string to binaryHEXTORAW(exp)
61HOUR(exp)Extract hour from datetimeEXTRACT(HOUR FROM exp)
62INITCAP(string)Capitalize wordsINITCAP(string)
63INSERT(expstartlenins)Replace substringUser-defined function
64INSTR(str, substr, pos, num)Get position of substringINSTR(str, substr, pos, num)
65INSTRB(expsearchstartnum)Get position of substring in bytesINSTRB(expsearchstartnum)
66INTEGER(exp)Convert to integerTRUNC(TO_NUMBER(exp))
67INT(exp)
68JULIAN_DAY(exp)Get Julian dayTO_NUMBER(TO_CHAR(exp, 'J'))
69LAST_DAY(date)Get last day of the monthLAST_DAY(date)
70LCASE(string)Lowercase stringLOWER(string)
71LEAST(exp, exp2, …)Get the minimum value in a setLEAST(exp, exp2, …)
72LEFT(string, n)Get n leftmost charactersSUBSTR(string, 1, n)
73LENGTH(string)Get length of string in charsLENGTH(string)
74LN(exp)Get natural logarithmLN(exp)
75LOCATE(substring, str, start)Get position of substringINSTR(str, substring, start
76LOCATE_IN_STRING(expsearchstartnum)Get position of substringINSTR(expsearchstartnum)
77LOG10(exp)Get logarithm, base 10LOG(10, exp)
78LONG_VARCHAR(exp)Convert to LONG VARCHARTO_CLOB(exp)
79LONG_VARGRAPHIC(exp)Convert to LONG VARGRAPHICTO_CLOB(exp)
80LOWER(string)Lowercase stringLOWER(string)
81LPAD(string, len)Pad the left-side of stringLPAD(string, len)
LPAD(string, len, pad)LPAD(string, len, pad)
82LTRIM(string)Remove leading spacesLTRIM(string)
LTRIM(string, set)Remove leading charsLTRIM(string, set)
83MAX(expexp2, …)Get the maximum value in a setGREATEST(expexp2, …)
84MICROSECOND(exp)Get the microsecondTO_NUMBER(TO_CHAR(exp, 'FF6'))
85MIDNIGHT_SECONDS(exp)Get seconds since midnightTO_NUMBER(TO_CHAR(exp, 'SSSSS'))
86MIN(expexp2, …)Get the minimum value in a setLEAST(expexp2, …)
87MINUTE(datetime)Extract minute from datetimeEXTRACT(MINUTE FROM datetime)
88MOD(dividend, divisor)Get the remainderMOD(dividend, divisor)
89MONTH(date)Extract month from dateEXTRACT(MONTH FROM date)
90MONTHNAME(date)Get the name of the monthTO_CHAR(date, 'Month')
91MONTHS_BETWEEN(date1, date2)Get number of months between 
date1 and date2
MONTHS_BETWEEN(date1, date2)
92MULTIPLY_ALT(exp, exp2)Get product of the 2 arguments(exp * exp2)
93NCHAR(exp)Convert to NCHARTO_NCHAR(exp)
94NCLOB(exp [,size])Convert to NCLOBTO_NCLOB(exp)
95NVARCHARConvert to NVARCHARTO_NCHAR
96NEXT_DAY(exp, weekday)Get next weekdayNEXT_DAY(exp, exp2)
97NULLIF(exp1, exp2)Return NULL if exp1 = exp2NULLIF(exp1, exp2)
98NVL(exp1, exp2)Replace NULL with the specified valueNVL(exp1, exp2)
NVL(exp1, exp2, …)Return first non-NULL expressionCOALESCE(exp1, exp2, …)
99NVL2(exp1, exp2, exp3)Return exp2 if exp1 is not NULL, otherwise exp3NVL2(exp1, exp2, exp3)
100OCTET_LENGTH(exp)Get length in bytesLENGTHB(exp)
101OVERLAY(exp, ins, start, len, unit)Replace substringUser-defined function
102POSITION(substring, exp, unit)Get position of substringINSTR(exp, substring
103POSSTR(exp, substring)Get position of substringINSTR(exp, substring)
104POWER(value, n)Raise value to the nth powerPOWER(value, n)
105QUARTER(date)Get the quarter of the yearTO_NUMBER(TO_CHAR(date, 'Q'))
106RADIANS(numeric)Convert degrees to radians(numeric) * 3.1415926535/180
107RAISE_ERROR(sqlstate, exp)Raise an errorRAISE_APPLICATION_ERROR 
108RAND([integer])Get random float value in (0, 1)DBMS_RANDOM.VALUE
109REAL(exp)Convert to REALTO_NUMBER(exp)
110REPEAT(string, n)Repeat string n timesRPAD(string, LENGTH(string) * nstring)
111REPLACE(str, search)Remove search-stringREPLACE(str, search)
REPLACE(str, search, replace)Replace search-stringREPLACE(str, search, replace)
112RIGHT(string, n)Get n rightmost charactersSUBSTR(string, -n
113ROUND(num, integer)Get rounded valueROUND(num, integer)
114ROUND_TIMESTAMP(exp [,format])Get rounded datetimeROUND(exp [,format])
115RPAD(string, len)Pad the right-side of stringRPAD(string, len)
RPAD(string, len, pad)RPAD(string, len, pad)
116RTRIM(string)Remove trailing spacesRTRIM(string)
RTRIM(string, set)Remove trailing charsRTRIM(string, set)
117SECOND(datetime[, integer])Extract second from datetimeTRUNC(EXTRACT(SECOND FROM 
datetime)[, integer])
118SIGN(exp)Get sign of expSIGN(exp)
119SIN(num)Get sineSIN(num)
120SINH(num)Get hyperbolic sineSINH(num)
121SMALLINT(exp)Convert to SMALLINTTRUNC(TO_NUMBER(exp))
122SOUNDEX(string)Get 4-character sound codeSOUNDEX(string)
123SPACE(integer)Get string of spacesRPAD(' ', integer)
124SQRT(num)Get square rootSQRT(num)
125STRIP(exp [,type, character])Remove charactersTRIM([type character FROM] exp)
126SUBSTR(string, pos, len)Get a substring of stringSUBSTR(string, pos, len)
127SUBSTR2(exp, start [,len])Get a substring of expSUBSTR2(exp, start [,len])
128SUBSTRB(exp, start [,len])Get a substring of expSUBSTRB(exp, start [,len])
129SUBSTRING(exp, start [,len], unit)Get a substring of expSUBSTR2(exp, start [,len])
SUBSTR4(exp, start [,len])
SUBSTRB(exp, start [,len])
130TAN(num)Get tangentTAN(num)
131TANH(num)Get hyperbolic tangentTANH(num)
132TIME(exp)Get timeTO_TIMESTAMP(exp)
133TIMESTAMP(exp)Convert to TIMESTAMPTO_TIMESTAMP(exp)
134TIMESTAMP_FORMAT(exp)Convert to TIMESTAMPTO_TIMESTAMP(exp)
135TIMESTAMP_ISO(exp)Convert to TIMESTAMPTO_TIMESTAMP(exp)
136TIMESTAMPDIFF(exp, exp2)Difference between two timestampsUser-defined function
137TO_CHAR(exp [,format])Convert to stringTO_CHAR(exp [,format])
138TO_CLOB(exp [,size])Convert to CLOBTO_CLOB(exp)
139TO_DATE(exp)Convert to TIMESTAMPTO_DATE(exp)
140TO_NCHAR(exp)Convert to NCHARTO_NCHAR(exp)
141TO_NCLOB(exp [,size])Convert to NCLOBTO_NCLOB(exp)
142TO_NUMBER(exp [,fmt])Convert to DECFLOAT(34)TO_NUMBER(exp [,fmt])
143TO_SINGLE_BYTE(exp)Convert to single-byte characterTO_SINGLE_BYTE(exp)
144TO_TIMESTAMP(exp)Convert to TIMESTAMPTO_TIMESTAMP(exp)
145TRANSLATE(exp, from, to)Replace charactersTRANSLATE(exp, from, to)
146TRIM([type trim FROM] string)Remove charactersTRIM([type trim FROM] string)
147TRUNC_TIMESTAMP(exp [,format]Truncate TIMESTAMPTRUNC(exp [,format]
148TRUNC(exp, exp2)Truncate expTRUNC(exp, exp2)
149TRUNCATE(exp, exp2)TRUNC(exp, exp2)
150UCASE(string)Uppercase stringUPPER(string)
151UPPER(string)Uppercase stringUPPER(string)
152VALUE(expexp2, …)Return first non-NULL expressionCOALESCE(expexp2, …)
153VARCHAR(exp [,exp2])Convert to VARCHARTO_CHAR(exp [,exp2])
154VARCHAR_BIT_FORMAT(exp [,fmt])Convert hex string to binaryHEXTORAW(exp)
155VARCHAR_FORMAT(exp [,fmt])Convert to stringTO_CHAR(exp [,fmt])
156VARCHAR_FORMAT_BIT(exp [,fmt])Convert binary to hex stringRAWTOHEX(exp)
157VARGRAPHIC(exp [,exp2])Convert to NCHARTO_NCHAR(exp [,exp2])
158WEEK(exp)Get week of the yearTO_NUMBER(TO_CHAR(exp, 'WW')) 
159WEEK_ISO(exp)Get week of the yearTO_NUMBER(TO_CHAR(exp, 'IW'))
160XMLATTRIBUTES(exp, …)Construct XML attributesXMLATTRIBUTES(exp, …)
161XMLCOMMENT(exp)Generate an XML commentXMLCOMMENT(exp)
162XMLCONCAT(exp, exp2, …)Concatenate XML expressionsXMLCONCAT(exp, exp2, …)
163XMLDOCUMENT(exp)Get XML document
164XMLELEMENT(NAME exp)Get an XQuery element nodeXMLELEMENT(NAME exp)
165XMLFOREST(exp, exp2, …)Get a forest of XML expressionsXMLFOREST(exp, exp2, …)
166XMLNAMESPACES(uri, …)Get namespace
167XMLPARSE(DOCUMENT exp)Parse XML documentXMLPARSE(DOCUMENT exp)
168XMLPI(NAME identifier)Get XML processing instructionXMLPI(NAME identifier)
169XMLQUERY(exp, …)Convert XML data in SQLXMLQUERY(exp, …)
170XMLROW(exp, …)Get XML document node
171XMLSERIALIZE(CONTENT exp 
AS datatype)
Get a serialized XML valueXMLSERIALIZE(CONTENT exp 
AS datatype)
172XMLTEXT(exp, …)Get XML text
173XMLVALIDATE(exp, …)Get XML with information
174XMLXMLXSROBJECTID(exp)Get XSR object
175XSLTRANSFORM(doc USING xslt)Transform XML documentXMLTRANSFORM(docxslt)
176YEAR(date)Extract year from dateEXTRACT(YEAR FROM date)

SELECT Statement

Converting SQL SELECT statement:

DB2Oracle
1SYSIBM.SYSDUMMY1 tableA single row, single column dummy tableDUAL table
2WHERE (c1, c2, …) = (v1, v2, …)Specific AND syntaxc1 = v1 AND c2 = v2 AND …
3EXCEPTSet operatorMINUS
4FETCH FIRST n ROWS ONLYReturn n rows after sorting ROWNUM and subquery
FETCH FIRST ROW ONLYReturn 1 row only after sorting
5WITH UR | CS | RS | RRIsolation level for SELECTWITH UR and CS removed

CREATE TABLE Statement

Converting CREATE TABLE statement from DB2 to Oracle:

DB2Oracle
1GENERATED ALWAYS | BY DEFAULT 
AS IDENTITY
Identity columnEmulated using sequence and trigger
2FOR COLUMN system_nameSystem column name (OS/400)Removed
3FOR BIT DATABinary data encodingRemoved
FOR SBCS | MIXED DATAColumn data encoding (z/OS)Removed
4CCSID ASCII | UNICODE | EBCDICCharacter setRemoved
CCSID numColumn character set (OS/400)Removed
5DEFAULT expColumn defaultDEFAULT must be specified right after 
data type, before NOT NULL etc. 
6IN tablespaceTablespace nameTABLESPACE tablespace
7DATA CAPTURE NONE | CHANGESChange data captureRemoved
8AUDIT NONE | CHANGES | ALLAudit type (z/OS)Removed
9WITH RESTRICT ON DROPDrop restrictionRemoved
10[NOT] VOLATILETable size variation (z/OS)Removed
11APPEND NO | YESAppend rows on insert or load (z/OS)Removed
12PARTITION BY SIZE EVERY n GSize-based partitioning (z/OS)Removed
13PARTITION BY RANGERange-based partitioningPARTITION BY RANGE (see below)

Implicit DEFAULT values in DB2:

DB2Oracle
1column CHAR(n) WITH DEFAULTcolumn CHAR(n) DEFAULT ''
2column VARCHAR(n) WITH DEFAULTcolumn VARCHAR2(n) DEFAULT ''
3column INTEGER WITH DEFAULTcolumn NUMBER(10) DEFAULT 0
4column DECIMAL(p, s) WITH DEFAULTcolumn NUMBER(p, s) DEFAULT 0
5column NUMERIC(p, s) WITH DEFAULTcolumn NUMBER(p, s) DEFAULT 0
6column DATE WITH DEFAULTcolumn DATE DEFAULT SYSDATE
7column TIMESTAMP WITH DEFAULTcolumn TIMESTAMP DEFAULT SYSTIMESTAMP
8column CLOB WITH DEFAULTcolumn CLOB DEFAULT EMPTY_CLOB()

Converting GLOBAL TEMPORARY TABLE clauses from DB2 to Oracle:

DB2Oracle
1DECLARE GLOBAL TEMPORARY TABLE nameCREATE GLOBAL TEMPORARY TABLE name
2AS (SELECT …)Definition and data from SELECTAS (SELECT …)
3ON COMMIT DELETE | PRESERVE ROWSON COMMIT DELETE | PRESERVE ROWS
4ON ROLLBACK DELETE ROWSDefault behavior, clause removed
ON ROLLBACK PRESERVE ROWSNot supported, commented 
5LOGGED | NOT LOGGEDRemoved
6WITH REPLACETRUNCATE TABLE is used
7WITH NO DATAData not copied at creation timeWHERE 1 = 0 condition added
DEFINITION ONLY
8Options ON COMMIT etc. specified after AS (SELECT …)Options specified before AS (SELECT …) 

Converting partitioning definition from DB2 to Oracle:

DB2Oracle
1PARTITION BY [RANGE] (col1, …)Range-based partitioningPARTITION 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/OSOracle
1CREATE TABLESPACE nameCREATE TABLESPACE name
2LOBLOB tablespaceRemoved
3IN databaseDatabase nameRemoved
4USING STOGROUP nameStorage group clauseDATAFILE 'tbsname.dbf'
5PCTFREE numFree space to leave in an index pageRemoved
6COMPRESS YES | NOCompression enabledRemoved
7FREEPAGE numLeave a free page per num pagesRemoved
8BUFFERPOOL nameBufferpool for tablespaceRemoved
9GBPCACHE CHANGED | ALL | SYSTEM | NONEPages written to global buffer poolRemoved
10CLOSE NO | YESData set eligible for closingRemoved
11COPY YES | NOCOPY utility is allowed for indexRemoved
12PIECESIZE sizeMaximum addressability of data setRemoved
13[NOT] LOGGEDLog changesNOLOGGING and LOGGING
14TRACKMOD YES | NOTrack changesRemoved
15SEGSIZE numNumber of pages in segmentRemoved
16LOCKSIZE ANY | TABLESPACE | TABLE | 
PAGE | ROW
Lock escalationRemoved
17LOCKMAX num | SYSTEMMaximum number of locksRemoved
18CCSID ASCII | UNICODE | EBCDICData encodingRemoved
19MAXROWS numMaximum number of rows per pageRemoved

USING STOGROUP clause (DB2 for z/OS):

DB2 z/OSOracle
1PRIQTY numRemoved
2SECQTY numRemoved
3ERASE NO | YESRemoved

CREATE INDEX Statement

Converting CREATE INDEX statement:

DB2Oracle
1PCTFREE numPCTFREE num

Additional DB2 z/OS clauses:

DB2 for z/OSSQL Server
1FREEPAGE numLeave a free page per num pagesRemoved
2BUFFERPOOL nameBufferpool for indexRemoved
3GBPCACHE CHANGED | ALL | NONEPages written to global buffer poolRemoved
4NOT CLUSTERNot the clustering indexRemoved
5CLOSE NO | YESData set eligible for closing or notRemoved
6COPY YES | NOCOPY utility is allowed for index or notRemoved
7PIECESIZE sizeMaximum addressability of data setRemoved
8[NOT] PADDEDPad variable-length columnsRemoved

USING STOGROUP clause (DB2 for z/OS only):

DB2 z/OSOracle
1PRIQTY numRemoved
2SECQTY numRemoved
3ERASE NO | YESRemoved

CREATE PROCEDURE Statement

Converting stored procedures from IBM DB2 to Oracle:

DB2Oracle
1CREATE OR REPLACE PROCEDURECREATE OR REPLACE PROCEDURE
2IN | OUT | INOUT param datatype(length)param IN | OUT | IN OUT datatype
3DETERMINISTICRemoved
4NOT DETERMINISTICRemoved
5LANGUAGE SQLRemoved
6CONTAINS SQLRemoved
7NO SQLRemoved
8READS SQL DATARemoved
9MODIFIES SQL DATARemoved
10[DYNAMIC] RESULT SETS numRemoved
11SPECIFIC nameRemoved
12CALLED ON NULL INPUTRemoved
13INHERIT SPECIAL REGISTERSRemoved
14[NO] EXTERNAL ACTIONRemoved
15COLLID namePackage collection (z/OS)Removed
16WLM ENVIRONMENT nameWorkload manager (z/OS)Removed
17RUN OPTIONS 'options'Run-time options (z/OS)Removed
18No AS keyword before outer BEGIN END blockIS keyword added
19Optional label before outer block label: BEGIN END labelLabel removed
20Declarations are inside BEGIN END blockDeclarations are before BEGIN END block
21Custom delimiter at the end/

For more information, see Conversion of Procedural SQL Statements.

CREATE FUNCTION Statement

Converting user-defined functions from DB2 to Oracle:

DB2Oracle
1CREATE OR REPLACE FUNCTION nameCREATE OR REPLACE FUNCTION name
2IN | OUT | INOUT param datatype(length)param IN | OUT | IN OUT datatype
3RETURNS datatype(length)RETURN datatype
4DETERMINISTICDETERMINISTIC
5NOT DETERMINISTICRemoved
6LANGUAGE SQLRemoved
7CONTAINS SQLRemoved
8NO SQLRemoved
9READS SQL DATARemoved
10MODIFIES SQL DATARemoved
11SPECIFIC nameRemoved
12CALLED ON NULL INPUTRemoved
13RETURNS NULL ON NULL INPUTRemoved
14INHERIT SPECIAL REGISTERSRemoved
15[NO] EXTERNAL ACTIONRemoved
16No AS keyword before outer BEGIN END blockAS keyword added
17BEGIN ATOMICBEGIN
18Declarations are inside BEGIN END blockDeclarations are before BEGIN END block
19No specific delimiter at the end/

For more information, see Conversion of Procedural SQL Statements.

CREATE TRIGGER Statement

Converting triggers from DB2 to Oracle:

DB2Oracle
1CREATE OR REPLACE TRIGGERCREATE OR REPLACE TRIGGER
2NO CASCADE BEFORETrigger actionBEFORE
3AFTERAFTER
4INSTEAD OFINSTEAD OF
5INSERTTrigger operationINSERT
6UPDATE [OF col, …]UPDATE [OF col, …]
7DELETEDELETE
8REFERENCING NEW [AS] new OLD [AS] oldREFERENCING NEW [AS] new OLD [AS] old
9FOR EACH ROWFOR EACH ROW
10FOR EACH STATEMENTFOR EACH STATEMENT
11MODE DB2SQLMode in DB2Removed
12WHEN (condition)Trigger conditionWHEN (condition)
13BEGIN ATOMICBEGIN
14NEW correlation name:NEW correlation name
15NEW. reference can be omitted in assignment:NEW. must be specified 
16OLD.columnOLD correlation name:OLD.column
17Optional BEGIN … ENDIf one statement in the bodyBEGIN … END required
18No 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:

DB2Oracle
1ALLOCATE CURSOR FOR RESULT SETProcess result set in SQL procedureRemoved, linked with SYS_REFCURSOR
2ASSOCIATE RESULT SET LOCATOR
3CALL proc(param, …)Call a procedureproc(param, …)
4CASE WHEN THEN ELSE END CASECASE statementCASE WHEN THEN ELSE END CASE
5DECLARE var datatype DEFAULT valueVariable declarationvar datatype DEFAULT value
DECLARE var, var2, … datatypevar datatypevar2 datatype; …
6DECLARE cur CURSOR FOR sqlCursor declarationCURSOR cur IS sql
DECLARE cur CURSOR WITH HOLD ...Remains open on commitWITH HOLD keyword removed
DECLARE cur CURSOR WITH RETURN FOR sqlResult setcur OUT SYS_REFCURSOR
7DECLARE CONTINUE HANDLER FOR NOT FOUNDNOT FOUND handlerEXCEPTION WHEN NO_DATA_FOUND, 
cur%NOTFOUND, SQL%ROWCOUNT
DECLARE type HANDLER FOR SQLEXCEPTIONSQL exception handlerEXCEPTION WHEN OTHERS
8DECLARE name CONDITION FOR SQLSTATE '23505'Unique key violationEXCEPTION WHEN DUP_VAL_ON_INDEX
DECLARE name CONDITION FOR SQLSTATE '02000'No data foundEXCEPTION WHEN NO_DATA_FOUND, 
cur%NOTFOUND, SQL%ROWCOUNT
9DECLARE SQLCODE INTEGER DEFAULT 0SQLCODE declarationDeclaration is not required, removed
DECLARE SQLSTATE CHAR(5)SQLSTATE declaration
10FETCH [FROM] cur INTO …Fetch a cursorFETCH cur INTO …
11FOR var AS SELECT … DO stmts 
END FOR;
For each row loopFOR var IN (SELECT …) LOOP stmts 
END LOOP;
FOR var AS cur CURSOR FOR 
SELECT … DO stmts END FOR;
12GET DIAGNOSTICS EXCEPTION 
var = MESSAGE_TEXT
Get the error messagevar := SQLERRM
GET DIAGNOSTICS var = ROW_COUNTGet affected rowsvar := SQL%ROWCOUNT
GET DIAGNOSTICS var = DB2_RETURN_STATUSProcedure call statusProcedure OUT parameter
13IF THEN ELSEIF ELSE END IFIF statementIF THEN ELSIF ELSE END IF 
IF (SELECT COUNT(*) ... ) > 0 THENSELECT COUNT(*) INTO cnt ...; IF cnt > 0 THEN
14label:Label declaration<<label>>
15LEAVE label;Leave a loopEXIT label;
16LOOP stmts END LOOP;A loop statementLOOP stmts END LOOP;
17REPEAT stmts UNTIL condition END REPEAT;Conditional loopLOOP stmts EXIT WHEN condition; END LOOP;
18RESIGNAL;Resignal the exceptionRAISE;
19SET v1 = valueAssignment statementv1 := value
SET v1 = valuev2 = value2, …v1 := valuev2 := value2; …
SET (v1, v2, …) = (value, value2, …)v1 := valuev2 := value2; …
SET (v1, v2, …) = (SELECT c1, c2, …)SELECT c1, c2, … INTO v1, v2, …
20SIGNAL SQLSTATE 'num' ('text')Raise an exceptionRAISE_APPLICATION_ERROR(-num, 'text')
21VALUES c1, … INTO v1, …Assignment statementv1 := c1; …
22WHILE condition DO sql END WHILEA loop statementWHILE condition LOOP sql END LOOP;

SQL Statements

Converting SQL statements from IBM DB2 to Oracle:

DB2Oracle
1COMMENT ON tab (col IS 'text')Comment on column (z/OS)COMMENT ON COLUMN tab.col IS 'text'
2CREATE AUXILIARY TABLE nameCreate a LOB table (z/OS)Commented
3CREATE DATABASE nameCreate a databaseCommented
4CREATE STOGROUP nameCreate a storage group (z/OS)Commented
5DECLARE GLOBAL TEMPORARY TABLECreate a temporary tableCREATE GLOBAL TEMPORARY TABLE
6DROP DATABASE nameDrop a databaseCommented
7DROP STOGROUP nameDrop a storage group (z/OS)Commented
8SET CURRENT PATH = listSet the current pathRemoved
9SET CURRENT SCHEMA = nameSet the current schemaALTER SESSION SET CURRENT_SCHEMA name
10UPDATE t1 SET (c1, c2, …) = (v1, v2, …)Update statementUPDATE t1 SET c1 = v1, c2 = v2, …
11VALUES c1, …Single-row result setSELECT c1, … FROM dual
VALUES c1, … INTO v1, …Assignment statementv1 := c1; …

CLP Commands

Converting Command Line Processor (CLP) commands from IBM DB2 to Oracle:

DB2Oracle
1EXPORT TO file OF DEL select_stmtExport to delimited ASCII fileSPOOL file; select_stmt; SPOOL OFF;

Error Codes and Messagges

Mapping error codes and messages from DB2 to Oracle:

DB2Oracle
1SQLSTATE '02000', SQLCODE 100Row not foundcur%NOTFOUND, SQL%NOTFOUND, SQLCODE 100
2SQLSTATE '23505'Unique constraint violationORA-00001 error, DUP_VAL_ON_INDEX exception
3SQLSTATE '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

728x90
그리드형

'DB' 카테고리의 다른 글

SQLite 공유 캐시 모드  (0) 2017.08.24
[Oracle] 시간 타입 컬럼 시분초 형태로 결과보기 (nls_date)  (0) 2017.06.28
(SQLite) SQLite with GPU  (0) 2017.04.30
(SQLITE) 참고 (링크)  (0) 2017.04.17
(SQLITE) Visual Studio  (0) 2017.04.17
Posted by kjun
,