DB2 Certification Guide for Common Servers - Softcover

Hutchison, Grant; Janacek, Calene

Softcover

ISBN 10: 0137274130 ISBN 13: 9780137274130

Publisher: Prentice Hall, 1996

Synopsis

Written for aspiring DB2 Database Administrators and Application Developers, this self-study guide covers the fundamentals of administering databases and developing applications for IBM's newest database technology. Paper. CD ROM included.

About this title

From the Back Cover

Today, obtaining professional certification requires true expertise grounded in in-depth product knowledge and skills. Written for aspiring DB2 Database Administrators and Application Developers, this self-study guide covers the fundamentals of administering and developing applications for IBM's newest database technology, DB2 Common Server.

Offering a complete guide to IBM's relational database servers, this self-paced book:

* Is divided into job roles, allowing readers to focus their efforts to their specific career choices.
* Includes exercises at the end of each chapter, providing hands-on experience with DB2.
* Incorporates sample test questions that help readers verify exam readiness.
* Features a companion CD-ROM with ready-to-run code.

Whether you're preparing for certification, or are interested in gaining a better understanding of DB2, DB2 Certification Guide for Common Servers is designed to meet the needs of today's— as well as tomorrow's—database professionals.

From the Inside Flap

Foreword
Relational database technology was invented at IBM Research over two decades ago and IBM delivered its first commercially available relational database products for mainframe and mid-range systems in the early 1980s.
The power and promise of relational technology was the ability to represent data in simple tabular form and through the powerful SQL query language and put it in the hands of business analysts and other decision makers. Over the last fifteen years, many businesses have realized that promise. The presence of DB2 has grown incredibly as the DB2 family of products has expanded to include all of the popular UNIX and PC platforms. Today tens of thousands of businesses, large and small, in all corners of the world, rely on DB2 databases to store their key corporate data assets, and thousands of dedicated database professionals are charged with keeping these systems up and running 24 hours a day, 7 days a week, 365 days a year. But what we have seen over the last decade is only a small fraction of what is to come.
Data has become the addiction of the 1990s as businesses attempt to leverage it to improve the productivity of their people, provide better service and support to customers, improve overall efficiency, and reduce time to market with new products and services. All of these elements are driving the requirements for not only more business data, but also for database management systems that can store and search new data types such as text, image, time series, and spatial data. Key to achieving this will be the ability to store, organize, and search vast amounts of data—and the advent of networked computing with the Internet and intranets has accelerated the requirement for these data types while it opens up enterprise databases to a whole new class of end users. All of these requirements are placing a tremendous amount of stress on existing database professionals. There is a shortage of qualified skills and there is an increasing demand to keep up with the latest advances in database technology.
The DB2 Certification Guide for Common Servers was developed to meet the needs of database professionals throughout enterprises of all sizes. Its purpose is to guide you through the fundamentals of administering and developing applications for IBM's latest database technology, DB2 Common Server. Because it is a self-study guide, you have the flexibility to proceed at your own pace. Upon completion of the exercises and sample tests, you should be able to pass the formal certification exams for the DB2 Database Administrator role and the DB2 Application Developer role. This is a very cost-effective way for database professionals to obtain a high level of competency with DB2 as well as an effective way for enterprises to ensure that they have the right skills on board.
The next millenium will bring tremendous opportunity and challenge to database professionals charged with the task of database administration or database application development. There will be an increasing demand for skilled professionals who can deal with the complexity of administering these very large, complex data stores and a corresponding pressure within organizations to reduce training costs. Increasingly the trend will be the responsibility of the individual to keep their technical skills current.
This guide is a start. It was developed with the future in mind.

Janet Perna
Director of Database Technology
IBM Software Solutions Division Preface.

This book is a complete guide to the IBM's relational database servers, known as DB2 Version 2 for common servers. DB2 Version 2 is available on many operating systems, and the book has been written with this in mind. Any significant differences in the implementation of DB2 on various operating systems are highlighted. If you are planning to become certified, or you would simply like to understand the powerful new DB2 database servers from IBM, then read on. Those interested in becoming an IBM Certified Professional will want to review the sample questions at the end of each chapter.

There are exercises provided at the end of many of the chapters to provide hands-on use of DB2.
The book is divided into 3 main sections:

DB2 Fundamentals (Chapters 1-5)
Installing and configuring DB2 servers and clients are covered in Chapters 1, 2, and 3. The Structured Query Language (SQL) is discussed in Chapter 4 and database conurrency is discussed in Chapter 5.

