Oracle vs DB2

1
! (The following mainly uses Oracle
8i and DB2 7.x ).

1. How to obtain the explain method of Oracle and DB2 In the first N records of 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;
In addition, you can use row_number () over () to implement it;
2. How to obtain the explain method of Oracle and DB2 before the date
Oracle can achieve this ﹕
Select sysdate from dual;
DB2 can achieve this ﹕
Select current timestamp from sysibm. sysdummy1;
3. How to explain the explain method of Oracle and DB2 connected to 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. It should be from the previous time.
Oracle can achieve this ﹕
Select sysdate from dual;
DB2 can achieve this ﹕
Select current timestamp from sysibm. sysdummy1;
5. internal memory join Oracle and DB2 memory method
Oracle can achieve this ﹕
Select a. * From bsempms A, bsdptms B where a. dpt_no = B. dpt_no;
DB2 can achieve this ﹕
Select * From db2admin. bsempms inner join db2admin. bsdptms
On db2admin. bsempms. dpt_no = db2admin. bsdptms. dpt_no;
6. Explain method of Oracle and DB2 connected to external database (right Outer Join, left Outer Join, full outer join, and combination Outer Join)
Oracle can achieve this ﹕
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 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;
7. How to parse the statement of Oracle and DB2 in this SQL File
Oracle can achieve this ﹕
SQL> @ $ path/filename. SQL;
DB2 can achieve this ﹕
Each row in the DB2-tvf $ path/filename file takes ;? Tail

2
1. How to view data analytics versions of Oracle and DB2
Oracle can achieve this ﹕
SQL> connect system/manager124 @ test;
Already exist.
SQL> select * from V $ version;

Banner
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0-Production
PL/SQL release 9.2.0.1.0-Production
Core 9.2.0.1.0 Production
TNS for 32-bit windows: Version 9.2.0.1.0-Production
Nlsrtl version 9.2.0.1.0-Production

DB2 can achieve this ﹕
Execute db2level in the Command window
D:/sqllib/bin> db2level
Db21000051 instance "DB2" uses DB2 code release "sql07020" with level identifier
"03010105" and informational tokens "DB2 v7.1.0.40", "n038515" and "wr21254 ".
2. How to quickly clear the explain method of Oracle and DB2 for a large table
Oracle can achieve this ﹕
SQL> truncate table table_name;
DB2 can achieve this ﹕
Alter table table_name active not logged initially with empty table;
3. How to view the partition space using the partition method of Oracle and DB2
Oracle can achieve this ﹕
Select tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m, to_char (100 * sum_free_m/sum_m, '99. 99') | '%'

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 achieve this ﹕
List tablespace containers for your table empty space show detail;
4. How to extract the common Oracle and DB2 explain methods for each part of a date from a time point
Oracle can achieve this ﹕
1>. The expiration method of the year at the time point:
Select to_char (sysdate, 'yyyy') from dual;
2>. Use the hour method of the month at the time point:
Select to_char (sysdate, 'mm') from dual;
3>. Method of getting the time point's Day:
Select to_char (sysdate, 'dd') from dual;
4>. Method for retrieving time points:
Select to_char (sysdate, 'hh24') from dual;
5>. Calculate the hour points:
Select to_char (sysdate, 'mi') from dual;
6>. The second interval method of the time point:
Select to_char (sysdate, 'ss') from dual;
7>. The interval of milliseconds from the time point is greater than 9i)
Select substr (systimestamp, 20, 6) from dual;
8>. The expiration method of the date at the time point:
Select trunc (sysdate) from dual;
9>. Method for retrieving time points:
Select to_char (sysdate, 'hh24: MI: ss') from dual;
10>. The date and time form changes to the character form
Select to_char (sysdate) from dual;
11>. Convert the string to a date or time form:
Select to_date ('2014/1/01') from dual;
12>. Return the week number of the week:
Select to_char (sysdate, 'D') from dual;
13>. Return the day of the year using the statement:
Select to_char (sysdate, 'ddd ') from dual;
14>. Return the second method of the time value specified in midnight and midnight:
Select to_char (sysdate, 'ssss') from dual;
15>. Return the week number of the year in the week method:
Select to_char (sysdate, 'ww ') from dual;

DB2 can achieve this ﹕
1>. The expiration method of the year at the time point:
Select Year (current timestamp) from sysibm. sysdummy1;
2>. Use the hour method of the month at the time point:
Select month (current timestamp) from sysibm. sysdummy1;
3>. Method of getting the time point's Day:
Select Day (current timestamp) from sysibm. sysdummy1;
4>. Method for retrieving time points:
Select hour (current timestamp) from sysibm. sysdummy1;
5>. Calculate the hour points:
Select minute (current timestamp) from sysibm. sysdummy1;
6>. The second interval method of the time point:
Select second (current timestamp) from sysibm. sysdummy1;
7>. millisecond lag method for time points:
Select microsecond (current timestamp) from sysibm. sysdummy1;
8>. The expiration method of the date at the time point:
Select date (current timestamp) from sysibm. sysdummy1;
9>. Method for retrieving time points:
Select time (current timestamp) from sysibm. sysdummy1;
10>. Date, time form changes to character form:
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 to a date or time form:
Select timestamp ('1970-10-20-12.00.00.000000') from sysibm. sysdummy1;
Select timestamp ('2014-10-20 12:00:00 ') from sysibm. sysdummy1;
Select date ('1970-10-20 ') from sysibm. sysdummy1;
Select date ('2014/1/123') from sysibm. sysdummy1;
Select time ('12: 00: 00') from sysibm. sysdummy1;
Select time ('12. 00.00 ') from sysibm. sysdummy1;
12>. Return the week number 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>. Return the day of the year using the statement:
Select dayofyear (current timestamp) from sysibm. sysdummy1;
14>. Return the second method of the time value specified in midnight and midnight:
Select midnight_seconds (current timestamp) form sysibm. sysdummy1;
15>. Return the week number of the year in the week method:
Select Week (current timestamp) form sysibm. sysdummy1

