Advanced DBA Certification Guide and Reference for DB2 ...



Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows, 1/e

ISBN:0130463884

Dwaine Snow / Tom Phan

[pic]

Abstract:

Whether you’re a long-time DB2 UDB professional or an experienced DBA who’s migrated from another database platform, there’s one sure way to demonstrate the highest level of skills in DB2 UDB database administration: pass the challenging IBM DB2 UDB Advanced Database Administration Exam.

This book is a guide to administering the advanced features of DB2 Universal Database Version 8. The book will discuss how to build a database in order to ensure optimal performance. It will discuss the manner in which to build table spaces and assign bufferpools based on the system usage and underlying disks system. The book will discuss the functionality built into DB2 to ensure system availability and will clustering and fail over discuss configuration options. The book will also discuss how to monitor a DB2 UDB system to determine if any bottle necks exist and will examine how to eliminate any potential performance issues.

Feedback for the authors: AdvancedDB2DBA@

Table of Contents:

This book contains ten chapters that have been written to assist database administrators with preparing for the exam 704, DB2 UDB V8.1 Advanced Database Administration for Linux, UNIX, and Windows, which leads to an IBM Certified Advanced Database Administrator certification. This book can also be used as an advanced DB2 UDB quick reference guide. At the end of the book there are also appendices that contain sample questions and answers to help readers review the topics discussed and prepare for the test, as well as DB2 best practices, techniques, tips, guidelines and rule of thumbs for every aspect of advanced DB2 UDB topics. Although readers may choose to read the book from beginning to end, it is not necessary to read the book in any particular order, and readers may use it as reference when dealing with a particular aspect of advanced DB2 topics:

Chapter 1 – Data Storage

Being able to store and retrieve large amount of data in order to response as effectively as possible to application requests is every database administrator dreams. To design and build a database that will meet performance expectations, the database administrator must understand all the depth on how to place the data on a particular storage. This chapter will focus in depth on the design and implementation of the physical storage model for the database.

Chapter 2 – Data Manipulation

Since most data manipulation takes place in the database buffer pools, configuring the buffer pools is the single most important tuning area for DB2 UDB databases. It is important that the buffer pools be sized correctly and assigned to the correct table spaces. This chapter will focus on the design and implementation of the buffer pools for optimal data access. We will discuss in depth on how to create, manage, and monitor of buffer pools to provide optimal performance for the database.

Chapter 3 – Database Availability

Supporting mission-critical applications to be available 24 hours a day and seven days a week (24x7) is a great challenge to every database administrator. This chapter will focus the ability to manage logs, to use advanced backup and recovery, to many features as such ability to mange logs, and to implement standby database using numerous methods. We will discuss the ability to use db2dart to verify the database architectural integrity.

Chapter 4 – Database Security

One of the most important responsibilities of the database administrator is database security. This chapter will focus on the external security services and internal access control to protect data and resources associated with a database server. We will discuss the ability to implement data encryption to ensure your data have an adequate level of protection.

Chapter 5 – Multi-Dimensional Clustering

Prior to Version 8, DB2 supported only single-dimensional clustering of data using clustering indexes. With the correct design of dimensions for the MDC tables, the performance will significantly improve. This chapter will focus the new concept of using the multi-dimensional clustering and the benefit underling clustering techniques.

Chapter 6 – The DB2 Optimizer

This chapter will focus on the different join strategies that the DB2 optimizer takes when a query is presented to it. We will discuss in depth on how you should analyze and interpret the choices that the DB2 optimizer makes to maximize the performance. We will analyze output from explain statements to understand how DB2 optimizer using the Materialized Query Table to optimal performance.

Chapter 7 – Federated Database Access

With a federated system you can send distributed requests to multiple data sources within a single SQL statement. In this chapter will focus on the concept of the federated database access. You will find many useful SQL statements, DB2 commands using step-by-step approach that will guide you through the process of planning, configuring, and administering a federated system.

Chapter 8 – Performance Tuning

