IBM Db2 11.1 Certification Guide

IBM Db2 11.1 Certification Guide

by Mohankumar Saraswatipura, Robert Collins
Released June 2018
Publisher(s): Packt Publishing
ISBN: 9781788626910

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Mastering material for dealing with DBA certification exams

About This Book
  • Prepare yourself for the IBM C2090-600 certification exam
  • Cover over 50 Db2 procedures including database design, performance, and security
  • Work through over 150 Q&As to gain confidence on each topic
Who This Book Is For

The IBM Db2 11.1 Certification Guide is an excellent choice for database administrators, architects, and application developers who are keen to obtain certification in Db2. Basic understanding of Db2 is expected in order to get the most out of this guide.

What You Will Learn
  • Configure and manage Db2 servers, instances, and databases
  • Implement Db2 BLU Acceleration and a DB2 pureScale environment
  • Create, manage, and alter Db2 database objects
  • Use the partitioning capabilities available within Db2
  • Enforce constraint checking with the SET INTEGRITY command
  • Utilize the Db2 problem determination (db2pd) and dsmtop tools
  • Configure and manage HADR
  • Understand how to encrypt data in transit and at rest
In Detail

IBM Db2 is a relational database management system (RDBMS) that helps you store, analyze, and retrieve data efficiently. This comprehensive book is designed to help you master all aspects of IBM Db2 database administration and prepare you to take and pass IBM's Certification Exams C2090-600. Building on years of extensive experience, the authors take you through all areas covered by the test. The book delves deep into each certification topic: Db2 server management, physical design, business rules implementation, activity monitoring, utilities, high availability, and security. IBM Db2 11.1 Certification Guide provides you with more than 150 practice questions and answers, simulating real certification examination questions. Each chapter includes an extensive set of practice questions along with carefully explained answers.

This book will not just prepare you for the C2090-600 exam but also help you troubleshoot day-to-day database administration challenges.

Style and approach

A comprehensive certification preparation guide for the C2090-600 exam, covering all the topics in greater detail and with sample questions and answers at the end of each chapter.

Publisher resources

