DB2

Design and Development Guide

Gabrielle Wiorkowksi, David Kull (Autoren)

Buch | Softcover

608 Seiten

1992 | 3rd edition
Addison Wesley (Verlag)
978-0-201-58049-5 (ISBN)

Infos

This book is a revision of the successful guide to DB2. It presents proven techniques required to produce DB2 applications with high performance characteristics. It has been updated to cover the new Release 2.3 of DB2 and the depth of coverage has been expanded throughout the seventeen chapters of the book. It also contains new and unique coverage of programming for performance, optimization, and the EXPLAIN command. Highlights: *Provides indepth and expanded coverage of performance. *Provides hints, tips, and guidelines for the design and development of application systems. *Conveys and understanding of SQL processing to maximize the performance so that the guidelines can be applied to your specific application system. *Incorporates new features of Release 2.3 throughout the book. *Contains a new chapter on program preparation and execution detailing how to use packages and plans, including fall back procedures. This book is an excellent resource for professional database designers and developers. It is concise and easy to read--making it an understandable guide for beginners as well. 0201580497B04062001

Inhalt

(Each chapter begins with an Introduction and concludes with a Summary and Exercises.)

1. Concepts and Components.


DB2’s Key Components.



An Introduction to SQL.



Introduction to Referential Integrity.



Table Naming.



A Word on Performance.



The DB2 System Architecture.



2. Review of Normalization.


The First Normal Form.



The Key Issue.



The Second Normal Form.



The Third Normal Form.



The Fourth Normal Form.



The Fifth Normal Form.



Minimizing Joins.



Storing Monthly Data.



3. Creating Physical Objects.


Databases.



Tablespace and Indexspace.



Free Space.



Locksize.



The Close Option.



Bufferpool Options.



Segmented Tablescpaces.



Create Index.



Partitioning and Performance.



4. Creating Tables and Views.


Creating Tables.



Views.



Dropping Objects.



Catalog Tables and Directory.



5. Referential Integrity.


The Rules.



DB2’s Referential Integrity System.



Data Manipulation Constraints.



Application Enforced Integrity.



Tracking Integrity Rules.



6. Indexing for Performance.


The B-Tree.



Use of Multiple Indexes.



Balancing the B-Tree.



Selecting Columns to Index.



The Value of Clustering.



Generating Primary Keys.



Index Cardinality and Composite Index Design.



When Not to Index.



Maintaining Clustering.



Index Monitoring.



7. Using SQL for Data Manipulation.


Select Statements.



Negating a Predicate.



Aggregates and Other Built-in Functions.



Date and Time Calculations.



Selecting by Groups.



Sorting the Answer Set.



Subselects.



Joins.



Union.



Outer Joins.



Intersection and Difference.



Inserting, Updating, or Deleting Data.



8. Concurrency Control.


Transactions, Commits, Rollbacks.



Lock Sizes and Types.



Isolation Level.



Intent Locks.



Acquire and Release Parameters.



Explicit Lock Statements.



Monitoring Locks.



Locks on the Catalog Tables, DBD, and Directory.



9. Program Development.


Static SQL.



Cursors and Repositioning.



Organization Chart Processing.



Batch Update Processing.



Benefits of Sequential Prefetch.



Restartable Batch Programs.



The SQL Communications Area.



Dynamic SQL.



Sample Host-Language Programs.



10. Program Preparation and Execution.


The Steps in Program Preparation.



DB2 Interactive.



Generating Data Structures.



Program Preparation.



The Benefit of Packages.



Binding SQL into Packages and Plans.



Rebinding Plans and Packages.



Freeing Plans and Packages.



Compile, Link, and Run.



The DSN Processor and Executing Programs.



11. Programming for Performance.


Efficient Joins.



Joins versus Subselects.



Incomplete Statements.



List Prefetch with Multiple and Single Indexes.



OPTIMIZE FOR “n” ROWS.



Functions Requiring Ordered Rows.



Predicates that Do Not Use Indexes.



The BETWEEN Operator.



Stage 1 and Stage 2 Predicates.



Managing a Skewed Distribution of Data.



Order of Predicate Evaluation.



Efficient Updating.



Steps to Consider in Coding Efficient SQL.



Indexes Are Not Always Best.



Capping the Costs.



Performance Results.



12. The Optimizer.


Parsing.



Optimization.



Code Generation



Execution.



Thresholds Applied at Bind and Execution Time.



Statistical Interdependence of Columns.



Fooling the Optimizer.



Simulating Production Data in a Test Environment.



Capacity Planning.



13. The EXPLAIN Command.


Requesting the Explanation.



Preparation for EXPLAIN.



Analyzing the Plan Table.



Sample Explanations.



When to Investigate Performance.



14. Application Monitoring.


Querying the Catalog.



Sample Queries Related to Performance.



When to Reorganize.



Space Management.



Tracking Referential Integrity Structures.



Determining Function and Object Privileges.



Documentation.



15. Security Control.


The System Administrator.



GRANT and REVOKE Statements.



Granting and Revoking Table Privileges.



Plan and Package Privileges.



Subsystem Privileges.



Database Privileges.



Resource Usage Privileges.



Revoke Cascades.



The AUTHID.



Restricting Access to Parts of Tables.



Data Definition Control.



16. Utilities.


The load Utility.



Loading Referential Integrity Structures.



RUNSTATS.



Reorganization.



COPY, MERGECOPY, and RECOVER.



Recovering Referential Structures.



17. Distributed Databases.


Three Types of Distributed Capability.



Distributed Request.



Location Transparency.



Local Autonomy.



Security.



Program Development in a Distributed Environment.



Distributed Enhancements.



Appendix A Tablespace Size Estimations.


Appendix B Estimating Index Sizes.


Appendix C Using SPUFI.


Answers to Exercises.


Index. 0201580497T04062001

Produkt-Details

• Erscheint lt. Verlag: 9.4.1992
• Verlagsort: Boston
• Sprache: englisch
• Maße: 168 x 240 mm
• Gewicht: 935 g
• Themenwelt: Informatik ► Datenbanken ► DB2
• ISBN-10: 0-201-58049-7 / 0201580497
• ISBN-13: 978-0-201-58049-5 / 9780201580495
• Zustand: Neuware