Skills and experience in implementing Oracle functions in DB2 Databases

This article will share with you some of the practical operation skills and experiences you have learned in implementing Oracle in DB2 databases, in practical applications, you may often encounter how to implement certain Oracle functions in DB2. Here I will summarize briefly.

There may be many ways to implement a function, but not all of them are listed here. You are welcome to continue to share them with us for further discussion and further steps! (the following mainly uses Oracle8I and DB2 7. X ).

1. How to Write Oracle and DB2 with the first n records in a table

Oracle can achieve this: Select * from user. bsempms where rownum <= n;

DB2 can achieve this: Select * from db2admin. bsempms fetch first n rows only;

You can also use row_number () over () to implement it in the DB2 database;

2. How to get the writing of Oracle and DB2 on the current date

Oracle can achieve this: Select sysdate from dual;

DB2 can achieve this: Select current timestamp from sysibm. sysdummy1;

3. How to connect Oracle and DB2 Fields

Oracle can achieve this: Select emp_no | emp_nam from bsempms; Select concat (emp_no, emp_nam) from bsempms;

DB2 can achieve this: Select emp_no | emp_nam from db2admin. bsempms; select emp_no concat emp_nam from db2admin. bsempms;

4. How to query the Oracle and DB2 statements of the Table Structure

Oracle can achieve this: Select sysdate from dual;

DB2 can achieve this: Select current timestamp from sysibm. sysdummy1;

5. Writing of Oracle and DB2 for INNER JOIN

Oracle can achieve this: Select a. * from bsempms a, bsdptms B where a. dpt_no = B. dpt_no;

DB2 database implementation can be like this: Select * from db2admin. bsempms inner join db2admin. bsdptms on db2admin. bsempms. dpt_no = db2admin. bsdptms. dpt_no;

6. Writing of Oracle and DB2 for external connections (right Outer Join, left Outer Join, full outer join, and combination Outer Join)

Oracle can achieve this: Select. * from bsempms a, bsdptms B where. dpt_no = B. dpt_no (+); Select. * from bsempms a, bsdptms B wherea. dpt_no (+) = B. dpt_no;

DB2 can achieve this ﹕

Select * from db2admin. bsempms right outer join db2admin. bsdptms on db2admin. bsempms. dpt_no = db2admin. bsdptms. dpt_no; Select * from db2admin. bsempms left outer join db2admin. bsdptms on db2admin. bsempms. dpt_no = db2admin. bsdptms. dpt_no; Select * from db2admin. bsempms full outer join db2admin. bsdptms on db2admin. bsempms. dpt_no = db2admin. bsdptms. dpt_no;