Adobe® Digital Editions software.This eBook requires the free
Before downloading this DRM-encrypted PDF, be sure to:
DB2 Developer's Guide, Fourth Edition is completely revised and updated, covering all the new features for Version 6 for OS/390. It includes a special chapter on how changes to the product impact its use. This book clarifies complex DB2 topics, provides performance and procedural advice for implementing well-designed DB2 applications, and describes what DB2 does behind the scenes. An entire chapter on Internet-related features is included.
How to Use This Book. Preface. Type 1 Indexes. Shared Read Only Data. RECOVER INDEX. Host Variables Without Colons. Data Set Passwords. Stored Procedure Registration. Other Concerns—DB2 Private Protocol Distributed Data. Feature by APAR. Synopsis.
I. SQL TOOLS, TIPS, AND TRICKS.1. The Magic Words.
An Overview of SQL. The Nature of SQL. Set-at-a-Time Processing. Types of SQL. SQL Tools of the Trade. Selection and Projection. Joins and Subqueries. Union. Outer Join. Sorting and Grouping. The Difference Between HAVING and WHERE. Relational Division. CASE Expressions. SQL Functions. Definition of DB2 Data Structures. Security Control over DB2 Data Structures. Static SQL. Dynamic SQL. SQL Performance Factors. Introduction to the Optimizer. Influencing the Access Path. DB2 Optimizer “Hints.” DB2 Performance Features. Summary.2. Data Manipulation Guidelines.
A Bag of Tricks. SQL Access Guidelines. Complex SQL Guidelines. Date and Time Guidelines. Data Modification Guidelines. Summary.3. Using DB2 Functions.
Column Functions. The AVG Function. The COUNT Function. The COUNT_BIG Function. The MAX Function. The MIN Function. The STDDEV Function. The SUM Function. The VARIANCE Function. Scalar Functions. The RAISE_ERROR Function. Built-In Function Guidelines. Use Functions Instead of Program Logic. Avoid Synonyms. Use UPPER instead of TRANSLATE. Use HAVING to Search Column Function Results. Summary.4. Using DB2 User-Defined Functions and Data Types.
What Is a User-Defined Function? Types of User-Defined Functions (UDFs). The Schema. Creating User-Defined Functions. How Functions Are Executed. Table Functions. Sourced Functions. User-Defined Function Guidelines. What Is a User-Defined Data Type? User-Defined Data Types (UDTs) and Strong Typing. User-Defined Distinct Types and LOBs. Using UDTs for Business Requirements. Assigning Values and UDTs. User-Defined Distinct Type Guidelines. Summary.5. Data Definition Guidelines.
Naming Conventions. Database, Tablespace, and Table Guidelines. Database Definitions. Using Simple Tablespaces. Using Segmented Tablespaces. Using LOB Tablespaces. Using Partitioned Tablespaces. Tablespace Parameters. Defining Multiple Tables per Segmented Tablespace. Table Definition Guidelines. Changing the Name of a Table. Global Temporary Tables. Normalization. Denormalization. Row and Column Guidelines. DB2 Table Parameters. Index Guidelines. Miscellaneous DDL Guidelines. Summary.6. Using DB2 Triggers for Integrity.
What Is a Trigger? The Schema. Triggers Are Like Other DB2 Objects. Why Use Triggers? When Does a Trigger Fire? Creating Triggers. Trigger Packages. Triggers Can Fire Other Triggers. Trigger Guidelines. Summary.7. Large Objects and Object/Relational Databases.
Defining the Term “Object/Relational.” What Is a Large Object? LOB Columns Versus VARCHAR and VARGRAPHIC Columns. Creating Tables That Contain LOB Columns. Accessing LOB Data. LOB Guidelines. DB2 Extenders. DB2 Extender Guidelines. Summary.8. Miscellaneous Guidelines.
Security and Authorization Guidelines. View Usage and Implementation Guidelines. The View Usage Rule. Reasons Not to Create One View Per Base Table. Miscellaneous View Guidelines. General SQL Coding Guidelines. Summary.
II. DB2 APPLICATION DEVELOPMENT.9. Using DB2 in an Application Program.
Embedded SQL Basics. Embedded SQL Guidelines. Host Variables. Host Structures. Null Indicator Variables and Structures. Host Variable Guidelines. Programming with Cursors. Using a Cursor for Data Modification. Embedded SELECT and Cursor Coding Guidelines. Modifying Data with Embedded SQL. Embedded Modification SQL Guidelines. Batch Programming Guidelines. Online Programming Guidelines. Summary.10. Dynamic SQL Programming.
Dynamic SQL Performance. Dynamic SQL Guidelines. Reasons You Should Know Dynamic SQL. EXECUTE IMMEDIATE. EXECUTE IMMEDIATE Guidelines. Non-SELECT Dynamic SQL. Non-SELECT Dynamic SQL Guidelines. Fixed-List SELECT. Fixed-List SELECT Guidelines. Varying-List SELECT. Varying-List SELECT Guidelines. Summary.11. Program Preparation.
Program Preparation Steps. Issue the DCLGEN Command. Precompile the Program. Issue the BIND Command. Compile the Program. Link the Program. Running a DB2 Program. Preparing a DB2 Program. Program Preparation Using DB2I. Program Preparation Using Batch Procedures. Program Preparation Using CLIST or <F102MO>REXX EXEC. Program Preparation Using Multiple Methods. What Is a DBRM? What Is a Plan? What Is a Package? Package Benefits. Package Administration Issues. Package Performance. What Is a Collection? Collection Size. Package List Size. Versions. Program Preparation Objects. Program Preparation Guidelines. DCLGEN Guidelines. Precompiler Guidelines. BIND Guidelines. Linkage Editor Guidelines. Summary.12. Alternative DB2 Application Development Methods.
Developing Applications Using Only SQL. SQL Application Guidelines. Using Client/Server Programming Languages. Connecting to DB2. Client/Server Guidelines. Using Fourth-Generation Languages. 4GL Application Guidelines. Using CASE. CASE Application Guidelines. Using Report Writers. Report Writer Application Guidelines. Using ODBC (the DB2 Call Level Interface). DB2 ODBC Guidelines. Using Java: SQLJ and JDBC. Summary.13. Using DB2 Stored Procedures.
What Is a Stored Procedure? DB2's Stored Procedure Implementation. Why Use Stored Procedures? Implementing DB2 Stored Procedures. Developing a Stored Procedure. Creating Stored Procedures. Managing Stored Procedures. Executing a Stored Procedure. Stored Procedure Guidelines. The Procedural DBA. Procedural SQL. IBM's SQL Stored Procedure Language. The Benefits of Procedural SQL. The Drawbacks of Procedural SQL. Stored Procedure Builder. Summary.14. The Procedural DBA.
The Classic Role of the DBA. The Role of the Procedural DBA. Summary.15. DB2 and the Internet.
III. DB2 IN-DEPTH.16. The Doors to DB2.
TSO (Time-Sharing Option). TSO/DB2 Parameters. DB2 Access Using Batch TSO. DB2 Access Using Foreground TSO. Online TSO/DB2 Design Techniques. DB2I and SPUFI. QMF. Other TSO-Based DB2 Tools. TSO Guidelines. CICS (Customer Information Control System). CICS Terminology and Operation. CICS Tables. CICS/DB2 Program Preparation. CICS Attach Facility. Types of Threads. The RCT Parameters. RCT Guidelines. Two-Phase Commit. CICS Design Guidelines. IMS (Information Management System). Types of IMS Programs. The IMS Attach Facility. Online Attach Considerations. The Resource Translation Table. IMS/TM Thread Use. Two-Phase Commit. Restart. IMS/DB2 Deadlocks. IMS SYSGEN Guidelines. DL/I Batch Interface. IMS/TM Design Guidelines. CAF (Call Attach Facility). Thread Creation and Use. Benefits and Drawbacks of CAF. Vendor Tools. Sample CAF Code. RRSAF (Recoverable Resource Manager Services Attach Facility). Comparison of the Environments. Resource Availability. Feasibility. Batch Considerations. The Critical Mass. Summary.17. Data Sharing.
Data Sharing Benefits. What Are Sysplex and Parallel Sysplex? Data Sharing Requirements. DB2 Data Sharing Groups. Application Impact. The DB2 Coupling Facility. Defining the Coupling Facility. Global Lock Management. Global Inter-System Communication. Global Data Buffering. Data Sharing Naming Conventions. Data Sharing Administration. Data Sharing Group Creation. Backup and Recovery. Subsystem Availability. Monitoring Data Sharing Groups. Coupling Facility Recovery. Data Sharing Guidelines. Summary.18. DB2 Behind the Scenes.
The Physical Storage of Data. Tablespace Data Pages. LOB Pages. Index Pages. Record Identifiers. What Makes DB2 Tick. Summary.19. The Optimizer.
Physical Data Independence. How the Optimizer Works. CPU Cost. I/O Cost. DB2 Catalog Statistics. SQL Statement. Filter Factors. Access Path Strategies. Scans. Sequential Prefetch. Indexed Access. Query Parallelism. Join Methods. Parallel Joins. Other Operations Performed by the Optimizer. Summary.20. The Table-Based Infrastructure of DB2.
The DB2 Catalog. What Is the DB2 Catalog? An Exception to the Rule. The Benefits of an Active Catalog. DB2 Catalog Structure. The DB2 Directory. SCT02. SPT01. DBD01. SYSUTILX. SYSLGRNX. QMF Administrative Tables. Summary.21. Locking DB2 Data.
How DB2 Manages Locking. Locks Versus Latches. Lock Duration. Bind Parameters Affecting Tablespace Locks. BIND Parameters Affecting Page and Row Locks. Tablespace Locks. Table Locks. Page Locks. Row Locks. Page Locks Versus Row Locks. Lock Suspensions, Timeouts, and Deadlocks. Partition Independence. Claims. Drains. Lock Avoidance. When Lock Avoidance Can Occur. Data Sharing Global Lock Management. Global Locking. Lock Structures. Hierarchical Locking. P-Locks Versus L-Locks. LOBs and Locking. Types of LOB Locks. Duration of LOB Locks. LOB Tablespace Locking Considerations. DB2 Locking Guidelines. Other DB2 Components. The Big Picture. Summary.
IV. DB2 PERFORMANCE MONITORING.
Defining DB2 Performance. Types of DB2 Performance Monitoring.22. Traditional DB2 Performance Monitoring.
DB2 Traces. Accounting Trace. Audit Trace. Global Trace. Monitor Trace. Performance Trace. Statistics Trace. Trace Destinations. Tracing Guidelines. DB2-PM. Accounting Report Set. Audit Report Set. The Explain Report Set. I/O Activity Report Set. Locking Report Set. Record Trace Report Set. SQL Trace Report Set. Statistics Report Set. Summary Report Set. System Parameters Report Set. Transit Time Report Set. Using DB2-PM. Online DB2 Performance Monitors. Traditional VTAM Performance Monitors. Agent-Based Performance Management. Online Performance Monitoring Summary. Viewing DB2 Console Messages. Displaying the Status of DB2 Resources. Monitoring OS/390 and MVS. Summary.23. Using EXPLAIN.
How EXPLAIN Works. Access Paths and the PLAN_TABLE. Querying the PLAN_TABLE. The PLAN_TABLE Columns. Sample Access Paths. Cost Estimates and the DSN_STATEMNT_TABLE. The DSN_STATEMNT_TABLE Columns. Function Resolution and the DSN_FUNCTION_TABLE. The DSN_FUNCTION_TABLE Columns. EXPLAIN Guidelines. Summary.24. DB2 Object Monitoring Using the DB2 Catalog.
Navigational Queries. Physical Analysis Queries. Partition Statistics Queries. Programmer's Aid Queries. Application Efficiency Queries. Authorization Queries. DB2 Catalog Query Guidelines. Summary.
PART V. DB2 PERFORMANCE TUNING.25. Tuning DB2's Environment.
Tuning the OS/390 and MVS Environment. Tuning Memory Use. Tuning CPU Use. Tuning I/O. Tuning Various MVS Parameters and Options. Tuning the Teleprocessing Environment. Summary.26. Tuning DB2's Components.
Tuning the DB2 Subsystem. Tuning the DB2 Catalog. DSNZPARMs. Drowning in a Bufferpool of Tears. Bufferpool Parameters. Determining Bufferpool Sizes. DB2 Bufferpool Guidelines. Hiperpools. Data Sharing Group Bufferpools. Data Sharing Bufferpool Guidelines. IRLM Tuning Options. Tuning the Database Design. Tuning the Application. Analyzing Access Paths. Influencing the Optimizer. Miscellaneous Guidelines. Consider Deleting Nonuniform Distribution Statistics. DB2 Referential Integrity Use. General Application Tuning. The Causes of DB2 Performance Problems. Summary.27. DB2 Resource Governing.
The Resource Limit Facility. Reactive Governing. Predictive Governing. Defining the RLST. The QMF Governor. The QMF F Parameter. Summary.
VI. DB2 UTILITIES AND COMMANDS.28. An Introduction to DB2 Utilities.
Summary.29. Data Consistency Utilities.
The CHECK Utility. The CHECK DATA Option. Referential Integrity Checking. Check Constraint Checking. LOB Reference Checking. Estimating CHECK DATA Work Data Set Sizes. CHECK DATA Locking Considerations. CHECK DATA Guidelines. The CHECK LOB Option. CHECK LOB Phases. CHECK LOB Locking Considerations. The EXCEPTIONS Parameter. The CHECK INDEX Option. CHECK INDEX Phases. Estimating CHECK INDEX Work Data Set Sizes. CHECK INDEX Locking Considerations. CHECK INDEX Guidelines. The REPAIR Utility. REPAIR Phases. The REPAIR DBD Option. REPAIR DBD Guidelines. The REPAIR LOCATE Option. REPAIR LOCATE Locking Considerations. REPAIR LOCATE Guidelines. The REPAIR SET Option. <F102MO>REPAIR SET Guidelines. The REPORT Utility. The REPORT TABLESPACESET Option. REPORT TABLESPACESET Guidelines. The DIAGNOSE Utility. Summary.30. Backup and Recovery Utilities.
The COPY Utility. COPY Phases. Calculating SYSCOPY Data Set Size. COPY Locking Considerations. COPY Guidelines. The MERGECOPY Utility. MERGECOPY Phases. Estimating SYSUT1 and SYSCOPY Data Set Sizes. Concurrency. MERGECOPY Guidelines. The QUIESCE Utility. QUIESCE Phases. QUIESCE Locking Considerations. QUIESCE Guidelines. The RECOVER Utility. The RECOVER Utility. The RECOVER TABLESPACE Utility. The RECOVER INDEX (or RECOVER INDEXSPACE) Utility. RECOVER Phases. RECOVER Locking Considerations. RECOVER Guidelines. The REBUILD INDEX Utility. REBUILD INDEX Phases. Estimating REBUILD INDEX Work Data Set Sizes. REBUILD INDEX Locking Considerations. REBUILD INDEX Guidelines. The REPAIR Utility. The REPORT RECOVERY Utility. REPORT RECOVERY Locking Considerations. REPORT RECOVERY Guidelines. Summary.31. Data Organization Utilities.
The LOAD Utility. Table Loading Philosophies. Estimating <F102MO>LOAD Work Data Set Sizes. LOAD Phases. Creating an Inline Copy During the LOAD. Gathering Inline Statistics During the LOAD. LOAD Rerun/Restart Procedures. <F102MO>LOAD Locking Considerations. LOAD Guidelines. The REORG Utility. Recommended Reorganization Standards. Reorganization Philosophies. Reorganization Frequency. Reorganization Job Stream. Estimating REORG Work Data Set Sizes. REORG INDEX Phases. REORG TABLESPACE Phases. REORG TABLESPACE Phases for LOB Tablespaces. REORG Rerun/Restart Procedures. Gathering Inline Statistics During the REORG. REORG and the SHRLEVEL Parameter. Using SHRLEVEL CHANGE to Achieve Online Reorganization. REORG TABLESPACE Locking Considerations. REORG INDEX Locking Considerations. REORG Guidelines. When Should the DB2 Catalog and Directory Be Reorganized? DB2 Catalog Reorganization Details. Catalog Reorganization Restrictions. Summary.32. Catalog Manipulation Utilities.
The CATMAINT Utility. CATMAINT Guidelines. The MODIFY Utility. MODIFY Phases. The MODIFY RECOVERY Utility. MODIFY RECOVERY Locking Considerations. MODIFY RECOVERY Guidelines. The RUNSTATS Utility. RUNSTATS Phases. RUNSTATS Locking Considerations. DB2 Catalog Tables Updated by RUNSTATS. RUNSTATS Guidelines. The STOSPACE Utility. STOSPACE Phases. STOSPACE Locking Considerations. STOSPACE Guidelines. Summary.33. Miscellaneous Utilities.
The Offline Utilities. The Change Log Inventory Utility (DSNJU003). The Print Log Map Utility (DSNJU004). Log Utility Guideline. DSNJLOGF Guideline. Service Aids. The Catalog Integrity Verification Utility (<F102MO>DSN1CHKR). DSN1CHKR Guidelines. The Compression Analyzer (DSN1COMP). DSN1COMP Guideline. The Offline Tablespace Copy Service Aid (DSN1COPY). DSN1COPY Guidelines. The DB2 Dump and Trace Program (DSN1SDMP). DSN1SDMP Data Sets. DSN1SDMP Guidelines. The Recovery Log Extractor (DSN1LOGP). DSN1LOGP Guidelines. The DB2 Data Set Dump Creator (DSN1PRNT). DSN1PRNT Guidelines. DB2 Sample Programs. The Dynamic SQL Processor (DSNTEP2). DSNTEP2 Guidelines. The Dynamic SQL Update Program (DSNTIAD). DSNTIAD Guidelines. The Sample Unload Program (DSNTIAUL). DSNTIAUL Guidelines. Interpreting DSNTIAUL, DSNTIAD, and DSNTEP2 Return Codes. Summary.34. DB2 Commands.
DB2 Environment Commands. Information-Gathering Commands. Information-Gathering Command Guidelines. Administrative Commands. Administrative Command Guidelines. Environment Control Commands. Environment Control Command Guidelines. DSN Commands. DSN Command Guidelines. IMS Commands. IMS Command Guidelines. CICS Commands. CICS Command Guidelines. TSO Commands. IRLM Commands. IRLM Command Guidelines. Summary.35. DB2 Utility and Command Guidelines.
Utility Guidelines. DB2 Utility Catalog Contention. Partition Level Operation. Coding Utility Control Cards. Specify the BUFNO JCL Parameter. Allocate Sufficient Sort Work Space for DFSORT. When Loading or Reorganizing, Specify LOG NO. When Loading or Reorganizing, Perform Inline Utilities. Back Up Data Using the COPY Utility or DFSMS. REBUILD INDEX Versus CREATE INDEX. The Pending States. Why Pending States Occur. How to Correct Pending States. Summary.36. DB2 Contingency Planning.
What Is a Disaster? Determining and Managing Risk. Disaster Recovery Requirements. Disaster Strikes. DB2 Recovery Basics. Strategy #1: The Sledgehammer. Strategy #2: The Scalpel. Strategy #3: DSN1COPY. DB2 Environmental Considerations. DB2 Contingency Planning Guidelines. Summary.
VII. THE IDEAL DB2 ENVIRONMENT.37. Components of a Total DB2 Solution.
DB2 Tools. Table Altering Tools (ALT). Auditing Tools (AUD). DB2 Catalog Query and Analysis Tools (CAT). Compression Tools (COM). DB2-Related Client/Server Tools (C/S). Database Analysis Tools (DBA). Database Modeling and Design Tools (DES). DASD and Space Management Tools (DSD). DB2 Table Editors. Index Analysis Tools (IDX). Integrity Tools (INT). DB2 Object Migration Tools (MIG). Data Movement Tools (MOV). Miscellaneous Tools (MSC). Internet Enabling Tools (NET). Operational Support Tools (OPR). PC-Based DB2 Emulation Products (PC). Plan Analysis Tools (PLN). Performance Monitors (PM). Products to Enhance Performance (PRF). DB2 Programming and Development Tools (PRG). QMF Enhancement Tools (QMF). Query Tools (QRY). Repositories (REP). Security Tools (SEC). Utility Enhancement Tools (UTL). DB2 Tools Vendors. The Vendor List. Evaluating DB2 Tools Vendors. Summary.38. Organizational Issues.
Education. Standards and Procedures. Roles and Responsibilities. Data Administration. Database Administration Guide. System Administration Guide. Application Development Guide. DB2 Security Guide. SQL Performance Guide. QMF Guide. Naming Conventions. Migration and Turnover Procedures. Design Review Guidelines. Operational Support. Political Issues. Environmental Support. Tool Requirements. Summary.
VIII. DISTRIBUTED DB2.
The Advantages of Data Distribution. DB2 Data Distribution. DB2 Data Warehousing.39. DRDA.
What Is DRDA? Benefits of DRDA. What about RDA? DRDA Functions. Application Requester. Application Server. Database Server. What Is Returned. DRDA Architectures and Standards. Advanced Program-to-Program Communication (APPC). Distributed Data Management (DDM). Formatted Data: Object Content Architecture (FD:OCA). Character Data Representation Architecture (CDRA). The Five DRDA Levels. User-Assisted Distribution. Remote Request. Remote Unit of Work. Distributed Unit of Work. Distributed Request. Putting It All Together. Summary.40. Distributed DB2.
Distributing Data Using DB2. The Basics. The Communication Database. Distributed Terms. DB2 Support for the DRDA Levels. Remote Request. Remote Unit of Work (RUW). Distributed Unit of Work (DUW). Methods of Accessing Distributed Data. Application-Directed Data Access. System-Directed Data Access. System-Directed Versus Application-Directed. Packages for Static SQL. Two-Phase Commit. Two-Phase Commit Terminology. What Are the Two Phases? Distributed Thread Support. Miscellaneous Distributed Topics. Combining DRDA and Private Protocol Requests. Combining DB2 Releases. Workstation DB2. Developing Client/Server Applications. ASCII Server Support. Native TCP/IP Support. Summary.41. Distribution Guidelines.
Distribution Behind the Scenes. Block Fetch. Coding Cursors to Encourage Block Fetch. Data Currency. Limited Versus Continuous Block Fetch. Dynamic Cursor Pre-Open. Distributed Performance Problems. Analyzing Distributed Throughput. Analyzing Distributed Response Time. General Distributed Performance Guidelines. Distributed Database Design Issues. Fragmentation. Replication. Snapshots. Distributed Data Placement. Distributed Optimization. Distributed Security Guidelines. Come-From Checking. Authid Translation. Miscellaneous Security Guidelines. Miscellaneous Distributed Guidelines. Summary.42. Data Warehousing with DB2.
Defining the Basic Terms. What Is a Data Warehouse? Operational Data Versus the Data Warehouse. What Is a Data Mart? What Is an Operational Data Store? What Is OLAP? Designing a Data Warehouse. The Role of Metadata. Star Schema. Populating a Data Warehouse. Replication Versus Propagation. Snapshots. Data Transformation. Accessing the Data Warehouse. Managing the Data Warehouse. The Big Picture. Data Warehouse Guidelines. Summary.
IX. APPENDIXES.Appendix A. DB2 SQLCODE and SQLSTATE Values.
The Activity Table: DSN8610.ACT. The Department Table: DSN8610.DEPT. The Employee Table: DSN8610.EMP. The Employee Photo and Resume Table: DSN8610.EMP_PHOTO_RESUME. The Employee Assignment Table: DSN8610.EMPPROJACT. The Project Table: DSN8610.PROJ. The Project Activity Table: DSN8610.PROJACT. The Sample STOGROUP. Sample Databases and Tablespaces.Appendix E. DB2 Manuals.
DB2 Standard Issue Manuals. DB2 Redbooks. Other DB2-Related Manuals. Query Management Facility (QMF) Standard Manuals. DB2 Administration Tool Standard Manual. Data Propagator (DPROP) Standard Manual. DB2 Performance Monitor (DB2-PM) Standard Manuals. Net.Data Standard Manuals.Appendix F. Type 1 Indexes.
Basic Index Structure. Type 1 Index Data Pages. Synopsis.Appendix G. Valid DB2 Data Types.
The DB2 Family. Some Major Differences. Summary.Appendix J. Summary of DB2 Version 4, Version 5, and Version 6 Changes.
DB2 Version 6 Features. Database Administration Features. Utility Features. Programming Features. Performance Features. DB2 Catalog Impact. DB2 Version 5 Features. Database Administration Features. Utility Features. Programming Features. Performance Features. DB2 Catalog Impact. DB2 Version 4 Features. Database Administration Features. Client/Server Features. Performance Features. Programming Features. DB2 Catalog Impact.Index.