home Repair Introduction to IBM DB2. Stored procedures and functions. Source materials and contact information

Introduction to IBM DB2. Stored procedures and functions. Source materials and contact information

DB2(in Russian it is pronounced “dibi two”, tracing paper from the English “dibi tu” is also common) - family software products in Information Management at IBM.

Most often, referring to DB2, they mean the relational database management system DB2 Universal Database (DB2 UDB), developed and released by IBM.

The spelling "DB/2" is sometimes seen, but this spelling is incorrect: in the IBM notation, the number in the denominator of a fraction indicates the platform and "/2" indicates the product for operating system OS/2 (or the PS/2 series of computers). For example, the version of DB2 for OS/2 was designated "DB2/2".

Implementations

The DB2 DBMS is currently available on the following platforms:

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

In the past, versions of the DB2 database server for OS/2, UnixWare, PTX have been released.

DB2 DBMS clients, in addition to the listed platforms, are released or have been released in various versions also for SINIX, IRIX, classic Mac OS and for MS-DOS, as well as in mobile version DB2 Everyplace for Windows CE, Palm OS, Symbian OS, Neutrino and virtual machine java.

Currently, in addition to the commercial products of the family, IBM also distributes a free distribution DB2 Express-C for Linux (x86, x86-64, POWER), Windows (x86, x86-64), Solaris (x86-64), Mac OS X (x86-64 beta) platforms. The free version has restrictions on the use of no more than one dual-core processor and 2 GB for the DBMS random access memory(the total number of processors and memory in the system can be any, but resources beyond the specified limits will not be used by the DBMS).

Story

DB2 has a long history and is considered by some to be the first DBMS to use SQL.

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

The DB2 DBMS got its name in 1982 with the first commercial release for SQL/DS and then for MVS called DB2. For a long time, along with "DB2", the "Database 2" variant, also a trademark of IBM, was used. Apparently, this was meant to be the second flagship IBM DBMS after the old hierarchical IMS DBMS.

The development of DB2 goes back to the early 1970s, when Dr. E. F. Codd, who worked for IBM, developed the theory of relational databases and published a data manipulation model in June 1970. To implement this model, he developed a relational database language and called it Alpha. IBM chose to outsource further development to a group of programmers outside of Dr. Codd's control. Violating some principles of the relational model, they implemented it as a "structured English language requests”, abbreviated as SEQUEL. Since SEQUEL was already a registered trademark, the name was shortened to SQL - "Structured Query Language" and has remained so to this day.

Thus, historically, DB2 evolved from DB2 for MVS (of which DB2 for z/OS is a descendant) and its sister SQL/DS for VM (of which DB2 Server for VSE & VM is a descendant). Later, another team of developers at IBM implemented the OS/2 EE Database Manager server, which later evolved into DB2 v2 for OS/2, AIX and then Windows, and then into DB2 UDB (its descendant is DB2 for Linux, UNIX and Windows). Another team completed the integration of the DB2 architecture with the embedded AS/400 database (a descendant - DB2 for i). IBM is gradually moving towards the integration of all these branches.

Peculiarities

To distinctive features DB2 includes a dialect of the SQL language, which defines, with rare exceptions, a purely declarative meaning of language constructs, and a powerful multi-phase optimizer that builds an effective query execution plan based on these declarative constructs. Unlike other SQL dialects, the DB2 SQL dialect has practically no hints to the optimizer, is poorly developed (and for a long time was generally absent) a stored procedure language, and thus everything is aimed at maintaining a declarative style of writing queries. At the same time, the DB2 SQL language is computationally complete, that is, it potentially allows you to define any computable correspondences between the source data and the result in a declarative form. This is achieved, among other things, through the use of table expressions, recursion and other advanced data manipulation mechanisms.

Due to IBM's focus on relational development and the firm's position in the computer industry, the DB2 SQL dialect has a significant impact on the ANSI/ISO SQL standards.

Stored procedures are not very widely used in DB2, and traditionally, conventional high-level programming languages (C, Java, PL/I, Cobol, etc.) are used to write stored procedures, this allows the programmer to easily format the same code either as part of the application, or as a stored procedure, depending on whether it is more appropriate to execute it on the client or on the server. DB2 also currently implements procedural SQL extension for stored procedures according to the ANSI SQL/PSM standard.

The DB2 optimizer makes extensive use of table distribution statistics (if the data collection process was done by the DBA), so the same SQL query can be translated into completely different execution plans depending on the statistical characteristics of the data it processes.

Since historically DB2 has evolved from multi-user systems on mainframes, a lot of attention in the DB2 architecture is given to issues of security and the distribution of roles of specialists maintaining DB2. In particular, unlike many other DBMSs, DB2 has separate roles for the DBMS administrator (responsible for configuring DB2 software components and running them optimally in computer system) and a database administrator (responsible for managing data in a particular database).

The use, if necessary, of static SQL in programs and the concept of packages allows, unlike most other DBMS, the implementation of such a security model, when the rights to perform certain operations can be granted to application programs in the absence of such rights for users working with these programs. In this case, this makes it possible to guarantee the impossibility of the user working with the database bypassing the application program, if the user has only rights to run the program, but not to independently manipulate data.

As part of the concept of increasing the level of integration of security tools in a computer system, DB2 does not have its own means of authenticating users, integrating with operating system tools or specialized security servers. Within DB2, only users authenticated by the system are authorized.

DB2 is the only general purpose relational DBMS that has implementations at the hardware/software level (IBM i system; DB2 support is also implemented on IBM System z mainframe hardware).

