DB2 Universal Database V8.1 Certification Exam 700 Study Guide

One of the biggest trends in the IT industry today is certification. Many application/software vendors now have in place certification programs designed to evaluate and validate an individual's proficiency with their 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 designed to guide you in your professional development. You simply select the role that's right for you, 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 sections are designed to help get you started by providing you with the prerequisites and requirements associated with each DB2 UDB Version 8.1 certification available.

IBM Certified Database Associate ”DB2 Universal Database V8.1 Family

The IBM Certified Database Associate ”DB2 Universal Database V8.1 Family certification is intended for entry-level DB2 UDB users who are knowledgeable about the fundamental concepts of DB2 UDB, Version 8.1 or DB2 UDB for iSeries (AS/400), V5R2. In addition to having some hands-on experience with and/or some formal training on DB2 UDB, Version 8.1 and/or DB2 UDB for iSeries (AS/400), V5R2, individuals seeking this certification should:

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

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

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

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

• Be familiar with the different types of constraints that are 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.

• Have a basic understanding of the methods used to isolate transactions from each other in a multiuser environment.

• Be familiar with the methods used to control whether a transaction acquires row-level or table-level locks.

In order to acquire the IBM Certified Database Associate ”DB2 Universal Database V8.1 Family certification, candidates must take and pass one exam: the DB2 UDB V8.1 Family Fundamentals exam (Exam 700). The roadmap for acquiring the IBM Certified Database Associate ”DB2 Universal Database V8.1 Family certification can be seen in Figure 1-1.

IBM Certified Database Administrator ”DB2 Universal Database V8.1 for Linux, UNIX, and Windows

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

• Know how to configure and manage DB2 UDB instances.

• Know how to configure client/server connectivity.

• Know how to create DB2 UDB databases.

• Possess a strong knowledge about SMS and DMS tablespaces, as well as be familiar with the management requirements of each.

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

• Be able to create constraints on and between table objects.

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

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

• Be able to obtain and modify DB2 Database Manager and database configuration file parameter values.

• Know how to capture and analyze Explain information.

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

• Be familiar with the functions of the DB2 Governor and the Query Patroller.

• Know how to capture and interpret snapshot monitor data.

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

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

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

• Know how to perform database-level and tablespace-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.

Candidates who have either taken and passed the DB2 UDB V7.1 Family Fundamentals exam (Exam 512) or acquired the IBM Certified Solutions Expert ”DB2 UDB V7.1 Database Administration for UNIX, Windows, and OS/2 certification must take and pass the DB2 UDB V8.1 for Linux, UNIX, and Windows Database Administration exam (Exam 701) to acquire the IBM Certified Database Administrator ”DB2 Universal Database V8.1 for Linux, UNIX, and Windows certification. All other candidates must take and pass both the DB2 UDB V8.1 Family Fundamentals exam (Exam 700) and the DB2 UDB V8.1 for Linux, UNIX, and Windows Database Administration exam (Exam 701). The roadmap for acquiring the IBM Certified Database Administrator ”DB2 Universal Database V8.1 for Linux, UNIX, and Windows certification can be seen in Figure 1-2.

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

IBM Certified Database Administrator ”DB2 Universal Database V8.1 for z/OS and OS/390

The IBM Certified Database Administrator ”DB2 Universal Database V8.1 for z/OS and OS/390 certification is intended for experienced DB2 UDB users who possess the knowledge and skills necessary to perform the day-to-day administration of DB2 UDB, Version 8.1 instances and databases residing on z/OS and OS/390 platforms. In addition to being knowledgeable about the fundamental concepts of DB2 UDB, Version 8.1 and having significant hands-on experience as a DB2 UDB 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 UDB 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 UDB, Version 8.1.

• 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 UDB uses to protect data and database objects against unauthorized access and/or modification.

Candidates who have either taken and passed the DB2 UDB V7.1 Family Fundamentals exam (Exam 512) or acquired the IBM Certified Solutions Expert ”DB2 UDB V7.1 Database Administration for OS/390 certification must take and pass the DB2 UDB V8.1 for z/OS Database Administration exam (Exam 702) to acquire the IBM Certified Database Administrator ”DB2 Universal Database V8.1 for z/OS and OS/390 certification. All other candidates must take and pass both the DB2 UDB V8.1 Family Fundamentals exam (Exam 700) and the DB2 UDB V8.1 for z/OS Database Administration exam (Exam 702). The roadmap for acquiring the IBM Certified Database Administrator ”DB2 Universal Database V8.1 for z/OS and OS/390 certification can be seen in Figure 1-4.

IBM Certified Application Developer ”DB2 Universal Database V8.1 Family

The IBM Certified Application Developer ”DB2 Universal Database V8.1 Family certification is intended for intermediate- to advanced-level application developers who possess the knowledge and skills necessary to create applications that interact with DB2 UDB for iSeries (AS/400), V5R2, and/or DB2 UDB, Version 8.1 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 UDB, Version 8.1 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 UDB objects.

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

• 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 UDB, Version 8.1 or DB2 UDB for iSeries (AS/400), V5R2.

