DB2 Universal Database V6.1 for Unix, Windows and OS/2 Certification Guide, 3rd Edition

Description

• Copyright 2000
• Dimensions: 7 X 9-1/4
• Pages: 1014
• Edition: 3rd

• Book
• ISBN-10: 0-13-086755-1
• ISBN-13: 978-0-13-086755-1

8675E-6

• The #1 guide and reference for IBM's powerhouse DB2 UDB V6.1 database
• New DB2 UDB V6.1 features for all platforms: UNIX, Windows, and OS/2!
• Complete coverage for database administrators and application developers!
• Sample test questions to assist in your preparation for IBM's new DB2 UDB V6.1 certification exams

IBM's DB2 Universal Database Version 6.1 for UNIX, Windows, and OS/2 delivers unprecedented power, scalability, e-business support, and much more! Now, there's a complete, authoritative guide to deploying, managing, and developing applications with DB2 UDB V6.1 - and preparing for IBM's updated DB2 V6.1 certification exams!

This is IBM's definitive guide and reference for the newest, most powerful version of DB2 Universal Database ever: Version 6.1. DB2 Universal Database Version 6.1 for UNIX, Windows and OS/2 Certification Guide, Third Edition delivers end-to-end coverage for every DB2 developer and administrator, on every platform: Windows, UNIX, or OS/2 - including the most systematic IBM DB2 certification review you can find.

IBM DB2 experts present a detailed overview of the DB2 UDB family of products, key DB2 concepts, and terminology, plus in-depth coverage of installation, configuration, networking, security, application development, data integrity, optimization, and more. Coverage includes:

• DB2 administration: populating tables with import and load utilities, monitoring, logging, maintenance, and tuning
• Security: Controlling data access through authentication, assigned authorities, user/group privileges, and more
• Database backup and recovery, step-by-step
• DB2 SQL, in depth: database objects, Data Definition Language (DDL), Data Manipulation Language (DML), triggers, outer joins, OLAP SQL, table hierarchies, concurrency, and embedded SQL
• Database access techniques using ODBC, JDBC, and ADO

Whether you're deploying DB2 UDB V6.1, managing DB2 UDB V6.1, preparing for DB2 UDB V6.1 certification, or all three, DB2 Universal Database Version 6.1 for UNIX, Windows, and OS/2 Certification Guide, Third Edition will be your single most valuable resource.



Sample Content

Downloadable Sample Chapter

Click here for a sample chapter for this book: 0130867551.pdf

Table of Contents

I. INTRODUCTION TO DB2 UDB.

1. Product Overview.

DB2 Universal Database—The Scalable Database. DB2 Universal Database Products. DB2 Universal Database. DB2 UDB Personal Edition. DB2 UDB Workgroup Edition. DB2 UDB Enterprise Edition. DB2 UDB Enterprise-Extended Edition. DB2 Connect. DB2 Connect Personal Edition. DB2 Connect Enterprise Edition. DB2 Developer's Edition. DB2 Satellite Edition. Satellite Components. Logical Components. Model Office. Maintenance of Satellite Systems. DB2 Data Links Manager. DB2 Data Links Manager System Overview. Components of a Data Links Server. Data Links Manager Facilities. DB2 Query Patroller. DB2 Licensing. Client/Server Communication Protocols. DB2 UDB Resource Usage. Features and Functions. The Control Center. Other Tools Available from the Control Center. The SmartGuides. Other DB2 UDB Tools. The Command Line Processor (CLP). Summary. Questions. Answers.

2. Getting Started.

Product Installation. Windows NT/9x and OS/2 Installation. UNIX Installation. DB2 Client Installation. Distributed Installation. The DB2 UDB Environment. DB2 Profile Registry. Environment Variables. Using the Command Line Processor. DAS and DB2 Instances. DB2 Administration Server (DAS) Instance. DB2 Instances. Creating the Sample Database. Using the Command Line. Using First Steps. Summary. Questions. Answers. Exercises.

3. Getting Connected.

