DB2 10.1/10.5 for Linux, UNIX, and Windows Database Administration: Certification Study Guide

DB2 10.1/10.5 for Linux, UNIX, and Windows Database Administration: Certification Study Guide

by Mohankumar Saraswatipura, Robert Collins
     
 

View All Available Formats & Editions


Much more than a simple certification study aid, this comprehensive 1,248 page 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 611 and 311: Certified Database Administrator. Building on years of extensive hands-on experience, the authors step you through all the
See more details below

Overview


Much more than a simple certification study aid, this comprehensive 1,248 page 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 611 and 311: Certified Database Administrator. Building on years of extensive hands-on experience, the authors step you through all the areas covered on the test. The book dives deep inside each certification topic: DB2 server management, physical design, business rules implementation, activity monitoring, utilities, high availability, security, and connectivity and networking. There is even a "crash course" chapter on DB2 10.5 features. Each chapter includes an extensive set of practice questions along with carefully explained answers. This book provides more than 400 practice questions and answers, more than 120 "flash cards" to help you study for the exam, and 50 step-by-step DB2 feature implementation procedures.

Editorial Reviews

From the Publisher

"This resource is designed to give the DB2 professional the information required in order to successfully obtain certification, or even to simply enhance their existing scope of DB2 knowledge. The authors have done an excellent job of distilling their many years of experience, both within the lab environment and within the live production environment into a logical, well-organized reference. Each section contains the fundamentals, plus valuable insights from the authors, and is backed up with sample exam questions, as well as detailed answers . . . . I am confident that with this guide, your certification will not be far away!" —Eric Sheley, Global IT Director, FTSE 100 Global Consumer Goods Company

Product Details

ISBN-13:
9781583473757
Publisher:
Mc Press
Publication date:
09/01/2015
Edition description:
Study Guid
Pages:
1248
Sales rank:
823,311
Product dimensions:
7.10(w) x 8.90(h) x 2.50(d)

Read an Excerpt

DB2 10.1/10.5 for Linux, UNIX, and Windows Database Administration

Certification Study Guide

By Mohankumar (Mohan) Saraswatipura, Robert (Kent) Collins

MC Press

Copyright © 2015 Mohankumar Saraswatipura and Robert Collins
All rights reserved.
ISBN: 978-1-58347-744-1

CHAPTER 1

IBM DB2 10.1 Certification


Recognized throughout the world, the IBM® Professional Certification Program offers a range of certification options for IT professionals. This chapter will introduce you to the various paths to obtain DB2® 10.1 Certification from IBM and will describe the testing software you will use when you take your DB2 10.1 certification exam.


DB2 10.1 Certification Roles

One of the biggest trends in the IT industry today is certification. Many application and software vendors now have certification programs in place that evaluate and validate an individual's proficiency with the vendor's latest product release. In fact, one reason IBM developed the Professional Certification Program was to provide a way for skilled technical professionals to demonstrate their knowledge and expertise with a particular version of an IBM product.

The Professional Certification Program from IBM consists of several distinct certification roles that are designed to guide you in your professional development.

You begin the certification process by selecting the role that is right for you and familiarizing yourself with the certification requirements for that role. To help get you started, the following subsections provide you with the prerequisites and requirements associated with each IBM DB2 10.1 and DB2 9.7 certification available.


DB2 10.1 Certification Tracks

IBM offers broadly three certification tracks for DB2 for Linux®, UNIX®, and Windows® (LUW) certification to cover expert areas such as database administration, application development, and solution development. Figure 1.1 shows the Database Administration track.


Track 1: Database Administration


IBM Certified Database Associate — DB2 10.1 Fundamentals

This certification is intended for entry-level DB2 users who are knowledgeable about the fundamental concepts of DB2 10.1 for Linux, UNIX, and Windows. In addition to having some hands-on experience with DB2 10.1, some formal training, or both, individuals seeking this certification should:

[check] Know what DB2 10.1 products are available and be familiar with the various ways DB2 10.1 is packaged

[check] Know which DB2 10.1 products must be installed to create a desired environment

[check] Know what features and functions are provided by the tools shipped with DB2 10.1

[check] Possess an in-depth knowledge of Structured Query Language (SQL), Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL) statements that are available with DB2 10.1

[check] Know how to create, access, and manipulate basic DB2 objects, such as tables, views, and indexes, along with the new temporal table feature

[check] Have a basic understanding of the methods used to isolate transactions from each other in a multiuser environment

[check] Be familiar with the methods used to control how locking is performed

[check] Be familiar with the different types of constraints and know how each is used

[check] Possess a strong knowledge about the mechanisms DB2 10.1 uses to protect data and database objects against unauthorized access and/or modification, including row and column access control (RCAC) and trusted context

