What is the difference between oracle and db2

Differences: 1. Oracle supports the "create or replace" statement, but DB2 does not support it; 2. The DATE type in ORACLE has hours, minutes and seconds, while DATE under DB2 is only year, month and day; 3. SUBSTR ( ) The second parameter of the function can take the value 0, but not in DB2.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Oracle and DB2 are two different databases.

Oracle:

Oracle is a relational database management system from Oracle Corporation.

ORACLE database system is a set of software products with distributed database as the core provided by the American ORACLE company (Oracle). It is currently the most popular client/server (CLIENT/SERVER) or B/S architecture. One of the databases. For example, SilverStream is a kind of middleware based on database. ORACLE database is currently the most widely used database management system in the world. As a general database system, it has complete data management functions; as a relational database, it is a complete relational product; as a distributed database, it realizes distribution processing function.

DB2:

stands for IBM DB2. It is a relational database management system developed by IBM in the United States. Its main operating environments are UNIX (including IBM's own AIX), Linux, IBM i (formerly known as OS/400), z/OS, and Windows server versions.

DB2 is mainly used in large-scale application systems. It has good scalability, can support everything from mainframe to single-user environments, and can be used on all common server operating system platforms. DB2 provides a high level of data utilization, integrity, security, recoverability, and execution capabilities for small to large-scale applications, with platform-independent basic functions and SQL commands. DB2 adopts data classification technology, which enables mainframe data to be easily downloaded to a LAN database server, allowing client/server users and LAN-based applications to access mainframe data and making database localization and remote connections transparent. DB2 is known for having a very complete query optimizer, whose external connections improve query performance and support multi-tasking parallel queries. DB2 has excellent network support capabilities. Each subsystem can connect hundreds of thousands of distributed users and activate thousands of active threads at the same time. It is especially suitable for large-scale distributed application systems.

The difference between oracle and db2

Data type conversion function

a. Subquery alias (as can be used)

ORACLE 中支持:select * from(select 1 from dual) 或者 select * from(select 1 from dual) t

DB2    中支持:select * from(select 1 from sys.userId) t 或者 select * from(select 1 from sys.userId) as t

两者兼容  写法：select * from(子查询) t

Oracle中遇到列名为Oracle关键字的，需要用双引号括起来

b. When creating an index (replace field)

Oracle supports, DB2 does not support

The create or replace statement is illegal under DB2

不可以用 CREATE OR REPLACE SEQUENCE a1，要用 CREATE SEQUENCE a1

The difference between c.DATE data types:

The DATE type in ORACLE contains hours, minutes and seconds, but under DB2 DATE is only year, month and day, such as '2007-04-28', and can be directly operated as a string. In DB2, it must The recording time, minutes and seconds must be of TIMESTAMP type. When sql passes values, the DB is of date type. If there is only year, month and day (yyyy-MM-dd) in the screen, when operating the database, pay attention when passing values:

① Use String to pass Value

② Use Date to pass the value (you need to format the hours, minutes and seconds in the Date variable)

select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss:ff') from dual;
select to_date(to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss:ff'),'yyyy-mm-dd hh24:mi:ss:ff')from dual;

d. Get the system date:

ORACLE： Select sysdate from dual;
DB2：    Select current timestamp/date from sysibm.sysdummy1;

e.Substr usage:

SUBSTR(Str,POS,<LENGTH>) ; --Str需要截取的参数，POS从什么位置截取，LENGTH截取的长度

In Oralce, position can take the value 0, but in DB2, it takes If the value is 0, an error will be reported;

In Oracle, our length can be greater than or equal to the length of the char string, but the function itself is processed based on your starting position to the end of the char string. The length of the position is processed, and it will not be determined by the length you gave. It is equivalent to Oracle identifying the correctness for us and processing the excess length.

In DB2, if the length of length is greater than the length between the starting position and the ending position, then the database will only report an error for us and will not perform the results we want like Oracle.

f. Differences in usage of functions rownum, etc.:

The function rownum is not supported in DB2;

If you use JDBC paging, please note that rownum is not supported in DB2. For example, obtain the 10 records with the smallest area_id from the masa_area table. The statements are as follows. Pay attention to the alias t here. Writing method

ORACLE:
select t.* from (select rownum as r1 ,masa_area.* from masa_area order by area_id) t where t.r1<=10

DB2: 
select t.* from (select rownumber() over() as r1 ,masa_area.* from masa_area order by area_id) t where t.r1<=10

Recommended tutorial: " Oracle Tutorial》

The above is the detailed content of What is the difference between oracle and db2. For more information, please follow other related articles on the PHP Chinese website!