Roadmap to Client/Server Communications. Automated Configuration using Discovery. Automated Configuration Using Access Profiles. Manual Configuration. DB2 Discovery. DB2 Directories. Examining DB2 UDB Directories. Configuring DB2 Clients. Automated Configuration using Discovery. Using Access Profiles. Manual Configuration. Summary of Configuring Connections. Configuring Communications. Configuring the DB2 Instance for Communications. Configuring the DAS Instance. Configuring DB2 Discovery. Binding Utilities. Binding Utilities Using the CCA. Instance Administration. Local Instance Administration. Attaching to an Instance using the Control Center. Summary. Questions. Answers.

4. Controlling Data Access.

Security. Overview of Security. Authentication. Authorities and Privileges. Windows NT Considerations. Auditing. Summary. Questions. Answers. Exercises.

II. USING SQL.

5. Database Objects.

Understanding Database Objects. Data Types. Tables. Schemas. Table Spaces. Views. Indexes. Packages. Buffer Pools. Transactions. Locks. Log Files. Creating a DB2 UDB Database. Managing Database Objects. Using SQL Data Definition Language (DDL). Data Types. Tables. Views. Indexes. Database Design and Implementation. DB2CERT Database Table Descriptions. Define User-Defined Data Types. Defining Columns. Keys. Defining Primary Keys. Defining Unique Keys. Defining Foreign Keys. Summary. Questions. Answers. Exercises.

6. Manipulating Database Objects.

Data Retrieval. Retrieving the Entire Table. Projecting Columns from a Table. Changing the Order of the Columns. Restricting Rows from a Table. Predicate Evaluation for UDTs. Restricting Rows Using Multiple Conditions. Selecting Columns from Multiple Tables. Using Correlation Names. Sorting Your Output. Derived Columns. DB2 Functions. Grouping Values. Eliminating Duplicates. Searching for String Patterns. Searching for Data in Ranges. Searching for Null Values. Searching for Negative Conditions. Searching for a Set of Values. Sub-Queries. Quantified Predicates. Case Expressions. Nested Table Expressions. Scalar Fullselect. Common Table Expressions. Set Operators. Data Modification. Inserting Data Records. Updating Data Records. Removing Data. View Classification. Deletable Views. Updatable Views. Insertable Views. Read-Only Views. Inoperative views. Summary. Questions. Answers. Exercises.

7. Advanced SQL.

Triggers. Trigger Usage. Trigger Activation. Trigger Example (After Trigger). Trigger Example (Before Trigger). Recursive SQL. Outer Join. Left Outer Join. Right Outer Join. Full Outer Join. Combining Outer Joins. OLAP Features. STAR Schemas. OLAP Indexes. STAR Joins. Super Grouping. Advanced CASE Expressions. Using CASE expressions to Group Values. Using CASE expressions in Functions. Structured Types and Typed Tables. Creating Structured Types. Altering Structured Types. Creating Typed Tables. Dropping Typed Tables. Inserting Rows into a Typed Table. Selecting a Row from a Typed Table. Updating and Deleting Rows from Typed Tables. Physical Implementation of Typed Tables. Reference Columns. View Hierarchies. SQL Functions for typed tables and typed views. TYPE Predicate. Considerations when using Typed tables and views. Examples of a Typed Table Hierarchy. Summary Tables. Creating a summary table. CURRENT REFRESH AGE Special Register. Considerations of using Summary Tables. System Catalog Information for Summary Tables. Summary. Exercises.

8. Concurrency.

Concurrency. Concurrency Considerations. Isolation Levels. Uncommitted Read. Cursor Stability. Read Stability. Repeatable Read. Choosing an Isolation Level. Locking. Lock Attributes. Lock Conversion. Lock Escalation. Lock Wait Behavior. Lock Table Statement. LOCKSIZE parameter of ALTER TABLE statement. Summary. Questions. Answers. Exercises.

III. DB2 UDB ADMINISTRATION.

9. Data Storage Management.

Buffer Pools. Table Spaces. SMS Table Spaces. DMS Table Spaces. Regular Table Spaces. Long Table Spaces. Temporary Table Spaces. Containers. Directory Containers. Device Containers. File Containers. Extents. Pages. Long Field Data. Large Object Data. Planning and Design. Sizing the Processor, Disk and Memory. Table Space Design. Implementation. Creating a Database. Creating Buffer Pools. Creating Table Spaces. Creating Tables. Dropping Table Spaces. Dropping Buffer Pools. Dropping a Database. Creating Table Spaces Using the Control Center. Table Space Maintenance. Database Files. Listing Table Spaces. Listing Table Space Containers. Table Space States. System Catalog Information About Table Spaces. Adding Containers to DMS Table Spaces. Summary. Questions. Answers. Exercises.

