Jump to content

IBM Db2

From Wikipedia, the free encyclopedia

This is an old revision of this page, as edited by Pi314m (talk | contribs) at 23:08, 24 August 2018 (→CurrentLY .... supported platforms). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

"DB2" redirects here. For other uses, see DB2 (disambiguation).
IBM Db2
Developer(s)IBM
Initial release1983 (1983)
Stable release
Db2 Data Server (11.1) / April 12, 2016; 8 years ago (2016-04-12)
Written inC, C++, assembly
Operating systemCross-platform
Available inEnglish
TypeRDBMS
LicenseProprietary EULA
Websitewww.ibm.com/db2/

IBM Db2 was the name introduced by IBM in 2017[1]. The names DB2(tm) and Db2(tm) refer to database-server products developed by IBM. These products all support the relational model, but in recent years, some products have been extended to support object-relational features and non-relational structures like JSON and XML.

Although IBM declared that "z/OS is the main operating system for ... Db2",[2] as of 2017 it is also available in the form of Database-as-a-Service (DBaaS).[3]

Currently supported platforms

History

Historically, and unlike other database vendors, IBM produced a platform-specific Db2 product for each of its major operating systems. However, in the 1990s IBM changed track and produced a Db2 mostly-common product, designed with a ("mostly") common code base for L-U-W (Linux-Unix-Windows); DB2 for z and DB2 for i are different. As a result, they use different "drivers."[6]

DB2 traces its roots back to the beginning of the 1970s when Edgar F. Codd, a researcher working for IBM, described the theory of relational databases, and in June 1970 published the model for data manipulation.[7]

In 1974, the IBM San Jose Research center developed a relational DBMS, System R, to implement Codd's concepts.[8] A key development of the System R project was SQL. To apply the relational model, Codd needed a relational-database language he named DSL/Alpha.[9] At the time, IBM didn't believe in the potential of Codd's ideas, leaving the implementation to a group of programmers not under Codd's supervision, who violated several fundamentals of Codd's relational model; the result was Structured English QUEry Language or SEQUEL.

When IBM released its first relational-database product, they wanted to have a commercial-quality sublanguage as well, so it overhauled SEQUEL, and renamed the basically-new language Structured Query Language (SQL):

  • to differentiate it from SEQUEL.
  • The acronym SEQUEL was changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.[10]

IBM bought Metaphor Computer Systems to utilize their GUI interface and encapsulating SQL platform that had already been in use since the mid 80's.

In parallel with the development of SQL, IBM also developed Query by Example (QBE), the first graphical query language.

IBM's first commercial relational-database product, SQL/DS, was released for the DOS/VSE and VM/CMS operating systems in 1981. In 1976, IBM released Query by Example for the VM platform where the table-oriented front-end produced a linear-syntax language that drove transactions to its relational database.[11] Later, the QMF feature of DB2 produced real SQL, and brought the same "QBE" look and feel to DB2.

The name DB2, or IBM Database 2, was first given to the Database Management System or DBMS in 1983 when IBM released DB2 on its MVS mainframe platform.[12]

When Informix Corporation acquired Illustra and made their database engine an object-SQL DBMS by introducing their Universal Server, both Oracle and IBM followed suit by changing their database engines to be capable of object-relational extensions. In 2001, IBM bought Informix Software, and in the following years incorporated Informix technology into the DB2 product suite. DB2 can technically be considered to be an object-SQL DBMS.

For some years DB2, as a full-function DBMS, was exclusively available on IBM mainframes. Later, IBM brought DB2 to other platforms, including OS/2, UNIX, and MS Windows servers, and then Linux (including Linux on z Systems) and PDAs. This process occurred through the 1990s. The inspiration for the mainframe version of DB2's architecture came in part from IBM IMS, a hierarchical database, and its dedicated database-manipulation language, IBM DL/I. DB2 is also embedded in the i5/OS operating system for IBM System i (iSeries, formerly the AS/400), and versions are available for z/VSE and z/VM. An earlier version of the code that would become DB2 LUW (Linux, Unix, Windows) was part of an Extended Edition component of OS/2 called Database Manager.