[check] Be familiar with how Extensible Markup Language (XML) data can be stored and manipulated


To acquire the IBM Certified Database Associate - DB2 10.1 Fundamentals certification, candidates must take and pass one exam: DB2 10.1 Fundamentals (C2090-610).


IBM Certified Database Administrator — DB2 10.1 for Linux, UNIX, and Windows

This certification is intended for experienced DB2 users who possess the knowledge and skills necessary to perform the day-to-day administration of DB2 10.1 instances and databases residing on Linux, UNIX, or Windows platforms. In addition to being knowledgeable about the fundamental concepts of DB2 10.1 and having significant hands-on experience as a DB2 database administrator (DBA), individuals seeking this certification should:

[check] Know how to create, configure, and manage DB2 10.1 instances and databases

[check] Know how to configure client/server connectivity

[check] Be able to obtain and modify the values of environment and registry variables

[check] Be able to obtain and modify DB2 database manager (instance) and database configuration parameter values

[check] Know how to use the IBM Data Studio GUI tools to manage instances and databases, create and access objects, and manage database changes and tasks

[check] Know how to create, access, modify, and manage the different DB2 10.1 objects

[check] Be able to create constraints on and between table objects

[check] Possess a strong knowledge about System Managed Space (SMS), Database Managed Space (DMS), and automatic storage table spaces, as well as database storage groups

[check] Know how to use autonomic features such as automatic maintenance, the self-tuning memory manager (STMM), and the Configuration Advisor

[check] Know how to manage XML data and modify it by using XPATH expressions

[check] Know how to use the DB2 administration views and table functions, along with event monitors to monitor the database

[check] Know how to capture and interpret snapshot monitor data

[check] Know how to capture and analyze DB2 Explain information

[check] Possess an in-depth knowledge of the Export, Import, Load, and Ingest utilities

[check] Know how to use the REORGCHK, REORG, REBIND, RUNSTATS, db2look, db2move, and db2pd commands

[check] Possess a strong knowledge about the mechanisms DB2 10.1 uses to protect data and database objects against unauthorized access or modification, including the label-based access control (LBAC) and RCAC mechanisms

[check] Have a basic understanding of the transaction logging mechanism and archival techniques

[check] Know how to perform database-level and table space-level backup, restore, and rollforward recovery operations

[check] Known how to implement High Availability and Disaster Recovery (HADR), and understand multiple standby HADR implementation guidelines

[check] Be able to interpret information stored in the administration notification log

[check] Possess a basic knowledge of the pureScale operations


Candidates who have passed either the DB2 10.1 Fundamentals exam (C2090-610) or the DB2 9 Fundamentals exam (C2090-730) must take and pass the DB2 10.1 for Linux, UNIX, and Windows Database Administration exam (C2090-611) to acquire the IBM Certified Database Administrator — DB2 10.1 for Linux, UNIX, and Windows certification.


IBM Certified Database Administrator — DB2 10.5 DBA for LUW Upgrade from 10.1

This certification is intended for experienced DB2 users who possess the knowledge and skills necessary to perform the day-to-day administration of DB2 10.5 instances and databases residing on Linux, UNIX, or Windows platforms. In addition to being knowledgeable about DB2 10.1 database administration concepts and having significant hands-on experience as a DB2 DBA for 10.5, especially with BLU Acceleration, individuals seeking this certification should:

[check] Know how to design, create, and manage databases on DB2 10.5 BLU

[check] Know how to configure DB2 workload for analytics system

[check] Know how to use autonomic computing features in DB2 10.5 BLU Acceleration

[check] Know how to use IBM Data Studio 4.1 and Optim™ Query Workload Tuner tools

[check] Be able to understand DB2 10.5 BLU Acceleration's seven big ideas:

1. It is simple — no indexes, multidimensional clustered (MDC) tables, materialized query tables (MQTs), range partitioning, or statistical views

2. Extreme compression and computer-friendly encoding

3. Deep hardware exploitation using single instruction, multiple data (SIMD) sets

4. Core-friendly parallelism

5. Column-organized store

6. Scan-friendly memory caching

7. Data skipping through a SYNOPSYS table

[check] Know how to implement table compression and backup compression

[check] Know how to use expression-based indexes and how to use RUNSTATS on the expression-based indexes

[check] Know how to use Oracle® compatibility features such as deep- nested object trees, extended row length, and three new index enhancements

[check] Know how to monitor a DB2 10.5 database instance and databases by using new monitoring elements

[check] Know how to use Explain to capture Column Table Queue (CTQ) operators in DB2 10.5 BLU

[check] Know how to use HADR monitoring enhancements

