The Official Introduction to DB2 for the Z/OS Version 8

The Official Introduction to DB2 for the Z/OS Version 8



Product Details

ISBN-13: 9780131477506
Publisher: IBM Press
Publication date: 05/28/2004
Series: IBM Redbooks
Edition description: REV
Pages: 496
Product dimensions: 7.27(w) x 9.54(h) x 1.00(d)

About the Author

SUSAN GRAZIANO SLOAN is an Advisory Software Engineer at IBM Silicon Valley Laboratory, San Jose, CA, where DB2 UDB for z/OS is developed. She has been team lead of the DB2 UDB for z/OS writing team since 2001, when she co-authored An Introduction to DB2 for OS/390, which earned a Merit Award in the Society for Technical Communication's Northern California competition.

Table of Contents


About this book.

Who Should Read This Book.

Conventions and Terminology Used in This Book.

How to Send Comments.



1. An Overview of DB2.

Scenarios for Using DB2.

Providing Availability and Scalability to Large Businesses.

Providing Information to Decision Makers.

Distributing Data and Providing Web Access.

The IBM DB2 Information Management Strategy.

The DB2 Universal Database Family.

Enterprise Servers.

DB2 UDB Editions.


More Servers.

The Networks: WANs and LANs.

Personal, Mobile, and Pervasive Environments.


Sources of Data.

Management Tools.

Application Development Tools.

Middleware and Client APIs.

The DB2 Universal Database Family Portrait.

Open Standards.

SQL-Related Standards.

DB2 UDB for z/Os Conformance.

For More Information.

Practice Exam Questions.

Answers to Practice Exam Questions.

2. DB2 Concepts.

Structured Query Language.

DB2 Data Structures.





Table Spaces.

Index Spaces.


Enforcement of Business Rules.

Referential Integrity and Referential Constraints.

Check Constraints.


Application Processes and Transactions.

Packages and Application Plans.




Distributed Data.

Remote Servers.


DB2 System Structures.


Active and Archive Logs.

Bootstrap Data Set.

Buffer Pools.

Practice Exam Questions.

Answers to Practice Exam Questions.

3. DB2 UDB for z/OS Architecture.

z/OS Overview.

DB2 in the z/OS Environment.

DB2 Lock Manager.

DB2 and the z/OS Security Server.

DB2 Attachment Facilities.






Distributed Data Facility.

The Parallel Sysplex Environment.

For More Information.


4. Designing Objects and Relationships.

Logical Database Design with Entity-Relationship Model.

Modeling your Data.

Defining Entities for Different Types of Relationships.

Defining Attributes for the Entities.

Normalizing Your Entities to Avoid Redundancy.

Logical Database Design with Unified Modeling Language.

Physical Database Design.

Denormalizing Tables to Improve Performance.

Using Views to Customize What Data a User Sees.

Determining What Columns to Index.

For More Information.

Practice Exam Questions.

Answers to Practice Exam Questions.

5. Working with SQL: The Language of DB2.

Executing SQL.

Methods of Executing SQL.

Executing SQL from Your Workstation.

Writing SQL Queries to Answer Questions: The Basics.

Example Tables.

Selecting Data from Columns: SELECT.

Processing a SELECT Statement.

Accessing DB2 Data that is Not in a Table.

Using Functions and Expressions.

Filtering the Number of Returned Rows: WHERE.

Putting the Rows in Order: ORDER BY.

Summarizing Group Values: GROUP BY.

Subjecting Groups to Conditions: HAVING.

Merging Lists of Values: UNION.

Joining Data from More Than One Table.

Using Subqueries.

Modifying Data.

Inserting New Data.

Updating Data.

Deleting Data.

For More Information.

Practice Exam Questions.

Answers to Practice Exam Questions.

6. Writing an Application Program.

Using Integrated Development Environments.

DB2 Development Support in Integrated Development Environments.

WebSphere Studio Application Developer.

DB2 Development Add-In for Visual Studio.NET.

Workstation Application Development Tools.

Choosing Programming Languages and Methods to Use.

Preparing an Application Program to Run.

Writing Static SQL Applications.

Overview of Static SQL.

Static SQL Programming Concepts.

Writing Dynamic SQL Applications.

