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
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.
|
Ask a Question About this Product More... |
|
