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

Certification Study Guide

MC Press

Contents

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.