IBM DB2 - AbsoluteAstronomy.com

The IBM DB2 Enterprise Server Edition is a relational model

Relational model

The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...

database server

Database server

A database server is a computer program that provides database services to other computer programs or computers, as defined by the client–server model. The term may also refer to a computer dedicated to running such a program...

developed by IBM

IBM

International Business Machines Corporation or IBM is an American multinational technology and consulting corporation headquartered in Armonk, New York, United States. IBM manufactures and sells computer hardware and software, and it offers infrastructure, hosting and consulting services in areas...

. It primarily runs on Unix

Unix

Unix is a multitasking, multi-user computer operating system originally developed in 1969 by a group of AT&T employees at Bell Labs, including Ken Thompson, Dennis Ritchie, Brian Kernighan, Douglas McIlroy, and Joe Ossanna...

(namely AIX), Linux

Linux

Linux is a Unix-like computer operating system assembled under the model of free and open source software development and distribution. The defining component of any Linux system is the Linux kernel, an operating system kernel first released October 5, 1991 by Linus Torvalds...

, IBM i (formerly OS/400

OS/400

IBM i is an EBCDIC based operating system that runs on IBM Power Systems. It is the current evolution of the operating system named i5/OS which was originally named OS/400 when it was introduced with the AS/400 computer system in 1988....

), z/OS

Z/OS

z/OS is a 64-bit operating system for mainframe computers, produced by IBM. It derives from and is the successor to OS/390, which in turn followed a string of MVS versions.Starting with earliest:*OS/VS2 Release 2 through Release 3.8...

and Windows

Microsoft Windows

Microsoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...

servers. DB2 also powers the different IBM InfoSphere Warehouse editions. Alongside DB2 is another RDBMS

Relational database management system

A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....

: Informix

Informix

IBM Informix is a family of relational database management system developed by IBM. It is positioned as IBM's flagship data server for online transaction processing as well as integrated solutions...

, which was acquired by IBM in 2001.

History

DB2 has a long history and is considered by many to have been the first database product to use SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

(also developed by IBM

IBM

) although Oracle

Oracle Corporation

Oracle Corporation is an American multinational computer technology corporation that specializes in developing and marketing hardware systems and enterprise software products – particularly database management systems...

released a commercial SQL database product somewhat earlier than IBM did.

The name DB2 was first given to the Database Management System or DBMS in 1983 when IBM released DB2 on its MVS mainframe platform. Prior to this, a similar product was named SQL/DS

SQL/DS

SQL/DS was IBM's first commercial implementation for its mainframe computers of a DBMS built around the SQL language....

on the VM platform. The earlier System 38 platform also contained a relational DBMS. System Relational, or System R, was a research prototype developed in the 1970s. DB2 has its roots back to the beginning of the 1970s when E.F. Codd

Edgar F. Codd

Edgar Frank "Ted" Codd was an English computer scientist who, while working for IBM, invented the relational model for database management, the theoretical basis for relational databases...

, working for IBM, described the theory of relational databases and in June 1970 published the model for data manipulation. To apply the model Codd needed a relational database language 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 (System Query Language) SQL to differentiate it from SEQUEL.

When Informix

Informix

IBM Informix is a family of relational database management system developed by IBM. It is positioned as IBM's flagship data server for online transaction processing as well as integrated solutions...

acquired Illustra

Illustra

Illustra was a commercialized version of the Postgres object-relational database management system sold by Illustra Information Technologies, a company formed by Michael Stonebraker and Gary Morgenthaler and several of Michael Stonebraker's current and former students including: Wei Hong, Jeff...

and made their database engine an object-SQL DBMS

Object-relational database

An object-relational database , or object-relational database management system , is a database management system similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language...

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

Informix

IBM Informix is a family of relational database management system developed by IBM. It is positioned as IBM's flagship data server for online transaction processing as well as integrated solutions...

and in the following years incorporated Informix technology into the DB2 product suite. Today, DB2 can technically be considered to be an object-SQL DBMS

Object-relational database

.

For some years DB2, as a full-function DBMS, was exclusively available on IBM mainframe

IBM mainframe

IBM mainframes are large computer systems produced by IBM from 1952 to the present. During the 1960s and 1970s, the term mainframe computer was almost synonymous with IBM products due to their marketshare...

s. Later IBM brought DB2 to other platforms, including OS/2

OS/2

OS/2 is a computer operating system, initially created by Microsoft and IBM, then later developed by IBM exclusively. The name stands for "Operating System/2," because it was introduced as part of the same generation change release as IBM's "Personal System/2 " line of second-generation personal...

, UNIX

Unix

and Windows

Microsoft Windows

servers, then Linux

Linux

(including Linux on zSeries) and PDA

Personal digital assistant

A personal digital assistant , also known as a palmtop computer, or personal data assistant, is a mobile device that functions as a personal information manager. Current PDAs often have the ability to connect to the Internet...

s. This process occurred through the 1990s. The inspiration for the mainframe version of DB2's architecture came in part from IBM IMS

Information Management System

IBM Information Management System is a joint hierarchical database and information management system with extensive transaction processing capabilities.- History :...

, a hierarchical database, and its dedicated database manipulation language, IBM DL/I

Data Language Interface

Data Language Interface is the language system used to access IBM’s IMS databases, and its data communication system....

. 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

Z/VM

z/VM is the current version in IBM's VM family of virtual machine operating systems. z/VM was first released in October 2000 and remains in active use and development . It is directly based on technology and concepts dating back to the 1960s, with IBM's CP/CMS on the IBM System/360-67...

.

An earlier version of the code that would become DB2 LUW (Linux, Unix, Windows) was part of an Extended Edition component of OS/2

OS/2

called Database Manager. 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

Län

Län and lääni refer to the administrative divisions used in Sweden and previously in Finland. The provinces of Finland were abolished on January 1, 2010....

. 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), a name that had already been used for the Linux-Unix-Windows version, 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 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

IBM Parallel Sysplex

In computing, a Parallel Sysplex is a cluster of IBM mainframes acting together as a single system image with z/OS. Used for disaster recovery, Parallel Sysplex combines data sharing and parallel computing to allow a cluster of up to 32 systems to share a workload for high performance and high...

data sharing. In fact, DB2 UDB Version 8 for z/OS

Z/OS

now requires a 64-bit

64-bit

64-bit is a word size that defines certain classes of computer architecture, buses, memory and CPUs, and by extension the software that runs on them. 64-bit CPUs have existed in supercomputers since the 1970s and in RISC-based workstations and servers since the early 1990s...

system and cannot run on earlier processors, and DB2 for z/OS maintains certain unique software differences in order to serve its sophisticated customers. 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

Shared nothing architecture

A shared nothing architecture is a distributed computing architecture in which each node is independent and self-sufficient, and there is no single point of contention across the system...

, 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 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

Distributed computing

Distributed computing is a field of computer science that studies distributed systems. A distributed system consists of multiple autonomous computers that communicate through a computer network. The computers interact with each other in order to achieve a common goal...

and z/OS. DB2 9 for z/OS

Z/OS

was announced in early 2007. IBM claimed that the new DB2 was the first relational database to store XML

XML

Extensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards....

"natively". Other enhancements include OLTP-related improvements for distributed platforms, business intelligence

Business intelligence

Business intelligence mainly refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes....

/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

Stored procedure

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...

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

Distributed computing

. 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 adds data compression for database indexes, temporary tables, and large objects. DB2 9.7 also supports 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 adds 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

IBM 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

MySQL

MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...

