List of Figures

Chapter 1: IBM DB2 9 Certification

Figure 1-1: IBM Certified Database Associate-DB2 9 Fundamentals certification roadmap.

Figure 1-2: IBM Certified Database Administrator-DB2 9 for Linux, UNIX, and Windows certification roadmap.

Figure 1-3: The accelerated approach for acquiring IBM Certified Database Administrator-DB2 9 for Linux, UNIX, and Windows certification.

Figure 1-4: IBM Certified Database Administrator-DB2 9 for z/OS and OS/390 certification roadmap.

Figure 1-5: IBM Certified Application Developer-DB2 9 Family certification roadmap.

Figure 1-6: IBM Certified Advanced Database Administrator-DB2 9 for Linux, UNIX, and Windows certification roadmap.

Figure 1-7: IBM Certified Solution Designer-DB2 Data Warehouse Edition V9.1 certification roadmap.

Figure 1-8: Title screen of the IBM Certification Exam testing software.

Figure 1-9: Typical question panel of the IBM Certification Exam testing software.

Figure 1-10: Time Remaining dialog.

Figure 1-11: Question panel for questions expecting multiple answers.

Figure 1-12: Question panel for questions that contain an exhibit.

Figure 1-13: Sample exhibit panel.

Figure 1-14: Item (question) review panel of the IBM Certification Exam testing software.

Figure 1-15: Item (question) review panel with all incomplete and marked items (questions) resolved.

Figure 1-16: Confirmation dialog for end of exam session.

Figure 1-17: Warning dialog for ending exam with incomplete items.

Figure 1-18: Exam results panel of the IBM Certification Exam testing software.

Figure 1-19: Section scores panel.

Chapter 2: Planning

Figure 2-1: DB2 Family Editions.

Figure 2-2: Simple DB2 client/server environment.

Figure 2-3: The Control Center (advanced view).

Figure 2-4: The Control Center Toolbar.

Figure 2-5: The Legend dialog.

Figure 2-6: The Replication Center.

Figure 2-7: The Command Editor.

Figure 2-8: The SQL Assist icon on the Command Editor toolbar.

Figure 2-9: SQL Assist.

Figure 2-10: Visual Explain.

Figure 2-11: The Access Plan icon on the Command Editor toolbar

Figure 2-12: The Task Center.

Figure 2-13: The Health Center.

Figure 2-14: The Messages view of the Journal.

Figure 2-15: The License Center.

Figure 2-16: The Configuration Assistant.

Figure 2-17: The second page of the Design Advisor wizard.

Figure 2-18: The Command Line Processor (in interactive input mode).

Figure 2-19: The Developer Workbench.

Chapter 3: Security

Figure 3-1: Hierarchy of the authorities and privileges available with DB2 9.

Figure 3-2: Database privileges available with DB2 9.

Figure 3-3: Table space privilege available with DB2 9.

Figure 3-4: Schema privileges available with DB2 9.

Figure 3-5: Table privileges available with DB2 9.

Figure 3-6: View privileges available with DB2 9.

Figure 3-7: Index privilege available with DB2 9.

Figure 3-8: Sequence privileges available with DB2 9.

Figure 3-9: Routine privilege available with DB2 9.

Figure 3-10: Package privileges available with DB2 9.

Figure 3-11: Server privilege available with DB2 9.

Figure 3-12: Nickname privileges available with DB2 9.

Figure 3-15: Invoking the Table Privileges dialog from the Control Center.

Figure 3-16: The Table Privileges dialog.

Chapter 4: Working with Databases and Database Objects

Figure 4-1: Hierarchical relationship between systems, instances, and databases.

Figure 4-2: Typical directory hierarchy tree for a nonpartitioned database.

Figure 4-3: Invoking the Create Database Wizard from the Control Center.

Figure 4-4: The first page of the Create Database Wizard.

Figure 4-5: Invoking the Add Database dialog from the Control Center.

Figure 4-6: The Add Database dialog.

Figure 4-7: Assigning a table object to a schema.

Figure 4-8: Invoking the Create Schema dialog from the Control Center.

Figure 4-9: The Create Schema dialog.

Figure 4-10: A simple database table.

Figure 4-11: Invoking the Create Table Wizard from the Control Center.

Figure 4-12: The first page of the Create Table Wizard.

Figure 4-13: A simple view that references two base tables.

Figure 4-14: Invoking the Create View dialog from the Control Center.

Figure 4-15: The Create View dialog.

Figure 4-16: How the WITH LOCAL CHECK OPTION clause is used to ensure that insert and update operations conform to a view's definition.

Figure 4-17: How the WITH CASCADED CHECK OPTION clause is used to ensure insert and update operations conform to a parent view's definition.

Figure 4-18: A simple index.

Figure 4-19: Invoking the Create Index dialog from the Control Center.

Figure 4-20: The Create Index wizard.

Figure 4-21: Invoking the Create Alias dialog from the Control Center.

Figure 4-22: The Create Alias dialog.

Figure 4-23: Invoking the Create Trigger dialog from the Control Center.

Figure 4-24: The Create Trigger dialog.

Figure 4-25: Invoking the Create Distinct Type dialog from the Control Center.

Figure 4-26: The Create Distinct Type dialog.

Figure 4-27: Dropping a table from the Control Center.

Chapter 5: Working with DB2 Data Using SQL and XQuery

Figure 5-1: A simple inner join operation.

Figure 5-2: A simple left outer join operation.

Figure 5-3: A simple right outer join operation.

Figure 5-4: A simple full outer join operation.

Figure 5-5: A simple UNION set operation.

Figure 5-6: A simple UNION ALL set operation.

Figure 5-7: A simple INTERSECT set operation.

Figure 5-8: A simple INTERSECT ALL set operation.

Figure 5-9: A simple EXCEPT set operation.

Figure 5-10: A simple EXCEPT set operation with the queries reversed.

Figure 5-11: A simple EXCEPT ALL set operation.

Figure 5-12: Terminating a transaction with the COMMIT SQL statement.

Figure 5-13: Terminating a transaction with the ROLLBACK SQL statement.

Figure 5-14: Evaluating the effects of a series of transactions.

Chapter 6: Working with DB2 Tables, Views, and Indexes

Figure 6-1: How the NOT NULL constraint prevents null values.

Figure 6-2: How the default constraint is used to provide data values.

Figure 6-3: How a CHECK constraint is used to control what values are accepted by a column.

Figure 6-4: How the UNIQUE constraint prevents the duplication of data values.

Figure 6-5: How a referential constraint is used to define a relationship between two tables.

Figure 6-6: An insert operation that conforms to the Insert Rule of a referential constraint.

Figure 6-7: An insert operation that violates the Insert Rule of a referential constraint.

Figure 6-8: How the ON UPDATE RESTRICT Update Rule of a referential constraint is enforced.

Figure 6-9: How the ON UPDATE NO ACTION Update Rule of a referential constraint is enforced.

Figure 6-10: How the ON DELETE CASCADE Delete Rule of a referential constraint is enforced.

Figure 6-11: How the ON DELETE SET NULL Delete Rule of a referential constraint is enforced.

Figure 6-12: How the ON DELETE RESTRICT Delete Rule of a referential constraint is enforced.

Figure 6-13: How the ON DELETE NO ACTION Delete Rule of a referential constraint is enforced.

Figure 6-14: Behavior of a simple informational constraint.

Figure 6-15: Assigning a table object to a schema.

Chapter 7: Database Concurrency

Figure 7-1: Example of how the Repeatable Read isolation level can affect application behavior.

Figure 7-2: Example of how the Read Stability isolation level can affect application behavior.

Figure 7-3: Example of how the Cursor Stability isolation level can affect application behavior.

Figure 7-4: Example of how the Uncommitted Read isolation level can affect application behavior.

Figure 7-5: How DB2 9 prevents uncontrolled concurrent access to a resource through the use of locks.

Figure 7-6: A simple lock conversion scenario-in this example, a Share (S) lock is converted to an Exclusive (X) lock.

Figure 7-7: Lock escalation-several individual row-level locks are changed to a single table-level lock.

Figure 7-8: A deadlock scenario-Transaction 1 is waiting for Transaction 2 to release its lock on Table B, and Transaction 2 is waiting for Transaction 1 to release its lock on Table A; however, neither transaction can release their respective locks because they have been suspended and are waiting to acquire other locks.