Beginning DB2: From Novice to Professional

Beginning DB2: From Novice to Professional

Released August 2008
Publisher(s): Apress
ISBN: 9781590599426

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

IBM's DB2 Express Edition is one of the most capable of the free database platforms available in today's marketplace. In Beginning DB2, author Grant Allen gets you started using DB2 Express Edition for web sites, desktop applications, and more. The author covers the basics of DB2 for developers and database administrators, shows you how to manage data in both XML and relational form, and includes numerous code examples so that you are never in doubt as to how things work. In this book, you'll find:

  • A friendly introduction to DB2 Express Edition, an industrial-strength, relational database from IBM

  • Dozens of examples so that you are never in doubt as to how things work

  • Coverage of important language interfaces, such as from PHP, Ruby, C#, Python, and more

The book is aimed at developers who want a robust database to back their applications.

Table of contents

  1. Copyright
  2. About the Author
  3. About the Technical Reviewer
  4. Acknowledgments
  5. Introduction
  6. 1. Getting Started
  7. 1. What Is DB2 and Where Can I Get It?
    1. 1.1. Who Should Read This Book?
    2. 1.2. Why Choose DB2?
      1. 1.2.1. DB2 Has History on Its Side
      2. 1.2.2. DB2 Is at the Forefront of Database Technology Today
      3. 1.2.3. DB2 Will Be Even Bigger in the Future
    3. 1.3. Choosing the Right DB2 Edition For You
    4. 1.4. Obtaining DB2: A World of Choice
    5. 1.5. Summary
  8. 2. Installing DB2 for Linux and Windows
    1. 2.1. Installing on Windows
    2. 2.2. Installing on Linux
    3. 2.3. Unpacking Installation Downloads
    4. 2.4. Using the DB2 9 Discovery Kit DVD
    5. 2.5. Checking Preinstallation Requirements
      1. 2.5.1. Memory
      2. 2.5.2. Disk
      3. 2.5.3. Users and Groups
      4. 2.5.4. Other Configuration Prerequisites
    6. 2.6. Proceeding with the Installation
      1. 2.6.1. Graphical Installation Under Windows and Linux
    7. 2.7. Command-Line Installation Under Linux
    8. 2.8. Uninstalling DB2
    9. 2.9. Reviewing Your DB2 Installation
    10. 2.10. Summary
  9. 2. Beginning Administration with DB2 Express Edition
    1. 3. DB2 Wizards: The Fast Path to Working with Your New Database
      1. 3.1. DB2 First Steps
        1. 3.1.1. Launching DB2 First Steps Under Windows
        2. 3.1.2. Launching DB2 First Steps Under Linux
        3. 3.1.3. Working with DB2 First Steps
      2. 3.2. Creating the SAMPLE Database
      3. 3.3. The DB2 Control Center
        1. 3.3.1. Starting the DB2 Control Center from First Steps
        2. 3.3.2. Starting the DB2 Control Center in Other Ways
        3. 3.3.3. Selecting Your Preferred DB2 Control Center View
        4. 3.3.4. Browsing the DB2 Control Center Object Views
        5. 3.3.5. Invoking the Create Table Control Center Wizard
      4. 3.4. Using the DB2 Command Editor
      5. 3.5. Command-Line Control of Your DB2 Environment
        1. 3.5.1. Powering Up the Command Line Processor
        2. 3.5.2. Learning About the Commands
        3. 3.5.3. Connecting and Disconnecting
      6. 3.6. Summary
    2. 4. Controlling Your Environment with DB2 Control Center and Command-Line Tools
      1. 4.1. Getting Started as the Right User
      2. 4.2. Starting and Stopping DB2 Processes
        1. 4.2.1. From the Control Center
        2. 4.2.2. From the Command Line
      3. 4.3. Server Components
        1. 4.3.1. DB2 Instances and the DAS Instance
        2. 4.3.2. The DB2 Governor
        3. 4.3.3. The DB2 License Server
        4. 4.3.4. The DB2 Management Service
        5. 4.3.5. The DB2 Remote Command Server
        6. 4.3.6. The DB2 Security Server
        7. 4.3.7. The DB2 Fault Monitor Coordinator
      4. 4.4. Configuring and Changing Your DB2 Instances and Databases
        1. 4.4.1. Two Different Levels of Configuration
        2. 4.4.2. Configuring Your DB2 Instances from the Control Center
        3. 4.4.3. Configuring a DB2 Database from the Control Center
        4. 4.4.4. Configuring and Changing Your DB2 Instances from the CLP
        5. 4.4.5. Configuring and Changing a DB2 Database from the CLP
      5. 4.5. More GUI Tools to Help You Manage DB2
      6. 4.6. More Tools Available from the Command Line
        1. 4.6.1. The Version Information Utility: DB2LEVEL
        2. 4.6.2. The DB2 Registry Utility: DB2SET
        3. 4.6.3. The Instance Listing Utilities: DB2ILIST and DASLIST
        4. 4.6.4. The DB2 Problem Determination Tool: DB2PD
      7. 4.7. Summary
  10. 3. Database Fundamentals with DB2 Express Edition
  11. 5. IBM Data Studio
    1. 5.1. Sourcing the Data Studio
    2. 5.2. Installation Highlights
      1. 5.2.1. Multiple Installers for Data Studio
      2. 5.2.2. The Power of Eclipse-Based IDE Installations
    3. 5.3. Running Data Studio
      1. 5.3.1. Starting a New Data Project
      2. 5.3.2. New Project Connection Management
    4. 5.4. Adding Objects to Your Project
      1. 5.4.1. New SQL Statement Wizard
      2. 5.4.2. New Stored Procedure Wizard
    5. 5.5. Summary
  12. 6. SQL for DB2: Part 1
    1. 6.1. Manipulating Data with SQL
      1. 6.1.1. Using Select Statements
      2. 6.1.2. Using Insert Statements
      3. 6.1.3. Using Update Statements
      4. 6.1.4. Using Delete Statements
    2. 6.2. Summary
  13. 7. SQL for DB2: Part 2
    1. 7.1. Creating and Managing DB2 Tables
      1. 7.1.1. Data Types in DB2
      2. 7.1.2. Your First Table
    2. 7.2. Naming DB2 Tables
    3. 7.3. Specifying Column Details and Constraints
      1. 7.3.1. Working with Null Data
      2. 7.3.2. Primary Key Constraints
      3. 7.3.3. Referential Constraints
      4. 7.3.4. Check Constraints
      5. 7.3.5. Disabling Constraints and Constraint Deferral
    4. 7.4. Automatic Value Generation for Columns
    5. 7.5. Specifying Table Storage Characteristics
    6. 7.6. Other Table Characteristics
    7. 7.7. Other Techniques for Table Creation
    8. 7.8. Summary
  14. 8. Developing Functions and Stored Procedures in DB2
    1. 8.1. DB2 Stored Procedures
      1. 8.1.1. Creating Stored Procedures in DB2
      2. 8.1.2. Dropping Procedures
      3. 8.1.3. Managing Procedures
      4. 8.1.4. Further Reading on DB2 Stored Procedures
    2. 8.2. User-Defined Functions in DB2
      1. 8.2.1. Types of User-Defined Functions in DB2
      2. 8.2.2. Creating User-Defined Functions in DB2
      3. 8.2.3. Calling User-Defined Functions in DB2
      4. 8.2.4. Managing and Deleting User-Defined Functions
    3. 8.3. Summary
  15. 9. Controlling Access to DB2
    1. 9.1. DB2 Building Blocks for Authentication
      1. 9.1.1. Configuring Where and When DB2 Authenticates Users
      2. 9.1.2. Choosing Your Authentication Option
    2. 9.2. DB2 Instance-Level Privileged Groups
    3. 9.3. Changing Your DB2 Authentication Parameters
      1. 9.3.1. Using Control Center to Manage Authentication Parameters
      2. 9.3.2. Using DB2 CLP to Manage Authentication Parameters
    4. 9.4. Group Authentication Issues
      1. 9.4.1. Configuring DB2 to Use Local or Global Groups
      2. 9.4.2. 64 Groups Limitation
      3. 9.4.3. Support for Windows AD Domain Features
    5. 9.5. Security Context of the DB2 Server
    6. 9.6. Summary
  16. 10. Securing Data in DB2
    1. 10.1. Managing Authorities and Privileges in DB2
      1. 10.1.1. Preparing to Use Authorities and Privileges
      2. 10.1.2. Database-Level Authorities
      3. 10.1.3. Group Behavior in DB2
      4. 10.1.4. Roles in DB2
    2. 10.2. Managing Object Privileges in DB2
      1. 10.2.1. Working with Privileges on DB2 Objects
      2. 10.2.2. Granting Object Privileges to Users
      3. 10.2.3. Label-Based Access Control in DB2
      4. 10.2.4. LBAC in Action
    3. 10.3. Summary
  17. 11. Using XML with DB2
    1. 11.1. Exploring XML in the Sample Database
    2. 11.2. Querying Your XML Data
      1. 11.2.1. Using XQuery for XML
      2. 11.2.2. Using XPath Queries for XML
      3. 11.2.3. More pureXML Features for Querying Data
    3. 11.3. Changing XML Data
      1. 11.3.1. Inserting XML Data
      2. 11.3.2. XML Schema Registration in DB2
      3. 11.3.3. Updating XML Data
      4. 11.3.4. Deleting XML Data
    4. 11.4. Summary
  18. 12. Indexes, Sequences, and Views
    1. 12.1. Working with Indexes
      1. 12.1.1. Creating Indexes
      2. 12.1.2. Enforcing Unique Values
      3. 12.1.3. The Important Case of Foreign Keys
      4. 12.1.4. Understanding Other Index Features
      5. 12.1.5. Using Design Advisor
      6. 12.1.6. Index Wrap Up
    2. 12.2. Working with Sequences
      1. 12.2.1. Creating Sequences
      2. 12.2.2. Altering Sequences
      3. 12.2.3. Using Sequences
      4. 12.2.4. Sequence Wrap Up
    3. 12.3. Working with Views
      1. 12.3.1. Defining Views
      2. 12.3.2. Further View Options
      3. 12.3.3. View Wrap Up
    4. 12.4. Summary
  19. 4. Programming with DB2 Express Edition
  20. 13. PHP with DB2
    1. 13.1. PHP and Zend Core for DB2
      1. 13.1.1. Starting an Install of Zend Core
      2. 13.1.2. Continuing the Installation
      3. 13.1.3. Post-Installation Checks
    2. 13.2. Developing with the IBM_DB2 PHP Extensions
      1. 13.2.1. Connecting to a Database
      2. 13.2.2. Managing IBM_DB2 PHP Connections
      3. 13.2.3. Working with Ad Hoc SQL Statements
      4. 13.2.4. Working with Prepared Statements and Stored Procedures
      5. 13.2.5. Other IBM_DB2 PHP Functions
    3. 13.3. Developing with the Zend Framework
      1. 13.3.1. Configuring the Zend Framework
      2. 13.3.2. Managing Connections with the Zend Framework
      3. 13.3.3. Executing Statements Using the Zend Core Framework
      4. 13.3.4. Other Major Classes in the Zend Core Framework
    4. 13.4. Summary
  21. 14. DB2 on Rails
    1. 14.1. Installing Ruby and Rails
    2. 14.2. Developing with Ruby, Rails, and DB2
      1. 14.2.1. Creating a Project with Rails
      2. 14.2.2. Connecting a Rails Project to DB2
      3. 14.2.3. Designing DB2 Tables in Rails
      4. 14.2.4. Executing a Migration in Ruby for DB2
      5. 14.2.5. Evolving Your DB2 Schema with Ruby on Rails
      6. 14.2.6. The Schema_Info Table for Ruby on Rails
      7. 14.2.7. Reverting to Earlier Schema Incarnations
    3. 14.3. Ruby on Rails Scaffolding for DB2
      1. 14.3.1. Generating the Scaffolding
      2. 14.3.2. Ruby on Rails Scaffolding in Action with DB2
    4. 14.4. Summary
  22. 15. DB2 Development with Microsoft Visual Studio.NET
    1. 15.1. Installing IBM Database Add-Ins
      1. 15.1.1. Registering the IBM Data Server Provider for .NET
      2. 15.1.2. Testing DB2 .NET Connectivity
      3. 15.1.3. Completing the Installation
      4. 15.1.4. Testing the Database Add-Ins
    2. 15.2. Managing DB2 Objects
      1. 15.2.1. Creating a New DB2 View Object
      2. 15.2.2. Building the DB2 Database Project
    3. 15.3. Building .NET Windows Applications with DB2
      1. 15.3.1. Binding DB2 Objects to Windows Controls
      2. 15.3.2. Specifying a DB2 Connection for a Windows C# Project
      3. 15.3.3. Exploring a Connection's Related Objects
      4. 15.3.4. Building and Running the Windows Project
    4. 15.4. Building .NET Web Applications with DB2
      1. 15.4.1. Binding DB2 Objects to Web Controls
      2. 15.4.2. Building and Running the Web Project
    5. 15.5. Summary
  23. 16. Developing Java Applications with DB2
    1. 16.1. Writing JDBC Applications for DB2
      1. 16.1.1. JDBC Driver Types
      2. 16.1.2. Writing the First Java DB2 Application
      3. 16.1.3. Refactoring the Connection Code
      4. 16.1.4. Working with Data
      5. 16.1.5. Working with DB2 JDBC Features
    2. 16.2. Developing Java Stored Procedures
      1. 16.2.1. Writing the Stored Procedure Java Class
      2. 16.2.2. Deploying the Java Stored Procedure Class
      3. 16.2.3. Writing the Stored Procedure
      4. 16.2.4. Testing the Java DB2 Stored Procedure
    3. 16.3. Getting Started with DB2 pureQuery for Java
      1. 16.3.1. Creating a pureQuery-Enabled Project
      2. 16.3.2. Autogenerating pureQuery Java Mapping Classes
      3. 16.3.3. Examining pureQuery Results
    4. 16.4. Summary
  24. 17. Exploring More DB2 Development Options
    1. 17.1. Perl
      1. 17.1.1. Installing the Perl DBI and DBD::DB2 Modules
      2. 17.1.2. Getting Connected
      3. 17.1.3. Executing SQL Statements
      4. 17.1.4. Going Further with Perl and DB2
    2. 17.2. Python
      1. 17.2.1. Installing the Python DB2 Driver
      2. 17.2.2. Getting Connected
      3. 17.2.3. Executing SQL Statements
      4. 17.2.4. Going Further with Python and DB2
    3. 17.3. Other Languages
      1. 17.3.1. C and C++
      2. 17.3.2. COBOL
      3. 17.3.3. Rexx
      4. 17.3.4. Visual Basic and Visual Basic.NET
      5. 17.3.5. Other Samples
    4. 17.4. Summary
  25. 5. Ongoing Database Administration with DB2
  26. 18. Database Creation and Configuration
    1. 18.1. Building Blocks of DB2 Databases
    2. 18.2. Creating DB2 Databases
      1. 18.2.1. Specifying an Alias
      2. 18.2.2. Handling Languages and Text in DB2 Databases
    3. 18.3. Managing Storage for the DB2 Database
      1. 18.3.1. Understanding Tablespace Use in DB2
      2. 18.3.2. Choosing the Right Tablespace Storage Management Type
      3. 18.3.3. Using System Managed Storage Tablespaces
      4. 18.3.4. Using Database Managed Storage Tablespaces
      5. 18.3.5. Tuning Initial Tablespace Parameters
      6. 18.3.6. Introducing Buffer Pools
    4. 18.4. Moving Beyond Database, Tablespace, and Buffer Pool Basics
      1. 18.4.1. Creating Databases with Many Options
      2. 18.4.2. Altering Databases
      3. 18.4.3. Working with Tablespaces and Buffer Pools
      4. 18.4.4. Dropping Databases
    5. 18.5. Summary
  27. 19. DB2 Backup and Recovery
    1. 19.1. Why You Should Care
    2. 19.2. Permissions for Backup and Recovery
    3. 19.3. Understanding DB2 Logging
      1. 19.3.1. General Logging Principles
      2. 19.3.2. Log File Types
      3. 19.3.3. Circular Logging
      4. 19.3.4. Archive Logging
      5. 19.3.5. Infinite Logging
      6. 19.3.6. Log Housekeeping
    4. 19.4. DB2 Backups
      1. 19.4.1. Anatomy of a Backup
      2. 19.4.2. Taking Backups from the Control Center
      3. 19.4.3. Advanced Backup Options from the Command Line
    5. 19.5. Database Recovery with DB2
      1. 19.5.1. Database Recovery for Free
      2. 19.5.2. Restoring a Database from Backup
      3. 19.5.3. Roll Forward Recovery from a Backup
      4. 19.5.4. Database Recovery Using the Control Center
      5. 19.5.5. Advanced Recovery Options from the Command Line
    6. 19.6. Summary
  28. 20. Sharing Your DB2 Environment
    1. 20.1. DB2 Clients on Linux and Windows
      1. 20.1.1. Using the DB2 Express-C Edition Installer
      2. 20.1.2. Using the Dedicated DB2 Client Installers
      3. 20.1.3. Deciding Which Components to Install
    2. 20.2. Configuring the DB2 Client for Your Database
      1. 20.2.1. The DB2 Client Configuration Assistant
      2. 20.2.2. Command-Line DB2 Client Configuration
    3. 20.3. Other Connectivity Options
      1. 20.3.1. DB2 Runtime Client
      2. 20.3.2. Type 4 JDBC Driver
      3. 20.3.3. Third-Party Connectivity Options
    4. 20.4. Summary
  29. 21. Moving Data in Bulk with DB2
    1. 21.1. File Formats for Moving Data
      1. 21.1.1. ASCII Format
      2. 21.1.2. Delimited Format
      3. 21.1.3. Lotus 1-2-3 Worksheet Format
      4. 21.1.4. Integration Exchange Format
    2. 21.2. Exporting Data
      1. 21.2.1. Performing a Simple Export
      2. 21.2.2. Exporting LOBs
      3. 21.2.3. Exporting XML Objects
      4. 21.2.4. Exporting via the Control Center
    3. 21.3. Importing Data
      1. 21.3.1. Performing a Simple Import
      2. 21.3.2. Dealing with Import Warnings and Errors
      3. 21.3.3. Importing a Mix of New and Updated Data
      4. 21.3.4. Performing More-Complex Imports
      5. 21.3.5. Graphical Import Using the Control Center
    4. 21.4. Loading Data with the Load Utility
    5. 21.5. Other Data-Movement Tools
    6. 21.6. Summary
  30. 22. Working with Design
    1. 22.1. Database Design Overview
    2. 22.2. Statement of Requirements
    3. 22.3. Identification of Users and Use Cases
    4. 22.4. Analysis of Use Cases and Data Requirements
    5. 22.5. Finding Relationships in the Data
    6. 22.6. Building the Logical Data Model
    7. 22.7. Translating a Logical Model to a Physical Model
      1. 22.7.1. Building the Physical Model in DB2
    8. 22.8. Model Review
    9. 22.9. Summary
  31. 23. Moving Up to More Advanced DB2
    1. 23.1. Using the db2look and db2move Utilities
      1. 23.1.1. Using db2look
      2. 23.1.2. Using db2move
      3. 23.1.3. Using db2look from the Control Center
    2. 23.2. DB2 Replication
      1. 23.2.1. Preparing for Replication
      2. 23.2.2. Configuring Replication via the Replication Center
      3. 23.2.3. Testing Replication in Action
    3. 23.3. Data Row Compression
      1. 23.3.1. Data Row Compression Design
      2. 23.3.2. Estimating Compression Savings
      3. 23.3.3. Compressing a Table
      4. 23.3.4. Limitations
    4. 23.4. Summary
  32. 24. Monitoring and Tuning DB2 Performance
    1. 24.1. Starting with a Sensible Approach to Tuning
    2. 24.2. Proactive Tuning with the Configuration Advisor
      1. 24.2.1. Understanding Isolation Levels in DB2
      2. 24.2.2. Understanding Locking in DB2
      3. 24.2.3. Using DB2's Optimistic Locking Features
      4. 24.2.4. Continuing with the Configuration Advisor
    3. 24.3. Understanding Self-Tuning Memory in DB2
      1. 24.3.1. Traditional DB2 Memory Management Model
      2. 24.3.2. Activating STMM Mode
      3. 24.3.3. Self-Tuning Memory Management Mechanics
      4. 24.3.4. Using the Memory Visualizer
    4. 24.4. Monitoring and Tuning Database Activity
      1. 24.4.1. Activating Monitor Switches
      2. 24.4.2. Using DB2 Event Monitors
      3. 24.4.3. Using DB2 Snapshot Monitors
    5. 24.5. Tuning the DB2 Optimizer with RUNSTATS
      1. 24.5.1. Automated RUNSTATS in DB2
      2. 24.5.2. Manually Running the RUNSTATS Command
    6. 24.6. Using the REORGCHK and REORG Utilities
      1. 24.6.1. Working with REORGCHK
      2. 24.6.2. Working with REORG
    7. 24.7. Summary

Product information

  • Title: Beginning DB2: From Novice to Professional
  • Author(s):
  • Release date: August 2008
  • Publisher(s): Apress
  • ISBN: 9781590599426