Analyze the advantages of the DB2 database

With the advantages of the DB2 database, IBM has competed for its own advantages in the database market. So what are the advantages of the DB2 database?

DB2 is a database product of IBM. In recent years, the strong development of Oracle, Sybase and other databases has led many people to think that DB2 will gradually exit the database market. However, after a few years, not only does DB2 not show signs of exit, instead, it expands step by step to gradually regain the lost market. Next we will make a simple comparison with Oracle, Sybase, and other databases, hoping that you can understand the advantages of the DB2 database.

Comparison between DB2 and Oracle

Openness/portability

IBM DB2 UDB is based on the latest international standards at all levels, from network protocol support to programming interfaces of application development programs, database storage programs and triggers, and distributed operations between servers, to ensure its openness and portability. Many features of ORACLE use proprietary, non-open technologies. For example, the ORACLEPL/SQL language is private to ORACLE. The stored program statements do not comply with the ANSISQL and ISOSQL standards. The Client/Server programming interface and oracle SQL * NET are not defined according to the standard.

Processing Performance

IBM has invented relational databases and their optimization technologies, and is constantly evolving. Therefore, DB2 is a leader in various performance tests. IBM is confident to surpass ORACLE in a variety of standard test environments. DB2 has excellent technologies in high-end applications, and there are numerous user instances with more than 1 tera-bytes data volume. Since ORACLE restricts users from publishing their performance tests without permission, it is generally difficult for users to obtain comparative ORACLE data. There are few customer instances with Oracle data volume exceeding 1 tera-bytes.

Parallel capability

The parallel version of DB2 supports Bitmap indexes and Object Relationships. Therefore, DB2 has better scalability and performance. Oracle triggers cannot support Parallelism: Parallel Processing cannot be used under some special referentialintegrity restrictions: If a table contains large objects, due to the aforementioned restrictions (tables cannot be separated ), parallel processing is also unavailable. In short, Oracle does not implement Bitmap indexes and Object Relationships in parallel systems,

Distributed Database

DB2 distributed database processing (such as two-phase commit) and connection functions are built on the core of DB2 and do not need to use other product accessories. Good performance, easy to use, and reduced costs. For the distributed functions of ORACLE, You need to purchase DistributedOption and SQL * NET to connect distributed databases.

Heterogeneous platform support

In a heterogeneous environment, the IBM database solution is simple, more seamless, and less costly than Oracle. Oracle's heterogeneous gateway has poor performance. when accessing a heterogeneous database, Oracle lacks global optimization, this results in excessive data and network transmission.

Replication capability

DB2 provides the most efficient and open replication solutions in the industry; DB2 provides automatic conflict detection and resolution methods. Oracle only provides example programs for automatic conflict detection and resolution. You must customize the example programs and then resolve the replication conflicts.

E-commerce Support

IBM's Java technology is recognized by the industry, and DB2 is recognized as the cornerstone of e-commerce. The JDBC interface provided by DB2 can also be used to write stored procedures in Java and user-defined functions. The Java Technology of Oracle is only limited to database and ERP applications.

Business Intelligence Support

DB2OLAPServer is much easier to use and implement than Express. DB2OLAPServer provides read/write capabilities-this is useful for some key multidimensional analysis scaling applications such as prediction and budgeting (Express only supports read ). In IBM's Business Intelligence solutions, data mining (IntelligentMiner) tools are available. OracleExpress only supports read capability, which is not conducive to support prediction and budget. Oracle lacks data mining tools.

Software Quality and reliability

DB2's software development lab passed the ISO9000 international quality management standard verification and approval as early as 1994. Therefore, DB2's software quality is the database with the highest reliability and lowest failure rate among all database manufacturers. After the launch of ORACLE version 6 and 7, it takes numerous revisions to achieve a certain degree of availability, which brings great inconvenience to users, the availability and reliability of the production system are seriously affected.

Comparison between DB2 and Sybase

Openness/portability

IBMDB2 programming interfaces comply with OPEN standards such as ANSI, X/OPEN Standard CLI (CallLevelInterface), and ANSISQL embedded SQL. IBMDB2's storage programs and trigger mechanisms use the latest international SQL standards and support standard open languages such as C/C ++. In DB2CommonServerV2.1.2 (1996) and DB2UDB (1997), the function of developing storage programs and triggers in Java is added. 1. Programming Interface: the programming interface used by Sybase is not open, because it uses its own OpenClient/OpenServer interface instead of a standard interface. 2. storage Program (StoredProcedure): Because Sybase's CLIENT/SERVER technology was developed in 1980s, related standards such as SQL92 were not used, therefore, the Transact-SQL statements used to compile storage programs and triggers are also proprietary.

Processing Performance

IBM has invented relational databases and their optimization technologies, and is constantly evolving. Therefore, DB2 is a leader in various performance tests. DB2 has excellent technologies in high-end applications, and there are numerous user instances with more than 1 tera-bytes data volume. Sybase OPTIMIZER cannot effectively support complex queries (ComplexQueries ). The Sybase optimizer cannot properly optimize SQL statements that are slightly complex in applications. Sybase has just entered the field of massive multi-processor MPP. Without such experience and precedent, Sybase has few customer instances larger than one TB of data.

Parallel capability

The parallel version of DB2 supports Bitmap indexes and object relationships, so DB2 has better scalability and performance. The core structure of Sybase databases is outdated, especially in multi-processor (SMP) ECS instances cannot be scaled together with hardware and software, and the advantages of multi-processor cannot be utilized. The single-process multi-clue structure cannot be more effective than the multi-process multi-clue structure of DB2 on machines such as SMP.

