Oracle VS DB2

1! (The following mainly takes Oracle8i and DB2 7.x as an example).

1. How to take a table before the N-record Oracle and DB2 Writing Oracle can achieve this: select * from user.bsempms where rownum <= n; DB2 can be implemented: select * from db2admin.bsempms fetch first n rows online; Alternatively, Row_Number () Over () can also be implemented; 2. How to get the current date Oracle and DB2 Oracle can implement this: Select sysdate from dual; DB2 can be implemented like this: Select Current TimeStamp from sysibm.sdummy1; 3 how to connect field Oracle and DB2 wording Oracle can be achieved: Select emp_no || emp_nam from bsempms; Select concat (emp_no, emp_nam) from bsempms; DB2 can be achieved: Select emp_no || emp_nam from db2admin.bsempms; select emp_no Concat Emp_nam from db2admin.bsempms; 4. It should be to take the current time Oracle can be implemented like this: SELECT SYSDATE AUAL; DB2 can be implemented in this way: Select Current TimeStamp from sysibm.sysdummy1; 5. Writing of Oracle and DB2 connecting Inner Join Oracle can be achieved: Select a * from bsempms a, bsdptms b where a.dpt_no = b.dpt_no; DB2 can be achieved:. Select * from db2admin.bsempms inner join db2admin.bsdptms on db2admin.bsempms.dpt_no = db2admin.bsdptms .dpt_no; 6. Oracle and DB2 of Oracle and DB2 (right) Oracle can be implemented like this in Oracle: SELECT A. * from bsempms A, BSDPTMS B where a.dpt_no = B.DPT_NO ( ); SELECT A. * from bsempms A, bsdptms b wherea.dpt_no ( ) = b.dpt_no; DB2 can be achieved: 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; 7 how to execute SQL script file. Oracle and DB2 Writing Oracle can implement this: SQL> @ $ path / filename.sql; DB2 can be implemented like this: DB2 -TVF $ PATH / FileName each line in the file;

21. How to view the version of Oracle and DB2 in the database Oracle can be implemented like this: SQL> Connect System / Manager124 @ TEST; connected. SQL> SELECT * FROM V $ Version;

Banner ------------------------------------- --------------- Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionPL / SQL Release 9.2.0.1.0 - ProductionCore 9.2.0.1.0 ProductionTns for 32-Bit Windows: Version 9.2 .0.1.0 - ProductionNLSRTL Version 9.2.0.1.0 - ProductionDB2 can be achieved: / SQLLIB / BIN> db2levelDB210851 Instance "DB2" uses DB2 code release "SQL07020" with level identifier "03010105" and informational tokens: in the command window to perform db2levelD "DB2 V7.1.0.40", "N010415" and "WR21254" .2. How to quickly empty a big table of Oracle and DB2 Oracle can implement this: SQL> TRUNCATE TABLE TABLE_NAME; DB2 can be implemented like this: Alter Table Table_name active not logged initially with empty table; 3 how to view usage table space of the Oracle and DB2 wording Oracle can be achieved:. SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m, to_char (100 * sum_free_m / sum_m, '99 .99 ') || '%' AS

