DB2 - Database Details

A database is a collection of related data tables or entities. For example, a typical database for an organization would consist of a customer, an order, and order details tables. All these tables are related to one another in some way. In this example, customers have orders and orders have order details. Even though each table exists on its own, collectively the tables comprise a database.

Database is a group of logically related Tablespaces and Indexspaces, which in turn contain tables and indexes respectively.

The default database, DSNDB04, is predefined in the DB2 installation process.

Table Space

Data is actually stored in a structure known as a table space.

Each table space correlates to one or more individual physical VSAM data sets in the DASD volumes of storage Group.

Each table space contains one or more tables.

There are three different types of table space:

1. Simple table space
2. Segmented table space
3. Partitioned table space

You will talk about table space in detail while dealing with “Locks”.

Index Space

An index space is the underlying storage structure for index data.

Each index space correlates to one or more individual physical VSAM data sets in the DASD volumes of storage Group.

It is automatically created by DB2 whenever an index is created.There can only be one index in an index space.

Table

Tables are logical structures maintained by the database manager.

When you store information in your filing cabinet you do not just toss it in a drawer. Rather, you create files within the filing cabinet, and then you file related data in specific files.

In the database world, that file is called a table. A table is a structured file that can store data of a specific type. A table might contain a list of customers, a product catalog, or any other list of information.

Each table has a name, and within a table, each column has a name. No particular ordering is maintained among the rows of a table, but rows can be retrieved in an order determined by values in their columns. The data in a table is logically related. All table data is assigned to table spaces.

A table consists of data logically arranged in columns and rows.

Column

• Tables are made up of columns. A column contains a particular piece of information within a table.

• Column is a single field in a table. All tables are made up of one or more columns.

• The best way to comprehend this is to envision database tables as grids, somewhat like spreadsheets. Each column in the grid contains a particular piece of information. In a customer table,

  For example, one column contains the customer number, another contains the customer name, and the address, city, state, and zip are all stored in their own columns.

Row

• Data in a table is stored in rows.

• Row is a record in a table.

• Again, envisioning a table as a spreadsheet style grid, the vertical columns in the grid are the table columns, and the horizontal rows are the table rows.

For example, a customer table might store one customer per row. The number of rows in the table is the number of records in it.

Types of Table

Base Table: A base table is created with the CREATE TABLE statement and is used to hold persistent user data.

Result Table: A result table is a set of rows that the database manager selects or generates from one or more base tables to satisfy a query.

Summary Table: A summary table is a table defined by a query that is also used to determine the data in the table. Summary tables can be used to improve the performance of queries. If the database manager determines that a portion of a query can be resolved using a summary table, the database manager can rewrite the query to use the summary table.

Temporary Table: A declared temporary table is created with a DECLARE GLOBAL TEMPORARY TABLE statement and is used to hold temporary data on behalf of a single application. This table is dropped implicitly when the application disconnects from the database

Index

An index is a data access aid that can be created on a table. It is an ordered set of pointers to rows in a table.

Each index is based on the values of data in one or more columns in a table. An index is an object that is separate from the data in the table. When you create an index, the database manager builds the structure and maintains it automatically.

An index can serve the following purposes:

• Improve performance. In most cases, access to data is faster with an index. Provides a fast way to find rows in a table based on their values in the key columns.

• Enforces uniqueness rules by defining a column or group of columns as a unique index or primary key.

• Provides a logical ordering of the rows of a table based on the key column values.

• Clusters the rows of a table in physical storage according to the order of the defined index.

View

A view provides a different way of looking at the data in one or more tables. View is a virtual table consisting of a SQL SELECT statement that accesses data from one or more tables or views.

A view never stores data. When you access a view, the SQL statement that defines it is executed to derive the requested data.

A view has columns and rows just like a base table. All views can be used just like base tables for data retrieval.

You can use views to control access to sensitive data, because views allow multiple users to see different presentations of the same data. For example, several users may be accessing a table of data about employees. A manager sees data about her employees but not employees in another department. A recruitment officer sees the hire dates of all employees, but not their salaries; a financial officer sees the salaries, but not the hire dates. Each of these users work with a view derived from the base table. Each view appears to be a table and has its own name.