High Availability

DB2 can use granularity to back up and RESTORE data (BACKUP and RESTORE), and the BACKUP can be performed ONLINE, it does not affect the simultaneous use of multiple users. This enables DB2 to be able to be used 24 hours a day, 7 days a week around the clock. When backing up the database and RESTORE database, Sybase needs to convert the system into a single user mode to ensure data integrity and consistency in the database. After BACKUP, DBCC should be run to check data consistency, this limits the availability of Sybase databases.

Data Integrity and manageability

Databases in IBMDB2 can automatically maintain consistency between the two tables without the need for DBA programming. Sybase introduces RI in its database technology (ReferentialIntegrity data integrity) Later, and has limited functionality. For example, if column A in Table A is the ForeignKey of Column B in another table B, it is not allowed in Sybase to delete A record in table, only A Trigger and StoredProcedure can be used to delete A record in Table A, delete related records in Table B, or set its value to NULL.

Heterogeneous platform support

In a heterogeneous environment, the IBM database solution is simple, more seamless, and cost-effective. Oracle's heterogeneous gateway has poor performance. when accessing a heterogeneous database, Oracle lacks global optimization, this results in excessive data and network transmission.

Replication capability

DB2 provides the most efficient and open replication solution in the industry. DB2 supports data preprocessing before data replication to the remote end, which is more efficient and has less network traffic. Sybase is an instant copy transaction, with a large amount of data and network traffic. According to the user's feedback, Sybase replication is difficult to implement. Data replication between SybaseAdaptiveServerAnywhere and AdaptiveServerEnterprise is not seamless and requires integration.

E-commerce Support

IBM's Java technology is recognized by the industry, and DB2 is recognized as the cornerstone of e-commerce. The JDBC interface provided by DB2 can also be used to write stored procedures in Java and user-defined functions. The Java Technology of Oracle is only limited to database and ERP applications.

Business Intelligence Support

DB2WarehouseManager has the built-in data warehouse management function. In IBM's Business Intelligence solutions, data mining (IntelligentMiner) tools are available. Sybase data mart (datamart) does not provide Warehouse management and automation functions.

Software Quality and reliability

DB2's software development lab passed the ISO9000 international quality management standard verification and approval as early as 1994. Therefore, DB2's software quality is the database with the highest reliability and lowest failure rate among all database manufacturers. The corruption of data in IBM systems is not only insensible, but also unacceptable to traditional IBM users. The data in Sybase must be maintained with caution because the data may be corrupted in the database and cannot be used again. If the data is backed up, it cannot be used again without prior patching. Therefore, Sybase has to provide a check program (DBCC) for the customer ).

Comparison between DB2 and Informix

Openness/portability

IBMDB2UDB is based on the latest international standards in all aspects, from network protocol support to programming interfaces of application development programs, database storage programs and triggers, and distributed operations between servers, to ensure its openness and portability. The private 4GL program of Informix can only be executed on Informix.

Processing Performance

IBM has invented relational databases and their optimization technologies, and is constantly evolving. Therefore, DB2 is a leader in various performance tests. DB2 has excellent technologies in high-end applications, and there are numerous user instances with more than 1 tera-bytes data volume. Since Informix started with a small database, many small databases still have restrictions in its product design so far. Many users have encountered Informix problems in data storage, log management, backup, recovery, and other aspects. The Extended Parallel option of Informix is difficult to support concurrent access by multiple users. Informix lacks management experience on a large amount of data, so its optimizer technology cannot optimize a large amount of data.

Parallel capability

DB2EEE can use HACMP to automatically replace faulty nodes for normal operation, but Informix cannot. DB2EEE has years of history and has issued the highest TPC-D and TPC-H benchmark values. DB2EEE supports the dynamic bitmap index to complete Star-jion, which improves the performance without increasing the maintenance workload of the database system administrator. On the Parallel Machine SP, when the high-performance switch of the SP cannot communicate, the InformixXPS database will be paralyzed. when the node of the parallel machine fails, the InformixXPS database will be paralyzed, with a downtime of 4 to 24 hours. InformixXPS has not been backed up or restored. InformixXPS does not monitor the query. It is possible that a user's query consumes all the resources of the system and does not allow other users to use it.

Distributed Database

DB2 directly supports the XA interface through the core and provides advanced XA functions not available by many other vendors. For example, the DB2 application of the Industrial and Commercial Bank of China Shandong Branch uses the DB2XA interface in a large number, it implements a large-scale online transaction processing system (OLTP) combined with CICS to support applications such as provincial communication and exchange. Informix supports heterogeneous distributed databases through X/OPEN and XA. Although Informix claims that it supports the X/OPEN and XA interfaces, it only works in a few versions through additional options. In this regard, Informix has little precedent and does not have technical support in this regard.

Heterogeneous platform support

In a heterogeneous environment, the IBM database solution is simple, more seamless, and cost-effective; Informix is weak in connecting databases. For example, Informix does not connect to other databases such as bbORACLE and Sybase.

Replication capability

DB2 provides the most efficient and open replication solutions in the industry; DB2 provides automatic conflict detection and resolution methods. Informix only supports replication between Informix databases. To replicate data with other databases, you must purchase third-party support software.

Four precautions for DB2 Data Import

DB2 Management page size limit

Policy Selection for DB2 environment variable Management

Management tasks that can be completed by the DB2 Management Server

DB2 isolation level-read for implementation