DB2 and Oracle distinction ____oracle

Today, the customer visited (Japanese) and asked me the difference between DB2 and Oracle. Because it's not a DBA, I haven't really summed it up. But my first feeling: One is instance, one is database. Building Ora libraries and DB2 libraries is not the same. It's really はずかしい. 


System Architecture Overview


First, we need to understand the architecture used by Oracle and understand how it differs from DB2. Figure 1 shows the system architecture of Oracle. The figure is compared to Figure 2, which shows the system structure of the DB2. When reading this article, for the sake of understanding, you can refer to these two graphs.
Figure 1. System architecture for Oracle on Linux, UNIX, and Windows Version 10.2


Figure 2. DB2 on Linux, UNIX, and Windows system architecture

Back to the top of the page

Instance


In Oracle and DB2, the concept of an instance is similar. In both cases, the instance refers to a combination of background processes and shared memory. The main difference between the two is that there can be only one database per instance in Oracle, while multiple databases in DB2 share an instance.

In Oracle, because the database is one-to-one with the instance, an instance is implicitly created while creating a database using the CREATE DATABASE command. Alternatively, to create an Oracle instance on your computer, you can either use the Database Configuration Assistant or use the Oradim utility, which is provided by the Oracle 9i via the NEW option. You must also provide some information, including system identifiers (Systems Identifier,sid) or a service name, instance password, maximum number of users, startup mode, and so on. Similarly, to delete an instance, you can use the Oradim utility to add the delete option. You need to provide a SID or service name here. Unless you create a new database during the installation process, the default instance is not created when Oracle is installed in fresh mode.


In DB2, when the product is installed on the Windows platform, an instance "DB2" is created by default. In Linux and UNIX, the default instance name is "Db2inst1". To create another instance on the same machine, simply execute the command de>db2icrt <instance name>de>.

Figure 3 shows the default DB2 instance "DB2" (in Windows) and another two instances created with the DB2ICRT command from the DB2 control Center GUI.
Figure 3. DB2 control Center GUI showing DB2 instance


To refer to a given DB2 instance in a command-line interface, you can use the environment variable db2instance. With this variable, you can specify the current active instance, and all commands will be applied to this instance. For example, if the db2instance is set to PROD, and then you execute the command de>create database Mydb1de>, you will create an association with the instance PROD. If you want to create the database on an instance DB2, you must first change the value of the db2instance variable to DB2. This is similar to Oracle_sid (System Identifier), which uses ORACLE_SID when the user wants to switch between instances.

There is also a simple way to identify an instance to use, and that is to use the DB2 control Center GUI, as shown in Figure 3. To see an entry in the tool that corresponds to the new instance, you need to add the instance to the tool by right-clicking instances and selecting add . To delete an instance of the DB2, you can execute the command de>db2idrop <instance name>de>.


In summary, in Oracle, you can use the Database Configuration Assistant to create, modify, start, stop, and delete instances, while in DB2 you can do the same thing with the control Center GUI. Also, Oracle instances and databases can only be one-to-one relationships, but not in DB2. Multiple databases can exist at the same time in a DB2 instance, and these databases can be used concurrently.

Back to the top of the page

Database

In Oracle, you can create a database manually using the Create DB command or database Configuration Assistant. When you create a database manually, you need to perform a series of steps, including setting an OS variable, preparing a parameter file, and creating a password file before executing the Create DB command.


Metadata information is stored and managed in data Dictionary, consisting of basic tables and corresponding views. Basic tables are created automatically during database creation, and views are constructed by running Catalog.sql and Catproc.sql scripts.

Therefore, an Oracle database can be viewed as a collection of 3 types of files: Data file: The physical implementation of the database, including the actual data. (similar to a container in DB2.) Redo files (Redo file): the equivalent of the transaction log (Transaction log) in DB2. Control file: Contains information that is used to maintain and validate database integrity.

In DB2, an instance can contain multiple databases, as shown in Figure 2. Each database is a closed, truly self-contained unit. Each database has its own catalog tablespaces, temporary table spaces, and user table spaces that are created by default when the database is created. DB2 contains a binary file called System database directory, which contains entries for all databases that can be connected from the DB2 machine. This directory is saved at the instance level.

When you create an instance, the database is not created by default, and you need to explicitly create a database by using the CREATE DATABASE command. You can also create a database with control Center, as shown in Figure 4 and Figure 5.
Figure 4. Creating a DB2 database using the control Center GUI
Figure 5. Creating a DB2 database using the control Center GUI (cont.)