IBM extended the functionality of Database Manager a number of times, including the addition of distributed database functionality by means of Distributed Relational Database Architecture (DRDA) that allowed shared access to a database in a remote location on a LAN. (Note that DRDA is based on objects and protocols defined by Distributed Data Management Architecture (DDM).)

Eventually, IBM took the decision to completely rewrite the software. The new version of Database Manager was called DB2/2 and DB2/6000 respectively. Other versions of DB2, with different code bases, followed the same '/' naming convention and became DB2/400 (for the AS/400), DB2/VSE (for the DOS/VSE environment) and DB2/VM (for the VM operating system). IBM lawyers stopped this handy naming convention from being used, and decided that all products needed to be called "product FOR platform" (for example, DB2 for OS/390). The next iteration of the mainframe and the server-based products were named DB2 Universal Database (or DB2 UDB).

In the mid-1990s, IBM released a clustered DB2 implementation called DB2 Parallel Edition, which initially ran on AIX. This edition allowed scalability by providing a shared-nothing architecture, in which a single large database is partitioned across multiple DB2 servers that communicate over a high-speed interconnect. This DB2 edition was eventually ported to all Linux, UNIX, and Windows (LUW) platforms, and was renamed to DB2 Extended Enterprise Edition (EEE). IBM now refers to this product as the Database Partitioning Feature (DPF) and bundles it with their flagship DB2 Enterprise product.

In mid-2006, IBM announced "Viper," which is the codename for DB2 9 on both distributed platforms and z/OS. DB2 9 for z/OS was announced in early 2007. IBM claimed that the new DB2 was the first relational database to store XML "natively". Other enhancements include OLTP-related improvements for distributed platforms, business intelligence/data warehousing-related improvements for z/OS, more self-tuning and self-managing features, additional 64-bit exploitation (especially for virtual storage on z/OS), stored procedure performance enhancements for z/OS, and continued convergence of the SQL vocabularies between z/OS and distributed platforms.

In October 2007, IBM announced "Viper 2," which is the codename for DB2 9.5 on the distributed platforms. There were three key themes for the release, Simplified Management, Business Critical Reliability and Agile XML development.

In June 2009, IBM announced "Cobra" (the codename for DB2 9.7 for LUW). DB2 9.7 added data compression for database indexes, temporary tables, and large objects. DB2 9.7 also supported native XML data in hash partitioning (database partitioning), range partitioning (table partitioning), and multi-dimensional clustering. These native XML features allows users to directly work with XML in data warehouse environments. DB2 9.7 also added several features that make it easier for Oracle Database users to work with DB2. These include support for the most commonly used SQL syntax, PL/SQL syntax, scripting syntax, and data types from Oracle Database. DB2 9.7 also enhanced its concurrency model to exhibit behavior that is familiar to users of Oracle Database and Microsoft SQL Server.

In October 2009, IBM introduced its second major release of the year when it announced DB2 pureScale. DB2 pureScale is a database cluster solution for non-mainframe platforms, suitable for Online transaction processing (OLTP) workloads. IBM based the design of DB2 pureScale on the Parallel Sysplex implementation of DB2 data sharing on the mainframe. DB2 pureScale provides a fault-tolerant architecture and shared-disk storage. A DB2 pureScale system can grow to 128 database servers, and provides continuous availability and automatic load balancing.

In 2009, it was announced that DB2 can be an engine in MySQL. This allows users on the System i platform to natively access the DB2 under the IBM i operating system (formerly called AS/400), and for users on other platforms to access these files through the MySQL interface. On the System i and its predecessors the AS/400 and the System/38, DB2 is tightly integrated into the operating system, and comes as part of the operating system. It provides journaling, triggers and other features.

In early 2012, IBM announced the next version of DB2, DB2 10.1 (code name Galileo) for Linux, UNIX, and Windows. DB2 10.1 contained a number of new data management capabilities including row and column access control which enables ‘fine-grained’ control of the database and multi-temperature data management that moves data to cost effective storage based on how "hot" or "cold" (how frequently the data is accessed) the data is. IBM also introduced ‘adaptive compression’ capability in DB2 10.1, a new approach to compressing data tables.

