"http://www.w3.org/TR/l11/DTD/l11.dtd">
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. 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 Tables consist of rows and columns, like spreadsheets. Data can be inserted, deleted, and updated within a table. 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 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 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.
To create, modify, or delete database objects, you use the Data Definition Language (DDL) consisting of the following SQL statements:
CREATE
DECLARE
ALTER
DROP
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
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.