Database management systems and information storage support (IBM DB2). Introduction to IBM DB2

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:

Base management system IBM data db2.

2. Individual task:

4. Schedule:

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

Appendix

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 received during the study basic courses on computer science knowledge and skills on the methods of using, developing and implementing classical algorithms in the studied programming languages;

Master the methods professional search information on the Internet;

Review reporting requirements, methods and application environments to prepare 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

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.

1.2 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 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, analogues of some DBMS widely used abroad, capable of functioning on domestic hardware and software platforms, were developed in an accelerated mode. 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, 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 relational databases data 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 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 tooling platform that spans the entire application lifecycle (design, development, deployment, support, and management) for all relational DBMS IBM, with the prospect of further expanding 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 today's applications, allowing, for example, employee photos to be stored in a HR database, graphics, 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 enable code reuse by storing operations that are common to different applications on the server rather than being 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. Usage custom functions instead of directly accessing the data, it 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 images, complex text documents, video, audio, and even fingerprints.

1.10 Critical analysis of the DB2 DBMS

Consider the pros and cons of the DBMS.

There is a good free version;

V free version no restrictions on the size of the base;

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 locking mode, it works better than PostgreSQL (we are talking about parallelism, the area 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 for new versions, 1C releases "adapted" 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

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.

IBM is releasing three editions of DB2 for the first time -- for corporate clients, for SMBs (DB2 Express), and for developers (DB2 Express-C).

"DB2 Viper promises to change the competitive landscape in the database industry," said Ambuj Goyal, general manager, IBM Information Management. new era database server technologies that will strengthen our investments and initiatives to enable our customers to expand 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. -- Possibility to store various versions of the same XML documents with different structures in the same table will allow us not to spend hours on 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

The listing of the program 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 are cycle 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 array string 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.

Appendix

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

Similar Documents

Database management system as an integral part of an automated data bank. Structure and functions of the database management system. Classification of DBMS according to the method of accessing the database. SQL language in database management systems, Microsoft DBMS.

abstract, added 11/01/2009


Classification of databases according to the nature of the stored information, the method of storing data and the structure of their organization. Modern database management systems and programs for their creation: Microsoft Office Access, Cronos Plus, Base Editor, My SQL.

presentation, added 06/03/2014


Features of information management in the economy. The concept and functions of a database management system, the use of a standard relational query language. Tools for organizing databases and working with them. Database management systems in the economy.

test, added 11/16/2010


Algorithms for processing data arrays. Database management system. Relational data model. Presentation of information in the form of a table. Relational database management system. Graphical multi-window interface.

control work, added 01/07/2007


External storage devices. Database management system. Create, maintain and share databases with many users. The concept of a programming system. Data access pages. Macros and modules. Monopoly mode of operation.

abstract, added 01/10/2011


The main classifying features of the database management system. Data model, type of program and nature of its use. Programming tools for professional developers. Organization of data processing centers in computer networks.

presentation, added 10/14/2013


The concept and purpose, principles of construction and internal structure of the database management system, their functional features and capabilities, criteria for evaluating efficiency. Language and software. Using SQL, types and data models.

presentation, added 03/18/2015


Objects of the Access database management system. Requests, forms, reports. Data types: text, memo field, numeric. OLE object field, hyperlink, lookup wizard. Manual, automated and automatic means of creating database objects.

presentation, added 10/31/2016


Databases as an integral part of information systems. The study of the relationship between the concepts of information and data. Database management system. Structured data example. Ensuring logical independence. Operating system security.

test, added 06/15/2009


Storage and processing of data. Database system components. The physical structure of the data. Creation of tables in MS Access. Data loading, database queries. Development of an information system using MS Access database management system.

DB2(in Russian it is pronounced “dibi two”, tracing paper from the English “dibitu” is also common) is a family of software products in the field of information management from IBM.

Most often, when 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 the fraction means the platform and "/2" means 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 a mobile version DB2 Everyplace for Windows CE, Palm OS, Symbian OS, Neutrino and the Java Virtual Machine.

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 of RAM for the DBMS (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.

DB2 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 of the principles of the relational model, they implemented it as a "Structured English Query Language", SEQUEL for short. 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

The distinguishing features of DB2 include 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 from these declarative constructs. Unlike other SQL dialects, the DB2 SQL dialect has virtually no optimizer hints, little (and for a long time) lack of development of the 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 the SQL procedural extension for stored procedures, in accordance with 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 a database administrator (responsible for configuring DB2 software components and running them optimally on a computer system) and a database administrator (responsible for managing the data in a particular database).

The use, if necessary, of static SQL in programs and the concept of packages, unlike most other DBMS, allows 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 a text string describing 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

Links

• Program page on the IBM website
• DB2 on developerWorks - DB2 articles and training
• PlanetDB2 - DB2 Blogs

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

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 laptop running Windows 95 to an entire 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 an application development platform, 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 enabling 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).

A relational database is a set of relationships whose names match the relationship schema names in the database schema. Today, a large number of different SQL database servers are known. Let's focus on the following four leading server DBMS - Oracle8i, IBM DB2, Microsoft SQL Server and Informix - and compare them in operation at each of the main stages of functioning.

Oracle8i. Oracle8i package, endowed with the most advanced set of functions for working with the Java language and accessing data over the Internet, a concurrent access optimization system. The only drawback of this DBMS is the complexity of administration, however, all the costs of its implementation and development will later pay off with efficient and reliable work. (complexity and high cost are debatable). Among the main properties of the Oracle DBMS, the following should be noted: Highest reliability. The ability to partition large databases into sections (large-database partition), which makes it possible to effectively manage gigantic gigabyte databases; Availability of universal means of information protection; Effective methods for maximizing query processing speed; Bitmap indexing; Free tables (in other DBMS all tables are filled immediately upon creation); Parallelization of operations in a query. Availability of a wide range of development, monitoring and administration tools. Focus on Internet technologies. Solutions that are not inferior to the development of Oracle can only be found in IBM's DB2. Orientation to Internet technologies is the main motto of modern Oracle products. In this regard, the interMedia package, which provides processing of data in multimedia formats, and Jserver, an embedded tool for working with the Java language, which combines the capabilities of the Java language with the capabilities of relational databases, can be noted. Enterprise JavaBeans are the building blocks that make up Java Internet applications. Oracle adheres to the principle that all important functions must be managed from a single center, so the proposed interMedia module provides users with the most advanced features for working with multimedia objects: Very advanced tools for processing audio clips; Still images; Video clips; Geographical data (with a whole set of functions related to determining the location included in the Locator module). Oracle8i implements today's best tools for object-oriented database design, including tabular structures that allow inheritance of properties and methods of other tabular database objects, which will avoid errors when building a database and facilitate their maintenance. It should also be noted that the multiversioning concurrency optimization system developed by Oracle is one of the most important characteristics of the Oracle architecture (this function is available only in the InterBase DBMS from InterBase from Inprise). This feature eliminates the situation where one user has to wait for another to complete changes to the contents of the database (ie, there are no read locks in Oracle). This feature allows Oracle8i to execute more transactions per second per user than any other database. In terms of performance when working in a WEB environment under LINUX, Oracle occupies an honorable second place after the MySQL DBMS, while significantly surpassing all other DBMS in terms of reliability and security.

DBMS Microsoft SQL Server The most important characteristics of this DBMS are: ease of administration, the ability to connect to the Web, the speed and functionality of the DBMS server mechanism, the availability of remote access tools. . Also, this database is equipped with excellent replication tools that allow you to synchronize PC data with database information and vice versa. The OLAP server included in the package makes it possible to save and analyze all the data available to the user. In principle, this DBMS is a modern full-featured database that is ideal for small and medium-sized organizations. It should be noted that SQL Server is inferior to other DBMS under consideration in two important indicators: programmability and means of operation. When developing client database applications based on Java, HTML, the problem of insufficient SQL Server software often arises and it will be more difficult to use this DBMS than DB2, Informix, Oracle or Sybase systems. The global trend in the 21st century has become the almost universal transition to the LINUX platform, and SQL Server operates only in the Windows environment. Therefore, the use of SQL Server is advisable only if the ODBC standard is used exclusively to access the contents of the database, otherwise it is better to use other DBMS.

IBM DB2 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 thoughtful sets of management and optimization tools and a database engine that can grow from a Windows 95 laptop to an entire 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 an application development platform, 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. The DB2 6.1 model is emerging as the most cost-effective high performance system. 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 tools 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.

2) SQL SELECT clause. Built-in functions.

SELECT column FROM table WHERE column LIKE pattern

SELECT * FROM Store_Information WHERE store_name LIKE "%AN% ‘;

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2

SELECT * FROM Persons WHERE LastName BETWEEN "Hansen" AND "Pettersen";

SELECT * FROM Persons WHERE LastName NOT BETWEEN "Hansen" AND "Pettersen";

SELECT Company, OrderNumber FROM Orders ORDER BY( sorting ) Company;

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber;

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC( reverse order ) ;

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC , OrderNumber ASC( right . order ) ;

SELECT * FROM Persons WHERE FirstName="Tove" AND LastName="Svendson";

SELECT * FROM Persons WHERE firstname="Tove" OR lastname="Svendson" ;

SELECT * FROM Persons WHERE (FirstName="Tove" OR FirstName="Stephen") AND LastName="Svendson" ;

SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales< 500 AND Sales > 275);

FunctionsSELECTfunction( column) FROMtable AVG - average value in the column; COUNT - number of values ​​in a column; MAX is the largest value in the column; MIN - the smallest value in the column; SUM - sum of values ​​by column

Examples: SELECT AVG(Age) FROM Persons; SELECT COUNT(store_name) FROM Store_Information; SELECT COUNT(DISTINCT store_name) FROM Store_Information; SELECT MAX(Age) FROM Persons SELECT SUM(Sales) FROM Store_Information;

3) Serialization of transactions, conflicts of operations. Transaction serialization methods. Synchronization grips, granular synchronization grips. Transaction serialization methods. Predicate Synchronization Captures. Serialization based on timestamps.

To achieve isolation of transactions, the DBMS must use methods to regulate the joint execution of transactions. The plan (method) for executing a set of transactions is called serial if the result of the joint execution of transactions is equivalent to the result of some sequential execution of the same transactions. Transaction serialization- this is a mechanism for their implementation according to some serial plan. Providing such a mechanism is the main function of the DBMS component responsible for managing transactions. A system that supports transaction serialization provides real user isolation. The main implementation problem is choosing a method for serializing a set of transactions that does not overly restrict their parallelism. A trivial solution that comes to mind is really sequential execution of transactions. But there are situations in which it is possible to execute statements of different transactions in any order while preserving seriality. Examples include read-only transactions, as well as transactions that do not conflict on database objects. The following types of conflicts can exist between transactions: W-W - transaction 2 tries to modify an object modified by transaction 1 that has not ended; R-W - transaction 2 tries to modify an object read by transaction 1 that has not ended; W-R - Transaction 2 is trying to read an object modified by transaction 1 that has not ended. Transaction serialization practices are based on these conflicts.

Exists two basic approaches to serialization of transactions - based on synchronization captures of database objects and on the use of timestamps. The essence of both approaches is to detect transaction conflicts and eliminate them. The most common approach in centralized DBMSs (including systems based on the "client-server" architecture) is the approach based on adherence to the two-phase protocol of synchronization captures database objects. In general terms, the protocol is that before performing any operation in transaction T on the database object r, on behalf of transaction T, a synchronization capture of the object r is requested in the appropriate mode (depending on the type of operation). The main modes of synchronization captures are: joint mode - S (Shared), which means a shared capture of an object and is required to perform an object reading operation; exclusive mode - X (eXclusive), meaning exclusive capture of the object and required to perform insertion, removal and modification operations. Granular Synchronization Capture - an approach that sync captures can be requested on objects of different levels: files, relations, and tuples. The required object level is determined by the operation being performed (for example, to perform a delete operation on a relation, the entire relation must be the sync capture object, but to perform a tuple delete operation, that tuple). An object of any level can be captured in S or X mode. Predicate Synchronization Capture- this is not the capture of objects, but the conditions (predicates) that these objects satisfy. An alternative method of serializing transactions that works well in conditions of rare transaction conflicts and does not require the construction of a transaction wait graph. based on using timestamps. The main idea of ​​the method (of which there are many varieties) is as follows: if transaction T1 started before transaction T2, then the system provides such execution mode, as if T1 had been completed in its entirety before T2 began.