In June 2013, IBM released DB2 10.5 (code name "Kepler").

On 12 April 2016, IBM announced DB2 LUW 11.1, and in June 2016, it was released.

In mid-2017, IBM re-branded its DB2 and dashDB product offerings and amended their names to "Db2".

Editions

IBM offers eight editions: Advanced Enterprise Server Edition, Enterprise Server Edition, Advanced Workgroup Server Edition, Workgroup Server Edition, Direct and Developer Editions and Express-C.

IBM DB2 Express-C

IBM DB2 Express-C
Developer(s)IBM
Initial release2006 (2006)
Stable release
DB2 Express-C (11.1) / June 15, 2016; 8 years ago (2016-06-15)
Written inC, C++
Operating systemCross-platform
Available inEnglish
TypeRDBMS
LicenseProprietary EULA
WebsiteDB2 Express-C homepage at ibm.com

IBM DB2 Express-C is a free to download, use and redistribute edition of the IBM DB2 data server, which has both XML database and relational database management system features.[13] It is limited to two CPU cores, 16 GB of RAM, a database size of 15 TB, and no Enterprise support and fix packs. DB2 Express-C has no limit on number of users.

History

On January 30, 2006, IBM announced a special free version of DB2 Express edition called DB2 Express-C. The DB2 Express-C edition was created for the 8.2 release of IBM DB2. After this DB2 Express-C was created for all new DB2 versions: 9.1 (codenamed "Viper"), 9.5 (codename "Viper 2"), 9.7 (codename "Cobra"), 10.01 (codename "Galileo"), 10.5 (codename "Kepler") and 11.1.

The IBM DB2 pureXML implementation of XML database features was introduced in the beta of DB2 9.

Supported environments

IBM DB2 Express C supports 32- and 64-bit x86 Windows XP, Vista and Windows 7, (with some issues in Windows 7). Linux version runs on 32- and 64-bit Intel/AMD and 64-bit IBM POWER. Solaris and Mac OS X versions are supported on x64 AMD/Intel machines only. List of supported Linux distributions: Ubuntu 8.04.1 LTS, RHEL 5, SLES 10 and 11, openSUSE 12.3. Other distributions are reported to work too and released .deb packages for Ubuntu works also on Debian.

Limitations

IBM DB2 Express-C prior to version 10.5 did not limit the amount of data that can be stored in a database; as of version 10.5, there is a 15 TB limit. The database engine does not limit the number of concurrent user connections. IBM offers 64-bit native versions of DB2 Express-C for all supported platforms, but 32-bit versions are still available for some environments. Remote applications can connect to DB2 Express-C over TCP/IP V4, TCP/IP V6, or named pipes. The DB2 Express-C database engine is limited to use up to 16 GB RAM (increase from 4 GB with v10.5) and two CPU cores, but it can be installed and used on machines of any size, with the remaining memory and CPU available for use by the O/S and other applications.

The DB2 Express-C feature set is very similar to DB2 Express edition (a paid product), but the main difference is that Express-C has lower CPU and memory limits. It has the following extra features enabled:

  • Backup compression
  • Homogeneous federation – only DB2, Informix Data Server and Oracle targets are supported
  • Homogeneous SQL replication
  • Net Search Extender
  • XML storage
  • Spatial extender

The following extra features are not supported: Advanced Copy Services, row compression, connection concentrator, DBF partitioning, DB2 governor, PureScale data sharing, geodetic extender, HADR, Homogeneous Q replication, Sybase compatibility, Label Based Access Control, Row and Column Access Control (RCAC), MQT's, MDC, online reorg, query parallelism, query patroller, table partitioning and workload management.

