DB2 Developer's Guide

Preface xxiii PART I: SQL TECHNIQUES, TIPS, AND TRICKS Chapter 1 The Magic Words 3 An Overview of SQL 4 SQL Tools of the Trade 13 Static SQL 42 Dynamic SQL 44 SQL Performance Factors 45 Chapter 2 Data Manipulation Guidelines 56 A Bag of Tricks 56 SQL Access Guidelines 58 Complex SQL Guidelines 90 Common Table Expressions and Recursion 110 Working with Nulls 115 Date and Time Guidelines 119 Data Modification Guidelines 125 Chapter 3 Using DB2 Functions 135 Aggregate Functions 135 Scalar Functions 141 Table Functions 159 MQSeries Built-In Functions 159 XML Built-In Functions 161 The RAISE_ERROR Function 162 The CAST Operation 163 Built-In Function Guidelines 163 Chapter 4 Using DB2 User-Defined Functions and Data Types 167 What Is a User-Defined Function? 167 Types of User-Defined Functions (UDFs) 168 What Is a User-Defined Data Type? 190 User-Defined Data Types (UDTs) and Strong Typing 191 Chapter 5 Data Definition Guidelines 200 An Overview of DB2 Database Objects 200 DB2 Databases 201 Creating and Using DB2 Table Spaces 204 DB2 Storage and STOGROUPs 239 Table Guidelines 244 General Table Guidelines 275 Normalization and Denormalization 278 Assuring Data Integrity in DB2 290 Referential Integrity 290 Views, Aliases, and Synonyms 302 Index Guidelines 313 Naming Conventions 313 Miscellaneous DDL Guidelines 322 Chapter 6 DB2 Indexing and Hashing Guidelines 324 How an Index Works 324 Creating Indexes 326 DB2 Hashing and Hash Organized Tables 337 Index and Hash Guidelines 34 Chapter 7 Database Change Management, Schema Evolution, and Database Definition On Demand 53 Online Schema Changes 354 Versioning for Online Schema Changes 370 Chapter 8 Using DB2 Triggers 373 What Is a Trigger? 373 Trigger Guidelines 388 Chapter 9 Large Objects and Object/Relational Databases 393 Defining the Term "Object/Relational" 393 What Is a Large Object? 394 LOB Guidelines 403 DB2 Extenders 407 Chapter 10 pureXML: Using XML in DB2 for z/OS 408 What Is XML? 408 pureXML 412 XML-DB2 Guidelines 425 Chapter 11 Supporting Temporal Data in DB2 for z/OS 428 The Need for Temporal Data 428 DB2 Temporal Support 430 Temporal Data Guidelines 446 Summary 447 Chapter 12 DB2 Security, Authorization, and Auditing 448 Authorization and Privileges 448 Database Auditing 476 Using External Security (for Example, RACF, ACF2, and Top Secret) 480 PART II: DB2 APPLICATION DEVELOPMENT Chapter 13 Using DB2 in an Application Program 486 Embedded SQL Basics 487 Embedded SQL Guidelines 489 Host Variables 504 Programming with Cursors 511 Modifying Data with Embedded SQL 525 Application Development Guidelines 527 Batch Programming Guidelines 536 Online Programming Guidelines 547 General SQL Coding Guidelines 552 Introduction to Java 554 Using REXX and DB2 563 Developing Applications Using Only SQL 565 Chapter 14 Dynamic SQL Programming 567 What Is Dynamic SQL? 567 Dynamic SQL Versus Static SQL 569 The Four Classes of Dynamic SQL 576 pureQuery 588 Making Dynamic SQL More Static and Vice Versa 589 Dynamic SQL Guidelines 594 Chapter 15 Program Preparation 601 Program Preparation Steps 601 Running a DB2 Program 608 Preparing a DB2 Program 609 What Is a DBRM? 622 What Is a Plan? 622 What Is a Package? 623 What Is a Collection? 628 Versions 629 Converting DBRM-Based Plans in DB2 V10 630 Program Preparation Objects 631 Program Preparation Guidelines 632 Chapter 16 Using DB2 Stored Procedures 65 6 What Is a Stored Procedure? 657 Implementing DB2 Stored Procedures 661 Procedural SQL 678 The Procedural DBA 683 IBM Data Studio 687 Chapter 17 DB2 and the Internet 689 The Internet Phenomenon 689 Accessing DB2 over the Internet 692 Finding DB2 Information Using the Internet 695 PART III: DB2 IN-DEPTH Chapter 18 The Doors to DB2 704 DB2 Program Execution Basics 704 TSO (Time-Sharing Option) 706 CICS (Customer Information Control System) 726 IMS (Information Management System) 751 CAF (Call Attach Facility) 763 RRSAF (Recoverable Resource Manager Services Attach Facility) 767 Comparison of the Environments 768 Chapter 19 Data Sharing 772 Data Sharing Benefits 772 Data Sharing Requirements 774 The DB2 Coupling Facility 778 Data Sharing Naming Conventions 782 Data Sharing Administration 783 Data Sharing Application Development Guidelines 787 Data Sharing Administration Guidelines 788 Chapter 20 DB2 Behind the Scenes 792 The Physical Storage of Data 792 What Makes DB2 Tick 808 Specialty Processors 812 Chapter 21 The Optimizer 816 Physical Data Independence 817 How the Optimizer Works 818 Filter Factors 821 Screening 823 Access Path Strategies 824 Other Operations Performed by the Optimizer 868 Chapter 22 The Table-Based Infrastructure of DB2 874 The DB2 Catalog 874 The DB2 Directory 886 Chapter 23 Locking DB2 Data 889 How DB2 Manages Locking 889 Locks Versus Latches 892 Lock Duration 892 Table Space Locks 895 Table Locks 897 Page Locks 898 Row Locks 899 Lock Suspensions, Timeouts, and Deadlocks 901 Partition Independence 904 Lock Avoidance 908 Data Sharing Global Lock Management 911 LOBs and Locking 914 DB2 Locking Guidelines 916 Other DB2 Components 921 The Big Picture 922 PART IV: DB2 PERFORMANCE MONITORING Defining DB2 Performance. 926 Types of DB2 Performance Monitoring 927 Chapter 24 DB2 Performance Monitoring 928 DB2 Traces 929 Trace Destinations 936 Using IFCIDs 937 Tracing Guidelines 938 Performance Monitoring and Reporting: Online and Batch 940 Monitoring and Reporting Strategy 967 Performance Profiles 970 Viewing DB2 Console Messages 972 Displaying the Status of DB2 Resources 977 Monitoring z/OS 979 Chapter 25 Using EXPLAIN 980 How EXPLAIN Works 980 Access Paths and the PLAN_TABLE 982 Cost Estimates and the DSN_STATEMNT_TABLE 998 Function Resolution and the DSN_FUNCTION_TABLE 1001 Additional Explain Tables 1002 Explaining the Dynamic Statement Cache 1003 EXPLAIN Guidelines 1005 Additional Tools for Managing Access Paths 1012 Chapter 26 The Five R's 1014 Approaches to Rebinding 1014 A Best Practice Approach to Rebinding 1016 Chapter 27 DB2 Object Monitoring Using the DB2 Catalog and RTS 1021 DB2 Catalog Queries 1021 Real Time Statistics 1048 Reviewing the Rules for an Effective Monitoring Strategy 1058 PART V: DB2 PERFORMANCE TUNING Chapter 28 Tuning DB2's Environment 1064 Tuning the z/OS Environment 1064 Tuning the Teleprocessing Environment 1087 Chapter 29 Tuning DB2's Components 1089 Tuning the DB2 Subsystem 1089 Tuning the Database Design 1114 Tuning the Application 1116 The Causes of DB2 Performance Problems 1137 Chapter 30 DB2 Resource Governing 1143 The Resource Limit Facility 1143 PART VI: DB2 UTILITIES AND COMMANDS Chapter 31 An Introduction to DB2 Utilities 1152 Generating Utility JCL 1152 Monitoring DB2 Utilities 1156 The IBM DB2 Utilities 1158 Using LISTDEF and TEMPLATE 1159 Issuing SQL Statements in DB2 Utilities 1173 Chapter 32 Data Consistency Utilities 1176 The CHECK Utility 1177 The CHECK DATA Option 1177 The CHECK LOB Option 1186 The CHECK INDEX Option 1188 The REPAIR Utility 1191 The REPAIR DBD Option 1192 The REPAIR LOCATE Option 1193 The REPAIR SET Option 1196 REPAIR and Versions 1198 The REPORT Utility 1198 The DIAGNOSE Utility 1200 Chapter 33 Backup and Recovery Utilities 1201 The COPY Utility 1202 The COPYTOCOPY Utility 1215 The MERGECOPY Utility 1218 The QUIESCE Utility 1220 The RECOVER Utility 1224 The REBUILD INDEX Utility 1232 The REPAIR Utility 1235 The REPORT RECOVERY Utility 1235 Backing Up and Restoring the System 1236 Chapter 34 Data Movement and Organization Utilities 1240 The LOAD Utility 1240 The UNLOAD Utility 1260 The REORG Utility 1265 Chapter 35 Catalog Manipulation Utilities 1289 The CATENFM Utility 1289 The CATMAINT Utility 1289 The DSNJCNVB Utility 1290 The MODIFY RECOVERY Utility 1290 The MODIFY STATISTICS Utility 1293 The RUNSTATS Utility 1295 The STOSPACE Utility 1311 Chapter 36 Stand-Alone Utilities and Sample Programs 1314 The Stand-Alone Utilities 1314 DB2 Sample Programs 1332 Chapter 37 DB2 Commands 1340 DB2 Environment Commands 1340 Information-Gathering Commands 1343 Administrative Commands 1353 Environment Control Commands 1358 DSN Commands 1359 IMS Commands 1361 CICS Commands 1362 TSO Commands 1364 IRLM Commands 1364 Chapter 38 DB2 Utility and Command Guidelines 1366 Utility Guidelines 1366 The Pending States 1372 Chapter 39 DB2 Contingency Planning 1376 What Is a Disaster? 1376 DB2 Recovery Basics 1380 Additional DB2 Disaster Recovery Technologies 1387 DB2 Environmental Considerations 1388 DB2 Contingency Planning Guidelines 1390 PART VII: THE IDEAL DB2 ENVIRONMENT Chapter 40 Components of a Total DB2 Solution 1394 DB2 Tools 1394 DB2 Tools Vendors 1420 Chapter 41 Organizational Issues 1423 Education 1423 Standards and Procedures 1429 Operational Support. 1440 Political Issues 1441 Environmental Support 1443 Tool Requirements 1443 Part VIII Distributed DB2 The Advantages of Data Distribution 1446 DB2 Data Distribution 1446 DB2 Data Warehousing 1447 Chapter 42 DRDA 1448 What Is DRDA? 1448 DRDA Functions 1449 DRDA Architectures and Standards 1451 The Five DRDA Levels 1453 Putting It All Together 1455 Chapter 43 Distributed DB2 1458 Distributing Data Using DB2 1458 DB2 Support for the DRDA Levels 1460 Methods of Accessing Distributed Data 1460 Packages for Static SQL 1465 Two-Phase Commit 1466 Miscellaneous Distributed Topics 1470 Chapter 44 DB2 Connect 1473 An Overview of IBM DB2 Connect 1473 Chapter 45 Distribution Guidelines 1485 Distribution Behind the Scenes 1485 Block Fetch 1487 Dynamic Cursor Pre-Open 1491 Distributed Performance Problems 1491 Distributed Database Design Issues 1496 Distributed Data Placement 1499 Distributed Optimization 1500 Distributed Security Guidelines 1501 Miscellaneous Distributed Guidelines 1502 Chapter 46 Data Warehousing with DB2 1506 Defining the Basic Terms 1507 Designing a Data Warehouse 1510 Populating a Data Warehouse 1513 Accessing the Data Warehouse 1519 Managing the Data Warehouse 1520 The Big Picture 1520 IBM Data Warehousing Solutions 1521 Materialized Query Tables 1522 General Data Warehouse Guidelines 1533 DB2-Specific Data Warehousing Guidelines 1538 Index 1541