DB2 Certification Guide For Common Servers

610 Pages·1997·95.3 MB·English

by  Grant Hutchison, Calene Janacek

gr.---..:----_--.--.:-,:.-.*.:.:-.;.-.::-..----..:..:--...-I - ?..?i: :.-:..-:i... {:i1----
i ion Cbrmon Sermers

D82 Certification Guide
for Common Servers
Grant Hutchison
and
Calene Janacek
To join a Prentice Hall PTR internet mailing list, point to
http://www.prenhall.com/register
-----
_____
_I_ - ____ - - - i - _--I- -
-+ . : I - I I1_11T1T11=11J-_--
___ _ _ ,
International Technical Support Organization
San Jose, California 95120
Prentice Hall PTR
Upper Saddle River, New Jersey 07458
http://www.prenhall.com
© Copyright International Business Machines Corporation 1997. All rights reserved.
Note to U.S. Government Users--Documentation related to restricted rights--Use, duplication9 or
disclosure is subject to restrictions set forth in GSA ADP Schedule Contract with IBM Corp®
More information about IBM, D82, and the ITSO:
http://www.ibm.com/services
http://www.software.ibm.com/data/db2
http://www.redbooks.ibm.com/redbooks
Editorial/production supervision: IV!.cfeo/czs Rczdfeztbcr
Cover desf gn.. Julie Santilli, IBM
Cover design director: /crry Vo/f¢
Manufacturing manager: A/cxz.I Hcyd/
Acquisitions editor: A4!.kc A4ccha#
Marketing Manager: Sfcpfoc# So/omo#
Editorial Assistant: Kczfc Hczrgcff
Published by Prentice Hall PTR
Prentice-Hall, Inc.
A Simon & Schuster Company
Upper Saddle River, New Jersey 07458
The Publisher offers discounts on this book when ordered in bulk quantities.
For more information, contact: Corporate Sales Department. Prentice Hall PTR,1 Lake St.,
Upper Saddle River, NJ 07458 Phone: [phone] Fax: [phone] ;E-mail:dan_rush@
prenhall.com.
The following terms are trademarks or registered trademarks of the IBM Corporation in the
United States and/or other countries: ADSTAR, Distributed Database Connection Services, DRDA,
DATABASE 2, D82, Distributed Relational Database Architecture. AIX, AS/400, OS/400,
VSEAISA, OS/2, SQL/DS, MVS/ESA9 IBM, VMAISA.
The following terms are trademarks of other companies as follows:
HP-UX Hewlett-Packard Company
Lotus,1-2-3 Lotus Development Corporation
Microsoft, Windows, Windows 95 Microsoft Corporation
Windows NT, Open Database Microsoft Corporation
Connectivity Microsoft Corporation
Postscript Adobe Systems, Incorporated
IPX/SPX, Netware, Novell Novell, Inc.
Solaris SUN Microsystems Inc.
UNIX, X/Open X/Open Company Limited
UNIX is a registered trademark in the U.S. and other countries licensed exclusively through X/Open
Company Limited. D82 Information on the Internet--http://www.software.ibm.com/data/db2
Printed in the United States of America
10 9 8 7 6 5 4 3 2 1
ISBN 0-13-727413-0
Prentice-Hall International (UK) Limited, Le#do#
Prentice-Hall of Australia Pty. Limited, Sychcy
Prentice-Hall Canada Inc., roro#fo
Prentice-Hall Hispanoamericana, S.A. , MCLx:I.co
Prentice-Hall of India Private Limited, IVcw Dc/fez.
Prentice-Hall of Japan, Inc., rokyo
Simon & Schuster Asia Pte. Ltd., S!.#gapore
Editora Prentice-Hall do Brasil, Ltda., Rz.o dc Jcz#c!+o
Table of Contents
Foreword XI
Preface xiii
Conventions XV
How this Book was Created xvii
About the Authors xviii
Acknowledgments xviii
Chapter 1 - Products and Components .......................... 1
D82 Family
D82 Components
D82 Command Line Processor (CLP) ............................. 5
D82 Administrator' s Toolkit 5
D82 Products 10
D82 Single-User 11
D82 Server 12
Distributed Database Connection Services (DDCS) ......... 13
D82 Software Developer's Kit (SDK) ............................. 15
Documentation 16
Licensing 17
D82 Client/Server Environment 18
Supported Communication Protocols 19
D82 Resource Usage 19
D82 Client/Server Scenarios 20
Summary 23
24
Questions
Answers 26
Chapter 2 - Getting Started and Connected .................. 27
Product Installation 28
in
Table of Contents
AIX Considerations 28
OS/2 Considerations 32
Windows NT Considerations
33
Windows 95 Considerations 34
D82 Client Installation 35
Installing the D82 SDK 35
Installing the CAE 35
Installing the ODBC Driver 37
Setting Up the D82 Environment 38
Creating an Instance 38
Starting the D82 Instance 42
Stopping the D82 Instance 44
Managing Instances 44
D82 Interfaces 44
Using the Command Line Processor 45
Using the Database Director 48
Creating the Sample Database 49
Summary 52
Questions 53
Answers 54
Exercises 55
Chapter3-ControllingDataAccess..............................59
Security 60
Overview of Security 60
Authorization Levels 64
Security Considerations 68
D82 Privileges (Data Control Language) ......................... 69
Explicit and Implicit Authorizations
Establishing a D82 Client/Server connection ............................ 76
Con figuring a D82 Server 77
D82 Directories
81
Con figuring D82 Clients 85
Cataloging a Database from a Client 85
Verifying the Connection 88
Binding Utilities 90
Instance Administration (ATTACH Command) ........................ 92
Local Instance Administration
User Attach Scenario
Table of Contents
Summary
Questions
Answers
Exercises
Chapter 4 - Using SQL 109
Understanding Database Objects 110
Data Types 110
D82-Supplied Data Types
id fl id
User-Defined Data Types 119
NULL Values 122
Code Page Considerations 123
Selecting the Correct Data Type 125
D82 Certification Database Design and Implementation 125
Tables 133
Table Spaces 137
Schemas 138
Indexes 139
Packages 140
Log Files 140
Transactions 141
Locks 141
Using SQL Data Definition Language (DDL) ........................... 141
Creating a D82 Database 143
Table Space Types 143
Views 144
150
Data Manipulation Language (DML) 153
Data Retrieval (SELECT) 153
Changing the Order of the Columns 156
Restricting Rows from a Table 157
Selecting Columns from Multiple Tables ........................ 160
D82 Functions
Data Modification (INSERTrupDATEmELETE) .......... 187
Removing Data
View Classification
Advanced SQL
User-Defined Defaults
Check Constraints
Table of Contents
Triggers
Summary
Questions
Answers
Exercises
Chapter 5 - Database Concurrency ................................ 219
Concurrency Considerations 220
Concurrency Problems 221
Lost Update Problem 221
Lost Update Solutions 224
Uncommitted Read 225
Non-Repeatable Read 226
Phantom Read Problem 227
Phantom Read Solution 228
Isolation Level 229
Uncommitted Read 229
Cursor Stability 229
Read Stability 230
Repeatable Read 230
Choosing an Isolation Level 230
LOcking 231
Lock Attributes 231
Lock Conversion 232
Lock Escalation 233
Lock Wait Behavior 233
Deadlock Behavior 234
Lock Table Statement 234
Summary 235
Questions 237
Answers 237
Exercises 238
Chapter6-DataPlacement............................................243
Containers
Table Spaces
Table Spaces and Containers
Extents
Table of Contents
Table Space Types
System Managed Storage (SMS) Table Spaces ......................... 247
Characteristics of the Default Table Spaces ..................... 249
When to Use SMS Table Spaces
Database Managed Storage (DMS) Table Spaces ............ 250
When to Use DMS Table Spaces 251
Planning a Table Space Environment 252
Creating Table Spaces 257
Performance Considerations 261
System Catalogs in D82 264
Managing Table Spaces 268
States of Table Spaces 276
Summary 278
279
Questions
Answers 281
Exercises 282
Chapter 7 - Data Management ........................................ 293
Populating Tables 294
The LOAD Utility 294
Using The LOAD Utility 297
Syntax of the LOAD Command 299
The SET CONSTRAINTS Statement 304
The IMPORT/EXPORT Utility 308
The EXPORT Utility 309
EXPORT Considerations 315
The IMPORT Utility 315
IMPORT Considerations 317
Data Maintenance 319
Analyzing Data's Physical Organization: REORGCHK . 319
Table Reorganization: REORG 325
RUNSTATS 328
Using the RUNSTATS Utility 329
Identifying Updated Statistics 330
The REBIND Utility 332
Data Maintenance Process 333
Modeling a Production Environment 334
User Maintenance 334
Fenced and Not-Fenced Procedures and Functions ......... 334
Table of Contents
The LIST APPLICATIONS command 336
The FORCE Command
336
Database Recovery
337
Unit of Work
337
Transaction 337
Use of Log Files
338
Types of Logging
341
Log File Usage 343
Backup and Restore 345
Performing a Table Space Level Backup or Restore ........ 352
Summary of Backup/Restore Considerations ................... 353
The Recovery History File 354
Graphical Management Tools 357
The Recovery Jobs Tool 357
The Database Director 358
Creating/Modifying D82 Objects
361
Summary 366
Questions 367
Answers
371
Exercises 372
Chapter8-DatabaseMonitoringandTuning..............377
Obtaining Database Access Information
Explain Facility
Query Compilation
Gathering Explain Data
Using the Explain Report Tools
Examining EXPLAIN Data
Database Monitoring
Event monitoring
Visual Performance Monitors
Performance Issues
Con figuring Database Resources
D82 Sorting Methods
D82 Server Resources
Problem Diagnosis
Performance Tuning Scenario
Summary
Questions