pct_free FROM (SELECT tablespace_name, sum (bytes) / 1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name), (SELECT tablespace_name AS fs_ts_name, max (bytes) / 1024/1024 AS max_m, count (blocks) AS count_blocks,

sum (bytes / 1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name) WHERE tablespace_name = fs_ts_name DB2 can be achieved: list tablespace containers for your tablespace number show detail; 4 how to remove portions of the date from a time point. The commonly used Oracle and DB2 Oracle can be implemented like this: 1>. Writing of the time point: select to_char (sysdate, 'yyyy') from dual; 2>. Write a time point: Select to_char (sysdate , 'Mm') from Dual; 3>. Write actions at the time of time: select to_char (sysdate, 'dd') from dual; 4>. Writing at the time point: select to_char (sysdate, 'hh24 " ) From dual; 5>. Writing of time points: select to_char (sysdate, 'mi') from dual; 6>. Write actions at the time point: select to_char (sysdate, 'ss') from dual; 7>. Write 9i or more of the time point) SELECT SUBSTR (SYSTIMESTAMP, 20, 6) from dual; 8>. Date of Take the time point: Select Trunc (sysdate) from dual; 9>. Take time point The time of the time: select to_char (sysdate, 'hh24: mi: ss') from dual; 10>. Date, time form becoming a character SELECT TO_CHAR (SYSDATE) from Dual; 11>. Convert cell string to date or Time form: SELECT TO_DATE ('2003/08/01') from Dual; 12>. Return parameters of the week's desk: select to_char (sysdate, 'd') from dual; 13>. Return parameter for one year The next few days: select to_char (sysdate, 'ddd') from dual; 14>. Returns the number of seconds between the time values ​​specified in the midnight and parameters: select to_char (sysdate, 'sssss) from du Al; 15>. Return parameters for the first few weeks of one year: select to_char (sysdate, 'ww') from dual;

DB2 can achieve this: 1>. Writing of the time of the time point: select year (current timestamp) from sysibm.system.sdummy1; 2>. Writing of the month of time point: Select Month (Current TimeStamp) from sysibm.systemsdummy1; 3 >. Take the time of time: select day (current timestamp) from sysibm.system s d 4 4 写 法 法 法 法 法 法 法 法 法 法 法 法 法 法 法 法 法 法 法 法Division: SELECT Minute (Current TimeStamp) from sysibm.sdummy1; 6>. Writing of the second time: Select Second (current timestamp) from sysibm.system.sdummy1; 7>. Take the time point milliseconds: select microsecond (current timestamp) from sysibm.sysdummy1; 8>. Date of taking the time point: Select Date (current timestamp) from sysibm.sdummy1; 9>. Time to take the time point of the time: SELECT TIME (Current TimeStamp) from Sysibm . .SYSDUMMY1; 10> date, the time pattern of morphological characters becomes: SELECT char (current date) FROM SYSIBM.SYSDUMMY1; SELECT char (current time) FROM SYSIBM.SYSDUMMY1; SELECT char (current date 12 hours) FROM SYSIBM.SYSDUMMY1 11>. Convert the string into a date or time form: SELECT TIMESTAMP ('2002-10-20-12.00.00.000000') from sysibm.sysdummy1; select timestamp ('2002-10-20 12:00:00') From sysibm.sdummy1; select date ('2002-10-20') from sysibm.sysdummy1; select da TE ('10 / 20/2002 ') from sysibm.sysdummy1; select time ('12: 00: 00') from sysibm.sysdummy1; select time ('12.00.00 ') from system.system.sdummy1; 12>. Return parameters the wording of the week: SELECT DAYNAME (current timestamp) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFWEEK (current timestamp) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFWEEK_ISO (current timestamp) FROM SYSIBM.SYSDUMMY1; 13> returns parameters in the year. A few days of writing: select dayofyear (current timestamp) from sysibm.sysdummy1; 14>. Returns the number of seconds between the time values ​​specified in the midnight and parameters: select midnight_seconds (current timestamp) form system.system.sdummy1; 15>. Return to the first few weeks of the year: Select Week (Current TimeStamp) Form System.SysdumMy1

31. How to check how many database instances Oracle and DB2 Oracle can implement this: SQL> SELECT * FROM V $ INSTANCE; DB2 can be implemented like this: DB2ilist2 is executed in the command window. How to query how many tables in the database? Oracle can implement this: SQL> Select * from all_tables; DB2 can be implemented in this way: SELECT * from syscat.tables; 3. How to know the situation of the chain Oracle and DB2 Oracle can be implemented: SQL > Select s.SID session_id, s.username, decode (lmode, 0, 'none, 1,' null ', 2,' row-s (sx) ', 3,' row-x (sx) ', 4 , 'Share', 5, 'S / ROW-X (SSX)', 6, 'Exclusive', To_Char (LMODE)) MODE_HELD, DECODE (Request, 0, 'None', 1, 'Null', 2, ' Row-S (SS) ', 3,' ROW-X (SX) ', 4,' Share ', 5,' S / Row-x (SSX) ', 6,' Exclusive ', TO_CHAR (Request) Mode_Requested , O.owner || '.' || O.Object_name || '(' || Object_Type || ')', S.Type Lock_Type, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 from V $ LOCK L, Sys.dba_Objects O, V $ session s where l.sid = s.SID and L.ID1 = O.Object_iddb2 can be implemented like this: Before you perform your stored procedure, execute the command to open the lock DB2 Update Monisor Switch Using Lock ON; then execute your stored procedure, use the command db2 get snapshot for locks on YourDaTDabasename during the execution of the stored procedure; you can see you lock 4. How to unlock the locked table in Oracle and DB2 Writing Oracle can achieve this: SQL> Alter System Kill Session 'SID, Service #'; DB2 can be implemented like this: DB2 forward application all; db2 terminate; 5. Test SQL statement to perform the time of Oracle and DB2 Oracle Oracle, Oracle, can be implemented: SQL> Set Timing ON; SQL> Select * from Tablename; DB2 can be implemented in this way: DB2BATCH -D library name -f contains file names of the SQL statement; 6. How to configure sequence's Oracle and DB2 Oracle Oracle to implement this: Buick Sequence SEQ_CUSTID CREATE SEQUENCE SEQ_CUSTID START 1 INCREMEMT BY 1;

When building a table: CREATE TABLINT NOT NULL, ...} Insert When: INSERT INTO TABLE CUST VALUES (seq_cust.nextval, ...) DB2 can be implemented: Identity field attribute usage: CREATE TABLE CUST_ID SMALLINT NOT Null Generated Always As Indentity (Start With 1 Increment By 1) Insert: Insert Into Table Cust (Cust_ID, ...) VALUES (Default, ...)

41. How to implement pagination display Oracle and DB2 Oracle can achieve this: SQL> Select Rownum, * from bsempms where rownum> = 5 and rown <= 100; DB2 can be implemented: 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; 6 plus two result sets mutual function Oracle and DB2 wording. Oracle can achieve this: SQL> SELECT * FROM BSEM PMS_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 be achieved: SELECT * FROM DB2ADMIN.BSEMPMSUNION SELECT * FROM DB2ADMIN.BSEMPMS; . SELECT * FROM DB2ADMIN.BSEMPMSUNION ALLSELECT * FROM DB2ADMIN.BSEMPMS; 7 Oracle primary key field names of the database table and how to find DB2 wording Oracle can be achieved: 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';

This is a wrong: 4. Oracle and DB2 of how to query the structure Oracle Oracle can be implemented like this: select sysdate from dual; DB2 can be implemented like this: Select Current TimeStamp from sysibm.sysdummy1; 2. Recommended landlord to introduce DB2 entry knowledge Such as: http://www.itpub.net/showthread.php? S = & threadId = 199368

2. How to get the current date Oracle and DB2 Oracle can implement this: select sysdate from dual; DB2 can be implemented in this way: select current timestamp from sysibm.sysdummy1;