[check] Know how to use online REORG in a pureScale environment

[check] Know how to set up HADR in a pureScale cluster environment

[check] Know how to use the rolling fix pack upgrade, STMM Multi- Tenancy, and explicit hierarchical locking in a pureScale cluster

[check] Know how to install, activate, and configure DB2 Advanced Copy Services customized scripts for backup operations

[check] Know how to convert row-organized tables to column-organized tables by using the db2convert utility

[check] Know how LOAD command execution differs between row-organized tables and column-organized tables


To acquire the IBM Certified Database Administrator — DB2 10.5 DBA for LUW Upgrade from 10.1, candidates must hold the IBM Certified Database Administrator — DB2 10.1 for Linux, UNIX, and Windows certification, and they must take and pass the DB2 10.5 DBA for LUW Upgrade from 10.1 exam (C2090-311).


IBM Certified Advanced Database Administrator — DB2 10.1 for Linux, UNIX, and Windows

This certification is intended for lead database administrators who possess extensive knowledge about DB2 and who have vast experience using DB2 10.1 on one or more of the following supported platforms: Linux, AIX, HP- UX, Sun Solaris, or Windows. In addition to being knowledgeable about the more complex concepts of DB2 and having significant experience as a DB2 10.1 database administrator, individuals seeking this certification should:

[check] Know how to design, create, and manage buffer pools

[check] Know how to design, create, and manage SMS, DMS, automatic storage table spaces and storage groups, as well as multi- temperature data

[check] Be able to create and manage MDC, range partitioning, range clustered, and insert time clustering (ITC) tables

[check] Be able to create constraints on and between table objects

[check] Be able to identify and modify the DB2 database manager and database configuration file parameter values that have the most impact on performance

[check] Possess a strong knowledge of query optimizations

[check] Be able to correctly analyze, isolate, and correct database performance problems

[check] Know how to manage a large number of users and connections, including connections to host systems

[check] Know when the creation of an index will improve database performance

[check] Be able to fully exploit intrapartition parallelism and interpartition parallelism

[check] Be able to design, configure, and manage Database Partition Feature (DPF) databases spanning multiple servers

[check] Know how to manage federated servers and distributed units of work

[check] Possess a strong knowledge about the external authentication mechanisms that DB2 10.1 uses to protect data and database objects against unauthorized access or modification

[check] Know how to implement data encryption by using LBAC and RCAC

[check] Know how to design, implement, and manage the DB2 audit feature

[check] Be able to understand and develop a logging strategy

[check] Know how to perform database-level and table space-level backup, restore, and rollforward recovery operations

[check] Be able to use the advanced backup and recovery features

[check] Know how to implement a standby database by using log shipping, replication, failover, fault monitoring, and HADR with multiple standby features

[check] Know how to design, configure, and maintain the DB2 Workload Manager (WLM)

[check] Know how to manage pureScale systems


To acquire the IBM Certified Advanced Database Administrator — DB2 10.1 for Linux, UNIX, and Windows certification, candidates must hold the IBM Certified Database Administrator — DB2 10.1 for Linux, UNIX, and Windows certification, and they must take and pass the DB2 10.1 for Linux, UNIX, and Windows Advanced Database Administration exam (C2090-614).


Track 2: Database Application Development


IBM Certified Application Developer — DB2 9.7 for Linux, UNIX, and Windows

This certification is intended for intermediate- to advanced-level application developers who possess the knowledge and skills necessary to create applications that interact with DB2 9.7 databases. In addition to being knowledgeable about the fundamental concepts of DB2 and having strong skills in embedded SQL programming, ODBC/CLI programming, Java database connectivity (JDBC) programming, or Structured Query Language Interface (SQLJ) programming, individuals seeking this certification should:

[check] Be familiar with the naming conventions used to identify DB2 9.7 objects

[check] Possess an in-depth knowledge of the complex database objects available with DB2 9.7

[check] Possess an in-depth knowledge of the SQL, DDL, DML, and DCL statements available with DB2 9.7

[check] Know the difference between static and dynamic SQL

[check] Be able to execute queries across multiple tables and views

[check] Be able to query tables across multiple databases, including federated databases

[check] Know what authorities and privileges are needed to access data within an application

[check] Possess an in-depth knowledge of the SQL functions

[check] Know when to use embedded SQL, CLI/ODBC, JDBC, SQLJ, PHP, Perl, Python, .NET, and XML

[check] Be able to identify the types of cursors, as well as know when to use cursors in an application and what their scope will be

[check] Be able to work with MQTs

[check] Possess an in-depth knowledge of XML document encoding management

[check] Be able to identify the results of XML parsing and XML serialization

[check] Know how to validate XML schemas