DB2 Database Administration (Chapters 6-8)
Creating a DB2 database and its related table spaces is covered in Chapter 6. The common administration tasks are discussed in Chapter 7, database monitoring and performance considerations are discussed in Chapter 8.

DB2 Application Development (Chapters 9-10)
Developing a static embedded SQL application is covered in Chapter 9. Alternate DB2 programming interfaces, including Call Level Interface (CLI) and dynamic embedded SQL, are discussed in Chapters 9 and 10.

Exercises are provided at the end of most of the chapters. The exercises make references to files which can be found on the companion CD-ROM. The exercises are provided to help confirm that the concepts discussed in the chapter are understood. Any DB2 operating environment can be used to perform the exercises.

This book can be used as a self-study guide to prepare for the DB2 certification exams or as a complete guide to DB2 Version 2.
The path to certification involves successfully completing two exams:
The test objectives are provided in Appendixes A, B, and C. These should be used as a guide to ensure that you are fully prepared to take the DB2 exams.
The DB2 Fundamentals Exam (500) covers these skills:
Understanding DB2 products and components
Creating database objects
Understanding various DB2 data types
Using SQL to manipulate database objects
Describing DB2 concurrency
For more details on the contents of this exam, see DB2 Fundamentals (500)—Test Objectives.
The DB2 Database Administration Exam(501) covers these skills:
Managing DB2 instances
Creating and maintaining database objects
Managing table spaces
Using utilities: IMPORT, LOAD, REORG, RUNSTATS
Managing recovery procedures (BACKUP/RESTORE)
Analyzing resource problems

For more details on the contents of this exam, see DB2 Database Administration (501)—Test Objectives.

The DB2 Application Development exam (502) covers these skills:
Developing static embedded SQL programs
Developing dynamic embedded SQL programs
Using host variables, parameter markers, and cursors
Understanding dynamic SQL (Call Level Interface)

For more details on the contents of this exam, see DB2 Application Development (502)—Test Objectives.

Conventions

Many examples of SQL statements, DB2 commands, and operating system commands are included throughout the book. SQL statements are usually displayed within a shaded box and any of the mandatory sections of the statements are shown in uppercase. An example of an SQL statement is shown:

SQL is not a case-sensitive language, so the above query would provide the same result regardless of the case of the SQL keywords, or the database object (table names or column names). Of course the data stored in the database is stored exactly as it was entered (including case). Therefore, the above query would only find the candidates with the last name of “HUTCHISON” or “JANACEK” . If the data were stored as “Hutchison” , it would not be part of the result table.

If SQL keywords are referred to in the text portion of the book, they will be shown as a bold mono-spaced font. For example, the SELECT statement is used to retrieve data from a DB2 database.

DB2 commands will be shown using the same method as SQL keywords. For example, the CREATE DATABASE command allows you to define the initial location of database objects. DB2 commands are issued from the Command Line Processor (CLP) utility. This utility will accept the commands in upper and lowercase. The CLP program itself is an executable called db2. In some operating systems, such as AIX, the program names are case sensitive. Therefore, be careful to enter the program name using the proper case.

There are a few operating system specific commands in this book. If the commands must be in lowercase they will be shown as such. For example, the UNIX command to create a user is the mkuser command.

Occasionally,notes are provided to highlight a particular point.
A note may be used to explain a minor operating system difference or it may be used to summarize a concept.

There are a few syntax diagrams shown in the book. We recommend that the Command Line Processor be used to verify the syntax of DB2 commands. Some of the commands are shown with the complete syntax as shown in the Command Line Processor. The following is an example of complete syntax of the DB2 BACKUP DATABASE command. A syntax diagram shows how the command should be entered using the Command Line Processor. The diagram should be followed from left to right, and from top to bottom. Parameters are regarded as keywords or variables. A parameter can be a combination of a keyword and a variable. Keywords represent constants in the command. They are shown in uppercase in the syntax diagram. For example, BACKUP DATABASE is a keyword. A keyword may be entered in upperD, lowerD, or mixed case. Variables are values that are user supplied. They are indicated in the command syntax using lowercase. At the command prompt, they may be entered in either upperD, lowerD, or mixed case, unless case restrictions are explicitly stated. Some of the user-supplied variables are required, such as database-alias in the BACKUP DATABASE command. The vertical bar Ôš means that the keyword following is required. Optional parameters are indicated in the syntax with square brackets Ôš . An optional parameter may have a possible list of user-supplied input values. This is indicated with the combination of Ô{š and three periods Ô...š. For example, a user may back up multiple tablespaces using a single BACKUP DATABASE command. The DB2 SQL Reference should be used to verify the syntax of SQL statements. To emphasize a term or concept, the term is shown in bold type or emphasized with italics. If a userÐdefined column or table is being referenced it will be shown in italics. How this Book was Created This book was a joint effort between the IBM Toronto Lab and the Austin ITSO (International Technical Support Organization). The ITSO is a group within IBM whose mission is to provide skill transfer on new products and technology worldwide. We provide direct feedback to the IBM software labs as we gather input from various groups of DB2 users, including IBM support personnel, customers and business partners. The ITSO provides a working environment for interested individuals to work with new IBM software products. These individuals may include IBM employees and customers. The team develops a workshop or book, known as a redbook. -

ITSO redbooks Ñ redbooks.ibm/redbooks For the DB2 Certification Guide for Common Servers, three people came to IBM Austin, Texas for eight weeks. They brought with them different skills and knowledge, including customer experiences with DB2 common server. They were: Gabriel Banuelos Gabriel works for Infomedia, a database consulting firm in Mexico. He has been a database specialist working with DB2 since 1991. Today, he works primarily with DB2 for AIX, DDCS and other related products. Gabriel has also participated in DSS and data warehousing benchmarks and pilot projects for the financial industry in Mexico. Volker Gosch Volker is an I/T specialist at IBM Germany. He has been engaged in several projects as a DB2 specialist for OS/2 and AIX in the German Software Development Lab. He has been involved in helping many customers install and implement the Distributed Database Connection Service (DDCS) product as part of client/server solutions. Greta Mantesso Greta Mantesso is from Milan, Italy. She has worked for IBM since 1990. Greta works in the AIX Support Center supporting DB2 customers directly. She installs and customizes DB2 at a growing number of customer sites in Italy. About the Authors Grant Hutchison Grant is a Staff Development Analyst at the IBM Toronto Lab. Since joining IBM in 1991, he has been working in the DB2 service and development team. He was part of the DB2 Version 2 development team and developed the DB2 Certification Program. Currently, Grant is a member of the DB2 customer service team in Toronto and is responsible for the DB2 education and skills development for the DB2 common server products. Grant has been a speaker at IDUG (International DB2 Users Group) and occasionally teaches DB2 courses. Calene Janacek Calene is the DB2 Project Leader for the International Technical Support Organization (ITSO) in Austin, Texas. She has been with IBM since 1989, working for the ITSO since 1993. Calene is responsible for producing technical manuals called Ôredbooksš that are used worldwide by support personnel and customers. She also develops workshops for new releases of DB2 that she teaches worldwide. She has presented at DB2 and AIX technical conferences. Acknowledgments We would like to thank the following people for assisting in the creation of this book. First off, we would like to thank the ITSO assignees and fellow authors of this book: Greta, Volker and Gabriel. Without their effort and cooperation the book would not have been completed. There are many people in the IBM Toronto Lab we would like to thank for reviewing chapters and providing some sample exercises. They include: Enzo Cialini, Kim Glover, Paul Zikopoulos, Laurie Francisco, Bob Harbus, Kevin Hoang, Tanya Morley, Murray Chislett, Jim LeBlanc and Aleem Rajpar. In Austin, Frank Rusconi of the ITSO provided technical expertise. We would like to especially thank Andrea Ward, as it was her initial idea to develop a selfDstudy guide for DB2. Thanks also to one of the best DB2 instructors, Melanie Stopfer. Thanks to the team at Prentice Hall PTR. Who were great to work with and very patient as we completed this book. Calene would like to thank Peter Coldicott for his support and understanding. Grant would like to thank Maria Morianou for her patience during the summer weekends of writing this book. DB2 Database Administrator DB2 Application Developer DB2 Application Development Exam (502) DB2 Database Administration Exam (501) DB2 Fundamentals Exam (500) SELECT lname, fname FROM candidate WHERE

lname = ÔHUTCHISONšOR

lname = ÔJANACEKš

Edition details

• Publisher: Prentice Hall
• Publication date: 1996
• ISBN 10: 0137274130
• ISBN 13: 9780137274130
• Binding: Paperback
• Language: English
• Edition number: 2
• Number of pages: 585