. This allows users on the System i platform to natively access the DB2 under the IBM i operating system (formerly called OS/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 October 2010, IBM announced the general availability (GA) of DB2 10 for z/OS. DB2® 10 for z/OS® expands the value delivered to businesses by IBM's industry-leading mainframe data server through innovations in key areas:

Improved operational efficiencies for "out-of-the-box" DB2 CPU savings
Unsurpassed resiliency for business-critical information
Rapid application and warehouse deployment for business growth
Enhanced business analytics and data visualization solutions with QMF

Selected features that deliver these valuable benefits to any business include:

When compared to running on DB2 9, depending on the workload, customers may experience reduced CPU utilization
When compared to running DB2 9, up to five to ten times more concurrent users on a single subsystem by avoiding memory constraints
Greater concurrency for data management, data definition, and data access, including DDL, BIND, REBIND, PREPARE, utilities, and SQL
Additional online changes for data definitions, utilities, and subsystems
Improved security with better granularity for administrative privileges, data masking, and audit capabilities
Temporal or versioned data to understand system and business times at the database level (Bi-temporal feature is not available on Oracle or any other competing RDBMS products)
pureXML™ and SQL enhancements to simplify portability from other database solutions
Productivity improved for database administrators, application programmers, and systems administrators
QMF Classic Edition, an optional for-charge feature, providing greater interoperability with other programs plus improved queries, forms, diagnostics, performance, and resource control
QMF Enterprise Edition, an optional for-charge feature, supporting QMF-based dashboards with visually rich page-based reports, an enhanced security model, support for HTML, PDF, or Flash QMF report and dashboard outputs and simplified content authoring

IBM and SAP have cooperated very closely on DB2 10 for z/OS, so now SAP users can benefit from DB2's scalability and performance enhancements significantly that allow for further growth of SAP applications and consolidation of hardware landscape at the same time.

Editions

DB2 for Linux, Unix and Windows has three separate editions: Express Edition, Workgroup Server Edition, and Enterprise Server Edition. Each of these editions has different groups of features for different sized workloads. Applications built for lower editions of DB2 are guaranteed to work on higher editions but at a higher level of performance.

The no-charge version of DB2 is called DB2 Express-C

IBM DB2 Express-C

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.-History:...

. DB2 Express-C is in some ways similar to the open source databases such as MySQL

MySQL

and PostgreSQL

PostgreSQL

PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...

as it is offered unsupported, free of charge for unrestricted use including use in production environments. Users needing enterprise level support and fixpacks must buy any standard DB2 Edition. DB2 Express-C, however, is based on the same code as other DB2 for Linux, Unix and Windows editions and is not open source. DB2 Express-C is also similar to the free versions of Oracle database

Oracle database

The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....

and Microsoft

Microsoft

Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...

SQL Server, except that DB2 Express-C has no limit on number of users or on database size. DB2 Express-C runs on 32 and 64bit Windows, Linux on x86, x64 and POWER processors, Solaris on x64 CPU and Intel machines running Mac OS X. It can be installed on machines of any size, but the database engine will use only two CPU cores and 2GB of RAM. Support is provided on a free, public Web forum. For this edition there are no fixpacks or official support from IBM.

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 for i (the former DB2/400) is the third major incarnation of DB2. It is very closely incorporated into the operating system of the IBM System i

IBM System i

The IBM System i is IBM's previous generation of midrange computer systems for IBM i users, and was subsequently replaced by the IBM Power Systems in April 2008....

machines.

DB2 also powers IBM InfoSphere

IBM InfoSphere

IBM Infosphere is a branded product line from IBM under its Information Management Software brand, announced in February 2008, which includes software products from its WebSphere and Information Server product lines...

Warehouse, which offers data warehouse capabilities. InfoSphere Warehouse offers several different editions and is available for z/OS, Linux, Unix, and Windows platforms. It includes several BI

Business informatics

Business informatics or organizational informatics is a discipline combining information technology , informatics and management concepts. The BI discipline was created in Germany, from the concept of "Wirtschaftsinformatik"...

features such as ETL

Extract, transform, load

Extract, transform and load is a process in database usage and especially in data warehousing that involves:* Extracting data from outside sources* Transforming it to fit operational needs...

, data mining

Data mining

Data mining , a relatively young and interdisciplinary field of computer science is the process of discovering new patterns from large data sets involving methods at the intersection of artificial intelligence, machine learning, statistics and database systems...

, OLAP acceleration, and in-line analytics.

Competition

IDC's Worldwide Database Management Systems 2009–2013 Forecast and 2008 Vendor Shares ranks Oracle database

Oracle database

The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....

as the leader in DBMS marketing share, followed by IBM DB2 and then Microsoft SQL Server

Microsoft SQL Server

Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...

. Other competitors include open source

Open source

The term open source describes practices in production and development that promote access to the end product's source materials. Some consider open source a philosophy, others consider it a pragmatic methodology...

products such as Firebird

Firebird (database server)

Firebird is an open source SQL relational database management system that runs on Linux, Windows, and a variety of Unix. The database forked from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten ....

, PostgreSQL

PostgreSQL

, MySQL

MySQL

and Ingres, and niche players such as Sybase

Sybase

Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms....

and MaxDB

MaxDB

MaxDB is an ANSI SQL-92 compliant relational database management system from SAP AG, which was delivered also by MySQL AB from 2003 to 2007. MaxDB is targeted for large SAP environments e.g. mySAP Business Suite and other applications that require enterprise-level database functionality...

.

In 2009, Gartner declared that "IBM DB2 9.7 Shakes Up the DBMS Market With Oracle Compatibility". This headline refers to the addition to DB2 of several features that are familiar to users of Oracle Database, making it easier for people with Oracle Database skills to work with DB2. These new features include DB2 support for the most commonly used SQL, PL/SQL, and scripting syntax from Oracle Database. They also include DB2 support for additional data types and concurrency models.

In the clustered DBMS arena, where databases can grow to many terabytes, IBM offers two approaches that compete with Oracle Real Application Clusters (RAC): DB2 pureScale and DB2 Database Partitioning Feature (DPF). DB2 pureScale is a shared-disk database cluster solution that is ideal for high-capacity Online Transaction Processing (OLTP) workloads. DB2 DPF lets you partition your database across multiple servers or within a large SMP server, which is ideal for Online Analytical Processing (OLAP) workloads. (Note that DB2 DPF is sold as part of IBM InfoSphere

IBM InfoSphere

Warehouse, which is the name for DB2 when it is sold in data warehouse environments.)

DB2 for z/OS arguably has fewer direct competitors. Oracle is attracting customers to its Linux on System z products, although apparently not at the expense of DB2. Oracle has a 31-bit

31-bit

Perhaps the only computing architecture based on 31-bit addressing is one of computing's most famous and most profitable. In 1983, IBM introduced 31-bit addressing in the System/370-XA mainframe architecture as an upgrade to the 24-bit addressing of earlier models...

RDBMS available for z/OS (Oracle Database 10g Release 2), but Oracle found it difficult to compete with DB2's feature set on z/OS. Oracle has announced it will support 10g on z/OS as long as customers wish, but the company will not introduce future versions of its database product on z/OS. CA-Datacom

DATACOM/DB

Datacom/DB is a relational database management system for the mainframes. Originally developed by Insyte Datacom, later acquired by Applied Data Research, it is now owned by CA Technologies which renamed it to CA-Datacom/DB and later to CA Datacom/DB.- External links :****...

and Software AG's ADABAS

Adabas

ADABAS is Software AG’s primary database management system.- History :First released in 1970, ADABAS is considered by some to have been one of the earliest commercially available database products...

are competing databases for z/OS, and there are certain niche products as well (Model 204

Model 204

Model 204 is a Database management system for IBM and compatible mainframes, which was first deployed in 1972. It incorporates a programming language and an environment for application development. It can deal with very large databases and very high transaction loads.Model 204 relies on its own...

, SUPRA SQL, NOMAD

Nomad software

Nomad Software is a relational database and fourth-generation language , originally developed in the 70s by time-sharing vendor National CSS, Inc...

, etc.) Non-relational databases that "compete" include IMS

Information Management System

IBM Information Management System is a joint hierarchical database and information management system with extensive transaction processing capabilities.- History :...

, and CA-IDMS

IDMS

IDMS is primarily a network database management system for mainframes. It was first developed at B.F. Goodrich and later marketed by Cullinane Database Systems...

, among others. At least some open source databases are ostensibly compatible with z/OS UNIX System Services

UNIX System Services

UNIX System Services is a required, included component of z/OS. USS is a certified UNIX implementation optimized for mainframe architecture. It is the first UNIX 95 to not be derived from the AT&T source code...

.

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

Transaction Processing Performance Council

Transaction Processing Performance Council is a non-profit organization founded in 1988 to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry...

's website.

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

Netezza

Netezza designs and markets high-performance data warehouse appliances and advanced analytics applications for uses including enterprise data warehousing, business intelligence, predictive analytics and business continuity planning....

and DATAllegro

DATAllegro

DATAllegro was a company that specializes in datawarehousing applicances. It was founded by Stuart Frost in 2003 inspired by and as a competitor to Data warehouse appliance pioneer Netezza...

, 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

- Features :XQuery provides the means to extract and manipulate data from XML documents or any data source that can be viewed as XML, such as relational databases or office documents....

. 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

REXX

REXX is an interpreted programming language that was developed at IBM. It is a structured high-level programming language that was designed to be both easy to learn and easy to read...

, PL/I

PL/I

PL/I is a procedural, imperative computer programming language designed for scientific, engineering, business and systems programming applications...

, COBOL

COBOL

COBOL is one of the oldest programming languages. Its name is an acronym for COmmon Business-Oriented Language, defining its primary domain in business, finance, and administrative systems for companies and governments....

, RPG

RPG programming language

RPG is a high-level programming language for business applications.It has a long history, having been developed by IBM in 1959 as the Report Program Generator - a tool to replicate card processing on the IBM 1401 then updated to RPG II for the IBM System/3 in the late 1960s, and since evolved into...

, FORTRAN

Fortran

Fortran is a general-purpose, procedural, imperative programming language that is especially suited to numeric computation and scientific computing...

, C++

C++

C++ is a statically typed, free-form, multi-paradigm, compiled, general-purpose programming language. It is regarded as an intermediate-level language, as it comprises a combination of both high-level and low-level language features. It was developed by Bjarne Stroustrup starting in 1979 at Bell...

, C

C (programming language)

C is a general-purpose computer programming language developed between 1969 and 1973 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system....

, Delphi

Delphi

Delphi is both an archaeological site and a modern town in Greece on the south-western spur of Mount Parnassus in the valley of Phocis.In Greek mythology, Delphi was the site of the Delphic oracle, the most important oracle in the classical Greek world, and a major site for the worship of the god...

, .NET

.NET Framework

The .NET Framework is a software framework that runs primarily on Microsoft Windows. It includes a large library and supports several programming languages which allows language interoperability...

CLI

Common Language Infrastructure

The Common Language Infrastructure is an open specification developed by Microsoft and standardized by ISO and ECMA that describes the executable code and runtime environment that form the core of the Microsoft .NET Framework and the free and open source implementations Mono and Portable.NET...

, Java

Java (programming language)

Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...

, Python

Python (programming language)

Python is a general-purpose, high-level programming language whose design philosophy emphasizes code readability. Python claims to "[combine] remarkable power with very clear syntax", and its standard library is large and comprehensive...

, Perl

Perl

Perl is a high-level, general-purpose, interpreted, dynamic programming language. Perl was originally developed by Larry Wall in 1987 as a general-purpose Unix scripting language to make report processing easier. Since then, it has undergone many changes and revisions and become widely popular...

, PHP

PHP

PHP is a general-purpose server-side scripting language originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document...

, Ruby

Ruby (programming language)

Ruby is a dynamic, reflective, general-purpose object-oriented programming language that combines syntax inspired by Perl with Smalltalk-like features. Ruby originated in Japan during the mid-1990s and was first developed and designed by Yukihiro "Matz" Matsumoto...

, and many other programming languages. DB2 also supports integration into the Eclipse and Visual Studio

Microsoft Visual Studio

Microsoft Visual Studio is an integrated development environment from Microsoft. It is used to develop console and graphical user interface applications along with Windows Forms applications, web sites, web applications, and web services in both native code together with managed code for all...

integrated development environment

Integrated development environment

An integrated development environment is a software application that provides comprehensive facilities to computer programmers for software development...

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

SQL

SQL is a programming language designed for managing data in relational database management systems ....

statement was executed. The primary, but not singularly useful, error diagnostic is held in the field SQLCODE

SQL Return Codes

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. An important feature of DB2 programs is the error processing...

within the SQLCA block.

The SQL return code

SQL Return Codes

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 TriggerDatabase trigger
A  database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database...

 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 codesSQL Return Codes
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. An important feature of DB2 programs is the error processing...

 for a more comprehensive list of common SQLCODEs.

 User Groups 
IDUG is the International DB2 Users Group, an independent, not-for-profit association of IT professionals who use IBM DB2. 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.



Many Regional user groups also exist, providing an even more granular level of support for all DB2 users. The Los Angeles DB2 Users Group is a good example of this local support.

 See also 

 DatalogDatalog
Datalog is a query and rule language for deductive databases that syntactically is a subset of Prolog.  Its origins date back to the beginning of logic programming, but it became prominent as a separate area around 1977 when Hervé Gallaire and Jack Minker organized a workshop on logic and databases...


 DB2 MagazineDB2 Magazine
DB2 Magazine was a U.S.-based magazine published by CMP Technology and sponsored by IBM Corp. In 2008, the magazine's name changed to IBM Database Magazine before later changing to IBM Data Management magazine in 2009 when it switched publishers...


 Geneva ERS
 MS Technology provides technologies that are embedded into DB2
 MST Viewer document and image viewer that connects directly with DB2 with AFP support.
 SQL/DSSQL/DS
SQL/DS  was IBM's first commercial implementation for its mainframe computers of a DBMS  built around the SQL language....




 External links 


 DB2 homepage at ibm.com
  DB2 10 for z/OS Documentation
 IBM DB2 Express-C - Free edition for download, redistribution and production.
 Up and Running with DB2 on Linux - IBM Redbooks
 Getting Started With DB2 Express-C 9.7 and Parallels Virtuozzo Containers 4.0 - IBM Whitepaper
 Voice of the DB2 Community - IBM DB2 Podcast Series
 IBM DB2 Training and Certification
 IDUG, the International DB2 Users Group
 Learning DB2 Database


        The source of this article is wikipedia, the free encyclopedia.  The text of this article is licensed under the GFDL.