DB2

(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