[check] Be able to execute and identify the results of an XQuery expression

[check] Be familiar with the SQL and XML functions available with DB2

[check] Be able to establish a connection to a database within an embedded SQL, CLI/ODBC, JDBC, SQLJ, or .NET application

[check] Possess the ability to analyze the contents of an SQL Communications Area (SQLCA) data structure

[check] Possess the ability to obtain and analyze CLI/ODBC diagnostic information and.NET diagnostic information

[check] Possess the ability to obtain and analyze JDBC trace, SQL exception, and JDBC error log information

[check] Be able to create triggers and identify their results

[check] Know how to cast data types

[check] Know when to use compound SQL, parameter markers, and distributed units of work

[check] Know when and how to create user-defined functions (UDFs) and stored procedures

[check] Know when and how to use the trusted context


Candidates who have passed either the DB2 V8.1 Family Fundamentals exam (C2090-700) or the DB2 9 Family Fundamentals exam (C2090-730) must take and pass the DB2 9.7 Application Development exam (C2090-543) to acquire the IBM Certified Application Developer — DB2 9.7 for Linux, UNIX, and Windows certification, as Figure 1.2 shows.


Track 3: Database Solution Development

IBM Certified Solution Developer — DB2 9.7 SQL Procedure

This certification is intended for intermediate to advanced-level application and solution developers who possess the knowledge and skills necessary to create SQL procedures that interact with DB2 9.7 databases. In addition to being knowledgeable about the fundamental concepts of DB2 and having strong skills in DB2 functions, triggers, and SQL procedures, individuals seeking this certification should:

[check] Be familiar with the naming conventions used to identify DB2 9.7 objects

[check] Know what authorities and privileges are needed to access data within an application

[check] Possess an in-depth knowledge of the complex database objects available with DB2 9.7

[check] Possess an in-depth knowledge of the SQL, DDL, DML, and DCL statements available with DB2 9.7

[check] Know the difference between static and dynamic SQL

[check] Possess an in-depth knowledge of the available SQL functions

[check] Know how to design, create, test, and deploy SQL procedures, including nested procedures

[check] Know how to design, create, test, and deploy functions and triggers

[check] Know how to use DB2 advanced features such as declared global temporary tables, ADMIN_CMD procedure, global variables, modules, and arrays

[check] Know how to use DB2 development tools and explain tools


Candidates who have passed either the DB2 V8.1 Family Fundamentals exam (C2090-700) or the DB2 9 Family Fundamentals exam (C2090-730) must take and pass the DB2 9.7 SQL Procedure Developer exam (C2090-545) to acquire the IBM Certified Solution Developer — DB2 9.7 SQL Procedure Developer certification, as Figure 1.3 illustrates.


(Continues...)
Excerpted from DB2 10.1/10.5 for Linux, UNIX, and Windows Database Administration by Mohankumar (Mohan) Saraswatipura, Robert (Kent) Collins. Copyright © 2015 Mohankumar Saraswatipura and Robert Collins. Excerpted by permission of MC Press.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Meet the Author


Mohankumar (Mohan) Saraswatipura works as a Database Solutions Architect at Kronsys, Inc., focusing on IBM DB2, Linux, UNIX, and Windows solutions. Prior to his current position, he worked as a Database Solutions Architect at Reckitt Benckiser Group, plc (UK) focusing on IBM Smart Analytics System 5600, Siebel, SQL Server, and SAP HANA solutions. He is an IBM Champion (2010–2015) and a DB2's Got Talent 2013 winner. Mohan has written dozens of technical papers for IBM developerWorks and IBM Data Magazine. He is an IBM Certified DB2 Advanced Database Administrator, DB2 Application Developer, and DB2 Problem Determination Master. Mohan holds a Master's of Technology (M Tech) degree in computer science and an Executive MBA (IT). Robert (Kent) Collins, founder of Shiloh Consulting, Inc., is currently a Data Solutions Architect with BNSF Railway. He is an IBM Champion (2010–2015) and a frequent speaker at the DB2Night Show and IDUG and IOD (IBM Insight) conferences. Kent has worked continually with DB2 from its introduction to the market in 1984, amassing a wealth of knowledge and experience. He graduated from the University of Texas at Dallas with majors in mathematics and computer science. He is an IBM Certified Solutions Expert and also holds certifications in DB2, AIX, Linux, .NET, Oracle, SQL Server, Windows, and z/OS. Kent is proficient in many programming languages and, as a Java Architect, specializes in Enterprise HA/Performance systems. He lives in Dallas with his wife, Vicki; together, they have three children and one grandchild. 

Customer Reviews

Average Review:

Write a Review

and post it to your social network

     

Most Helpful Customer Reviews

See all customer reviews >