3
1. How to check how many data examples are stored in Oracle and DB2 statements?
Oracle can achieve this ﹕
SQL> select * from V $ instance;
DB2 can achieve this ﹕
Run db2ilist in the Command window.
2. How to check the number of tables in Oracle and DB2 Databases
Oracle can achieve this ﹕
SQL> select * From all_tables;
DB2 can achieve this ﹕
In the Command Center
Select * From syscat. tables;
3. How to know the metadata of a table? Explain methods of Oracle and DB2
Oracle can achieve this ﹕
SQL> select S. sid session_id, S. username, decode (lmode, 0, 'none', 1, 'null', 2, 'row-s (SS) ', 3, 'row-X (SX )', 4, 'share', 5,'s/row-X (ssx) ', 6, 'clusive', 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, 'clusive', to_char (request) mode_requested, O. owner | '. '| O. object_name | '(' | O. 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_id
DB2 can achieve this ﹕
Before you execute your stored procedure, execute the command to open the monitoring light of the lock.
DB2 update monisor switches using lock on;
Then execute your stored procedure and use the command during the execution of the stored procedure
DB2 get snapshot for locks on yourdatdabasename;
You can see the lock.
4. How do I parse the explain method of Oracle and DB2 for the tables that exist?
Oracle can achieve this ﹕
SQL> alter system kill session 'sid, service #';
DB2 can achieve this ﹕
DB2 force application all;
DB2 terminate;
5. Explain the Oracle and DB2 statement of the time used by the SQL statement.
Oracle can achieve this ﹕
SQL> set timing on;
SQL> select * From tablename;
DB2 can achieve this ﹕
Db2batch-D database name-F contains the name of the SQL statement file;
6. how to configure sequence's explain Method for Oracle and DB2
Oracle can achieve this ﹕
Create sequence seq_custid
Create sequence seq_custid start 1 incrememt by 1;
When creating a table:
Create Table cust
{Cust_id smallint not null,
...}
Insert:
Insert into Table cust
Values (seq_cust.nextval ,...)
DB2 can achieve this ﹕
Identity field attribute usage:
Create Table
Cust_id smallint not null generated always as indentity (start
1 increment by 1)
Insert:
Insert into Table Cust (cust_id ,...)
Values (default ,...)

4
1. How to Implement the explain methods of Oracle and DB2
Oracle can achieve this ﹕
SQL> select rownum, * From bsempms where rownum> = 5 and rownum <= 100;
DB2 can achieve this ﹕
Select * from (select row_number () over () as a, db2admin. bsempms. * from
Db2admin. bsempms) as temp where a> = 5 and a <= 100;
2. Explain method of Oracle and DB2 used to create tables from other tables
Oracle can achieve this ﹕
SQL> Create Table A as select * from B;
DB2 can achieve this ﹕
Create Table A like B;
3. How can I change the Oracle and DB2 encryption methods using dense passwords?
Oracle can achieve this ﹕
SQL> alter user user123 identified by password_new;
DB2 can achieve this ﹕
Connect to dbname user db2admin using oldpassw new newpassw confirm newpassw;
4. How to add the explain method of Oracle and DB2 using explain
Oracle can achieve this ﹕
SQL> create user user123 identified by password_new;
DB2 can achieve this ﹕
Add User:
"Start/set/control panel/user" to add a user name (for example, db2admin)
Assign permissions:
Grant dbadm on database to User Username
5. Comparison of Oracle and DB2 functions for the two result sets
Oracle can achieve this ﹕
SQL> select * From bsempms_old minus select * From bsempms_new;
DB2 can achieve this ﹕
Select * From bsempms_old distinct T select * From bsempms_new;
Select * From bsempms_old before t all select * From bsempms_new;
6. Explain method of Oracle and DB2 for adding two result sets to each other
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 select * From bsempms_new;
DB2 can achieve 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 Oracle and DB2 partition method for 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 achieve this ﹕
Select colnames from syscat. indexes where tabname = 'table _ name ';

This is incorrect:
4. How to query the explain method of Oracle and DB2 in the structure of the explain table
Oracle can achieve this ﹕
Select sysdate from dual;
DB2 can achieve this ﹕
Select current timestamp from sysibm. sysdummy1;

2. We recommend that you introduce the basic knowledge of DB2, for example:
Http://www.itpub.net/showthread.php? S = & threadid = 199368.

2. How to obtain the explain method of Oracle and DB2 before the date
Oracle can achieve this ﹕
Select sysdate from dual;
DB2 can achieve this ﹕
Select current timestamp from sysibm. sysdummy1;