Modern versions of DB2 provide enhanced support for using XML data, including operations on individual elements of XML documents.

Error processing

A useful feature of DB2 SQL Server is its ability to handle errors. The SQLCA structure is used for this purpose. SQL Communications Area- SQL link area) that returns error information to the application program after each execution of the SQL statement.

SQLCODE Structure Fields and Their Values

The main, but not always useful error diagnostics is contained in the field SQLCODE(data type - integer) inside the SQLCA block. It can take the following values:

0 means success.
A positive number means success with one or more warnings. For example, +100 means no columns were found.
A negative number means failure with an error. For example, −911 means a detected expired lock wait interval (or deadlock) triggering a sequential rollback.

SQLERRM(data type - a string of 71 characters). Contains text string with a description of the error if the SQLCODE field is less than zero.

SQLERRD(data type - array, 6 integers). Describes the result of executing the last SQL statement:

1 element - internal information;
2nd element - contains the value of the SERIAL type field generated by the server for the INSERT statement, or an additional error code;
3rd element - equal to the number of processed records;
4th element - the approximate cost of executing this operator;
5th element - offset of the error in the text record of the SQL statement;
6th element - internal information.

Notes

Literature

Date K. DB2 Relational DBMS Guide. - M.: Finance and statistics, 1988. - 320 p. - ISBN 5-279-00063-9
Zikopoulos P.K., Baklarz J., deRus D., Melnik R.B. DB2 Version 8: The Official Guide = DB2 Version 8: The Official Guide. - M.: KUDITS-OBRAZ, 2004. - 400 p. - ISBN 5-9579-0031-1
Smirnov S. N. Working with IBM DB2: Tutorial. - M.: Helios, 2001. - 304 p. - ISBN 5-85438-007-2 (recommended by UMO universities in the region information security as a teaching aid in the specialties "Integrated Information Security of Automated Systems" and "Computer Security")
Susan Visser, Bill Wong. Teach Yourself DB2 Universal Database in 21 Days = Sams Teach Yourself DB2 Universal Database in 21 Days. - 2nd ed. - M.: Williams, 2004. - 528 p. - ISBN 0-672-32582-9
Hook J., Harbus R., Snow D. The Universal Guide to DB2 for Windows NT®. - New Jersey: Prentice Hall PTR, 1999. - P. 504. - ISBN 0-13-099723-4

Wikimedia Foundation. 2010 .

See what "IBM DB2" is in other dictionaries:

IBM DB2- Developer(s) IBM Initial release 1983 (1983) ... Wikipedia

IBM DB2- DB2 ist ein kommerzielles relationales Datenbank Management System (RDBMS) der Firma IBM, dessen Ursprünge auf das System R und die Grundlagen von E. F. Codd vom IBM Research aus dem Jahr 1970 zurückgeht. Inhaltsverzeichnis 1 Eigenschaften 1.1… … Deutsch Wikipedia

IBM DB2- Développeur IBM Dernière version ... Wikipedia en Français

IBM DB2 Commonstore- DB2 CommonStore Archiving software produced by IBM for managing e mail messages or SAP ERP data. Part of the IBM Information Management portfolio which builds upon the DB2 database platform. DB2 CommonStore is one of several products which are… … Wikipedia

Enterprise Edition - a solution for large organizations that need reliable transaction processing and rich functionality;

Advanced Edition - this version supports an unlimited number of connections, clustering and provides scalability, allowing you to achieve high performance (the version does not provide two-phase transaction confirmation and automatic failover);

Small Business Edition - the version is designed to support Internet applications. The maximum number of allowed IIOP/CORBA connections is ten, suitable for organizations whose systems do not need clustering, mainframe support, two-phase transaction confirmation and automatic failover;

The Developer Edition, an option for developers building and testing applications using Sybase EAServer, is fully featured but limited to five IIOP connections.

Sybase EAServer provides a number of important benefits, including:

reliable operation of the system by providing automatic recovery health at the level of server memory, load balancing, and clustering;

investment protection through integration with existing internal systems;

Simplified management through comprehensive configuration and administration capabilities such as component instance caching, transaction management, process caching, and graphical component parameterization.

Subd db2

DBMS company IBM DB2, first appeared in the early eighties. Versions of this product currently run on AS/400AIX, S/390, Windows, OS/2, AIX, HP-UX, SCO UnixWare, Linux, NUMA-Q, and Sun Solaris.

DB2 Enterprise Server Edition comes with a rich set of administration tools. DB2 data access mechanisms support ODBC, JDBC, and ADO/OLE DB drivers, and for Microsoft users visual studio additional modules included in the environment are available, such as the Stored Procedure Builder. Windows versions of DB2 also allow you to create stored procedures in Visual Basic. Borland development tools support access to DB2 not only through ADO/OLE DB and ODBC, but also through its own universal data access mechanisms - Borland Database Engine and dbExpress.

DB2 DBMS supports the creation of data warehouses and, in addition to the tools for transferring data to the warehouse, it contains the Data Warehouse Center as part of the client part, which allows you to manage all the processes associated with the creation and support of data warehouses, including creating a warehouse schema, determining sources of initial data for it, creating schedules updating data in the storage, checking and correcting incorrect data. To support OLAP storage, DB2 Universal Database Server includes an OLAP Starter Kit based on Hyperion Essbase OLAP Server.

