DB2 Developer's Guide, 5/e

Marketshare for DB2 has been growing steadily over the past 5 years and with the announcement of DB2 Universal Database V8 (T-Rex), the product has never had more momentum. DB2 owns about 30 percent of the database market--the same as Oracle. Not only is the product used in many Fortune 500 companies, but it is becoming very popular in small to medium sized businesses as well. This book provides the reader with a comprehensive reference and research tool for DB2 for the mainframe. Official material is awkwardly written, spans over a dozen manuals in PDF format, and lacks real-world guidance. Author, Craig Mullins, consistently hears from readers of past editions that they rely on this book as their primary reference for DB2. Craig Mullins is constantly being asked when it will support a new release. "This is an excellent book . . . It is chock full of DB2 technical information, design and tuning approaches and database administration guidelines . . .Mullins seems to have dumped his entire DB2 life experience into DB2 Developer's Guide."- Jonathon Sayles in Relational Database Journal

Table of Contents:

Introduction.
I. SQL TOOLS, TIPS, AND TRICKS.

An Overview of SQL. SQL Tools of the Trade. Static SQL. Dynamic SQL. SQL Performance Factors.

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.

Column Functions. Scalar Functions. The RAISE_ERROR Function. MQSeries Built-in Functions. XML Publishing Built-in Functions. Built-in Function Guidelines.

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.

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.

Creating Indexes. Index Guidelines.

Online Schema Changes.

What Is a Trigger? Trigger Guidelines.

Defining the Term "Object/Relational". What Is a Large Object? LOB Guidelines. DB2 Extenders.

Granting and Revoking Privileges.

II. DB2 APPLICATION DEVELOPMENT.

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.

Dynamic SQL Performance. Dynamic SQL Guidelines. Reasons You Should Know Dynamic SQL. EXECUTE IMMEDIATE. Non-SELECT Dynamic SQL. Fixed-List SELECT. Varying-List SELECT.

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.

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.

What Is a Stored Procedure? Implementing DB2 Stored Procedures. The Procedural DBA. Procedural SQL. DB2 Development Center.

The Classic Role of the DBA. The Role of the Procedural DBA.

The Internet Phenomenon. Accessing DB2 over the Internet. Using XML and DB2. Finding DB2 Information Using the Internet.

III. DB2 IN-DEPTH.

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.

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.

The Physical Storage of Data. What Makes DB2 Tick.

Physical Data Independence. How the Optimizer Works. Filter Factors. Access Path Strategies. Other Operations Performed by the Optimizer. Access Path Strategy Compatibility.

The DB2 Catalog. The DB2 Directory.

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.

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.

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.

DB2 Catalog Queries. Real Time Statistics. Reviewing the Rules for an Effective Monitoring Strategy.

V. DB2 PERFORMANCE TUNING.

Tuning the z/OS and OS/390 Environment. Tuning the Teleprocessing Environment.

Tuning the DB2 Subsystem. Tuning the Database Design. Tuning the Application. The Causes of DB2 Performance Problems.

The Resource Limit Facility. The QMF Governor.

VI. DB2 PERFORMANCE UTILITIES AND COMMANDS.

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.

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.

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.

The LOAD Utility. The UNLOAD Utility. The REORG Utility.

The CATENFM Utility. The CATMAINT Utility. The DSNJCNVB Utility. The MODIFY Utility. The RUNSTATS Utility. The STOSPACE Utility.

The Stand-alone Utilities. DB2 Sample Programs.

DB2 Environment Commands. Information-Gathering Commands. Administrative Commands. Environment Control Commands. DSN Commands. IMS Commands. CICS Commands. TSO Commands. IRLM Commands.

Utility Guidelines. The Pending States.

What Is a Disaster? DB2 Environmental Considerations. DB2 Contingency Planning Guidelines.

VII. THE IDEAL DB2 ENVIRONMENT.

DB2 Tools. DB2 Tools Vendors.

Education. Standards and Procedures. Operational Support. Political Issues. Environmental Support. Tool Requirements.

VIII. DISTRIBUTED DB2.

What Is DRDA? DRDA Functions. DRDA Architectures and Standards. The Five DRDA Levels. Putting It All Together. 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.

An Overview of IBM DB2 Connect.

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.

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.

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.

DB2Version 6 Features. PDF. DB2Version 5 Features. PDF. DB2Version 4 Features. PDF.

Basic Index Structure. PDF.

IX. APPENDIXES.

Appendix B. DB2 Tools Vendors. PDF.

The Vendor List. PDF.

Appendix D. DB2 Limits. PDF.

Appendix E. DB2 on Other Platforms. PDF.

The DB2 Family. PDF. Packaging and Naming Issues. PDF.

E-Business Enhancements. PDF. Application Development Enhancements. PDF. Data Management Enhancements. PDF. Business Intelligence Enhancements. PDF. Additional V7 Information. PDF.

Architecture. PDF. Database Administration. PDF. Programming and Development. PDF. Migration to DB2 V8. PDF.

When Should the DB2 Catalog and Directory Be Reorganized? PDF.