DB2 Essentials: Understanding DB2 in a Big Data World

by Raul Chong, Clara Liu

Overview

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.

Product Details

ISBN-13:	9780133461923
Publisher:	Pearson Education
Publication date:	10/26/2013
Series:	IBM Press
Sold by:	Barnes & Noble
Format:	eBook
Pages:	784
File size:	70 MB
Note:	This product may take a few minutes to download.

About the Author

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.

Table of Contents

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