Book description
The Easy, Visual Way to Master IBM® DB2 for Linux®, UNIX®, and Windows®—Fully Updated for Version 9.5
IBM DB2 9 and DB2 9.5 provide breakthrough capabilities for providing Information on Demand, implementing Web services and Service Oriented Architecture, and streamlining information management. Understanding DB2: Learning Visually with Examples, Second Edition, is the easiest way to master the latest versions of DB2 and apply their full power to your business challenges.
Written by four IBM DB2 experts, this book introduces key concepts with dozens of examples drawn from the authors' experience working with DB2 in enterprise environments. Thoroughly updated for DB2 9.5, it covers new innovations ranging from manageability to performance and XML support to API integration. Each concept is presented with easy-to-understand screenshots, diagrams, charts, and tables.
This book is for everyone who works with DB2: database administrators, system administrators, developers, and consultants. With hundreds of well-designed review questions and answers, it will also help professionals prepare for the IBM DB2 Certification Exams 730, 731, or 736.
Coverage includes
Choosing the right version of DB2 for your needs
Installing and configuring DB2
Understanding the DB2 environment, instances, and databases
Establishing client and server connectivity
Working with database objects
Utilizing breakthrough pureXML™ technology, which provides for nativeXML support
Mastering administration, maintenance, performance optimization, troubleshooting, and recovery
Understanding improvements in the DB2 process, memory, and storage models
Implementing effective database security
Leveraging the power of SQL and XQuery
Table of contents
- Copyright
- IBM Press
- Foreword
- Preface
- Acknowledgments
- About the Authors
-
1. Introduction to DB2
- 1.1. Brief History of DB2
- 1.2. The Role of DB2 in the Information On Demand World
- 1.3. DB2 Editions
- 1.4. DB2 Clients
- 1.5. Try-and-Buy Versions
- 1.6. Host Connectivity
- 1.7. Federation Support
- 1.8. Replication Support
- 1.9. IBM WebSphere Federation Server and WebSphere Replication Server
- 1.10. Special Package Offerings for Developers
- 1.11. DB2 Syntax Diagram Conventions
- 1.12. Case Study
- 1.13. Summary
- 1.14. Review Questions
-
2. DB2 at a Glance: The Big Picture
- 2.1. SQL Statements, XQuery Statements, and DB2 Commands
- 2.2. DB2 Tools Overview
-
2.3. The DB2 Environment
- 2.3.1. An Instance(1)
- 2.3.2. The Database Administration Server
- 2.3.3. Configuration Files and the DB2 Profile Registries(2)
- 2.3.4. Connectivity and DB2 Directories(3)
- 2.3.5. Databases(4)
- 2.3.6. Table Spaces(5)
- 2.3.7. Tables, Indexes, and Large Objects(6)
- 2.3.8. Logs(7)
- 2.3.9. Buffer Pools(8)
- 2.3.10. The Internal Implementation of the DB2 Environment
- 2.4. Federation
- 2.5. Case Study: The DB2 Environment
-
2.6. Database Partitioning Feature
- 2.6.1. Database Partitions
- 2.6.2. The Node Configuration File
- 2.6.3. An Instance in the DPF Environment
- 2.6.4. Partitioning a Database
- 2.6.5. Configuration Files in a DPF Environment
- 2.6.6. Logs in a DPF Environment
- 2.6.7. The Catalog Partition
- 2.6.8. Partition Groups
- 2.6.9. Buffer Pools in a DPF Environment
- 2.6.10. Table Spaces in a Partitioned Database Environment
- 2.6.11. The Coordinator Partition
- 2.6.12. Issuing Commands and SQL Statements in a DPF Environment
- 2.6.13. The DB2NODE Environment Variable
- 2.6.14. Distribution Maps and Distribution Keys
- 2.7. Case Study: DB2 with DPF Environment
- 2.8. IBM Balanced Warehouse
- 2.9. Summary
- 2.10. Review Questions
-
3. Installing DB2
- 3.1. DB2 Installation: The Big Picture
-
3.2. Installing DB2 Using the DB2 Setup Wizard
- 3.2.1. Step 1 for Windows: Launch the DB2 Setup Wizard
- 3.2.2. Step 1 for Linux and UNIX: Launch the DB2 Setup Wizard
- 3.2.3. Step 2: Choose an Installation Type
- 3.2.4. Step 3: Choose Whether to Generate a Response File
- 3.2.5. Step 4: Specify the Installation Folder
- 3.2.6. Step 5: Set User Information for the DB2 Administration Server
- 3.2.7. Step 6: Create and Configure the DB2 Instance
- 3.2.8. Step 7: Create the DB2 Tools Catalog
- 3.2.9. Step 8: Enable the Alert Notification Feature
- 3.2.10. Step 9: Specify a Contact for Health Monitor Notification
- 3.2.11. Step 10: Enable Operating System Security for DB2 Objects (Windows Only)
- 3.2.12. Step 11: Start the Installation
- 3.3. Non-Root Installation on Linux and Unix
- 3.4. Required User IDs and Groups
- 3.5. Silent Install Using a Response File
- 3.6. Advanced DB2 Installation Methods (Linux and UNIX Only)
- 3.7. Installing a DB2 License
- 3.8. Installing DB2 in a DPF Environment
- 3.9. Installing Multiple db2 Versions and Fix Packs on the Same Server
- 3.10. Installing DB2 Fix Packs
- 3.11. Migrating DB2
- 3.12. Case Study
- 3.13. Summary
- 3.14. Review Questions
-
4. Using the DB2 Tools
- 4.1. DB2 Tools: The Big Picture
- 4.2. The Command-Line Tools
- 4.3. Web-Based Tools
- 4.4. General Administration Tools
- 4.5. Information Tools
- 4.6. Monitoring Tools
- 4.7. Setup Tools
- 4.8. Other Tools
- 4.9. Tool Settings
- 4.10. Case Study
- 4.11. Summary
- 4.12. Review Questions
-
5. Understanding the DB2 Environment, DB2 Instances, and Databases
- 5.1. The DB2 Environment, DB2 Instances, and Databases: The Big Picture
- 5.2. The DB2 Environment
-
5.3. The DB2 Instance
- 5.3.1. Creating DB2 Instances
- 5.3.2. Creating Client Instances
- 5.3.3. Creating DB2 Instances in a Multipartitioned Environment
- 5.3.4. Dropping an Instance
- 5.3.5. Listing the Instances in Your System
- 5.3.6. The DB2INSTANCE Environment Variable
- 5.3.7. Starting a DB2 Instance
- 5.3.8. Stopping a DB2 Instance
- 5.3.9. Attaching to an Instance
- 5.3.10. Configuring an Instance
- 5.3.11. Working with an Instance from the Control Center
- 5.3.12. The DB2 Commands at the Instance Level
- 5.4. The Database Administration Server
- 5.5. Configuring a Database
- 5.6. Instance and Database Design Considerations
- 5.7. Case Study
- 5.8. Summary
- 5.9. Review Questions
-
6. Configuring Client and Server Connectivity
- 6.1. Client and Server Connectivity: The Big Picture
- 6.2. The DB2 Database Directories
-
6.3. Supported Connectivity Scenarios
- 6.3.1. Scenario 1: Local Connection from a DB2 Client to a DB2 Server
- 6.3.2. Scenario 2: Remote Connection from a DB2 Client to a DB2 Server
- 6.3.3. Scenario 3: Remote Connection from a DB2 Client to a DB2 Host Server
- 6.3.4. Scenario 4: Remote Connection from a DB2 Client to a DB2 Host Server via a DB2 Connect Gateway
- 6.3.5. Binding Utilities
-
6.4. Configuring Database Connections Using the Configuration Assistant
- 6.4.1. Configuring a Connection Using DB2 Discovery in the Configuration Assistant
- 6.4.2. Configuring a Connection Using Access Profiles in the Configuration Assistant
- 6.4.3. Configuring a Connection Manually Using the Configuration Assistant
- 6.4.4. Automatic Client Reroute Feature
- 6.4.5. Application Connection Timeout Support
- 6.5. Diagnosing DB2 Connectivity Problems
- 6.6. Case Study
- 6.7. Summary
- 6.8. Review Questions
-
7. Working with Database Objects
- 7.1. DB2 Database Objects: The Big Picture
- 7.2. Databases
- 7.3. Partition Groups
- 7.4. Table Spaces
- 7.5. Buffer Pools
- 7.6. Schemas
- 7.7. Data Types
-
7.8. Tables
- 7.8.1. Table Classification
- 7.8.2. System Catalog Tables
- 7.8.3. User Tables
- 7.8.4. Default Values
- 7.8.5. Using NULL Values
- 7.8.6. Identity Columns
- 7.8.7. Constraints
- 7.8.8. Not Logged Initially Tables
- 7.8.9. Partitioned Tables
- 7.8.10. Row Compression
- 7.8.11. Table Compression
- 7.8.12. Materialized Query Tables and Summary Tables
- 7.8.13. Temporary Tables
- 7.9. Indexes
- 7.10. Multidimensional Clustering Tables and Block Indexes
- 7.11. Combining DPF, Table Partitioning, and MDC
- 7.12. Views
- 7.13. Packages
- 7.14. Triggers
- 7.15. Stored Procedures
- 7.16. User-Defined Functions
- 7.17. Sequences
- 7.18. Case Study
- 7.19. Summary
- 7.20. Review Questions
-
8. The DB2 Storage Model
- 8.1. The DB2 Storage Model: The Big Picture
- 8.2. Databases: Logical and Physical Storage of Your Data
- 8.3. Database Partition Groups
- 8.4. Table Spaces
- 8.5. Buffer Pools
- 8.6. Case Study
- 8.7. Summary
- 8.8. Review Questions
-
9. Leveraging the Power of SQL
-
9.1. Querying DB2 Data
- 9.1.1. Derived Columns
- 9.1.2. The SELECT Statement with COUNT Aggregate Function
- 9.1.3. The SELECT Statement with DISTINCT Clause
- 9.1.4. DB2 Special Registers
- 9.1.5. Scalar and Column Functions
- 9.1.6. The CAST Expression
- 9.1.7. The WHERE clause
- 9.1.8. Using FETCH FIRST n ROWS ONLY
- 9.1.9. The LIKE Predicate
- 9.1.10. The BETWEEN Predicate
- 9.1.11. The IN Predicate
- 9.1.12. The ORDER BY Clause
- 9.1.13. The GROUP BY...HAVING Clause
- 9.1.14. Joins
- 9.1.15. Working with NULLs
- 9.1.16. The CASE Expression
- 9.1.17. Adding a Row Number to the Result Set
- 9.2. Modifying Table Data
- 9.3. Selecting from UPDATE, DELETE, or INSERT
- 9.4. The MERGE Statement
- 9.5. Recursive SQL
- 9.6. The UNION, INTERSECT, and EXCEPT Operators
- 9.7. Case Study
- 9.8. Summary
- 9.9. Review Questions
-
9.1. Querying DB2 Data
-
10. Mastering the DB2 pureXML Support
- 10.1. XML: The Big Picture
- 10.2. pureXML in DB2
- 10.3. Querying XML Data
- 10.4. SQL/XML Publishing Functions
- 10.5. Transforming XML Documents Using XSLT Functions
- 10.6. Inserting XML Data into a DB2 Database
- 10.7. Updating and Deleting XML Data
- 10.8. XML Indexes
- 10.9. XML Schema Support and Validation in DB2
- 10.10. Annotated XML Schema Decomposition
- 10.11. XML Performance Considerations
- 10.12. pureXML Restrictions
- 10.13. Case Study
- 10.14. Summary
- 10.15. Review Questions
-
11. Implementing Security
- 11.1. DB2 Security Model: The Big Picture
-
11.2. Authentication
- 11.2.1. Configuring the Authentication Type at a DB2 Server
- 11.2.2. Configuring the Authentication Type at a DB2 Client
- 11.2.3. Authenticating Users at/on the DB2 Server
- 11.2.4. Authenticating Users Using the Kerberos Security Service
- 11.2.5. Authenticating Users with Generic Security Service Plug-ins
- 11.2.6. Authenticating Users at/on the DB2 Client(s)
- 11.3. Data Encryption
- 11.4. Administrative Authorities
-
11.5. Database Object Privileges
- 11.5.1. Schema Privileges
- 11.5.2. Table Space Privileges
- 11.5.3. Table and View Privileges
- 11.5.4. Index Privileges
- 11.5.5. Package Privileges
- 11.5.6. Routine Privileges
- 11.5.7. Sequence Privileges
- 11.5.8. XSR Object Privileges
- 11.5.9. Security Label Privileges
- 11.5.10. LBAC Rule Exemption Privileges
- 11.5.11. SET SESSION AUTHORIZATION Statement and SETSESSIONUSER Privilege
- 11.5.12. Implicit Privileges
- 11.5.13. Roles and Privileges
- 11.5.14. TRANSFER OWNERSHIP Statement
- 11.6. Label-Based Access Control (LBAC)
- 11.7. Authority and Privilege Metadata
- 11.8. Windows Domain Considerations
- 11.9. Trusted Contexts Security Enhancement
- 11.10. Case Study
- 11.11. Summary
- 11.12. Review Questions
- 12. Understanding Concurrency and Locking
-
13. Maintaining Data
- 13.1. DB2 Data Movement Utilities: The Big Picture
- 13.2. Data Movement File Formats
-
13.3. The DB2 EXPORT Utility
- 13.3.1. File Type Modifiers Supported in the Export Utility
- 13.3.2. Exporting Large Objects
- 13.3.3. Exporting XML Data
- 13.3.4. Specifying Column Names
- 13.3.5. Authorities Required to Perform an Export
- 13.3.6. Exporting a Table Using the Control Center
- 13.3.7. Run an export Command Using the ADMIN_CMD Procedure
-
13.4. The DB2 IMPORT Utility
- 13.4.1. Import Mode
- 13.4.2. Allow Concurrent Write Access
- 13.4.3. Regular Commits during an Import
- 13.4.4. Restarting a Failed Import
- 13.4.5. File Type Modifiers Supported in the Import Utility
- 13.4.6. Importing Large Objects
- 13.4.7. Importing XML Data
- 13.4.8. Select Columns to Import
- 13.4.9. Authorities Required to Perform an Import
- 13.4.10. Importing a Table Using the Control Center
- 13.4.11. Run an import Command with the ADMIN_CMD Procedure
-
13.5. The DB2 Load Utility
- 13.5.1. The Load Process
- 13.5.2. The LOAD Command
- 13.5.3. File Type Modifiers Supported in the load Utility
- 13.5.4. Loading Large Objects
- 13.5.5. Loading XML Data
- 13.5.6. Collecting Statistics
- 13.5.7. The COPY YES/NO and NONRECOVERABLE Options
- 13.5.8. Validating Data against Constraints
- 13.5.9. Performance Considerations
- 13.5.10. Authorities Required to Perform a Load
- 13.5.11. Loading a Table Using the Control Center
- 13.5.12. Run a load Command with the ADMIN_CMD Procedure
- 13.5.13. Monitoring a Load Operation
- 13.6. The DB2MOVE Utility
- 13.7. The db2relocatedb Utility
- 13.8. Generating Data Definition Language
- 13.9. DB2 Maintenance Utilities
- 13.10. Case Study
- 13.11. Summary
- 13.12. Review Questions
-
14. Developing Database Backup and Recovery Solutions
- 14.1. Database Recovery Concepts: The Big Picture
- 14.2. DB2 Transaction Logs
- 14.3. Recovery Terminology
- 14.4. Performing Database and Table Space Backups
- 14.5. Database and Table Space Recovery Using the RESTORE DATABASE Command
- 14.6. Database and Table Space Roll Forward
- 14.7. Recovering a Dropped Table
- 14.8. The Recovery History File
- 14.9. Database Recovery Using the RECOVER DATABASE Command
-
14.10. Rebuild Database Option
- 14.10.1. Rebuilding a Recoverable Database Using Table Space Backups
- 14.10.2. Rebuilding a Recoverable Database Using Only a Subset of the Table Space Backups
- 14.10.3. Rebuilding a Recoverable Database Using Online Backup Images That Contain Log Files
- 14.10.4. Rebuilding a Recoverable Database Using Incremental Backup Images
- 14.10.5. Rebuilding a Recoverable Database Using the Redirect Option
- 14.10.6. Rebuilding a Nonrecoverable Database
- 14.10.7. Database Rebuild Restrictions
-
14.11. Backup Recovery through Online Split Mirroring and Suspended I/O Support
- 14.11.1. Split Mirroring Key Concepts
- 14.11.2. The db2inidb Tool
- 14.11.3. Cloning a Database Using the db2inidb Snapshot Option
- 14.11.4. Creating a Standby Database Using the db2inidb Standby Option
- 14.11.5. Creating a Backup Image of the Primary Database Using the db2inidb Mirror Option
- 14.11.6. Split Mirroring in Partitioned Environments
- 14.11.7. Integrated Flash Copy
-
14.12. Maintaining High Availability with DB2
- 14.12.1. Log Shipping
-
14.12.2. Overview of DB2 High Availability Disaster Recovery (HADR)
- 14.12.2.1. HADR Setup
- 14.12.2.2. HADR Database States
- 14.12.2.3. HADR Synchronization Modes
- 14.12.2.4. HADR Commands Overview
- 14.12.2.5. Setting Up and Initializing HADR
- 14.12.2.6. Performing a Takeover—Switching Database Roles
- 14.12.2.7. Performing a Takeover—Failover
- 14.12.2.8. Summary of the Takeover Behavior with Respect to Database States
- 14.12.2.9. The Automatic Client Reroute Feature and HADR
- 14.12.2.10. Stopping HADR
- 14.12.2.11. The HADR Wizard
- 14.13. The Fault Monitor
- 14.14. Case Study
- 14.15. Summary
- Review Questions
-
15. The DB2 Process Model
- 15.1. The DB2 Process Model: The Big Picture
- 15.2. Threaded Engine Infrastructure
- 15.3. The DB2 Engine Dispatchable Units
- 15.4. Tuning the Number of EDUs
- 15.5. Monitoring and Tuning the DB2 Agents
- 15.6. The Connection Concentrator
- 15.7. Commonly Seen DB2 Executables
- 15.8. Additional Services/Processes on Windows
- 15.9. Case Study
- 15.10. Summary
- 15.11. Review Questions
- 16. The DB2 Memory Model
-
17. Database Performance Considerations
- 17.1. Relation Data Performance Fundamentals
- 17.2. System/Server Configuration
- 17.3. The DB2 Configuration Advisor
- 17.4. Configuring the DB2 Instance
- 17.5. Configuring Your Databases
- 17.6. Lack of Proper Maintenance
- 17.7. Automatic Maintenance
- 17.8. The Snapshot Monitor
- 17.9. Event Monitors
- 17.10. The DB2 Optimizer
- 17.11. The Explain Tool and Explain Tables
- 17.12. Using Visual Explain to Examine Access Plans
- 17.13. Workload Management
- 17.14. Case Study
- 17.15. Summary
- 17.16. Review Questions
-
18. Diagnosing Problems
- 18.1. Problem Diagnosis: The Big Picture
- 18.2. How DB2 Reports Issues
- 18.3. DB2 Error Message Description
- 18.4. DB2 First Failure Data Capture
- 18.5. Receiving E-mail Notifications
- 18.6. Tools for Troubleshooting
- 18.7. Searching for Known Problems
- 18.8. Case Study
- 18.9. Summary
- 18.10. Review Questions
- A. Solutions to the Review Questions
- B. Use of Uppercase versus Lowercase in DB2
- C. IBM Servers
- D. Using the DB2 System Catalog Tables
- Resources
Product information
- Title: Understanding DB2®: Learning Visually with Examples, Second Edition
- Author(s):
- Release date: December 2007
- Publisher(s): IBM Press
- ISBN: 9780768681772
You might also like
book
Understanding DB2®: Learning Visually with Examples
The Easy, Visual Guide to IBM DB2 V8.2 IBM DB2 Universal Database™ V8.2 for Linux®, UNIX®, …
book
Programming Languages: Concepts and Implementation
Programming Languages: Concepts and Implementation teaches language concepts from two complementary perspectives: implementation and paradigms. It …
book
Programming Python, 4th Edition
If you've mastered Python's fundamentals, you're ready to start using it to get real work done. …
book
Effective Java™: Programming Language Guide
A new edition of this title is available, ISBN-10: 0321356683 ISBN-13: 9780321356680