How to achieve Oracle in DB2 (4) Author: CCBZZP In realistic application, you may often encounter some of the functions of Oracle in DB2, here I simply summarize, implement a function There are many ways, there is not all listed here. You are welcome to continue, so you can share, discuss together, and close in the same! (The following mainly in Oracle8i, 9i, and DB2 7.x as an example).
1. How to achieve paging displayed Oracle and DB2 Oracle can implement this: SQL> Select Rownum, * from bsempms where rownum> = 5 and rown = 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. Oracle and DB2 of Oracle and DB2 using other tables Oracle can be implemented like this: SQL> CREATE Table a as select * from b; DB2 can be implemented in this way: Create Table A Like B; 3. How to change the user password Oracle and DB2 Oracle can implement this: SQL> ALTER USER USER123 Identified by password_new; DB2 can be implemented: Connect to DBNAME DB2ADMINUSING OLDPASSW; 4. How to increase the user's Oracle and DB2 Oracle Oracle can implement this: SQL> CREATE USER USER123 Identified by password_new; DB2 can be implemented like this: Add User: "Start / Setting / Control Panel / User Add a User Name (Example: DB2Admin) Assignment Permissions: Grant DBADM on Database to User User Name 5. Oracle and DB2 of Oracle and DB2 of Two Result Sets Oracle can be implemented: SQL> SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW; DB2 can be achieved: SELECT * FROM BSEMPMS_OLD EXCEPT SELECT * FROM BSEMPMS_NEW; SELECT * FROM BSEMPMS_OLD EXCEPT ALL SELECT * FROM BSEMPMS_NEW; O 6 plus two result sets a function of the cross. racle and DB2 wording Oracle can be achieved: 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 SELECT * FROM BSEMPMS_NEW; DB2 can do Implementation: 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 the primary key field of the database table Oracle and DB2 Oracle This is implemented: SQL> Select * from user_constraints where constraint_type = 'p' and table_name = 'table_name'; DB2 can be implemented like this: select colnames from syscat.indexes where tabname = 'table_name'; to be renewed ...
