Bibliographic record and links to related information available from the Library of Congress catalog.
Note: Contents data are machine generated based on pre-publication provided by the publisher. Contents may have variations from the printed book or be incomplete or contain other coding.
Table of Contents Foreword Preface Who Should Read This Book? About the CD-ROM Getting Started A Word of Advice Conventions Contacting the Authors Acknowledgments About the CD-ROM Chapter 1 Introduction to DB2 UDB 1.1 A Brief History of DB2 1.2 DB2 software and the IBM E-Business On-Demand Model 1.3 DB2 UDB Editions 1.3.1 Everyplace Edition 1.3.2 Personal Edition 1.3.3 Workgroup Server Edition 1.3.4 Express Edition 1.3.5 Enterprise Server Edition 1.4 DB2 UDB Clients 1.5 "Try and Buy" Versions 1.6 Host Connectivity 1.7 Federated Support 1.8 Replication Support 1.9 IBM DB2 Information Integrator 1.10 Special Package Offerings for Developers 1.11 DB2 Syntax Diagram Conventions 1.12 Case Study 1.12 Summary 1.13 Review Questions Chapter 2 DB2 at a Glance: The Big Picture 2.1. SQL Statements and DB2 Commands 2.1.1 SQL Statements 2.1.2 DB2 System Commands 2.1.3 DB2 Command Line Processor (CLP) Commands 2.2 DB2 Tools Overview 2.2.1 Command Line Tools 2.2.2 Development Tools 2.2.3 General Administration Tools 2.2.4 Information Tools 2.2.5 Monitoring Tools 2.2.6 Setup Tools 2.2.7 Other Tools 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 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 Partitioning Maps and Partitioning Keys 2.7 Case Study: DB2 with DPF Environment 2.8 Summary 2.9 Review Questions Chapter 3 Installing DB2 3.1 DB2 Installation: The Big Picture 3.2 Required User IDs and Groups 3.2.1 User IDs and Groups Required for Windows 3.2.2 IDs and Groups Required for Linux/UNIX 3.2.3 Creating User IDs and Groups if NIS Is Installed in Your Environment (Linux/UNIX Only) 3.3 Installing DB2 Using the DB2 Setup Wizard 3.3.1 Step 1 for Windows: Launch the DB2 Setup Wizard 3.3.2 Step 1 for Linux/UNIX: Launch the DB2 Setup Wizard 3.3.3 Step 2: Choose an Installation Type 3.3.4 Step 3: Choose Whether to Generate a Response File 3.3.5 Step 4: Indicate Whether This Machine Will Be Part of a Partitioned Database Environment 3.3.6 Step 5: Specify the Installation Folder 3.3.7 Step 6: Set User Information for the DB2 Administration Server 3.3.8 Step 7: Enable the Alert Notification Feature 3.3.9 Step 8: Create and Configure the DB2 Instance 3.3.10 Step 9: Create the DB2 Tools Catalog 3.3.11 Step 10: Specify a Contact for Health Monitor Notification 3.3.12 Step 11: Enable Operating System Security for DB2 objects (Windows Only) 3.3.13 Step 12: Start the Installation 3.4 Silent Install Using a Response File 3.4.1 Creating a Response File 3.4.2 Installing DB2 Using a Response File on Windows 3.4.3 Installing DB2 Using a Response File on Linux/UNIX 3.5 Installing DB2 Manually (Linux/UNIX Only) 3.5.1 Installing DB2 Using the Native Operating System Install Tool 3.5.2 Installing DB2 Using the db2_install Script 3.6 Installing a DB2 License 3.6.1 Installing a DB2 Product License Using the License Center 3.6.2 Installing the DB2 Product License Using the db2licm Command 3.7 Installing DB2 in a DPF Environment 3.8 Installing DB2 FixPaks 3.8.1 Applying a Regular DB2 FixPak (All Supported Platforms and Products) 3.8.2 Applying an Alternate FixPak (Available on DB2 ESE and on UNIX Platforms Only) 3.9 Case Study 3.10 Summary 3.11 Review Questions Chapter 4 Using the DB2 Tools 4.1 DB2 Tools: The Big Picture 4.2 The Command Line Tools 4.2.1 The Command Line Processor and the Command Window 4.2.2 The Command Editor 4.3 Development Tools 4.3.1 The Development Center 4.3.2 The Project Deployment Tool 4.4 General Administration Tools 4.4.1 The Control Center 4.4.2 The Journal 4.4.3 The Replication Center 4.4.4 The Task Center 4.5 Information Tools 4.5.1 Information Center 4.5.2 Checking for DB2 Updates 4.6 Monitoring Tools 4.6.1 The Activity Monitor 4.6.2 Event Analyzer 4.6.3 Health Center 4.6.4 Indoubt Transaction Manager 4.6.5 The Memory Visualizer 4.6.6 The db2pd Tool 4.7 Setup Tools 4.7.1 First Steps 4.7.2 The Configuration Assistant 4.7.3 Register Visual Studio Add-Ins 4.8 Other Tools 4.8.1 License Center 4.8.2 SQL Assist 4.8.3 Satellite Administration Center 4.9 Tool Settings 4.10 Case Study 4.11 Summary 4.12 Review Questions Chapter 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.2.1 Environment Variables 5.2.2 DB2 Profile Registries 5.3 The DB2 Instance 5.3.1 Creating DB2 Instances 5.3.2 Creating DB2 64-bit Instances 5.3.3 Creating Client Instances 5.3.4 Creating DB2 Instances in a Multi-Partitioned Environment 5.3.5 Dropping an Instance 5.3.6 Listing the Instances in Your System 5.3.7 The DB2INSTANCE Environment Variable 5.3.8 Starting a DB2 Instance 5.3.9 Stopping a DB2 Instance 5.3.10 Attaching to an Instance 5.3.11 Configuring an Instance 5.3.12 Working with an Instance from the Control Center 5.3.13 The DB2 Commands at the Instance Level 5.4 The Database Administration Server 5.4.1 The DAS Commands 5.5 Configuring a Database 5.5.1 Configuring a Database from the Control Center 5.5.2 The DB2 Commands at the Database Level 5.6 Instance and Database Design Considerations 5.7 Case Study 5.8 Summary 5.9 Review Questions Chapter 6 Configuring Client and Server Connectivity 6.1 Client and Server Connectivity: The Big Picture 6.2 The DB2 Directories 6.2.1 The DB2 Directories: An Analogy Using a Book 6.2.2 The System Database Directory 6.2.3 The Local Database Directory 6.2.4 The Node Directory 6.2.5 The Database Connection Services Directory 6.2.6 The Relationship Between the DB2 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.2.1 Enabling the Database Server to Accept Client Connections 6.3.2.2 Cataloging the Node Directory and Database Directory on the Client 6.3.3 Scenario 3: Remote Connection from a DB2 Client to a DB2 Host Server 6.3.3.1 Enabling the Database Server to Accept Client Connections 6.3.3.2 Cataloging the Node Directory, Database Directory, and DCS Directory on the Client 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.5 Case Study 6.6 Summary 6.7 Review Questions Chapter 7 Working with Database Objects 7.1 DB2 Database Objects: The Big Picture 7.2 Databases 7.2.1 Database Partitions 7.2.2 The Database Node Configuration File (db2nodes.cfg) 7.3 Partition Groups 7.4 Table Spaces 7.4.1 Table Space Classification 7.4.2 Default Table Spaces 7.5 Buffer Pools 7.6 Schemas 7.7 Data Types 7.7.1 DB2 Built-in Data Types 7.7.2 User-Defined Types 7.7.3 Choosing the Proper Data Type 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 Table Compression 7.8.10 Materialized Query Tables and Summary Tables 7.8.11 Temporary Tables 7.9 Indexes 7.9.1 Working with Indexes 7.9.2 Clustering Indexes 7.10 Multidimensional Clustering Tables and Block Indexes 7.10.1 MDC Tables 7.10.2 Block Indexes 7.10.3 The Block Map 7.10.4 Choosing Dimensions for MDC Tables 7.11 Views 7.11.1 View Classification 7.11.2 Using the WITH CHECK OPTION 7.11.3 Nested Views 7.12 Packages 7.13 Triggers 7.14 Stored Procedures 7.15 User-Defined Functions 7.16 Sequences 7.17 Case Study 7.18 Summary 7.19 Review Questions Chapter 8 The DB2 Storage Model 8.1 The Big Picture: The DB2 Storage Model 8.2 Databases: Logical and Physical Storage of Your Data 8.2.1 Creating a Database 8.2.2 The Default Database Structure 8.2.3 Database Creation Examples 8.2.4 Listing Databases 8.2.5 Dropping Databases 8.2.6 The Sample Database 8.3 Database Partition Groups 8.3.1 Database Partition Group Classifications 8.3.2 Default Partition Groups 8.3.3 Creating Database Partition Groups 8.3.4 Modifying a Database Partition Group 8.3.5 Listing Database Partition Groups 8.3.6 Dropping a Database Partition Group 8.4 Table Spaces 8.4.1 Containers 8.4.2 Pages 8.4.3 Extents 8.4.4 Creating Table Spaces 8.4.5 Container Tags 8.4.6 SMS Table Spaces 8.4.7 DMS Table Spaces 8.4.8 Table Space Considerations in a Multi-Partition Environment 8.4.9 Listing Table Spaces 8.4.10 Altering a Table Space 8.5 Buffer Pools 8.5.1 Creating Buffer Pools 8.5.2 Maximizing Buffer Pool Size on Windows 8.5.3 Altering Buffer Pools 8.5.4 Dropping Buffer Pools 8.6 Case Study 8.7 Summary 8.8 Review Questions Chapter 9 Leveraging the Power of SQL 9.1 Querying DB2 Data 9.1.1 Derived Columns 9.1.2 The SELECT COUNT Statement 9.1.3 The SELECT DISTINCT Statement 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 DB2 Data 9.3 Selecting from UPDATE, DELETE, and INSERT 9.4 The MERGE Statement 9.5 Recursive SQL 9.6 The UNION, INTERSECT, and EXCEPT Operators 9.6.1 The UNION and UNION ALL Operators 9.6.2 The INTERSECT and INTERSECT ALL Operators 9.6.3 The EXCEPT and EXCEPT ALL Operators 9.7 Case Study 9.8 Summary 9.9 Review Questions Chapter 10 Implementing Security 10.1 DB2 Security Model: The Big Picture 10.2 Authentication 10.2.1 Configuring the Authentication Type at a DB2 Server 10.2.2 Configuring the Authentication Type at a DB2 Client 10.2.3 Authenticating Users at the DB2 Server 10.2.4 Authenticating Users with the Kerberos Security Service 10.2.5 Authenticating Users with Generic Security Service Plug-ins 10.2.6 Authenticating Users at the DB2 Clients 10.3 Data Encryption 10.4 Administrative Authorities 10.4.1 Managing Administrative Authorities 10.5 Database Object Privileges 10.5.1 Schema Privileges 10.5.2 Table Space Privileges 10.5.3 Table and View Privileges 10.5.4 Index Privileges 10.5.5 Package Privileges 10.5.6 Routine Privileges 10.5.7 Sequence Privileges 10.5.8 Implicit Privileges 10.6 Authority and Privilege Metadata 10.7 Windows Domain Considerations 10.7.1 Windows Global Groups and Local Groups 10.7.2 Access Tokens 10.8 Case Study 10.9 Summary 10.10 Review Questions Chapter 11 Understanding Concurrency and Locking 11.1 DB2 Locking and Concurrency: The Big Picture 11.2 Concurrency and Locking Scenarios 11.2.1 Lost Updates 11.2.2 Uncommitted Reads 11.2.3 Nonrepeatable Reads 11.2.4 Phantom Reads 11.3 DB2 Isolation Levels 11.3.1 Uncommitted Reads 11.3.2 Cursor Stability 11.3.3 Read Stability 11.3.4 Repeatable Reads 11.4 Changing Isolation Levels 11.4.1 Using the DB2 Command Window 11.4.2 Using the DB2 PRECOMPILE and BIND Commands 11.4.3 Using the DB2 Call Level Interface 11.4.4 Using the Application Programming Interface 11.4.5 Working with Statement Level Isolation Level 11.5 DB2 Locking 11.5.1 Lock Attributes 11.5.2 Lock Waits 11.5.3 Deadlocks 11.5.4 Lock Deferral 11.5.5 Lock Escalation 11.6 Diagnosing Lock Problems 11.6.1 Using the list applications Command 11.6.2 Using the force application Command 11.6.3 Using the Snapshot Monitor 11.6.4 Using Snapshot Table Functions 11.6.5 Using the Event Monitor 11.6.6 Using the Activity Monitor 11.6.7 Using the Health Center 11.7 Techniques to Avoid Locking 11.8 Case Study 11.9 Summary 11.10 Review Questions Chapter 12 Maintaining Data 12.1 DB2 Data Movement Utilities: The Big Picture 12.2 Data Movement File Formats 12.2.1 Delimited ASCII (DEL) Format 12.2.2 Non-Delimited ASCII (ASC) Format 12.2.3 PC Version of IXF (PC/IXF) Format 12.2.4 WSF Format 12.2.5 Cursor 12.3 The DB2 EXPORT Utility 12.3.1 File Type Modifiers Supported in the Export Utility 12.3.2 Exporting Large Objects (LOBs) 12.3.3 Specifying Column Names 12.3.4 Authorities Required to Perform an Export 12.3.5 Exporting a Table Using the Control Center 12.4 The DB2 IMPORT Utility 12.4.1 Import Mode 12.4.2 Allow Concurrent Write Access 12.4.3 Regular Commits During an Import 12.4.4 Restarting a Failed Import 12.4.5 File Type Modifiers Supported in the Import Utility 12.4.6 Importing Large Objects 12.4.7 Selecting Columns to Import 12.4.8 Authorities Required to Perform an Import 12.4.9 Importing a Table Using the Control Center 12.5 The DB2 LOAD Utility 12.5.1 The Load Process 12.5.2 The LOAD Command 12.5.3 File Type Modifiers Supported in the Load Utility 12.5.4 Loading Large Objects 12.5.5 Collecting Statistics 12.5.6 The COPY YES/NO and NONRECOVERABLE Options 12.5.7 Validating Data Against Constraints 12.5.8 Performance Considerations 12.5.9 Authorities Required to Perform a Load 12.5.10 Loading a Table Using the Control Center 12.5.11 Monitoring a Load Operation 12.6 The db2move Utility 12.7 The db2relocatedb Utility 12.8 Generating Data Definition Language 12.9 DB2 Maintenance Utilities 12.9.1 The RUNSTATS Utility 12.9.2 The REORG and REORGCHK Utilities 12.9.3 The REBIND Utility and the FLUSH PACKAGE CACHE Command 12.9.4 Database Maintenance Process 12.10 Case Study 12.11 Summary 12.12 Review Questions Chapter 13 Developing Database Backup and Recovery Solutions 13.1 Database Recovery Concepts 13.1.1 Recovery Scenarios 13.1.2 Recovery Strategies 13.1.3 Unit of Work (Transactions) 13.1.4 Types of Recovery 13.2 DB2 Transaction Logs 13.2.1 Understanding the DB2 Transaction Logs 13.2.2 Primary and Secondary Log Files 13.2.3 States of Logs 13.2.4 Logging Methods 13.2.5 Handling the DB2 Transaction Logs 13.2.6 Userexit 13.3 Recovery Terminology 13.3.1 Logging Methods Versus Recovery Methods 13.3.2 Recoverable Versus Nonrecoverable Databases 13.4 Performing Database and Table Space Backups 13.4.1 Online Access Versus Offline Access 13.4.2 Database Backup 13.4.3 Table Space Backup 13.4.4 Incremental Backups 13.4.5 Performing Backups with the Control Center 13.4.6 The Backup Files 13.5 Database and Table Space Recovery Using the RESTORE DATABASE Command 13.5.1 Database Recovery 13.5.2 Table Space Recovery 13.5.3 Table Space Recovery Considerations 13.5.4 Performing RESTOREs with the Control Center 13.5.5 Redirected Restore 13.6 Database and Table Space Roll Forward 13.6.1 Database Roll Forward 13.6.2 Table Space Roll Forward 13.6.3 Table Space Roll Forward Considerations 13.6.4 Performing ROLLFORWARDs with the Control Center 13.7 Recovering a Dropped Table 13.8 The Recovery History File 13.9 Database Recovery Using the RECOVER DATABASE Command 13.10 High Availability Through Online Split Mirroring and Suspended I/O Support 13.10.1 Split Mirroring Key Concepts 13.10.2 Cloning a Database Using the db2inidb Snapshot Option 13.10.3 Creating a Standby Database Using the db2inidb Standby Option 13.10.4 Creating a Backup Image of the Primary Database Using the db2inidb Mirror Option 13.10.5 Split Mirroring in Partitioned Environments 13.11 High Availability Disaster Recovery 13.11.1 Overview of HADR 13.11.2 Overview of HADR Setup 13.11.3 Synchronization Modes 13.11.4 The HADR Wizard 13.12 Using DB2 Tools to Inspect the Health of Your Database 13.12.1 The db2dart Tool 13.12.2 The INSPECT tool 13.13 Case Study 13.14 Summary 13.15 Review Questions Chapter 14 The DB2 Process Model 14.1. The DB2 Process Model: The Big Picture 14.2 The DB2 Engine Dispatchable Units 14.2.1 The DB2 Instance-Level EDUs 14.2.2 The DB2 Database-Level EDUs 14.2.3 The Application-Level EDUs 14.2.4 Per-Request EDUs 14.3 Tuning the Number of EDUs 14.4 Monitoring and Tuning the DB2 Agents 14.5 The Connection Concentrator 14.6 Commonly Seen DB2 Executables 14.7 Additional Services/Processes on Windows 14.8 Case Study Diagnosing a Problem with the Help of DB2 Processes How to Control the Number of Connections by Setting the Number of DB2 Agents Example 1 Example 2 14.9 Summary 14.8 Review Questions Chapter 15 The DB2 Memory Model 15.1 DB2 Memory Allocation: The Big Picture 15.2 Instance-Level Shared Memory 15.3 Database-Level Shared Memory 15.3.1 The Database Buffer Pools 15.3.2 The Database Lock List 15.3.3 The Database Shared Sort Heap Threshold 15.3.4 The Package Cache 15.3.5 The Utility Heap Size 15.3.6 The Catalog Cache 15.3.7 Database Logging Parameters 15.3.8 Database Memory 15.4 Application-Level Shared Memory 15.4.1 Application Group Shared Memory 15.5 Agent-Level Private Memory 15.5.1 Application Heap 15.5.2 The Sort Heap and Sort Heap Threshold 15.5.3 Query Heap 15.5.4 Client I/O Block Size 15.5.5 Agent Stack 15.5.6 Java Interpreter Heap 15.5.7 Statement Heap 15.5.8 Statistics Heap 15.6 The Memory Model 15.7 32-Bit Memory Model Considerations 15.8 64-Bit Memory Model Considerations 15.9 AWE Support with Windows 15.10 Case Study 15.11 Summary 15.12 Review Questions Chapter 16 Database Performance Considerations 16.1 Performance Fundamentals 16.2 System/Server Configuration 16.2.1 Ensuring There Is Enough Memory Available 16.2.2 Ensuring There Are Enough Disks to Handle I/O 16.2.3 Ensuring There Are Enough CPUs to Handle the Workload 16.3 The DB2 Configuration Advisor 16.3.1 Invoking the Configuration Advisor from the Command Line 16.3.2 Invoking the Configuration Advisor from the Control Center 16.4 Configuring the DB2 Instance 16.4.1 Maximum Requester I/O Block Size 16.4.2 Intra-Partition Parallelism 16.4.3 Sort Heap Threshold 16.4.4 The DB2 Agent Pool 16.5 Configuring Your Databases 16.5.1 Average Number of Active Applications 16.5.2 Database Logging 16.5.3 Sorting 16.5.4 Locking 16.5.5 Buffer Pool Prefetching and Cleaning 16.6 Lack of Proper Maintenance 16.7 The Snapshot Monitor 16.7.1 Setting the Monitor Switches 16.7.2 Capturing Snapshot Information 16.7.3 Resetting the Snapshot Monitor Switches 16.8 Event Monitors 16.9 The DB2 Optimizer 16.10 The Explain Tool and Explain Tables 16.11 Using Visual Explain to Examine Access Plans 16.12 Case Study 16.13 Summary 16.14 Review Questions Chapter 17 Diagnosing Problems 17.1 The Big Picture: Problem Diagnosis 17.2 How Does DB2 Report Problems? 17.3 DB2 Error Message Description 17.4 DB2 First-Failure Data Capture 17.4.1 The Database Manager Configuration Parameters Related to FFDC 17.4.2 A db2diag.log Example 17.4.3 Administration Notification Log Examples 17.5 Receiving E-mail Notifications 17.6 The db2support Tool 17.7 The DB2 Trace Facility 17.8 Searching for Known Problems 17.9 Case Study 17.10 Summary 17.11 Review Questions Appendix A Solutions to the Review Questions Appendix B Use of Uppercase Versus Lowercase in DB2 Appendix C IBM Servers Appendix D Using the DB2 System Catalog Tables D.1 DB2 System Catalog Tables D.2 How to Extract Information from the System Catalog Tables D.3 How to Use the SYSSTAT Tables to Perform What-if Modeling and Analysis Appendix E Setting Up Database Connectivity for DB2 UDB for z/OS and DB2 UDB for iSeries Part I: Setting Up Database Connectivity for DB2 UDB for z/OS Part II: Setting Up Database Connectivity for DB2 UDB for iSeries Appendix F Diagnosing DB2 Connectivity Problems F.1 Diagnosing Client-Server TCP/IP Connection Problems F.1.1 Verifying the Server Configuration F.1.2 Verifying the Client Configuration Glossary Resources About the Authors Index
Library of Congress Subject Headings for this publication:
Relational databases.
IBM Database 2.