oracle and a number of similarities and differences between db2

  1, Table space for indexes of different 

Oralce:

  Create TABLE T1 …………. IN DATA_TA 

  Create Index …….. ON T1 …… IN INDX_TS 

DB2:

  Create TABLE T1 …….. IN DATA_TS INDEX IN INDX_TS 

  Create INDEX ….. ON T1 

  2, RowID 

Oracle:

  Oracle has RowID, it is only by the database, in the process, get 

DB2:

  DB2 should be the relative value of this procedure can not be obtained 

  3, a number of conversions 

Oracle:

  Oracle support some transformation, such as: 

  Create table t1 (c1 int); 

  Select * from t1 where c1 ='1 ' 

  Select 'abc' | | 2 from t1 

DB2:

  DB2 do not support the conversion of some, such as: 

  Create table t1 (c1 int); 

  Select * from t1 where c1 = CAST ('1 'AS INT) 

  Select 'abc' | | CHAR (2) from t1 

  4, as a function of the value of the default values in Table 

Oracle:

  Oracle support to function as the value of the default values in the table, such as: 

  Create table t1 (C1 Number DEFAULT FN () NOT NULL, 

  C2 DATE DEFAULT NOT NULL SYSDATE 

)

DB2:

  DB2 does not support the value of the function as a form of default values, such as: 

  Create table t1 (C1 INT NOT NULL, 

  C2 TIMESTAMP DEFAULT CURRENT TIMESTAMP 

)

  This is not allowed 

  5, some built-in function 

Oracle

DB2

NVL

COALESCE

INSTR

LOCATE

DECODE

CASE

LENGTH

LENGTH

  TO_CAHR () 

  CHAR (), INTEGER (), TO_CHAR () 

  TO_DATE () 

  CHAR (date, FN) 

  TO_DATE () 

  6, the common synonyms (Public Synonyms) 

Oracle:

  Oracle to support the common synonyms 

DB2:

  DB2 is not a synonym for public support 

  7, Date / Time 

Oracle

DB2

SYSDATE

  CURRENT DATE 

  CURRENT TIMESTAMP 

  DT1 +2 

  Add_months (DT1, 5) 

  DT1 +2 days 

  DT2 +5 months 

  DT1 +3 years 

DT1-DT2

  TIMESTAMODIFF (k, Char (TS1-TS2) 

  To_date (DT1, 'MM / DD / YYYY') 

  Char (DT1, USA) 

  Char (TM1, ISO) 

  8, in connection 

Oracle

DB2

  Select A.lastname, A_id, B.name From emp A, Customer B 

  where A.id (+) = B.sales_rep_id; 

  Select A.lastname, A_id, B.name From emp A, RIGHT OUTER JOIN Customer B 

  where A.id = B.sales_rep_id; 

  Select A.lastname, A_id, B.name From emp A, Customer B 

  where A.id = B.sales_rep_id (+); 

  Select A.lastname, A_id, B.name From emp A, LEFT OUTER JOIN Customer B 

  where A.id = B.sales_rep_id; 

  Select A.lastname, A_id, B.name From emp A, Customer B 

  where A.id (+) = B.sales_rep_id (+); 

  Select A.lastname, A_id, B.name From emp A, Full OUTER JOIN Customer B 

  where A.id = B.sales_rep_id; 

  Now, Oracle 9i also support this ANSI join syntax 

  9, string and null the merger 

  Select 'abc' | | c1 from T1 

  C1 is null 

  Result: 

  Oracle: abc 

  DB2: null 

  To be 'abc', DB2 in the need for such treatment 

  Select 'abc' | | coalesce (c1,'') from T1 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: