DB2 Universal Database (UDB) supports many different types of applications, on many different kinds of data, in many different software and hardware environments.
This book provides a complete guide to DB2 UDB Version 5 in all its aspects, including the interfaces that support end users, application developers, and database administrators. It is complementary to the IBM product documentation, providing a clear and informal explanation of how the features of DB2 were intended to be used. It is an extensive revision of the author's earlier book, Using the New DB2: IBM's Object-Relational Database System.
* Offers complete and self-contained information, and does not assume prior knowledge of DB2, SQL, or relational database concepts
* Covers elementary principles of database management as well as the advanced features of UDB, including recursive queries, constraints, triggers, user-defined datatypes, stored procedures, parallel databases, and graphical tools for database administration
* Includes dozens of practical tips that will save readers many hours of work in developing database applications
* Provides hundreds of tested examples written in SQL, C, C++, and Java, all of which are available on the MKP web site
Muu info
* Offers complete and self-contained information, and does not assume prior knowledge of DB2, SQL, or relational database concepts * Covers elementary principles of database management as well as the advanced features of UDB, including recursive queries, constraints, triggers, user-defined datatypes, stored procedures, parallel databases, and graphical tools for database administration * Includes dozens of practical tips that will save readers many hours of work in developing database applications * Provides hundreds of tested examples written in SQL, C, C++, and Java, all of which are available on the MKP web site
Foreword vii(2) Don Haderle Foreword ix(8) Jim Gray Preface xvii CHAPTER 1 INTRODUCTION 1(34) 1.1 About This Book 2(6) 1.1.1 Notational Conventions 4(1) 1.1.2 Syntax Diagrams 5(2) 1.1.3 Examples 7(1) 1.1.4 Tips 7(1) 1.2 Product Overview 8(17) 1.2.1 UDB Clients and Servers 8(4) 1.2.2 Related Products 12(3) 1.2.3 Instances and Databases 15(2) 1.2.4 Interactive Tools 17(1) 1.2.5 Application Programs 18(2) 1.2.6 Dynamic Applications 20(2) 1.2.7 Stored Procedures 22(1) 1.2.8 User Roles 23(2) 1.3 A Brief History of SQL 25(10) 1.3.1 System R 26(1) 1.3.2 Products and Standards 27(1) 1.3.3 Some Controversial Decisions 28(4) 1.3.4 References 32(3) CHAPTER 2 BASICS 35(96) 2.1 Tables 36(3) 2.1.1 Example Database 35(4) 2.2 Names and Schemas 39(2) 2.3 Basic SQL Datatypes 41(2) 2.4 Queries 43(31) 2.4.1 Expressions 44(4) 2.4.2 Datetime Arithmetic 48(1) 2.4.3 Casting 49(1) 2.4.4 Search Conditions 50(6) 2.4.5 Joins 56(2) 2.4.6 Column Functions 58(2) 2.4.7 Grouping 60(2) 2.4.8 Query Blocks 62(3) 2.4.9 Queries and Literal Tables 65(4) 2.4.10 SELECT Statement 69(2) 2.4.11 VALUES Statement 71(2) 2.4.12 SQLCODE and SQLSTATE 73(1) 2.5 Data Modification 74(6) 2.5.1 INSERT Statement 74(2) 2.5.2 UPDATE Statement 76(2) 2.5.3 DELETE Statement 78(2) 2.6 Data Definition 80(23) 2.6.1 Creating a Table 81(1) 2.6.2 Altering a Table 82(1) 2.6.3 Renaming a Table 83(1) 2.6.4 Creating an Alias 84(2) 2.6.5 Creating a View 86(5) 2.6.6 Creating an Index 91(3) 2.6.7 Creating a Schema 94(1) 2.6.8 Dropping an Object 95(2) 2.6.9 Commenting on an Object 97(2) 2.6.10 Normalization 99(4) 2.7 Protecting Data Consistency 103(12) 2.7.1 Transactions 103(6) 2.7.2 Database Connections 109(6) 2.8 Authorization 115(16) 2.8.1 Instance-Level Authorities 116(2) 2.8.2 Database-Level Authorities 118(1) 2.8.3 Table and View Privileges 118(2) 2.8.4 Index Privileges 120(1) 2.8.5 Schema Privileges 120(1) 2.8.6 Package Privileges 120(2) 2.8.7 GRANT and REVOKE Statements 122(3) 2.8.8 Authorization Checking 125(6) CHAPTER 3 INTERACTIVE SQL 131(18) 3.1 DB2 Tools 132(9) 3.1.1 The Command Center 132(4) 3.1.2 The Script Center 136(1) 3.1.3 The Journal 137(2) 3.1.4 The Information Center 139(2) 3.2 The Command Line Processor 141(4) 3.2.1 Command Options 143(2) 3.3 Interactive Commands 145(4) 3.3.1 Controlling Isolation Level 146(1) 3.3.2 Controlling Connection Type 146(1) 3.3.3 Getting Help 147(1) 3.3.4 Comments 147(2) CHAPTER 4 STATIC SQL 149(50) 4.1 Using Static SQL in C Programs 150(34) 4.1.1 Host Variables 151(2) 4.1.2 The SQL Declare Section 153(2) 4.1.3 Exchanging Double-Byte Strings 155(2) 4.1.4 Return Codes and Messages 157(3) 4.1.5 WHENEVER Statement 160(2) 4.1.6 Cursor Declarations 162(3) 4.1.7 OPEN Statement 165(1) 4.1.8 FETCH Statement 166(1) 4.1.9 CLOSE Statement 167(1) 4.1.10 Single-Row SELECT and VALUES Statements 168(1) 4.1.11 Positioned UPDATE and DELETE Statements 169(3) 4.1.12 Using Cursors with Interactive SQL 172(1) 4.1.13 Compound SQL 173(2) 4.1.14 Example Program PARTS1: Ordering Parts 175(9) 4.2 Using Static SQL in C++ Programs 184(4) 4.3 Building an Application Program 188(11) 4.3.1 Precompiling a Program 191(4) 4.3.2 Rebinding a Package 195(4) CHAPTER 5 QUERY POWER 199(72) 5.1 CASE Expressions 200(8) 5.1.1 Simple Form 200(2) 5.1.2 General Form 202(4) 5.1.3 RAISE_ERROR Function 206(1) 5.1.4 NULLIF and COALESCE Functions 207(1) 5.2 Subqueries 208(11) 5.2.1 Scalar Subqueries 213(2) 5.2.2 Table Expressions 215(4) 5.3 Table Functions 219(2) 5.4 Explicit Joins 221(7) 5.5 Extended FROM Clause 228(3) 5.6 Super Groups 231(16) 5.6.1 ROLLUP 233(5) 5.6.2 CUBE 238(5) 5.6.3 GROUPING SETS 243(1) 5.6.4 Multiple Grouping Specifications 244(3) 5.7 Common Table Expressions 247(4) 5.8 Recursion 251(20) 5.8.1 Recursion with Computation 254(5) 5.8.2 Recursive Searching 259(12) CHAPTER 6 DATATYPES AND FUNCTIONS 271(118) 6.1 Large Objects 272(24) 6.1.1 Creating LOB Columns 274(3) 6.1.2 Declaring Large-Object Variables in C and C++ 277(5) 6.1.3 Locators 282(5) 6.1.4 File References 287(3) 6.1.5 Limitations of LOB Datatypes 290(2) 6.1.6 Example Program SCHOLAR: Processing Scholarship Applications 292(4) 6.2 Distinct Types 296(10) 6.2.1 Creating Distinct Types 296(3) 6.2.2 Casting Functions 299(3) 6.2.3 Using Distinct Types 302(1) 6.2.4 Assigning Distinct Types 303(3) 6.3 Function Path 306(4) 6.3.1 SET CURRENT FUNCTION PATH Statement 307(3) 6.4 User-Defined Functions 310(63) 6.4.1 Creating a Sourced Function 313(6) 6.4.2 Creating an External Scalar Function 319(9) 6.4.3 Function Resolution 328(3) 6.4.4 Implementing an External Scalar Function 331(10) 6.4.5 Installing an External Function 341(2) 6.4.6 Using Locators with External Functions 343(6) 6.4.7 Scratchpad Functions 349(9) 6.4.8 Table Functions 358(7) 6.4.9 Using External Functions with Distinct Types 365(2) 6.4.10 Writing an External Function in Java 367(2) 6.4.11 External Functions and OLE Automation 369(1) 6.4.12 Dropping a Function 370(2) 6.4.13 Commenting on a Function 372(1) 6.5 Steps Toward Objects 373(7) 6.5.1 Example: A Polygon Datatype 373(7) 6.6 Datatype Conversions 380(9) 6.6.1 Promotion of Function Arguments 381(1) 6.6.2 UNION Semantics 381(2) 6.6.3 Assignment 383(1) 6.6.4 Casting 384(5) CHAPTER 7 ACTIVE DATA 389(72) 7.1 Constraints 390(10) 7.1.1 NOT NULL Constraints 391(1) 7.1.2 Column Defaults 391(2) 7.1.3 Unique Constraints 393(1) 7.1.4 Check Constraints 393(1) 7.1.5 Primary Key Constraints 394(1) 7.1.6 Foreign Key Constraints 395(5) 7.2 Creating and Dropping Constraints 400(10) 7.2.1 CREATE TABLE Statement 401(7) 7.2.2 ALTER TABLE Statement 408(2) 7.3 Triggers 410(24) 7.3.1 Creating and Dropping Triggers 414(24) 7.3.2 Assignment Statement 416(1) 7.3.3 SIGNAL Statement 417(1) 7.3.4 Before Triggers 418(4) 7.3.5 After Triggers 422(5) 7.3.6 Recursive Triggers 427(4) 7.3.7 Comparing Constraints and Triggers 427(1) 7.3.8 Interactions Among Constraints and Triggers 432(2) 7.4 Designing an Active Database 434(18) 7.5 Binding and Dependencies 452(9) 7.5.1 Conservative Binding Semantics 453(2) 7.5.2 Types of Dependencies 455(6) CHAPTER 8 DYNAMIC SQL 461(90) 8.1 Call Level Interface 462(36) 8.1.1 Handles 464(1) 8.1.2 Configuring CLI 465(1) 8.1.3 Summary of CLI Functions 466(5) 8.1.4 Typed Parameter Markers 471(1) 8.1.5 Example Program LOADER1 472(10) 8.1.6 Example Program QUERY1 482(16) 8.2 Using Dynamic SQL with Java 498(16) 8.2.1 JDBC Applications 498(4) 8.2.2 Example Program LOADER2 502(5) 8.2.3 JDBC Applets 507(7) 8.3 Embedded Dynamic SQL 514(37) 8.3.1 Embedded Dynamic Statements 514(5) 8.3.2 Example Program LOADER3 519(7) 8.3.3 The SQLDA Descriptor 526(4) 8.3.4 Using an SQLDA in a PREPARE or DESCRIBE Statement 530(4) 8.3.5 Using an SQLDA in an OPEN, FETCH, EXECUTE, or CALL Statement 534(5) 8.3.6 Example Program QUERY3 539(12) CHAPTER 9 STORED PROCEDURES 551(28) 9.1 The Server Side 552(15) 9.1.1 Example Program SERVER1: A Stored Procedure for a Bank 553(5) 9.1.2 Rules for Implementing Stored Procedures 558(1) 9.1.3 Installing a Stored Procedure 559(6) 9.1.4 Writing a Stored Procedure in Java 565(1) 9.1.5 Writing a Stored Procedure in BASIC 566(1) 9.2 The Client Side 567(12) 9.2.1 The CALL Statement 567(5) 9.2.2 Calling a Stored Procedure from a CLI Client 572(4) 9.2.3 Result Sets 576(3) CHAPTER 10 DATABASE ADMINISTRATION 579(104) 10.1 Databases and Physical Space 580(9) 10.1.1 Tablespaces and Bufferpools 580(6) 10.1.2 Creating and Dropping Databases 586(1) 10.1.3 Wheres the Data? 587(2) 10.2 Parallel Databases 589(10) 10.2.1 Intra-Partition Parallelism 589(2) 10.2.2 Inter-Partition Parallelism 591(5) 10.2.3 Reconfiguring a Parallel System 596(3) 10.3 The Control Center 599(21) 10.3.1 Systems (General) 603(1) 10.3.2 Systems (Specific) 603(1) 10.3.3 Instances (General) 604(1) 10.3.4 Instances (Specific) 604(3) 10.3.5 Databases (General) 607(1) 10.3.6 Databases (Specific) 608(5) 10.3.7 Objects Within Databases 613(7) 10.4 The Client Configuration Assistant 620(4) 10.5 Commands 624(8) 10.5.1 Managing Instances 625(1) 10.5.2 The Profile Registry 626(1) 10.5.3 The Administration Server 627(1) 10.5.4 Other Operating System-Level Commands 628(2) 10.5.5 UDB Commands 630(2) 10.6 Managing Database Recovery 632(11) 10.6.1 Types of Recovery 633(2) 10.6.2 Recovery Commands 635(7) 10.6.3 Using the Journal for Recovery 642(1) 10.7 Moving Data in Bulk 643(16) 10.7.1 File Formats 644(1) 10.7.2 Exporting Data 645(1) 10.7.3 Importing Data 646(3) 10.7.4 Loading Data 649(4) 10.7.5 Check Pending State 653(3) 10.7.6 Loading a Partitioned Database 656(3) 10.8 Tuning for Performance 659(15) 10.8.1 Controlling the Optimizer 660(2) 10.8.2 Statistics 662(5) 10.8.3 Reorganizing Tables 667(3) 10.8.4 Explaining a Plan 670(4) 10.9 Monitoring the Database 674(9) 10.9.1 The Snapshot Monitor 676(4) 10.9.2 Event Monitors 680(3) APPENDIX A: Special Registers 683(4) APPENDIX B: Functions 687(22) B.1 Column Functions 688(2) B.2 Scalar Functions 690(16) B.3 Operators 706(3) B.3.1 Prefix Operators 706(1) B.3.2 Infix Operators 706(3) APPENDIX C: Typecodes 709(4) APPENDIX D: System Catalog Tables 713(44) D.1 SYSCAT Catalog Views 714(37) D.1.1 BUFFERPOOLNODES 714(1) D.1.2 BUFFERPOOLS 715(1) D.1.3 CHECKS 715(1) D.1.4 COLAUTH 716(1) D.1.5 COLCHECKS 717(1) D.1.6 COLDIST 717(1) D.1.7 COLUMNS 718(2) D.1.8 CONSTDEP 720(1) D.1.9 DATATYPES 721(1) D.1.10 DBAUTH 722(1) D.1.11 EVENTMONITORS 722(2) D.1.12 EVENTS 724(1) D.1.13 FUNCPARMS 724(1) D.1.14 FUNCTIONS 725(4) D.1.15 INDEXAUTH 729(1) D.1.16 INDEXES 729(2) D.1.17 KEYCOLUSE 731(1) D.1.18 NODEGROUPDEF 732(1) D.1.19 NODEGROUPS 732(1) D.1.20 PACKAGEAUTH 733(1) D.1.21 PACKAGEDEP 734(1) D.1.22 PACKAGES 734(3) D.1.23 PARTITIONMAPS 737(1) D.1.24 PROCEDURES 737(2) D.1.25 PROCPARMS 739(1) D.1.26 REFERENCES 739(1) D.1.27 SCHEMAAUTH 740(1) D.1.28 SCHEMATA 741(1) D.1.29 STATEMENTS 742(1) D.1.30 TABAUTH 742(1) D.1.31 TABCONST 743(1) D.1.32 TABLES 744(3) D.1.33 TABLESPACES 747(1) D.1.34 TRIGDEP 748(1) D.1.35 TRIGGERS 749(1) D.1.36 VIEWDEP 750(1) D.1.37 VIEWS 750(1) D.2 SYSSTAT Updatable Catalog Views 751(6) D.2.1 COLDIST 752(1) D.2.2 COLUMNS 752(1) D.2.3 FUNCTIONS 753(1) D.2.4 INDEXES 754(1) D.2.5 TABLES 755(2) APPENDIX E: Syntax for Host Variable Declarations in C and C++ 757(8) E.1 Basic Datatypes 758(3) E.1.1 Numeric Host Variables 758(1) E.1.2 String Host Variables 759(2) E.2 Large-Object Datatypes 761(4) E.2.1 LOB Host Variables 761(1) E.2.2 Locators and File References 761(4) APPENDIX F: IBM Publications 765(4) F.1 Platform-Independent Publications 765(2) F.2 Platform-Specific Publications 767(2) INDEX 769
Don Chamberlin is a member of the DB2 development team at the IBM Almaden Research Center, and an adjunct professor of computer engineering at Santa Clara University. Dr. Chamberlin is co-inventor of the original SQL database language. He is an ACM Fellow, a member of the National Academy of Engineering and holds a Ph.D. in electrical engineering from Stanford University.
