DB2 for Z/OS and OS/390 Development for Performance Vol. 1 of 2

Overview

The fourth edition of the book is published in two volumes due to its size with the inclusion of V6 and V7 features and facilities. The table of contents is in Volume 1 and the index is in Volume 2. The two volumes cross reference each other. Please order both volumes which is the same price as the third edition in one volume. The book is organized to follow the life cycle of an application system, beginning with the creation of objects and the design of indexes to lay the foundation for high performing systems. ...
See more details below
Available through our Marketplace sellers.
Other sellers (Hardcover)
  • All (4) from $10.20   
  • New (1) from $59.48   
  • Used (3) from $10.20   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$59.48
Seller since 2014

Feedback rating:

(323)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
Brand New Item.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

The fourth edition of the book is published in two volumes due to its size with the inclusion of V6 and V7 features and facilities. The table of contents is in Volume 1 and the index is in Volume 2. The two volumes cross reference each other. Please order both volumes which is the same price as the third edition in one volume. The book is organized to follow the life cycle of an application system, beginning with the creation of objects and the design of indexes to lay the foundation for high performing systems. Programmer/analyst must develop high performing SQL to process data efficiently and have the knowledge base to tune SQL when required to meet response time requirements with minimal I/O and CPU time usage to minimize costs. The book concludes with the use of the utilities including enhancements in V6 and V7 for efficient maintenance of the data. It provides indepth coverage of the design and development of application systems for performance using DB2. Many examples of high performing SQL are given including actual timings to indicate the order of magnitude improvements that can be achieved by following the guidelines discussed. Alternatives are presented and discussed along with the pros and cons so that you can make the proper decisions for your application system. The author provides many practical examples, hints, tips, and guidelines for the design and development of application systems. This book is an excellent resource for experienced DB2 professionals as well as providing a concise and easy-to-read guide for those new to DB2. The following list of chapters give you an idea of the content.

1. Concepts and Components
2. Creating STOGROUPs, Databases, and Buffer Pools
3. Creating Tablespaces
4. Creating Tables and Views
5. Index Usage for Performance
6. Index Design for Performance
7. The Basics of SQL Data Manipulation
8. Concurrency Control
9. Program Development
10. Batch Processing
11.Triggering Actions in DB2
12. Stored Procedures
13. User-Defined Function
14. Program Preparation and Execution
15. Join Performance
16. Subselects and Table Expressions
17. Parallel Processing
18. Programming for Performance
19. The Optimizer
20. Explaining the Access Path Chosen by the Optimizer
21. Load and Check Data Utilities
22. Runstats and Reorganization
23. Copy, Quiesce, Report, Rebuild, Recover, and Rebuild


Read More Show Less

What People Are Saying

Greg Lackey
Thank you for making the day to day jobs of DBAs and developers easier and more productive.
Jaydeep Ghosh
Last week I took the DB2 certification for OS/390 and completed it successfully. Your book was very useful in this matter.
Paul Ranaldo
The book is an excellent, practical, hands-on guide. It is proving to be very useful in helping us solve our day-to-day problems. I think it's a must have for any serious minded DB2 technician. I have encouraged all my DBAs to get it and use it.
Terry Mason
With your latest edition, you have truly produced the ultimate "red book" for DB2. It's not just a "how to" - it also indicates what results to expect and why.
— Terry Mason and Susan Gausden, Brooklands Technology Limited, UK
Ward Fry
I am especially pleased with the organization and depth you present; one gets a good overview, yet can drill down for thorough understanding.
Read More Show Less

Product Details

  • ISBN-13: 9780966846058
  • Publisher: Gabrielle & Assoc.
  • Publication date: 2/28/2002
  • Edition description: Fourth Edition
  • Edition number: 4
  • Pages: 715
  • Product dimensions: 6.24 (w) x 9.56 (h) x 1.63 (d)

Read an Excerpt

Index Design for Performance Chapter (first 15 pages) Index Design for Performance ID.1 INTRODUCTION

