7.1. DB2 Database Objects: The Big Picture A database is a collection of database objects, and you can create it in one or more database partitions. A database partition, as its name implies, is part of a database. We discuss these concepts in more detail in the next sections; for now, we introduce you to the DB2 database objects. Figure 7.1 illustrates these objects in a database created in a single-partition environment (database partition 0). The database objects are described next. A partition group is a logical object representing a collection of database partitions. In a single-partition environment, partition groups are not relevant; however, in multi-partition environments, a partition group facilitates the work of a database administrator, as he is able to perform database operations on several partitions at a time. Partition groups can contain one or more table spaces. In Figure 7.1, partition group pg1 contains table space tbls1. Table spaces are logical objects that associate tables and indexes to the physical devices where these objects are stored, as well as to the physical memory where the data in these objects is cached. Tables and indexes must be created inside a table space as illustrated in Figure 7.1, where tables t1 and t2 and index ix1 are all created inside table space tbls1. Tables consist of rows and columns, like spreadsheets. Data can be inserted, deleted, and updated within a table. Figure 7.1 has two tables, t1 and t2. Indexes are an ordered set of keys each pointing to a row in a table. They improve the performance when looking for specific rows. Indexes can also be used to guarantee the uniqueness of rows. In Figure 7.1, index ix1 is associated to table t1. A buffer pool is an area in physical memory that caches the database information most recently used. Without buffer pools, every single piece of data has to be retrieved from disk, which is very slow. Buffer pools are associated to tables and indexes through a table space. In Figure 7.1, table space tbls1 is associated to buffer pool bp1, therefore tables t1 and t2 and index ix1 use buffer pool bp1. A view is an alternate way of representing data that exists in one or more tables. A view can include some or all of the columns from one or more tables. It can also be based on other views. In Figure 7.1, view v1 is based on table t1. Every object in the database is created with a two-part name separated by a dot: schema_name.object_name The first part of this two-part name is the schema name. A schema is an object that provides a logical grouping of other database objects. A schema can be owned by an individual who can control access to the objects within it. Schemas can be implicitly or explicitly specified when accessing an object. A trigger is an object that contains application logic that is triggered by specific actions like an update to a table. For example, in Figure 7.1, a trigger can be created so that after table t1 is updated, table t2 is also updated with some other information. A stored procedure is an object used to move application logic to your database. By keeping part of the application logic in the database, there are performance improvements as the amount of network traffic between the application and the database is considerably reduced. User-defined functions (UDFs) allow database users to extend the SQL language by creating functions that can be used anywhere a DB2 built-in function is used. Similar to stored procedures, application logic can be moved to the database by using UDFs. A package is an object containing the compiled version of your SQL queries as well as the access path that the DB2 optimizer, the brain of DB2, has chosen to retrieve the data for those queries. A sequence object allows the generation of unique numbers in sequence. These numbers can be used across the database as a unique identifier for tables or for applications.
Figure 7.1. An overview of the DB2 database objects
To create, modify, or delete database objects, you use the Data Definition Language (DDL) consisting of the following SQL statements: The following objects can be created and dropped using the CREATE and DROP statements, respectively: Tables Indexes Schemas Views User-defined functions User-defined types Buffer pools Table spaces Stored procedures Triggers Servers (for federated databases) Wrappers (for federated databases) Nicknames (for federated databases) Sequences
You use the DECLARE statement to create temporary tables, and the ALTER statement to change one or more characteristics of an existing database object. You can alter most, but not all, of the database objects created with the CREATE statement. The CREATE, DECLARE, ALTER, and DROP statements are used throughout this chapter. |