The DB2 DBMS supports storing and searching XML documents by their elements and attributes. Using the SOAP protocol, you can access Web services that provide access to DB2 stored procedures and data (Web services are managed by the IBM WebSphere application server).

The most important technical characteristics of DB2 Universal Database include support for relational and complex data using object extensions, the ability to work on multiprocessor platforms, cluster support, 64-bit memory architecture, query parallelization, availability of tools for heterogeneous administration and data processing, support for distributed transactions .

DB2 is the only database that can be developed on .Net while running on any platform including Linux, mainframes, Windows. DB2 has Java stored procedures.

Extended support for Visual Studio includes support for Web services, full support for storing XML data without transforming it, and the ability to create applications and Web sites without writing code.

DB2 is built on relational technologies with extensive support for XML, which simplifies data development and integration. XML is a key component of the metadata generation process and metadata controls in DB2.

DB2 version 9 introduces pureXML technology, the revolutionary Venom data compression technology, and offline data management. DB2 provides access to information stored in the Oracle and MySQL DBMS.

Implemented in DBMS enhancements allow developers to significantly simplify and accelerate the creation of applications that work simultaneously with XML and relational data stores. For example, pureXML technology supports the XQuery language, a standard designed specifically for processing XML data. To retrieve relational or XML data, application developers can use XQuery, XPath, standard SQL language- or all three at the same time.

The DB2 Developer Workbench comprehensive development environment allows you to create, edit, debug, test, and deploy stored procedures and user-defined functions. The Developer Workbench can be used to develop applications and to create, edit, and execute SQL statements and XML queries.

The DB2 version 9 database server enables the seamless, concurrent movement of relational and XML data, regardless of format, platform, or location ( http://www-306.ibm.com/software/data/db2/v9/).

DB2 also compresses indexes and temporary tables, which no other DBMS can do today. Implementation of a fault-tolerant two-server configuration, in which a server with a standby database can be used for reporting.

DB2 is available in two editions: DB2 Workgroup and DB2 Enterprise Edition. The DBMS implements query processing parallelization, a full set of replication tools, query pivot tables to improve database performance, object-oriented database design features, and Java language tools.

DB2 is equipped with a complete set of multimedia extensions for storing and manipulating text, sound, video, images, and geographic data. The main disadvantages of DB2 are the relative complexity of administration.

In DB2, thanks to the Index Smart Guide, you can tune, forming optimal indexes for a given number of hits, which characterizes the typical load on the database. DB2 DBMS allows you to generate pivot tables, which greatly increases the efficiency of its work as a data warehouse. A pivot table is a temporary work area used by the DBMS to store answers to frequently received queries.

The DB2 Spatial extension for working with spatial data was developed in collaboration with ESRI [ http://www.esri.com/partners/alliances/ibm/solutions.html,http://www.esri.com/partners/alliances/ibm/index.html].

IBM releases three editions of DB2 at once - for corporate clients, for SMBs (DB2 Express), and for developers (DB2 Express-C).

IBM has a simplified version of the DBMS DB2 Express-C, it is relational but supports XML.

DBMS Informix

Prior to its acquisition by IBM, Informix's flagship product was the Informix Dynamic Server (IDS), which supports UNIX and Windows platforms and provides efficient operation on both single and multiprocessor systems, as well as in clusters. This product has powerful parallel processing capabilities. Among the main characteristics of Informix Dynamic Server, it should be noted that it is used to manage disk space as OS tools; as well as own functions that allow you to bypass OS restrictions, manage memory sharing; dynamic flow control; support for table and index fragmentation across multiple disks; query parallelization; data mirroring. The IDS server supports two-phase completion of transactions, as well as heterogeneous transactions (in this case, other servers can participate in transactions). Of particular note is the server's support for time series.

Server functionality extensions are implemented on the basis of DataBlade - collections of database objects and subroutines in the C language, connected to the database and released both by the DBMS manufacturer and by a number of independent manufacturers.

Of the data access mechanisms, IDS supports ODBC, JDBC, and ADO/OLE DB drivers. Borland development tools support access to IDS using Borland Database Engine, dbExpress, Microsoft .NET mechanisms.

Recently, there has been a transition from relational DBMS to object-oriented ones. Informix, following this concept, presents its solution in the Centaur DBMS, based on the Informix Dynamic Server 7.3 relational database and the Informix Universal Data Option object-relational database, and combining the high performance of Dynamic Server when working with data with universality and multimedia features of Universal Data Option. This implementation is intended for the development of Internet systems. The DBMS has a flexible development environment, scalability to match the intensive workloads of the Internet, and tools for working with new types of data that have become ubiquitous with the development of the Web. The Java tools implemented in this system will allow developers to create stored procedures, user programs and DataBlades components in this language.

The Centaur package comes with built-in ActiveX object handling. This makes it possible to create database stored procedures in the Visual Basic language.

Centaur is an add-on to Informix Dynamic Server and works with the traditional database format for this package, so that users retain all the old functions, and upgrading the system to the level of new versions will not be very difficult. The system is equipped with facilities for object-oriented database design, creation of specialized tables and indexing programs; allows users to embed their own functions in queries and not rely solely on standard SQL tools.

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Hosted at http://www.allbest.ru/

Minsk Institute of Management

Department of automated information systems

Report on the passage of general engineering practice

Terms of internship: from 11/19/2012 to 12/16/2012

Student group 111201-z

THEM. Vaitovich

Head senior

T.V. Russian teacher,

master of technical sciences

Hosted on Allbest.ru

Private educational institution

Minsk Institute of Management

Faculty: engineering and information

Department: automated information systems

Specialty: 1-40 01 02 - Information systems and technologies (by directions)

Individual task for general engineering practice for student Khobotova Anastasia Mikhailovna group No. 111201z

1. Topic of the abstract:

IBM DB2 database management system.

2. Individual task:

4. Schedule:

	Name of educational (general engineering) practice activities	Dates
	Organizational meeting for the IIP
	Getting an individual assignment for practice	11.09.12 - 24.09.12
	Tours (exhibitions)	19.11.12 - 24.11.12
	Conducting seminars, meetings, talks	19.11.12 - 30.11.12
	Abstract preparation	01.12.12 - 09.12.12
	Completion of an individual task	01.12.12 - 16.12.12
	Preparing a practice report	17.12.12 - 11.03.13
	Providing a report on practice to the department
	Protection of the practice report	19.03.13 - 23.03.13

Head T.V. Hare

Introduction

1. Database management system

1.1 The concept of a DBMS

1.2 The history of the creation of the DBMS

1.6 Large objects

1.8 Triggers

1.9 Relational extenders

1.11 Overview of IBM DB2 editions

2. Practical part

2.1 Statement of the problem

2.2 Solution algorithm

2.3 Software implementation

2.4 Program testing

Conclusion

Application

Introduction

The purpose of the general engineering practice is to consolidate and expand the knowledge gained by the student in the course of study, to acquire an idea of the future profession. The objectives of the practice are: - consolidation of the theoretical material presented in the lectures of the disciplines of the subject block;

Acquisition of computer skills, with Windows OS and a standard office suite;

Consolidation of the knowledge and skills obtained during the study of basic courses in computer science on the methods of using, developing and implementing classical algorithms in the studied programming languages;

Master the methods professional search information on the Internet;

To study the requirements for reporting, methods and application environments for preparing reports on the results of practice;

Working with office equipment.

The first section of the report discusses the IBM DB2 database management system: the concept of a DBMS is described, types and a brief description of DBMS IBM DB2.

In the second part of the report, an algorithm and a C program are developed to find the maximum element of array A in array B.

1. IBM DB2 database management system

1.1 The concept of a database management system

The database (DB) is named collection interconnected data under the control of the DBMS.

Database management system (DBMS) - a set of software and linguistic tools of general or special purpose, providing control over the creation and use of databases.

1.2 The history of the creation of the DBMS

active search activity acceptable ways The socialization of a continuously growing volume of information led to the creation in the early 60s of special software systems called "Database Management Systems" (DBMS).

The main feature of the DBMS is the presence of procedures for entering and storing not only the data itself, but also descriptions of their structure. Files provided with a description of the data stored in them and controlled by the DBMS began to be called data banks, and then "Databases" (DB).

When the production of computers of the ES computer family began. The work was carried out in two directions. First of all, attempts were made to create their own original domestic DBMS. At the same time, in an accelerated mode, analogues of some DBMSs widely used abroad, capable of operating on domestic hardware and software platforms, were developed. A similar approach was also used to create a DBMS for hardware platforms, the mass production of which began in the country after the appearance of the ES computer platform - for SM computers, ASVT, IBM-compatible personal computers and etc. .

1.3 History of the IBM DB2 DBMS

DB2 is a family of relational database management systems produced by IBM. This is one of the "mature" world DBMS, a constant leader in performance, in terms of technical implementation, scalability, etc.

DB2 has a long history. It is the first DBMS to use SQL. From 1975 to 1982, the DB2 prototype was developed at IBM under the name System Relational, or System R.

DB2 got its name in 1982 with the first commercial release for VM called SQL/DS and then the release for MVS called DB2.

The development of DB2 goes back to the early 1970s when Dr. E.F. Codd, who worked for IBM, developed the theory of relational databases and published a data manipulation model in June 1970. To implement this model, he developed a relational database language and called it Alpha.

IBM DB2 is the most high-performance and powerful DBMS in the world. Its main unique advantage is that any application written for DB2 will work with DB2 data servers running on any distributed platform supported by DB2 (Windows, HP-UX, Sun Solaris, Linux, Mac OS X, and AIX®).

DB2 comes in various editions and packages. All editions and packages of DB2 are built on the same database. source code; they differ only in functionality and licensing terms that target the features, functions, and benefits of DB2 to their respective market segments and price groups.

The scope of tasks that can be solved using DB2 object extensions is significantly increased compared to the classic relational approach. Having borrowed from the object-oriented model the most necessary for modern applications elements, DB2 retained all the advantages of a relational DBMS. This makes it possible to use both relational and object-oriented approaches when building corporate information systems on its basis.

1.4 DB2 functionality

DB2 functionality:

Multiplatform.

Advanced Copy Services (ACS). DB2 ACS allows you to use fast storage copy technology to perform data copy work in backup and restore operations. The ability to copy data using a storage device greatly speeds up backup and restore operations. A backup using DB2 ACS is called backup via a snapshot backup.

Online table reorganization allows the user to reorganize a table without interrupting full access To her.

Support for High Availability Disaster Recovery (HADR). The DB2 HADR functionality provides high availability and failover support for DB2 databases.

Support for Materialized Query Tables (materialized query tables), Query Parallelism (query parallelism), Multidimensional Clustering Tables (MDC, multidimensional table clustering).

Support for data compression when backing up.

Support for SQL replication.

Support for Database Partitioning (database partitioning). This functionality allows you to distribute a single database image across multiple physical servers.

DB2 Text Search. The DB2 Text Search feature allows you to search the text columns of DB2 tables.

IBM Data Studio is a tool platform that spans the entire life cycle applications (design, development, deployment, support and management) for all IBM relational DBMS, with the prospect of further expansion of support. This means that you not only have a portable SQL API, but also a set of tools that allow you to implement enterprise-wide business logic.

1.5 User-defined data types

DB2 gives the user the ability to define new data types. The new data type must match one of the base types provided by the system, but they can have their own semantics defined. At the same time, DB2 is able to manipulate such data in accordance with the logic defined for them. You can specify the set of operations allowed on a data type by changing it from that of the base type.

DB2 implements a strong typing mechanism. In this case, only those operations that are defined for itself, and not for the base class, are applicable to data of an indefinite type. For a DBMS, this approach provides a powerful mechanism for data integrity control.

So, you can define the type "zip code" as derived from an integer, but at the same time prohibit the operations of multiplication and division for data of this type, as meaningless, while for the base class these operations are valid.

1.6 Large objects

DB2/2 and DB2/6000 provide the user with new data types such as binary large objects (BLOBS) and text large objects (CLOBS). BLOBS allow you to store data of any kind up to two gigabytes in size. CLOBS have the same size limits, but are designed to store text as a sequence of single-byte or double-byte characters and can be associated with a specific code page. The presence of such data types allows you to embed relational tables with data of non-traditional types, primarily multimedia. This capability is becoming increasingly important for modern applications, allowing for example to store employee photos in a HR database, graphic images, sound, video, large texts. At the same time, the main attention is paid to achieving high performance and reliability, as well as removing restrictions on the use of large objects. So, you can create a table that includes more than ten fields containing two-gigabyte objects.

Great opportunities when working with large objects provides the definition of new data types and functions. This makes it possible to set the possibility of searching for a picture by its element, or the operation of comparing texts, etc.

1.7 User defined functions

User-defined functions allow the internal representation of data to be hidden from the application by providing some encapsulation of the data. They also allow you to define new operations on both underlying data and user-defined types.

User-defined functions allow you to achieve code reuse by allowing operations that are common to various applications, are stored on the server rather than included in each individual application. To implement these functions, programming languages are used, and for their implementation in the DBMS, the CREATE FUNCTION statement introduced into the data definition language. In fact, this statement associates a user-defined function with a specific program that is executed when this function is called. Using user-defined functions instead of directly accessing the data can provide some encapsulation of the data, which can be used to hide its internal structure from the user. In addition, DB2 supports a mechanism for overloading user-defined function names, similar to that used in OODB, but does not allow functions to be associated with specific data elements, as methods and objects are associated in the object approach. Adding flexibility to user-defined functions is the ability to work on both DB2 data and other data at the same time, such as files, email and others. There are two ways in which user-defined functions can interact with a DB2 server. The first is that the function has direct access to the database, which allows for maximum performance, but is a potential threat to server health and data integrity. In the second option, the function runs as a separate process from the database server, which provides data and DBMS protection, but reduces performance.

The user can choose the best approach for his task, depending on its specifics.

1.8 Triggers

Triggers define a set of operations that are performed when certain events occur in the database, such as when a table is updated.

Triggers can be used to perform functions that, in an object-oriented approach, are performed by methods (for example, validating input values), or by a constructor (assigning values when a new record is created).

The use of triggers allows you to make data "active", to model not only the structure and properties, but also the behavior of data objects stored in the database.

1.9 Relational extenders

DB2 Relational Extenders are a good example of these new features. They provide ample opportunity to work with non-traditional data, using the ability to define custom data types and functions. To store multimedia data, and to maintain the integrity of the links - triggers.

Currently, there are five relational extenders that allow you to work with an image, complex text documents, video, audio, and even with fingerprints.

1.10 Critical analysis of the DB2 DBMS

Consider the pros and cons of the DBMS.

There is a good free version;

The free version has no limits on the size of the database;

The free version does not have a request limiter, this allows you to serve dozens of users at the same time;

Good free technical support;

Unlike PostgreSQL, it is possible to get paid support from the manufacturer, which allows it to be used in the Entrprise business sector;

With 1C-Enterprise configurations in automatic mode locks work better than PostgreSQL (we are talking about parallelism, the scope of locks is at the row level, not tables);

Good performance;

Less problems with non-unique indexes (in fact, to solve the problem, it is recommended to temporarily load databases into DB2);

Better handles situations like "not enough memory for the 1C server";

There is no limit on 256 tables, which expands the possibilities when working with RLS.

Few specialists and high cost of good specialists;

Small prevalence;

Unlike MS SQL Server, 1C releases "adapted" versions for new versions;

The size of the databases is larger than in other DBMS;

Requires "fine" tuning of the DBMS parameters, there is auto-tuning of the system, but incomplete;

Some messages may be incorrectly processed by the platform, for the solution it is necessary to "lower the level" of error logging;

1C-CUP collects long requests, waits on locks, but does not collect deadlocks.

1.11 Overview of IBM DB2 editions

Names of editors	Opportunities and limitations
IBM DB2 Express-C	Basic free version of IBM DB2 DBMS. Technical support is not provided. Those. Limitations: Uses a maximum of 2 cores and 1 processor, 2 GB of RAM. There is no support for replication and clustering, there is no guarantee and the possibility of purchasing additional. software packages to expand the capabilities of the DBMS. Versions are released without service packs. Supported OS: Windows and Windows x64, Linux, Linux x86, Linux x64, Linux on Power, Solaris, Mac OS X. Supported OS: Windows, Linux (POWER and x86 servers), Mac OS X and Solaris x64. Recommended: for small healthcare facilities that are limited in financial capacity and do not need technical support from IBM with up to 50-70 users.
IBM DB2 Express - C FTL (Fixed Term License)	First commercial version. Completely similar to the previous product, but with a 12 month IBM 24x7 technical support subscription + replication support + 2-server cluster support + FixPack release and multi-version support. Those. Limitations: Uses a maximum of 2 cores and 1 processor, 2 GB of RAM. Supported OS: Windows, Linux (POWER and x86 servers) and Solaris x64. Recommended: for small and medium health care facilities, limited in financial capacity, but in need of technical support from IBM with up to 50-150 users.
IBM DB2 Express Edition	Initial release of IBM DB2 with technical support for 12 months. Those. limits: up to 4 cores ( Intel processors) and up to 4 GB of RAM. Can only be used on 2 processor 2 core servers (for Intel) or weaker. Supported OS: Windows, Linux (POWER and x86 servers), AIX, Solaris (SPARC and x64), HP-UX (IA-64 only) Recommended: For small and medium hospitals requiring IBM support users up to 70-200.
IBM DB2 Workgroup Server Edition	Server Edition Represents a DB2 data server that provides the same functionality as DB2 Express, but is designed for higher workload requirements that require more memory, computing power, a greater degree of high readiness without additional settings, and has a wider range of platform-specific deployment options. Those. limitations: You can use a variety of high performance multi-processor servers (including Power, Itanium, Ultra SPARC, etc.), but up to 16 GB of RAM for all processors. Supported OS: Windows, Linux (POWER, System z and x86 servers), AIX, Solaris (SPARC and x64), HP-UX (IA-64 only). Recommended: for medium and large healthcare facilities that need technical support from IBM with a number of users from 100.
IBM DB2 Enterprise Server Edition	For the largest institutions and the most high-performance computing. Supported OS: Windows, Linux (POWER, System z and System x), AIX, Solaris (SPARC and x64), and HP-UX servers (IA-64 only from DB2 9.5). Recommended: for large healthcare facilities and a network of several healthcare facilities that need technical support from IBM with a number of users from 1000.

1.12 Next Generation DB2

IBM unveiled the next-generation DB2 9 database server, codenamed Viper, the most significant advancement in database technology in more than twenty years.

The release of DB2 9 marks the end of a five-year IBM project that has transformed traditional, static database technologies into the concept of an interactive, dynamic database server that provides customers with greater control over all types of information such as documents, audio and video files, images, Web pages, and XML transactions with digital signatures.

The new IBM database server is an industry first to enable seamless, parallel movement of relational and XML data, regardless of format, platform or location.

More than 750 software developers from eight countries contributed to the creation of the Viper database, which is designed and customized with a focus on information management in SOA environments.

DB2 9 introduces three significant technological advances -- the patented pureXML technology, the revolutionary Venom data compression technology, and advanced offline data management. A similar combination of new to the industry functionality, enhanced with security and disaster recovery enhancements, will speed up information requests and provide customers with unrestricted access to information. In addition, the new version of DB2 provides access to information stored in conventional Oracle and MySQL databases, which vendors do not offer the same capability.

For the first time, IBM is releasing three editions of DB2 at once - for enterprise customers, for small and medium-sized businesses (DB2 Express), and for developers (DB2 Express-C).

"DB2 Viper promises to change the competitive landscape of the database industry," said Ambuj Goyal, general manager, IBM Information Management. our customers to grow their business by leveraging information on demand."

DB2 9 pre-test and evaluation customers and partners have provided extremely positive feedback. Zurich Insurance, N.A., a leading commercial property casualty insurance provider serving large corporations and SMB clients, has chosen to use DB2 9 to gain access to new technology pureXML.

"The insurance industry is facing a growing need to store massive amounts of XML documentation.

pureXML technology provides an unprecedented level of flexibility in defining custom functions and performing external operations on XML data stored in legacy databases, said James Surber, Senior Database Administrator, Zurich Insurance N.A. "Being able to store different versions of the same XML documents with different structures in the same table will allow us to avoid spending hours manually unloading and reloading the entire table when our XML structure changes."

2. Practical part

2.1 Statement of the problem

Two arrays are given: A, consisting of n elements, and B, consisting of m elements. Determine if the largest element of array A is contained in array B.

2.2 Solution algorithm

The scheme of the solution algorithm is shown in the drawing 02.16.011.001

In block 1, the size of matrices A and B is entered.

Blocks 2-11 are used to enter the matrix from the keyboard.

Blocks 12-23 are used to display the original arrays in matrix form.

Blocks 24-30 are used to find the maximum element of array A.

Blocks 31-37 are used to find the maximum element of array A in array B.

Blocks 38-40 are the final solution to the problem and show whether the maximum element of array A is in array B.

2.3 Software implementation

Listing software module for solving the problem in the C programming language is given in the appendix.

First you need to declare all variables:

int A, B, n, m, i, k, max, x;

where A and B are integer arrays consisting of 100 strings; n and m are variables that store the number of matrix rows; i and k - loop variables; max - integer variable for storing the maximum element of array A; x is an integer variable for finding the maximum element of array A in array B.

scanf_s("%d", &n);

scanf_s("%d", &m);)

while (n>=100, m>=100);

Dimension input is carried out using the do...while loop, which allows you to check the correctness of the entered data. Because Since memory for an array is allocated statically, then the number of matrix rows should not exceed 100, which is specified when declaring arrays. In case of an erroneous entry, a prompt will appear again to enter the dimension of the array.

Array elements are entered inside nested arrays. for loops: in the first cycle i changes - the line number from 0 to n-1, and in the second cycle k - the line number from 0 to m-1. At each iteration of the nested loops, a prompt is displayed for entering array elements, and array elements are entered from the keyboard.

for (i=0;i

scanf_s("%d", & A[i]);)

for(k=0;k

printf("Array A: ");

for (i=0;i

printf("%d", A[i]);

printf("Array B: ");

for(k=0;k

printf("%d", B[k]);

Next, you need to assign to the variable max an initial value equal to the zero element of the array and, sorting through the elements of the row of array A, find the maximum element using the if statement. We assign the maximum element of the array A to the value max. The maximum element of array A is displayed.

for (i=1;i

if (max

Next, you need to assign the initial value equal to 0 to the variable x and sorting through the elements of the row of array B, we find the maximum element of array A using the if statement. If the maximum element of array A is in array B, a confirmation is displayed. If the array B does not contain the maximum element of the array A, then a confirmation is displayed about this.

for(k=0;k

if (B[k]==max) x=1;

2.4 Program testing

A test example of the program, which illustrates the incorrect input of the array dimensions, is shown in Figure 2.1 for data n=105 and m=3.

Figure 2.1 - An example of the program

The test example of the program operation shown in Figure 2.2 illustrates the program operation for the following matrices:

Figure 2.2 - An example of the program

Figure 2.3 - An example of the program

Conclusion

As a result of the general engineering practice, the IBM DB2 DBMS was studied, which has an efficient and reliable platform for building applications. Including products 1c. This DBMS from IBM can also be integrated with other tools running on different hardware and software platforms (Linux and Windows) - Microsoft Visual Studio, Microsoft Visual Basic, Microsoft Visual C ++, etc.

In the practical part of the report, an algorithm and a program were developed for finding the maximum element of array A in array B. The results of testing the program showed that it works for both positive and negative integers. The assignment has been completed in full.

List of sources used

1. GOST 19.701-90 - Unified system of program documentation - Schemes of algorithms, programs, data and systems - Conventions and execution rules

2. Date K. Guide to the relational DB2 DB2. - M.: Finance and statistics, 1988. - 320 p.

3. Kogalovsky M.R. "Encyclopedia of database technologies" / M.R. Kogalovsky. - M.: Finance and statistics, 2002. - 800 p.

4. Deitel H.M. How to program in C / H.M. Deitel, P.J. Deitel. - M.: Binom, 2006. - 1037 p.

5. Raoul F. Chong. Getting started with DB2 Express 9.7. Moscow, 2010.-269 p.

Application

Program module listing

#include "stdafx.h"

#include "stdio.h"

#include "conio.h"

#include "math.h"

int _tmain(int argc, _TCHAR* argv)

int A, B, n,m,i,k,max,x;

do (printf("Vvedite kol-vo elements massiva A:\n");

scanf_s("%d", &n);

printf("Vvedite kol-vo elements massiva B:\n");

scanf_s("%d", &m);)

while (n>=100, m>=100);

for (i=0;i

(printf ("Vvedite elementi massiva A [%d]: ",i+1);

scanf_s("%d", & A[i]);)

for(k=0;k

(printf ("Vvedite elementi massiva B [%d]: ",k+1);

scanf_s("%d",&B[k]);)

printf("Array A: ");

for (i=0;i

printf("%d", A[i]);

printf("Array B: ");

for(k=0;k

printf("%d", B[k]);

for (i=1;i

if (max

printf ("Maximal"nij element massiva A: %d \n",max);

for(k=0;k

if (B[k]==max) x=1;

if (x==1) printf ("Maximal"nij element massiva A nahoditsia v massive B\n");

else printf("Maximal"nij element massiva A ne nahoditsia v massive B\n");

database copy algorithm

Hosted on Allbest.ru

Introduction

Acquisition of computer skills, with Windows OS and a standard office suite;

To master the methods of professional search for information on the Internet;

To study the requirements for reporting, methods and application environments for preparing reports on the results of practice;

Working with office equipment.

In the first section of the report, the IBM DB2 database management system is considered: the concept of a DBMS is described, types and a brief description of the IBM DB2 DBMS are given.

In the second part of the report, an algorithm and a C program are developed to find the maximum element of array A in array B.

IBM DB2 database management system

The concept of a database management system

A database (DB) is a named collection of interrelated data managed by a DBMS.

Database management system (DBMS) - a set of software and linguistic tools for general or special purposes that manage the creation and use of databases.

The history of the creation of the DBMS

Vigorous activity to find acceptable ways of socializing the continuously growing volume of information led to the creation in the early 60s of special software systems called "Database Management Systems" (DBMS).

The history of the creation of the IBM DB2 DBMS

DB2 has a long history. It is the first DBMS to use SQL. From 1975 to 1982, the DB2 prototype was developed at IBM under the name System Relational, or System R.

DB2 got its name in 1982 with the first commercial release for VM called SQL/DS and then the release for MVS called DB2.

DB2 comes in various editions and packages. All editions and packages of DB2 are built on the same source code base; they differ only in functionality and licensing terms that target the features, functions, and benefits of DB2 to their respective market segments and price groups.

The scope of tasks that can be solved using DB2 object extensions is significantly increased compared to the classic relational approach. By borrowing from the object-oriented model the elements most necessary for modern applications, DB2 has retained all the advantages of a relational DBMS. This makes it possible to use both relational and object-oriented approaches when building corporate information systems on its basis.

The IBM DB2 DBMS is the result of nearly 30 years of research and development work by IBM. The latest version of this DBMS (6.x) features one of the most elaborate set of management and optimization tools and a database engine that can grow from a Windows 95 laptop to a whole cluster of S/390 mainframes running OS/390.

The DB2 package is available in two editions: DB2 Workgroup and DB2 Enterprise Edition. This DBMS implements all the innovative database engine technologies known from previous versions of DB2, such as parallel query processing, a full set of replication tools, query summary tables to improve database performance, object-oriented database design features, and Java language features. In addition, the DB2 system is equipped with a complete set of multimedia extensions that allow you to save and manipulate text, sound and video fragments, images and geographic data. We can say that in terms of scalability, the database clustering technology developed by IBM specialists has no analogues. These extensions greatly facilitate the process of developing applications for the Web, as well as programs containing photographic images and voluminous text reports. The DB2 system is also quite competitive as a platform for application development, as there is a Stored Procedure Builder tool that automatically converts the SQL statement into the appropriate Java class and includes it in the database structure. In DB2 6.1, interoperability with other DBMSs has been greatly improved by allowing the use of Microsoft's OLE DB specification, a new database access standard. The DB2 administration tools, which are now rewritten in Java and available from the Web, deserve the highest praise.

The main disadvantages of this DBMS are the relative complexity of administration and the lack (yet) of implementations for popular server operating systems, such as LINUX.

In this DBMS, thanks to the Index Smart-Guide, it is possible to carry out tuning, forming optimal indexes for a given number of accesses, which characterizes the typical load on the database. DB2 is the only package that allows you to generate pivot tables, which significantly improves the efficiency of the DBMS as data warehouses. A PivotTable is a temporary workspace used by the database to store answers to frequently asked queries. Well, it can be said that with new functionality, as well as parallelization and the ability to select almost any type of join and index (except perhaps raster indexes), the DB2 6.1 model turns into the most inexpensive of high-performance systems. The administrative tools of this DBMS are quite appropriate for the level of tasks being solved, in addition, it provides exceptionally wide opportunities for working with multimedia data and for programming (which is clearly lacking in Microsoft SQL Server).

DBMS from Informix.

Recently, there has been a transition from relational DBMS to object-oriented ones (which is clearly seen in the example of Oracle). Informix also following this concept announced a new Centaur DBMS solution based on the Informix Dynamic Server 7.3 relational database and the Informix Universal Data Option object-relational database and combining the high performance of Dynamic Server when working with data with universality and multimedia functions of the Universal Data Option. This implementation is intended for the development of Internet systems. It is expected that this DBMS will have a flexible development environment with scalability to match the intensive workloads characteristic of the Internet, and tools for working with new types of data that have become ubiquitous with the development of the Web. The Java features implemented in the new system will allow developers to create stored procedures, user programs, and DataBlades components in this language, which Informix calls custom database extensions.

From the point of view of Inforix customers, this is a big step forward, because until now, when working with DataBlades, they could only use C and SPL, Informix's internal language for writing stored procedures. In addition, the Centaur package will be equipped with built-in ActiveX object handling. This will make it possible, for example, to create database stored procedures in the Visual Basic language; however, this requires the Centaur package to run in a Windows NT environment.

Centaur will be an add-on to Informix Dynamic Server and work with the traditional database format for this package, so that users will have all the old functions at their disposal, and upgrading the system to the new version will not be very difficult. In addition, the Centaur package will retain all of the design and programming capabilities that made the Informix Universal Server system an outstanding engineering achievement. The new system will be equipped with facilities for object-oriented database design, creation of specialized tables and indexing programs; it will allow users to embed their own functions in queries and not rely solely on standard SQL tools.

Conclusions.

Having considered the main characteristics of architectures for building AIS, server operating systems and DBMS, in the future, as the AIS architecture, we will choose the Internet / Intranet architecture, as the Linux server OS, as the Oracle 8i DBMS. The summary table presents the comparative characteristics of the two most common solutions today based on Microsoft SQL Server 7.0 (on NT) and Oracle8i (on Unix, Linux).

	Microsoft SQL Server 7.0
Administrative management
Graphic tools
Ease of maintenance
Data Engine
Working with multiple CPUs	Acceptable
Join function and index selection
Simultaneous access by multiple users
Multimedia data processing
Connecting to the Web
Audio, video, image processing
Search in this text
Interoperability		Acceptable
Interfacing with other databases
single sign-on
Work under various operating systems	Acceptable
Programming options	Acceptable
Stored procedures and triggers
Internal programming language
Database building

Object Oriented Systems
Working with branches
Replication
Distributed transaction processing
Remote administration
Organization of data warehouses and preparation of reports
Loading Tools
Analysis Tools

Just about the complex. Programs. Iron. Internet. Windows

Introduction to IBM DB2. Stored procedures and functions. Source materials and contact information

Implementations

Story

Peculiarities

Error processing

SQLCODE Structure Fields and Their Values

Notes

Links

Literature

See what "IBM DB2" is in other dictionaries:

Subd db2

Send your good work in the knowledge base is simple. Use the form below

Similar Documents

Introduction

IBM DB2 database management system

The concept of a database management system

The history of the creation of the DBMS

The history of the creation of the IBM DB2 DBMS