DB2 Server for VSE & VM: Database Administration

Table of Contents

About This Manual

  • Some Terminology
  • Components of the Relational Database Management System
  • Organization
  • Prerequisite IBM Publications
  • Highlighting Conventions

    • |How to Send Your Comments

    Syntax Notation Conventions

    SQL Reserved Words

    Summary of Changes

  • |Summary of Changes for DB2 Version 7 Release 1
  • |Enhancements, New Functions, and New Capabilities
  • |Reliability, Availability, and Serviceability Improvements
  • |Library Enhancements

    • Chapter 1. Designing a Database

  • Sample Tables
  • Entities, Properties, and Occurrences
  • Step 1: Select the Data to Record in the Database
  • Step 2: Define Tables for Each Type of Relationship
  • One-to-One Relationships
  • One-to-Many and Many-to-One Relationships
  • Many-to-Many Relationships
  • Step 3: Provide Column Definitions for Tables
  • Step 4: Identify One or More Columns as a Primary Key
  • Step 5: Ensure that Equal Values Represent the Same Entity
  • Step 6: Plan for Referential Integrity
  • Elements of Referential Integrity
  • DELETE, INSERT, and UPDATE Considerations
  • Step 7: Normalize Your Tables
  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • Fourth Normal Form
  • Step 8: Considerations for Distributed Data
  • Definitions
  • Application Programming
  • System Operations
  • Distributing Existing Data

    • Chapter 2. Implementing Your Design

  • Storage Concepts
  • How Information is Stored in Dbspaces
  • Database Generation
  • Defining Dbspaces
  • Identifying Dbspace Requirements
  • Adding Dbspaces to the Database
  • Acquiring Dbspaces
  • Retrieving Information about Dbspace Parameters
  • Restrictions on the ACQUIRE DBSPACE Statement
  • Creating Tables
  • Controlling Who Creates Tables
  • How to Create Tables
  • Naming Tables
  • Choosing Columns
  • Specifying Columns
  • Specifying Data Types
  • Specifying a PRIMARY KEY
  • Specifying a UNIQUE Constraint
  • Considerations for Referential Integrity when Creating Tables
  • Placing Tables in Dbspaces
  • Creating Views
  • Reasons for Using Views
  • Creating a View on a Table
  • Creating a View from Several Tables
  • Things You Cannot Do with a View
  • Materializing a View
  • Creating Indexes
  • Index Key
  • UNIQUE Indexes
  • The PCTFREE Clause
  • Clustering Rows of a Table on an Index
  • Some Things to Remember When Defining Keys
  • General Performance Considerations on the Use of Indexes
  • Migration Considerations for Indexes
  • Using the Catalog in Database Design
  • Retrieving Catalog Information about a Table
  • Retrieving Catalog Information about Columns
  • Retrieving Catalog Information about Indexes
  • Retrieving Catalog Information about Views
  • Retrieving Catalog Information about Authorization
  • The COMMENT ON Statement

    • Chapter 3. Maintaining Your Database

  • Maintaining Tables
  • Loading Data into Tables
  • Copying Tables
  • Moving Tables from One Dbspace to Another
  • Merging Data from Multiple Tables
  • Altering the Design of a Table
  • Altering Referential and Unique Constraints
  • Enforcing Referential Constraints
  • Moving Data from One Application Server to Another
  • Removing Tables
  • Maintaining Dbspaces
  • Altering the Design of a Dbspace
  • Reorganizing a Dbspace to Free Storage Pool Pages
  • Removing Dbspaces
  • VSAM Restrictions
  • Reorganizing Indexes on the Catalog Tables

    • Chapter 4. Supporting Your Users

  • Adding a New User
  • Setting Up New ISQL Users
  • Authorizing Access
  • Specifying a Default Application Server in VM
  • Loading Initial Tables
  • Training New Users
  • Removing Users from an Application Server
  • Example

    • Chapter 5. Providing Security

  • Authorities
  • Types of Authorities
  • Granting Authorities
  • Revoking Authorities
  • Privileges
  • Privileges of Ownership
  • Granting Privileges to Other Users
  • Revoking Privileges
  • Monitoring Privileges
  • Privileges on Application Programs
  • Connecting to an Application Server in VM
  • Establishing a Default Application Server
  • Connecting to the Application Server Implicitly
  • Connecting to the Application Server Explicitly
  • Connecting to an Application Server in VSE
  • Establishing a Default Application Server
  • Connecting to the Application Server in Different VSE Environments
  • User IDs for Remote CICS/VSE Transactions
  • Connecting to an Application Server in Special Circumstances
  • |Resolving Remote Server Name to Target Database (CICS)
  • |Resolving Remote Server Name to Target Database (VSE Batch)
  • Restricting Access Using Views
  • Example
  • Changing User Passwords
  • Example
  • Securing the Database Catalog Tables
  • Example 1
  • Example 2
  • Example 3
  • Security Auditing
  • Auditing Security Using the Catalog Tables
  • Auditing Security Using Tracing

    • Chapter 6. Recovering from Failures

  • Overview of Recovery Concepts
  • Logical Units of Work
  • CMS Work Units
  • Atomic Operations
  • Dynamic Application Backout
  • Restart Processing
  • Recovery from Application Failures
  • Application Program Recovery in VM
  • Dropping the DB2 Server for VM Resource Adapter Code
  • Batch and VSE/ICCF Application Recovery
  • Online Application Recovery
  • ISQL Sessions
  • DBS Utility Processing
  • Preprocessor
  • Recovery from User Logic Errors
  • Dynamic Recovery from User Errors
  • Selective Recovery from User Data Errors
  • Database Recovery from User Logic Errors

    • Chapter 7. Customizing the HELP Text and Messages Text

  • The SYSLANGUAGE Table
  • The SYSTEXT1 and SYSTEXT2 Tables
  • Adding Topics to HELP Text Tables
  • Adding a HELP Topic to the HELP Text Supplied by IBM
  • Creating Your Own HELP Text Tables
  • Making the HELPTEXT Dbspace Larger
  • Moving the HELP Text to Another Dbspace
  • Printing the HELP Text Using the DBS Utility
  • Printing the HELP Text Using ISQL

    • Chapter 8. Application Design Considerations

  • Application Implementation Capabilities
  • Batch/Interactive Capabilities
  • Online (CICS) Transaction Processing Capabilities
  • Query Capabilities
  • Report Writing Capabilities
  • Programmed Application Capabilities
  • EXECs that Use DB2 Server for VM Facilities
  • Application Development Capabilities
  • Application Database Considerations
  • Database Support for Application Development
  • Database Support for Query/Report Writing
  • Application Implementation Considerations
  • VSE Batch/Interactive Application Considerations
  • Online CICS/VSE Transaction Considerations
  • Application Development Considerations
  • Loading Data into Test Dbspaces
  • Use of Synonyms in Application Development
  • Testing SQL Statements
  • Checking Application Code
  • Query/Report Writing Considerations
  • User Identifiers (Userids) for Query Users
  • Application Independence with CMS Work Units
  • Application Maintenance Considerations
  • Data Administration Support
  • Data Independence Support
  • Arithmetic Operations
  • Data Access Changes
  • Hypothetical Change Support

    • Chapter 9. DB2 Server for VM Database Configurations

  • DB2 Server for VM Concepts
  • Operating Modes for the Database Machine
  • Example Configurations
  • One Database Machine with One Database
  • One Database Machine with Two Databases
  • Several Database Machines with Many Databases
  • Multiple Database Machines on Different Processors
  • Accessing a Database from a Processor that Does Not Have One
  • Performance Considerations with Multiple Databases
  • VSE Guest Sharing (On VM/ESA Systems Only)

    • Chapter 10. Usage Environments in VSE

  • Batch/Interactive Application Processing
  • Online (CICS) Transaction Processing
  • Application Development
  • Query/Report Writing

    • Chapter 11. Stored Procedures

  • Stored Procedure Concepts
  • Stored Procedure Servers
  • The Stored Procedure Server
  • The Stored Procedure Handler
  • Stored Procedure Server Groups
  • Setting up a Stored Procedure Server
  • Managing Stored Procedure Servers
  • Stored Procedure Server Allocation
  • States of a Stored Procedure Server
  • Altering or Dropping a Stored Procedure Server Definition
  • Stored Procedures
  • Preparing a Stored Procedure to Run
  • Dropping or Altering a Stored Procedure
  • Initialization Parameters Affecting Stored Procedure Execution
  • PTIMEOUT Parameter
  • PROCMXAB Parameter
  • Summary of Environment Interactions

    • Appendix A. Estimating Your Dbspace Requirements

  • Estimating Dbspace Size
  • General Guidelines
  • Estimating Storage for a Table
  • Estimating the Number of Header Pages
  • Estimating the Number of Data Pages
  • Estimating the Number of Index Pages
  • Estimating Internal Dbspace Size and DASD Needs for Sort Operations
  • When Do We Sort?
  • Internal Dbspace Characteristics
  • Calculating Internal Dbspace Size Requirements
  • Calculating Total Internal Dbspace and DASD Needs

    • Appendix B. CMS EXECs

  • SQLINIT EXEC
  • Initializing a User Machine
  • SQLGLOB EXEC
  • SQLCIREO EXEC
  • SQLDBID EXEC
  • SQLRMEND EXEC
  • Example
  • ARISDBHD EXEC
  • ARISDBLD EXEC
  • SQLLEVEL EXEC

    • Appendix C. Querying the Status of an Application (VM Only)

  • Example

    • Appendix D. Maximums

  • ISQL Maximums

    • Appendix E. SQLGLOB Parameters (VSE Only)

  • Transactions for Updating SQLGLOB Parameters
  • DSQG - Update global SQLGLOB Parm Transaction
  • DSQU - Update user SQLGLOB Parm Transaction
  • DSQQ - Query SQLGLOB Parm Transaction
  • DSQD - Delete user SQLGLOB Parm Transaction
  • |Batch Program to Update/Query the SQLGLOB File
  • |Using Online and Batch Resource Adapter Tracing
  • |Online Trace File JCL
  • |Batch Trace File JCL
  • |Formatting the Online or Batch Trace File

    • Notices

  • Trademarks

    • Bibliography

    Index


    [ Top of Page | Previous Page | Next Page | Table of Contents | Index ]