DB2 Developer's Guide

by Mullins, Craig S.

• ISBN13: 9780672326134
• ISBN10: 0672326132
• Edition: 5th
• Format: Paperback
• Copyright: 2004-05-11
• Publisher: Sams Publishing

Summary

Learn the best techniques and tricks from expert author Craig Mullins. Apply these real-world pieces of advice, undocumented tips, solutions, projects, and techniques to your own database management system. Mullins gives you what you need to take your DB2 development to the next level. Written by a developer for developers, DB2 Developers Guide, Fifth Edition provides a solutions-oriented approach to learning the foundation and capabilities of this latest version of the worlds number one database management system. LEARN THE CONCEPTS AND BUILD THE APPLICATIONS Implement innovative shortcuts, tips, tricks, techniques, and development guidelines to optimize all facets of DB2 development and administration Understand the guidelines for binding DB2 application plans and packages Use expert advice to implement distributed DB2 applications Connect your DB2 databases to the World Wide Web Review exhaustive coverage of V6 topicsincluding triggers, user-defined functions, stored procedure extensions, predictive governing, hiperspace bufferpools, the REBUILD utility, the DSSIZE parameter, user-defined distinct types, auxiliary tables, and more Read implementation information and guidance for large objects to store multimedia datasuch as audio, video, and imagesin your DB2 for OS/390 databases (BLOBs, CLOBs, DBCLOBs, and DB2 extenders) Learn how and why to use the new EXPLAIN tables - DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE Discover how to implement a procedural DBA function to manage triggers, stored procedures, and UDFs

Author Biography

Craig S. Mullins is Director of DB2 Technology Planning for BMC Software, Inc. He 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 Sybase classes. Craig is a regular lecturer at industry conferences and also frequently writes for computer industry publications.

Table of Contents

Introduction	1	(4)
Part I SQL Tools, Tips, and Tricks
The Magic Words	5	(45)
An Overview of SQL	6	(9)
SQL Tools of the Trade	15	(22)
Static SQL	37	(2)
Dynamic SQL	39	(1)
SQL Performance Factors	40	(10)
Data Manipulation Guidelines	50	(60)
A Bag of Tricks	50	(3)
SQL Access Guidelines	53	(24)
Complex SQL Guidelines	77	(17)
Common Table Expressions and Recursion	94	(5)
Working with NULLs	99	(2)
Date and Time Guidelines	101	(4)
Data Modification Guidelines	105	(5)
Using DB2 Functions	110	(21)
Column Functions	110	(5)
Scalar Functions	115	(10)
The RAISE_ERROR Functions	125	(1)
MQSeries Built-in Functions	126	(1)
XML Publishing Built-in Functions	127	(1)
Built-in Function Guidelines	128	(3)
Using DB2 User-Defined Functions and Data Types	131	(29)
What Is a User-Defined Function?	131	(1)
Types of User-Defined Functions	131	(20)
What Is a User-Defined Data Type?	151	(1)
User-Defined Data Types and Strong Typing	152	(8)
Data Definition Guidelines	160	(116)
An Overview of DB2 Database Objects	160	(1)
DB2 Databases	161	(3)
Creating and Using DB2 Table Spaces	164	(35)
DB2 Storage and STOGROUPs	199	(5)
Table Guidelines	204	(29)
Normalization and Denormalization	233	(12)
Assuring Data Integrity in DB2	245	(10)
Views, Aliases, and Synonyms	255	(11)
Index Guidelines	266	(1)
Naming Conventions	266	(8)
Miscellaneous DDL Guidelines	274	(2)
DB2 Indexes	276	(22)
Creating Indexes	278	(9)
Index Guidelines	287	(11)
Database Change Management and Schema Evolution	298	(15)
Online Schema Changes	299	(14)
Using DB2 Triggers for Integrity	313	(16)
What Is a Trigger?	313	(11)
Trigger Guidelines	324	(5)
Large Objects and Object/Relational Databases	329	(20)
Defining the Term ``Object/Relational''	329	(1)
What Is a Large Object?	330	(7)
LOB Guidelines	337	(3)
DB2 Extenders	340	(9)
DB2 Security and Authorization	349	(17)
Granting and Revoking Privileges	349	(17)
Part II DB2 Application Development
Using DB2 in an Application Program	366	(66)
Embedded SQL Basics	367	(2)
Embedded SQL Guidelines	369	(13)
Host Variables	382	(7)
Programming with Cursors	389	(10)
Embedded Select and Cursor Coding Guidelines	399	(4)
Modifying Data with Embedded SQL	403	(2)
Application Development Guidelines	405	(9)
Batch Programming Guidelines	414	(10)
Online Programming Guidelines	424	(6)
General SQL Coding Guidelines	430	(2)
Dynamic SQL Programming	432	(22)
Dynamic SQL Performance	432	(2)
Dynamic SQL Guidelines	434	(8)
Reasons You Should Know Dynamic SQL	442	(1)
Execute Immediate	443	(2)
Non-Select Dynamic SQL	445	(3)
Fixed-List Select	448	(2)
Varying-List Select	450	(4)
Program Preparation	454	(51)
Program Preparation Steps	454	(6)
Running a DB2 Program	460	(1)
Preparing a DB2 Program	461	(13)
What Is a DBRM?	474	(1)
What Is a Plan?	474	(1)
What Is a Package?	475	(5)
What Is a Collection?	480	(2)
Versions	482	(1)
Program Preparation Objects	482	(1)
Program Preparation Guidelines	483	(1)
DCLGEN Guidelines	484	(1)
Precompiler Guidelines	485	(2)
BIND Guidelines	487	(17)
Linkage Editor Guidelines	504	(1)
Alternative DB2 Application Development Methods	505	(29)
Developing Applications Using Only SQL	505	(3)
Using Client/Server Programming Languages	508	(3)
Using Fourth-Generation Languages	511	(2)
Using CASE	513	(4)
Using Report Writers	517	(1)
Using ODBC (Call Level Interface)	518	(4)
Using Java and DB2	522	(9)
Using REXX and DB2	531	(3)
Using DB2 Stored Procedures	534	(29)
What Is a Stored Procedure?	535	(3)
Implementing DB2 Stored Procedures	538	(16)
The Procedural DBA	554	(1)
Procedural SQL	554	(7)
DB2 Development Center	561	(2)
The Procedural DBA	563	(7)
The Classic Role of the DBA	564	(1)
The Role of the Procedural DBA	565	(5)
DB2 and the Internet	570	(27)
The Internet Phenomenon	570	(3)
Accessing DB2 Over the Internet	573	(5)
Using XML and DB2	578	(5)
Finding DB2 Information Using the Internet	583	(14)
Part III DB2 In-Depth
The Doors to DB2	597	(83)
TSO (Time-Sharing Option)	599	(24)
CICS (Customer Information Control System)	623	(34)
IMS (Information Management System)	657	(13)
CAF (Call Attach Facility)	670	(4)
RRSAF (Recoverable Resource Manager Services Attach Facility)	674	(1)
Comparison of the Environments	675	(3)
The Critical Mass	678	(2)
Data Sharing	680	(18)
Data Sharing Benefits	680	(2)
Data Sharing Requirements	682	(3)
The DB2 Coupling Facility	685	(4)
Data Sharing Naming Conventions	689	(1)
Data Sharing Administration	690	(4)
Data Sharing Application Development Guidelines	694	(1)
Data Sharing Administration Guidelines	695	(3)
DB2 Behind the Scenes	698	(22)
The Physical Storage of Data	698	(17)
What Makes DB2 Tick	715	(5)
The Optimizer	720	(47)
Physical Data Independence	721	(1)
How the Optimizer Works	722	(2)
Filter Factors	724	(2)
Access Path Strategies	726	(33)
Other Operations Performed by the Optimizer	759	(4)
Access Path Strategy Compatibility	763	(4)
The Table-Based Infrastructure of DB2	767	(13)
The DB2 Catalog	767	(10)
The DB2 Directory	777	(3)
Locking DB2 Data	780	(32)
How DB2 Manages Locking	780	(3)
Locks Versus Latches	783	(1)
Lock Duration	783	(3)
Table Space Locks	786	(2)
Table Locks	788	(1)
Page Locks	789	(1)
Row Locks	790	(2)
Lock Suspensions, Timeouts, and Deadlocks	792	(3)
Partition Independence	795	(3)
Lock Avoidance	798	(1)
Data Sharing Global Lock Management	799	(2)
LOBs and Locking	801	(2)
DB2 Locking Guidelines	803	(6)
Other DB2 Components	809	(1)
The Big Picture	810	(2)
Part IV DB2 Performance Monitoring
Traditional DB2 Performance Monitoring	812	(47)
DB2 Traces	813	(6)
Trace Destinations	819	(1)
Tracing Guidelines	820	(1)
DB2 Performance Monitor (DB2 PM)	821	(25)
Using DB2 PM	846	(3)
Online DB2 Performance Monitors	849	(4)
Viewing DB2 Console Messages	853	(5)
Displaying the Status of DB2 Resources	858	(1)
Monitoring z/OS and OS/390	858	(1)
Using Explain	859	(25)
How Explain Works	859	(2)
Access Paths and the Plan_Table	861	(14)
Cost Estimates and the DSN_Statement_Table	875	(3)
Function Resolution and the DSN_Function_Table	878	(1)
Explain Guidelines	879	(5)
DB2 Object Monitoring Using the DB2 Catalog and RTS	884	(34)
DB2 Catalog Queries	884	(25)
Real Time Statistics	909	(8)
Reviewing the Rules for an Effective Monitoring Strategy	917	(1)
Part V DB2 Performance Tuning
Tuning DB2's Environment	918	(23)
Tuning the z/OS and OS/390 Environment	918	(21)
Tuning the Teleprocessing Environment	939	(2)
Tuning DB2's Components	941	(51)
Tuning the DB2 Subsystem	941	(26)
Tuning the Database Design	967	(2)
Tuning the Application	969	(18)
The Causes of DB2 Performance Problems	987	(5)
DB2 Resource Governing	992	(7)
The Resource Limit Facility	992	(5)
The QMF Governor	997	(2)
Part VI DB2 Performance Utilities and Commands
An Introduction to DB2 Utilities	999	(25)
Generating Utility JCL	999	(4)
Monitoring DB2 Utilities	1003	(2)
Version 7 and the State of IBM DB2 Utilities	1005	(2)
Using Listdef and Template	1007	(13)
Issuing SQL Statements in DB2 Utilities	1020	(4)
Data Consistency Utilities	1024	(24)
The Check Utility	1025	(1)
The Check Data Option	1025	(8)
The Check Lob Option	1033	(2)
The Check Index Option	1035	(3)
The Repair Utility	1038	(1)
The Repair DBD Option	1039	(1)
The Repair Locate Option	1040	(2)
The Repair Set Option	1042	(3)
Repair and Versions	1045	(1)
The Report Utility	1045	(1)
The Diagnose Utility	1046	(2)
Backup and Recovery Utilities	1048	(35)
The Copy Utility	1049	(9)
The Copytocopy Utility	1058	(3)
The Mergecopy Utility	1061	(3)
The Quiesce Utility	1064	(3)
The Recover Utility	1067	(1)
The Recover Utility	1068	(7)
The Rebuild Index Utility	1075	(3)
The Repair Utility	1078	(1)
The Report Recovery Utility	1078	(2)
Backing Up and Restoring the System	1080	(3)
Data Organization Utilities	1083	(48)
The Load Utility	1083	(20)
The Unload Utility	1103	(4)
The Reorg Utility	1107	(24)
Catalog Manipulation Utilities	1131	(23)
The Catenfm Utility	1131	(1)
The Catmaint Utility	1131	(1)
The Dsnjcnvb Utility	1132	(1)
The Modify Utility	1133	(5)
The Runstats Utility	1138	(13)
The Stospace Utility	1151	(3)
Stand-alone Utilities and Sample Programs	1154	(25)
The Stand-alone Utilities	1154	(17)
DB2 Sample Programs	1171	(8)
DB2 Commands	1179	(26)
DB2 Environment Commands	1179	(3)
Information-Gathering Commands	1182	(10)
Administrative Commands	1192	(5)
Environment Control Commands	1197	(1)
DSN Commands	1198	(1)
IMS Commands	1199	(2)
CICS Commands	1201	(1)
TSO Commands	1202	(1)
IRLM Commands	1203	(2)
DB2 Utility and Command Guidelines	1205	(9)
Utility Guidelines	1205	(6)
The Pending States	1211	(3)
DB2 Contingency Planning	1214	(14)
What Is a Disaster?	1214	(10)
DB2 Environmental Considerations	1224	(1)
DB2 Contingency Planning Guidelines	1225	(3)
Part VII The Ideal DB2 Environment
Components of a Total DB2 Solution	1228	(30)
DB2 Tools	1228	(27)
DB2 Tools Vendors	1255	(3)
Organizational Issues	1258	(22)
Education	1258	(7)
Standards and Procedures	1265	(11)
Operational Support	1276	(2)
Political Issues	1278	(1)
Environmental Support	1279	(1)
Tool Requirements	1279	(1)
Part VIII Distributed DB2
DRDA	1280	(10)
What Is DRDA?	1280	(1)
DRDA Functions	1281	(2)
DRDA Architectures and Standards	1283	(2)
The Five DRDA Levels	1285	(2)
Putting It All Together	1287	(2)
Distributed DB2	1289	(1)
Distributed DB2	1290	(15)
Distributing Data Using DB2	1290	(1)
DB2 Support for the DRDA Levels	1291	(1)
Methods of Accessing Distributed Data	1292	(4)
System-Directed Versus Application-Directed	1296	(1)
Packages for Static SQL	1297	(1)
Two-Phase Commit	1297	(4)
Miscellaneous Distributed Topics	1301	(4)
DB2 Connect	1305	(11)
An Overview of IBM DB2 Connect	1305	(11)
Distribution Guidelines	1316	(20)
Distribution Behind the Scenes	1316	(2)
Block Fetch	1318	(4)
Dynamic Cursor Pre-Open	1322	(1)
Distributed Performance Problems	1322	(5)
Distributed Database Design Issues	1327	(2)
Distributed Data Placement	1329	(1)
Distributed Optimization	1330	(1)
Distributed Security Guidelines	1331	(2)
Miscellaneous Distributed Guidelines	1333	(3)
Data Warehousing with DB2	1336	(35)
Defining the Basic Terms	1337	(3)
Designing a Data Warehouse	1340	(3)
Populating a Data Warehouse	1343	(6)
Accessing the Data Warehouse	1349	(1)
Managing the Data Warehouse	1349	(1)
The Big Picture	1350	(1)
DB2 Data Warehouse Center	1351	(2)
Materialized Query Tables	1353	(10)
General Data Warehouse Guidelines	1363	(5)
DB2-Specific Data Warehousing Guidelines	1368	(3)
On The Web
Special Consideration for DB2 Version 6	1371	(5)
Type 1 Indexes	1371	(1)
Shared Read Only Data	1372	(1)
Recover Index	1372	(1)
Host Variables Without Colons	1373	(1)
Dataset Passwords	1373	(1)
Stored Procedure Registration	1374	(1)
Synopsis	1375	(1)
Short Summary of DB2 V4 Through V6 Changes	1376	(5)
DB2Version 6 Features	1376	(2)
DB2Version 5 Features	1378	(1)
DB2Version 4 Features	1379	(2)
Type 1 Indexes	1381	(6)
Basic Index Structure	1381	(6)
Part IX Appendixes
A DB2 Sample Tables	1387	(9)
B DB2 Tools Vendors	1396	(11)
The Vendor List	1398	(9)
C Valid DB2 Data Types	1407	(4)
D DB2 Limits	1411	(5)
E DB2 on Other Platforms	1416	(6)
The DB2 Family	1416	(4)
Packaging and Naming Issues	1420	(2)
F DB2 Version 7 Overview	1422	(15)
E-Business Enhancements	1422	(1)
Application Development Enhancements	1423	(6)
Data Management Enhancements	1429	(6)
Business Intelligence Enhancements	1435	(1)
Additional V7 Information	1435	(2)
G DB2 Version 8 Overview	1437	(10)
Architecture	1437	(1)
Database Administration	1438	(4)
Programming and Development	1442	(2)
Migration to DB2 V8	1444	(3)
H Reorganizing the DB2 Catalog	1447
When Should the DB2 Catalog and Directory Be Reorganized?	1448
Index	1371