To do this, each transaction T is assigned a timestamp t corresponding to the start time T. When performing an operation on an object r, transaction T marks it with its timestamp and operation type (read or change). Before performing an operation on object r, transaction T1 performs the following actions: Checks if transaction T, which marked this object, has ended. If T has ended, T1 marks the object r and performs its operation. If transaction T has not completed, then T1 checks if the operations are in conflict. If the operations are non-conflicting, the timestamp with a lower value remains or is affixed to the object r, and transaction T1 performs its operation. If operations T1 and T conflict, then if t(T) > t(T1) (that is, transaction T is younger than T), T is rolled back and T1 continues. If t(T)< t(T1) (T "старше" T1), то T1 получает новую временную метку и начинается заново. К недостаткам метода временных меток относятся потенциально более частые откаты транзакций, чем в случае использования синхронизационных захватов. Это связано с тем, что конфликтность транзакций определяется более грубо. Кроме того, в distributed systems it is not very easy to generate global timestamps with a full order relation.

The IBM DB2 database management system began its development in the distant 70s and now occupies a strong position in the corporate DBMS market, meeting high requirements for performance, reliability, security and scalability.

Igor Bulatenko, Information Security Specialist, Positive Technologies

The IBM DB2 database management system began its development in the distant 70s and now occupies a strong position in the corporate DBMS market, meeting high requirements for performance, reliability, security and scalability. In the private sector, DB2 has not been widely adopted, despite the availability of a free version of IBM DB2 Express. Perhaps this is the reason why there aren't many articles on the Internet about configuring and using DB2.

The DB2 security model has a wide range of functionality and allows you to protect data both from external influences and to differentiate access rights for internal users using the DBMS itself.

However, it is difficult for an unprepared user to understand all this diversity from scratch, so some important aspects will be discussed in this article.

Point of entry

The entry point to DB2 looks like this: DBMS -> instance (instance) that can be bound to a specific port -> specific database name. Security settings can be changed both in a specific instance and in a specific database.

Authentication

Authentication is the primary security mechanism that is applied when you try to connect to a DB2 server. Authentication checks if the credentials provided are correct. Main Feature in DB2 is that user authentication is done by external plugins only. Internal users, unlike Oracle or MS SQL Server, do not exist here. Even the user creation feature found in IBM Data Studio does not actually create a user, but assigns the database connection privilege to the specified user.

There are several authentication options, the desired option is controlled by the AUTHENTICATION parameter in the database manager. The value of this parameter affects where clients will be authenticated (on the server side or on the client side) and whether data will be transmitted in encrypted form (values ​​ending with _ENCRYPT). Supported values ​​for this parameter are available at:

You can view the database manager configuration by querying the sysibmadm.dbmcfg table, but this requires access to any of the databases, which is not always possible. if you have local access to the server, you can open the processor command line(db2 or db2.exe on Windows), connect to the instance and run the following commands:

db2 => attach to db2inst1
db2 => get database manager configuration

The default value for the AUTHENTICATION parameter is SERVER. Validation of the provided user credentials is performed on the server side by means of the operating system, but all data is transmitted in clear text and can be intercepted by an attacker.

Let's see how the captured information looks like in Wireshark.

The login and password transmitted from the client are visible in the package when viewing EBCDIC.

When changing the authentication type to SERVER_ENCRYPT, the login and password will be transmitted in encrypted form and checked on the server side.

The value changes as follows:

db2 => attach to db2inst1
db2 => update database manager configuration using authentication server_encrypt
db2 => db2stop force
db2 => db2start

The authentication package will then look like this:

However, the text of requests and the result will still be transmitted in the clear.

Wireshark request packet:

Packet with response in Wireshark:

If the AUTHENTICATION parameter is set to DATA_ENCRYPT, then the user's credentials are encrypted, as well as information transmitted between the client and the server.

The value is changed in the same way as the example above:

db2 => attach to db2inst1
db2 => update database manager configuration using authentication data_encrypt
db2 => db2stop force
db2 => db2start

After that, the transmitted data will also be encrypted:

Also, you need to pay attention to the CLIENT authentication type. At this type authentication, it is considered that a secure communication channel exists between the client and the server, and if the user has gained access to the client, he can access the server without checking the correctness of the credentials. That is, authentication as such occurs on the client side, verification is not performed on the server side. Even if the user who connects to the server does not have access rights, he still receives all the privileges that are assigned to the PUBLIC group. Therefore, you should not use this type of authentication, it will provide attackers with the opportunity to gain access to the server without much effort.

If suddenly, for some reason, this type of authentication is needed, then you need to take into account that there are two more additional parameters that ultimately affect how the user's credentials will be verified. These are the trust_allclnts parameter, with which you can specify which clients are considered trusted, and the trust_clntauth parameter, which determines where to check the login and password, if they were passed during the connection. Both of these options affect authentication only if the AUTHENTICATION option is set to CLIENT.

If successful, the user ID is mapped to a DB2 ID. Typically, the ID is the same as the username, but it uses uppercase characters.

Authorization

Permissions at the level of a particular instance are written in the configuration of the database manager. These are the following powers:

• SYSADM (system administrator authority);
• SYSCTRL (permission to control the system);
• SYSMAINT (permission to maintain the system);
• SYSMON (permission to monitor the system).

These privileges are set by specifying the group that the user will belong to. For this, the following parameters of the dbmcfg file are used (according to the above permissions):

It is not easy to get a list of users who are members of a group using DB2 tools, you need to do it in the operating system itself or analyze which groups a particular user belongs to (see the "useful queries" tab for a query).

When configuring DB2, be sure to check the list of users that have been assigned the SYSADM authority. This permission allows you to manage all database objects.

The permissions of a particular database can be viewed in the SYSCAT.DBAUTH view. Pay attention to the CONNECTAUTH privilege, which determines whether the user will have access to the database or not, and the NOFENCEAUTH privilege, which is responsible for creating non-fenced (not fenced) procedures and functions. Such procedures are performed in the database address space and, in the event of an error, may violate the integrity of the database and tables in it.

Privilege

The privileges of routines (functions, procedures, and methods) can be viewed in the SYSCAT.ROUTINEAUTH view. Everything here is not entirely trivial, depending on the SPECIFICNAME and TYPENAME fields, privileges can be issued to all subroutines of a given scheme.

Users, groups, roles

Also, DB2 has a built-in PUBLIC role. A database user does not need to be granted the PUBLIC role: it is not possible to revoke the PUBLIC role from a user. When a privilege is granted to the PUBLIC role, the privilege is actually granted to all database users. You should not grant any database authority to the PUBLIC role. Privileges on tables and views should be granted with extreme caution, only for viewing and without the possibility of reassignment (WITH GRANT OPTION).

Due to the peculiarity of authentication when granting privileges, the existence of a user or group in the system is not checked. As a result, authentication users may appear in the system without being tied to real users of the system. You can find such users using the following SQL query:

To search for such groups, a similar query is used, but the query indicates that data about PUBLIC should not be displayed:

LBAC

Automatic scanning tools

Useful queries and commands

Get database manager settings:

or

Change database manager option:

After that, you need to restart the instance:

Get database settings:

or

List of operating system users:

List of operating system groups:

Display the current database name:

Enter current username:

Get a list of groups the user belongs to:

List of all installed DBMS:

List of all instances in the DBMS:

Limit the number of output lines: