DB2 Essentials

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.

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