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.

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