Introduction
This unit introduces the DB2 Database Manager and the basic concepts behind a table.
After completing this unit, you should be able to:
- Identify the advantages of a relational database
- Define a relation
- Name the language used to talk to a relational database manager (RDBM)
- List three characteristics assigned to each column
- Define the tasks performed by DB2 when running an application
- Define the roles that are performed within DB2
Database managers are sophisticated software programs that maintain our data. Each database manager provides a set of automated services. We will discuss some of the more commonly used DB2 services in this course.
The word relation is simply a synonym for the word table. A relational database is one in which the data is perceived as being stored in rows and columns. Relational database managers understand SQL, the programming language for relational databases. End users and applications access DB2 data through SQL.
In this unit we will discuss the basic structure of a table, and will mention three characteristics that are assigned to all columns. We will discuss potential tasks associated with DB2, and the various I/T roles that support and use the DB2 environment.
The ease of working with a relational database
We work with tables of data every day. An Excel spreadsheet usually consists of rows and columns. A phone book is another example of a table of data. Each page has the same columns (name, address, and phone number), just different rows. Phone books are in alphabetical sequence. The rows in a relational database may or may not be in a particular order.
A relational database is a table database, consisting of rows and columns. At each juncture of a row and column, one and only one value may be present, according to relational theory.
Load data into a DBMS
Database managers provide many services, such as:
- Logging – Saving “before” and “after” images (called UNDO and REDO records) of any DB2 rows changed by SQL (INSERT, UPDATE, DELETE).
- Security – Monitoring and controlling access to DB2 data
- Optimization – Minimizing the use of system resources (CPU cycles and physical I/O) when performing SQL queries and doing other DB2 work
- Locking – Serializing the access to DB2 data in order to allow concurrency, while guaranteeing data integrity
- Recovery – Restoring DB2 data due to outages, system failures or corruption by incorrect program logic
- Data integrity – Database theory demands that all changes to data follow the ACID model. From examples during this course, you will see that DB2 does apply the characteristics of this model:
ATOMICITY
refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. A transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won’t be debited if the other is not credited as well. It is the concept of “all or nothing”.
CONSISTENCY
refers to the database being in a legal state when the transaction begins and when it ends. This means that a transaction can’t break the rules, or integrity constraints, of the database. If an integrity constraint states that no salaries can exceed $100,000, then any transaction violating this rule will be aborted. DB2 ensures that only valid data is allowed within DB2.
ISOLATION
refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; a bank manager can see the transferred funds on one account or the other, but never on both — even if they ran their query while the transfer was still being processed. More formally, isolation means the transaction history (or schedule) is able to be serialized. For performance reasons, this ability is the most often relaxed constraint. Uncommitted Reads can be allowed in DB2, but it is not the default behavior.
DURABILITY
refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won’t need to abort the transaction. Typically, all transactions are written into a log that can be played back to recreate the system to its state right before the failure. A transaction can only be deemed committed after it is safely in the log. DB2 ensures that no data is “lost”.
IBM’s DB2 adheres to these guidelines, and therefore passes the ACID test!
DB2 is a Relational Database Management System
The database management system we are working with in class is DB2. DB2 is a relational database management system (RDBMS). The word relation is a synonym for the word table. A relational database management system is a database manager that manages data perceived to be stored in a table format, that is, columns and rows. IBM Informix is another example of a Relational Database Management System.
Table names in DB2
Every table in DB2 must be given a unique name. A table always has two parts, separated by a period … a schema/owner/creator name, followed by the table name. Optionally, if there are multiple DB2 systems connected together, it may become necessary to qualify the table with a location name as well. When that is the case, the location name comes first.
In the visual above, we refer to PARTS as the simple table name. There could be many schemas or owners with a DB2 table named PARTS. There could be a TEST.PARTS, a QA.PARTS and a PROD.PARTS …a PARTS table each for a Test, a Quality Assurance and a Production environment. PROD.PARTS, which is referred to as a qualified table name, is the PARTS table that was created by the schema/owner known as PROD. Finally, if our company had manufacturing facilities in Atlanta, Chicago and Seattle, and we needed to maintain a PROD.PARTS table and DB2 system at each of those locations, we could fully qualify the table name… for example, ATLANTA.PROD.PARTS.
Each simple table name must be unique within a schema. Each qualified table name must be unique within a location.
Table creation
Talking to DB2 in English, French, or German, and so forth, would be too cumbersome. What is needed in order to more effectively communicate with DB2 is a language that is more precise (accurate and specific), and more concise (shorter, abbreviated).