IBM DB2

DB2 (in Russian it is said "two diba", the tracing-paper from English "dib that" is also widespread) — family of software products in the field of information management of IBM company. Most often, referring to DB2, mean the relational DB2 Universal Database database management system (DB2 UDB) developed and released by IBM company.

Despite goodwill to to the operating system Linux which is distributed under the license open source, the corporation IBM is not going to open codes of the DBMS DB2 yet. It was said by the director IBM of the Linux Technology center Jim Vasco at the annual Linux Foundation Collaboration Summit conference which took place (April, 2011) in San Francisco. Inside IBM there is a permanent fight between representatives of different divisions, Vasco explained^[1]. In some cases the choice for benefit of Linux or Windows means decline in income from sales of the software, but revenue growth from services, and in other cases the speech can go about income from sale of the equipment. It is necessary to look for an optimal solution, he concluded. Transition under control Oracle of the packets with the open code developed in Sun Microsystems created certain problems for IBM, Vasco reported. Oracle tries to convince clients to exchange the equipment of IBM on its own Exadata servers and Oracle DBMS. In 2011 Linux Foundation the director Jim Zemlin expects development based on Linux of the specialized high-performance systems like IBM Watson and the ready devices requiring the minimum setup.

Implementations

DB2 DBMS is provided by versions on the following platforms:

• DB2 for Linux, UNIX and Windows v9 for the AIX platforms, HP-UX, Linux, Solaris, Windows and the beta for the Mac OS X platform
• DB2 for z/OS v9 for the z/OS and OS/390 platforms
• DB2 Server for VSE & VM v7 for the z/VM and z/VSE platforms
• DB2 for i for the IBM i platform (it is built in a system at the hardware-software level)

In the past versions of the DB2 DBMS server for OS/2, by UnixWare, PTX were released.

Clients of DB2 DBMS, in addition to the listed platforms, are issued or issued in different versions also for SINIX, IRIX, classical Mac OS and for MS-DOS and also in mobile version of DB2 Everyplace for Windows CE, Palm OS, Symbian OS, Neutrino and the Java virtual machine.

Now, in addition to commercial products of family, IBM extends also free DB2 Express-C distribution kit for platforms of Linux (x86, x86-64, POWER), Windows (x86, x86-64), Solaris (x86-64), Mac OS X (x86-64 beta). [1] The free version has restrictions for use for work of DBMS no more than one dual-core the processor and 2 Gbytes of RAM (the total quantity of processors and memory in a system can be any, but resources over the specified restrictions will not be used by DBMS).

2019: Adding of artificial intelligence

At the beginning of June, 2019 IBM announced adding of artificial intelligence in the relational DB2 DBMS which first version was released for 36 years before.

The updated DBMS supports application development for intellectual data processing using new drivers in which the compatibility with different programming languages and platforms is implemented. IBM says that developers can easily build and analyze models of machine learning for the applications created using DB2. It is supposed that such programs will be more reliable, to require the smaller amount of resources for management and to promote performance improvement.

IBM added artificial intelligence to DBMS which is released 36 years ago

Supported languages — Go, Ruby, Python, PHP, Java, Node.js and Sequelize. Besides, DB2 also supports the tool for creation of the analytical  reports Jupyter Notebook and Visual Studio Code.

The senior administrator of the database in Micro Focus Prasun Mahapatra says that new AI functions are important because people expect much bigger from DBMS. Micro Focus by the beginning of June, 2019 continues to use DB2.

Databases should be more smart. Thanks to the fact that made new DBMS using artificial intelligence of DB2 users will be able to optimize everything: from use of data structures, memory and a disk to the most advanced queries — Makhapatra emphasized.

In DB2 there was also an Augmented Data Explorer tool allowing to make requests orally in  a natural language and  to receive answers  in the form of the visualized data.

One more innovation — support of a blockchain and the normal SQL engine for data access of other IBM platforms. At last, the Data Virtualization technology from IBM Cloud Private for Data service by means of which users can look for data in  the most various sources is added to DBMS, saving time which was spent on  transactions of extraction, conversion and  loading earlier.^[2]

2017: The announcement of additions for control over data

The IBM corporation announced on July 25 a number of the key additions and functionality of IBM Db2 designed to help customers to organize control over corporate data with the help of simple tools in use.

Db2 Developer Community Edition

This solution for developers which can be downloaded free of charge on the website of IBM. The latest version includes a full range of the Db2 tools and allows specialists to create quickly prototypes of applications, using at the same time all opportunities of the corporate edition Enterprise Edition. Users can download software and begin loading and data management within 15 minutes, regardless of whether they work on computers or notebooks, claim in IBM.

Db2 on Cloud

The updated solution Db2 on Cloud is completely managed service available in IBM Cloud.

Among characteristics there are technologies:

• Dynamic scale ("slider") for establishment of parameters of performance and amount of memory — using click by a mouse it is possible to change instantly scale, increasing or reducing performance of data processing and necessary amount of RAM, by the same method it is possible to increase the amount of memory of storage system of information;
• The web console — helps customers to master quicker service and to accelerate work with it.

In general the solution Db2 on Cloud allows to avoid labor-intensive process of approval and purchase of additional computing resources and supplements IBM Db2 Hosted, the version of the database placed in IBM Cloud.

Db2 on Cloud Benchmark

IBM published the new test of assessment of performance which shows more high efficiency of the solution in general.

DB2 Analytics Accelerator

The solution DB2 Analytics Accelerator for z/OS was upgraded using opportunities of HTAP. In more detail about it is here.

Versions

2017: JSON and HTAP

On July 25 IBM announced that Db2 supports JSON, the format of documents open source using language, convenient for perception, for movement of data objects now. JSON which is usually applied in new applications can be integrated into relational Db2 DBMS now, providing to users an easy way of information storage from applications. Developers can use the SQL skills for storage, management and selection of data of JSON in Db2 system. It reduces the need for additional studying of specialized information warehouses and gives the chance to integrate data on mobile and web activities in analytical storage systems for the subsequent receiving insights about behavior of users. Db2 optimizes data retrieval of JSON with indexation support, increasing at the same time request processing performance.

Besides, IBM announced Db2 expansion using architecture of Hybrid Transactional/Analytical Processing (HTAP). It became possible thanks to technical preliminary release of support of secondary indexation in RAM for BLU Acceleration technology. As explained in corporations, use in solution IBM Db2 of the BLU Acceleration mechanism developed for HTAP allows to avoid duplication of information. It reduces the number of requirements to amount of memory and a delay time, unlike other databases. Secondary indexes increase performance of processing of some requests for storages of operational data and analytical storage systems and also working tasks of OLTP and HTAP. Users can already test possibilities of Db2 in the application development environment.

IBM DB2 the 9th version

IBM announced in September, 2012 successful certification of the software package IBM DB2 v.9.7 Fix Pack 3. The product received the certificate of the Federal Service for Technical and Export Control (FSTEC) on compliance to software requirements of information security tools. The certificate certifies that DB2 can be used during creation of automated systems to a class of security "1G" inclusive and for data protection in personal data information systems to the first class. The received certificate of the Federal Service for Technical and Export Control (FSTEC) confirms compliance of the IBM DB2 software product to requirements of the regulating document "Protection against unauthorized access to information. Part 1. Information security software. Classification by the level of absence control of not declared opportunities" on the 4th level of control and compliance to requirements of specifications.

Passing of certification of such level allows to process personal data of the most advanced stage of confidentiality, such as medical data and also to do it in large volumes — the personal data operator can process information about more than one hundred thousands of subjects. Besides, existence of such certificate allows the customers using the software product to undergo at the corresponding creation of own information complexes certification for the widespread requirement to data protection level in automated systems — to a class of security "1G".

IBM DB2 the 10th version

In April, 2012 IBM for the first time in four years released the next release relational DBMS IBM DB2 – version 10. Besides, the storage system was exposed to updating IBM InfoSphere. The tenth versions of both products can be purchased since April 30, 2012.

According to the vendor, the new version of DBMS requires significantly smaller resources of memory and also has higher high-speed performance in response to requests of users in comparison with the previous releases of a system. So, DB2 10 can reduce the volume of a required free space by 90%, and InfoSphere can execute requests 10 times faster, than earlier, said in the official statement of IBM.

DB2 10 represents the first significant updating of DBMS for the last few years: the 10th version of a system for z/OS, however, appeared in 2010, but this release is intended for at the same time Linux, Unix and Windows of systems.

Both products contain new functionality. DB2 supports the RDF format (Resource Description Framework) now, and InfoSphere can interact with Apache Hadoop deployments. Among other improvements in DB2, in particular, it is possible to note acceleration of processes of backup and input-output.

DB2 10 is also more flexible. In particular, database administrators received tools for distribution of the carriers given for storage on different types: for example, operational information can be placed on faster solid state drives, and less valuable data – on cheaper and slow tape devices.

The new feature under the name time travel allows to manage more effectively temporary data, at users of the 10th version for z/OS it made great success. With its help the user or the program can study data in the context of time of their existence in DBMS on the set periods. Use of such sred is relevant for analytics.

DB2 10 can be loaded free of charge for use in an industrial environment on no more than two main cores and 2 GB of memory. More functional versions will manage in the amount beginning from $6180 where also the cost of annual service enters. The cost of InfoSphere is based on the number of processors or volume of the stored data, basic versions will cost about $40 thousand for Tb.

IBM DB2 10.5 version

In cloud infrastructure of IBM in the form of IBM SmartCloud service the new version of DBMS will be unrolled - DB2 10.5 available from June, 2013 the press service of corporation reported on May 27, 2013.

According to statements of corporation, thanks to a set of BLU technologies (Big data, Lightening fast, Ultra easy) of Acceleration accelerating data processing by 25 times and more, SmartCloud with DB2 10.5 can be applied to analytics of real time.

BLU mechanisms optimize on speed DB2 requests to a DB in memory. In particular, at column processing only the necessary columns, and excess data are read out (for example, the duplicated occurrences) passed. Also parallel vector multiprocessor processing and, is applied to economy of the place — technology of a compression which, according to developers, does not worsen high-speed performance. About 16 main cores and 1 Terabyte or more memory are available to each copy of DB2 in "cloud".

IBM claims, SmartCloud allows the customer to apply a BI system, usual for it, but to readdress SQL- and an OLAP requests to a cloud service of IBM.

DBMS SAP HANA Now it is possible to copy to perform in SmartCloud while only for needs of testing and development, note century IBM.

History

DB2 has a long story and as some consider, became the first DBMS using SQL.

From 1975 to 1982 the prototype of DB2 was developed in IBM under the name System Relational, or System R. The SQL language was for the first time implemented in IBM System R, but this system had research character, and a commercial product, the including SQL, the first the Oracle company in 1979 released.

DB2 DBMS received the name in 1982 when the first commercial release for VM under the name SQL/DS, and then release for MVS under name DB2 was released. Long time along with "DB2" the Database 2 option which is also a trademark of IBM was used. Most likely, meant that it is the second flagman IBM DBMS after old hierarchical IMS DBMS.

Development of DB2 originates in the beginning of the 1970th when doctor E.F. Kodd working for IBM developed the theory of relational databases and in June, 1970 published model of manipulation with data. He developed language of relational databases for the embodiment of this model and called it Alpha. IBM preferred to transfer further development to group of programmers, uncontrollable doctor Koddu. Having broken some principles of a relational model, they implemented it as "the structured English of requests", in abbreviated form SEQUEL. As SEQUEL was already the registered trademark, the name was reduced to SQL — "the structured language of requests", and it remained it to this day.

Thus, historically DB2 DBMS arose from products DB2 for MVS (which descendant is DB2 for z/OS) and related to it SQL/DS for VM (the descendant — DB2 Server for VSE & VM). Further other group of developers in IBM implemented the OS/2 EE Database Manager server afterwards evolving in DB2 v2 for OS/2, AIX and then Windows, and then in DB2 UDB (his descendant — DB2 for Linux, UNIX and Windows). One more collective executed integration of architecture of DB2 with AS/400 embedded database (the descendant — DB2 for i). IBM gradually moves on the way of integration of all these branches.

Specific Features

The language dialect of SQL defining, with rare exception, purely declarative sense of language constructions and the powerful multiphase optimizer building the effective execution plan of a request on these declarative constructions belongs to distinctive features of DB2. Unlike other dialects of SQL, in SQL DB2 dialect there are practically no hints to the optimizer, the stored procedure language is a little developed (and long time in general was absent), and, thus, everything is directed to maintenance of declarative style of writing of requests. The SQL DB2 language at the same time is computationally complete, i.e. potentially allows to define in a declarative form any computable compliances between initial data and result. It is reached including due to use of tabular expressions, a recursion and other developed data manipulation mechanisms.

Thanks to a priority of IBM in development of the relational theory and to positions of firm in the computer industry, the dialect of DB2 SQL has a considerable impact on the SQL ANSI/ISO standards.

Stored procedures in DB2 are not really widely applied, at the same time it is traditional for writing of stored procedures normal programming languages of the high level (Si, by Java, PL/I are used, Kobol, etc.), it allows the programmer to make out easily the same code or as a part of the application or as, depending on that, on the client or on the server it it is more reasonable to execute stored procedure. Now in DB2 the procedural SQL expansion for stored procedures according to the ANSI SQL/PSM standard is also implemented.

The optimizer of DB2 widely uses statistics of distribution of data in tables (if process of its collecting was executed by the database manager) therefore the same request in the SQL language can be ottranslirovan in absolutely different execution plans depending on statistical characteristics of data which it processes.

As historically DB2 developed from the multi-user systems on mainframes, much attention in architecture of DB2 is paid to security issues and casts of the servicing DB2 of specialists. In particular, unlike many other DBMS, in DB2 there are separate roles for the database administrator (responsible for configuring of program DB2 components and their optimal accomplishment in a computer system) and the database manager (responsible for data management in specific base).

Use in need of programs of static SQL and the concept of packets allows, unlike the majority of other DBMS, implementation of such model of security when the rights to accomplishment of certain transactions can be issued to application programs in the absence of such rights at the users working with these programs. It allows to guarantee in that case impossibility of work of the user with the database in circumvention of the application program if the user has only rights to start of the program, but not on independent manipulation with data.

Within the concept of increase in level of integration of security aids in a computer system, DB2 has no own means of authentication of users, being integrated with means of the operating system or specialized security servers. Within DB2 only authorization of the users authenticated by a system is performed.

DB2 is the only relational DBMS of general purpose having implementations at the hardware-software level (the IBM i system; also in the equipment of IBM System z mainframes means of support of DB2 are implemented).

Modern versions of DB2 provide expanded support of use of data in the XML format, including transaction with separate elements of documents XML.

Notes