10. Maintaining Data.

Moving data. Delimited ASCII files. Non-Delimited ASCII Files. PC/IXF Files. Worksheet Format Files. Data Movement Utilities. The Export Utility. The IMPORT Utility. The Load Utility. The LOAD QUERY Command. The SET INTEGRITY Statement. The DB2MOVE Utility. The DB2LOOK Utility. Data Maintenance. Analyzing Data's Physical Organization. Table Reorganization. Generating Statistics. The Rebind Utility. Data Maintenance Process. Modelling a Production Environment. Summary. Questions. Answers. Exercises.

11. Database Recovery.

Database Recovery Concepts. Unit of Work. Transaction. Types of Recovery. Recovery Strategies. Recoverable and Non-Recoverable Databases. Online and Offline Access. Use of Log Files. Log Buffers. Primary and Secondary Log Files. Types of Logging. Log File Usage. Version Recovery using Backup and Restore. Roll-Forward Recovery. Managing Log Files. Other Recovery Considerations. Summary. Questions. Answers. Exercises.

12. Monitoring and Tuning.

Elements of Performance. Tuning Guidelines. Performance Improvement Process. How Much Can a System Be Tuned? A Less Formal Approach. Monitoring Methods in DB2 UDB. Obtaining Database Access Information. Explain Facilities. Overview of the SQL Compiler. Explain Tables. Gathering Explain Data. Examining EXPLAIN Data. Guidelines on Using EXPLAIN Output. Indexes Advisor Facility. Database Monitoring. Snapshot Monitoring. Event Monitoring. Visual Performance Monitors. DB2 Governor. Performance Issues. Configuring Database Resources. DB2 Sorting Methods. DB2 Server Resources. Query Parallelism. Intra-Partition and Inter-Partition Parallelism. Setting Up Intra-Partition Parallelism. Performance Tuning Scenario. Diagnostics and Problem Determination. Error Messages and SQL Codes. Tracing Problems in DB2 Universal Database. Summary. Questions. Answers. Exercises.

IV. DEVELOPING APPLICATIONS.

13. Programming Overview.

DB2 UDB Application Development Environment. Software Requirements. Application Development Considerations. Choosing a DB2 UDB Programming Interface. Embedded SQL. Call Level Interface and ODBC. Java Interfaces (JDBC and SQLJ). Native DB2 UDB APIs. Microsoft Data Objects (DAO, RDO, ADO). Other Interfaces and Tools. Choosing DB2 UDB Server-Side Features. DB2 UDB Application Overview. Using Schemas and Aliases. Schema. Alias. Summary. Questions. Answers.

14. Embedded SQL Programming.

Embedded SQL Overview. Creating Packages. Binding Applications. Static Embedded SQL. Host Variables. Connecting to Databases. Error Handling—Using and Interpreting the SQLCA. Error Handling—Message Retrieval. Indicator Variables. Data Retrieval Methods. Using Cursors. Dynamic Embedded SQL. First Look at Dynamic SQL. Dynamic SQL Phases. Other Concepts. Types of Dynamic SQL Statements. SQLDA Data Structure. Summary. Questions. Answers. Exercises.

15. Other Programming Methods.

Support for CLI and ODBC Programming. Embedded Dynamic versus Call Level Interface. ODBC versus CLI. Setting Up the CLI Environment. Coding a DB2 UDB CLI Application. Support For Java Programming. JDBC Programming. SQLJ Programming. Building, Distributing and Running Java Programs. Other Application Interfaces. Programming with ActiveX Data Objects. Advanced DB2 UDB Programming Features. Stored Procedures. User Defined Functions. Large Objects. Compound SQL. Summary. Questions. Answers. Exercises.

V. APPENDICES.

Appendix A: DB2 UDB V.

Certification Test Objectives. DB2 UDB V6.1 Fundamentals. DB2 UDB V6.1 Database Administration. DB2 UDB V6.1 Application Development.

Appendix B: Example DB2 UDB Source Code.
Index.
CD-ROM Installation.

Preface

Preface

This book is a complete guide to the IBM's relational database servers, known as DB2 Universal Database Version 6.1. DB2 Universal Database (DB2 UDB) Version 6.1 is available on many operating systems, and the book has been written with this in mind. Any significant differences in the implementation of DB2 UDB on various operating systems are highlighted. If you are planning to become certified, or you would simply like to understand the powerful new DB2 UDB 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 UDB.

The book is divided into 4 parts:

• Part 1 — Introduction to DB2 UDB (Chapters 1-4).
  Installing and configuring DB2 UDB servers and clients are covered in Chapters 1, 2, 3, and 4.
• Part 2 — Using SQL (Chapters 5-8).
  The Structured Query Language (SQL) is discussed in Chapters 5, 6, and 7. Database concurrency is discussed in Chapter 8.
• Part 3 — DB2 UDB Database Administration (Chapters 9-12).
  Creating a DB2 UDB database and its related table spaces is covered in Chapter 9. The common administration tasks are discussed in Chapters 10 and 11. Database monitoring and performance considerations are discussed in Chapter 12.
• Part 4 — Developing Applications with DB2 UDB (Chapters 13-15).
  An introduction to application development is given in Chapter 13. Developing embedded SQL applications is covered in Chapter 14. Alternate DB2 UDB programming interfaces, including Call Level Interface (CLI), JDBC and SQLJ are discussed in Chapter 15.

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

This book can be used as a self-study guide to help you prepare for the DB2 Universal Database V6.1 certification exams or as a guide to DB2 Universal Database V6.1.

The path to certification involves successfully completing these exams:

The test objectives are provided in Appendix A. These should be used as a guide to ensure that you are fully prepared to take the DB2 UDB V6.1 exams.

Experience with DB2 Universal Database Version 6.1 is the best way to prepare for any of these DB2 UDB V6.1 certification exams. Use this Certification Guide in conjunction with your day-to-day use of DB2 UDB V6.1 to assist you in preparing for these exams.

The DB2 UDB V6.1 Fundamentals Exam (509) 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 test objectives of this exam, see "DB2 UDB V6.1 Fundamentals (509)" on page 965.

The DB2 UDB V6.1 Database Administration Exam (510) 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 test objectives of this exam, see "DB2 UDB V6.1 Database Administration (510)" on page 968.

The DB2 UDB V6.1 Application Development Exam (511) 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 test objectives of this exam, see "DB2 UDB V6.1 Application Development (511)" on page 970.

Note: More information about DB2 UDB certification can be found at http://www.ibm.com/certify.

How this Book was Created

This book was a joint effort between the Austin ITSO (International Technical Support Organization) and the IBM Toronto Lab. 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.

Note: ITSO redbooks — http://www.redbooks.ibm.com/redbooks

The DB2 Universal Database Certification Guide was updated for Version 6.1, making this the third edition. Many people from all over the world assisted with their expertise:

• Jonathan Cook is the DB2 Project Leader at the International Technical Support Organization (ITSO), Austin Center. He has ten years of experience as a database specialist working in the areas of application development and database administration. He has been with IBM since 1992, working in both the United Kingdom and France before joining the Austin ITSO. He writes extensively and teaches IBM classes worldwide on DB2 for the UNIX and Intel platforms.
• Robert Harbus is the DB2 Universal Database Certification Program Manager at the IBM Toronto Lab. A member of the DB2 UDB team since its inception in 1991, Robert provided Technical Marketing and electronic customer support and direct customer support most recently as a member of the DB2 Universal Database Enterprise - Extended Edition (EEE) support team. Robert is currently responsible for the DB2 UDB certification program and testing, and works with the ITSO and IBM Learning Services to ensure that DB2 UDB education courses, training and material are available to meet the needs of DB2 UDB users. Robert teaches DB2 UDB internals and certification courses worldwide and is involved in producing certification guides, other DB2 UDB books and redbooks.
• Tetsuya Shirai is a Project Leader at the International Technical Support Organization (ITSO), Austin Center. He worked in Tokyo, Japan before joining the Austin ITSO. He has been with IBM for eight years, working for the last 4 years with DB2 UDB products. Shirai-san has worked in the area of DB2 services, teaching classes to both customers and IBMers. He has also provided consulting services to DB2 customers worldwide.
• Ravdeep S. Ahuja has been working with DB2 since he first joined the IBM Toronto Lab in 1994. He currently provides technical support and services for DB2 cross-platform and DRDA connectivity to customers all over the world. Ravdeep is an IBM Certified Solutions Expert in DB2 UDB Database Administration and Application Development. He is also a Net.Data specialist and an expert in the area of Internet related database access.
• John Goh works for IBM Global Services in Sydney, Australia. He has many years of experience of working with DB2 UDB, including the partitioned database version, DB2 UDB Enterprise-Extended Edition.
• Jan Marais joined IBM South Africa in 1996 after completing a degree in electronical engineering. He stared training on DB2 Common Server that same year. Since then he has presented various courses on DB2 as well as provided technical support to customers as part of the Product Support Services group in South Africa.
• Chihoko Nakajima — Chihoko Nakajima has worked with DB2 Universal Database for last 5 years. She is working for the IBM Japan Technical Support organization and provides support for customers using DB2 UDB on the PC and UNIX (serial and parallel) platforms.

The following people worked on the second edition of this book:

Calene Janacek

Calene was 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 was responsible for producing technical manuals called "redbooks" that are used worldwide by support personnel and customers. She also developed workshops for new releases of DB2 that she taught worldwide. She has presented at DB2 and AIX technical conferences.

Dwaine Snow

Dwaine Snow has worked with DB2 Universal Database and its predecessors for the past 7 years. During this time, he has written and designed applications using DB2. He has also worked in the DB2 Universal Database Customer Service Team helping DB2 customers. Dwaine was the DB2 Universal Database Certification and Education coordinator. In this role, he was responsible for the DB2 Universal Database certification program and tests, as well as the availability of IBM courses required to develop DB2 UDB expertise. Dwaine has written a number of DB2-related articles and has contributed to a number of DB2 related redbooks. He has presented at DB2 conferences and has taught DB2 Universal Database related courses worldwide.

Finn McCann

Finn McCann worked in the IBM Data Management Center of Competence, Dublin, Ireland. He has two years experience working in DB2 Common Server pre-sales technical support. He is IBM Certified as both a DB2 Common Server Database Administrator and Application Developer.

Gabriel Banuelos

Gabriel worked on the first edition of the book. He works for Infomedia, a database consulting firm in Mexico. He has been a database specialist working with DB2 since 1991. He works primarily with DB2 on UNIX platforms, DB2 Connect and other related products. Gabriel has also participated in DSS and data warehousing benchmarks and pilot projects for the financial industry in Mexico.

Adriana Gonzalez

Adriana Gonzalez has been working for IBM since 1991. During this time, she has been working with DB2 and has participated in several customer projects. She was a Data Management Product Specialist in charge of the DB2 Family and related products for IBM Mexico.

Rubina Goolamhussen

Rubina is a Data Management Products support specialist in IBM Portugal. She supports DB2 for Intel and UNIX platforms. She has worked with related DB2 Products for 2 years.

Nancy Miller

Nancy Miller is a part of IBM's Personal Solutions System Center, which performs services and support for DB2 and many other products on the Intel platforms. She been working with and supporting DB2 since 1992.

Lonnie Hostin

Lonnie is a DB2 Specialist in the Personal System Solutions Center (PSSC) in Dallas, TX. Lonnie has been with IBM since 1983. Lonnie and the PSSC team work closely with marketing to provide skills transfer and educational services to customers. He also provides service skills such as database performance and tuning, DB2 DRDA connectivity, and database migration.

The DB2 Universal Database Certification Guide (for Version 5) was an update of the DB2 Certification Guide for Common Servers. Calene Janacek and Gabriel Banuelos worked on the first release — the other authors were:

Grant Hutchison

Grant is out of 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. Grant has been a speaker at IDUG (International DB2 Users Group).

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.



Updates

Submit Errata