When the column of a view is directly derived from the column of a base table, that view column inherits any constraints that apply to the base table column. For example, if a view includes a foreign key of its base table, insert and update operations using that view are subject to the same referential constraints as is the base table. Also, if the base table of a view is a parent table, delete and update operations using that view are subject to the same rules as are delete and update operations on the base table.

A view can become inoperative (for example, if the base table is dropped); if this occurs, the view is no longer available for SQL operations.

The best way to recognize views is to look at an example. (You will study about the SQLs in detail further. This example is just to make you understand the concept of views).

You have following three tables:

• Customers
• Orders
• OrderDetails

You need to retrieve the customers who had ordered a specific product.

The column details are as follows.

The query is as follows:

SELECT cust_name, cust_contact
    FROM Customers, Orders, OrderDetails
    WHERE Customers.cust_id = Orders.cust_id
     AND OrderDetails.order_num = Orders.order_num
    AND prod_id = 'RGAN01';

Anyone needing this data would have to understand the table structure, as well as how to create the query and join the tables. To retrieve the same data for another product (or for multiple products), the last WHERE clause would have to be modified.

Now imagine that you could wrap that entire query in a virtual table called ProductCustomers. You could then simply do the following to retrieve the same data:

SELECT cust_name, cust_contact
    FROM ProductCustomers
    WHERE prod_id = 'RGAN01';

This is where views come into play. ProductCustomers is a view, and as a view, it does not contain any columns or data. Instead it contains a query—the same query used above to join the tables properly.

Why Use Views?

Here are some common uses of views:

• To draw data from multiple tables.

• To reuse SQL statements.

• To simplify complex SQL operations. After the query is written, it can be reused easily, without having to know the details of the underlying query itself.

• To expose parts of a table instead of complete tables.

• To secure data. Users can be given access to specific subsets of tables instead of to entire tables.

• To change data formatting and representation. Views can return data formatted and presented differently from their underlying tables.

For the most part, after views are created, they can be used in the same way as tables. You can perform SELECT operations, filter and sort data, join views to other views or tables, and possibly even add and update data. There are some restrictions on this last item. The important thing to remember is views are just that, views into data stored elsewhere. Views contain no data themselves, so the data they return is retrieved from other tables. When data is added or changed in those tables, the views will return that changed data.

There are some performance issues with views because views contain no data, any retrieval needed to execute a query and that must be processed every time the view is used. If you create complex views with multiple joins and filters, or if you nest views, you may find that performance is dramatically degraded. Be sure you test execution before deploying applications that use views extensively.

Synonym

• An alternative, private name for a table or view.

• A synonym can be used only by the individual who creates it.

When a table or view is dropped, all synonyms defined on it are also dropped.

Alias

1. A locally defined name for a table or view in the same local DB2 subsystem or in a remote DB2 subsystem. Aliases give DB2 location independence because an alias can be created for a table at a remote site, thereby freeing the user from specifying the site that contains the data. Aliases can be used also as a type of global synonym because they can be accessed by anyone, not only by their creator.

2. When a table/view is dropped, all aliases defined on it are NOT dropped.

3. Use Synonyms for Program Development, use Aliases for Distributed Applications and use Views for security and joining.

Following table gives the difference between “Synonym” and “Alias”

Physical Storage of Data:

The following figure represents how the data is physically stored in DB2 system.

Buffer Pool

Buffer pools are areas of virtual storage in which DB2 temporarily stores pages of table spaces or indexes.

Buffer pools improve database performance. If a needed page of data is already in the buffer pool, that page is accessed faster than if that page had to be read directly from disk. The database manager has agents whose tasks are to retrieve data pages from disk and place them in the buffer pool (prefetchers), and to write modified data pages from the buffer pool back to disk (page cleaners).

The reading and writing of data pages to and from disk is called disk input/output (I/O). Avoiding the wait associated with disk I/O is the primary way to improve the performance of the database. How you create the buffer pool, and configure the database manager and the agents associated with the buffer pool, controls the performance of the database.

In DB2, you have the option of allocating 80 Buffer Pools:

• 50 4K buffer pools and
• 10 8K, 16K and 32K buffer pools.

The default buffer pool is BP0.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!