DB2 Universal Database V8.1 Certification Exam 700 Study Guide
| Earlier, we saw that a DB2 UDB database is comprised of four distinct types of objects:
We have also seen how system objects, recovery objects, and storage objects come into play whenever a new database is created. Now, let's turn our attention to database objects. Database objects, also known as data objects, are used to control how all user data (and some system data) is stored and organized. Data objects include:
Tables
A table is a logical database object that acts as the main repository in a database. Tables present data as a collection of unordered rows with a fixed number of columns. Each column contains values of the same data type or one of its subtypes , and each row contains a set of values for every column available. Usually, the columns in a table are logically related , and additional relationships can be defined between two or more tables. The storage representation of a row is called a record, the storage representation of a column is called a field , and each intersection of a row and column is called a value. Figure 4-11 shows the structure of a simple database table. Figure 4-11. A simple database table.
With DB2 UDB, five types of tables are available: Base tables. User-defined tables designed to hold persistent user data. Result tables . DB2 Database Manager-defined tables populated with rows retrieved from one or more base tables in response to a query. Summary tables. User-defined tables whose column definitions are based on the results of a query that is also used to populate the table. Summary tables are used to improve query performance. Declared temporary tables. User-defined tables used to hold nonpersistent data temporarily, on behalf of a single application. Declared temporary tables are explicitly created by an application when they are needed and implicitly destroyed when the application that created them terminates its last database connection. Typed tables. User-defined tables whose column definitions are based on the attributes of a user-defined structured data type. Data associated with base tables, summary tables, and typed tables is physically stored in tablespaces ”the actual tablespace used is specified during the table creation process. Indexes
An index is an object that contains an ordered set of pointers that refer to rows in a base table. Each index is based upon one or more columns in the base table they refer to, yet they are stored as separate entities. Figure 4-12 shows the structure of a simple index, along with its relationship to a base table. Figure 4-12. A simple index.
Indexes are used primarily to enforce record uniqueness and to help the DB2 Database Manager quickly locate records in response to a query. Indexes can also provide greater concurrency in multiuser environments ”because records can be located faster, acquired locks do not have to be held as long. However, there is a price for these benefits: Additional storage space is needed whenever indexes are used, and performance can actually decrease when new data is added to a base table and existing data is modified. In both cases, the operations performed must be applied to both the base table and to any corresponding indexes. Views
Views are used to provide a different way of looking at the data stored in one or more base tables. Essentially, a view is a named specification of a result table that is populated whenever the view is referenced in an SQL statement. Like base tables, views can be thought of as having columns and rows. And in most cases, data can be retrieved from a view the same way it can be retrieved from a table. However, whether or not a view can be used in insert, update, and delete operations depends upon how it was defined ”views can be defined as being insertable, updatable, deletable, and read-only. Although views look similar to base tables, they do not contain real data. Instead, views refer to data stored in other base tables. Only the view definition itself is actually stored in the database. (In fact, when changes are made to the data presented in a view, the changes are actually made to the data stored in the base table(s) the view references.) Figure 4-13 shows the structure of a simple view, along with its relationship to a base table. Figure 4-13. A simple view that references a base table.
Because views allow different users to see different presentations of the same data, they are often used to control access to data. For example, suppose you had a table that contained information about all employees that worked for a particular company. Managers could be given access to this table using a view that only allows them to see information about the employees that work in their department. Members of the payroll department, on the other hand, could be given access to the table using a view that only allows them to see the information needed to generate employee paychecks . Both sets of users are given access to the same table; however, because each user works with a different view, it appears that they are working with their own tables.
Aliases
An alias is an alternate name for a table or view. (Aliases can also be created for nicknames that refer to data tables or views located on federated systems.) Aliases can be used to reference any table or view that can be referenced by its primary name. However, an alias cannot be used in every context that a primary table or view name can. For example, an alias cannot be used in the check condition of a check constraint, nor can it be used to reference a user-defined temporary table. Like tables and views, an alias can be created, dropped, and have comments associated with it. However, unlike tables (but similar to views), aliases can refer to other aliases, using a process known as chaining . Aliases are publicly referenced names , so no special authority or privilege is required to use them. However, access to the table or view that is referred to by an alias still has the authorization requirements associated with these types of objects. So why would you want use an alias instead of the actual table/view name? Suppose you needed to develop an application that interacts with a table named EMPLOYEES that resides in your company's payroll database. During the development process, you would like to run the application against a test EMPLOYEES table; then when development is complete, the application will need to run against the production EMPLOYEES table. By using an alias instead of a base table name in all table references made by the application, you can quickly change the application so that it works with the production EMPLOYEES table instead of the test EMPLOYEES table simply by changing table name the alias refers to. Schemas
Schemas are objects that are used to logically classify and group other objects in the database. Because schemas are objects themselves , they have privileges associated with them that allow the schema owner to control which users can create, alter, and drop objects within them. Most objects in a database are named using a two-part naming convention. The first (leftmost) part of the name is called the schema name or qualifier , and the second (rightmost) part is called the object name . Syntactically, these two parts are concatenated and delimited with a period (for example, HR.EMPLOYEE). Each time an object that can be qualified by a schema name is created, it is assigned to the schema that is provided with its name. (If no schema name is provided, the object is assigned to the default schema, which is usually the user ID of the individual that created the object.) Some schema names, such as the names assigned to the four schemas implicitly created when a database is created (SYSIBM, SYSCAT, SYSSTAT, and SYSFUN), are reserved and cannot be used. Schemas are usually created implicitly when other objects are created. (When a new database is created, all users are granted IMPLICIT_SCHEMA privileges. This allows any user to create objects in any schema not already in existence.) However, schemas can be created explicitly as well. Triggers
A trigger is used to define a set of actions that are to be executed whenever an insert, update, or delete operation is performed on a specified table. Triggers can be used, along with referential constraints and check constraints, to enforce data integrity rules and business rules. (A data integrity rule might be that whenever the record for an employee is deleted from the table that holds employee information, the corresponding record will be deleted from the table that holds payroll information. A business rule might be that an employee's salary cannot be increased by more than 10 percent.) Triggers can also be used to update other tables, automatically generate or transform values for inserted and/or updated rows, or invoke functions to perform special tasks . By using triggers, the logic needed to enforce such business rules can be placed directly in the database, and applications that work with the database can concentrate solely on data storage, data management, and data retrieval. And by storing the logic needed to enforce data integrity rules and business rules directly in the database, it can be modified as data integrity rules and business rules change without requiring applications to be recoded and recompiled. Before a trigger can be created, several criteria must be identified: Subject table. The table that the trigger is to interact with. Trigger event. An SQL operation that causes the trigger to be activated whenever it is performed against the subject table. This operation can be an insert operation, an update operation, or a delete operation. Trigger activation time. Indicates whether the trigger should be activated before or after the trigger event occurs. A before trigger will be activated before the trigger event occurs; therefore, it will be able to see new data values before they are inserted into the subject table. An after trigger will be activated after the trigger event occurs; therefore, it can only see data values that have already been inserted into the subject table. Set of affected rows. The rows of the subject table that are being inserted, updated, or deleted. Trigger granularity. Specifies whether the actions the trigger will perform are to be performed once for the entire insert, update, or delete operation or once for every row affected by the insert, update, or delete operation. Triggered action. An optional search condition and a set of SQL statements that are to be executed whenever the trigger is activated. (If a search condition is specified, the SQL statements will only be executed if the search condition evaluates to true.) If the trigger is a before trigger, the triggered action can include statements that retrieve data, set transition variables , or signal SQL states. If the trigger is an after trigger, the triggered action can include statements that retrieve data, insert records, update records, delete records, or signal SQL states. Triggered actions can refer to the values in the set of affected rows using what are known as transition variables . Transition variables use the names of the columns in the subject table, qualified by a specified name that indicates whether the reference is to the original value (before the insert, update, or delete operation is performed) or the new value (after the insert, update, or delete operation is performed). Another means of referring to values in the set of affected rows is through the use of transition tables . Transition tables also use the names of the columns in the subject table, but they allow the complete set of affected rows to be treated as a table. Unfortunately, transition tables can only be used in after triggers. The activation of one trigger may cause the activation of other triggers, or even the reactivation of the same trigger. This event is known as trigger cascading , and because trigger cascading can occur, a significant change can be made to a database as the result of a single INSERT , UPDATE , or DELETE statement. User-Defined Data Types
As the name implies, user-defined data types (UDTs) are data types that are created (and named) by a database user. A user-defined data type can be a distinct data type that shares a common representation with one of the built-in data types provided with DB2 UDB, or it can be a structured type that consists of a sequence of named attributes, each of which has its own data type. Structured data types can also be created as subtypes of other structured types, thereby defining a type hierarchy. User-defined data types support strong data typing, which means that even though they may share the same representation as other built-in or user-defined data types, the value of one user-defined data type is only compatible with values of that same type (or of other user-defined data types within the same data type hierarchy). As a result, user-defined data types cannot be used as arguments for most of the built-in functions available. Instead, user-defined functions (or methods) that provide similar functionality must be developed whenever that kind of capability is needed. User-Defined Functions (or Methods)
User-defined functions (UDFs) are special objects used to extend and enhance the support provided by the built-in functions available with DB2 UDB. Like user-defined data types, user-defined functions (or methods) are created and named by a database user. A user-defined function can be an external function written in a high-level programming language, or a sourced function whose implementation is inherited from some other function that already exists. Like all built-in functions, user-defined functions are classified as being scalar, column (or aggregate), or table in nature. Scalar functions return a single value and can be specified in an SQL statement wherever a regular expression can be used. (The built-in function SUBSTR() is an example of a scalar function.) Column functions return a single-valued answer from a set of like values (a column) and can also be specified in an SQL statement wherever a regular expression can be used. (The built-in function AVG() is an example of a column function.) Table functions return a table to the SQL statement that references it and can only be specified in the FROM clause of a SELECT statement. Table functions are used to work with data that does not reside in a DB2 UDB database and/or to convert such data into a format that resembles that of a DB2 table. (The built-in function SNAPSHOT_TABLE is an example of a table function.) Sequences
A sequence is an object that is used to automatically generate data values. Unlike an identity column, which is used to generate data values for a specific column in a table, a sequence is not tied to any specific column or any specific table. Instead, a sequence behaves like a unique counter that resides outside the database, with the exception that it does not present the same concurrency and performance problems that can occur when external counters are used. All sequences have the following characteristics:
In addition, sequences generate values in one of three ways:
To facilitate the use of sequences in SQL operations, two expressions are available: PREVVAL and NEXTVAL . The PREVVAL expression returns the most recently generated value for the specified sequence, and the NEXTVAL expression returns the next value for the specified sequence.
|