DB2 Essentials: Understanding DB2 in a Big Data World(IBM Press)

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

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.