View/Submit Errata

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. IBM Db2 11.1 Certification Guide
  3. Packt Upsell
    1. Why subscribe?
    2. PacktPub.com
  4. Foreword
  5. Contributors
    1. About the authors
    2. About the reviewers
    3. Packt is searching for authors like you
  6. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the color images
      2. Conventions used
    4. Get in touch
      1. Reviews
  7. Introduction to the Db2 Certification Path
    1. Db2 11.1 Certification track
      1. Db2 10.5 Fundamentals for LUW–Exam C2090-615
      2. Db2 11.1 Fundamentals for LUW–Exam C2090-616
      3. Db2 11.1 DBA for LUW–Exam C2090-600
    2. Preparing for the C2090-600 certification exam
    3. Registering for the C2090-600 certification exam
    4. Summary
  8. Db2 Server Management
    1. Working with Db2 instances
      1. Creating an instance
      2. Dropping an instance
      3. Listing instances
      4. Displaying the Db2 service level
      5. Verifying the database's readiness for upgrading
      6. Upgrading a database instance
      7. Starting and stopping an instance
      8. Attaching and detaching to an instance
      9. Quiescing an instance
    2. Configuring the Db2 system environment
      1. Listing and modifying the global registry
      2. Aggregate registry variables
      3. Configuring Db2 instances and databases
        1. The Db2 database manager configuration
        2. The Db2 database configuration
    3. DB2 autonomic computing features
      1. Self-Tuning Memory Manager (STMM)
      2. Automatic storage
        1. Creating storage groups
        2. Altering storage groups
        3. Dropping storage groups
    4. A word about Db2 workload manager (WLM)
      1. The WLM objects
        1. Workloads
        2. Thresholds
        3. Work action and work class sets
        4. Histogram templates
    5. A word about IBM Data Server Manager (DSM)
    6. Summary
    7. Practice questions
    8. Solutions
  9. Physical Design
    1. Database planning
    2. Creating a Db2 database
      1. Enabling automatic storage
    3. Buffer pools
      1. Buffer pool planning
      2. Creating a buffer pool
      3. Altering a buffer pool
    4. Storage groups
      1. Storage group planning
      2. Altering storage groups
      3. Renaming a storage group
      4. Dropping a storage group
    5. Tablespaces
      1. Containers
      2. Tablespace planning
      3. Creating tablespaces
      4. Adding containers to automatic storage tablespaces
      5. Tablespace rebalance operation
      6. Reclaimable storage
      7. Converting DMS tablespaces to use automatic storage
    6. Smart disk storage management
    7. Creating a table
      1. Alter table
      2. Expression-based indexes
      3. Invalidation of database objects
      4. Revalidation of database objects
    8. Online table move
      1. Tracking the execution of online table move
      2. Online table move planning
      3. Online table move-running changes
    9. Copy schema procedure
    10. Range clustering and range partitioning tables
      1. Range clustered tables
      2. Range clustered table planning
      3. Range partitioned tables
      4. Listing the data partitions
      5. Detaching a data partition
      6. Attaching a data partition
      7. MDC
      8. MQT
      9. ITC
    11. Db2 BLU Acceleration
      1. Implementing BLU
      2. BLU compression and statistics
      3. BLU disk space management
      4. BLU platform management
      5. Anatomy of a columnar table
      6. Db2 V11.1 BLU advances
      7. db2convert
    12. Db2 pureScale
      1. Members
      2. Cluster-caching facility (CF)
      3. Shared disk storage
      4. Cluster services and interconnect
      5. Db2 v11.1 pureScale advances
        1. Availability
        2. Installation and upgrades
        3. Management and performance
        4. Features
      6. Self-tuning database memory manager (STMM) in pureScale
      7. Self-tuning CF memory in pureScale
        1. Db2_DATABASE_CF_MEMORY (DB2 registry variable)
        2. CF_DB_MEM_SZ (DB CFG in 4K pages)
        3. CF_GBP_SZ, CF_LOCK_SZ, CF_SCA_SZ (DB CFG in 4K pages)
    13. Data compression
      1. Static row compression
      2. Should the table be compressed?
      3. Getting information about the compression dictionary
      4. Building a row compression dictionary
      5. Index compression
      6. LOBs and XML compression
      7. Temporary tables and replication support
      8. Adaptive compression
    14. SQL compatibilities
    15. Compatibility features for Netezza
    16. Summary
    17. Practice questions
    18. Solutions
  10. Implementing Business Rules
    1. Business rules
      1. NOT NULL constraints
      2. DEFAULT constraints
      3. CHECK constraints
      4. UNIQUE constraints
      5. NOT ENFORCED informational constraints
      6. Referential integrity constraints
        1. The INSERT rule for referential constraints
        2. The update rule for referential constraints
        3. The delete rule for referential constraints
    2. The SET INTEGRITY statement
    3. Views with CHECK OPTION
    4. Creating and using triggers
    5. The system catalog views
    6. Summary
    7. Practice questions
    8. Answers
  11. Monitoring Db2 Activity
    1. Monitoring infrastructure
      1. Monitoring table functions
      2. Monitoring system information
      3. Monitoring activities
      4. Monitoring data objects
      5. Monitoring locks
      6. Monitoring system memory
      7. Monitoring the pureScale environment
      8. Additional monitoring table functions
      9. Monitoring table functions: a quick reference
    2. Administrative views for monitoring
      1. A word about the MONREPORT module
      2. Event monitors
    3. EXPLAIN facility tools
      1. A word about the EXPLAIN operators
    4. Db2 troubleshooting tools
      1. The db2pd command
      2. The dsmtop command
    5. Summary
    6. Practice questions
    7. Solutions
  12. Db2 Utilities
    1. Data movement file formats
    2. Data movement utilities
      1. The Db2 Export utility
      2. The Db2 Import utility
      3. The Db2 Load Utility
        1. Phases of a load operation
        2. Data load best practices for BLU Column-Organized Tables
        3. Monitoring a Load operation
      4. The DB2 Ingest Utility
        1. Monitoring an Ingest operation
    3. DB2 utilities comparison – Import vs. Ingest vs. Load
    4. A word about the CATALOG STORAGE ACCESS command
    5. Other DB2 data movement options
      1. The SYSPROC.ADMIN_MOVE_TABLE () procedure
      2. The SYSPROC.ADMIN_COPY_SCHEMA () procedure
      3. Object Copy using the db2move command
    6. A word about the db2look tool command
    7. Database maintenance utilities
      1. The REORGCHK utility
      2. The REORG Utility
      3. The RUNSTATS Utility
      4. The REBIND Utility
      5. Flushing the Package Cache Utility
    8. The ADMIN_CMD () procedure
    9. Summary
    10. Practice questions
    11. Answers
  13. High Availability
    1. Transactions and logging parameters
    2. Transaction logging strategies
    3. Database recovery methods
    4. Backup and recovery
      1. The Db2 backup utility
        1. Incremental and delta backups
        2. A word about hardware accelerated backup
      2. The Db2 restore utility
      3. The Db2 roll-forward utility
      4. The Db2 recover utility
    5. A word about the db2relocatedb command
    6. High availability and disaster recovery
      1. HADR synchronization modes
      2. HADR-specific parameters
      3. Setting up an HADR multiple standby environment
      4. A word about the Db2 HA instance configuration utility (db2haicu)
        1. Activating the RoS feature
    7. Db2 pureScale architecture
    8. Geographically dispersed Db2 pureScale db2cluster (GDPC)
    9. Db2 pureScale db2cluster service command options
    10. Explicit Hierarchical Locking 
    11. Summary
    12. Practice questions
    13. Answers
  14. Db2 Security
    1. Authentication and authorization
    2. Authorities and privileges
      1. Authorities
        1. The system administrative authority (SYSADM) 
        2. The system control authority (SYSCTRL)
        3. The system maintenance authority (SYSMAINT)
        4. The system monitor authority (SYSMON)
        5. The database administrator authority (DBADM)
        6. The security administrator authority (SECADM)
        7. The SQL administrator (SQLADM) authority
        8. The workload management administrator authority (WLMADM)
        9. The Explain administrator authority (EXPLAIN)
        10. The Access control administrator authority (ACCESSCTRL)
        11. The data access administrator authority (DATAACCESS)
        12. The data load (LOAD) authority
      2. Privileges
    3. LDAP-based authentication
    4. Role-based access control
    5. Trusted context and trusted connections
    6. Row and column access control (RCAC)
      1. Row permissions
      2. Column masks
    7. The Db2 audit facility
      1. Audit policies
      2. The db2audit tool command
    8. Secure Socket Layer (SSL) implementation in Db2
    9. Db2 native encryption
    10. Summary
    11. Practice questions
    12. Answers
  15. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: IBM Db2 11.1 Certification Guide
  • Author(s): Mohankumar Saraswatipura, Robert Collins
  • Release date: June 2018
  • Publisher(s): Packt Publishing
  • ISBN: 9781788626910