DB2 Essentials Understanding DB2 in a Big Data World 3rd Edition

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.

DB2 Essentials, 3E: Understanding DB2 in a Big Data World is the easiest way to master the newest versions of DB2 for Linux®, UNIX®, and Windows, and apply their full power to today’s business challenges. This highly visual guide introduces you to key concepts with dozens of examples drawn from the authors’ experience working with IBM DB2 in enterprise environments. It has been thoroughly updated to reflect major improvements in both DB2 Version 10 and Version 9.7, including innovations in manageability, integration, Big Data support, and cloud computing.

This easy tutorial is built around visual learning. Each chapter starts with a large picture to introduce its key concepts. Figures are used extensively, and are fully and clearly explained; each concept is presented with intuitive screenshots, diagrams, charts, and tables. Many examples and case studies illustrate how “theory” is applied in real-life scenarios and production environments. Hundreds of review questions help you prepare for IBM’s newest DB2 certification exams; solutions are provided in an appendix.

Building on the previous edition (Understanding DB2), DB2 Essentials is now the first of two volumes. “Essentials” covers everything DB2 newcomers need to get productive, including:

• A brief history of IBM DB2, new features in the current version, and available products, editions, and tools
• Installing DB2
• Creating instances, databases, and database objects
• Setting up security and choosing the right security options
• Managing data access (including introductions to SQL and data concurrency)
• Setting up remote connectivity
• Administering DB2 databases with the most useful DB2 utilities

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.

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