In Figure 5, you can also see what happens when you click the show Command . All the DB2 control Center GUI screens will display SQL statements or commands that are actually executed in the background. These commands can be saved in a script for later execution, or they can be copied to the command line Processor (CLP) or command Center GUI tool and executed in these tools. These tools correspond to Oracle's sql*plus and ISQL *plus respectively.

You can use the ' DROP database ' command or delete a DB2 database in the DB2 control Center GUI. This is not a command that is used in Oracle. The deletion of the database is done by deleting all relevant data files.


Databases in the same instance typically do not interact with each other. However, if an application needs to interact with multiple databases, this requirement can be met by enabling Federated (Federation) support. In the Resources section, there is an article on the Commonwealth.

Back to the top of the page

containers, table spaces, buffer pools, and pages

In Oracle, the physical data is stored in files called data files. This is similar to the DB2 container (container), and the DB2 container is where the data is actually stored. Each Oracle database contains a table space called SYSTEM, which is created automatically by Oracle when the database is created. Other table spaces for user data, temporary data, and indexed data need to be created after the database has been created, and you need to specify a user for the table space before you can use them.


In DB2, a table space is a logical object, as a layer between a logical table and a physical container. When you create a tablespace, you can associate it with a specific buffer pool (the database cache) and associate it with a specific container. This provides flexibility for performance management. For example, if you have a hot table, you can define it in a separate table space, which is associated with a separate buffer pool. This helps ensure that the data in this table is continuously cached in memory.

in DB2, when you use the CREATE DATABASE command and its default values, three default table spaces are created automatically. Table 1 describes the default DB2 tablespace: table 1. DB2 tablespace created by default when creating a database with default values


Table Space Name Describe
Syscatspace Catalog table space containing meta data
TEMPSPACE1 A system temporary table space used to perform operations such as connection and sorting. The name of this table space can be changed
USERSPACE1 This table space is optional, and you can use this tablespace to store user tables if you do not explicitly specify the table space when you create the table

In DB2, table spaces cannot be shared across databases because the database is a stand-alone unit. Because tablespaces are knowable only in one database, two different databases can have tablespaces with the same name. As you can see in Figure 2, the database MYDB1 has a tablespace named Mytbls, and the database MYDB2 has a table space with the same name.


The DB2 tablespace can be divided into two categories: SMS (System managed tablespace) and DMS (database managed tablespace). SMS table spaces are managed by the operating system, and they can only be directories. SMS table spaces can grow automatically as needed, so SMS provides good performance and requires little management. DMS table spaces are managed by DB2, either as files or as raw devices. This type of table space provides the best performance, but requires some management. For example, you need to specify how much space you want to allocate for this table space, because this tablespace cannot grow automatically.


There is no concept of SMS in Oracle's storage model, but its data files are similar to DB2 DMS table spaces. That is, you can increase the size of the database by increasing the size of the data file, or by adding a data file to the table space, or by adding a new table space.

table 2 shows the corresponding relationship between the Oracle database or tablespace and the DB2 database or tablespace. table 2. The correspondence between Oracle database and DB2 database and table space

Oracle database or table space DB2 database or table space
SYSTEM is the tablespace that holds the catalog (data dictionary) information Syscatspace (catalog table space); As in Oracle, only save this information at the database level
Data Dictionary (metadata containing table and view forms) residing in the SYSTEM table space System catalog table (identified by SYSIBM mode), System view (identified by Syscat or Sysstat mode), residing in the Syscatspace table space
SCOTT Database SAMPLE Database
TEMP table Space System temporary table space (named Tempspace1 by default)
UNDO table Space N/A
USER table Space User table space. By default, USERSPACE1 is typically created after the database is created

as noted earlier, Oracle's data buffer concept is equivalent to the DB2 buffer pool. However, DB2 allows multiple buffer pools to exist. There is no need to define the number of buffer pools that can be created in DB2, and the name of the buffer pool can be arbitrary.

The concept of block in Oracle is the most similar to the pages in DB2. The size of a DB2 page can be 4k, 8k, 16k, or 32k. A row in a table can only be placed on one page, not across multiple pages, as in Oracle.

Back to the top of the page

Object Name

The object names in Oracle form the following:

De>[schema_name.] object_name[@database]de>

In DB2, an object name is also a two-part structure:

