Preface | p. xv |
Introduction to DB2 | |
Database Concepts | p. 3 |
What is a Database? | p. 3 |
Roles in the Database Environment | p. 6 |
Database System Components | p. 7 |
Hardware | p. 7 |
Data | p. 7 |
Users | p. 8 |
Database Management Software | p. 8 |
Categories of DBMSs | p. 9 |
Data Models | p. 9 |
Four Types of DBMSs | p. 10 |
Relational Model | p. 12 |
Data Structure | p. 12 |
Data Manipulation | p. 14 |
Data Integrity | p. 14 |
Implementation of the Relational Model in DB2 | p. 14 |
Data Structure | p. 15 |
Data Manipulation | p. 15 |
Data Integrity | p. 15 |
DB2 Overview | p. 17 |
Origin and History | p. 17 |
DB2's Predecessors | p. 18 |
Benefits of DB2 | p. 18 |
Environment | p. 19 |
DB2 Attachments, DSN, and Threads | p. 20 |
Interactive Access to Data | p. 21 |
Components and Features | p. 21 |
IRLM, System Services, Data Base Services | p. 21 |
DBRMs, BIND, PLANs, and the Optimizer | p. 23 |
DB2 Directory and Catalog | p. 24 |
DB2 Governor | p. 26 |
DB2 Subsystem | p. 26 |
Distributed Data Facility | p. 26 |
Objects | p. 27 |
Table | p. 27 |
Tablespace | p. 27 |
Index | p. 29 |
Indexspace | p. 31 |
Database | p. 31 |
Storage Group | p. 32 |
View | p. 32 |
Bufferpool | p. 34 |
Structured Query Language | p. 35 |
DML, DDL, and DCL | p. 35 |
Interactive and Embedded | p. 36 |
Data Types | p. 37 |
Constants | p. 38 |
Nulls and Defaults | p. 39 |
Referential Integrity | p. 40 |
Defining and Loading a Sample Database | p. 45 |
Gaining Authorization to Use DB2 | p. 45 |
The Sample Database | p. 46 |
SPUFI | p. 50 |
Input and Output Datasets | p. 51 |
SPUFI Defaults | p. 53 |
Edit and Execute | p. 55 |
Browse | p. 57 |
Data Manipulation Language | |
The Select Statement | p. 63 |
Introduction | p. 63 |
The Select Statement Format | p. 63 |
The Results Table--A Subset of Your Data | p. 65 |
The Select Clause | p. 66 |
*, Columns, Literals | p. 66 |
Distinct | p. 68 |
The Where Clause | p. 68 |
Comparison Operators | p. 69 |
And, Or | p. 70 |
Between, In | p. 71 |
Like | p. 72 |
Negative Conditions | p. 73 |
Parentheses and Precedence | p. 74 |
Column Functions and Groups | p. 75 |
SUM, AVG, MIN, MAX, COUNT | p. 76 |
Group By | p. 79 |
Having | p. 80 |
Arithmetic Operations | p. 81 |
+, -, *, / | p. 82 |
In the Select Clause | p. 82 |
In the Where Clause | p. 83 |
Scalar Functions and Concatenation | p. 83 |
Substr, Length | p. 84 |
Conversion | p. 86 |
Concatenation | p. 88 |
Ordering the Result | p. 89 |
Order By Column Name | p. 89 |
Order By Column Number | p. 90 |
Null Considerations | p. 91 |
Comparisons, Arithmetic, Order By, Column Functions | p. 92 |
Value | p. 94 |
Subqueries, Unions, Joins | p. 95 |
Subquery | p. 95 |
After a Comparison Operator | p. 99 |
In | p. 102 |
Any, Some, All | p. 103 |
Correlated Subquery | p. 106 |
Correlated Subquery with Different Tables | p. 106 |
Correlation Names | p. 108 |
Correlated Subquery with the Same Table | p. 109 |
Exists | p. 111 |
Union | p. 114 |
Join | p. 116 |
Simple Joins | p. 116 |
Joining a Table to Itself | p. 120 |
Outer Join | p. 121 |
Update Operations and Date and Time Support | p. 123 |
Date and Time | p. 123 |
Special Registers | p. 123 |
Date and Time Functions | p. 125 |
Duration | p. 128 |
Arithmetic and Comparison | p. 129 |
Modifying the Database | p. 131 |
View Restrictions | p. 132 |
Insert | p. 132 |
Delete | p. 135 |
Update | p. 137 |
Referential Integrity with Insert, Delete, And Update | p. 139 |
Insert | p. 139 |
Delete | p. 140 |
Update | p. 141 |
Query Management Facility | |
QMF Overview | p. 145 |
First Steps | p. 146 |
Home Panel | p. 146 |
QMF Overview | p. 146 |
Temporary and Permanent Objects | p. 151 |
Query | p. 152 |
Data | p. 152 |
Form | p. 152 |
Proc | p. 153 |
Profile | p. 153 |
QMF Commands | p. 155 |
Save | p. 155 |
Display | p. 155 |
Run | p. 156 |
Import, Export, Print, Erase, Retrieve | p. 157 |
List | p. 159 |
Formatting Reports | p. 161 |
Headings, Edit Codes, Usage Codes, Control Breaks | p. 162 |
Form.Calc, Form.Detail | p. 171 |
Query by Example | p. 174 |
Prompted Query | p. 175 |
Using QMF to Support End-User Applications | p. 177 |
Query | p. 177 |
Proc | p. 179 |
The List Command | p. 182 |
Batch Execution of QMF | p. 182 |
A QMF Application | p. 183 |
Application Programming | |
Accessing DB2 from an Application Program--Introduction to Embedded SQL | p. 191 |
Table Declarations | p. 191 |
Declare Table Statement | p. 192 |
DCLGEN | p. 192 |
SQL Communications Area (SQLCA) | p. 196 |
Embedded SQL Statements | p. 197 |
Delimiters | p. 197 |
Select Into | p. 198 |
Host Variables | p. 199 |
Variable-Length Columns | p. 199 |
Data Type Compatibility | p. 200 |
To Supply a Value | p. 201 |
Host Structures | p. 202 |
Nulls | p. 203 |
Error Handling | p. 206 |
SQLCA Fields | p. 206 |
Whenever | p. 207 |
DSNTIAR | p. 208 |
Program1 | p. 210 |
Embedded SQL--Advanced Topics | p. 219 |
Data Integrity | p. 219 |
Logging | p. 219 |
Commit And Rollback | p. 220 |
What DB2 Does at Commit Time | p. 222 |
Locking | p. 222 |
Cursor Operations | p. 223 |
Declaring the Cursor | p. 224 |
Opening and Closing the Cursor | p. 224 |
Retrieving a Row--Fetch | p. 225 |
Update and Delete | p. 227 |
Reestablishing Cursor Position | p. 229 |
Dynamic SQL | p. 231 |
Dynamic SQL Compared to Static SQL | p. 231 |
Examples of Dynamic SQL in DB2 | p. 231 |
Testing Considerations | p. 232 |
Synonyms in Testing | p. 232 |
Requirements for Test Data | p. 233 |
DSNTIAUL | p. 234 |
Submitting SQL in Batch | p. 235 |
DSNTEP2 | p. 236 |
DSNTIAD | p. 236 |
Program2 | p. 237 |
Accessing DB2 from a CICS Program | p. 245 |
DB2-CICS Attachment Architecture | p. 245 |
CICS Resource Control Table | p. 245 |
Types of Threads | p. 246 |
Functions Provided by the DB2-CICS Attach | p. 246 |
The DSNC Transaction | p. 247 |
Program Flow Through the DB2-CICS Attach | p. 247 |
DB2-CICS Recovery and Restart--The Two-Phase Commit | p. 248 |
Programming Considerations | p. 249 |
SQL Design Guidelines | p. 249 |
Cursors Within Pseudo-Conversational Programs | p. 250 |
Security Considerations | p. 253 |
DB2-CICS Connection | p. 253 |
Attachment Facility and DB2 Commands | p. 253 |
Plan Execution and User Identification | p. 253 |
Test versus Production Environment | p. 254 |
Program3 | p. 254 |
Program Preparation and Execution | p. 261 |
DB21 Primary Option Menu | p. 264 |
DB21 Defaults | p. 265 |
Program Preparation Panels | p. 266 |
Precompile Panel | p. 268 |
Bind Panel | p. 270 |
Compile, Link, and Run | p. 273 |
Bind/Rebind/Free | p. 281 |
STDSQL(86) And NOFOR | p. 284 |
Program Preparation Outside of DB21 | p. 284 |
TSO Foreground Execution | p. 285 |
Execution in Batch | p. 285 |
CICS Considerations | p. 287 |
Design and Implementation of a Database | |
Logical Database Design | p. 291 |
Introduction | p. 291 |
A Variety of Approaches and Tools | p. 292 |
Identifying the Business Information Requirements | p. 293 |
Business Entities | p. 293 |
Relationships | p. 294 |
Conditional Relationships | p. 296 |
Identify the Data Requirements | p. 297 |
Data Entities and Relationships | p. 297 |
Define Primary Key Data Elements | p. 300 |
Define Foreign Key Data Elements | p. 300 |
Define NonKey Data Elements | p. 302 |
Normalization | p. 302 |
First Normal Form | p. 302 |
Second Normal Form | p. 303 |
Third Normal Form | p. 305 |
Denormalization | p. 307 |
Referential Integrity Decisions | p. 307 |
Data Definition and Data Control | p. 309 |
DDL--Creating DB2 Objects | p. 309 |
Storage Groups | p. 310 |
Database | p. 311 |
Tablespace and Table | p. 311 |
Index | p. 313 |
Primary and Foreign Key | p. 315 |
View and Synonym | p. 317 |
Altering DB2 Objects | p. 318 |
Altering Storage Group, Index, Tablespace | p. 318 |
Alter Table | p. 318 |
Dropping DB2 Objects | p. 319 |
Data Control Language | p. 319 |
Resources and Privileges | p. 320 |
Grouped Privileges | p. 320 |
Explicit and Implicit Authorization | p. 322 |
Data Control Language Statements | p. 322 |
Grant | p. 322 |
Revoke | p. 323 |
Primary and Secondary Auth IDs | p. 324 |
Set | p. 325 |
DB2 Catalog Tables for Security | p. 325 |
Miscellaneous Topics | |
DB2 Performance | p. 329 |
When a Request Is Made for Data | p. 329 |
Initial Procedures | p. 329 |
SQL Processing | p. 330 |
Commit and Thread Termination | p. 330 |
Data Base Services | p. 330 |
Relational Data System | p. 331 |
Data Manager | p. 331 |
Buffer Manager | p. 331 |
Indexes in DB2 | p. 332 |
Structure of Indexes | p. 332 |
Clustered Index | p. 333 |
Benefits and Costs of Index | p. 335 |
Access Paths | p. 337 |
Tablespace Scans | p. 337 |
Matchings and Nonmatching Index Scans | p. 337 |
Index Only | p. 338 |
Sequential Prefetch | p. 338 |
List Prefetch | p. 339 |
Optimization | p. 339 |
Effect of Predicates on Performance | p. 340 |
Sargable and Nonsargable Predicates | p. 340 |
Indexable Predicates | p. 341 |
Access Paths of Queries with Multiple Tables | p. 343 |
Nested-Loop Join | p. 343 |
Merge-Scan Join | p. 344 |
Sorts | p. 345 |
Locking | p. 345 |
Lock Size | p. 346 |
Lock Duration | p. 346 |
Lock Type | p. 347 |
Performance Considerations and EXPLAIN | p. 349 |
The Explain Function | p. 349 |
Creating the Plan Table | p. 349 |
Querying the Plan Table | p. 350 |
Invoking Explain | p. 352 |
Information Provided by Explain | p. 353 |
Examples | p. 358 |
Query 10 | p. 358 |
Query 20 | p. 359 |
Query 30 | p. 360 |
DB2 Commands and Utilities | p. 363 |
DB2 Commands | p. 363 |
DB2 Command Execution | p. 365 |
Utilities | p. 367 |
Utility Execution | p. 369 |
Utility Monitoring and Control | p. 370 |
The Load Utility | p. 372 |
The Runstats Utility | p. 376 |
The Copy Utility | p. 377 |
Distributed Database Facility | p. 379 |
Concepts of Distributed Databases | p. 380 |
What is a Distributed DBMS? | p. 380 |
Local and Remote | p. 381 |
Commit Scope and Logical Unit of Work | p. 382 |
Location Transparency | p. 382 |
Local Autonomy | p. 383 |
Data Distribution in DB2 2.2 | p. 384 |
Communicating with Other Subsystems | p. 384 |
Three-Part Names for Tables and Views | p. 385 |
Using Aliases | p. 385 |
Date, Time, and Other Special Registers | p. 386 |
Referential Integrity Limitations | p. 387 |
Accessing Remote Data | p. 387 |
Remote Table Name Translation | p. 388 |
Query and Block Fetch | p. 389 |
Update | p. 390 |
Authorization in Distributed Environment | p. 391 |
Program Preparation | p. 391 |
Limitations | p. 392 |
Glossary | p. 393 |
DB2's System/Catalog Tables | p. 403 |
Syntax | p. 407 |
Predicate Types and Processing | p. 417 |
Acronyms | p. 419 |
Index | p. 421 |
Table of Contents provided by Syndetics. All Rights Reserved. |