Performance is the key to any successful application running databases of any size with any combination number of users. This chapter will discuss some fundamentals of performance tuning as it relates to DB2 and we will focus on how to configure DB2 for optimal performance using database and database manager configuration parameters, and DB2 registry variables, as well as how to monitor the database to ensure that the configuration parameters are configured correctly.

Chapter 9 – Database Communications

Communication protocols on the DB2 server must be configured in order to accept requests from remote DB2 clients. This chapter will focus on the concepts related to communications between DB2 clients and servers, as well as between database partitions. We will discuss in depth on how to configure a partitioned database on multiple servers, how to configure database connections to hosts systems, how to size the FCM Communication Buffers, and how to troubleshoot connection problem related to the FCM configuration.

Chapter 10 – Performance Tuning Examples

The performance tuning process is an iterative approach. This chapter will discuss some examples of typical performance tuning problems and the steps taken to resolve the problems. These examples are based on the practical experiences at real customer locations.

Appendices

Appendix A – Useful DB2 Commands

Appendix B – parseSQL.pl

Appendix C – emfmtDIF.pl

Appendix D – Comparing Index Advisor Recommended Indexes with Existing Indexes

Appendix E – Configuration Parameters That Can Be Changed Online

Appendix F – Explain Tools

Appendix G – Starting and Stopping the DB2 Instance

Appendix H – LDAP Integration in DB2 UDB Using Microsoft Active Directory

Appendix I – Tuning DB2 UDB in the IBM LDAP Environment

Appendix J – DB2 Support Information

Appendix K – DB2 Connect Implementation

Appendix L – Sample Questions

Appendix M – Answers to Sample Questions

About the Authors:

Dwaine Snow is fully certified with DB2 UDB. These certifications include:

IBM Certified Advanced Database Administrator

IBM Certified Solutions Expert - Business Intelligence

Dwaine has 12 years of experience implementing and tuning DB2 to support workloads from Web-based transactions up to multiterabyte data warehouses. Dwaine is the product manager for DB2 partitioned databases and is co-author of the following books:

The DB2 Universal Database Certification Guide

The DB2 Cluster Certification Guide

The Universal Guide to DB2 on Windows NT

DB2 Universal Database Advanced DBA Certification Guide and Reference

Tom Phan has over 14 years of experience in database development from data modeling, physical database design to database administration and database conversion on a wide variety of Open Systems platform (S/390, AIX, and Sun Solaris). He specializes in database performance and tuning, and benchmark on DB2 on UNIX platforms.

He has been certified by IBM as Information Technology Specialist in the System Products and Services discipline and he currently works for IBM Global Services as a consultant in the IBM National Data Infrastructure practice.

He has extensive experience in planning and implementing Sun Solaris Cluster and VERITAS journaling file system using multiple EMC Symmetric disk subsystems, Hitachi storage and Sun Storage Proton disk arrays. He also has experience in planning and implementing Terabytes database on the Sun Cluster.

He has reviewed many pSeries, RS/6000 SP2 and Sun E10000 Enterprise Server configurations and recommended changes to CPUs and disk layouts to optimize the disk I/O based on the customer requirements and future growth.

He has performed AIX and Solaris system kernel tuning, monitored and tuned database performance and transferred DB2 UDB Administration, AIX System Administration and Solaris System Administration skills to local staff.

He has assisted in the design and implementation of Terabytes data warehouse systems in MPP environments featuring DB2 S/390, and DB2 UDB EEE for AIX and Sun Solaris. He also has experience in the design and development of dimensional model to meet the needs of business analysts and OLAP systems. He also has experience with the ETL transformation process using Informatica v6.2 and DataStage, Parallel Extender tools.

He has developed and presented the DB2 UDB EEE Methodology at the National IBM AIX Technical Services Update. He also has taught many technical classes and planning workshops on DB2 UDB EEE related to UNIX configuration topics.

He has extensive experience in production support and automation process using PERL and Korn shell scripts. He wrote custom Korn shell scripts to perform data manipulation and automated production support.

He has successfully performed engagements for clients in a broad range of industries, including retail, manufacture, and banking.

Education:

Master of Computer Science from Bradley University

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download