De>schema_name.object_namede>


As in Oracle, the DB2 schema name is used to logically organize objects. But an important difference between the two is that in DB2, the schema name does not necessarily match a user ID. Any user with Implicit_schema permissions can create an object in a nonexistent pattern. For example, suppose "Peter" has implicit_schema permission, he executes the following command:

De>create TABLE World. TABLEA (LastName char ()) de>

This command creates the table world. TABLEA, where world is the newly created pattern. If Peter does not explicitly specify a pattern, the command creates the table Peter. TABLEA, because the connection ID is used by default.

In DB2, you always connect to a database before you issue a database-related command. Therefore, under this architecture, the object name does not need to include the database name.

Back to the top of the page

tables, views, and indexes


In Oracle and DB2, tables, views, and indexes are basically the same.

DB2 provides a utility called design Advisor that you can use to recommend indexes for specific queries or workloads. Design Advisor can be invoked from DB2 control Center or by using the Db2advis command from the DB2 CLP. In DB2, the index is directly bound to the table definition. For example, when using DMS table spaces, you can specify which tablespace the index is stored in by using the following statement:

De>create TABLE mytable (col1 integer, col2 char ()) in TBLS1 index in tbls2de>

The above example shows that the data in the table will be stored in the table space ' tbls1 ', and the index page will be stored in the tablespace ' tbls2 '. In Oracle syntax, however, the CREATE index statement has an option to specify which table space the index is stored in.

In addition, in DB2, once the index is created, you cannot modify any of the clauses in the index definition. To make changes, you need to delete the index, and then re-create the index.


As in Oracle, DB2 tables, views, and indexes in different databases can have the same name. Tables and views in the same database must use a different name, but the index is allowed to be created with the same name as an existing table or view.

Back to the top of the page

Stored procedures, triggers, and user-defined functions (UDF)

In an Oracle environment, there are many ways to create and access stored procedures, triggers, and functions. Pl/sql is an object-oriented (oo) process extension of SQL that supports data manipulation (DML), flow control, declarations of variables and constants, procedure and function definitions, and OO data types such as nested tables and variable-length arrays (varray). Oracle also incorporates the JVM into its engine. In an Oracle database, you can use SQLJ to create, store, and execute stored procedures, functions, and triggers as classes. Oracle also supports the JDBC driver for Type 1 through 4.

DB2 stored procedures can be written in any language supported by the DB2 precompiled compiler, including Java, C, C + +, REXX, Fortran, and COBOL. However, we recommend that you use SQL procedural Language (SQL PL), which is very similar to Oracle's pl/sql. When developing the SQL PL stored procedure, a C compiler is needed because the stored procedure is first converted to C. The C implementation of a stored procedure can provide a performance advantage because the code needs to be compiled only once (especially in unfenced mode). However, when developing this stored procedure, an additional C compiler is required on the development system. In future releases of DB2, SQL PL stored procedures that do not require C compiler support are expected to occur. DB2 stored procedure Development also uses the JDBC driver of Type 1 through 4 to support SQLJ and Java.

Triggers and functions can be developed using inline SQL/PL, a method that does not require a C compiler. This method supports a subset of the SQL PL statement. You can also use DB2 Development Center Tool to simplify the creation, construction, debugging, and deployment of DB2 stored procedures and user-defined functions.

Back to the top of the page


Configuration file

Traditionally, Oracle stores all session-and system-related parameters in a text file, which is often called Initsid.ora. However, since this text file is not persistent, Oracle introduced server Parameter file (SPFILE) from Oracle 9i, a binary parameter file stored on the server. It persists after the instance is stopped and before it is started. However, when SPFILE is unavailable, the Initsid.ora file is still used. Prior to the introduction of SPFILE, any alter SYSTEM and ALTER session commands that have an effect on the parameters can only persist during instance or conversation activity. Whenever a database instance requires a rebound (rebound), the DBA must manually modify the Initsid.ora text file. For listeners, the network access configuration is usually stored in Listener.ora, and for client access, the network access configuration is usually stored in Tnsnames.ora.

In DB2, configuration parameters are also stored at the instance-level and database-level, at the instance level is the Database Manager configuration file, which is the database configuration file at the database level. Most of these parameters can be changed dynamically, that is, you do not have to stop and restart the instance or reconnect all connections in order for the changes to the parameter values to take effect.

