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 Edition builds 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
Reihe
Auflage
Sprache
Verlagsort
Verlagsgruppe
Zielgruppe
Maße
Höhe: 231 mm
Breite: 190 mm
Dicke: 55 mm
Gewicht
ISBN-13
978-0-13-283642-5 (9780132836425)
Schweitzer Klassifikation
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