DB2 Express-C is not permitted for use in high availability environments such as involving replication, active-passive, or shared disk clustering. In-place updates of the DB2 software (via patch sets called fix packs) are not available for DB2 Express-C; the existing DB2 Express-C engine must be removed and replaced with a newer release of DB2 Express-C. In either scenario, the DB2 database(s) remain intact on the server and are not touched by the software installation utility. IBM only offers the current version of Express-C on their website, but users may choose to install and run older releases of DB2 Express-C that they have previously downloaded from IBM.

Updates

DB2 Express-C version is unsupported and regular DB2 fix packs can not be applied to it. IBM does not release any fixes, but they do publish updated installation images and remove old ones. Unix versions need to be reinstalled, but it is possible to perform in-place updates on Windows versions by just running the installation program of a newer version. If you need access to regular DB2 fix packs, which are released several times per year, you need to buy DB2 Express Edition (or better).

Installation images are traditionally refreshed once for every major DB2 release to sync code with second fix pack. For example, DB2 Express-C for 9.1 release is available as DB2 Express-C 9.1 and DB2 Express-C 9.1.2. DB2 9.7 Express-C was refreshed to 9.7.1 breaking that tradition. DB2 Express-C v10.01 was made available on April 30, 2012.

Subscription

In DB2 9.7 there are no more annual subscriptions for DB2 Express-C product. Subscription features were moved to the DB2 Express (non "-C") product instead with price tag unchanged. For upgrading DB2 Express-C to DB2 Express you need to reinstall DB2. Uninstalling DB2 does not delete your data or database (which can be recatalogued after a new version of DB2 is installed).

Annual FTL (fixed term license) for DB2 Express is licensed per server and increases resource limits to 8 CPU cores and 64 GB RAM (as of v10.5[14]) and adds high availability pack. This pack includes: HADR (active–passive cluster solution), provides asynchronous replication, ACS (Advanced Copy Services) for storage-based (snapshot) data backup and restore, online table reorganizations and federation support for other DB2 servers. For HADR or replication you must have one FTL license per server. With DB2 Express you can get standard DB2 support features: access to regular DB2 fix packs, access to DB2 tech and phone support, and access to old but still supported DB2 versions.

Others

DB2 for z/OS (the mainframe) is available in its traditional product packaging, or in the Value Unit Edition, which allows customers to instead pay a one-time charge.

DB2 also powers IBM InfoSphere Warehouse, which offers data warehouse capabilities. InfoSphere Warehouse is available for z/OS. It includes several BI features such as ETL, data mining, OLAP acceleration, and in-line analytics.

DB2 10.5 for Linux, UNIX and Windows, contains all of the functionality and tools offered in the prior generation of DB2 and InfoSphere Warehouse on Linux, UNIX and Windows.

Cloud services

IBM provides three cloud services for DB2 on its Bluemix platform:

  • Db2 on Cloud: A fully managed version of Db2 on the cloud.
  • Db2 Hosted: An unmanaged, hosted version of Db2.
  • IBM dashDB: A managed cloud database based on the DB2 engine, with additional capabilities from the Netezza engine. Inside the dashDB family, there are two editions: IBM dashDB for Analytics (data warehouse), and dashDB Local (a Docker container image allowing deployment on private clouds.)

On June 22, 2017, IBM renamed its cloud offerings.[15] Originally, Db2 on Cloud was named "dashDB for Transactions" and "Db2 on Cloud" referred to what is now "Db2 Hosted".

Technical information

DB2 can be administered from either the command-line or a GUI. The command-line interface requires more knowledge of the product but can be more easily scripted and automated. The GUI is a multi-platform Java client that contains a variety of wizards suitable for novice users. DB2 supports both SQL and XQuery. DB2 has native implementation of XML data storage, where XML data is stored as XML (not as relational data or CLOB data) for faster access using XQuery.

DB2 has APIs for Rexx, PL/I, COBOL, RPG, Fortran, C++, C, Delphi, .NET CLI, Java, Python, Perl, PHP, Ruby, and many other programming languages. DB2 also supports integration into the Eclipse and Visual Studio integrated development environments.

pureQuery is IBM's data access platform focused on applications that access data. pureQuery supports both Java and .NET. pureQuery provides access to data in databases and in-memory Java objects via its tools, APIs, and runtime environment as delivered in IBM Data Studio Developer and IBM Data Studio pureQuery Runtime.[16]

