×

Uh-oh, it looks like your Internet Explorer is out of date.

For a better shopping experience, please upgrade now.

DB2 9 Fundamentals: Certification Study Guide
     

DB2 9 Fundamentals: Certification Study Guide

by Roger E. Sanders
 

See All Formats & Editions


Starting with the certification process itself and ending with the concept of isolation levels and locking, this guide covers everything needed to know to pass the DB2 9 Fundamentals Certification Exam (Exam 730). The prerequisite for all intermediate and follow-on DB2 certifications, XML usage, basic table creation, a rich set of sample questions,

Overview


Starting with the certification process itself and ending with the concept of isolation levels and locking, this guide covers everything needed to know to pass the DB2 9 Fundamentals Certification Exam (Exam 730). The prerequisite for all intermediate and follow-on DB2 certifications, XML usage, basic table creation, a rich set of sample questions, and detailed answers are all included, forming the foundation for understanding the newest version of IBM’s flagship database product. Regardless of what future certification path the reader is planning on pursuing, this study guide will chart a course for proficiency in DB2 9.

Editorial Reviews

From the Publisher

"I strongly recommend this book to anyone looking to obtain an IBM certified DB2 professional certification."  —MC iApplication Designer

MC iApplication Designer
I strongly recommend this book to anyone looking to obtain an IBM certified DB2 professional certification.

Product Details

ISBN-13:
9781583470725
Publisher:
Mc Press
Publication date:
05/01/2007
Edition description:
Study Guid
Pages:
552
Product dimensions:
7.00(w) x 9.00(h) x 1.02(d)

Related Subjects

Read an Excerpt

DB2 9 Fundamentals Certification Study Guide


By Roger E. Sanders

MC Press

Copyright © 2007 Roger Sanders
All rights reserved.
ISBN: 978-1-58347-615-4



CHAPTER 1

IBM DB2 9 Certification


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


DB2 9 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 are designed to evaluate and validate an individual's proficiency with the vendor's latest product release. In fact, one of the reasons the Professional Certification Program from IBM was developed 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 is comprised of several distinct certification roles that are designed to guide you in your professional development. You simply select the role that's right for you, and then you begin the certification process by choosing the role you wish to pursue and familiarizing yourself with the requirements for that role. The following subsections are designed to help get you started by providing you with the prerequisites and requirements associated with each DB2 9 certification available.


IBM Certified Database Associate — DB2 9 Fundamentals

The IBM Certified Database Associate — DB2 9 Fundamentals certification is intended for entry-level DB2 9 users who are knowledgeable about the fundamental concepts of DB2 9 for Linux, UNIX, and Windows, DB2 9 for zSeries (OS/390), or DB2 9 for iSeries (AS/400). In addition to having some hands-on experience, some formal training, or both on DB2 9, individuals seeking this certification should:

• Know what DB2 9 products are available and be familiar with the various ways DB2 9 is packaged

• Know what DB2 9 products must be installed in order to create a desired environment

• Know what features and functions are provided by the various tools that are shipped with DB2 9

• Possess a strong knowledge about the mechanisms DB2 9 uses to protect data and database objects against unauthorized access and/or modification

• Know how to create, access, and manipulate basic DB2 objects, such as tables, views, and indexes

• Be familiar with the different types of constraints that are available and know how each is used

• Be familiar with how XML data can be stored and manipulated

• 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 9

• Have a basic understanding of the methods used to isolate transactions from each other in a multi-user environment

• Be familiar with the methods used to control how locking is performed


In order to acquire the IBM Certified Database Associate — DB2 9 Fundamentals certification, candidates must take and pass one exam: the DB2 9 Family Fundamentals exam (Exam 730). The roadmap for acquiring the IBM Certified Database Associate — DB2 9 Fundamentals certification is illustrated in Figure 1-1.


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

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

• Know how to configure and manage DB2 9 instances

• Know how to configure client/server connectivity

• Be able to obtain and modify the values of environment/Registry variables

• Be able to obtain and modify DB2 Database Manager (instance) and database configuration file parameter values

