DB2数据库管理考试指南 英文版PDF电子书下载

PART Ⅰ DB2 Certification 1

Chapter 1 DB2 Universal Database Professional Certification 3

The Professional Certification Program from IBM 3

DB2 Universal Database Certification Roles 4

IBM Certified Specialist—DB2 UDB V6/V7 User 5

IBM Certified Solutions Expert—Business Intelligence 6

IBM Certified Solutions Expert—DB2 UDB V7.1 Database Administration for UNIX,Windows,and OS/2 6

IBM Certified Solutions Expert—DB2 UDB V7.1 Family Application Development 7

IBM Certified Solutions Expert—DB2 UDB V7.1 Database Administration for OS/390 8

IBM Certified Advanced Technical Expert—DB2 for Clusters 9

IBM Certified Advanced Technical Expert—DB2—DRDA 10

IBM Certified Advanced Technical Expert—DB2 Data Replication 11

The Certification Process 11

Preparing for the Tests 12

The Testing Procedure 13

Benefits Professional Certification Provides 15

Summary 16

PART Ⅱ DB2 UDB Fundamentals(Tests 509 and 512) 17

Chapter 2 Installation and Planning 19

The DB2 Universal Database Family 19

DB2 Universal Database Products 20

DB2 Universal Database Clients 26

Other DB2 Universal Database Products 27

DB2 Universal Database Packages 34

DB2's Administration Tools 36

The Control Center 36

The Command Center 38

The Script Center 41

The Alert Center 42

The Journal 42

The Command Line Processor(CLP) 43

The Performance Monitor 44

Summary 45

The Client Configuration Assistant(CCA) 45

Questions 47

Answers 48

Chapter 3 Instances and Security 50

Instances 50

The DB2 Administration Server(DAS)Instance 52

Controlling Database Access 53

Authentication 54

Authorities 55

Privileges 59

Granting and Revoking Authorities and Privileges 65

Summary 67

Questions 69

Answers 71

Chapter 4 Creating and Accessing DB2 Databases and DB2 Database Objects 72

What Is a DB2 Universal Database Database? 73

The Relational Database Model 73

Creating a DB2 Universal Database Database 75

Destroying a DB2 Universal Database Database 79

The DB2 Universal Database Directory Files 80

Uncataloging a DB2 Universal Database Database 81

Cataloging a DB2 Universal Database Database 82

Understanding Objects 84

System Objects 84

Recovery Objects 84

Storage Objects 84

Database(or Data)Objects 88

Summary 103

Questions 106

Answers 108

Chapter 5 Creating Table Objects 110

Understanding Data Types 110

Integer 111

Small Integer 111

Big Integer 112

Decimal 112

Single-Precision Floating Point 112

Double-Precision Floating Point 113

Fixed-Length Character String 113

Varying-Length Character String 113

Varying-Length Long Character String 114

Fixed-Length Double-Byte Character String 115

Varying-Length Double-Byte Character String 115

Varying-Length Double-Byte Long Character String 115

Date 116

Time 116

Timestamp 117

Binary Large Object 117

DataLink 118

Character Large Object 118

Double-Byte Character Large Object 118

User-Defined Data Types(UDTs) 119

Extenders 119

Understanding Constraints 120

The NOT NULL Constraint 120

The Column Default Constraint 121

Unique Constraints 123

Referential Constraints 124

Check Constraints 128

The CREATE TABLE SQL Statement 129

CREATE TABLE SQL Statement Examples and Resulting Table Characteristics 133

System Catalog Tables 135

Summary 137

Questions 140

Answers 143

Structured Query Language(SQL) 145

Chapter 6 Basic SQL 145

Data Definition Language(DDL)Statements 146

Creating Nodegroups—The CREATE NODEGROUP Statement 147

Creating Buffer Pools—The CREATE BUFFERPOOL Statement 149

Creating Table Spaces—The CREATE TABLESPACE Statement 150

Creating Tables—The CREATE TABLE Statement 153

Creating Views—The CREATE VIEW Statement 154

Creating Indexes—The CREATE INDEX Statement 158

Creating Schemas—The CREATE SCHEMA Statement 160

Creating Aliases—The CREATE ALIAS Statement 161

Describing an Object—The COMMENT ON Statement 162

Dropping an Object—The DROP Statement 163

Modifying Object Definitions 164

Data Manipulation Language(DML)Statements 175

Retrieving Data—The SELECT Statement and Its Clauses 175

Adding Data—The INSERT Statement 184

Changing Data—The UPDATE Statement 185

Deleting Data—The DELETE Statement 186

Summary 187

Questions 193

Answers 201

Chapter 7 Database Concurrency 204

What Is Data Consistency? 204

Transactions 205

Concurrency and Isolation Levels 207

Repeatable Read 208

Read Stability 209

Cursor Stability 209

Uncommitted Read 209

Specifying the Isolation Level 210

Choosing the Proper Isolation Level 210

Locking 210

Lock Attributes and Lock States 211

Lock Conversion 213

Lock Compatibility 213

Lock Escalation 215

Deadlocks 216

Lock Waits and Timeouts 217

How Locks Are Acquired 218

Factors That Affect Locking 220

Summary 221

Questions 225

Answers 227

PART Ⅲ DB2 UDB Administration(Tests 513) 229

Chapter 8 Server Management 231

Instances 231

Attaching to an Instance 232

Detaching from an Instance 233

The DB2 Administration Server (DAS) Instance 234

Distributed Connections 234

Configuring Communications 235

DB2 Discovery 239

Controlling Database Access—Revisited 244

Authentication 244

AWord about Users and Groups 246

Authorities 246

Privileges 250

Granting and Revoking Authorities and Privileges 255

Taking Control of a Server to Perform Maintenance Operations 258

Scheduling Maintenance with the Journal 260

Summary 261

Questions 267

Answers 269

Chapter 9 Data Placement 271

Creating a DB2 Universal Database Database—AReview 271

The DB2 Universal Database Storage Model 272

Buffer Pools 272

Table Spaces 274

Containers 275

Basic Buffer Pool and Table Space Requirements 276

Characteristics That Affect Table Space Performance 277

Page Size 277

Extent Size 278

Prefetch Size 280

Obtaining Information about Existing Table Spaces 280

Table Space States 283

Obtaining Information about Table Space Containers 284

Altering an Existing Table Space 285

Summary 285

Questions 289

Answers 292

Chapter 10 Data Access 293

Tables—A Review 293

Views 294

Types of Views 295

Imposing Restrictions on Views with the WITH CHECK OPTION clause 296

Indexes and Constraints 297

Indexes—A Review 297

Understanding Constraints 299

Unique(and Nonunique)Indexes 299

Referential Constraints Revisited 300

Examples of Referential Constraints 303

Improving Performance with a Clustering Index 306

System Catalog Tables and Views 307

Using the Script Center 310

An Alternative Method for Viewing Information Stored in Tables and Views 312

Reverse-Engineering a Database with DB2LOOK 313

Expanding a Database's Boundaries with Federated Systems 315

Summary 317

Questions 321

Answers 325

Chapter 11 Monitoring Database Activity 327

The Database System Monitor 327

The Snapshot Monitor 328

Event Monitors 333

The Performance Monitor 344

Starting,Stopping,and Viewing the Performance Monitor 344

Sending Messages to the Journal 345

A Word about the Query Patroller Tracker 346

Monitoring SQL Behavior 346

The Explain Facility 347

Configuring Database and Database Manager Resources 355

Viewing the DB2 Database Manager's Configuration 355

Viewing a Database's Configuration 358

Changing the Value of a DB2 Database Manager Configuration Parameter 360

Changing the Value of a Database's Configuration Parameter 368

Changing the Value of a DB2 Registry or Environment Variable 375

Summary 376

Questions 383

Answers 385

Chapter 12 DB2 Universal Database's Data Movement and Data Management Utilities 386

DB2 Universal Database's Data Movement Utilities 386

Supported File Formats 387

Obtaining Columnar Data from External Files 389

The Export Utility 392

The Import Utility 396

The Load Utility 406

The DB2MOVE Utility 419

DB2 Universal Database's Data Maintenance Utilities 421

The Reorganize Check Utility 422

The Reorganize Table Utility 425

The Run Statistics Utility 427

Summary 430

Questions 434

Answers 436

Chapter 13 Database Recovery and Maintenance 438

Transaction Logging 438

Circular Logging 440

Archival Logging 440

Returning a Database to a Consistent State 441

Managing Database Recovery 444

Creating a Backup Image of a Database 445

The Recovery History File 447

Restoring a Database(or a Table Space)from a Backup Image 448

Roll-Forward Recovery 452

Rebuilding Invalid Indexes 456

Configuration Parameters That Affect Logging and Data Recovery 456

Summary 458

Questions 462

Answers 463

Chapter 14 Problem Determination 465

Common Types of Errors 465

Error Codes 466

Viewing Error Messages 467

First Failure Data Capture(FFDC)Information 468

The db2diag.log File 470

Interpreting db2diag.log Entry Headers 470

Interpreting SQLCA Data Structure Value Entries 472

Interpreting Hexadecimal Codes 476

Locating Failed Objects 477

An Example Scenario 478

Summary 480

Questions 482

Answers 483

Appendix A System Catalog Views 484

Appendix B Bibliography 550