The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
The eBook copy of this book is not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.
DB2 Developer's Guide is the field's #1 go-to source for on-the-job information on programming and administering DB2 on IBM z/OS mainframes. Now, three-time IBM Information Champion Craig S. Mullins has thoroughly updated this classic for DB2 v9 and v10. Mullins fully covers new DB2 innovations including temporal database support; hashing; universal tablespaces; pureXML; performance, security and governance improvements; new data types, and much more. Using current versions of DB2 for z/OS, readers will learn how to: * Build better databases and applications for CICS, IMS, batch, CAF, and RRSAF * Write proficient, code-optimized DB2 SQL * Implement efficient dynamic and static SQL applications * Use binding and rebinding to optimize applications * Efficiently create, administer, and manage DB2 databases and applications * Design, build, and populate efficient DB2 database structures for online, batch, and data warehousing * Improve the performance of DB2 subsystems, databases, utilities, programs, and SQL stat DB2 Developer's Guide, Sixth Editionbuilds on the unique approach that has made previous editions so valuable. It combines: * Condensed, easy-to-read coverage of all essential topics: information otherwise scattered through dozens of documents * Detailed discussions of crucial details within each topic * Expert, field-tested implementation advice * Sensible examples
Craig S. Mullins is a data management strategist, researcher, and consultant. He is president and principal consultant of Mullins Consulting, Inc. and the publisher and editor of The Database Site (www.TheDatabaseSite.com). Craig has also been appointed as an Information Champion by IBM.
Craig has extensive experience in all facets of database systems development, including systems analysis and design, database and system administration, data analysis, and developing and teaching DB2 and database development classes. He has worked with DB2 since Version 1 and has experience in multiple roles, including programmer, DBA, instructor, and analyst. His experience spans industries, having worked for companies in the following fields: manufacturing (USX Corporation), banking (Mellon Bank), utilities (Duquesne Light Company), commercial software development (BMC Software, NEON Enterprise Software, and PLATINUM Technology, Inc.), consulting (ASSET, Inc. and Mullins Consulting, Inc.), and computer industry analysis (Gartner Group). In addition, Craig authored many of the popular “Platinum Monthly DB2 Tips” and worked on Platinum’s DB2 system catalog and access path posters.
Craig is a regular lecturer at industry conferences. You may have seen him present at such events as the International DB2 Users Group (IDUG), the IBM Information on Demand (IOD) Conference, the IBM DB2 Technical Conference, SHARE, DAMA, CMG, or at one of many regional user groups throughout the world. Craig is a member of the IDUG Volunteers Hall of Fame.
Craig is also the author of Database Administration: The Complete Guide to Practices and Procedures(ISBN 0-201-74129-6). This book offers the industry’s only comprehensive guide to heterogeneous database administration.
Craig is a frequent contributor to computer industry publications, with hundreds of articles published over the past couple decades. His articles have been published in Byte, DB2 Update, Database Programming & Design, DBMS, Data Management Review, zJournal, and many others. Craig writes four regular columns, including “The DBA Corner” for Database Trends and Applications, “The Database Report” for The Data Administration Newsletter, “z/Data Perspectives” for zJournal, and “The Buffer Pool” for IDUG Solutions Journal. He also writes a blog focusing on DB2 topics at http://db2portal.blogspot.com. Complete information on Craig’s published articles and books can be found on his website at www.craigsmullins.com.
Craig graduated cum laude from the University of Pittsburgh with a B.S. degree and a dual major in computer science and economics. Follow Craig on Twitter at www.twitter.com/craigmullins.
Table of Contents
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 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 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 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