How to achieve Oracle related functions in DB2 (4)
Author: CCBZZP
In reality, everyone may often encounter some of the functions of Oracle in DB2,
Here I simply summarize it, there are many ways to achieve a function, there is not all listed here,
Welcome everyone to continue, in order to share, discuss together, close in common! (The following mainly Oracle
8i, 9i and db2 7.x as an example).
1. How to implement the Oracle and DB2 of the pagination display
Oracle can achieve this:
SQL> SELECT ROWNUM, * from bsempms where rownum> = 5 and rownum <= 100;
DB2 can be implemented like this:
Select * from (select row_number () over () AS A, DB2ADMIN.BSEMPMS. * from
DB2ADMIN.BSEMPMS) AS TEMP WHERE A> = 5 and A <= 100;
2. Writing of Oracle and DB2 using other tables creation tables
Oracle can achieve this:
SQL> CREATE TABLE A AS SELECT * from B;
DB2 can be implemented like this:
Create Table a Like B;
3. How to change the Oracle and DB2 of the user password
Oracle can achieve this:
SQL> ALTER USER USER123 Identified by Password_new;
DB2 can be implemented like this:
Connect to DBName User DB2Admin Using Oldpassw New NewPassw Confirm Newpassw;
4. How to increase the user's Oracle and DB2
Oracle can achieve this:
SQL> CREATE User User123 Identified by Password_new;
DB2 can be implemented like this:
Add user:
"Start / Setting / Control Panel / User" Add a username (Example: db2admin)
assign permissions:
Grant DBADM on Database to User User Name
5. Writing of Oracle and DB2 of two result sets
Oracle can achieve this:
SQL> SELECT * from bsempms_old minus select * from bsempms_new;
DB2 can be implemented like this:
Select * from bsempms_old except select * from bsempms_new;
Select * from bsempms_old except all selection * from bsempms_new;
6. Oracle and DB2 of two result sets
Oracle can achieve this:
SQL> Select * from bsempms_old intersect select * from bsempms_new;
SQL> SELECT * from bsempms_old union select * from bsempms_new;
SQL> SELECT * from bsempms_old union all selection * from bsempms_new;
DB2 can be implemented like this:
SELECT * from DB2Admin.bsempms
Union
SELECT * from DB2Admin.bsempms;
SELECT * from DB2Admin.bsempms
Union all
SELECT * from DB2Admin.bsempms;
7. How to find the name of Oracle and DB2 of the name of the primary key field of the database table
Oracle can achieve this:
SQL> Select * from user_constraints where constraint_type = 'p' and table_name = 'table_name'; DB2 can be implemented in this way:
Select colnames from syscat.indexes where tabname = 'Table_name';