If you want to manually change specific database manager configuration parameters in the CLP, you can use the command de>update DBM CFG using <parameter name> <new value>de>.

If you want to manually change specific database parameters in the CLP, you can use the command de>update DB CFG for <database name> using <parameter name> <new De>.

These two commands are equivalent to Oracle's alter SYSTEM and ALTER sessions respectively. Alternatively, you can use control Center to view and modify the values of these parameters. If you right-click a given instance and choose Configure Parameters, you can see the window shown in Figure 6.
Figure 6. DB2 Database Manager configuration parameters (instance level)


At the database level, you can see the window shown in Figure 7 by right-clicking a given database and selecting Configure Parameters.
Figure 7. Database configuration parameters (database level)

DB2 provides a number of parameters for configuring the system. However, if you want to automatically configure your system in an easy way, you can use the de>autoconfigurede> command (or the Configuration Advisor GUI) It sets the database Manager configuration parameters and database configuration parameters to the best value based on some information you provide. Figure 8 shows the Configuration Advisor.
Figure 8. DB2 Configuration Advisor

In addition to configuration files, DB2 typically uses DB2 registry variables for platform-related configuration. Note that the DB2 registry variable has no relationship to the Windows registry. You can use the command Db2set to view and change these variables.

The connection (network access) information is stored in the System database directory, the local database directory, and the node directory. These are binary files that can only be modified with the CATALOG and Uncatalog commands.

Back to the top of the page

Memory Architecture and background processes


Next, let's look at the memory architecture and background processes and compare the differences between the memory architecture and background processes in Oracle and DB2.
Figure 9. Oracle memory Architecture and background processes

The System Global area (SGA) in Oracle is a set of shared memory blocks for storing information about an instance. These include statement caching, redo log buffers, and data buffer caching. Program Global Area (PGA) and user Global area (UGA) shared memory blocks that contain data and control information for server processes and user sessions.

Oracle support has multiple instances on the same computer, but it is not allowed to share background processes. For example, three instances on the same computer require three sets of background processes. It is therefore recommended that you include a database, an instance, and multiple schemas on a single computer.
Figure 10. DB2 memory Architecture and background processes

Both DB2 and Oracle use shared memory blocks, but the DB2 is slightly different from the Oracle memory architecture. Because the DB2 instance can contain more than one database, there are two levels of configuration. As already mentioned in the previous section, the instance-level configuration can be done in the DBM cfg file, while database-level configuration can be done in the DB cfg file. The configuration parameters at these two levels can be adjusted to tune memory usage. The following article will describe the memory structure of DB2 and the different background processes in more detail.

Oracle allocates memory to the instance and database when they are started, while DB2 allocates memory at different levels. This is mainly because the DB2 instance can contain multiple databases. There are three main memory structures in DB2: instance Shared memory : This is the Database Manager global shared memory, which is assigned to the instance when the instance is started using the de>db2startde> command, and is issued in the de> The db2stopde> command is in an assigned state until the instance is stopped. database Shared Memory : This is the database global memory that was allocated when the database was activated or first connected to the database. The allocated memory includes a buffer pool, a lock list, a database heap, a utility heap, a package cache, and a catalog cache. Application Shared Memory : This is the memory that is allocated when an application connects to a database, and is used by agents that handle the work requested by clients connected to the database. Each application connected to the database is allocated memory, so it is important to configure the correct configuration of the parameters that affect the application's shared memory.


in DB2 for Windows, server activity takes the form of threads, and in Linux and UNIX environments, these activities are implemented in the form of background processes. There are several levels of processes in DB2: instance level: These processes are initialized at instance startup: DB2 Daemon spawner (DB2GDS): Global daemon, with each instance corresponding to one such process (only in UNIX) STRONG>DB2 System Controller (DB2SYSC): DB2 the main process. DB2 watchdog (Db2wdog): The parent process for all other processes. DB2 Format Log (DB2FMTLG): Similar to the ARCn process in Oracle, which allocates space for log files in the log path. 
Database level: These processes are initialized when a connection to the database is established: DB2 Log Reader (DB2LOGGR): A subset of the Pmon processes that resemble Oracle. The process reads the log files during rollback, restart recovery, and roll forward. DB2 log Writer (DB2LOGW): Flushes the log from the log buffer to the transaction log file on disk. Equivalent to the LGWR process in Oracle. DB2 Page Cleaner (DB2PCLNR): equivalent to DBW

in Oracle