Types of Dynamic SQL.

Dynamic SQL Programming Concepts.

Using ODBC to Execute Dynamic SQL.

Using Java to Execute Static and Dynamic SQL.

SQLJ Support.

JDBC Support.

Using an Application Program as a Stored Procedure.

Choosing a Language for Creating Stored Procedures.

Running Stored Procedures.

Setting Up the Stored Procedure Environment.

Preparing a Stored Procedure.

-Writing and Preparing an Application to Call Stored Procedures.

For More Information.

Practice Exam Questions.

Answers to Practice Exam Questions.

7. Implementing your Database Design.

Defining Tables.

Types of Tables.

Table Definitions.

Defining Columns and Rows in a Table.

Determining Column Attributes.

Choosing a Data Type for the Column.

Using Null and Default Value.

Enforcing Validity of Column Values with Check Constraints.

Designing Rows.

Defining a Table Space.

General Naming Guidelines for Table Spaces.

Coding Guidelines for Defining Table Spaces.

Segmented Table Spaces.

Partitioned Table Spaces.

Large Object Table Spaces.

Assignment of Table Spaces to Physical Storage.

A Few Examples of Table Space Definitions.

Defining Indexes.

Index Keys.

General Index Attributes.

Partitioned Table Index Attributes.

Guidelines for Defining Indexes.

Defining Views.

Coding the View Definitions.

Inserting and Updating Data through Views.

Defining Large Objects.

Defining Databases.

Defining Relationships with Referential Constraints.

How DB2 Enforces Referential Constraints.

Building a Referential Structure.

Defining the Tables in the Referential Structure.

Loading the Tables.

Defining Other Business Rules.

Defining Triggers.

Defining User-Defined Functions.

For More Information.

Practice Exam Questions.

Answers to Practice Exam Questions.

8. Managing DB2 Performance.

Understanding Performance Issues.

Know the Performance Requirements.

Design Applications with Performance in Mind.

Determine the Origin of a Performance Problem.

Use the Tools Available for Performance Analysis.

Moving Data Efficiently through the System.

Caching Data: The Role of Buffer Pools.

Compressing Data.

Keeping Data Organized.

Improving Performance for Multiple Users: Locking and Concurrency.

How Locking Works.

How to Promote Concurrency.

Improving Query Performance.

Access Paths: The Key to Query Performance.

Query and Application Performance Analysis.

Learning about Specific Performance Considerations.

For More Information.

Practice Exam Questions.

Answers to Practice Exam Questions.

9. Managing DB2 Operations.

Using Tools to Manage DB2.

DB2 Control Center and Related Tools.

msys for Setup DB2 Customization Center.

DB2 Administration Tool.

Issuing Commands and Running Utilities.

DB2 Commands.

DB2 Utilities.

Managing Data Sets.

Authorizing Users to Access Data.

Controlling Access to DB2 Subsystems.

Controlling Data Access: The Basics.

Controlling Access to DB2 Objects through Explicit Privileges and Authorities.

Controlling Access by Using Multilevel Security.

Controlling Access by Using Views.

Granting and Revoking Privileges.

Backup and Recovery.

Overview of Backup and Recovery.

Backup and Recovery Tools.

Regular Backups and Data Checks.

Database Changes and Data Consistency.

Events in the Recovery Process.

Optimizing Availability during Backup and Recovery.

For More Information.

Practice Exam Questions.

Answers to Practice Exam Questions.


10. DB2 and the Web.

Web Application Environment.

Components of Web-Based Applications.

Architectural Characteristics of Web-Based Applications.

Benefits of DB2 UDB for z/OS Server.

Web-based Applications and WebSphere Studio Application Developer.

Web Services and DB2.

For More Information.

11. Distributed Data Access.

Introduction to Distributed Data Access.

Programming Techniques for Accessing Remote Servers.

Using Explicit CONNECT Statements.

Using Three-Part Names.

Coding Considerations.

Program Preparation Considerations.

Planning Considerations.

Coordination of Updates.

DB2 Transaction Manager Support.

Servers that Support Two-Phase Commit.

Servers that Do Not Support Two-Phase Commit.

Network Traffic Reduction.

Coding Efficient Queries.

