Sams Teach Yourself DB2® Universal Database™ in 21 Days, Second Edition

Sams Teach Yourself DB2® Universal Database™ in 21 Days, Second Edition

by Susan Visser, Bill Wong
Released August 2003
Publisher(s): Sams
ISBN: 9780672325823

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

Marketshare for DB2 has been growing steadily over the past 5 years and with the recent release of DB2 Universal Database V8, the product has never had more momentum. Not only is the product used in every company on the Fortune 500, but it is becoming very popular in the small to medium sized businesses as well. Sams Teach Yourself DB2 Universal Database in 21 Days, Second Edition, focuses on performing tasks using the graphical interfaces and wizards that are provided with DB2 on the Windows platform. (DB2 also runs on z/OS, OS/400, AIX, Linux, HP-UX, and Sun Solaris.) Readers are guided through performing all the commonly used tasks to run DB2, including installing DB2, setting up DB2, creating databases and tables, populating the database with data, accessing the data, ensuring the database is tuned for performance. This book differs from the competition in that it provides examples and scenarios making it very easy for the reader to learn complicated tasks. It gives them everything they need for the commonly used tasks in a simple to understand manner. Quizzes and exercises strengthen the knowledge gained and ensure concepts are learned rather than memorized.

Table of contents

  1. Copyright
  2. About the Author
  3. Acknowledgments
  4. We Want to Hear from You!
  5. Introduction
    1. Assumptions
    2. How to Use This Book
    3. Conventions Used in This Book
    4. About the CD-ROM
  6. 1. At a Glance
    1. 1. What DB2 Can Do for You
      1. What Is DB2 Universal Database?
        1. DB2’s Various Flavors
          1. DB2 Workgroup Server Edition
          2. DB2 Enterprise Server Edition
          3. DB2 Express Edition
          4. DB2 Personal Edition
          5. DB2 Personal Developer’s Edition
          6. DB2 Universal Developer’s Edition
          7. DB2 Connect Enterprise Edition
          8. DB2 Connect Personal Edition
        2. DB2’s Architecture
        3. DB2 and Its Companion Products
          1. The DB2 Server
          2. The DB2 Clients
      2. Understanding How DB2 Universal Database Works with Data
      3. DB2 Tools for Administering Databases
        1. Managing Databases with the Control Center
        2. Managing Communications on the Server
        3. Managing Connections to Databases with the Configuration Assistant
      4. Accessing Host Data from the Desktop
      5. Developing Applications with the DB2 Developer’s Editions
      6. Accessing DB2 Data from the Web
        1. Using Java Database Connectivity
      7. Using DB2 Parallelism
      8. Overview of the DB2 Enterprise Host Servers
        1. DB2 Universal Database for z/OS and OS/390
        2. DB2 Universal Database for iSeries
        3. DB2 Server for VSE & VM
      9. Summary
      10. What Comes Next?
      11. Q&A
      12. Workshop
        1. Quiz
    2. 2. Exploring the Capabilities of DB2 Universal Database
      1. Major Components of DB2
      2. What Is a Relational Database?
        1. Tables, Columns, and Rows
        2. Views
        3. Schemas
        4. Keys
          1. Unique and Primary Keys
          2. Foreign Keys
          3. Indexes
        5. Packages
        6. Data Types
          1. Distinct Types
          2. Large Object (LOB) Support
        7. Functions
      3. Data Integrity
        1. Roll-Forward Recovery
        2. Constraints
          1. Unique Constraints
          2. Referential Constraints and Relationships
          3. Table Check Constraints
          4. Informational Constraints
        3. User-Defined Types (UDTs)
        4. Triggers
        5. System Catalog Tables
        6. Instances
        7. Directories
        8. Storage Objects
          1. Table Spaces
          2. Containers
          3. Buffer Pool
        9. Configuration Files
        10. Recovery Objects
          1. Log Files
            1. Circular Logging
            2. Archive Logging
            3. Active Logs
            4. Online Archived Logs
            5. Offline Archived Logs
          2. Recovery History File
      4. Application Programs
        1. Embedded SQL
          1. Static and Dynamic SQL
          2. Precompiling and Binding
        2. Open Database Connectivity (ODBC)
        3. DB2 Call Level Interface (DB2 CLI)
        4. Application Programming Interfaces (APIs)
      5. System Management Facilities
        1. Online Administrative Capability
        2. Lightweight Directory Access Protocol (LDAP)
      6. Summary
      7. What Comes Next?
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercise
    3. 3. Installing and Configuring DB2 Server
      1. Preparing for the Install
        1. Hardware and Software Requirements
        2. Creating a User Account for Installing DB2 Products
      2. Performing a Typical Install
      3. Performing a Custom Install
      4. Performing a Compact Install
      5. Installing the Product Information library
      6. Modifying, Repairing, or Removing DB2
      7. Summary
      8. What Comes Next?
      9. Q&A
      10. Workshop
        1. Quiz
    4. 4. Getting Started
      1. Logging On to the System
      2. Understanding the Desktop
      3. Changing Passwords
      4. Starting or Stopping DB2
      5. Using DB2 First Steps
        1. Creating the Sample Database
        2. Viewing and Working with the Sample Database
          1. The Menu Bar
          2. The Control Center Toolbar
          3. The Object Tree
          4. The Contents Pane
          5. The Contents Pane’s Toolbar
        3. Viewing the Product Information library
        4. Organizing and Viewing Objects by Schema
        5. Connecting to a Database
      6. Granting Privileges to Other Users
        1. Changing Default Privileges for Users
      7. Summary
      8. What Comes Next?
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercise
    5. 5. Setting Up DB2 Instances and Server Communications
      1. Working with DB2 Instances
        1. Setting Up Instances
        2. Listing Available Instances
        3. Adding Additional Instances
        4. How DB2 Selects an Instance
          1. Setting DB2INSTANCE for the Current Session
          2. Setting DB2INSTANCE for All Sessions
          3. Setting DB2INSTANCE Globally
        5. Starting and Stopping a DB2 Server Instance
        6. Running Multiple Instances Concurrently
        7. Attaching to Instances
        8. Removing Instances
      2. Modifying the DB2 Communication Configuration of Server Instances
      3. Viewing and Modifying Configuration Files
        1. Viewing and Modifying Database Manager Configuration Files
        2. Viewing and Modifying Database Configuration Files
        3. Viewing and Modifying Registry Variables
      4. Adding Systems Through the Control Center
      5. The DB2 Administration Server
        1. Setting Discovery on the Server
        2. Setting Discovery Parameters at the DAS Level
        3. Setting Discovery Parameters at the Instance Level
        4. Setting Discovery Parameters at the Database Level
      6. Server Profiles
        1. Generating a Server Profile
      7. Summary
      8. What Comes Next?
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    6. 6. Installing and Configuring DB2 Clients
      1. Understanding the Different Types of DB2 Clients
      2. Preparing for the Install
        1. Hardware and Software Requirements
        2. Creating a User Account for Installing DB2 Products
      3. Performing a Typical Install
      4. Performing a Custom Install
      5. Performing a Compact Install
      6. Configuring Client-to-Server Communications with the Configuration Assistant
      7. Configuring Database Connections
        1. Configuring Database Connections Using a Profile
        2. Searching the Network for Databases
        3. Manually Configure a Connection to a DB2 Database
        4. Verifying the Connection
        5. Creating Client Profiles
          1. Exporting a Client Profile
          2. Importing a Client Profile
      8. Summary
      9. What Comes Next?
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercise
    7. 7. Ensuring Data Security
      1. Authentication
        1. Authentication Types
          1. SERVER
          2. SERVER_ENCRYPT
          3. CLIENT
          4. KERBEROS
          5. KERBEROS_ENCRYPT
        2. Setting the Authentication Level
      2. Access to DB2 Universal Database
      3. Access Within DB2
        1. Authorities
          1. SYSADM
          2. DBADM
          3. SYSCTRL
          4. SYSMAINT
          5. LOAD
        2. Schemas
        3. Privileges
        4. Granting and Revoking Privileges and Authorities
      4. Windows Security Considerations
      5. Summary
      6. What Comes Next?
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercise
  7. 2. At a Glance
    1. 8. Designing the CDLIB Database
      1. Deciding What Data to Store in the Database
        1. The CATEGORY Table
        2. The RECORDLABEL Table
        3. The ARTIST Table
        4. The GROUP Table
        5. The ALBUM Table
        6. The SONG Table
        7. The ARTISTINGROUP Table
        8. The PORTRAIT Table
        9. The AUDIOCLIP Table
        10. The ALBUMCOVER Table
      2. Defining Tables for Each Type of Relationship
      3. Identifying the Primary Key
      4. Identifying Constraints and Foreign Keys
      5. Normalizing Your Table
        1. First Normal Form
        2. Second Normal Form
        3. Third Normal Form
      6. Summary
      7. What Comes Next?
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercise
    2. 9. Creating Databases and Tables
      1. Creating a Simple Database
        1. Creating Tables
        2. Adding Data to Tables
        3. Setting Up Referential Integrity
        4. Altering Tables
      2. Summary
      3. What Comes Next?
      4. Q&A
      5. Workshop
        1. Quiz
        2. Exercise
    3. 10. Creating Table Spaces
      1. Managing Your Data in Table Spaces
      2. Using the Create Table Space Wizard
      3. Allocating Additional Space
      4. Creating a Buffer Pool
      5. Creating Table Spaces for Indexes and Large Data
      6. Creating Tables in Table Spaces
      7. Summary
      8. What Comes Next?
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercises
    4. 11. Accessing the Data
      1. Accessing Data Through the Control Center
      2. Accessing Data Through the Command Center
      3. Using the Command Line Processor
        1. Entering Commands
        2. Entering Commands in a Command Window
      4. Accessing Data Through Microsoft Access
      5. Accessing Data Through Java Applications
        1. JRE/JDK Support
        2. Setting Up the Windows Java Environment
        3. Java Sample Programs
        4. Accessing Data Through WebSphere Application Server
      6. Accessing Data Through Your Own Applications
        1. Binding Database Utilities
      7. Summary
      8. What Comes Next?
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercise
    5. 12. SQL Concepts
      1. Data Manipulation Language
        1. Selecting Columns
        2. Selecting Rows
        3. Sorting Rows
        4. Removing Duplicate Rows
        5. Using Expressions to Calculate Values
        6. Naming Expressions
        7. Selecting Data from More Than One Table (Join)
        8. Using a Subquery
        9. Column Functions
        10. Scalar Functions
        11. Grouping
          1. Using a WHERE Clause with a GROUP BY Clause
          2. Using the HAVING Clause After the GROUP BY Clause
        12. Combining Queries by Set Operators
        13. IN, BETWEEN, LIKE, EXISTS, and Quantified Predicates
          1. Using the IN Predicate
          2. Using the BETWEEN Predicate
          3. Using the LIKE Predicate
          4. Using the EXISTS Predicate
          5. Quantified Predicates
        14. Inserting Data
        15. Changing Data
        16. Deleting Data
        17. Using Views
      2. How SQL Statements Are Invoked
        1. Embedding SQL into Applications
        2. Performance of Static Versus Dynamic SQL Statements
      3. Developing Windows Applications
      4. Summary
      5. What Comes Next?
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercise
    6. 13. Advanced SQL
      1. Object-Oriented SQL Concepts
        1. Constraints
          1. Referential Constraints and Relationships
        2. User-Defined Types (UDTs)
          1. User-Defined Structured Types
        3. Triggers
        4. Routines (Stored Procedures, UDFs, Methods)
          1. Registering Routines
          2. SQL Procedures
        5. Large Object Support
      2. Development Center
      3. DB2 XML Extender
      4. DB2 Net Search Extender
      5. DB2 Spatial Extender
      6. Summary
      7. What Comes Next?
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercise
    7. 14. Design Considerations
      1. System Resources
        1. Disk Space Management
        2. Separating Different Data Types
        3. Enabling Prefetch
        4. Large Object Considerations
        5. General System Recommendations
      2. General SQL Guidelines
        1. Stored Procedures
        2. Compound SQL
        3. General SQL Recommendations
      3. Managing Concurrency
        1. Row and Table Locking
        2. General Concurrency Recommendations
      4. Index Considerations
        1. General Index Recommendations
      5. Complex Query Application Considerations
      6. Summary
      7. What Comes Next?
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercise
  8. 3. At a Glance
    1. 15. Using System Administration Tools
      1. Using the Task Center
        1. Creating a DB2 Command Script
        2. Importing Scripts
        3. Running Scripts
        4. Scheduling Scripts
      2. Using the Journal
        1. Viewing the Results of a Job
      3. Customizing Tools Settings
      4. Making Your Database More Useful
        1. Creating Indexes
      5. Working with Directories
      6. Managing Contacts
      7. Managing Licenses
      8. Summary
      9. What Comes Next?
      10. Q&A
      11. Workshop
        1. Quiz
        2. Exercise
    2. 16. Recovery Concepts
      1. Log Management
      2. Backing Up Databases
        1. Using the Backup Wizard
        2. Forcing Users Off DB2
      3. Recovering Data
        1. Using the Restore Data Wizard
      4. Database History File
      5. Summary
      6. What Comes Next?
      7. Q&A
      8. Workshop
        1. Quiz
        2. Exercise
    3. 17. Moving Data
      1. Exporting Data
        1. Performing a Simple Export
        2. Specifying Column Names
        3. Exporting Large Objects (LOBs)
      2. Importing and Loading Data
        1. Importing Data from Files
          1. Specifying the Path, Import Format, and Other Settings
          2. Non-delimited ASCII (ASC) Import Options
          3. Delimited ASCII (DEL) Import Options
          4. WSF Import Options
          5. IXF Import Options
      3. Loading Data
        1. Setting Integrity Constraints
      4. Summary
      5. What Comes Next?
      6. Q&A
      7. Workshop
        1. Quiz
        2. Exercise
    4. 18. Replicating Data
      1. Replication Concepts
      2. Creating a Replication Environment
        1. Step 1: Create the Capture Control Tables
        2. Step 2: Register a Source Table
        3. Step 3: Create the Apply Control Tables
        4. Step 4: Create a Subscription Set
        5. Step 5: Start the Capture Program
        6. Step 6: Start the Apply Program
        7. Replication Operations
      3. Summary
      4. What Comes Next?
      5. Q&A
      6. Workshop
        1. Quiz
        2. Exercise
    5. 19. Database Monitoring Tools
      1. Health Monitor
      2. Database System Monitor
        1. Event Monitoring
          1. Creating an Event Monitor
          2. Stopping an Event Monitor
          3. Viewing Monitored Events
        2. Snapshot Monitoring
      3. Using Visual Explain
        1. Producing an Access Plan Graph
        2. Analyzing an Access Plan Graph
          1. Cumulative Costs
          2. Cumulative Properties
          3. Input Arguments
      4. Collecting Statistics
      5. Reorganizing a Table
      6. Storage Management Tool
      7. Summary
      8. What Comes Next?
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercise
    6. 20. Tuning DB2 Universal Database Performance
      1. Controlling Your DB2 Environment
        1. Controlling the DB2 Profile Registry
        2. DB2 Registry Values and Environment Variables
      2. Configuration Parameters
        1. Setting Database Manager Configuration Parameters on a Client Instance
        2. Setting Database Manager Configuration Parameters on a Server Instance
        3. Setting Database Configuration Parameters on a Server Instance
        4. Summary of Configuration Parameters Covered in This Book
      3. Using the Configuration Advisor
      4. Using the Design Advisor
      5. Input/Output Performance
        1. Indexes
        2. Buffer Pools
        3. Asynchronous Buffer Writer
        4. Row Blocking
        5. Prefetching Data Pages
        6. Parallel Input/Output
        7. Big Block Reads
      6. Summary
      7. What Comes Next?
      8. Q&A
      9. Workshop
        1. Quiz
        2. Exercise
    7. 21. Diagnosing Problems
      1. Accessing Documentation
        1. Online Messages
      2. Error-Logging Facilities
        1. Administration Notification Log
        2. DB2 Diagnostic Log
      3. Error-Logging Facilities
        1. Inspect Command
        2. Database Analysis and Reporting Tool
      4. Configuration Files
      5. Tracing Facilities
        1. Trace Configuration
        2. DB2 CLI Trace
        3. ODBC Driver Manager Trace
        4. DB2 JDBC Trace
      6. Updating DB2 Products
        1. Handling Insufficient Space
      7. Summary
      8. What Comes Next?
      9. Q&A
      10. Workshop
        1. Quiz
        2. Exercise
  9. 4. Appendixes
    1. A. Getting Certified
      1. What Is Certification?
      2. Benefits of Becoming Certified
      3. Am I Ready to Take the Exam?
      4. Taking a Certification Exam
      5. IBM Certified Database Associate DB2 UDB V8.1 Family Fundamentals
        1. Certification Requirements
        2. Exam Objectives for Exam 700, DB2 UDB V8.1 Family Fundamentals
      6. Beyond Exam 700
        1. Database Administrator Role
        2. Solution Designer Role
        3. Application Developer Role
    2. B. Answers to Quiz Questions
      1. Day 1
      2. Day 2
      3. Day 3
      4. Day 4
      5. Day 5
      6. Day 6
      7. Day 7
      8. Day 8
      9. Day 9
      10. Day 10
      11. Day 11
      12. Day 12
      13. Day 13
      14. Day 14
      15. Day 15
      16. Day 16
      17. Day 17
      18. Day 18
      19. Day 19
      20. Day 20
      21. Day 21

Product information

  • Title: Sams Teach Yourself DB2® Universal Database™ in 21 Days, Second Edition
  • Author(s): Susan Visser, Bill Wong
  • Release date: August 2003
  • Publisher(s): Sams
  • ISBN: 9780672325823