It is important for both DBAs and programmer/analysts to understand how to use indexes. DBAs are usually concerned with physical design and maintenance, and programmer/analysts must understand how to write SQL statements that make the best use of indexes. Chapter IU describes the methods used by the index manager to efficiently locate rows.

This chapter describes issues related to designing indexes. It identifies the characteristics of columns that should and should not be indexed, and it describes the characteristics of columns that benefit the most from having a clustering index. Information about designing composite indexes are addressed.

ID.2 INDEX DESIGN GUIDELINES

Indexes can be used in a variety of ways and have a number of advantages. The primary advantage of indexes is the ability to process a small percentage of the rows efficiently with minimal I/O and CPU usage. We would like to see index usage when selecting, updating, deleting, and joining a small percentage (less than 5 to 10 percent) of rows in medium to large tables. A clustering index improves performance for processing a larger percentage of rows (less than 30 to 50 percent), particularly when the rows must be processed in sequence. Indexes can also be used to avoid access to data pages with index only processing. There are many reasons for having many indexes. However, there are costs associated with maintaining indexes that requires minimizing the number of indexes on a table.

Each time a row is inserted or deleted, the corresponding operation must be performed on each index. Each time an indexedcolumn is updated, the record identifier (RID) must be deleted and inserted for the new value. The costs of changing indexes is often more than the costs of changing the data. In addition, there is increased exposure to contention (latches and P-locks when using row locking and sysplex data sharing).

Whenever DB2 loads or reorganizes a table, it must build or rebuild each index to it. Whenever it recovers a tablespace, it must also recover or rebuild the indexes. The index itself may need to be reorganized (rebuilt) more frequently than the tablespace, as a result of index page splitting as discussed in Section ID.5. Finally, indexes require DASD space. Using indexes efficiently is a matter of selecting those whose performance benefits outweigh these costs. The benefits depend on the way the data are to be used and the table size.

Columns That Should Be Indexed

Primary keys and foreign keys are often searched or joined over a small percentage of rows and are good candidates for indexes. Indeed, the primary key must have a unique index to guarantee unique values in the column. If there is no index on the foreign key, an update of a primary key value requires a tablespace scan of each dependent table. This scan is done to verify that the old value does not exist in a dependent table. Similarly, when a row is deleted from a parent table and no index exists on the foreign key, it is necessary to do a tablespace scan on each dependent table to enforce the delete rule, described in Chapter CT. Both DB2 enforced and application enforced referential integrity benefit from having an index on the foreign key.

Joins are often performed on the primary key and foreign key columns; therefore, an index on these columns makes the join much more efficient in most cases.

Figure ID.1 Summarizes the characteristics of columns that benefit from an index.

Figure ID.1. Characteristics of columns that benefit from an index

* Primary key and foreign key columns.

* Columns which must have unique values to satisfy a business requirement.

* Columns that have column functions computed frequently (COUNT, SUM, AVG, MIN, and MAX for example).

* Columns used to test for the existence of a value (if the value does not exist, no data pages will be accessed).

* Columns which are searched or joined over less than 5 to 10 percent of the rows when considering a non-clustering index.

* Columns which are searched or joined over less than 30 to 50 percent of the rows when considering a clustering index.

* Columns frequently used together in a WHERE clause can benefit from a composite index to avoid maintaining multiple indexes

* Columns frequently used in an ORDER BY, GROUP BY, or DISTINCT clause to avoid sorts.

Columns That Benefit from Clustering

The choice of the column or columns to be included in the clustering index determines where DB2 inserts rows. The keyword CLUSTER, specified when the index is created, instructs DB2 to maintain the rows on the data pages in sequence according to the indexed column as described in Chapter IU.

The optimizer is likely to use the clustering index to avoid a sort for ORDER BY, GROUP BY, DISTINCT, and join processing. A column where these operations are often performed is a good candidate for the clustering index.

