|
DB2 Developers Guide 5th Edition
Learn the best techniques and tricks from expert author Craig Mullins. Apply these real-world pieces of advice, undocumented tips, solutions, projects, and techniques to your own database management system. Mullins gives you what you need to take your DB2 development to the next level.
Written by a developer for developers, DB2 Developer¿s
Guide, Fifth Edition provides a solutions-oriented approach to learning the foundation and capabilities of this latest version of the world¿s number one database management system.
LEARN THE CONCEPTS AND BUILD THE APPLICATIONS
* Implement innovative shortcuts, tips, tricks, techniques, and development guidelines to optimize all facets of DB2 development and administration
* Understand the guidelines for binding DB2 application plans and packages
* Use expert advice to implement distributed DB2 applications
* Connect your DB2 databases to the World Wide Web
* Review exhaustive coverage of V6 topics¿including triggers, user-defined functions, stored procedure extensions, predictive governing, hiperspace bufferpools, the REBUILD utility, the DSSIZE parameter, user-defined distinct types, auxiliary tables, and more
* Read implementation information and guidance for large objects to store multimedia data¿such as audio, video, and images¿in your DB2 for OS/390 databases (BLOBs, CLOBs, DBCLOBs, and DB2 extenders)
* Learn how and why to use the new EXPLAIN tables - DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE
* Discover how to implement a procedural DBA function to manage triggers, stored procedures, and UDFs
CONTENTS:
Introduction.
I. SQL TOOLS, TIPS, AND TRICKS.
1. The Magic Words.
An Overview of SQL. SQL Tools of the Trade. Static SQL. Dynamic SQL. SQL Performance Factors.
2. Data Manipulation Guidelines.
A Bag of Tricks. SQL Access Guidelines. Complex SQL Guidelines. Common Table Expressions and Recursion. Working with NULLs. Date and Time Guidelines. Data Modification Guidelines.
3. Using DB2 Functions.
Column Functions. Scalar Functions. The RAISE_ERROR Function. MQSeries Built-in Functions. XML Publishing Built-in Functions. Built-in Function Guidelines.
4. Using DB2 User-Defined Functions and Data Types.
What Is a User-Defined Function? Types of User-Defined Functions. What Is a User-Defined Data Type? User-Defined Data Types and Strong Typing.
5. Data Definition Guidelines.
An Overview of DB2 Database Objects. DB2 Databases. Creating and Using DB2 Table Spaces. DB2 Storage and STOGROUPs. Table Guidelines. Normalization and Denormalization. Assuring Data Integrity in DB2. Views, Aliases, and Synonyms. Index Guidelines. Naming Conventions. Miscellaneous DDL Guidelines.
6. DB2 Indexes.
Creating Indexes. Index Guidelines.
7. Database Change Management and Schema Evolution.
Online Schema Changes.
8. Using DB2 Triggers for Integrity.
What Is a Trigger? Trigger Guidelines.
9. Large Objects and Object/Relational Databases.
Defining the Term "Object/Relational". What Is a Large Object? LOB Guidelines. DB2 Extenders.
10. DB2 Security and Authorization.
Granting and Revoking Privileges.
II. DB2 APPLICATION DEVELOPMENT.
11. Using DB2 in an Application Program.
Embedded SQL Basics. Embedded SQL Guidelines. Host Variables. Programming with Cursors. Embedded SELECT and Cursor Coding Guidelines. Modifying Data with Embedded SQL. Application Development Guidelines. Batch Programming Guidelines. Online Programming Guidelines. General SQL Coding Guidelines.
12. Dynamic SQL Programming.
Dynamic SQL Performance. Dynamic SQL Guidelines. Reasons You Should Know Dynamic SQL. EXECUTE IMMEDIATE. Non-SELECT Dynamic SQL. Fixed-List SELECT. Varying-List SELECT.
13. Program Preparation.
Program Preparation Steps. Running a DB2 Program. Preparing a DB2 Program. What Is a DBRM? What Is a Plan? What Is a Package? What Is a Collection? Versions. Program Preparation Objects. Program Preparation Guidelines. DCLGEN Guidelines. Precompiler Guidelines. BIND Guidelines. Linkage Editor Guidelines.
14. Alternative DB2 Application Development Methods.
Developing Applications Using Only SQL. Using Client/Server Programming Languages. Using Fourth-Generation Languages. Using CASE. Using Report Writers. Using ODBC (Call Level Interface). Using Java and DB2. Using REXX and DB2.
15. Using DB2 Stored Procedures.
What Is a Stored Procedure? Implementing DB2 Stored Procedures. The Procedural DBA. Procedural SQL. DB2 Development Center.
16. The Procedural DBA.
The Classic Role of the DBA. The Role of the Procedural DBA.
17. DB2 and the Internet.
The Internet Phenomenon. Accessing DB2 over the Internet. Using XML and DB2. Finding DB2 Information Using the Internet.
III. DB2 IN-DEPTH.
18. The Doors to DB2.
TSO (Time-Sharing Option). CICS (Customer Information Control System). IMS (Information Management System). CAF (Call Attach Facility). RRSAF (Recoverable Resource Manager Services Attach Facility). Comparison of the Environments. The Critical Mass.
19. Data Sharing.
Data Sharing Benefits. Data Sharing Requirements. The DB2 Coupling Facility. Data Sharing Naming Conventions. Data Sharing Administration. Data Sharing Application Development Guidelines. Data Sharing Administration Guidelines.
20. DB2 Behind the Scenes.
The Physical Storage of Data. What Makes DB2 Tick.
21. The Optimizer.
Physical Data Independence. How the Optimizer Works. Filter Factors. Access Path Strategies. Other Operations Performed by the Optimizer. Access Path Strategy Compatibility.
22. The Table-Based Infrastructure of DB2.
The DB2 Catalog. The DB2 Directory.
23. Locking DB2 Data.
How DB2 Manages Locking. Locks Versus Latches. Lock Duration. Table Space Locks. Table Locks. Page Locks. Row Locks. Lock Suspensions, Timeouts, and Deadlocks. Partition Independence. Lock Avoidance. Data Sharing Global Lock Management. LOBs and Locking. DB2 Locking Guidelines. Other DB2 Components. The Big Picture.
IV. DB2 PERFORMANCE MONITORING.
24. Traditional DB2 Performance Monitoring.
DB2 Traces. Trace Destinations. Tracing Guidelines. DB2 Performance Monitor (DB2 PM). Using DB2 PM. Online DB2 Performance Monitors. Viewing DB2 Console Messages. Displaying the Status of DB2 Resources. Monitoring z/OS and OS/390.
25. Using EXPLAIN.
How EXPLAIN Works. Access Paths and the PLAN_TABLE. Cost Estimates and the DSN_STATEMNT_TABLE. Function Resolution and the DSN_FUNCTION_TABLE. EXPLAIN Guidelines.
26. DB2 Object Monitoring Using the DB2 Catalog and RTS.
DB2 Catalog Queries. Real Time Statistics. Reviewing the Rules for an Effective Monitoring Strategy.
V. DB2 PERFORMANCE TUNING.
27. Tuning DB2's Environment.
Tuning the z/OS and OS/390 Environment. Tuning the Teleprocessing Environment.
28. Tuning DB2's Components.
Tuning the DB2 Subsystem. Tuning the Database Design. Tuning the Application. The Causes of DB2 Performance Problems.
29. DB2 Resource Governing.
The Resource Limit Facility. The QMF Governor.
VI. DB2 PERFORMANCE UTILITIES AND COMMANDS.
30. An Introduction to DB2 Utilities.
Generating Utility JCL. Monitoring DB2 Utilities. Version 7 and the State of IBM DB2 Utilities. Using LISTDEF and TEMPLATE. Issuing SQL Statements in DB2 Utilities.
31. Data Consistency Utilities.
The CHECK Utility. The CHECK DATA Option. The CHECK LOB Option. The CHECK INDEX Option. The REPAIR Utility. The REPAIR DBD Option. The REPAIR LOCATE Option. The REPAIR SET Option. REPAIR and Versions. The REPORT Utility. The DIAGNOSE Utility.
32. Backup and Recovery Utilities.
The COPY Utility. The COPYTOCOPY Utility. The MERGECOPY Utility. The QUIESCE Utility. The RECOVER Utility. The RECOVER Utility. The REBUILD INDEX Utility. The REPAIR Utility. The REPORT RECOVERY Utility. Backing Up and Restoring the System.
33. Data Organization Utilities.
The LOAD Utility. The UNLOAD Utility. The REORG Utility.
34. Catalog Manipulation Utilities.
The CATENFM Utility. The CATMAINT Utility. The DSNJCNVB Utility. The MODIFY Utility. The RUNSTATS Utility. The STOSPACE Utility.
35. Stand-alone Utilities and Sample Programs.
The Stand-alone Utilities. DB2 Sample Programs.
36. DB2 Commands.
DB2 Environment Commands. Information-Gathering Commands. Administrative Commands. Environment Control Commands. DSN Commands. IMS Commands. CICS Commands. TSO Commands. IRLM Commands.
37. DB2 Utility and Command Guidelines.
Utility Guidelines. The Pending States.
38. DB2 Contingency Planning.
What Is a Disaster? DB2 Environmental Considerations. DB2 Contingency Planning Guidelines.
VII. THE IDEAL DB2 ENVIRONMENT.
39. Components of a Total DB2 Solution.
DB2 Tools. DB2 Tools Vendors.
40. Organizational Issues.
Education. Standards and Procedures. Operational Support. Political Issues. Environmental Support. Tool Requirements.
VIII. DISTRIBUTED DB2.
41. DRDA.
What Is DRDA? DRDA Functions. DRDA Architectures and Standards. The Five DRDA Levels. Putting It All Together. Distributed DB2.
42. Distributed DB2.
Distributing Data Using DB2. DB2 Support for the DRDA Levels. Methods of Accessing Distributed Data. System-Directed Versus Application-Directed. Packages for Static SQL. Two-Phase Commit. Miscellaneous Distributed Topics.
43. DB2 Connect.
An Overview of IBM DB2 Connect.
44. Distribution Guidelines.
Distribution Behind the Scenes. Block Fetch. Dynamic Cursor Pre-Open. Distributed Performance Problems. Distributed Database Design Issues. Distributed Data Placement. Distributed Optimization. Distributed Security Guidelines. Miscellaneous Distributed Guidelines.
45. Data Warehousing with DB2.
Defining the Basic Terms. Designing a Data Warehouse. Populating a Data Warehouse. Accessing the Data Warehouse. Managing the Data Warehouse. The Big Picture. DB2 Data Warehouse Center. Materialized Query Tables. General Data Warehouse Guidelines. DB2-Specific Data Warehousing Guidelines.
ON THE WEB.
Web 1. Special Consideration for DB2 Version 6. PDF.
Type 1 Indexes. PDF. Shared Read Only Data. PDF. RECOVERINDEX. PDF. Host Variables Without Colons. PDF. Dataset Passwords. PDF. Stored Procedure Registration. PDF. Synopsis. PDF.
Web 2. Short Summary of DB2 V4 Through V6 Changes. PDF.
DB2Version 6 Features. PDF. DB2Version 5 Features. PDF. DB2Version 4 Features. PDF.
Web 3. Type 1 Indexes. PDF.
Basic Index Structure. PDF.
IX. APPENDIXES.
Appendix A. DB2 Sample Tables. PDF.
Appendix B. DB2 Tools Vendors. PDF.
The Vendor List. PDF.
Appendix C. Valid DB2 Data Types. PDF.
Appendix D. DB2 Limits. PDF.
Appendix E. DB2 on Other Platforms. PDF.
The DB2 Family. PDF. Packaging and Naming Issues. PDF.
Appendix F. DB2 Version 7 Overview. PDF.
E-Business Enhancements. PDF. Application Development Enhancements. PDF. Data Management Enhancements. PDF. Business Intelligence Enhancements. PDF. Additional V7 Information. PDF.
Appendix G. DB2 Version 8 Overview. PDF.
Architecture. PDF. Database Administration. PDF. Programming and Development. PDF. Migration to DB2 V8. PDF.
Appendix H. Reorganizing the DB2 Catalog. PDF.
When Should the DB2 Catalog and Directory Be Reorganized? PDF.
Index.
|