Sending Multiple Rows in a Single Message.

Optimizing for Large and Small Result Sets.

Improving Dynamic SQL Performance.

For More Information.

12. Data Sharing with Your DB2 Data.

Advantages of DB2 Data Sharing.

Improves Availability of Data.

Enables Scalable Growth.

Supports Flexible Configurations.

Leaves Application Interfaces Unchanged.

How Data Sharing Works.

How DB2 Protects Data Consistency.

How an Update Happens.

How DB2 Writes Changed Data to Disk.

Some Data Sharing Considerations.

Tasks That Are Affected by Data Sharing.

Availability Considerations.

For More Information.


Appendix A. Example Tables in This Book.

Employee Table.

Department Table.

Project Table.

Employee-to-Project Activity Table.

Products Table.

Parts Table.

Appendix B. IBM DB2 UDB Certification Program.

Examining the DB2 UDB Version 8 Certification Roadmap.

Exploring Exam 700: DB2 UDB V8.1 Family Fundamentals.

The Role of a Certified Database Associate.

Certification Requirements.

Exam 700 Objectives.

Preparing for the Exam.

Study Guidelines.

Exam Information Sources.

Assessment Exams.

Scheduling Your Exam.

Earning More Advanced Certifications.





About this book

  • Who should read this book

  • Conventions and terminology used in this book

  • How to send comments

  • Acknowledgments

This book provides a comprehensive introduction to IBM DB2 Universal Database for z/OS and covers the material on the first exam in the DB2 UDB certification series: DB2 UDB V8.1 Family Fundamentals. It explains the basic concepts that are associated with relational database management systems in general, and with DB2 Universal Database for z/OS in particular.

Most chapters in this book begin with a list of the exam topics that the chapter covers and conclude with practice test questions to help you prepare for the certification exam.

Part One of the book provides an overview of the DB2 UDB for z/OS product, other products that work with DB2 UDB for z/OS, and relational database concepts.

  • -Chapter 1. An overview of DB2

  • -Chapter 2. DB2 concepts

  • -Chapter 3. DB2 UDB for z/OS architecture

Part Two of the book provides introductory information about a variety of tasks that DB2 users perform. You can read some or all of these chapters, depending on your areas of interest and your needs.

  • -Chapter 4. Designing objects and relationships

  • -Chapter 5. Working with SQL: The language of DB2

  • -Chapter 6. Writing an application program

  • -Chapter 7. Implementing your database design

  • -Chapter 8. Managing DB2 performance

  • -Chapter 9. Managing DB2 operations

Part Three of the book provides information about some specialized topic

  • -Chapter 10. DB2 and the Web

  • -Chapter 11. Distributed data access

  • -Chapter 12. Data sharing with your DB2 data