• Know how to use Automatic Maintenance and self-tuning memory

• Know how to create DB2 9 databases

• Possess a strong knowledge about SMS, DMS, and Automatic Storage table spaces, as well as be familiar with the management requirements of each

• Know how to create, access, modify, and manage the different DB2 objects available

• Know how to manage XML data

• Be able to create constraints on and between table objects

• Know how to capture and interpret snapshot monitor data

• Know how to create and activate event monitors, as well as capture and interpret event monitor data

• Know how to capture and analyze Explain information

• Know how to use the DB2 Control Center and other GUI tools available to manage instances and databases, create and access objects, create tasks, schedule jobs, and view Explain information

• Possess an in-depth knowledge of the EXPORT, IMPORT, and LOAD utilities

• Know how to use the REORGCHK, REORG, REBIND, RUNSTATS, db2look, db2move, and db2pd utilities

• Know how to perform database-level and table space-level backup, restore, and roll-forward recovery operations

• Have a basic understanding of transaction logging

• Be able to interpret information stored in the administration notification log

• Possess a strong knowledge about the mechanisms DB2 9 uses to protect data and database objects against unauthorized access and/or modification


Candidates who have either taken and passed the DB2 V8.1 Family Fundamentals exam (Exam 700) or acquired the IBM Certified Database Administrator — DB2 V8.1 for Linux, UNIX, and Windows certification (by taking and passing Exams 700 and 701) must take and pass the DB2 9 for Linux, UNIX, and Windows Database Administration exam (Exam 731) to acquire the IBM Certified Database Administrator — DB2 9 for Linux, UNIX, and Windows certification. All other candidates must take and pass both the DB2 9 Family Fundamentals exam (Exam 730) and the DB2 9 for Linux, UNIX, and Windows Database Administration exam (Exam 731). The roadmap for acquiring the IBM Certified Database Administrator — DB2 9 for Linux, UNIX, and Windows certification can be seen in Figure 1-2.

Candidates who already hold the IBM Certified Database Administrator — DB2 V8.1 for Linux, UNIX, and Windows certification may opt to take the DB2 9 for Linux, UNIX, and Windows Database Administration Upgrade exam (Exam 736) to acquire the IBM Certified Database Administrator — DB2 9 for Linux, UNIX, and Windows certification. This exam, which is half the length and half the cost of the DB2 9 for Linux, UNIX, and Windows Database Administration exam (Exam 731), is designed to test a candidate's knowledge of the new features and functions that are provided in DB2 9. Essentially, the upgrade exam provides certified DB2 Version 8.1 DBAs an accelerated approach for acquiring an equivalent Version 9 certification. This accelerated approach is outlined in Figure 1-3.


IBM Certified Database Administrator — DB2 9 for z/OS

The IBM Certified Database Administrator — DB2 9 for z/OS certification is intended for experienced DB2 9 users who possess the knowledge and skills necessary to perform the day-to-day administration of DB2 9 instances and databases residing on OS/390 platforms. In addition to being knowledgeable about the fundamental concepts of DB2 9 and having significant hands-on experience as a DB2 9 Database Administrator, individuals seeking this certification should:

• Know how to convert a logical database design to a physical database design

• Know how to create, access, modify, and manage the various DB2 9 objects available

• Know how to interpret the contents of system catalogs and directories

• Possess a strong knowledge about the activities associated with enabling stored procedures

• Be familiar with the different types of constraints available and know how each is used

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

• Know the difference between static and dynamic SQL

• Know how to manage storage allocation with tools such as VSAM DELETE, VSAM DEFINE, and STOGROUP

• Be familiar with DB2 Disaster Recovery

• Possess a basic understanding of the different object statuses available (for example: RECP, GRECP, LPL, and RESTP)

• Be able to describe the effects of COMMIT frequency

• Know how to capture and analyze Explain information

• Know how to capture and analyze DB2 Trace data

• Be able to determine the best characteristics for an index

• Be able to describe the benefits of data sharing

• Be able to describe the features that enable around-the-clock availability