Error processing

An important feature of Db2 computer programs is error handling. The SQL communications area (SQLCA) structure was once used exclusively within a Db2 program to return error information to the application program after every SQL statement was executed. The primary, but not singularly useful, error diagnostic is held in the field SQLCODE within the SQLCA block.

The SQL return code values are:

  • 0 means successful execution.
  • A positive number means successful execution with one or more warnings. An example is +100, which means no rows found.
  • A negative number means unsuccessful with an error. An example is -911, which means a lock timeout (or deadlock) has occurred, triggering a rollback.

Later versions of Db2 added functionality and complexity to the execution of SQL. Multiple errors or warnings could be returned by the execution of an SQL statement; it may, for example, have initiated a database trigger and other SQL statements. Instead of the original SQLCA, error information should now be retrieved by successive executions of a GET DIAGNOSTICS statement.

See SQL return codes for a more comprehensive list of common SQLCODEs.

See also

References

  1. ^ "Db2 on Cloud".
  2. ^ "Db2 11 - Introduction - What is Db2 for z/OS for z/OS?".
  3. ^ Pam Baker (July 23, 2018). "IBM Db2 on Cloud Review & Rating". PC Magazine. IBM Db2 on Cloud is a dream Database-as-a-Service (DBaaS) solution for developers and business analysts because
  4. ^ "Db2 for z/OS". Db2 for z/OS is a relational database that you can use to maintain large volumes of data with a high level of availability, security, and performance.
  5. ^ "IBM z/VSE V6.2". October 10, 2017. IBM z/VSE V6.2 is capable as of December 1, 2017, when used in .... Db2 Server for VSE and VM and Db2 Server for VSE Client Edition
  6. ^ "DB2 database -> does it continue to be platform-specific? (iSeries, LUW, z/OS)".
  7. ^ Codd, E. F. (June 1970). "A Relational Model of Data for Large Shared Data Banks" (PDF). Communications of the ACM. 13 (6): 377–387. doi:10.1145/362384.362685.
  8. ^ Chamberlin, Donald D.; et al. (October 1981). "A History and Evaluation of System R" (PDF). Communications of the ACM. 24 (10). doi:10.1145/358769.358784.
  9. ^ Codd, E.F. (1971). "A data base sublanguage founded on the relational calculus". SIGFIDET '71 Proceedings of the 1971 ACM SIGFIDET (now SIGMOD) Workshop on Data Description, Access and Control. doi:10.1145/1734714.1734718.
  10. ^ "SQL SERVER – What is SQL? How to pronounce SQL?".
  11. ^ Basu, Dipankar (October 25, 1982). "Relational Models Viable in Commercial World". Computerworld. Retrieved February 23, 2013.
  12. ^ "Introduction to DB2 UDB". Apr 22, 2005. Retrieved 2010-03-29. Since the 1970s, when IBM Research invented the Relational Model and the Structured Query Language (SQL), IBM has developed a complete family of RDBMS software. Development started on mainframe platforms such as Virtual Machine (VM), Virtual Storage Extended (VSE), and Multiple Virtual Storage (MVS). In 1983, DB2 for MVS Version 1 was born. "DB2" was used to indicate a shift from hierarchical databases, like the Information Management System (IMS) popular at the time, to the new relational databases.
  13. ^ "About IBM DB2 Express-C". IBM. April 2009. Retrieved 2009-04-07.
  14. ^ "Which distributed edition of DB2 10.5 is right for you?". IBM. April 2015. Retrieved 2016-03-26.
  15. ^ https://www.ibm.com/blogs/bluemix/2017/06/ibm-dashdb-transactions-db2-cloud-renamed/
  16. ^ "Getting Started with pureQuery". www.ibm.com. IBM. 20 October 2009. Retrieved 11 June 2016.
Wikibooks has a book on the topic of: Structured Query Language/Return Codes
Retrieved from "https://en.wikipedia.org/w/index.php?title=IBM_Db2&oldid=856403641"