DB2 Provides a Reliable and Consistent Relational DBMS.
In today's business environments, the demand for access to information is still increasing, and the volume and complexity of the data, as well as the variety of the applications using the data, are growing rapidly. Users who were once satisfied with simp le programs accessing data files now require powerful database applications accessing data managed by database management tools. Application programmers using smaller workstations now require the features of systems once available only on larger machines . However, throughout this evolution, the historical data kept on the mainframe must still be preserved - it must still be accessed by some of the legacy applications.
More and more, critical business applications are being moved from mainframe machines to client/server LANs because of the advances in hardware capacity, software function, and performance. Data may now be located anywhere in the office, across the count ry, or all over the world. Design decisions must now be made on where best to store data, how to access it quickly, and how to set up production databases and applications on various platforms so that they can best interact. One of the biggest design dec isions is whether to store data under the centralized control of a big database on the mainframe, or near users in smaller databases managed by semi-autonomous servers.
IBM's DB2 has always been well known as a mainframe DBMS, but it too has made the downsizing move from the mainframe to departmental servers, and recently all the way down to the PC.
Referential integrity is imposed by adding referential constraints to table and column definitions. You define the basic declarative referential-integrity constraints using the PRIMARY KEY, FOREIGN KEY, and REFERENCE clauses that form part of the CREATE TABLE or ALTER TABLE statements. You can define unique constraints using the primary key of a table or a unique index. Check constraints can also be defined to provide a single constraint at the table level, which can involve multiple table columns. The maximum number of constraints on a single table depends on the table's storage structure used. DB2 supports the on clause - for example, ON DELETE NO ACTION and ON DELETE CASCADE. If a newly inserted row in the dependent table violates the referential co nstraint, all row(s) in the same transaction are backed out. If a deleted row(s) in the parent table violates the referential constraint, the RESTRICT, NO ACTION, CASCADE, or SET NULL constraint is enforced. If no delete rule is defined when the table is created, the NO ACTION constraint is applied as the default. If an updated row in the parent table violates the referential constraint, an error occurs and no rows are updated. You can give names to all of the constraints you declare.
DB2 previously supported only system-defined default column values; now user-defined column default values are also supported. The "default clause" of CREATE TABLE and ALTER TABLE has been extended to allow for this, and it is SQL-92-compliant. A table c heck constraint specifies search conditions that are enforced for each row of a table and automatically activated when an update or insert statement runs against the table.
You can create a view with the WITH CHECK OPTION clause. This clause indicates that any updated or inserted row to the view must be checked against the view definition's WHERE clause, and it must be rejected if it does not conform. This enhances data int egrity but requires additional processing. If omitted, no checks are performed against the view definition.
A database can be partitioned into parts called table spaces. A table space can be spread over one or more physical storage devices. DB2's table space storage model provides a level of indirection between the database and the tables stored within the dat abase. Table spaces let you assign the location of the database and the table data directly onto a container - a directory, device, or file name. A single table space can span several containers. This capability provides improved performance, a more flex ible configuration, and better integrity. For even better performance, each container can use a different disk. DB2's SQL optimizer chooses the most efficient way to access data in the tables.
The only index storage structure that is used is a B+ tree. DB2 has introduced the concept of pre-fetching data pages for both table scans and index scans. DB2 will now also detect sequential access to index pages and generate pre-fetch requests. These f unctions will significantly reduce the elapsed time for index scans, especially with non-selective index scans. When index clustering is poor the database manager will not use the index but will instead scan the entire table. To improve the effectiveness of the indexes, the REORG utility is used to cluster the index on particular columns. A table can only be clustered on one index.
DB2 has a trigger mechanism with which business rules and advanced referential-integrity constraints may be implemented. A user-defined function can be called within a triggered SQL statement. "Trigger granularity" defines whether the trigger actions wil l be performed once for the statement or for each affected row. "Trigger activation time" defines whether the trigger is activated before or after the trigger event is performed.
DB2 supports stored procedures. A stored procedure is coded using SQL and your favorite 3GL. The Database Application Remote Interface stored procedures permit a call to a remote database to execute a pre-programmed procedure. One call may represent seve ral accesses to the database. A stored procedure can have a maximum of 32,767 parameters.
DB2 creates a package object in the process of compiling a static SQL statement. The package contains control information used to process the SQL statements. Users don't need explicit authority over the database objects used; once they have permission to run a package, they can manipulate data as determined by the package.
DB2 can greatly improve the performance of sequential I/O by using the sequential pre-fetch facility, which uses parallel I/O. The database manager provides concurrency control and prevents uncontrolled access by means of locks. The lock granularity dete rmines the level of the lock. DB2 provides both table-level and row-level locking. Tables and rows can be locked in either share or exclusive mode. In share mode, other applications can retrieve data as read-only.
DB2 supports outer joins, which are produced using a combination of common table expressions - for example, EXCEPT ALL. The syntax used in an outer join is not in accordance with the SQL-92 standard. There are three types of outer joins: left outer join, right outer join, and full outer join.
DB2 provides three levels of security checks to view or manipulate data stored in the database: system entry, database connection, and database object usage. Four privilege types enable users to create or access database resources: database privileges, t able and index privileges, package privileges, and index privileges. Authority levels exist to provide a method of grouping privileges and higher-level database manager maintenance and utility operation: System Administration Authority (SYSADM), Database Administration Authority (DBADM), System Control Authority (SYSCTRL), and System Maintenance Authority (SYSMAINT). A user or group can have individual privileges or one or more of the above authority levels.
The Database Director is a graphical interface that displays database objects and their relationships to one another. Using the Database Director, you can select one or more database objects to perform the tasks provided by the DBA Utility, Visual Explai n (a tool for analyzing and tuning SQL statements), and Performance Monitor.
A great deal of organizational data is managed by DB2 on host and mini-computer systems. Applications running on any of the supported client platforms can work with this data transparently as if it was managed by a DB2 common server. DDCS provides this f acility by implementing the DRDA protocol to permit desktop and workstation applications to work with DB2 servers on host systems. DDCS comes in single-user and multi-user gateways. DDCS lets DB2 systems on the host assume the role of an enterprise super -server. This solution enables established data management procedures to be maintained, and applications can then be distributed to PC and workstation platforms.
A two-phase commit, or distributed unit of work access, is supported. For example, within a single transaction, databases on both servers can be accessed and updated with full integrity.
The DataJoiner is a solution to consider if you need to transparently access and/or join data distributed across multiple vendor sources, namely DB2, Informix, Sybase, Oracle, or Microsoft SQL Server.
Using the DataHub, you can manage your client/server environment from a single control point that consists of an easy-to-use graphical interface for the following relational DBMSs: DB2, Oracle, Informix, and Sybase.
DB2 was traditionally designed for and implemented on the mainframe, but today it also runs on high-end and midrange servers, as well as small desktop PCs. DB2 runs on a variety of platforms, from large systems such as MVS/ESA, VM, and VSE, and mid-sized systems such as OS/400 and Unix-based systems, to single-user systems such as OS/2 and Windows NT. It is important to note, too, that these aren't just simple code ports but what IBM calls "deep ports." On each platform, DB2 is rearchitected to exploit the features and facilities of the host operating system. For example, on Windows NT, DB2 runs as a Windows NT service; it is integrated with the NT Performance Monitor, and user authentication is performed using the native NT security facility. Similarl y, on Solaris, DB2 makes use of a thread semaphore to increase multi-threading, and it takes advantage of the kernel's asynchronous I/O and intimate shared memory for improved performance.
IBM has hereby shown that its constant and substantial investment in DBMS research and development over the course of nearly three decades has paid off. IBM is well qualified to provide the highest level of DBMS support in the industry, even over the wid e range of platforms on which its flagship DBMS product now runs.