DB2 Developer's Guide

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.  

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