DB2 is one of IBM's families of relational database management system (or, as IBM now calls it, data server) software products within IBM's broader Information Management Software line. International Business Machines Corporation abbreviated IBM and nicknamed "Big Blue", is a multinational Computer Technology A Relational database management system (RDBMS is a Database management system (DBMS that is based on the Relational model as introduced by E Information Management Software is one of the brands within IBM 's Software Group division Although there are different "editions" and "versions" of DB2 which run on devices ranging from handhelds to mainframes, most often DB2 refers to DB2 Enterprise Server Edition or the top-of-the-line DB2 Data Warehouse Edition (DB2 DWE), which runs on Unix, Windows or Linux servers; or DB2 for z/OS. A mobile device (also known as cellphone device, handheld device, handheld computer, "Palmtop" or simply handheld) is a pocket-sized Mainframes (often colloquially referred to as Big Iron) are Computers used mainly by large organizations for critical applications typically bulk data DB2 is one of IBM 's families of Relational database management system (RDBMS (or as IBM now calls it data server software products within IBM's broader Information Unix (officially trademarked as UNIX, sometimes also written as Unix with Small caps) is a computer Microsoft Windows is a series of Software Operating systems and Graphical user interfaces produced by Microsoft. Linux (commonly pronounced ˈlɪnəks z/OS is a 64-bit Operating system for Mainframe computers, created by IBM. Beside DB2 there exists Informix, which was acquired by IBM in 2001. Informix is a family of Relational database management system (RDBMS products by IBM.
Contents |
History
DB2 has a long history and is considered by many to have been the first database product to use SQL (also first developed by IBM).
The name DB2 was first given to the Database Management System or DBMS in 1983 when IBM released DB2 on its MVS mainframe platform. A database management system ( DBMS) is Computer software designed for the purpose of managing Databases DBMSes may use any of a variety of Data models Prior to this, a similar product was named SQL/DS on the VM mainframe. SQL/DS (Structured Query Language/Data System was IBM 's first commercial implementation for its mainframe Computers of a DBMS (Database Management The earlier System 38 platform also contained a relational DBMS. System Relational, or System R, was a research prototype developed in the 1970s. IBM System R is a Database system built as a research project at IBM San Jose Research (now IBM Almaden Research Center) in the 1970s IBM System R is a Database system built as a research project at IBM San Jose Research (now IBM Almaden Research Center) in the 1970s DB2 has its roots back to the beginning of the seventies when Dr. E.F. Codd, working for IBM, described the theory of relational databases and in June of 1970 published the model for data manipulation. Edgar Frank "Ted" Codd ( August 23, 1923 – April 18, 2003) was a British computer scientist who while working To apply the model Codd needed a relational database language which he named Alpha. 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 SQL to differentiate it from SEQUEL. SQL, contrary to popular belief, does not stand for Structured Query Language as "it breaks the cardinal rule of structured languages by allowing branches to remote locations. "(Allen G. Taylor, 2004)
Historically, it is interesting to note that when Informix 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. Informix is a family of Relational database management system (RDBMS products by IBM. Illustra was a commercialized version of the Postgres object-relational database management system ( DBMS) sold by Illustra Information Technologies a company formed An object-relational database ( ORD) or object-relational database management system ( ORDBMS) is a Database management system (DBMS similar Moreover, in 2001, IBM bought Informix and in the following years incorporated Informix technology into the DB2 product suite. Informix is a family of Relational database management system (RDBMS products by IBM. Today, DB2 can technically be considered to be an object-SQL DBMS. An object-relational database ( ORD) or object-relational database management system ( ORDBMS) is a Database management system (DBMS similar
For some years DB2, as a full-function DBMS, was exclusively available on IBM mainframes. IBM mainframes, though perceived as synonymous with Mainframe computers in general due to their marketshare are now technically and specifically IBM 's line of business Later IBM brought DB2 to other platforms, including OS/2, UNIX and Windows servers, then Linux (including Linux on zSeries) and PDAs. OS/2 is a computer Operating system, initially created by Microsoft and IBM, then later developed by IBM exclusively Unix (officially trademarked as UNIX, sometimes also written as Unix with Small caps) is a computer Microsoft Windows is a series of Software Operating systems and Graphical user interfaces produced by Microsoft. Linux (commonly pronounced ˈlɪnəks Linux on System z is the collective term for the Linux operating system compiled to run on IBM mainframes especially System z machines This process was mainly undertaken through the 1990s. The inspiration for the mainframe version of DB2's architecture came in part from IBM DL/1 and IBM IMS, both initially hierarchical and then later network (or CODASYL) databases. Data Language/1 (DL/1 DL/I is the language system used to access IBM ’s IMS Databases and its data communication system IBM Information Management System ( IMS) is a joint Hierarchical database and Information management system with extensive Transaction processing CODASYL (often spelt Codasyl) is an Acronym for "Conference on Data Systems Languages" 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. IBM i is an Operating system used on IBM Power Systems, a unified server platform from the former IBM System i and IBM System p servers The IBM System i is IBM's previous generation of systems designed for IBM i users and was subsequently replaced by the IBM Power Systems in April 2008 The IBM System i is IBM's previous generation of systems designed for IBM i users and was subsequently replaced by the IBM Power Systems in April 2008 The IBM System i is IBM's previous generation of systems designed for IBM i users and was subsequently replaced by the IBM Power Systems in April 2008 z/VSE (Virtual Storage Extended is an Operating system for IBM mainframe computers the latest one in the DOS/360 lineage, which originated in 1965 See also VM (operating system z/VM is the current version in IBM's VM family of Virtual machine Operating systems.
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. OS/2 is a computer Operating system, initially created by Microsoft and IBM, then later developed by IBM exclusively IBM extended the functionality of Database Manager a number of times, including the addition of distributed database functionality that allowed shared access to a database in a remote location on a LAN. Eventually IBM declared that insurmountable complexity existed in the Database Manager code, and took the difficult decision to completely rewrite the software in their Toronto Lab. The new version of Database Manager, called DB2 like its mainframe parent, ran on the OS/2 and RS/6000 platforms, 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), with the introduction of widespread confusion over which version (mainframe or server) of the DBMS was being referred to. At this point, the mainframe version of DB2 and the server version of DB2 were coded in entirely different languages (PL/S for the mainframe and C++ for the server), but shared very similar functionality and used a common architecture for SQL optimization: the Starburst Optimizer.
Over the years DB2 has both exploited and driven numerous hardware enhancements, particularly on IBM System z with such features as Parallel Sysplex data sharing. IBM System z, or earlier IBM eServer zSeries, is a brand name designated by IBM to all its Mainframe computers In 2000 IBM rebranded the existing In computing a Parallel Sysplex is a cluster of IBM mainframes acting together in a single system image usually with Z/OS. In fact, DB2 UDB Version 8 for z/OS now requires a 64-bit system and cannot run on earlier processors, and DB2 for z/OS maintains certain unique software differences in order to serve its sophisticated customers. z/OS is a 64-bit Operating system for Mainframe computers, created by IBM. '64-bit' CPUs have existed in Supercomputers since the 1960s and in RISC -based workstations and servers since the early 1990s. Although the ultimate expression of software-hardware co-evolution is the IBM mainframe, to some extent that phenomenon occurs on other platforms as well, as IBM's software engineers collaborate with their hardware counterparts.
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. A shared nothing architecture (SN is a Distributed computing architecture in which each node is independent and self-sufficient and there is no Single point of contention 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 sells it as an add-on to 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. Distributed computing deals with Hardware and Software Systems containing more than one processing element or Storage element concurrent DB2 9 for z/OS was announced in early 2007. z/OS is a 64-bit Operating system for Mainframe computers, created by IBM. IBM claims that the new DB2 will be the first relational database to store XML "natively". Don't change "Extensible" 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. Online transaction processing, or OLTP, refers to a class of systems that facilitate and manage transaction-oriented applications typically for data entry and retrieval Business intelligence ( BI) refers to technologies applications and practices for the collection integration analysis and presentation of business Information and A data warehouse is a Repository of an organization's electronically stored data A stored procedure is a Subroutine available to applications accessing a relational database system.
DB2 9 on distributed platforms began shipping worldwide on July 28, 2006, with pricing starting at $4,874 per processor or $165 per user (minimum of 5 users) for DB2 9 Express, including one year of support. Distributed computing deals with Hardware and Software Systems containing more than one processing element or Storage element concurrent Events 1540 - Thomas Cromwell is executed at the order of Henry VIII of England on charges of Treason. Year 2006 ( MMVI) was a Common year starting on Sunday of the Gregorian calendar. [1] DB2 for z/OS pricing starts at about $4,450 per month, including support.
Editions
DB2 is available in several "editions," or licensing arrangements. By opting for a reduced-feature "edition," IBM allows customers to avoid paying for database features which they do not need. Sample editions include the Express, Workgroup, and Enterprise Edition. The most sophisticated edition for Linux/UNIX/Windows is DB2 Data Warehouse Enterprise Edition, or DB2 DWE for short. This edition is intended for mixed workload (OLTP and data warehousing) or business intelligence (BI) implementations. Business intelligence ( BI) refers to technologies applications and practices for the collection integration analysis and presentation of business Information and DB2 DWE includes several "BI" features such as ETL, data mining, OLAP acceleration, and in-line analytics. Extract Transform and Load ( ETL) is a process in data warehousing that involves extracting data from outside sources Data mining is the process of Sorting through large amounts of data and picking out relevant information Online Analytical Processing or OLAP (ˈoʊlæp is an approach to quickly provide answers to analytical queries that are multi-dimensional in nature
DB2 for z/OS is available under its own licensing terms. z/OS is a 64-bit Operating system for Mainframe computers, created by IBM. Starting with Version 8, IBM brought DB2 for z/OS and for the other platforms into much closer alignment. (Previously there were significant differences in SQL vocabularies, for example. ) DB2 for z/OS has some exclusives — notably Multi-Level Security (MLS), extremely large table sizes, and hardware-assisted compression — owing to its special environment and the demanding needs of its customers. DB2 for z/OS has always been known for its leading OLTP performance and capabilities, and for its reliability and availability to support mission-critical business operations, but the z/OS version is now starting to acquire BI features as well, such as materialized query tables (MQTs) and star schema. Oracle's CEO Larry Ellison commented that DB2 for z/OS is the one competitive database he respects and admires. Oracle Corporation ( specializes in developing and marketing Enterprise software products — particularly Database management systems In 2007 Oracle ranked Lawrence Joseph "Larry" Ellison (born August 17, 1944) is an American entrepreneur and the co-founder and CEO of Oracle Corporation [2]
On January 30, 2006, IBM released a no-charge version of DB2 called DB2 Express-C. This was an expected response to the recently announced free versions of Oracle 10g and Microsoft SQL Server. Oracle Database (commonly referred to as Oracle RDBMS or simply Oracle) is a Relational database management system (RDBMS produced and marketed by Microsoft Corporation is an American multinational Computer technology Corporation, which rose to dominate the Home computer Unlike Microsoft or Oracle's free editions, Express-C has no limit on number of users or on database size. While versions 8. 2 and 9. 1 of DB2 Express-C imposed hardware limits on the server on which it ran, DB2 Express-C 9. 5 can run on Windows and Linux machines of any size, but the database engine will not utilize more than two CPU cores and 2GB RAM. In 2007, IBM introduced a yearly support subscription called the Fixed Term License (FTL), which offers a year of telephone support for Express-C for US$3000 per server. Purchasing the FTL also allows the DB2 Express-C engine to use up to four CPU cores and 4GB RAM. Users of DB2 Express-C who don't purchase an FTL subscription can receive support and assistance on a free, public web forum staffed by IBM technicians and other DB2 users.
Competition
Historically, prime position in the database management software market has been held by Oracle. On May 3, 2004, IBM's head of database development and sales, Janet Perna, claimed their main competitors were Oracle in the context of advanced transaction handling and Teradata in the context of decision support systems (e. Teradata Corporation ( is a hardware and software vendor specializing in data warehousing and analytic applications Decision support systems constitute a class of computer-based Information systems including knowledge-based systems that support Decision-making activities g. data warehousing). A data warehouse is a Repository of an organization's electronically stored data However, there are competitors in smaller markets, including Microsoft SQL Server (which is only available for Microsoft Windows), open source products such as FirebirdSQL, PostgreSQL and MySQL, and niche players such as Sybase and MaxDB. Microsoft SQL Server is a Relational database management system (RDBMS produced by Microsoft. Open source is a development methodology which offers practical accessibility to a product's source (goods and knowledge Firebird (sometimes erroneously called FirebirdSQL is a Relational database management system offering many ANSI features PostgreSQL is an Object-relational database management system (ORDBMS MySQL is a Relational database management system (RDBMS which has more than 11 million installations Sybase Inc ( is an enterprise software and services company exclusively focused on managing and mobilizing information MaxDB is an ANSI SQL-92 (entry level compliant relational database management system ( RDBMS) from SAP AG, which was delivered also
In the clustered DBMS arena, where databases can grow to many terabytes, IBM's Database Partitioning Feature (DPF) is often pitted against Oracle Real Application Clusters (RAC), a shared-disk implementation formerly known as Oracle Parallel Server (OPS).
DB2 for z/OS arguably has fewer direct competitors, but Oracle is attracting customers to its Linux on zSeries products, although apparently not at the expense of DB2. CA-Datacom and Software AG's ADABAS are competing relational databases for z/OS, and there are certain niche products as well (Model 204, SUPRA SQL[3], NOMAD, etc. DATACOM/DB was a Database management system of the Inverted file type ADABAS ( Acronym for Adaptable DAta BAse System is Software AG ’s primary Database management system Model 204 is a DBMS for IBM and compatible mainframes, which was first deployed in 1972. Nomad is a Relational database and Fourth-generation language (4GL originally developed in the 70s by Time-sharing vendor National CSS Inc ) Oracle has a 31-bit RDBMS available for z/OS. Perhaps the only computing architecture based on 31-bit addressing is one of computing's most famous and most profitable Non-relational databases that "compete" include IMS, and CA-IDMS, among others. IBM Information Management System ( IMS) is a joint Hierarchical database and Information management system with extensive Transaction processing IDMS (Integrated Database Management System is a ( network) CODASYL Database management system first developed at B
IBM and DB2 are frequently at or near the top of the TPC-C and TPC-H industry benchmarks published on the Transaction Processing Performance Council's website. Transaction Processing Performance Council (TPC is a non-profit organization founded in 1988 to define Transaction processing and Database benchmarks and to disseminate Transaction Processing Performance Council (TPC is a non-profit organization founded in 1988 to define Transaction processing and Database benchmarks and to disseminate Transaction Processing Performance Council (TPC is a non-profit organization founded in 1988 to define Transaction processing and Database benchmarks and to disseminate
In 2006 IBM stepped up its competition in the emerging data warehouse appliance market by releasing a product line of pre-configured hardware/software systems combining DB2 Data Warehouse Edition with either IBM system p (AIX) or IBM system x (Linux) servers. This family of "warehouse appliance-like" systems was given the name IBM Balanced Configuration Unit, or BCU, and is aimed at the warehouse appliance market typified by Netezza and DATAllegro, but it differentiates itself in that it uses the full-featured version of DB2 instead of a single-purpose warehouse-oriented RDBMS.
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. XQuery is a Query language (with some Programming language features that is designed to query collections of XML data 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 .NET CLI, Java, Python, Perl, PHP, Ruby, C++, C, REXX, PL/I, COBOL, RPG, FORTRAN, and many other programming languages. The Common Language Infrastructure (CLI is an open Specification (published under ECMA-335 and ISO/IEC 23271 developed by Microsoft that describes the executable Python is a general-purpose High-level programming language. Its design philosophy emphasizes programmer productivity and code readability NOTES FOR EDITORS "Perl" is not an acronym (read the "Name" section below PHP is a computer Scripting language. Originally designed for producing Dynamic web pages it has evolved to include a Command line interface capability Ruby is a dynamic, reflective, general purpose Object-oriented programming language that combines syntax inspired by Perl with Smalltalk C++ (" C Plus Plus " ˌsiːˌplʌsˈplʌs is a general-purpose Programming language. tags please moot on the talk page first! --> In Computing, C is a general-purpose cross-platform block structured REXX (REstructured eXtended eXecutor is an interpreted Programming language which was developed at IBM. PL/I ("Programming Language One" ˌpiːˌɛlˈwʌn is an imperative computer Programming language designed for scientific engineering COBOL (ˈkoʊbɒl is one of the oldest programming languages still in active use RPG is a Programming language for business applications Originally an initialism for Report Program Generator, it officially no longer stands for anything Fortran (previously FORTRAN) is a general-purpose, procedural, imperative Programming language that is especially suited to DB2 also supports integration into the Eclipse and Visual Studio .NET integrated development environments. In Computing, Eclipse is a Software platform comprising extensible Application frameworks tools and a Runtime library for Software Microsoft Visual Studio is the main Integrated Development Environment (IDE from Microsoft. In Computing, an integrated development environment ( IDE) is a Software application that provides comprehensive facilities to Computer programmers
Error processing
An important feature of DB2 computer programs is error handling. Computer programs (also software programs, or just programs) are instructions for a Computer. 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. Application software is a subclass of Computer software that employs the capabilities of a computer directly and thoroughly to a task that the user wishes to perform The primary, but not singularly useful, error diagnostic is held in the field SQLCODE within the SQLCA block. SQL Return Codes are used on a day to day basis for the diagnosis of programming failures as a result of SQL calls by DB2 Computer programs.
The SQL return code values are:
- 0 means successful execution. SQL Return Codes are used on a day to day basis for the diagnosis of programming failures as a result of SQL calls by DB2 Computer programs.
- 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. A database trigger is Procedural code that is automatically executed in response to certain events on a particular table in a Database. 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. SQL Return Codes are used on a day to day basis for the diagnosis of programming failures as a result of SQL calls by DB2 Computer programs.
Training and Certification
IBM Information Management Training and Certification accelerates DB2 learning with IBM product experts. International Business Machines Corporation abbreviated IBM and nicknamed "Big Blue", is a multinational Computer Technology Take advantage of Instructor Led Online (ILO) — an innovative new training option that combines the benefits of classroom training with the cost savings of an online course.
IBM Training includes a complete set of core Data Servers Training courses that apply to both DB2 for LUW and DB2 for z/OS. These courses delve into many essential DB2 concepts, from fundamentals to advanced SQL topics.
User Groups
IDUG is the International DB2 Users Group, an independent, not-for-profit association of IT professionals who use IBM’s leading relational database management system (RDBMS), DB2. The International DB2 Users Group (IDUG is an independent not-for-profit association of IT professionals who use IBM 's leading relational database management system IDUG provides education, technical resources, peer networking opportunities, online resources and other programs that enable DB2 users to enhance their ability to leverage DB2 to achieve personal and professional objectives.
Leadership
Janet Perna was General Manager of IBM Software Group's Information Management division until her retirement from IBM in July, 2005. She was replaced by Ambuj Goyal. [4]
See also
References
- ^ IBM press release June 8, 2006
- ^ "In Larry's Own Words", October, 2003
- ^ SUPRA 4GL
- ^ Ambuj Goyal
External links
- IDUG, the International DB2 Users Group
- IBM DB2 Training and Certification
- IBM DB2 Express-C 9.5 Free Download
- IBM DB2 9 Demo
- IBM DB2 Support Forums
- IBM Database Magazine
- Portal containing DB2 technical information and blog
- DB2usa - Links to DB2 for z/OS documents available on the web
- DB2 Podcast Series