DB2 for OS/390 Development for Performance

Author: Gabrielle Wiorkowski
Gabrielle & Associates
ISBN: 0-9668460-1-X

If you’re a developer or administrator working with DB2 on OS/390, there’s a new book that you should take a look at. It contains much that will be useful to you in your work environment.

You may be new to DB2, or you may have been working with it for a number of years. DB2 on IBM’s large systems is as successful as it is today for numerous reasons and thanks to numerous people.
Among the major reasons for DB2’s initial success are: (l) the early interaction and feedback the development team received from interested users, and (2) the team’s ability and desire to respond
to that feedback in building the DB2 product. One of the early users of DB2 was Gabrielle Wiorkowski, then at Texas Instruments, now a senior DB2 consultant.

Gabrielle has continued to be actively involved with DB2 and the DB2 user community throughout the product’s evolution. Many DB2 users may be familiar with her first book DB2: Design and
Development Guide, which she coauthored with David Kull. The book was initially published by Addison-Wesley in 1988. Subsequent editions appeared in 1990 and 1992. Gabrielle’s new book is entitled
DB2 for OS/390 Development for Performance.

It’s a sequel to the first book. If you found the first book of value, you’ll refer again and again to her new one. If you haven’t seen her earlier book, but you’ve been confronted by questions
like the ones below, read on:

  • How does list prefetch differ from sequential and dynamic prefetch?
  • Under what conditions are all rows processed by DB2 when a cursor is opened or when the first row is fetched?
  • Under what conditions is an index not used to locate a row to be updated or deleted?
  • What are the major advantages of type 2 indexes?
  • When is a merge join favored over the nested loop and hybrid join? How can you encourage a merge join?
  • If a matching index scan is chosen for the predicate WHERE JN > ‘J3′, what can you do to disallow the use of the index and encourage a tablespace scan?
  • Why might you use a mass delete approach followed by LOAD RESUME rather than LOAD REPLACE?
  • Should the SORTDATA parameter be used with SHRLEVEL NONE and REFERENCE when reorganizing a tablespace? Why?

These and many other questions are addressed specifically in Gabrielle’s new book. In fact, the book is organized into 22 chapters, each of which concludes with a summary, list of exercises, and
answers to all exercises. If you’re a very busy DB2 system administrator, database administrator, database designer, or application developer, you might want to flip to the list of questions for a
particular chapter. If you know the answers to all of the questions, return to your work–you’re well positioned to get the most out of DB2. If you don’t know all the answers, but you’d like to,
take time to read the chapter. Then check your concentration, or absorption rate, or whatever, by reviewing the questions again to see if you’ve gotten what you really wanted to get out of the
chapter. The lists of answers will help you to crystalize your thoughts.

The breadth and depth of the DB2 material covered in this book is unmatched by any single book or other form of publication anywhere. The initial hard-copy published version comprises nearly 1000
pages. It’s not likely you’ll want to read the book from beginning to end, as you would a novel, though the basic, clear writing style of the author makes that possible. If you’re an experienced
DB2 user, you’ll skip quickly over some of the material. The chapter titles enable you to turn quickly to a topic of interest at a particular time: Concepts and Components, Creating STOGROUPs,
Databases, and Buffer Pools, Creating Tablespaces, Creating Tables and Views, Index Usage for Performance, Index Design for Performance, The Basics of SQL Data Manipulation, Concurrency Control,
Program Development, Stored Procedures, Batch Processing, Program Preparation and Execution, Join Performance, Subselects and Table Expressions, Parallel Processing, Programming for Performance,
The Optimizer, Explaining the Access Path Chosen by the Optimizer, Load and Check Data Utilities, Runstats and Reorganization, Copy, Quiesce, Report, and Recover, and Security.

I’ve believed for some time that Gabrielle Wiorkoswki knows as much as, or more than, anyone outside of IBM’s DB/2 development team about DB2’s use of indexes, optimization, and other aspects of
performance. This book is based on what she has learned, her direct experiences, and the experiences of other DB2 professionals with whom she comes in contact. I’ve not seen the DB2 optimizer and
how to analyze and influence the access paths chosen by the optimizer presented more effectively anywhere. DB2 can perform well, or it can perform poorly. It’s you, the administrator, database
designer, or application developer who applies its capabilities wisely or unwisely. This book provides practical information of use to you today in your job environment.

I review a great many manuscripts, written by many authors. Gabrielle is second to none in her attention to detail, her care and accuracy. She presents the facts as facts, but she goes further.
You’ll find explicit statements of advantages, disadvantages, and recommendations. The book benefits from Gabrielle’s years as a noted instructor and seminar leader. She knows her subject matter,
and she knows how to communicate it effectively.

How does this book differ from Gabrielle’s earlier book, you may be wondering. The earlier book was written when most people had little knowledge of DB2. The new book contains more advanced
coverage of DB2 as well as information useful to people with limited or no DB2 experience. It presents new features and facilities of DB2 Versions 3, 4, and 5 which significantly effect the design,
development, and tuning of application systems. In my review of chapters as the book evolved, for example, I questioned Gabrielle as to why so much coverage of utilities.

Her response: IBM has provided a lot of enhancements to its utilities in the most recent releases, and I want to make sure users are aware of them.

When you decide that you’d like to have a book, the question becomes: Where or how do I get it? DB2 for OS/390 Development for Performance is the first or one of the first books of its kind to be
available in hard copy form (ISBN 0-9668460-1-X), on CD, and in soft copy form via the Internet. Both the CD and Internet forms can be read, searched, and printed using a free copy of Adobe Acrobat
Reader. Further, Gabrielle has committed to provide updates as soon as possible when new features of DB2 become available on IBM put tapes and through new releases of DB2. This approach contrasts
markedly to the traditional publishing cycle, where new editions are at best a year or two in the making. This book is truly an opportunity that’s too good to miss.


submit to reddit

About Marilyn Bohl

Marilyn Bohl, Vice President, Work Process Systems, San Jose, CA.