Columns frequently searched or joined over a range of values using the operators BETWEEN, >, /td> clustering index means that values are maintained in sequence on the data pages. A matching index scan can be used followed by a non-matching index scan to satisfy a range predicate. DB2 can use sequential prefetch to scan the data pages and the leaf pages.

A column with few distinct values (a low cardinality) is a good candidate for clustering if an index is required on the column at all. Generally it is not a good idea to create an index on a column with a low cardinality because the index does not narrow the search. However, if an index is required, a clustering index on such a column is a good choice because all of the like values are grouped (clustered) together on the data pages.

In earlier releases of DB2, the primary key was often chosen as the clustering index to allow for cursor repositioning operations. The use of the OPTIMIZE FOR n ROWS clause makes it unnecessary to use the clustering index for cursor repositioning, as described in Chapter PP. However, if there is batch processing requirements in which the input is in primary key sequence, the primary key is a good choice for the clustering index as described in Chapter BP.

The foreign key is a good candidate for frequent one-to-many joins (where many is large such as greater than 1,000, for example). If all of the foreign key rows are grouped together on the data pages, the join processing is more efficient. An index on the foreign key is also useful for cascading deletes and set nulls of foreign key rows with referential integrity constraint processing. If all of the J7s are grouped together on the data pages of a dependent table, a cascade delete of J7s can efficiently locate and delete all J7s that are grouped together.

Figure ID.2 summarizes the characteristics of columns that benefit from a clustering index. No one column has all of the characteristics listed here. Consider the trade-offs when deciding which column or columns should have the clustering index.

Figure ID.2. Characteristics of columns that benefit from a clustering index

* Column frequently processed in sequence using the operators ORDER BY, GROUP BY, or DISTINCT.

* Column frequently searched or joined over a range of values using predicates such as BETWEEN, >, /td>

* Column with a low index cardinality or skewed distribution (if an index is required).

* Primary key column is a good candidate for batch processing.

* Foreign key column is a good candidate for frequent one-to-many joins and enforcement of referential integrity (the many foreign values will be clustered together).

When using a partitioned tablespace, the clustering index must be the partitioning index.

Recommendation: It is very important to specify a clustering index based on your analysis of how the data is processed. If you do not create an index using the parameter CLUSTER, then by default the first index created (or more precisely the first index on the chain of indexes in the database descriptor (DBD)) is used to determine where to insert rows.

One problem with allowing DB2 to default to a clustering index is that when indexes are dropped and recreated, the first index on the chain may not be created first. DB2 starts using the new first index on the chain of indexes in the DBD to determine where to insert rows; what you thought was the clustering index is no longer used as such.

The REORG utility does not resequence rows if a clustering index is not explicitly declared. Further, the parameter SORTDATA on the REORG utility statement is ignored if there is no clustering index declared on a table. This can severely detract from performance. SORTDATA required 74 percent less elapsed time when reorganizing data with a cluster ratio of 80 percent in one case. Another deterrent to good reorganization performance is when SHRLEVEL CHANGE and NOSYSREC is used without an explicitly defined clustering index. With a clustering index defined, the rows are not written to SYSREC during unload and need not be sorted. However, without an explicitly defined cluster index this processing is necessary. The REORG OFFPOSLIMIT threshold applies only to explicitly defined clustering indexes as discussed in Chapter RR.

Consider using the following SELECT statements to verify that all tables created over the years do in fact have a clustering index.

SELECT I.TBCREATOR, I.TBNAME, I.CREATOR, I.NAME, I.CLUSTERING
FROM SYSIBM.SYSINDEXES I
WHERE NOT EXISTS
   (SELECT 1
   FROM SYSIBM.SYSINDEXES X
   WHERE X.CLUSTERING = 'Y'
   AND X.CREATOR = I.CREATOR
   AND X.NAME = I.NAME)
   ORDER BY I.TBCREATOR, I.TBNAME;

Recommendation: The rows that are returned from the SELECT statement do not have a clustering index defined. It is recommended that all tables have an explicitly declared clustering index.

Exception: If MEMBER CLUSTER is specified when creating a partitioned or simple tablespace as discussed in Chapter TS, rows are not clustered.

When Clustering Is Not Useful: Depending on the type of predicates used on the columns, some columns do not benefit from having a clustering index. If equal predicates are used on a column with a unique index, clustering has no advantages. One matching index scan can be used to locate the one row. With the exception of batch processing, there is generally no advantage to having a clustering index on a primary key with a unique index if equal predicates are used. If few rows are processed (less than 20, for example), the cost of using a non-clustering index can be acceptable. In addition, if the processing is mostly index-only processing, there is no need to access the data pages and the order of rows on the data pages does not matter.

Analyze How Data is Used

To understand the cost vs. benefits of using indexes, you must analyze how the data is used to determine which columns should be indexed. Identify columns that are frequently searched or joined, and estimate the percentage of rows processed by frequently executed SQL statements. Consider creating the clustering index on the column most frequently searched or joined in sequence. Consider non-clustering indexes on searched or joined columns in which less than 5 to 10 percent of the rows are to be selected, updated, deleted, and joined.

Refine this analysis with an estimate of the percentage of the rows that are inserted, updated, and deleted over a given period of time. Minimize the number of indexes when inserting, updating, and deleting more than about 10 percent of the rows on a weekly basis. If heavy update activity is concentrated on a weekly, monthly, quarterly, or annual basis, consider the techniques for avoiding index maintenance described in Chapter BP.

Composite indexes are useful when columns are frequently referenced together. They reduce the number of indexes that must be maintained and increase the chance of index-only retrieval.

These are only general guidelines. The physical design of the tables and indexes requires an analysis of how the data is to be processed. A process analysis matrix sometimes called a CRUD (Create, Retrieve, Update, and Delete) matrix is useful in summarizing how the data will be processed. Figure ID.3 is an example of such a matrix.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 6 Customer Reviews
  • Anonymous

    Posted November 15, 2003

    One of the best books on DB2

    This is one of the best DB2 books that I have read. The book starts with the basics and provides all the details that you always wanted to know. The book will be beneficial to developers, DBAs and to anyone who wants to get a better understanding of DB2. The book will also be helpful to people taking the IBM certification exam on database admin. for OS/390 (exam # 516). I have recommended this book to everyone at work.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted January 14, 2003

    DB2 for z/OS &OS/390 Development for performance by Gabrielle Wiorkowski

    This book is extremely helpful for all levels of DBAs, system programmers and DB2 developers. It is most comprehensive and includes all features of v.6 and v.7. At our shop, we have always used Gabriell's book for all releases of DB2. This particular edition is very helpful, in the sense it is a text book for all levels and all parts of DB2 world. Anytime in question, we look it up in this book for answer. I will recommend this book as a must for DB2 tech team.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted December 26, 2002

    Excellent book

    This book is amazing! . As a DBA , I keep on referring this book again & again . It has very useful information & can be used by anyone who wants a really good understanding of DB2 Cheers to the author for writing such a wonderful book!

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted June 7, 2002

    The most comprehensive book on DB2 .

    This book excels in clarity , depth and the number of topics covered . The questions at the end of the chapters are helpful too . Also , anyone preparing for the DB2 Certification ( for OS/390 ) would benefit from this .

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted May 4, 2002

    DB2 for Z/OS and OS/390 Development for Performance Vol. 1 of 2

    This is the first book I've encountered that starts at the basics and proceeds to build on them, rather than starting at some esoteric point or someplace in the middle. I like the book above anything else I've come across.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted April 10, 2002

    DB2 for Z/OS and OS/390 Development for Performance Vol. 1 of 2

    I got the newest addition of your book. Two volumes! I really like the way you present the concepts and you give enough samples. I must say you improved my comprehension of DB2 immensely.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing all of 6 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)