• Know the difference between static and dynamic SQL.

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

• Know when to use ODBC/CLI, JDBC, SQLJ, OLE DB, and embedded SQL.

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

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

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

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

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

• Be able to cast user-defined data types (UDTs) within an application.

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

• Be familiar with the steps involved in creating an embedded SQL application.

• Know when to declare host variables, as well as how to use host variables in a query.

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

• Be familiar with the steps involved in creating an ODBC/CLI application.

• Be familiar with the different ODBC/CLI handle types available.

• Know how to configure a DB2 ODBC driver.

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

• Know the correct sequence for calling ODBC/CLI functions.

• Be familiar with the steps involved in creating a JDBC application.

• Be familiar with the steps involved in creating an SQLJ application.

• Know the difference between JDBC and SQLJ.

• Be familiar with the various JDBC objects available.

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

• Know how to manage transactions across multiple databases using JTA.

• Be familiar with the DB2 Development Center.

Candidates who have either taken and passed the DB2 UDB V7.1 Family Fundamentals exam (Exam 512) or acquired the IBM Certified Solutions Expert ”DB2 UDB V7.1 Family Application Development certification must take and pass the DB2 UDB V8.1 Family Application Development exam (Exam 703) to acquire the IBM Certified Application Developer ”DB2 Universal Database V8.1 Family certification. All other candidates must take and pass both the DB2 UDB V8.1 Family Fundamentals exam (Exam 700) and the DB2 UDB V8.1 Family Application Development exam (Exam 703). The roadmap for acquiring the IBM Certified Application Developer ”DB2 Universal Database V8.1 Family certification can be seen in Figure 1-5.

IBM Certified Advanced Database Administrator ”DB2 Universal Database V8.1 for Linux, UNIX, and Windows

The IBM Certified Advanced Database Administrator ”DB2 Universal Database V8.1 for Linux, UNIX, and Windows certification is intended for lead Database Administrators who possess extensive knowledge about DB2 UDB, Version 8.1 and who have extensive experience using DB2 UDB 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 UDB, Version 8.1 and having significant experience as a DB2 UDB 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 intra-parallelism and inter-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 tablespace-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 tune memory and I/O.

• 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 UDB uses to protect data and database objects against unauthorized access and/or modification.

• Know how to implement data encryption.

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

IBM Certified Solution Designer ”Business Intelligence V8.1

The IBM Certified Solution Designer ”Business Intelligence V8.1 certification is intended for individuals who are knowledgeable about IBM's Business Intelligence solutions, as well as the fundamental concepts of DB2 Universal Database, Version 8.1. In addition to having the knowledge and skills necessary to design, develop, and support Business Intelligence applications, individuals seeking this certification should:

• Be familiar with Business Intelligence terms, as well as be able to describe the benefits of Business Intelligence.

• Know how the characteristics and purpose of data marts differ from those of data warehouses.

• Know how the characteristics and purpose of operational data stores differ from those of data warehouses.

• Know how the characteristics and purpose of multidimensional databases differ from those of data warehouses.

• Know how network communications can impact Business Intelligence architecture.

• Be able to select the appropriate tools to perform transformation, extraction, data modeling, data cleansing, loading, and propagation when given Business Intelligence data and customer requirements.

• Be able to select the appropriate visualization and presentation techniques to use when given Business Intelligence data and customer requirements.

• Be able to select the appropriate analysis techniques to use when given Business Intelligence data.

• Be able to select the appropriate front-end features to use based on presentation, level of interactivity, Web-versus-FAT client, static versus dynamic, and user skill level when given customer requirements.

• Be able to define and distinguish metadata, as well as be able to describe metadata management processes and techniques.

• Know how to implement a metadata strategy when given Business Intelligence data and customer requirements.

• Know how to identify the business requirements of the customer as they relate to a Business Intelligence solution.

• Know how to define a customer's business goals and objectives, determine growth requirements, evaluate existing hardware and software, identify constraints, identify critical success factors, and determine availability and recovery requirements.

• Possess an in-depth knowledge of methods of extraction, methods for transformation, methods for cleansing, methods for workload balancing, methods for moving data, methods for scheduling, and methods for error detection and handling.

• Be able to differentiate between full differential capturing and snapshot capturing.

• Possess an in-depth knowledge of data warehouse management.

• Be able to design a solution for producing a searchable metadata repository for business users.

Candidates who have either taken and passed the DB2 UDB V7.1 Family Fundamentals exam (Exam 512) or acquired the IBM Certified Solutions Expert ”DB2 UDB V7.1 Business Intelligence certification must take and pass the Business Intelligence Solutions V8.1 exam (Exam 705) to acquire the IBM Certified Solution Designer ”Business Intelligence V8.1 certification. All other candidates must take and pass both the DB2 UDB V8.1 Family Fundamentals exam (Exam 700) and the Business Intelligence Solutions V8.1 exam (Exam 705). The roadmap for acquiring the IBM Certified Solution Designer ”Business Intelligence V8.1 certification can be seen in Figure 1-7.