book

Understanding DB2®: Learning Visually with Examples, Second Edition

Name: Understanding DB2®: Learning Visually with Examples, Second Edition
ISBN: 9780768681772

by Raul F. Chong, Xiaomei Wang, Michael Dang, Dwaine R. Snow

December 2007

Beginner to intermediate

1056 pages

24h 49m

English

IBM Press

Read now

Unlock full access

Copyright
Dedication
IBM Press
Information ManagementRational and Software DevelopmentComputingWeb SphereLotusOpen SourceBusiness Strategy & Management
Foreword
Preface
Who Should Read This Book?Getting StartedA Word of AdviceConventionsContacting the AuthorsWhat’s NewDB2 9DB2 9.5
Acknowledgments
About the Authors
A.0.1 Author Publications
1. Introduction to DB2
1.1. Brief History of DB21.2. The Role of DB2 in the Information On Demand World1.2.1. On-Demand Business1.2.2. Information On Demand1.2.3. Service-Oriented Architecture1.2.4. Web Services1.2.5. XML1.2.6. DB2 and the IBM Strategy1.3. DB2 Editions1.3.1. DB2 Everyplace Edition1.3.2. DB2 Personal Edition1.3.3. DB2 Express-C1.3.4. DB2 Express Edition1.3.5. DB2 Workgroup Server Edition1.3.6. DB2 Enterprise Server Edition1.4. DB2 Clients1.5. Try-and-Buy Versions1.6. Host Connectivity1.7. Federation Support1.8. Replication Support1.9. IBM WebSphere Federation Server and WebSphere Replication Server1.10. Special Package Offerings for Developers1.11. DB2 Syntax Diagram Conventions1.12. Case Study1.13. Summary1.14. Review Questions
2. DB2 at a Glance: The Big Picture
2.1. SQL Statements, XQuery Statements, and DB2 Commands2.1.1. SQL Statements2.1.2. XQuery Statements2.1.3. DB2 System Commands2.1.4. DB2 Command Line Processor (CLP) Commands2.2. DB2 Tools Overview2.2.1. Command-Line Tools2.2.2. General Administration Tools2.2.3. Information Tools2.2.4. Monitoring Tools2.2.5. Setup Tools2.2.6. Other Tools2.3. The DB2 Environment2.3.1. An Instance(1)2.3.2. The Database Administration Server2.3.3. Configuration Files and the DB2 Profile Registries(2)2.3.3.1. Environment Variables2.3.3.2. The DB2 Profile Registry2.3.3.3. Configuration Parameters2.3.4. Connectivity and DB2 Directories(3)2.3.4.1. System Database Directory2.3.4.2. Local Database Directory2.3.4.3. Node Directory2.3.4.4. Database Connection Services Directory2.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 Environment2.4. Federation2.5. Case Study: The DB2 Environment2.6. Database Partitioning Feature2.6.1. Database Partitions2.6.2. The Node Configuration File2.6.3. An Instance in the DPF Environment2.6.4. Partitioning a Database2.6.5. Configuration Files in a DPF Environment2.6.6. Logs in a DPF Environment2.6.7. The Catalog Partition2.6.8. Partition Groups2.6.9. Buffer Pools in a DPF Environment2.6.10. Table Spaces in a Partitioned Database Environment2.6.11. The Coordinator Partition2.6.12. Issuing Commands and SQL Statements in a DPF Environment2.6.12.1. The db2_all command2.6.12.2. Using Database Partition Expressions2.6.13. The DB2NODE Environment Variable2.6.14. Distribution Maps and Distribution Keys2.7. Case Study: DB2 with DPF Environment2.8. IBM Balanced Warehouse2.9. Summary2.10. Review Questions
3. Installing DB2
3.1. DB2 Installation: The Big Picture3.2. Installing DB2 Using the DB2 Setup Wizard3.2.1. Step 1 for Windows: Launch the DB2 Setup Wizard3.2.2. Step 1 for Linux and UNIX: Launch the DB2 Setup Wizard3.2.3. Step 2: Choose an Installation Type3.2.4. Step 3: Choose Whether to Generate a Response File3.2.5. Step 4: Specify the Installation Folder3.2.6. Step 5: Set User Information for the DB2 Administration Server3.2.7. Step 6: Create and Configure the DB2 Instance3.2.8. Step 7: Create the DB2 Tools Catalog3.2.9. Step 8: Enable the Alert Notification Feature3.2.10. Step 9: Specify a Contact for Health Monitor Notification3.2.11. Step 10: Enable Operating System Security for DB2 Objects (Windows Only)3.2.12. Step 11: Start the Installation3.3. Non-Root Installation on Linux and Unix3.3.1. Differences between Root and Non-Root Installations3.3.2. Limitations of Non-Root Installations3.3.3. Installing DB2 as a Non-Root User3.3.4. Enabling Root-Based Features in Non-Root Installations3.4. Required User IDs and Groups3.4.1. User IDs and Groups Required for Windows3.4.2. IDs and Groups Required for Linux and UNIX3.4.3. Creating User IDs and Groups if NIS Is Installed in Your Environment (Linux and UNIX Only)3.5. Silent Install Using a Response File3.5.1. Creating a Response File3.5.1.1. Creating a Response File Using the DB2 Setup Wizard3.5.1.2. Creating a Custom Response File Using a Sample Response File3.5.1.3. Creating a Response File Using the Response File Generator (Windows Only)3.5.2. Installing DB2 Using a Response File on Windows3.5.3. Installing DB2 Using a Response File on Linux and UNIX3.6. Advanced DB2 Installation Methods (Linux and UNIX Only)3.6.1. Installing DB2 Using the db2_install Script3.6.2. Manually Installing Payload Files (Linux and UNIX)3.7. Installing a DB2 License3.7.1. Installing a DB2 Product License Using the License Center3.7.2. Installing the DB2 Product License Using the db2licm Command3.8. Installing DB2 in a DPF Environment3.9. Installing Multiple db2 Versions and Fix Packs on the Same Server3.9.1. Coexistence of Multiple DB2 Versions and Fix Packs (Windows)3.9.2. Using Multiple DB2 Copies on the Same Computer (Windows)3.9.3. Coexistence of Multiple DB2 Versions and Fix Packs (Linux and UNIX)3.9.3.1. The db2ls Command (Linux and UNIX)3.9.3.2. DB2 Administrative Server (DAS) and Multiple DB2 Copies3.10. Installing DB2 Fix Packs3.10.1. Applying a Regular DB2 Fix Pack (All Supported Platforms and Products)3.10.2. Applying Fix Packs to a Non-Root Installation3.11. Migrating DB23.11.1. Migrating DB2 Servers3.11.2. Migration Steps for a DB2 Server on Windows3.11.3. Migration Steps for a DB2 Server on Linux and UNIX3.12. Case Study3.13. Summary3.14. Review Questions
4. Using the DB2 Tools
4.1. DB2 Tools: The Big Picture4.2. The Command-Line Tools4.2.1. The Command Line Processor and the Command Window4.2.1.1. Methods to Work with the CLPMethod 1: Interactive ModeMethod 2: Non-Interactive ModeMethod 3: Non-interactive Mode Using a File as Input4.2.1.2. CLP Command Options4.2.1.3. Obtaining Help Information from the CLP4.2.1.4. Line ContinuationMethod 1: Using the Backslash (\) CharacterMethod 2: Using a Delimiter Terminator Character with the CLP in Interactive Mode4.2.1.5. The CLP Front-End and Back-End Processes4.2.2. The Command Editor4.2.2.1. Visual Explain4.3. Web-Based Tools4.3.1. The Data Server Administration Console4.4. General Administration Tools4.4.1. The Control Center4.4.2. The Journal4.4.3. License Center4.4.4. The Replication Center4.4.5. The Task Center4.5. Information Tools4.5.1. Information Center4.5.2. Checking for DB2 Updates4.6. Monitoring Tools4.6.1. The Activity Monitor4.6.2. Event Analyzer4.6.3. Health Center4.6.4. Indoubt Transaction Manager4.6.5. The Memory Visualizer4.7. Setup Tools4.7.1. The Configuration Assistant4.7.2. Configure DB2 .NET Data Provider4.7.3. First Steps4.7.4. Register Visual Studio Add-Ins4.7.5. Default DB2 and Database Client Interface Selection Wizard4.8. Other Tools4.8.1. The IBM Data Studio4.8.2. SQL Assist4.8.3. Satellite Administration Center4.8.4. The db2pd Tool4.9. Tool Settings4.10. Case Study4.11. Summary4.12. Review Questions

