Table Of Content

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: 800-382-3419 Fax: 201-236-7141 ;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