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