A Complete Guide to DB2 Universal Database book cover

A Complete Guide to DB2 Universal Database

DB2 Universal Database (UDB) supports many different types of applications, on many different kinds of data, in many different software and hardware environments.

This book provides a complete guide to DB2 UDB Version 5 in all its aspects, including the interfaces that support end users, application developers, and database administrators. It is complementary to the IBM product documentation, providing a clear and informal explanation of how the features of DB2 were intended to be used. It is an extensive revision of the author's earlier book, Using the New DB2: IBM's Object-Relational Database System.

Paperback, 816 Pages

Published: June 1998

Imprint: Morgan Kaufmann

ISBN: 978-1-55860-482-7

Contents

  • 1. Introduction

    1.1 About This Book

    1.1.1 Notational Conventions

    1.1.2 Syntax Diagrams

    1.1.3 Examples

    1.1.4 Tips

    1.2 Product Overview

    1.2.1 UDB Clients and Servers

    1.2.2 Related Products

    1.2.3 Instances and Databases

    1.2.4 Interactive Tools

    1.2.5 Application Programs

    1.2.6 Dynamic Applications

    1.2.7 Stored Procedures

    1.2.8 User Roles

    1.3 A Brief History of SQL

    1.3.1 System R

    1.3.2 Products and Standards

    1.3.3 Some Controversial Decisions

    1.3.4 References

    2. Basics

    2.1 Tables

    2.1.1 Example Database

    2.2 Names and Schemas

    2.3 Basic SQL Datatypes

    2.4 Queries

    2.4.1 Expressions

    2.4.2 Datetime Arithmetic

    2.4.3 Casting

    2.4.4 Search Conditions

    2.4.5 Joins

    2.4.6 Column Functions

    2.4.7 Grouping

    2.4.8 Query Blocks

    2.4.9 Queries and Literal Tables

    2.4.10 SELECT Statement

    2.4.11 VALUES Statement

    2.4.12 SQLCODE and SQLSTATE

    2.5 Data Modification

    2.5.1 INSERT Statement

    2.5.2 UPDATE Statement

    2.5.3 DELETE Statement

    2.6 Data Definition

    2.6.1 Creating a Table

    2.6.2 Altering a Table

    2.6.3 Renaming a Table

    2.6.4 Creating an Alias

    2.6.5 Creating a View

    2.6.6 Creating an Index

    2.6.7 Creating a Schema

    2.6.8 Dropping an Object

    2.6.9 Commenting on an Object

    2.6.10 Normalization

    2.7 Protecting Data Consistency

    2.7.1 Transactions

    2.7.2 Database Connections

    2.8 Authorization

    2.8.1 Instance-Level Authorities

    2.8.2 Database-Level Authorities

    2.8.3 Table and View Privileges

    2.8.4 Index Privileges

    2.8.5 Schema Privileges

    2.8.6 Package Privileges

    2.8.7 GRANT and REVOKE Statements

    2.8.8 Authorization Checking

    3. Interactive SQL

    3.1 DB2 Tools

    3.1.1 The Command Center

    3.1.2 The Script Center

    3.1.3 The Journal

    3.1.4 The Information Center

    3.2 The Command Line Processor

    3.2.1 Command Options

    3.3 Interactive Commands

    3.3.1 Controlling Isolation Level

    3.3.2 Controlling Connection Type

    3.3.3 Getting Help

    3.3.4 Comments

    4. Static SQL

    4.1 Using Static SQL in C Programs

    4.1.1 Host Variables

    4.1.2 The SQL Declare Section

    4.1.3 Exchanging Double-Byte Strings

    4.1.4 Return Codes and Messages

    4.1.5 WHENEVER Statement

    4.1.6 Cursor Declarations

    4.1.7 OPEN Statement

    4.1.8 FETCH Statement

    4.1.9 CLOSE Statement

    4.1.10 Single-Row SELECT and VALUES Statements

    4.1.11 Positioned UPDATE and DELETE Statements

    4.1.12 Using Cursors with Interactive SQL

    4.1.13 Compound SQL

    4.1.14 Example Program PARTS1: Ordering Parts

    4.2 Using Static SQL in C++ Programs

    4.3 Building an Application Program

    4.3.1 Precompiling a Program

    4.3.2 Rebinding a Package

    5. Query Power

    5.1 CASE Expressions

    5.1.1 Simple Form

    5.1.2 General Form

    5.1.3 RAISE_ERROR Function

    5.1.4 NULLIF and COALESCE Functions

    5.2 Subqueries

    5.2.1 Scalar Subqueries

    5.2.2 Table Expressions

    5.3 Table Functions

    5.4 Explicit Joins .

    5.5 Extended FROM Clause

    5.6 Super Groups

    5.6.1 ROLLUP

    5.6.2 CUBE

    5.6.3 Grouping Sets

    5.6.4 Multiple Grouping Specifications

    5.7 Common Table Expressions

    5.8 Recursion

    5.8.1 Recursion with Computation

    5.8.2 Recursive Searching

    6. Datatypes and Functions

    6.1 Large Objects

    6.1.1 Creating LOB Columns

    6.1.2 Declaring Large-Object Variables in C and C++

    6.1.3 Locators

    6.1.4 File References

    6.1.5 Limitations of LOB Datatypes

    6.1.6 Example Program SCHOLAR: Processing Scholarship Applications

    6.2 Distinct Types

    6.2.1 Creating Distinct Types

    6.2.2 Casting Functions

    6.2.3 Using Distinct Types

    6.2.4 Assigning Distinct Types

    6.3 Function Path

    6.3.1 SET CURRENT FUNCTION PATH Statement

    6.4 User-Defined Functions

    6.4.1 Creating a Sourced Function

    6.4.2 Creating an External Scalar Function

    6.4.3 Function Resolution

    6.4.4 Implementing an External Scalar Function

    6.4.5 Installing an External Function

    6.4.6 Using Locators with External Functions

    6.4.7 Scratchpad Functions

    6.4.8 Table Functions

    6.4.9 Using External Functions with Distinct Types

    6.4.10 Writing an External Function in Java

    6.4.11 External Functions and OLE Automation

    6.4.12 Dropping a Function

    6.4.13 Commenting on a Function

    6.5 Steps Toward Objects

    6.5.1 Example: A Polygon Datatype

    6.6 Datatype Conversions

    6.6.1 Promotion of Function Arguments

    6.6.2 UNION Semantics

    6.6.3 Assignment

    6.6.4 Casting

    7. Active Data

    7.1 Constraints

    7.1.1 NOT NULL Constraints

    7.1.2 Column Defaults

    7.1.3 Unique Constraints

    7.1.4 Check Constraints

    7.1.5 Primary Key Constraints

    7.1.6 Foreign Key Constraints

    7.2 Creating and Dropping Constraints

    7.2.1 CREATE TABLE Statement

    7.2.2 ALTER TABLE Statement

    7.3 Triggers

    7.3.1 Creating and Dropping Triggers

    7.3.2 Assignment Statement

    7.3.3 SIGNAL Statement

    7.3.4 Before Triggers

    7.3.5 After Triggers

    7.3.6 Recursive Triggers

    7.3.7 Comparing Constraints and Triggers

    7.3.8 Interactions Among Constraints and Triggers

    7.4 Designing an Active Database

    7.5 Binding and Dependencies

    7.5.1 Conservative Binding Semantics

    7.5.2 Types of Dependencies

    8. Dynamic SQL

    8.1 Call Level Interface

    8.1.1 Handles

    8.1.2 Configuring CLI

    8.1.3 Summary of CLI Functions

    8.1.4 Typed Parameter Markers

    8.1.5 Example Program LOADER1

    8.1.6 Example Program QUERY1

    8.2 Using Dynamic SQL with Java

    8.2.1 JDBC Applications

    8.2.2 Example Program LOADER2

    8.2.3 JDBC Applets

    8.3 Embedded Dynamic SQL

    8.3.1 Embedded Dynamic Statements

    8.3.2 Example Program LOADER3

    8.3.3 The SQLDA Descriptor

    8.3.4 Using an SQLDA in a PREPARE or DESCRIBE Statement

    8.3.5 Using an SQLDA in an OPEN, FETCH, EXECUTE, or CALL

    Statement

    8.3.6 Example Program QUERY3

    9. Stored Procedures

    9.1 The Server Side

    9.1.1 Example Program SERVER1: A Stored Procedure for a Bank

    9.1.2 Rules for Implementing Stored Procedures

    9.1.3 Installing a Stored Procedure

    9.1.4 Writing a Stored Procedure in Java

    9.1.5 Writing a Stored Procedure in BASIC

    9.2 The Client Side

    9.2.1 The CALL Statement . . . 589

    9.2.2 Calling a Stored Procedure from a CLI Client

    9.2.3 Result Sets

    10. Database Administration

    10.1 Databases and Physical Space

    10.1.1 Tablespaces and Bufferpools

    10.1.2 Creating and Dropping Databases

    10.1.3 Where's the Data?

    10.2 Parallel Databases

    10.2.1 Intra-Partition Parallelism

    10.2.2 Inter-Partition Parallelism

    10.2.3 Reconfiguring a Parallel System

    10.3 The Control Center

    10.3.1 Systems (General)

    10.3.2 Systems (Specific)

    10.3.3 Instances (General)

    10.3.4 Instances (Specific)

    10.3.5 Databases (General)

    10.3.6 Databases (Specific)

    10.3.7 Objects Within Databases

    10.4 The Client Configuration Assistant

    10.5 Commands

    10.5.1 Managing Instances

    10.5.2 The Profile Registry

    10.5.3 The Administration Server

    10.5.4 Other Operating System-Level Commands

    10.5.5 UDB Commands

    10.6 Managing Database Recovery

    10.6.1 Types of Recovery

    10.6.2 Recovery Commands

    10.6.3 Using the Journal for Recovery

    10.7 Moving Data in Bulk

    10.7.1 File Formats

    10.7.2 Exporting Data

    10.7.3 Importing Data

    10.7.4 Loading Data

    10.7.5 Check Pending State

    10.7.6 Loading a Partitioned Database

    10.8 Tuning for Performance

    10.8.1 Controlling the Optimizer

    10.8.2 Statistics

    10.8.3 Reorganizing Tables

    10.8.4 Explaining a Plan

    10.9 Monitoring the Database

    10.9.1 The Snapshot Monitor

    10.9.2 Event Monitors

    Appendix A: Special Registers

    Appendix B: Functions

    B.1 Column Functions

    B.2 Scalar Functions

    B.3 Operators

    B.3.1 Prefix Operators

    B.3.2 Infix Operators

    Appendix C: Typecodes

    Appendix D: System Catalog Tables

    D.1 SYSCAT Catalog Views

    D.1.1 BUFFERPOOLNODES

    D.1.2 BUFFERPOOLS

    D.1.3 CHECKS

    D.1.4 COLAUTH

    D.1.5 COLCHECKS

    D.1.6 COLDIST

    D.1.7 COLUMNS

    D.1.8 CONSTDEP

    D.1.9 DATATYPES

    D.1.10 DBAUTH

    D.1.11 EVENTMONITORS

    D.1.12 EVENTS

    D.1.13 FUNCPARMS

    D.1.14 FUNCTIONS

    D.1.15 INDEXAUTH

    D.1.16 INDEXES

    D.1.17 KEYCOLUSE

    D.1.18 NODEGROUPDEF

    D.1.19 NODEGROUPS

    D.1.20 PACKAGEAUTH

    D.1.21 PACKAGEDEP

    D.1.22 PACKAGES

    D.1.23 PARTITIONMAPS

    D.1.24 PROCEDURES

    D.1.25 PROCPARMS

    D.1.26 REFERENCES

    D.1.27 SCHEMAAUTH

    D.1.28 SCHEMATA

    D.1.29 STATEMENTS

    D.1.30 TABAUTH

    D.1.31 TABCONST

    D.1.32 TABLES

    D.1.33 TABLESPACES

    D.1.34 TRIGDEP

    D.1.35 TRIGGERS

    D.1.36 VIEWDEP

    D.1.37 VIEWS

    D.2 SYSSTAT Updatable Catalog Views

    D.2.1 COLDIST

    D.2.2 COLUMNS

    D.2.3 FUNCTIONS

    D.2.4 INDEXES

    D.2.5 TABLES

    Appendix E: Syntax for Host Variable Declarations in C and C++

    E.1 Basic Datatypes

    E.1.1 Numeric Host Variables

    E.1.2 String Host Variables

    E.2 Large-Object Datatypes

    E.2.1 LOB Host Variables

    E.2.2 Locators and File References

    Appendix F: IBM Publications

    F.1 Platform-Independent Publications

    F.2 Platform-Specific Publications

     

     

Advertisement

advert image