Most chapters provide a list of useful citations at the end of the chapter. The citations direct you to Web information and books that provide the next level of detail. (The only exception is "Chapter 2. DB2 concepts," which doesn't have this section because every topic it introduces is also explained in more detail in subsequent chapters.) For example, "Chapter 4. Designing objects and relationships" gives an overview of the task of designing a database and concludes with a list of references to more advanced database design information. In some cases, these sections also provide references to similar information about DB2 in other operating systems (such as Windows, AIX®, Linux, iSeries, VM, and VSE).

"Appendix A. Example tables in this book" shows the example DB2 tables that this book uses to illustrate different concepts.

"Appendix B. IBM DB2 UDB certification program" describes the IBM certification program, explains what to expect on the fundamentals exam, and lists sections of this book you need to study. It also tells you how to find all the available resources that you can use to prepare for the certification exams and explains the requirements for subsequent exams in the DB2 UDB certification series.

After reading this book, you will understand basic concepts about DB2, and you will know where to look for additional details about individual topics that this book describes.

Who should read this book

If you are new to DB2 UDB for z/OS or you are preparing for the DB2 UD Fundamentals exam, this book is for you. Perhaps you have worked with DB2 on other operating systems (Windows, Linux, AIX, iSeries, VM, or VSE). Perhaps you have worked on non-IBM database management systems (DBMSs) or on the IBM hierarchic DBMS, which is called Information Management System (IMS). Perhaps you have never worked with DBMSs, but you want to work with this product, which many companies use for mission-critical data and application programs. Regardless of your background, if you want to learn about DB2 UDB for z/OS, this book will help you.

If you will be working with DB2 UDB for z/OS and already know what specific job you will have, begin by reading Part One (Chapters 1 through 3). Then, you can consider what your role will be when you choose to read all or only a subset of the remaining chapters. For example, assume that you know you will be a database administrator (DBA) for an organization that has some distributed applications and is beginning to plan for e-business. In this case, you would probably want to read at least Chapters 4, 7, 10, and 11.

The author of this book assumes that most readers are data processing professionals.

Conventions and terminology used in this book

This book uses the following conventions to distinguish certain types of information.

Bold font

Identifies labels that distinguish various types of information (such as Tip, Recommendation, and Example) or words and phrases that need special emphasis.

Italic font

Identifies new terms that the information defines, programming variables, and titles of other books.

Monospaced font

Identifies example code.

Indicates that the "For more information" section at the end of the chapter identifies additional information sources about the topic that precedes the book icon.

Identifies information about one or more tools that you and your organization can use. This book mentions and briefly describes many other tools and products. It also provides references to more detailed sources of information.

Indicates information that is an especially important tip or recommendation.

This book refers to DB2 Universal Database for z/OS as DB2 UDB for z/OS. In cases where the context makes the meaning clear, the book refers to DB2 UDB for z/OS as DB2. This book uses a short form for the titles of other DB2 UDB for z/OS books. For example, a reference to DB2 SQL Reference is a citation to IBM DB2 Universal Database for z/OS SQL Reference. The Bibliography lists the full book titles.

When referring to a DB2 UDB product other than DB2 UDB for z/OS, this book uses the product's full name to avoid ambiguity.

This book uses the following terms:


Represents either the DB2 UDB for z/OS licensed program or a particular DB2 UDB for z/OS subsystem.


Refers to the DB2 Performance Monitor tool, which can be used on its own or as part of the DB2 Performance Expert for z/OS and Multiplatforms product.

C , C++, and C language

Represent the C or C++ programming language.


Represents CICS Transaction Server for z/OS or CICS Transaction Server for OS/390®.


Represents the IMS Database Manager or IMS Transaction Manager.


Represents the MVS element of the z/OS operating system. The new name for the MVS element is Base Control Program (BCP).


Represents the functions that are provided by the RACF component of the z/OS Security Server.

How to send comments

Your feedback is welcome. If you have comments about this book, you can contact the publisher at or contact the author directly at


Thanks to the IBM developers who provided valuable review input, especially Roger Miller, Curt Cotner, Casey Dawes, James Pickel, Peggy Rader, Ramani Croisettier, Jeff Josten, Cindy Saracco, Akira Shibamiya, Craig Friske, Keith Howell, Chris Holstrom, and Mark Wilson. Special thanks to Chris Farrar for his diligence in reviewing this book cover to cover.

Thanks to Susan Visser for encouraging me to expand the scope of this book to cover material for the DB2 Family Fundamentals certification exam and for her efforts to get this book into the market.

Thanks to the current and former DB2 UDB for z/OS writers who have developed high-quality information that set the stage for the creation of this book: Kathryn Zeidenstein, Patti Tonello, Ann Hernandez, Mary Paquet, Jeanne Kays, and Judy Tobias.

Thanks to graphic designers Polly Hughes and Richard Katz, who designed the illustrations that clearly communicate the concepts in this book.

Thanks to the support of the IBM management team: Mark Flores, Lori Fisher, Maureen Zoric, and Fiona Gleeson.

Special thanks to Ann Hernandez for her invaluable contributions: writing the practice test questions and answers, applying outstanding editing skills to this book, and sharing her enthusiasm and advice.

Finally, special thanks to my husband, Steve Sloan, for deciding to design and produce this book. His willingness to take on this challenge is a credit to his natural creative talent. Thanks, Steve, for your love and support as we successfully worked together for the first time. Thanks to Carol Christensen and Dave Kessler for their understanding when Steve spent many hours away from his job working on this project. Thanks to our son, Kenneth, for his patience and for knowing when to make himself scarce. And thanks, Mom, for bringing us home cooked meals at the right times.

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews