DB2 Certification Guide for Common Servers

1. Products and Components.


DB2 Family. DB2 Components. DB2 Command Line Processor (CLP). DB2 Administrator's Toolkit. DB2 Products. DB2 Single-User. DB2 Server. Distributed Database Connection Services (DDCS). DB2 Software Developer's Kit (SDK). Documentation. Licensing. DB2 Client/Server Environment. Supported Communication Protocols. DB2 Resource Usage. DB2 Client/Server Scenarios. Summary. Questions. Answers.



2. Getting Started and Connected.


Product Installation. AIX Considerations. OS/2 Considerations. Windows NT Considerations. Windows 95 Considerations. DB2 Client Installation. Installing the DB2 SDK. Installing the CAE. Installing the ODBC Driver. Setting Up the DB2 Environment. Creating an Instance. Starting the DB2 Instance. Stopping the DB2 Instance. Managing Instances. DB2 Interfaces. Using the Command Line Processor. Using the Database Director. Creating the Sample Database. Summary. Questions. Answers. Exercises.



3. Controlling Data Access.


Security. Overview of Security. Authorization Levels. Security Considerations. DB2 Privileges (Data Control Language). Explicit and Implicit Authorizations. Establishing a DB2 Client/Server Connection. Configuring a DB2 Server. DB2 Directories. Configuring DB2 Clients. Cataloging a Database from a Client. Verifying the Connection. Binding Utilities. Instance Administration (ATTACH Command). Local Instance Administration. User Attach Scenario. Summary. Questions. Answers. Exercises.



4. Using SQL.


Understanding Database Objects. Data Types. DB2-Supplied Data Types. User-Defined Data Types. NULL Values. Code Page Considerations. Selecting the Correct Data Type. DB2 Certification Database Design and Implementation. Problem Domain. Tables. Table Spaces. Schemas. Indexes. Packages. Log Files. Transactions. Locks. SQL Data Definition Language (DDL). Creating a DB2 Database. Table Space Types. Views. Indexes. Data Manipulation Language (DML). Data Retrieval (SELECT). Changing the Order of the Columns. Restricting Rows from a Table. Selecting Columns from Multiple Tables. DB2 Functions. Data Modification (INSERT/UPDATE/DELETE). Removing Data. View Classification. Advanced SQL. User-Defined Defaults. Check Constraints. Triggers. Summary. Questions. Answers. Exercises.



5. Database Concurrency.


Concurrency Considerations. Concurrency Problems. Lost Update Problem. Lost Update Solutions. Uncommitted Read. Non-Repeatable Read. Phantom Read Problem. Phantom Read Solution. Isolation Level. Uncommitted Read. Cursor Stability. Read Stability. Repeatable Read. Choosing an Isolation Level. Locking. Lock Attributes. Lock Conversion. Lock Escalation. Lock Wait Behavior. Deadlock Behavior. Lock Table Statement. Summary. Questions. Answers. Exercises.



6. Data Placement.


Containers. Table Spaces. Table Spaces and Containers. Extents. Table Space Types. System Managed Storage (SMS) Table Spaces. Characteristics of the Default Table Spaces. When to Use SMS Table Spaces. Database Managed Storage (DMS) Table Spaces. When to Use DMS Table Spaces. Planning a Table Space Environment. Creating Table Spaces. Performance Considerations. System Catalogs in DB2. Managing Table Spaces. States of Table Spaces. Summary. Questions. Answers. Exercises.



7. Data Management.


Populating Tables. The LOAD Utility. Using The LOAD Utility. Syntax of the LOAD Command. The SET CONSTRAINTS Statement. The IMPORT/EXPORT Utility. The EXPORT Utility. EXPORT Considerations. The IMPORT Utility. IMPORT Considerations. Data Maintenance. Analyzing Data's Physical Organization: REORGCHK. Table Reorganization: REORG. RUNSTATS. Using the RUNSTATS Utility. Identifying Updated Statistics. The REBIND Utility. Data Maintenance Process. Modeling a Production Environment. User Maintenance. Fenced and Not-Fenced Procedures and Functions. The LIST APPLICATIONS Command. The FORCE Command. Database Recovery. Unit of Work. Transaction. Use of Log Files. Types of Logging. Log File Usage. Backup and Restore. Performing a Table Space Level Backup or Restore. Summary of Backup/Restore Consideration. The Recovery History File. Graphical Management Tools. The Recovery Jobs Tool. The Database Director. Creating/Modifying DB2 Objects. Summary. Questions. Answers. Exercises.



8. Database Monitoring and Tuning.


Obtaining Database Access Information. Explain Facility. Query Compilation. Gathering Explain Data. Using the Explain Report Tools. Examining EXPLAIN Data. Database Monitoring. Event monitoring. Visual Performance Monitors. Performance Issues. Configuring Database Resources. DB2 Sorting Methods. DB2 Server Resources. Problem Diagnosis. Performance Tuning Scenario. Summary. Questions. Answers. Exercises.

Part A. Snapshot Monitoring.

Part B. Event Monitoring.

Part C. Sorting and Visual Explain.

9. DB2 Application Programming.


DB2 Application Development Environment. Client/Server Platform. Connection Types. Choosing a DB2 Programming Method. Static Embedded SQL. Dynamic Embedded SQL. Call Level Interface (CLI). Native DB2 Application Programming Interfaces (APIs). Accessing Data from an Application. Using Schemas. Using an Alias. Creating Packages. Binding Applications. Binding Utilities. Parts of a DB2 Embedded Static SQL Application. Host Variables. Indicator Variables. Using Cursors. Handling Application Termination. Interrupt Handling. Sample Program — REORGIT. Summary. Questions. Answers. Exercises.

10. DB2 Advanced Application Programming.


Writing Dynamic Programs. First Look at Dynamic SQL. Dynamic SQL Phases. Types of Dynamic SQL Statements. Dynamic SQL — Call Level Interface. Callable SQL Interfaces. ODBC vs. CLI. Embedded Dynamic vs. Call Level Interface. Setting Up a CLI Development Environment. Stored Procedures. User Defined Functions. COMPOUND SQL. Summary. Questions. Answers. Exercises.

Part A. Dynamic Embedded SQL.

Appendix A. Test Objectives — DB2 Fundamentals (500).
Appendix B. Test Objectives — DB2 Database Administration (501).
Appendix C. Test Objectives — DB2 Application Development (502).
Appendix D. DB2 Tools and Utilities.
Appendix E. DB2CERT Application Source Code.
Index.