DB2 Essentials
Understanding DB2 in a Big Data World
3rd Edition
Published on 14. November 2013
Book
Hardback
784 pages
978-0-13-346190-9 (ISBN)
Description
The Easy, Visual Introduction to IBM DB2 Version 10.5 for Linux, UNIX, and Windows
Foreword by Judy Huber, Vice President, Distributed Data Servers and Data Warehousing; Director, IBM Canada Laboratory
This book covers everything you need to get productive with the latest version of IBM DB2 and apply it to today's business challenges. It discusses key features introduced in DB2 Versions 10.5, 10.1, and 9.7, including improvements in manageability, integration, security, Big Data support, BLU Acceleration, and cloud computing.
DB2 Essentials illuminates key concepts with examples drawn from the authors' extensive experience with DB2 in enterprise environments. Raul F. Chong and Clara Liu explain how DB2 has evolved, what's new, and how to choose the right products, editions, and tools. Next, they walk through installation, configuration, security, data access, remote connectivity, and day-to-day administration.
Each chapter starts with an illustrative overview to introduce its key concepts using a big picture approach. Clearly explained figures are used extensively, and techniques are presented with intuitive screenshots, diagrams, charts, and tables. Case studies illustrate how "theory" is applied in real-life environments, and hundreds of review questions help you prepare for IBM's newest DB2 certification exams.
Coverage includes
* Understanding the role of DB2 in Big Data
* Preparing for and executing a smooth installation or upgrade
* Understanding the DB2 environment, instances, and databases
* Configuring client and server connectivity
* Working with database objects
* Getting started with BLU Acceleration
* Implementing security: authentication and authorization
* Understanding concurrency and locking
* Maintaining, backing up, and recovering data
* Using basic SQL in DB2 environments
* Diagnosing and solving DB2 problems
This book is for anyone who plans to work with DB2, including DBAs, system administrators, developers, and consultants. It will be a great resource whether you're upgrading from an older version of DB2, migrating from a competitive database, or learning your first database platform.
Foreword by Judy Huber, Vice President, Distributed Data Servers and Data Warehousing; Director, IBM Canada Laboratory
This book covers everything you need to get productive with the latest version of IBM DB2 and apply it to today's business challenges. It discusses key features introduced in DB2 Versions 10.5, 10.1, and 9.7, including improvements in manageability, integration, security, Big Data support, BLU Acceleration, and cloud computing.
DB2 Essentials illuminates key concepts with examples drawn from the authors' extensive experience with DB2 in enterprise environments. Raul F. Chong and Clara Liu explain how DB2 has evolved, what's new, and how to choose the right products, editions, and tools. Next, they walk through installation, configuration, security, data access, remote connectivity, and day-to-day administration.
Each chapter starts with an illustrative overview to introduce its key concepts using a big picture approach. Clearly explained figures are used extensively, and techniques are presented with intuitive screenshots, diagrams, charts, and tables. Case studies illustrate how "theory" is applied in real-life environments, and hundreds of review questions help you prepare for IBM's newest DB2 certification exams.
Coverage includes
* Understanding the role of DB2 in Big Data
* Preparing for and executing a smooth installation or upgrade
* Understanding the DB2 environment, instances, and databases
* Configuring client and server connectivity
* Working with database objects
* Getting started with BLU Acceleration
* Implementing security: authentication and authorization
* Understanding concurrency and locking
* Maintaining, backing up, and recovering data
* Using basic SQL in DB2 environments
* Diagnosing and solving DB2 problems
This book is for anyone who plans to work with DB2, including DBAs, system administrators, developers, and consultants. It will be a great resource whether you're upgrading from an older version of DB2, migrating from a competitive database, or learning your first database platform.
More details
Other editions
Previous edition
Raul F. Chong | Xiaomei Wang | Michael Dang
Understanding DB2 (paperback)
Learning Visually with Examples
Book
02/2012
2nd Edition
IBM Press
€55.70
Article exhausted; check for reprint
Persons
Raul F. Chong is a Senior DB2, Big Data and Cloud Program Manager and Technical Evangelist based at the IBM Canada Laboratory. He leads the development and design of several offerings for the Information Management (IM) brand of IBM, with the goal of increasing awareness and growing communities around IBM IM products, such as IBM InfoSphere BigInsights, IBM InfoSphere Streams, DB2 database software, IBM Data Studio, InfoSphere Data Architect, and pureQuery technology. As part of the IM Cloud Computing Center of Competence at the Toronto Lab, Raul leads the development and deployment of projects by the community using DB2 on the Cloud, such as bigdatauniversity.com and db2oncampus.org. As a technical evangelist, Raul travels worldwide delivering presentations and workshops targeting customers, IBM business partners, and the Academia. Raul develops and leads the development of collateral material such as articles, books, videos, courses, and DVDs that help educate users in IBM IM products. He has also participated actively in the development of training material and offerings of IBM IM Certification programs. Raul joined IBM in 1997 and has worked as a DB2 consultant, DB2 technical support specialist, and DB2 Information Developer. Raul has summarized many of his DB2 experiences through the years in the first and second editions of the book Understanding DB2-Learning Visually with Examples for which he is the lead author. He has also co-authored the book DB2 SQL PL Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS (ISBN 0131477005), and other books that are part of the DB2 on Campus book series. In his spare time, Raul enjoys playing with his two little daughters. Raul is fluent in Spanish as he was born and raised in Peru, but he keeps some of the Chinese traditions from his grandparents. He also enjoys reading history and archeology books.
Clara Liu was recently appointed to be the Program Manager of IBM Cross Brand Technical Initiatives. She manages leading edge strategic projects across IBM brands. Her prior management role was with the DB2 Planning team. Her previous consulting experience gave her an insight and solid understanding of customers' needs. Based on market demand, competition pressure, and objectives of maximizing return of investment, she drives software enhancements into the product with the right balance between leading edge technology and business needs. In many DB2, Warehouse, PureData Systems versions and releases, Clara held key responsibilities such as planning product enhancements, managing product offer portfolio and license entitlement, making software available on fulfillment systems for customers, and planning for smooth migration paths for customers when products reach end of life. Over the years, she delivered many product hands-on and video demonstrations with her team. Those are great assets for the IBM Sales and Marketing team and customers who want to 'see' and 'play' with the technologies. Due to the board involvement in numerous phases of the database product life cycle, Clara has established great networking within the development organization as well as across the business teams. As a mother of two, Clara had coauthored five books (including this one), all focusing in her technical expertise, DB2 for Linux, UNIX, and Windows.
Clara Liu was recently appointed to be the Program Manager of IBM Cross Brand Technical Initiatives. She manages leading edge strategic projects across IBM brands. Her prior management role was with the DB2 Planning team. Her previous consulting experience gave her an insight and solid understanding of customers' needs. Based on market demand, competition pressure, and objectives of maximizing return of investment, she drives software enhancements into the product with the right balance between leading edge technology and business needs. In many DB2, Warehouse, PureData Systems versions and releases, Clara held key responsibilities such as planning product enhancements, managing product offer portfolio and license entitlement, making software available on fulfillment systems for customers, and planning for smooth migration paths for customers when products reach end of life. Over the years, she delivered many product hands-on and video demonstrations with her team. Those are great assets for the IBM Sales and Marketing team and customers who want to 'see' and 'play' with the technologies. Due to the board involvement in numerous phases of the database product life cycle, Clara has established great networking within the development organization as well as across the business teams. As a mother of two, Clara had coauthored five books (including this one), all focusing in her technical expertise, DB2 for Linux, UNIX, and Windows.
Content
Foreword xxiv
Chapter 1 Introduction to DB2
A Brief History of DB2: From Past to Present 1
The Role of DB2 in the Big Data World 3
Characteristics of Big Data 4
Types of Big Data 5
The IBM Big Data Platform 6
Integration of DB2 with BigInsights (Hadoop) 11
DB2 Editions 13
DB2 Express-C 15
DB2 Express Server Edition 16
DB2 Workgroup Server Edition 16
DB2 Enterprise Server Edition 16
DB2 Advanced Workgroup Server Edition 17
DB2 Advanced Enterprise Server Edition 17
DB2 Developer Edition 18
DB2 Advanced Recovery Feature 18
IBM Data Server Clients and Driver Packages 19
Mainframe Host Connectivity 20
Database Federation Support 21
Database Replication Support 21
DB2 Syntax Diagram Conventions 23
Case Study 26
Summary 28
Review Questions 28
Chapter 2 DB2 at a Glance: The Big Picture 31
SQL Statements, XQuery Statements, and DB2 Commands 32
SQL Statements 33
XQuery Statements 34
DB2 System Commands 36
DB2 Command Line Processor (CLP) Commands 36
DB2 Command Line Processor Plus (CLPPlus) Commands 37
DB2 Text Search Commands 37
Database Tools and Utilities 38
Command-Line Tools 39
IBM Data Studio 39
Design, Configuration, Tuning, and Monitoring Tools 42
Setup Tools 42
Information Tools 43
The DB2 Environment 43
An Instance 44
The Database Administration Server 45
DB2 Profile Registries and DB2 Configuration Files 45
Connectivity and DB2 Directories 49
Databases 51
Table Spaces 52
Tables, Indexes, and Large Objects 53
Database Transaction Logs 53
Buffer Pools 53
Storage Groups 53
Directory Structure of Your DB2 Environment 53
Database Partitioning 57
Database Partitions 58
The Node Configuration File 62
An Instance in the DB2 Database Partitioning Environment 64
Partitioning a Database 65
The Catalog Partition 67
Partition Groups 67
Buffer Pools in a DB2 Database Partitioning Environment 68
Table Spaces in a Partitioned Database Environment 69
The Coordinator Partition 69
Issuing Commands and SQL Statements in a Database Partitioned Environment 69
The DB2NODE Environment Variable 70
Distribution Maps and Distribution Keys 71
DB2 pureScale 72
DB2 pureScale Architecture Overview 73
Cluster Interconnect 74
Cluster Caching Facility (CF) 75
DB2 Cluster Services (CS) 75
Cluster File System 75
DB2 pureScale Instance 76
Database Federation 78
Use of Uppercase Versus Lowercase in DB2 80
Case Study 83
Summary 85
Review Questions 85
Chapter 3 Installing DB2 89
DB2 Installation: The Big Picture 90
DB2 Installation System Requirements 94
Installing DB2 Using the DB2 Setup Wizard 95
Launch the DB2 Setup Wizard on Windows 96
Launch the DB2 Setup Wizard on Linux and UNIX 97
Generate a Response File 97
Select Features to Be Installed 98
Specify a Unique DB2 Copy Name 99
Set User Information for the DB2 Administration Server 99
Create and Configure the DB2 Instance 99
Enable Operating System Security for DB2 Objects (Windows Only) 100
Review Installation Settings and Start the Installation 101
Root and Non-Root Installation on Linux and UNIX 101
Limitations of Non-Root Installations 102
Installing DB2 with a Non-Root User 103
Enabling Some Root-Based Features in Non-Root Installations 103
Required User IDs and Groups 105
User IDs and Groups Required for Windows 105
User IDs and Groups Required for Linux and UNIX 106
Silent Install Using a Response File 107
Creating a Response File Using the DB2 Setup Wizard 109
Installing DB2 Using a Response File on Windows 112
Installing DB2 Using a Response File on Linux and UNIX 112
Advanced DB2 Installation Methods (Linux and UNIX Only) 113
Installing DB2 Using the db2_install Script 113
Manually Installing the DB2 Payload Files 115
Installing a DB2 License 116
Reducing DB2 Product Installation Image Size 118
Customizing DB2 Installation Images 118
Installing DB2 Using a Pruned Installation Image 120
Installing Multiple DB2 Versions and Fix Packs on the Same Server 121
Coexistence of Multiple DB2 Versions and Fix Packs (Windows) 121
Coexistence of Multiple DB2 Versions and Fix Packs (Linux and UNIX) 124
The db2ls Command (Linux and UNIX) 124
DB2 Administrative Server (DAS) and Multiple DB2 Copies 126
Installing DB2 Fix Packs 126
Applying Fix Packs to a Non-Root Installation 128
Upgrading to the Latest DB2 Version 128
Case Study 1 128
Case Study 2 130
Summary 132
Review Questions 132
Chapter 4 Using Database Tools and Utilities 137
Database Tools: The Big Picture 137
The Command-Line Tools 138
The DB2 Command Window 139
The DB2 Command Line Processor 141
The DB2 Command Line Processor Plus 154
IBM Data Studio 160
IBM Data Studio Workspace and the Task Launcher 161
Connection Profiles 162
General Database Administration Tools 164
General Database Development Tools 171
IBM Data Studio Web Console 174
Set-Up Tools 175
Configure DB2 .NET Data Provider 176
First Steps 176
Default DB2 and Database Client Interface Selection Wizard 177
The Replication Center 178
Information Tools 178
DB2 Information Center 179
Checking for DB2 Updates 180
Problem Determination Tools 180
The db2pd Tool 181
Case Study 1 181
Case Study 2 182
Summary 183
Review Questions 184
Chapter 5 Understanding the DB2 Environment, DB2 Instances, and Databases 187
The DB2 Environment, DB2 Instances, and Databases: The Big Picture 187
The DB2 Environment 188
The DB2 Instance 198
Creating DB2 Instances 200
Creating Client Instances 201
Creating DB2 Instances in a pureScale Environment 202
Dropping an Instance 202
Listing the Instances in Your System 203
Using the DB2INSTANCE Environment Variable 204
Starting a DB2 Instance 204
Stopping a DB2 Instance 207
Attaching to an Instance 208
Configuring an Instance 209
Working with an Instance from IBM Data Studio 214
Using the DB2 Commands at the Instance Level 216
The Database Administration Server (DAS) 216
Using the DAS Commands 217
Configuring a Database 217
Configuring a Database from IBM Data Studio 223
Using the DB2 Commands at the Database Level 226
The Configuration Advisor 226
Design Considerations for Instances and Databases 228
Case Study 229
Summary 231
Review Questions 232
Chapter 6 Configuring Client and Server Connectivity 235
Client and Server Connectivity: The Big Picture 235
The DB2 Database Directories 237
The DB2 Database Directories: An Analogy Using a Book 238
The System Database Directory 239
The Local Database Directory 241
The Node Directory 242
The Database Connection Services Directory 244
The Relationship Between the DB2 Directories 245
Supported Connectivity Scenarios 249
Scenario 1: Local Connection from a Data Server Client to a DB2 Server 249
Scenario 2: Remote Connection from a Data Server Client to a DB2 Server 251
Scenario 3: Remote Connection from a Data Server Client to a DB2 Host Server 258
Scenario 4: Remote Connection from a Data Server Client to a DB2 Host Server via a DB2 Connect Gateway 262
Scenario 5: Remote Connection from an Application to a DB2 Server 264
DB2 Packages and the Bind Process 265
Automatic Client Reroute Feature 267
Application Connection Timeout Support 268
TCP/IP Keepalive Timeout Support 269
Diagnosing DB2 Connectivity Problems 269
Diagnosing Client-Server TCP/IP Connection Problems 270
Case Study 278
Step 1: Configure the DB2 Connect Gateway Machine 278
Step 2: Test the Connection from the DB2 Connect Gateway Machine to the Host 278
Step 3: Enable the TCP/IP Listener on the Gateway Machine 279
Step 4: Configure a Data Server Client to Connect to the Host via the Gateway 279
Summary 280
Review Questions 281
Chapter 7 Working with Database Objects 285
Database Objects: The Big Picture 285
Databases 290
Database Partitions 290
Automatic Storage 296
Creating a Database 297
Default Database Objects Created 299
Listing Databases 300
Dropping Databases 300
Database Creation Examples 300
The SAMPLE Database 304
Partition Groups 305
Database Partition Group Classifications 305
Default Partition Groups 306
Creating Database Partition Groups 307
Modifying a Database Partition Group 308
Listing Database Partition Groups 308
Dropping a Database Partition Group 310
Table Spaces 310
Table Space Classification 310
Default Table Spaces 311
Containers 312
Storage Groups 312
Pages 315
Extents 315
Creating Table Spaces 317
SMS Table Spaces 318
DMS Table Spaces 320
Automatic Storage Managed Table Spaces 322
Comparing SMS, DMS, and Automatic Storage Table Spaces 323
Listing Table Spaces 324
Altering a Table Space 325
Dropping a Table Space 325
Buffer Pools 326
Creating Buffer Pools 326
Altering Buffer Pools 329
Dropping Buffer Pools 330
Schemas 330
Data Types 332
DB2 Built-in Data Types 332
User-Defined Types (UDTs) 337
Choosing the Proper Data Type 338
Tables 339
Table Classification 339
System Catalog Tables 340
User Tables 341
Default Values 344
Using NULL Values 346
Identity Columns 347
Constraints 350
Not Logged Initially Tables 362
Partitioned Tables 363
Row Compression 366
Table Compression 369
Materialized Query Tables and Summary Tables 370
Temporary Tables 370
Temporal Tables and Time Travel Query 372
Indexes 379
Working with Indexes 379
Clustering Indexes 382
Multidimensional Clustering (MDC) Tables and Block Indexes 383
MDC Tables 384
Block Indexes 385
The Block Map 387
Choosing Dimensions for MDC Tables 388
Combining Database Partitioning, Table Partitioning, and MDC 388
Views 389
View Classification 391
Using the WITH CHECK OPTION 394
Nested Views 395
Packages 395
Triggers 396
Stored Procedures 397
User-Defined Functions 400
Sequences 401
Modules 403
Case Study 1 404
Case Study 2 407
Summary 408
Review Questions 409
Chapter 8 Implementing Security 415
DB2 Security Model: The Big Picture 415
Authentication Methods 417
Configuring the Authentication Type at a DB2 Server 417
Configuring the Authentication Type at a DB2 Client 419
Authenticating Users at the DB2 Server 421
Authenticating Users Using the Kerberos Security Service 423
Authenticating Users with Generic Security Service Plug-ins 424
Authenticating Users at the Data Server Client 427
Administrative Authorities 431
Managing Administrative Authorities 433
Database Object Privileges 438
Schema Privileges 438
Table Space Privileges 440
Table and View Privileges 441
Index Privileges 444
Package Privileges 445
Routine Privileges 446
Sequence Privileges 448
Security Label Privileges 449
SET SESSION AUTHORIZATION Statement and SETSESSIONUSER Privilege 450
Implicit Privileges 452
Roles and Privileges 453
TRANSFER OWNERSHIP Statement 456
Data Encryption 456
Label-Based Access Control (LBAC) 458
Views and LBAC 462
Implementing an LBAC Security Solution 462
LBAC in Action 465
Column Level Security and Referential Integrity 466
Row and Column Access Control (RCAC) 467
Built-In SQL Functions and Session Variables Supporting RCAC 468
Creating Row Permissions 469
Creating Column Masks 471
Enforcing Row Permissions and Column Masks 472
Behavior of INSERT, DELETE, and UPDATE Under RCAC 473
Implementing a RCAC Security Solution 473
RCAC in Action 475
Extending the Case Scenario 476
Benefits of Using RCAC 478
Trusted Contexts 479
Windows Security Considerations 481
Windows Domain Considerations 481
Windows Extended Security 483
Authority and Privilege Metadata 484
Case Study 486
Working with Authorities and Privileges 486
Working with Data Encryption, Ownership Transfer, and Roles 491
Working with RCAC 492
Summary 493
Review Questions 494
Chapter 9 Understanding Concurrency and Locking 499
DB2 Locking and Concurrency: The Big Picture 500
Concurrency and Locking Scenarios 500
Lost Updates 501
Uncommitted Reads 502
Nonrepeatable Reads 503
Phantom Reads 504
DB2 Isolation Levels 504
Uncommitted Reads 504
Cursor Stability 505
Read Stability 510
Repeatable Reads 511
Changing Isolation Levels 512
Using the DB2 Command Window 512
Using the DB2 precompile and bind Commands 514
Using the DB2 Call Level Interface 514
Using the Application Programming Interface 516
Working with Statement Level Isolation Level 516
DB2 Locking 517
Lock Attributes 518
Lock Waits 524
Deadlocks 526
Lock Deferral 527
Lock Escalation 528
Diagnosing Lock Problems 529
Using the list applications Command 529
Using the force application Command 531
Using the Snapshot Monitor 532
Using Snapshot Table Functions 536
Using the Event Monitor 536
Techniques to Avoid Locking 536
Case Study 538
Exercises 539
Setup 539
Part 1: Testing Isolation CS Without CC 542
Part 2: Different Access Paths, Different Locking 543
Part 3: Simulating a Deadlock Situation 544
Part 4: Testing Isolation CS with CC 546
Part 5: Testing Isolation UR 547
Summary 547
Review Questions 548
Chapter 10 Maintaining, Backing Up, and Recovering Data 553
DB2 Data Movement Utilities: The Big Picture 553
Data Movement File Formats 555
The DB2 EXPORT Utility 557
The DB2 IMPORT Utility 559
The DB2 Load Utility 562
The Ingest Utility 573
The db2move Utility 577
Generating Data Definition Language 579
DB2 Maintenance Utilities: The Big Picture 580
The RUNSTATS Utility 580
The REORG and REORGCHK Utilities 582
The REBIND Utility and the FLUSH PACKAGE CACHE Command 584
Automatic Database Maintenance 585
Database Backup, Recovery, and Roll Forward Concepts: The Big Picture 585
Recovery Scenarios and Strategies 586
Unit of Work (Transaction) 587
Types of Recovery 588
DB2 Transaction Logs 589
Logging Methods 596
Handling the DB2 Transaction Logs 601
Recovery Terminology 602
Performing Database and Table Space Backups 602
The Backup Files 607
Performing Database and Table Space Recovery 608
Database and Table Space Roll Forward 614
The Recovery History File 618
Database Recovery Using RECOVER DATABASE 620
Case Study 621
Summary 623
Review Questions 625
Appendix A Solutions to the Review Questions 629
Appendix B Introduction to SQL 645
Querying DB2 Data 646
Derived Columns 646
The SELECT Statement with COUNT Aggregate Function 648
The SELECT Statement with DISTINCT Clause 648
DB2 Special Registers 649
Scalar and Column Functions 651
The CAST Expression 652
The FROM Clause 653
The WHERE Clause 653
Using FETCH FIRST n ROWS ONLY 653
The LIKE Predicate 654
The BETWEEN Predicate 655
The IN Predicate 655
The ORDER BY Clause 656
The GROUP BY...HAVING Clause 657
Joins 657
Working with NULLs 660
The CASE Expression 661
Adding a Row Number to the Result Set 662
Modifying Table Data 663
Selecting from UPDATE, DELETE, or INSERT 664
The MERGE Statement 666
The UNION, INTERSECT, and EXCEPT Operators 668
The UNION and UNION ALL Operators 668
The INTERSECT and INTERSECT ALL Operators 670
The EXCEPT and EXCEPT ALL Operators 670
Recursive SQL Statements 671
Appendix C A Comparison of DB2 and Oracle Terminology 675
Product and Functionality Mapping 675
Terminology Mapping 677
DB2 Compatibility Features 680
Data Types, SQL, and Packages Support in DB2 680
PL/SQL Support in DB2 681
Concurrency Control 681
IBM Database Conversion Workbench 681
Appendix D Diagnosing Problems 683
Problem Diagnosis: The Big Picture 683
The Help (?) Command 684
DB2 First Occurrence Data Capture (FODC) 686
Administration Notification Log 686
db2diag.log 686
Trap Files 686
Dump Files 687
Core Files (Linux/UNIX Only) 687
DB2 Instance Level Configuration Parameters Related to FODC 687
Administration Notification Log Examples 690
db2diag.log Example 690
Tools for Troubleshooting 692
DB2VAL 692
DB2DIAG 692
The db2support Tool 692
The DB2 Trace Facility 693
The db2dart Tool 694
The INSPECT Tool 695
DB2COS 695
DB2PDCFG 697
DB2FODC 697
Searching for Known Problems 699
Appendix E Resources 701
Index 707
Chapter 1 Introduction to DB2
A Brief History of DB2: From Past to Present 1
The Role of DB2 in the Big Data World 3
Characteristics of Big Data 4
Types of Big Data 5
The IBM Big Data Platform 6
Integration of DB2 with BigInsights (Hadoop) 11
DB2 Editions 13
DB2 Express-C 15
DB2 Express Server Edition 16
DB2 Workgroup Server Edition 16
DB2 Enterprise Server Edition 16
DB2 Advanced Workgroup Server Edition 17
DB2 Advanced Enterprise Server Edition 17
DB2 Developer Edition 18
DB2 Advanced Recovery Feature 18
IBM Data Server Clients and Driver Packages 19
Mainframe Host Connectivity 20
Database Federation Support 21
Database Replication Support 21
DB2 Syntax Diagram Conventions 23
Case Study 26
Summary 28
Review Questions 28
Chapter 2 DB2 at a Glance: The Big Picture 31
SQL Statements, XQuery Statements, and DB2 Commands 32
SQL Statements 33
XQuery Statements 34
DB2 System Commands 36
DB2 Command Line Processor (CLP) Commands 36
DB2 Command Line Processor Plus (CLPPlus) Commands 37
DB2 Text Search Commands 37
Database Tools and Utilities 38
Command-Line Tools 39
IBM Data Studio 39
Design, Configuration, Tuning, and Monitoring Tools 42
Setup Tools 42
Information Tools 43
The DB2 Environment 43
An Instance 44
The Database Administration Server 45
DB2 Profile Registries and DB2 Configuration Files 45
Connectivity and DB2 Directories 49
Databases 51
Table Spaces 52
Tables, Indexes, and Large Objects 53
Database Transaction Logs 53
Buffer Pools 53
Storage Groups 53
Directory Structure of Your DB2 Environment 53
Database Partitioning 57
Database Partitions 58
The Node Configuration File 62
An Instance in the DB2 Database Partitioning Environment 64
Partitioning a Database 65
The Catalog Partition 67
Partition Groups 67
Buffer Pools in a DB2 Database Partitioning Environment 68
Table Spaces in a Partitioned Database Environment 69
The Coordinator Partition 69
Issuing Commands and SQL Statements in a Database Partitioned Environment 69
The DB2NODE Environment Variable 70
Distribution Maps and Distribution Keys 71
DB2 pureScale 72
DB2 pureScale Architecture Overview 73
Cluster Interconnect 74
Cluster Caching Facility (CF) 75
DB2 Cluster Services (CS) 75
Cluster File System 75
DB2 pureScale Instance 76
Database Federation 78
Use of Uppercase Versus Lowercase in DB2 80
Case Study 83
Summary 85
Review Questions 85
Chapter 3 Installing DB2 89
DB2 Installation: The Big Picture 90
DB2 Installation System Requirements 94
Installing DB2 Using the DB2 Setup Wizard 95
Launch the DB2 Setup Wizard on Windows 96
Launch the DB2 Setup Wizard on Linux and UNIX 97
Generate a Response File 97
Select Features to Be Installed 98
Specify a Unique DB2 Copy Name 99
Set User Information for the DB2 Administration Server 99
Create and Configure the DB2 Instance 99
Enable Operating System Security for DB2 Objects (Windows Only) 100
Review Installation Settings and Start the Installation 101
Root and Non-Root Installation on Linux and UNIX 101
Limitations of Non-Root Installations 102
Installing DB2 with a Non-Root User 103
Enabling Some Root-Based Features in Non-Root Installations 103
Required User IDs and Groups 105
User IDs and Groups Required for Windows 105
User IDs and Groups Required for Linux and UNIX 106
Silent Install Using a Response File 107
Creating a Response File Using the DB2 Setup Wizard 109
Installing DB2 Using a Response File on Windows 112
Installing DB2 Using a Response File on Linux and UNIX 112
Advanced DB2 Installation Methods (Linux and UNIX Only) 113
Installing DB2 Using the db2_install Script 113
Manually Installing the DB2 Payload Files 115
Installing a DB2 License 116
Reducing DB2 Product Installation Image Size 118
Customizing DB2 Installation Images 118
Installing DB2 Using a Pruned Installation Image 120
Installing Multiple DB2 Versions and Fix Packs on the Same Server 121
Coexistence of Multiple DB2 Versions and Fix Packs (Windows) 121
Coexistence of Multiple DB2 Versions and Fix Packs (Linux and UNIX) 124
The db2ls Command (Linux and UNIX) 124
DB2 Administrative Server (DAS) and Multiple DB2 Copies 126
Installing DB2 Fix Packs 126
Applying Fix Packs to a Non-Root Installation 128
Upgrading to the Latest DB2 Version 128
Case Study 1 128
Case Study 2 130
Summary 132
Review Questions 132
Chapter 4 Using Database Tools and Utilities 137
Database Tools: The Big Picture 137
The Command-Line Tools 138
The DB2 Command Window 139
The DB2 Command Line Processor 141
The DB2 Command Line Processor Plus 154
IBM Data Studio 160
IBM Data Studio Workspace and the Task Launcher 161
Connection Profiles 162
General Database Administration Tools 164
General Database Development Tools 171
IBM Data Studio Web Console 174
Set-Up Tools 175
Configure DB2 .NET Data Provider 176
First Steps 176
Default DB2 and Database Client Interface Selection Wizard 177
The Replication Center 178
Information Tools 178
DB2 Information Center 179
Checking for DB2 Updates 180
Problem Determination Tools 180
The db2pd Tool 181
Case Study 1 181
Case Study 2 182
Summary 183
Review Questions 184
Chapter 5 Understanding the DB2 Environment, DB2 Instances, and Databases 187
The DB2 Environment, DB2 Instances, and Databases: The Big Picture 187
The DB2 Environment 188
The DB2 Instance 198
Creating DB2 Instances 200
Creating Client Instances 201
Creating DB2 Instances in a pureScale Environment 202
Dropping an Instance 202
Listing the Instances in Your System 203
Using the DB2INSTANCE Environment Variable 204
Starting a DB2 Instance 204
Stopping a DB2 Instance 207
Attaching to an Instance 208
Configuring an Instance 209
Working with an Instance from IBM Data Studio 214
Using the DB2 Commands at the Instance Level 216
The Database Administration Server (DAS) 216
Using the DAS Commands 217
Configuring a Database 217
Configuring a Database from IBM Data Studio 223
Using the DB2 Commands at the Database Level 226
The Configuration Advisor 226
Design Considerations for Instances and Databases 228
Case Study 229
Summary 231
Review Questions 232
Chapter 6 Configuring Client and Server Connectivity 235
Client and Server Connectivity: The Big Picture 235
The DB2 Database Directories 237
The DB2 Database Directories: An Analogy Using a Book 238
The System Database Directory 239
The Local Database Directory 241
The Node Directory 242
The Database Connection Services Directory 244
The Relationship Between the DB2 Directories 245
Supported Connectivity Scenarios 249
Scenario 1: Local Connection from a Data Server Client to a DB2 Server 249
Scenario 2: Remote Connection from a Data Server Client to a DB2 Server 251
Scenario 3: Remote Connection from a Data Server Client to a DB2 Host Server 258
Scenario 4: Remote Connection from a Data Server Client to a DB2 Host Server via a DB2 Connect Gateway 262
Scenario 5: Remote Connection from an Application to a DB2 Server 264
DB2 Packages and the Bind Process 265
Automatic Client Reroute Feature 267
Application Connection Timeout Support 268
TCP/IP Keepalive Timeout Support 269
Diagnosing DB2 Connectivity Problems 269
Diagnosing Client-Server TCP/IP Connection Problems 270
Case Study 278
Step 1: Configure the DB2 Connect Gateway Machine 278
Step 2: Test the Connection from the DB2 Connect Gateway Machine to the Host 278
Step 3: Enable the TCP/IP Listener on the Gateway Machine 279
Step 4: Configure a Data Server Client to Connect to the Host via the Gateway 279
Summary 280
Review Questions 281
Chapter 7 Working with Database Objects 285
Database Objects: The Big Picture 285
Databases 290
Database Partitions 290
Automatic Storage 296
Creating a Database 297
Default Database Objects Created 299
Listing Databases 300
Dropping Databases 300
Database Creation Examples 300
The SAMPLE Database 304
Partition Groups 305
Database Partition Group Classifications 305
Default Partition Groups 306
Creating Database Partition Groups 307
Modifying a Database Partition Group 308
Listing Database Partition Groups 308
Dropping a Database Partition Group 310
Table Spaces 310
Table Space Classification 310
Default Table Spaces 311
Containers 312
Storage Groups 312
Pages 315
Extents 315
Creating Table Spaces 317
SMS Table Spaces 318
DMS Table Spaces 320
Automatic Storage Managed Table Spaces 322
Comparing SMS, DMS, and Automatic Storage Table Spaces 323
Listing Table Spaces 324
Altering a Table Space 325
Dropping a Table Space 325
Buffer Pools 326
Creating Buffer Pools 326
Altering Buffer Pools 329
Dropping Buffer Pools 330
Schemas 330
Data Types 332
DB2 Built-in Data Types 332
User-Defined Types (UDTs) 337
Choosing the Proper Data Type 338
Tables 339
Table Classification 339
System Catalog Tables 340
User Tables 341
Default Values 344
Using NULL Values 346
Identity Columns 347
Constraints 350
Not Logged Initially Tables 362
Partitioned Tables 363
Row Compression 366
Table Compression 369
Materialized Query Tables and Summary Tables 370
Temporary Tables 370
Temporal Tables and Time Travel Query 372
Indexes 379
Working with Indexes 379
Clustering Indexes 382
Multidimensional Clustering (MDC) Tables and Block Indexes 383
MDC Tables 384
Block Indexes 385
The Block Map 387
Choosing Dimensions for MDC Tables 388
Combining Database Partitioning, Table Partitioning, and MDC 388
Views 389
View Classification 391
Using the WITH CHECK OPTION 394
Nested Views 395
Packages 395
Triggers 396
Stored Procedures 397
User-Defined Functions 400
Sequences 401
Modules 403
Case Study 1 404
Case Study 2 407
Summary 408
Review Questions 409
Chapter 8 Implementing Security 415
DB2 Security Model: The Big Picture 415
Authentication Methods 417
Configuring the Authentication Type at a DB2 Server 417
Configuring the Authentication Type at a DB2 Client 419
Authenticating Users at the DB2 Server 421
Authenticating Users Using the Kerberos Security Service 423
Authenticating Users with Generic Security Service Plug-ins 424
Authenticating Users at the Data Server Client 427
Administrative Authorities 431
Managing Administrative Authorities 433
Database Object Privileges 438
Schema Privileges 438
Table Space Privileges 440
Table and View Privileges 441
Index Privileges 444
Package Privileges 445
Routine Privileges 446
Sequence Privileges 448
Security Label Privileges 449
SET SESSION AUTHORIZATION Statement and SETSESSIONUSER Privilege 450
Implicit Privileges 452
Roles and Privileges 453
TRANSFER OWNERSHIP Statement 456
Data Encryption 456
Label-Based Access Control (LBAC) 458
Views and LBAC 462
Implementing an LBAC Security Solution 462
LBAC in Action 465
Column Level Security and Referential Integrity 466
Row and Column Access Control (RCAC) 467
Built-In SQL Functions and Session Variables Supporting RCAC 468
Creating Row Permissions 469
Creating Column Masks 471
Enforcing Row Permissions and Column Masks 472
Behavior of INSERT, DELETE, and UPDATE Under RCAC 473
Implementing a RCAC Security Solution 473
RCAC in Action 475
Extending the Case Scenario 476
Benefits of Using RCAC 478
Trusted Contexts 479
Windows Security Considerations 481
Windows Domain Considerations 481
Windows Extended Security 483
Authority and Privilege Metadata 484
Case Study 486
Working with Authorities and Privileges 486
Working with Data Encryption, Ownership Transfer, and Roles 491
Working with RCAC 492
Summary 493
Review Questions 494
Chapter 9 Understanding Concurrency and Locking 499
DB2 Locking and Concurrency: The Big Picture 500
Concurrency and Locking Scenarios 500
Lost Updates 501
Uncommitted Reads 502
Nonrepeatable Reads 503
Phantom Reads 504
DB2 Isolation Levels 504
Uncommitted Reads 504
Cursor Stability 505
Read Stability 510
Repeatable Reads 511
Changing Isolation Levels 512
Using the DB2 Command Window 512
Using the DB2 precompile and bind Commands 514
Using the DB2 Call Level Interface 514
Using the Application Programming Interface 516
Working with Statement Level Isolation Level 516
DB2 Locking 517
Lock Attributes 518
Lock Waits 524
Deadlocks 526
Lock Deferral 527
Lock Escalation 528
Diagnosing Lock Problems 529
Using the list applications Command 529
Using the force application Command 531
Using the Snapshot Monitor 532
Using Snapshot Table Functions 536
Using the Event Monitor 536
Techniques to Avoid Locking 536
Case Study 538
Exercises 539
Setup 539
Part 1: Testing Isolation CS Without CC 542
Part 2: Different Access Paths, Different Locking 543
Part 3: Simulating a Deadlock Situation 544
Part 4: Testing Isolation CS with CC 546
Part 5: Testing Isolation UR 547
Summary 547
Review Questions 548
Chapter 10 Maintaining, Backing Up, and Recovering Data 553
DB2 Data Movement Utilities: The Big Picture 553
Data Movement File Formats 555
The DB2 EXPORT Utility 557
The DB2 IMPORT Utility 559
The DB2 Load Utility 562
The Ingest Utility 573
The db2move Utility 577
Generating Data Definition Language 579
DB2 Maintenance Utilities: The Big Picture 580
The RUNSTATS Utility 580
The REORG and REORGCHK Utilities 582
The REBIND Utility and the FLUSH PACKAGE CACHE Command 584
Automatic Database Maintenance 585
Database Backup, Recovery, and Roll Forward Concepts: The Big Picture 585
Recovery Scenarios and Strategies 586
Unit of Work (Transaction) 587
Types of Recovery 588
DB2 Transaction Logs 589
Logging Methods 596
Handling the DB2 Transaction Logs 601
Recovery Terminology 602
Performing Database and Table Space Backups 602
The Backup Files 607
Performing Database and Table Space Recovery 608
Database and Table Space Roll Forward 614
The Recovery History File 618
Database Recovery Using RECOVER DATABASE 620
Case Study 621
Summary 623
Review Questions 625
Appendix A Solutions to the Review Questions 629
Appendix B Introduction to SQL 645
Querying DB2 Data 646
Derived Columns 646
The SELECT Statement with COUNT Aggregate Function 648
The SELECT Statement with DISTINCT Clause 648
DB2 Special Registers 649
Scalar and Column Functions 651
The CAST Expression 652
The FROM Clause 653
The WHERE Clause 653
Using FETCH FIRST n ROWS ONLY 653
The LIKE Predicate 654
The BETWEEN Predicate 655
The IN Predicate 655
The ORDER BY Clause 656
The GROUP BY...HAVING Clause 657
Joins 657
Working with NULLs 660
The CASE Expression 661
Adding a Row Number to the Result Set 662
Modifying Table Data 663
Selecting from UPDATE, DELETE, or INSERT 664
The MERGE Statement 666
The UNION, INTERSECT, and EXCEPT Operators 668
The UNION and UNION ALL Operators 668
The INTERSECT and INTERSECT ALL Operators 670
The EXCEPT and EXCEPT ALL Operators 670
Recursive SQL Statements 671
Appendix C A Comparison of DB2 and Oracle Terminology 675
Product and Functionality Mapping 675
Terminology Mapping 677
DB2 Compatibility Features 680
Data Types, SQL, and Packages Support in DB2 680
PL/SQL Support in DB2 681
Concurrency Control 681
IBM Database Conversion Workbench 681
Appendix D Diagnosing Problems 683
Problem Diagnosis: The Big Picture 683
The Help (?) Command 684
DB2 First Occurrence Data Capture (FODC) 686
Administration Notification Log 686
db2diag.log 686
Trap Files 686
Dump Files 687
Core Files (Linux/UNIX Only) 687
DB2 Instance Level Configuration Parameters Related to FODC 687
Administration Notification Log Examples 690
db2diag.log Example 690
Tools for Troubleshooting 692
DB2VAL 692
DB2DIAG 692
The db2support Tool 692
The DB2 Trace Facility 693
The db2dart Tool 694
The INSPECT Tool 695
DB2COS 695
DB2PDCFG 697
DB2FODC 697
Searching for Known Problems 699
Appendix E Resources 701
Index 707