• Know how to use the REORG, BIND, REPAIR, UNLOAD, RUNSTATS, LOAD, and MODIFY utilities, including being able to restart a failed utility

• Know how to use the DISPLAY, START, STOP, ALTER, RECOVER, and TERM UTILITY commands

• Possess a basic understanding of the CHECK DATA/INDEX/LOB utility

• Be able to demonstrate how DB2I is used

• Be able to identify the functions of the Control Center

• Possess a strong knowledge about the mechanisms DB2 9 uses to protect data and database objects against unauthorized access and modification


Candidates who have either taken and passed the DB2 V8.1 Family Fundamentals exam (Exam 700) or acquired the IBM Certified Database Administrator — DB2 V8.1 for z/OS and OS/390 certification (by taking and passing Exams 700 and 702) must take and pass the DB2 9 for z/OS Database Administration exam (Exam 732) to acquire the IBM Certified Database Administrator — DB2 9 for z/OS certification. All other candidates must take and pass both the DB2 9 Family Fundamentals exam (Exam 730) and the DB2 9 for z/OS Database Administration exam (Exam 732). The roadmap for acquiring the IBM Certified Database Administrator — DB2 9 for z/OS certification can be seen in Figure 1-4.


IBM Certified Application Developer — DB2 9 Family

The IBM Certified Application Developer — DB2 9 Family certification is intended for intermediate- to advanced-level application developers who possess the knowledge and kills necessary to create applications that interact with DB2 9 databases residing on supported platforms, including Linux, AIX, HP-UX, Sun Solaris, Windows, zSeries (z/OS, OS/390), and iSeries (AS/400). In addition to being knowledgeable about the fundamental concepts of DB2 9 and having strong skills in embedded SQL programming, ODBC/CLI programming, JDBC programming, or SQLJ programming, individuals seeking this certification should:

• Be familiar with the naming conventions used to identify DB2 9 objects

• Know what authorities and privileges are needed in order to access data with an application

• Possess an in-depth knowledge of the complex database objects available with DB2 9

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

• Know the difference between static and dynamic SQL

• Possess an in-depth knowledge of the SQL functions available

• Know when to use Embedded SQL, CLI/ODBC, JDBC, SQLJ, PHP, PERL, PYTHON, .NET, and XML

• Be able to query tables across multiple tables and views

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

• Be able to work with materialized query tables (MQTs)

• Be able to identify the results of XML parsing and XML serialization

• Possess an in-depth knowledge of XML document encoding management

• Know how XML schemas are validated

• Be able to execute and identify the results of an XQuery expression

• Be familiar with the SQL/XML functions that are available with DB2 9

• Be able to establish a connection to a database within an Embedded SQL, CLI/ODBC, JDBC, SQLJ, or .NET application

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

• Possess the ability to obtain and analyze ODBC/CLI diagnostic information

• Possess the ability to obtain and analyze JDBC trace, SQL exception, and JDBC error log information

• Possess the ability to obtain and analyze .NET diagnostic information

• Be able to query tables across multiple databases, including federated databases

• Possess the ability to create triggers and identify their results

• Know how to cast data types

• Know when to use Compound SQL, parameter markers, and distributed units of work

• Know when to use user-defined functions (UDFs) and stored procedures

• Know how to create UDFs and stored procedures

• Be familiar with the DB2 Developer Workbench


Candidates who have either taken and passed the DB2 V8.1 Family Fundamentals exam (Exam 700) or acquired the IBM Certified Application Developer — DB2 V8.1 Family certification (by taking and passing Exams 700 and 703) must take and pass the DB2 9 Family Application Development exam (Exam 733) to acquire the IBM Certified Application Developer — DB2 9 Family certification. All other candidates must take and pass both the DB2 9 Family Fundamentals exam (Exam 730) and the DB2 9 Family Application Development exam (Exam 733). The roadmap for acquiring the IBM Certified Application Developer — DB2 9 Family certification can be seen in Figure 1-5.


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

The IBM Certified Advanced Database Administrator — DB2 9 for Linux, UNIX, and Windows certification is intended for lead Database Administrators who possess extensive knowledge about DB2 9 and who have extensive experience using DB2 9 on one or more of the following supported platforms: Linux, AIX, HP-UX, Sun Solaris, and Windows. In addition to being knowledgeable about the more complex concepts of DB2 9 and having significant experience as a DB2 9 Database Administrator, individuals seeking this certification should:

• Know how to design, create, and manage both SMS and DMS tablespaces

• Know how to design, create, and manage buffer pools

• Be able to take full advantage of intrapartition parallelism and interpartition parallelism

• Be able to design and configure federated database access

• Know how to manage distributed units of work

• Be able to develop a logging strategy

• Be able to create constraints on and between table objects

• Know how to perform database-level and table-space-level backup, restore, and roll-forward recovery operations

• Be able to use the advanced backup and recovery features available

• Know how to implement a standby database using log shipping, replication, failover, and fault monitoring

• Be able to identify and modify the DB2 Database Manager and database configuration file parameter values that have the most impact on performance

• Possess a strong knowledge of query optimizer concepts

• Be able to correctly analyze, isolate, and correct database performance problems

• Know how to manage a large number of users and connections, including connections to host systems

• Know how to create, configure, and manage a partitioned database spanning multiple servers

• Be able to create and manage multidimensional clustered tables

• Know when the creation of an index will improve database performance

• Be able to identify and resolve database connection problems

• Possess a strong knowledge about the external authentication mechanisms DB2 9 uses to protect data and database objects against unauthorized access and/or modification

• Know how to implement data encryption using Label-Based Access Control (LBAC)


To acquire the IBM Certified Advanced Database Administrator — DB2 9 for Linux, UNIX, and Windows certification, candidates must hold the IBM Certified Database Administrator — DB2 9 for Linux, UNIX, and Windows certification, and they must take and pass the DB2 9 for Linux, UNIX, and Windows Advanced Database Administration exam (Exam 734). The roadmap for acquiring the IBM Certified Advanced Database Administrator — DB2 9 for Linux, UNIX, and Windows certification can be seen in Figure 1-6.


IBM Certified Solution Designer — DB2 Data Warehouse Edition V9.1

The IBM Certified Solution Designer — DB2 Data Warehouse Edition V9.1 certification is intended for individuals who are knowledgeable about the fundamental concepts of IBM's DB2 Data Warehouse Edition (DWE), Version 9.1. In addition to having the knowledge and skills necessary to design, develop, and support DB2 data warehouse environments using DB2 DWE, anyone seeking this certification should:

• Be able to explain how data warehouse and front-end analytics impact Business Intelligence Analytics architecture

• Know the difference between a multidimensional database and a relational database warehouse Figure 1–6: IBM Certified Advanced Database Administrator — DB2 9 for Linux, UNIX, and Windows certification roadmap.

• Know how to manage storage allocation with tools such as VSAM DELETE, VSAM DEFINE, and STOGROUP

• Be familiar with DB2 Disaster Recovery

• Possess a basic understanding of the different object statuses available (for example: RECP, GRECP, LPL, and RESTP)

• Be able to describe the effects of COMMIT frequency

• Know how to capture and analyze Explain information

• Know how to capture and analyze DB2 Trace data

• Be able to determine the best characteristics for an index

• Be able to describe the benefits of data sharing

• Be able to describe the features that enable around-the-clock availability

• Know how to use the REORG, BIND, REPAIR, UNLOAD, RUNSTATS, LOAD, and MODIFY utilities, including being able to restart a failed utility

• Know how to use the DISPLAY, START, STOP, ALTER, RECOVER, and TERM UTILITY commands


(Continues...)

Excerpted from DB2 9 Fundamentals Certification Study Guide by Roger E. Sanders. Copyright © 2007 Roger Sanders. 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


Roger E. Sanders is the president of Roger Sanders Enterprises, Inc. He has written articles for publications such as Certification Magazine and IDUG Solutions Journal, authored tutorials for IBM's developerWorks website, and writes a regular column in DB2 Magazine.

Customer Reviews

Average Review:

Post to your social network

     

Most Helpful Customer Reviews

See all customer reviews