31,19 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Mastering material for dealing with DBA certification exams
Key FeaturesPrepare yourself for the IBM C2090-600 certification examCover over 50 Db2 procedures including database design, performance, and securityWork through over 150 Q&As to gain confidence on each topicBook Description
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.
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 restWho 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.
Mohankumar Saraswatipura is a database solutions architect focusing on IBM Db2, Linux, Unix, Windows, and SAP HANA solutions. He is an IBM Champion (2010-2018) and a DB2's Got Talent 2013 winner. He is also a frequent speaker at the DB2Night Show and IDUG North America conferences. He has written dozens of technical papers for IBM developerWorks, Data Magazine, and the DB2 10.1/10.5 certification guide. He holds a Master's of technology in computer science and an executive MBA from IIM Calcutta. Robert (Kent) Collins, the founder of Shiloh Consulting, is currently a database solutions architect at BNSF Railway. He is an IBM Champion (2010–2018) and a frequent speaker at the DB2Night Show, IDUG North America, and IBM Insight conferences. Kent has worked continually with Db2 since its introduction to the market in 1984, amassing a wealth of knowledge and experience. He graduated from University of Texas in Dallas with majors in mathematics and computer science.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 518
Veröffentlichungsjahr: 2018
Ähnliche
IBM Db2 11.1 Certification Guide
Copyright © 2018 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author(s), nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor:Amey VarangaonkarAcquisition Editor: Divya PoojariContent Development Editor: Eisha DsouzaTechnical Editor: Ishita VoraCopy Editors: Safis Editing, Vikrant PhadkayProject Coordinator: Shweta BirwatkarProofreader: Safis EditingIndexer: Priyanka DhadkeGraphics: Jisha ChirayilProduction Coordinator: Nilesh Mohite
First published: June 2018 Production reference: 1270618
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78862-691-0
www.packtpub.com
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
PacktPub.com
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Foreword
It is my pleasure and honor to introduce this book’s authors, Mohankumar “Mohan” Saraswatipura and Robert “Kent” Collins. Since the late 1990s, my work and life’s mission has been to help grow, nurture, and support the community around IBM® Db2® databases. Through presenting at the International Db2 User Group, helping worldwide Db2 customers and clients, and producing the ever-popular edutainment webinar series The Db2Night Show™, I have come to know the best of the best in the Db2 industry. You are about to learn from, and enjoy, an excellent book written by two of the brightest minds in the Db2 world.
I first noticed Mohan in 2013 when he participated in “Db2’s GOT TALENT” on The Db2Night Show. His presentations were brilliant, polished, professional, and helpful, and ultimately he won first place in this worldwide competition. I met him in person at IDUG’s 25th Anniversary in Orlando later that year and was further impressed by his professionalism, articulate communication, and enthusiastic and polite demeanor. In recent years, Mohan also authored a DB2 9.7 Advanced Application Developer Cookbook, co-authored the DB2 10.1 and 10.5 Administration Certification Study Guide, and presented at IDUG several times. He has been a repeat popular guest on The Db2Night Show many times and has published several popular magazine articles. Seriously, we should all worry about his health—it seems this man never sleeps, or, if he does, his life revolves around Db2 24x7.
I first met Kent when IBM was launching DB2 V10.5 with “BLU” columnar capabilities at the Almaden Research Center. If you’re an IBM groupie like many, Kent’s face might be more familiar to you because IBM has featured him at many of their roadshow events for emerging technologies. He has been featured as a “talking head” during keynotes at IBM IOD conferences, the Pure System Launch event in Boston, IBM Tech and Customer Briefings, and more. He has authored many TCP style documents on pre-release versions of Db2 that showcase new features, functionality, and Db2 performance breakthroughs. And like Mohan, Kent also co-authored the DB2 10.1 and 10.5 Administration Certification Study Guide. He has presented at several IBM and IDUG conferences, and he’s been our guest many times on The Db2Night Show. Frankly, when it comes to IBM Db2 pureScale, I think Kent is absolutely on the top shelf, with very few peers if any.
As a key executive at a Db2 Performance Tools company, I’ve long believed that it is critically important for people to first understand what is important to monitor, how to do it, and how to interpret and react to the Db2 performance data. Once people have this foundational understanding, then they can make smart choices about the processes and tools that they will use to automate analysis. This is why I’ve taught technical sessions at IDUG every year since 1996, and it's also one of the reasons that I think you are going to love this book. Mohan and Kent have a gift for explaining the complex; making it simple; and leading with examples, samples, and illustrations. After you read Chapter 5 on monitoring Db2 activity and try out many of the sample monitoring SQLs provided, you’ll be torn between keeping this book at your desk and under your pillow. Maybe you should buy a second copy!
Finally, and not surprisingly, both Mohan and Kent have been awarded IBM Champion status from 2010 to 2018 (current). Because of their deep knowledge of and love for IBM Db2, their many devoted contributions to the worldwide Db2 community, and their professional, helpful demeanor, I recently nominated both Mohan and Kent to become IBM Db2 GOLD Consultants. I hope you enjoy this book and learn a lot from it, and that someday you might be blessed with the opportunity to meet these Db2 professionals in person at an IDUG conference, have the ability to work alongside them professionally, or both!
With kindest regards,
Scott Hayes
President and Founder of DBI Software, IBM Db2 GOLD Consultant, and IBM Champion
Contributors
About the authors
Mohankumar Saraswatipura is a database solutions architect focusing on IBM Db2, Linux, Unix, Windows, and SAP HANA solutions. He is an IBM Champion (2010-2018) and a DB2's Got Talent 2013 winner. He is also a frequent speaker at the DB2Night Show and IDUG North America conferences. He has written dozens of technical papers for IBM developerWorks, Data Magazine, and the DB2 10.1/10.5 certification guide. He holds a Master's of technology in computer science and an executive MBA from IIM Calcutta.
Robert (Kent) Collins, the founder of Shiloh Consulting, is currently a database solutions architect at BNSF Railway. He is an IBM Champion (2010–2018) and a frequent speaker at the DB2Night Show, IDUG North America, and IBM Insight conferences. Kent has worked continually with Db2 since its introduction to the market in 1984, amassing a wealth of knowledge and experience. He graduated from University of Texas in Dallas with majors in mathematics and computer science.
About the reviewers
Colin A. Chapman has worked for 30+ years as an IT professional in a broad range of technical roles. His experience covers mainframe operations, application design and development, systems programming, and database design and administration. More recently, he has specialized in Db2 on Linux, Unix, and Windows platforms. He has a broad range of in-depth Db2 skills, particularly in areas such as large partitioned databases, clustering, high availability, cross-platform replication, performance, recovery, security, and complex problem diagnostics.
Corina Munsch has 25+ years of software design and development experience. She has developed applications in C++, Java, and Groovy/Grails. She has experience with both Oracle and Db2 databases. She is currently a senior consulting application developer.
Lan Pham has worked for over 27 years as an IT professional in a broad range of product development roles within IBM Toronto Lab (especially Db2) for over 24 years. More recently, he worked on Db2 pureScale kernel development. He holds a Bachelor of Science degree in electrical engineering from University of Western Ontario.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Title Page
Copyright and Credits
IBM Db2 11.1 Certification Guide
Packt Upsell
Why subscribe?
PacktPub.com
Foreword
Contributors
About the authors
About the reviewers
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the color images
Conventions used
Get in touch
Reviews
Introduction to the Db2 Certification Path
Db2 11.1 Certification track
Db2 10.5 Fundamentals for LUW–Exam C2090-615
Db2 11.1 Fundamentals for LUW–Exam C2090-616
Db2 11.1 DBA for LUW–Exam C2090-600
Preparing for the C2090-600 certification exam
Registering for the C2090-600 certification exam
Summary
Db2 Server Management
Working with Db2 instances
Creating an instance
Dropping an instance
Listing instances
Displaying the Db2 service level
Verifying the database's readiness for upgrading
Upgrading a database instance
Starting and stopping an instance
Attaching and detaching to an instance
Quiescing an instance
Configuring the Db2 system environment
Listing and modifying the global registry
Aggregate registry variables
Configuring Db2 instances and databases
The Db2 database manager configuration
The Db2 database configuration
DB2 autonomic computing features
Self-Tuning Memory Manager (STMM)
Automatic storage
Creating storage groups
Altering storage groups
Dropping storage groups
A word about Db2 workload manager (WLM)
The WLM objects
Workloads
Thresholds
Work action and work class sets
Histogram templates
A word about IBM Data Server Manager (DSM)
Summary
Practice questions
Solutions
Physical Design
Database planning
Creating a Db2 database
Enabling automatic storage
Buffer pools
Buffer pool planning
Creating a buffer pool
Altering a buffer pool
Storage groups
Storage group planning
Altering storage groups
Renaming a storage group
Dropping a storage group
Tablespaces
Containers
Tablespace planning
Creating tablespaces
Adding containers to automatic storage tablespaces
Tablespace rebalance operation
Reclaimable storage
Converting DMS tablespaces to use automatic storage
Smart disk storage management
Creating a table
Alter table
Expression-based indexes
Invalidation of database objects
Revalidation of database objects
Online table move
Tracking the execution of online table move
Online table move planning
Online table move-running changes
Copy schema procedure
Range clustering and range partitioning tables
Range clustered tables
Range clustered table planning
Range partitioned tables
Listing the data partitions
Detaching a data partition
Attaching a data partition
MDC
MQT
ITC
Db2 BLU Acceleration
Implementing BLU
BLU compression and statistics
BLU disk space management
BLU platform management
Anatomy of a columnar table
Db2 V11.1 BLU advances
db2convert
Db2 pureScale
Members
Cluster-caching facility (CF)
Shared disk storage
Cluster services and interconnect
Db2 v11.1 pureScale advances
Availability
Installation and upgrades
Management and performance
Features
Self-tuning database memory manager (STMM) in pureScale
Self-tuning CF memory in pureScale
Db2_DATABASE_CF_MEMORY (DB2 registry variable)
CF_DB_MEM_SZ (DB CFG in 4K pages)
CF_GBP_SZ, CF_LOCK_SZ, CF_SCA_SZ (DB CFG in 4K pages)
Data compression
Static row compression
Should the table be compressed?
Getting information about the compression dictionary
Building a row compression dictionary
Index compression
LOBs and XML compression
Temporary tables and replication support
Adaptive compression
SQL compatibilities
Compatibility features for Netezza
Summary
Practice questions
Solutions
Implementing Business Rules
Business rules
NOT NULL constraints
DEFAULT constraints
CHECK constraints
UNIQUE constraints
NOT ENFORCED informational constraints
Referential integrity constraints
The INSERT rule for referential constraints
The update rule for referential constraints
The delete rule for referential constraints
The SET INTEGRITY statement
Views with CHECK OPTION
Creating and using triggers
The system catalog views
Summary
Practice questions
Answers
Monitoring Db2 Activity
Monitoring infrastructure
Monitoring table functions
Monitoring system information
Monitoring activities
Monitoring data objects
Monitoring locks
Monitoring system memory
Monitoring the pureScale environment
Additional monitoring table functions
Monitoring table functions: a quick reference
Administrative views for monitoring
A word about the MONREPORT module
Event monitors
EXPLAIN facility tools
A word about the EXPLAIN operators
Db2 troubleshooting tools
The db2pd command
The dsmtop command
Summary
Practice questions
Solutions
Db2 Utilities
Data movement file formats
Data movement utilities
The Db2 Export utility
The Db2 Import utility
The Db2 Load Utility
Phases of a load operation
Data load best practices for BLU Column-Organized Tables
Monitoring a Load operation
The DB2 Ingest Utility
Monitoring an Ingest operation
DB2 utilities comparison – Import vs. Ingest vs. Load
A word about the CATALOG STORAGE ACCESS command
Other DB2 data movement options
The SYSPROC.ADMIN_MOVE_TABLE () procedure
The SYSPROC.ADMIN_COPY_SCHEMA () procedure
Object Copy using the db2move command
A word about the db2look tool command
Database maintenance utilities
The REORGCHK utility
The REORG Utility
The RUNSTATS Utility
The REBIND Utility
Flushing the Package Cache Utility
The ADMIN_CMD () procedure
Summary
Practice questions
Answers
High Availability
Transactions and logging parameters
Transaction logging strategies
Database recovery methods
Backup and recovery
The Db2 backup utility
Incremental and delta backups
A word about hardware accelerated backup
The Db2 restore utility
The Db2 roll-forward utility
The Db2 recover utility
A word about the db2relocatedb command
High availability and disaster recovery
HADR synchronization modes
HADR-specific parameters
Setting up an HADR multiple standby environment
A word about the Db2 HA instance configuration utility (db2haicu)
Activating the RoS feature
Db2 pureScale architecture
Geographically dispersed Db2 pureScale db2cluster (GDPC)
Db2 pureScale db2cluster service command options
Explicit Hierarchical Locking 
Summary
Practice questions
Answers
Db2 Security
Authentication and authorization
Authorities and privileges
Authorities
The system administrative authority (SYSADM) 
The system control authority (SYSCTRL)
The system maintenance authority (SYSMAINT)
The system monitor authority (SYSMON)
The database administrator authority (DBADM)
The security administrator authority (SECADM)
The SQL administrator (SQLADM) authority
The workload management administrator authority (WLMADM)
The Explain administrator authority (EXPLAIN)
The Access control administrator authority (ACCESSCTRL)
The data access administrator authority (DATAACCESS)
The data load (LOAD) authority
Privileges
LDAP-based authentication
Role-based access control
Trusted context and trusted connections
Row and column access control (RCAC)
Row permissions
Column masks
The Db2 audit facility
Audit policies
The db2audit tool command
Secure Socket Layer (SSL) implementation in Db2
Db2 native encryption
Summary
Practice questions
Answers
Other Books You May Enjoy
Leave a review - let other readers know what you think
Preface
IBM Db2 is a Relational Database Management System (RDBMS) that helps users to store, analyze, and retrieve their data efficiently. This guide will help you understand Db2 v11.1 DBA certification topics. It covers more than 50 step-by-step procedures for DBAs, all of Db2 v11.1's new features, and its benefits in the real world. This book can also be used to enhance your Db2 skill set.
Who this book is for
This guide is an excellent choice for database administrators, architects, and application developers who are keen to obtain a certification in Db2. Basic understanding of Db2 is expected in order to get the most out of this guide.
What this book covers
Chapter 1, Introduction to the Db2 Certification Path, covers the certification exam and how to register for it.
Chapter 2, Db2 Server Management, is about building and configuring Db2 instances and databases. It also covers topics on autonomic computing, which will help you to manage your database optimally.
Chapter 3, Physical Design, covers all the Db2 database design aspects for DBAs.
Chapter 4, Implementing Business Rules, explains business implementation rules through Db2 constraints.
Chapter 5, Monitoring Db2 Activity, covers all Db2 monitoring aspects for DBAs and architects.
Chapter 6, Db2 Utilities, explains all Db2 utilities and their uses.
Chapter 7, High Availability, covers all Db2 high availability and disaster recovery features, including pureScale.
Chapter 8, Db2 Security, explains all Db2 security features and their implementation methods.
To get the most out of this book
Download and install IBM Db2 v11.1 Developer Community Edition from https://www.ibm.com/account/reg/us-en/signup?formid=urx-19888 and follow the instructions in the chapters. This certification guide gives you easy access to the invaluable learning techniques. Try out every single command and statement to experience the techniques.
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: http://www.packtpub.com/sites/default/files/downloads/Bookname_ColorImages.pdf.
Conventions used
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "Mount the downloaded WebStorm-10*.dmg disk image file as another disk in your system."
A block of code is set as follows:
html, body, #map { height: 100%; margin: 0; padding: 0}
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
[default]exten => s,1,Dial(Zap/1|30)exten => s,2,Voicemail(u100)
exten => s,102,Voicemail(b100)
exten => i,1,Voicemail(s0)
Any command-line input or output is written as follows:
$ mkdir css
$ cd css
Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Select System info from the Administration panel."
Get in touch
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
Introduction to the Db2 Certification Path
This certification guide is designed for IT professionals who plan to take the IBM Certified Database Administrator–Db2 11.1 for Linux, Unix, and Windows exam C2090-600. Using this certification study guide, you will learn how to do the following:
Configure and manage Db2 v11.1 servers, instances, and databases
Implement Db2 BLU Acceleration databases
Create and implement database objects
Implement business rules using constraints
Implement high availability and disaster recovery solutions
Monitor and troubleshoot at instance and database levels
Implement security at instance, database, and objects levels
This certification study guide is designed to provide the Db2 professional with the information required to successfully obtain C2090-600 certification. Each chapter contains topics covered in the exam, plus valuable insights into each topic, along with sample exam questions and detailed answers.
Db2 11.1 Certification track
IBM offers one Certification track in Db2 for Linux, Unix, and Windows, essentially covering the fundamentals of intermediate level DBA concepts. The diagram that follows shows the database administration track and the prerequisites required to obtain the DBA certification:
Db2 10.5 Fundamentals for LUW–Exam C2090-615
C2090-615 is an entry-level exam for Db2 users who are familiar with the fundamental concepts of Db2 10.5 for Linux, Unix, and Windows. This exam covers the following topics:
Knowledge of Db2 product packing, including the editions and supported features
Knowledge of database workloads, such as
Online Transactional Processing
(
OLTP
), data warehouse, and
Hybrid Transactional
/
Analytical Processing
(
HTAP
) and which Db2 products must be installed to support the desired workload
Knowledge of Db2 10.5 BLU Acceleration and implementation steps
Knowledge of Oracle compatibility features and the application enablement process
Knowledge of data and database security overall including authorities, privileges,
Row and Column Access Control
(
RCAC
), and
Label-Based Access Control
(
LBAC
)
Knowledge of trusted context and its uses
Knowledge of different types of tables available within Db2, such as
multi-dimensional clustering
(
MDC
),
insert time clustering
(
ITC
),
range clustered table
(
RCT
),
materialized query table
(
MQT
), and range partitioned and temporal tables
Knowledge of
Structured Query Language
(
SQL
) statements such as
SELECT
,
INSERT
,
UPDATE
, and
DELETE
Knowledge of
Data Control Language
(
DCL
) statements such as
COMMIT
,
ROLLBACK
,
AUTOCOMMIT
, and
SAVEPOINT
Knowledge of using SQL stored procedures,
user-defined functions
(
UDF
), and triggers
Knowledge of SQL compatibility enhancements made in Db2 10.5 such as extended rows and excluding
NULL
keys from indexes
Knowledge of
Extensible Markup Language
(
XML
) data retrieval and manipulation using XQuery and XPath expressions
Knowledge of isolation levels and locking behaviors
Db2 11.1 Fundamentals for LUW–Exam C2090-616
This is an entry-level exam for Db2 users on the fundamental concepts of Db2 11.1 for Linux, Unix, and Windows. This exam covers the following topics:
Knowledge of
Db2
product packing, including the editions and supported features
Knowledge of database workloads such as OLTP, data warehouse, and HTAP and which
Db2
products must be installed to support the desired workload
Knowledge of
Db2
BLU Acceleration and implementation steps
Knowledge of
Db2
pureScale architecture and its benefits
Knowledge of federation support to link the objects between
Db2
databases and other database vendor products
Knowledge of Oracle and Neteeza compatibility features and the application enablement process
Knowledge of data and database security overall including authorities, privileges, RCAC, and LBAC
Knowledge of trusted context and its uses
Knowledge of different types of tables available within
Db2
such as MDC, ITC, RCT, MQT
,
and range partitioned and temporal tables
Knowledge of SQL statements such as
SELECT
,
INSERT
,
UPDATE
, and
DELETE
Knowledge of DCL statements such as
COMMIT
,
ROLLBACK
,
AUTOCOMMIT
and
SAVEPOINT
Knowledge of using SQL stored procedures, UDF, and triggers
Knowledge of SQL compatibility enhancements made in
Db2
11.1, such as
BINARY
and
VARBINARY
data type support, regular expression support in scalar functions, OLAP extension specifications, including
NTH_VALUE
,
CUME_DIST
,
PERCENT_RANK
, the
OFFSET
clause in
SELECT
to skip the number of rows, support for outer joins using the outer join operator (+), new built-in aggregate and scalar functions, new data types, and new synonyms
Knowledge of OLAP specification functions, such as
CUME_DIST()
,
PERCENT_RANK()
,
RANK()
,
DENSE_RANK()
, and
NTILE()
, and the business use cases for each
Knowledge of XML data retrieval and manipulation using XQuery and XPath expressions
Knowledge of isolation levels and locking behaviors
Db2 11.1 DBA for LUW–Exam C2090-600
This certification is intended for experienced Db2 users who have the knowledge necessary to perform the regular day-to-day administration of Db2 11.1 instances and databases on Linux, UNIX, and Windows. This exam covers the following topics:
Ability to install, configure, and manage
Db2
servers, instances, and databases
Knowledge about System Managed, Database Managed, and Automatic Storage table spaces
Ability to create and manage storage groups based on frequency of data access, acceptable access time, volatility of data, and application business requirements
Knowledge of
Db2
autonomic features such as the
self-tuning memory manager
(
STMM
), automatic maintenance, and the configuration advisor
Knowledge of IBM
Data Server Manager
(
DSM
) including the editions and its monitoring capabilities
Ability to design, create, and manage databases using
Db2
11.1 BLU Acceleration
Knowledge of
Db2
11.1 pureScale enhancements such as support for HADR sync and near sync data synchronization modes, unified workload balancing,
geographically dispersed Db2 pureScale cluster
(
GDPC
) support, ease of pureScale cluster health check commands, and GPFS replication support
Ability to create, manage, and alter database objects
Ability to implement table, index compression, and backup compression
Knowledge of SQL compatibility enhancements made in
Db2
11.1
Knowledge of partitioning capabilities available within
Db2
including range partitioning, database partitioning, or
Massively Parallel Processing
(
MPP
)
Ability to create and modify constraints between tables and to enforce constraint checking using the
SET INTEGRITY
command
Ability to use the
Db2
administrative views and table functions, along with event monitors and
db2pd
command options to monitor the health of the database
Knowledge of the dsmtop command and its capabilities
Ability to capture and analyze
Db2
explain information for any specific SQL statement and/or workload
Ability to use Export, Import, Load, and Ingest data movement utilities
Ability to use the
REORGCHK
,
REORG
,
REBIND
,
RUNSTATS
,
FLUSH PACKAGE CACHE
,
ADMIN_CMD()
,
db2look
, and
db2move
commands
Ability to back up and recover the database at the database and table space level
Ability to implement
High Availability and Disaster Recovery
(
HADR
) in multiple standby environments and to perform HADR takeover with zero or minimal data loss
Ability to implement and maintain a
Db2
pureScale cluster
Ability to protect data and the database objects against unauthorized access or modification
Ability to implement RCAC and LBAC to protect sensitive data
Knowledge of data encryption at rest and in transit using
Db2
native encryption and
Secured Socket Layer
(
SSL
) features
Ability to implement a database audit facility to monitor and record sensitive data access and/or modification
To acquire the IBM Certified Database Administrator–Db2 11.1 for Linux, Unix, and Windows (C2090-600: IBM Db2 11.1 DBA for LUW), candidates must hold either IBM Certified Database Associate–Db2 10.5 Fundamentals for LUW (C2090-615) or the IBM Certified Database Associate–Db2 11.1 Fundamentals for LUW (C2090-616) certification.
Preparing for the C2090-600 certification exam
This certification study guide prepares you for every exam objective with necessary sample questions to ensure that you have the skills and knowledge necessary to take the exam. The Db2 11.1 DBA certification exam questions are categorized into seven units and the percentage of each unit is as listed in the following table. Understanding the percentage of each unit will help you to focus on important concepts and areas:
Unit
Topic
Percentage Coverage
1
Server Management
15
2
Physical Design
22
3
Implementing Business Rules
10
4
Monitoring Db2 Activity
12
5
Utilities
13
6
High Availability
13
7
Security
15
Registering for the C2090-600 certification exam
Once you are confident and ready to take the certification exam, contact Pearson VUE—an IBM-authorized testing vendor—via www.pearsonvue.com/ibm and register for the exam. You must make arrangements to take a certification exam at least 24 hours in advance and be ready with the following information:
Your name (name as you want it to appear on the certification).
An identification number (if you have taken IBM certification exams before, this is the number assigned to you at the time of the exam; if you don't have one, the testing vendor will create a new one for you).
Primary contact information.
Email address.
Contact number.
Certification exam number such as C2090-600.
The testing center where you would like to take the certification exam.
The date when you would like to take the certification exam.
For more information about this exam, visit IBM Professional Certification Program at http://www-03.ibm.com/certify/certs/08002109.shtml.
Summary
The objective of this chapter was to acquaint you with the certification examinations path available for Db2 v11.1, the certification examination pattern, and the certification examination registration process.
Db2 Server Management
This chapter covers the steps to configure and manage Db2 servers, instances, and databases. You will learn how to create and efficiently manage the database storage groups. You will also learn how to use many of the Db2 autonomic computing features to improve database availability and performance. You will learn how to use the Db2 workload manager (WLM) and its functionalities to efficiently meet the service-level agreements in the environment. By the end of the chapter, you will also be able to use the data server manager (DSM) to administer, monitor, and manage the Db2 database server. After the completion of this chapter, you will be able to demonstrate the ability to:
Create and manage Db2 instances and databases
View and modify the Db2 registry variables
View and modify the Db2 database manager configuration parameters
View and modify the Db2 database configuration parameters
Use Db2 autonomic computing features, including the
self-tuning memory manager
(
STMM
), data compression, automatic maintenance, the configuration advisor, and utility throttling
Create and modify storage groups
Use Db2 WLM to effectively manage the Db2 server
Use IBM DSM to administer the Db2 database server
Working with Db2 instances
A Db2 instance is an environment responsible for managing system resources and the databases that fall under their control. Basically, an instance is made up of a set of processes, threads, and memory areas. The following diagram illustrates the relationship between a Db2 server, its instances, and its associated databases:
The server is at the highest level in the hierarchy, and you can have multiple Db2 versions installed on a given server. It is recommended that you check the system requirements for IBM Db2 for Linux, Unix, and Windows installations at https://www-01.ibm.com/support/docview.wss?uid=swg27038033.
Instances are second in the hierarchy and are responsible for managing the system resources required to run the database. You can create multiple instances on a server, providing a unique database environment for each instance. In the example shown in the figure above, there are two instances, db2inst1 and db2inst2, which provide an environment to run a different set of databases of the same or different versions. Once an instance is created, the instance directory stores all information that pertains to a database instance. Each instance contains:
The
database manager configuration
(
DBM CFG
) file
The system database directory
The
node
directory
The node configuration file, called
db2nodes.cfg
Other files, which contain troubleshooting information
Databases occupy the third level in the hierarchy and are responsible for managing the storage, modification, and retrieval of data. When a database is first created, the database directory stores all information that pertains to a database. Each database contains:
The
database configuration
(
DB CFG
) file
The system catalog tables and associated objects, including storage groups, table spaces, buffer pools, and containers
Allocation of the database recovery log, sometimes known as the transaction log
Db2 provides a number of commands for creating and managing instances. The following table shows the system commands that can be executed directly from a regular Unix shell. In order to invoke the CLP on Windows, execute and run db2cmd. On Unix servers, log in to the server as an instance owner and start a command line session:
Command
Purpose
Command Path
db2icrt
Creates a new instance
Unix: DB2DIR/instance
Windows: DB2PATHbin
db2idrop
Removes an existing instance
Unix: DB2DIR/instance
Windows: DB2PATHbin
db2ilist
Lists all of the instances that have been defined within one installation
Unix: DB2DIR/instance
Windows: DB2PATHbin
db2level
Shows the version and service level of the installed Db2 product for the current instance
~/sqllib/bin/db2level
db2ckupgrade
Verifies that one or more databases are ready to be upgraded to a newer version of Db2
Unix: DB2DIR/bin of the newly installed DB2 version
Windows: db2WindowsUtilities from the product CD
db2iupgrade
Upgrades an existing instance to a newer version of Db2
Unix: DB2DIR/instance
Windows: DB2PATHbin
db2iupdt
Updates an instance to a higher Fix Pack level within a release, converts an instance from non-pureScale to pureScale, or changes the topology of a Db2 pureScale instance
Unix: DB2DIR/instance
Windows: DB2PATHbin
db2start
Starts the database manager for the current instance
Unix: ~/sqllib/adm/
db2stop
Stops the database manager for the current instance
Unix: ~/sqllib/adm/
Creating an instance
The db2icrt command creates a Db2 instance in the home directory of the instance owner. It creates the SQLLIB subdirectory and DBM CFG parameters, with the default settings.
The syntax for the command in the Unix environment is as follows:
db2icrt -u <FencedUserID> <InstanceName>
In the preceding command, the following applies:
FencedUserID
: Identifies the name of the user ID under which fenced user-defined functions and fenced stored procedures will run
InstanceName
: Identifies the name of the instance, which is also the name of the user in the operating system
If you want to create an instance named db2inst1 using the fenced username db2fenc1, you can do so by executing the command db2icrt as the root user from the Db2 installation instance directory, also called the DB2DIR/instance directory:
cd /opt/ibm/db2/V11.1/instance db2icrt -u db2fenc1 db2inst1
Dropping an instance
The db2idrop command drops an existing Db2 instance and removes the SQLLIB subdirectory and DBM CFG parameter file. However, this command can only be run when the instance is stopped and is inactive.
The syntax for the command is as follows:
db2idrop <InstanceName>
In the preceding command, the following applies:
InstanceName
: Identifies the name of the instance intended to be dropped
If you want to drop an instance named db2inst1, execute the command db2idrop as the root user from the DB2DIR/instance directory:
db2idrop db2inst1
Listing instances
The db2ilist command lists all of the instances that have been defined within one installation, and the syntax for the command is as follows:
db2ilist
If you want to list all of the instances for the Db2 v11.1 installation, you can do so by executing the command db2ilist as an instance owner:
db2ilist
-- Output of db2ilist would look something like: db2inst1 db2inst2
Displaying the Db2 service level
The db2level command shows the current version and service level of the installed Db2 product for the current instance.
Verifying the database's readiness for upgrading
The db2ckupgrade command verifies that a database is ready for an upgrade, and the syntax for the command is as follows:db2ckupgrade <DatabaseName> -l <UpgradeCheckLog>
In the preceding command, the following applies:
UpgradeCheckLog
: Identifies a log file to write a list of errors and warning messages generated for the scanned database.
DatabaseName
: Identifies the name of the database intended to be upgraded:
db2ckupgrade SAMPLE -l upgradecheck.log
If you want to check the readiness of database SAMPLE to upgrade from Db2 10.5 to Db2 11.1, you can do so by executing the command db2ckupgrade as the instance owner of the database SAMPLE from the DB2DIR/bin directory, where DB2DIR is the location of the newer Db2 version installation:
db2ckupgrade SAMPLE -l upgradecheck.log
Upgrading a database instance
The db2iupgrade command upgrades the database instance from an older version to a newer version.
The syntax for the command is as follows:
db2iupgrade <InstanceName>
In the preceding command, the following applies:
InstanceName
: Identifies the name of the instance intended to be upgraded
If you want to upgrade an instance named db2inst1 from Db2 10.5 to Db2 11.1, you can do so by executing the command db2iupgrade as the root user from the DB2DIR/instance directory of Db2 11.1:
db2iupgrade db2inst1
Starting and stopping an instance
The db2start command starts the database manager background processes associated with a particular instance. The syntax for this command is as follows:
db2start
The syntax could also be:
START [DATABASE MANAGER | DB MANAGER | DBM]
If you want to start the database manager for the db2inst1, execute the following command:
db2start
You can also execute the following:
db2 "START DATABASE MANAGER"
The db2stop command stops the database manager processes for a particular instance. The syntax for this command is as follows:
db2stop
You can also use:
STOP [DATABASE MANAGER | DB MANAGER | DBM] <FORCE>
If you want to start the database manager for the db2inst1, execute the following command:
db2stop
You can also execute the following:
db2 "STOP DATABASE MANAGER"
Configuring the Db2 system environment
The Db2 database environment contains a number of operating system environment variables and Db2-specific profile registry variables that are used to manage, monitor, and control the behavior of the Db2 system.
Environment variables are set at the operating system level using standard operating system commands, such as set on a Windows operating system and export on a UNIX operating system. Examples are shown here:
set DB2INSTANCE=db2inst1 [On Windows]
export DB2INSTANCE=db2inst1 [On UNIX]
The Db2 profile registry settings are categorized as:
Db2 global-level profile registry
: All server-wide environment variable settings reside in this registry, and these settings are applicable to all of the instances that pertain to a particular version of Db2.
Db2 instance-level profile registry
: The environment variable settings for a particular instance are stored in this registry. The values defined in this profile registry override any corresponding settings in the global-level profile registry.
Db2 instance node-level profile registry
: The environment variable settings that are specific to a node in a
Massively Parallel Processing
(
MPP
) (also called a
Database Partitioning Feature
(
DPF
)) database environment are stored in this registry. The values defined in this profile registry override any corresponding settings in the global-level and instance-level profile registries.
DB2 user-level profile registry
: The environment variable settings that are specific to each user are stored in this registry, and they takes higher precedence over other profile registry settings.
The following table shows the order in which Db2 resolves the registry settings and the environment variables when configuring the system:
Profile Registry
Precedence
Location on Linux and UNIX platform
Environment Variables
1
For Bourne or korn shell: instance_home/sqllib/db2profile
For C shell: instance_home/sqllib/db2cshrc
User Level
2
Not applicable
Instance Node Level
3
$INSTHOME/sqllib/nodes
Filename: <nodenumber>.env
Instance Level
4
$INSTHOME/sqllib/profile.env
Global Level
5
For root installations: /var/db2/global.reg
For non-root installations:
home_directory/sqllib/global.reg
Aggregate registry variables
An aggregate registry variable is a group of several registry variables, such as a configuration, that is identified by one registry variable name. The purpose of an aggregated registry variable is to ease registry configuration for broad operational objectives.
In Db2 11.1, the only valid aggregate registry variable is DB2_WORKLOAD, and the valid values for this variable are:
ANALYTICS
: Column-organized BLU-specific workload setting
1C
:
1C application-specific workload setting
CM
:
Content manager-specific workload setting
COGNOS_CS
:
Cognos content server-specific workload setting
FILENET_CM
:
FileNet content manager-specific workload setting
INFOR_ERP_LN
:
Infor
enterprise resource planning
(
ERP
) Baan-specific workload setting
MAXIMO
:
Maximo-specific workload setting
MDM
:
Master data management-specific workload setting
SAP
:
SAP application-specific workload setting
TPM
:
Tivoli provisioning manager-specific workload setting
WAS
:
WebSphere application server-specific workload setting
WC
: WebSphere commerce-specific workload setting
WP
:
WebSphere portal-specific workload setting
If you want to set the aggregated registry variable DB2_WORKLOAD to support the SAP application workload, you can do so by executing the following command:
db2set DB2_WORKLOAD=SAP db2set -all | grep -i DB2_WORKLOAD [i] DB2_ONLINERECOVERY_WITH_UR_ACCESS=FALSE [DB2_WORKLOAD] [i] DB2_PARALLEL_ACS=YES [DB2_WORKLOAD] [i] DB2_TRANSCHEMA_EXCLUDE_STATS=TRUE [DB2_WORKLOAD] [i] DB2_USE_FAST_LOG_PREALLOCATION=TRUE [DB2_WORKLOAD] [i] DB2_CDE_STMTCACHING=YES [DB2_WORKLOAD] [i] DB2_INDEX_PCTFREE_DEFAULT=0 [DB2_WORKLOAD] [i] DB2_SKIP_VIEWRECREATE_SAP=TRUE [DB2_WORKLOAD] [i] DB2_BLOCKING_WITHHOLD_LOBLOCATOR=NO [DB2_WORKLOAD] [i] DB2_AGENT_CACHING_FMP=OFF [DB2_WORKLOAD] [i] DB2_TRUST_MDC_BLOCK_FULL_HINT=YES [DB2_WORKLOAD] [i] DB2_CREATE_INDEX_COLLECT_STATS=YES [DB2_WORKLOAD] [i] DB2_ATS_ENABLE=YES [DB2_WORKLOAD] [i] DB2_RESTRICT_DDF=YES [DB2_WORKLOAD] [i] DB2_DUMP_SECTION_ENV=YES [DB2_WORKLOAD] [i] DB2_WORKLOAD=SAP [i] DB2_TRUNCATE_REUSESTORAGE=IMPORT,LOAD [DB2_WORKLOAD] [i] DB2_MDC_ROLLOUT=DEFER [DB2_WORKLOAD] [i] DB2_ATM_CMD_LINE_ARGS=-include-manual-tables [DB2_WORKLOAD] [i] DB2_VIEW_REOPT_VALUES=YES [DB2_WORKLOAD] [i] DB2_OBJECT_TABLE_ENTRIES=65532 [DB2_WORKLOAD] [i] DB2_IMPLICIT_UNICODE=YES [DB2_WORKLOAD] [i] DB2_BCKP_PAGE_VERIFICATION=TRUE [DB2_WORKLOAD] [i] DB2_BCKP_INCLUDE_LOGS_WARNING=YES [DB2_WORKLOAD] [i] DB2_RUNTIME_DEBUG_FLAGS=TOLERANT_FLOAT,DISABLE_BLANK_TOLERANCE [DB2_WORKLOAD] [i] DB2STMM=APPLY_HEURISTICS:YES,GLOBAL_BENEFIT_SEGMENT_UNIQUE:YES [DB2_WORKLOAD] [i] DB2_INLIST_TO_NLJN=YES [DB2_WORKLOAD] [i] DB2_MINIMIZE_LISTPREFETCH=YES [DB2_WORKLOAD] [i] DB2_REDUCED_OPTIMIZATION=4,INDEX,JOIN,NO_TQ_FACT,NO_HSJN_BUILD_FACT,STARJN_CARD_SKEW,NO_SORT_MGJOIN,REDUCE_LOCKING,CART OFF,CAP OFF [DB2_WORKLOAD] [i] DB2NOTIFYVERBOSE=YES [DB2_WORKLOAD] [i] DB2_INTERESTING_KEYS=YES [DB2_WORKLOAD] [i] DB2_EXTENDED_OPTIMIZATION=GY_DELAY_EXPAND 1000,NO_NLJN_SPLIT_BUFFER [DB2_WORKLOAD] [i] DB2COMPOPT=VOLATILETSF,WORKLOADSAP,BLU_SAP_MEMTBL,LOTO1,BREAK_VIEWCSE,REPL_UNLIMITED [DB2_WORKLOAD] [i] DB2COMM=TCPIP [DB2_WORKLOAD]
To view all of the registry variables that are implicitly set by the preceding aggregated registry setting command, you can execute the db2set command with the -gd option:
db2set -gd DB2_WORKLOAD=SAP DB2_REDUCED_OPTIMIZATION=4,INDEX,JOIN,NO_TQ_FACT,NO_HSJN_BUILD_FACT,STARJN_CARD_SKEW,NO_SORT_MGJOIN,REDUCE_LOCKING,CART OFF,CAP OFF DB2_MINIMIZE_LISTPREFETCH=YES DB2_INLIST_TO_NLJN=YES ..... ..... DB2_AVOID_LOCK_ESCALATION=TRUE You can SYSPROC.ENV_GET_REG_VARIABLES table function to retrieve the registry settings: SELECT SUBSTR (REG_VAR_NAME, 1, 20) as REG_VAR_NAME, SUBSTR (REG_VAR_VALUE, 1, 20) as REG_VAR_VALUE_IN_MEM, IS_AGGREGATE FROM TABLE (ENV_GET_REG_VARIABLES (-1)) AS T1;
Configuring Db2 instances and databases
Along with registry variables, Db2 also uses a set of database manager configuration (DBM) and database configuration (DB) parameters to control how system resources are allocated and managed to optimally run the database engine. Let's look at the DBM and DB configuration parameters in detail.