5. Understanding the DB2 Environment, DB2 Instances, and Databases
5.1. The DB2 Environment, DB2 Instances, and Databases: The Big Picture5.2. The DB2 Environment5.2.1. Environment Variables5.2.2. DB2 Profile Registries5.3. The DB2 Instance5.3.1. Creating DB2 Instances5.3.2. Creating Client Instances5.3.3. Creating DB2 Instances in a Multipartitioned Environment5.3.4. Dropping an Instance5.3.5. Listing the Instances in Your System5.3.6. The DB2INSTANCE Environment Variable5.3.7. Starting a DB2 Instance5.3.8. Stopping a DB2 Instance5.3.9. Attaching to an Instance5.3.10. Configuring an Instance5.3.11. Working with an Instance from the Control Center5.3.12. The DB2 Commands at the Instance Level5.4. The Database Administration Server5.4.1. The DAS Commands5.5. Configuring a Database5.5.1. Configuring a Database from the Control Center5.5.2. The DB2 Commands at the Database Level5.6. Instance and Database Design Considerations5.7. Case Study5.8. Summary5.9. Review Questions
6. Configuring Client and Server Connectivity
6.1. Client and Server Connectivity: The Big Picture6.2. The DB2 Database Directories6.2.1. The DB2 Database Directories: An Analogy Using a Book6.2.2. The System Database Directory6.2.3. The Local Database Directory6.2.4. The Node Directory6.2.5. The Database Connection Services Directory6.2.6. The Relationship between the DB2 Directories6.2.6.1. A Local Connection6.2.6.2. A Remote Connection to a DB2 Database on another Server6.2.6.3. A Remote Connection to a Host DB2 Server6.3. Supported Connectivity Scenarios6.3.1. Scenario 1: Local Connection from a DB2 Client to a DB2 Server6.3.2. Scenario 2: Remote Connection from a DB2 Client to a DB2 Server6.3.2.1. Enabling the Database Server to Accept Client Connections6.3.2.1.1. TCP/IP connection6.3.2.2. Cataloging the Node Directory and Database Directory on the Client6.3.2.2.1. TCP/IP Connection6.3.3. Scenario 3: Remote Connection from a DB2 Client to a DB2 Host Server6.3.3.1. Enabling the Database Server to Accept Client Connections6.3.3.2. Cataloging the Node Directory, Database Directory, and DCS Directory on the Client6.3.3.2.1. TCP/IP Connection6.3.4. Scenario 4: Remote Connection from a DB2 Client to a DB2 Host Server via a DB2 Connect Gateway6.3.5. Binding Utilities6.4. Configuring Database Connections Using the Configuration Assistant6.4.1. Configuring a Connection Using DB2 Discovery in the Configuration Assistant6.4.2. Configuring a Connection Using Access Profiles in the Configuration Assistant6.4.2.1. Using the Configuration Assistant to Export an Access Profile6.4.2.2. Using the Configuration Assistant to Import an Access Profile6.4.2.2.1. Importing an Access Profile Using the CA’s Import Profile Function6.4.2.2.2. Importing an Access Profile Using the CA’s Add Database Wizard6.4.3. Configuring a Connection Manually Using the Configuration Assistant6.4.4. Automatic Client Reroute Feature6.4.5. Application Connection Timeout Support6.5. Diagnosing DB2 Connectivity Problems6.5.1. Diagnosing Client-Server TCP/IP Connection Problems6.5.1.1. Verifying the Server Configuration6.5.1.2. Verifying the Client Configuration6.6. Case Study6.7. Summary6.8. Review Questions
7. Working with Database Objects
7.1. DB2 Database Objects: The Big Picture7.2. Databases7.2.1. Database Partitions7.2.2. The Database Node Configuration File (db2nodes.cfg)7.3. Partition Groups7.4. Table Spaces7.4.1. Table Space Classification7.4.2. Default Table Spaces7.5. Buffer Pools7.6. Schemas7.7. Data Types7.7.1. DB2 Built-in Data Types7.7.1.1. Numeric Data Types7.7.1.2. String Data Types7.7.1.3. Datetime Data Types7.7.1.4. Extensible Markup Language Data Type7.7.2. User-Defined Types7.7.3. Choosing the Proper Data Type7.8. Tables7.8.1. Table Classification7.8.2. System Catalog Tables7.8.3. User Tables7.8.4. Default Values7.8.5. Using NULL Values7.8.6. Identity Columns7.8.7. Constraints7.8.7.1. Unique Constraints7.8.7.2. Referential Constraints7.8.7.2.1. Using Primary, Unique, and Foreign Keys to Establish Referential Integrity7.8.7.2.2. Referential Integrity Implications on SQL Operations7.8.7.3. Check Constraints7.8.7.4. Informational Constraints7.8.8. Not Logged Initially Tables7.8.9. Partitioned Tables7.8.9.1. Detaching Partitions7.8.10. Row Compression7.8.10.1. Enabling Row Compression7.8.10.2. Creating the Compression Dictionary7.8.10.3. Verifying Compression Characteristics7.8.10.4. Automatic Dictionary Creation7.8.11. Table Compression7.8.12. Materialized Query Tables and Summary Tables7.8.13. Temporary Tables7.9. Indexes7.9.1. Working with Indexes7.9.2. Clustering Indexes7.10. Multidimensional Clustering Tables and Block Indexes7.10.1. MDC Tables7.10.2. Block Indexes7.10.3. The Block Map7.10.4. Choosing Dimensions for MDC Tables7.11. Combining DPF, Table Partitioning, and MDC7.12. Views7.12.1. View Classification7.12.1.1. Deleteable Views7.12.1.2. Updatable Views7.12.1.3. Insertable Views7.12.1.4. Read-Only Views7.12.2. Using the WITH CHECK OPTION7.12.3. Nested Views7.13. Packages7.14. Triggers7.15. Stored Procedures7.16. User-Defined Functions7.17. Sequences7.18. Case Study1)2)7.19. Summary7.20. Review Questions
8. The DB2 Storage Model
8.1. The DB2 Storage Model: The Big Picture8.2. Databases: Logical and Physical Storage of Your Data8.2.1. Creating a Database8.2.2. Database Creation Examples8.2.2.1. Creating a Database in a Single-Partition Environment8.2.2.2. Creating a Database in a Multipartition Environment8.2.3. Listing Databases8.2.4. Dropping Databases8.2.5. The Sample Database8.3. Database Partition Groups8.3.1. Database Partition Group Classifications8.3.2. Default Partition Groups8.3.3. Creating Database Partition Groups8.3.4. Modifying a Database Partition Group8.3.5. Listing Database Partition Groups8.3.6. Dropping a Database Partition Group8.4. Table Spaces8.4.1. Containers8.4.2. Storage Paths8.4.3. Pages8.4.4. Extents8.4.5. Creating Table Spaces8.4.6. Automatic Storage8.4.6.1. Creating Automatic Storage Table Spaces8.4.7. SMS Table Spaces8.4.7.1. Creating SMS Table Spaces8.4.8. DMS Table Spaces8.4.8.1. Creating DMS Table Spaces8.4.8.1.1. Using Device Containers8.4.8.1.2. Using File Containers8.4.9. Table Space Considerations in a Multipartition Environment8.4.10. Listing Table Spaces8.4.11. Altering a Table Space8.4.11.1. Enlarging a DMS Table Space8.4.11.2. Shrinking a DMS Table Space8.4.11.2.1. The Table Space High-Water Mark8.4.11.3. Dropping a Table Space8.5. Buffer Pools8.5.1. Creating Buffer Pools8.5.2. Altering Buffer Pools8.5.3. Dropping Buffer Pools8.6. Case Study8.7. Summary8.8. Review Questions
9. Leveraging the Power of SQL
9.1. Querying DB2 Data9.1.1. Derived Columns9.1.2. The SELECT Statement with COUNT Aggregate Function9.1.3. The SELECT Statement with DISTINCT Clause9.1.4. DB2 Special Registers9.1.5. Scalar and Column Functions9.1.6. The CAST Expression9.1.7. The WHERE clause9.1.8. Using FETCH FIRST n ROWS ONLY9.1.9. The LIKE Predicate9.1.10. The BETWEEN Predicate9.1.11. The IN Predicate9.1.12. The ORDER BY Clause9.1.13. The GROUP BY...HAVING Clause9.1.14. Joins9.1.15. Working with NULLs9.1.16. The CASE Expression9.1.17. Adding a Row Number to the Result Set9.2. Modifying Table Data9.3. Selecting from UPDATE, DELETE, or INSERT9.4. The MERGE Statement9.5. Recursive SQL9.6. The UNION, INTERSECT, and EXCEPT Operators9.6.1. The UNION and UNION ALL Operators9.6.2. The INTERSECT and INTERSECT ALL Operators9.6.3. The EXCEPT and EXCEPT ALL Operators9.7. Case Study9.8. Summary9.9. Review Questions
10. Mastering the DB2 pureXML Support
10.1. XML: The Big Picture10.1.1. What Is XML?10.1.2. Well-Formed Versus Valid XML Documents10.1.3. Namespaces in XML10.1.4. XML Schema10.1.5. Working with XML Documents10.1.5.1. XML Parsers: DOM Versus SAX10.1.5.2. XQuery and XPath10.1.5.2.1. XPath Expressions10.1.5.3. SQL/XML10.1.6. XML versus the Relational Model10.1.7. XML and Databases10.1.7.1. Methods to Store and Work with XML Documents in Databases10.1.7.1.1. Using a CLOB or VARCHAR Column10.1.7.1.2. Using the Shredding Method to Store XML Documents10.1.7.1.3. Using pureXML10.2. pureXML in DB210.2.1. pureXML Storage in DB210.2.2. Base-Table Inlining and Compression of XML Data10.2.3. The pureXML Engine10.3. Querying XML Data10.3.1. Querying XML Data with XQuery10.3.1.1. The db2-fn:xmlcolumn Function10.3.1.2. The db2-fn:sqlquery Function10.3.1.3. XQuery with FLWOR Expressions10.3.1.4. Join Operations with XQuery10.3.2. Querying XML Data with SQL/XML10.3.2.1. XMLQUERY10.3.2.2. XMLEXISTS10.3.2.3. XMLTABLE10.3.2.4. XML Serialization10.4. SQL/XML Publishing Functions10.5. Transforming XML Documents Using XSLT Functions10.6. Inserting XML Data into a DB2 Database10.6.1. XML Parsing10.7. Updating and Deleting XML Data10.8. XML Indexes10.8.1. System Indexes10.8.2. User-Created Indexes10.8.3. Conditions for XML Index Eligibility10.9. XML Schema Support and Validation in DB210.9.1. XML Schema Repository10.9.1.1. Registering XSR Objects10.9.1.2. Obtaining Information about Registered XML Schemas10.9.2. XML Validation10.9.2.1. Performing Validation10.9.2.2. The IS VALIDATED Predicate10.9.2.3. Determining the Schema of a Previously Validated Document10.9.3. Compatible XML Schema Evolution10.10. Annotated XML Schema Decomposition10.11. XML Performance Considerations10.11.1. Choosing Correctly Where and How to Store Your XML Documents10.11.2. Creating Appropriate Indexes and Using SQL/XML and XQuery Statements Efficiently10.11.3. Ensure That Statistics on XML Columns Are Collected Accurately10.12. pureXML Restrictions10.13. Case Study10.14. Summary10.15. Review Questions
11. Implementing Security
11.1. DB2 Security Model: The Big Picture11.2. Authentication11.2.1. Configuring the Authentication Type at a DB2 Server11.2.2. Configuring the Authentication Type at a DB2 Client11.2.3. Authenticating Users at/on the DB2 Server11.2.4. Authenticating Users Using the Kerberos Security Service11.2.5. Authenticating Users with Generic Security Service Plug-ins11.2.6. Authenticating Users at/on the DB2 Client(s)11.3. Data Encryption11.4. Administrative Authorities11.4.1. Managing Administrative Authorities11.5. Database Object Privileges11.5.1. Schema Privileges11.5.2. Table Space Privileges11.5.3. Table and View Privileges11.5.4. Index Privileges11.5.5. Package Privileges11.5.6. Routine Privileges11.5.7. Sequence Privileges11.5.8. XSR Object Privileges11.5.9. Security Label Privileges11.5.10. LBAC Rule Exemption Privileges11.5.11. SET SESSION AUTHORIZATION Statement and SETSESSIONUSER Privilege11.5.12. Implicit Privileges11.5.13. Roles and Privileges11.5.14. TRANSFER OWNERSHIP Statement11.6. Label-Based Access Control (LBAC)11.6.1. Views and LBAC11.6.2. Implementing an LBAC Security Solution11.6.3. LBAC In Action11.6.4. Column Level Security11.7. Authority and Privilege Metadata11.8. Windows Domain Considerations11.8.1. Windows Global Groups and Local Groups11.8.2. Access Tokens11.9. Trusted Contexts Security Enhancement11.10. Case Study11.11. Summary11.12. Review Questions
12. Understanding Concurrency and Locking
12.1. DB2 Locking and Concurrency: The Big Picture12.2. Concurrency and Locking Scenarios12.2.1. Lost Updates12.2.2. Uncommitted Reads12.2.3. Nonrepeatable Reads12.2.4. Phantom Reads12.3. DB2 Isolation Levels12.3.1. Uncommitted Reads12.3.2. Cursor Stability12.3.3. Read Stability12.3.4. Repeatable Reads12.4. Changing Isolation Levels12.4.1. Using the DB2 Command Window12.4.2. Using the DB2 PRECOMPILE and BIND Commands12.4.3. Using the DB2 Call Level Interface12.4.4. Using the Application Programming Interface12.4.5. Working with Statement Level Isolation Level12.5. DB2 Locking12.5.1. Lock Attributes12.5.1.1. Table-Level Lock Modes12.5.1.2. Row Lock Modes12.5.2. Lock Waits12.5.3. Deadlocks12.5.4. Lock Deferral12.5.5. Lock Escalation12.6. Diagnosing Lock Problems12.6.1. Using the list applications Command12.6.2. Using the force application Command12.6.3. Using the Snapshot Monitor12.6.4. Using Snapshot Table Functions12.6.5. Using the Event Monitor12.6.6. Using the Activity Monitor12.6.7. Using the Health Center12.7. Techniques to Avoid Locking12.8. Case Study12.9. Summary12.10. Review Questions
13. Maintaining Data
13.1. DB2 Data Movement Utilities: The Big Picture13.2. Data Movement File Formats13.2.1. Delimited ASCII (DEL) Format13.2.2. Non-Delimited ASCII (ASC) Format13.2.3. PC Version of IXF (PC/IXF) Format13.2.4. WSF Format13.2.5. Cursor13.3. The DB2 EXPORT Utility13.3.1. File Type Modifiers Supported in the Export Utility13.3.1.1. Changing the Column Delimiter13.3.1.2. Changing the Character Delimiter13.3.1.3. Changing the Date Format13.3.1.4. Changing the Code Page13.3.2. Exporting Large Objects13.3.3. Exporting XML Data13.3.4. Specifying Column Names13.3.5. Authorities Required to Perform an Export13.3.6. Exporting a Table Using the Control Center13.3.7. Run an export Command Using the ADMIN_CMD Procedure13.4. The DB2 IMPORT Utility13.4.1. Import Mode13.4.2. Allow Concurrent Write Access13.4.3. Regular Commits during an Import13.4.4. Restarting a Failed Import13.4.5. File Type Modifiers Supported in the Import Utility13.4.5.1. Handling Target Tables with Generated and Identity Columns13.4.6. Importing Large Objects13.4.7. Importing XML Data13.4.8. Select Columns to Import13.4.9. Authorities Required to Perform an Import13.4.10. Importing a Table Using the Control Center13.4.11. Run an import Command with the ADMIN_CMD Procedure13.5. The DB2 Load Utility13.5.1. The Load Process13.5.2. The LOAD Command13.5.2.1. The MESSAGES, SAVECOUNT, and WARNINGCOUNT Options13.5.2.2. Loading from a CURSOR13.5.2.3. MODIFIED BY dumpfile and Exception Table13.5.2.4. Loading from a Client13.5.2.5. Locking Considerations during a Load13.5.2.6. The INDEXING MODE Option13.5.3. File Type Modifiers Supported in the load Utility13.5.3.1. Leaving Free Space in Data and Index Pages13.5.4. Loading Large Objects13.5.5. Loading XML Data13.5.6. Collecting Statistics13.5.7. The COPY YES/NO and NONRECOVERABLE Options13.5.8. Validating Data against Constraints13.5.9. Performance Considerations13.5.10. Authorities Required to Perform a Load13.5.11. Loading a Table Using the Control Center13.5.12. Run a load Command with the ADMIN_CMD Procedure13.5.13. Monitoring a Load Operation13.5.13.1. Table States13.5.13.2. Table Space States13.5.13.3. Load Querying13.5.13.4. The LIST UTILITIES Command13.6. The DB2MOVE Utility13.7. The db2relocatedb Utility13.8. Generating Data Definition Language13.9. DB2 Maintenance Utilities13.9.1. The RUNSTATS Utility13.9.2. The REORG and REORGCHK Utilities13.9.3. The REBIND Utility and the FLUSH PACKAGE CACHE Command13.9.4. Database Maintenance Process13.9.5. Automatic Database Maintenance13.10. Case Study13.11. Summary13.12. Review Questions
14. Developing Database Backup and Recovery Solutions
14.1. Database Recovery Concepts: The Big Picture14.1.1. Recovery Scenarios14.1.2. Recovery Strategies14.1.3. Unit of Work (Transaction)14.1.4. Types of Recovery14.1.4.1. Crash Recovery14.1.4.2. Version Recovery14.1.4.3. Roll Forward Recovery14.2. DB2 Transaction Logs14.2.1. Understanding the DB2 Transaction Logs14.2.2. Primary and Secondary Log Files14.2.3. Log File States14.2.3.1. Active Logs14.2.3.2. Online Archive Logs14.2.3.3. Offline Archive Logs14.2.4. Logging Methods14.2.4.1. Circular Logging14.2.4.2. Archival Logging14.2.4.3. Infinite Active Logging14.2.4.4. Log Mirroring14.2.5. Handling the DB2 Transaction Logs14.3. Recovery Terminology14.3.1. Logging Methods versus Recovery Methods14.3.2. Recoverable versus Nonrecoverable Databases14.4. Performing Database and Table Space Backups14.4.1. Online Access versus Offline Access14.4.2. Database Backup14.4.2.1. Database Backup on DPF Environments14.4.3. Table Space Backup14.4.4. Incremental Backups14.4.5. Backing Up a Database with the Control Center14.4.6. The Backup Files14.5. Database and Table Space Recovery Using the RESTORE DATABASE Command14.5.1. Database Recovery14.5.2. Table Space Recovery14.5.3. Table Space Recovery Considerations14.5.4. Restoring a Database with the Control Center14.5.5. Redirected Restore14.5.5.1. Classic Redirected Restore14.5.5.2. Redirected Restore Using an Automatically Generated Script14.6. Database and Table Space Roll Forward14.6.1. Database Roll Forward14.6.2. Table Space Roll Forward14.6.3. Table Space Roll Forward Considerations14.6.4. Roll Forward a Database Using the Control Center14.7. Recovering a Dropped Table14.8. The Recovery History File14.9. Database Recovery Using the RECOVER DATABASE Command14.10. Rebuild Database Option14.10.1. Rebuilding a Recoverable Database Using Table Space Backups14.10.2. Rebuilding a Recoverable Database Using Only a Subset of the Table Space Backups14.10.3. Rebuilding a Recoverable Database Using Online Backup Images That Contain Log Files14.10.4. Rebuilding a Recoverable Database Using Incremental Backup Images14.10.5. Rebuilding a Recoverable Database Using the Redirect Option14.10.6. Rebuilding a Nonrecoverable Database14.10.7. Database Rebuild Restrictions14.11. Backup Recovery through Online Split Mirroring and Suspended I/O Support14.11.1. Split Mirroring Key Concepts14.11.2. The db2inidb Tool14.11.3. Cloning a Database Using the db2inidb Snapshot Option14.11.4. Creating a Standby Database Using the db2inidb Standby Option14.11.5. Creating a Backup Image of the Primary Database Using the db2inidb Mirror Option14.11.6. Split Mirroring in Partitioned Environments14.11.7. Integrated Flash Copy14.12. Maintaining High Availability with DB214.12.1. Log Shipping14.12.1.1. Setting Up Log Shipping14.12.2. Overview of DB2 High Availability Disaster Recovery (HADR)14.12.2.1. HADR Setup14.12.2.2. HADR Database States14.12.2.3. HADR Synchronization ModesSYNCNearsyncASYNC14.12.2.4. HADR Commands Overview14.12.2.5. Setting Up and Initializing HADR14.12.2.6. Performing a Takeover—Switching Database Roles14.12.2.7. Performing a Takeover—Failover14.12.2.8. Summary of the Takeover Behavior with Respect to Database States14.12.2.9. The Automatic Client Reroute Feature and HADR14.12.2.10. Stopping HADR14.12.2.11. The HADR Wizard14.13. The Fault Monitor14.13.1. The Fault Monitor Facility (Linux and UNIX Only)14.13.2. Fault Monitor Registry File14.13.3. Setting Up the DB2 Fault Monitor14.14. Case Study14.15. SummaryReview Questions
15. The DB2 Process Model
15.1. The DB2 Process Model: The Big Picture15.2. Threaded Engine Infrastructure15.2.1. The DB2 Processes15.3. The DB2 Engine Dispatchable Units15.3.1. The DB2 Instance-Level EDUs15.3.2. The DB2 Database-Level EDUs15.3.3. The Application-Level EDUs15.3.4. Per-Request EDUs15.4. Tuning the Number of EDUs15.5. Monitoring and Tuning the DB2 Agents15.6. The Connection Concentrator15.7. Commonly Seen DB2 Executables15.8. Additional Services/Processes on Windows15.9. Case Study15.10. Summary15.11. Review Questions
16. The DB2 Memory Model
16.1. DB2 Memory Allocation: The Big Picture16.2. Instance-Level Shared Memory16.3. Database-Level Shared Memory16.3.1. The Database Buffer Pools16.3.2. The Database Lock List16.3.3. The Database Shared Sort Heap Threshold16.3.4. The Package Cache16.3.5. The Utility Heap Size16.3.6. The Catalog Cache16.3.7. Database Logging Parameters16.3.8. Database Memory16.4. Application-Level Shared Memory16.4.1. Application Shared Heap16.4.2. Application Heap16.4.3. Statement Heap16.4.4. Statistics Heap16.4.5. Application Memory16.5. Agent Private Memory16.5.1. The Sort Heap and Sort Heap Threshold16.5.2. Agent Stack16.5.3. Client I/O Block Size16.5.4. Java Interpreter Heap16.6. The Memory Model16.7. Case Study16.8. Summary16.9. Review Questions
17. Database Performance Considerations
17.1. Relation Data Performance Fundamentals17.2. System/Server Configuration17.2.1. Ensuring There Is Enough Memory Available17.2.2. Ensuring There Are Enough Disks to Handle I/O17.2.3. Ensuring There Are Enough CPUs to Handle the Workload17.3. The DB2 Configuration Advisor17.3.1. Invoking the Configuration Advisor from the Command Line17.3.2. Invoking the Configuration Advisor from the Control Center17.4. Configuring the DB2 Instance17.4.1. Maximum Requester I/O Block Size17.4.2. Intra-Partition Parallelism17.4.3. Sort Heap Threshold17.4.4. The DB2 Agent Pool17.5. Configuring Your Databases17.5.1. Average Number of Active Applications17.5.2. Database Logging17.5.3. Sorting17.5.4. Locking17.5.5. Buffer Pool Prefetching and Cleaning17.6. Lack of Proper Maintenance17.7. Automatic Maintenance17.8. The Snapshot Monitor17.8.1. Setting the Monitor Switches17.8.2. Capturing Snapshot Information17.8.3. Resetting the Snapshot Monitor Switches17.9. Event Monitors17.10. The DB2 Optimizer17.11. The Explain Tool and Explain Tables17.12. Using Visual Explain to Examine Access Plans17.13. Workload Management17.13.1. Preemptive Workload Management17.13.2. Reactive Workload Management17.14. Case Study17.15. Summary17.16. Review Questions
18. Diagnosing Problems
18.1. Problem Diagnosis: The Big Picture18.2. How DB2 Reports Issues18.3. DB2 Error Message Description18.4. DB2 First Failure Data Capture18.4.1. DB2 Instance-Level Configuration Parameters Related to FFDC18.4.1.1. The DIAGPATH Parameter18.4.1.2. The DIAGLEVEL Parameter18.4.1.3. The NOTIFYLEVEL Parameter18.4.2. db2diag.log Example18.4.3. Administration Notification Log Examples18.5. Receiving E-mail Notifications18.6. Tools for Troubleshooting18.6.1. The db2support tool18.6.2. The DB2 Trace Facility18.6.3. The db2dart Tool18.6.4. The INSPECT Tool18.6.5. DB2COS (DB2 Call Out Script)18.6.6. DB2PDCFG Command18.6.7. First Occurrence Data Capture (FODC)18.7. Searching for Known Problems18.8. Case Study18.9. Summary18.10. Review Questions
A. Solutions to the Review Questions
Chapter 1Chapter 2Chapter 3Chapter 4Chapter 5Chapter 6Chapter 7Chapter 8Chapter 9Chapter 10Chapter 11Chapter 12Chapter 13Chapter 14Chapter 15Chapter 16Chapter 17Chapter 18
B. Use of Uppercase versus Lowercase in DB2
C. IBM Servers
D. Using the DB2 System Catalog Tables
Db2 System Catalog TablesHow to Find Information in the System Catalog TablesHow to Use the Sysstat Tables to Perform What-If Modeling and Analysis
Resources

Overview

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Understanding DB2®: Learning Visually with Examples

Publisher Resources

ISBN: 9780768681772Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills