Cover image for DB2--concepts, programming, and design
Title:
DB2--concepts, programming, and design
Author:
Ranade, Jay.
Publication Information:
New York : McGraw-Hill, [1991]

©1991
Physical Description:
xviii, 430 pages : illustrations ; 24 cm.
General Note:
"Covers version 2.2"--Cover.

Includes index.
Language:
English
Title Subject:
Subject Term:
Added Author:
ISBN:
9780070512658
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Central Library QA76.9.D3 D38547 1991 Adult Non-Fiction Central Closed Stacks
Searching...

On Order

Summary

Summary

DB2, announced in 1983, has quickly replaced IMS as IBM's premier database product. This book covers key DB2 concepts from the perspective of DB2 Version 2, the most recent release of DB2, and provides a sample database that can be created on DB2 systems.


Author Notes

Jay Ranade, (top left) the author of best-selling computer books, is an Assistant Vice-President/Senior Systems Architect with Merrill Lynch and Series Editor-in-Chief for McGraw-Hill. Mukesh Sehgal (top right) is President of Relational Systems Group, a New York City-based consulting company. Phyllis Elkind (bottom left) is a Senior Consultant with Modern Data Management, a consulting group specializing in database management systems. Joseph Grossman (bottom right) is the Vice President of Infomonics, Inc., and has worked as a DBA in many Fortune 500 companies.


Table of Contents

Prefacep. xv
Part 1. Introduction to DB2
Chapter 1. Database Conceptsp. 3
1.1 What is a Database?p. 3
1.1.1 Roles in the Database Environmentp. 6
1.2 Database System Componentsp. 7
1.2.1 Hardwarep. 7
1.2.2 Datap. 7
1.2.3 Usersp. 8
1.2.4 Database Management Softwarep. 8
1.3 Categories of DBMSsp. 9
1.3.1 Data Modelsp. 9
1.3.2 Four Types of DBMSsp. 10
1.4 Relational Modelp. 12
1.4.1 Data Structurep. 12
1.4.2 Data Manipulationp. 14
1.4.3 Data Integrityp. 14
1.5 Implementation of the Relational Model in DB2p. 14
1.5.1 Data Structurep. 15
1.5.2 Data Manipulationp. 15
1.5.3 Data Integrityp. 15
Chapter 2. DB2 Overviewp. 17
2.1 Origin and Historyp. 17
2.1.1 DB2's Predecessorsp. 18
2.1.2 Benefits of DB2p. 18
2.2 Environmentp. 19
2.2.1 DB2 Attachments, DSN, and Threadsp. 20
2.2.2 Interactive Access to Datap. 21
2.3 Components and Featuresp. 21
2.3.1 IRLM, System Services, Data Base Servicesp. 21
2.3.2 DBRMs, BIND, PLANs, and the Optimizerp. 23
2.3.3 DB2 Directory and Catalogp. 24
2.3.4 DB2 Governorp. 26
2.3.5 DB2 Subsystemp. 26
2.3.6 Distributed Data Facilityp. 26
2.4 Objectsp. 27
2.4.1 Tablep. 27
2.4.2 Tablespacep. 27
2.4.3 Indexp. 29
2.4.4 Indexspacep. 31
2.4.5 Databasep. 31
2.4.6 Storage Groupp. 32
2.4.7 Viewp. 32
2.4.8 Bufferpoolp. 34
2.5 Structured Query Languagep. 35
2.5.1 DML, DDL, and DCLp. 35
2.5.2 Interactive and Embeddedp. 36
2.5.3 Data Typesp. 37
2.5.4 Constantsp. 38
2.5.5 Nulls and Defaultsp. 39
2.6 Referential Integrityp. 40
Chapter 3. Defining and Loading a Sample Databasep. 45
3.1 Gaining Authorization to Use DB2p. 45
3.2 The Sample Databasep. 46
3.3 SPUFIp. 50
3.3.1 Input and Output Datasetsp. 51
3.3.2 SPUFI Defaultsp. 53
3.3.3 Edit and Executep. 55
3.3.4 Browsep. 57
Part 2. Data Manipulation Language
Chapter 4. The Select Statementp. 63
4.1 Introductionp. 63
4.1.1 The Select Statement Formatp. 63
4.1.2 The Results Table--A Subset of Your Datap. 65
4.2 The Select Clausep. 66
4.2.1 *, Columns, Literalsp. 66
4.2.2 Distinctp. 68
4.3 The Where Clausep. 68
4.3.1 Comparison Operatorsp. 69
4.3.2 And, Orp. 70
4.3.3 Between, Inp. 71
4.3.4 Likep. 72
4.3.5 Negative Conditionsp. 73
4.3.6 Parentheses and Precedencep. 74
4.4 Column Functions and Groupsp. 75
4.4.1 SUM, AVG, MIN, MAX, COUNTp. 76
4.4.2 Group Byp. 79
4.4.3 Havingp. 80
4.5 Arithmetic Operationsp. 81
4.5.1 +, -, *, /p. 82
4.5.2 In the Select Clausep. 82
4.5.3 In the Where Clausep. 83
4.6 Scalar Functions and Concatenationp. 83
4.6.1 Substr, Lengthp. 84
4.6.2 Conversionp. 86
4.6.3 Concatenationp. 88
4.7 Ordering the Resultp. 89
4.7.1 Order By Column Namep. 89
4.7.2 Order By Column Numberp. 90
4.8 Null Considerationsp. 91
4.8.1 Comparisons, Arithmetic, Order By, Column Functionsp. 92
4.8.2 Valuep. 94
Chapter 5. Subqueries, Unions, Joinsp. 95
5.1 Subqueryp. 95
5.1.1 After a Comparison Operatorp. 99
5.1.2 Inp. 102
5.1.3 Any, Some, Allp. 103
5.2 Correlated Subqueryp. 106
5.2.1 Correlated Subquery with Different Tablesp. 106
5.2.2 Correlation Namesp. 108
5.2.3 Correlated Subquery with the Same Tablep. 109
5.2.4 Existsp. 111
5.3 Unionp. 114
5.4 Joinp. 116
5.4.1 Simple Joinsp. 116
5.4.2 Joining a Table to Itselfp. 120
5.4.3 Outer Joinp. 121
Chapter 6. Update Operations and Date and Time Supportp. 123
6.1 Date and Timep. 123
6.1.1 Special Registersp. 123
6.1.2 Date and Time Functionsp. 125
6.1.3 Durationp. 128
6.1.4 Arithmetic and Comparisonp. 129
6.2 Modifying the Databasep. 131
6.2.1 View Restrictionsp. 132
6.2.2 Insertp. 132
6.2.3 Deletep. 135
6.2.4 Updatep. 137
6.3 Referential Integrity with Insert, Delete, And Updatep. 139
6.3.1 Insertp. 139
6.3.2 Deletep. 140
6.3.3 Updatep. 141
Part 3. Query Management Facility
Chapter 7. QMF Overviewp. 145
7.1 First Stepsp. 146
7.1.1 Home Panelp. 146
7.1.2 QMF Overviewp. 146
7.2 Temporary and Permanent Objectsp. 151
7.2.1 Queryp. 152
7.2.2 Datap. 152
7.2.3 Formp. 152
7.2.4 Procp. 153
7.2.5 Profilep. 153
7.3 QMF Commandsp. 155
7.3.1 Savep. 155
7.3.2 Displayp. 155
7.3.3 Runp. 156
7.3.4 Import, Export, Print, Erase, Retrievep. 157
7.3.5 Listp. 159
7.4 Formatting Reportsp. 161
7.4.1 Headings, Edit Codes, Usage Codes, Control Breaksp. 162
7.4.2 Form.Calc, Form.Detailp. 171
7.5 Query by Examplep. 174
7.6 Prompted Queryp. 175
Chapter 8. Using QMF to Support End-User Applicationsp. 177
8.1 Queryp. 177
8.2 Procp. 179
8.3 The List Commandp. 182
8.4 Batch Execution of QMFp. 182
8.5 A QMF Applicationp. 183
Part 4. Application Programming
Chapter 9. Accessing DB2 from an Application Program--Introduction to Embedded SQLp. 191
9.1 Table Declarationsp. 191
9.1.1 Declare Table Statementp. 192
9.1.2 DCLGENp. 192
9.2 SQL Communications Area (SQLCA)p. 196
9.3 Embedded SQL Statementsp. 197
9.3.1 Delimitersp. 197
9.3.2 Select Intop. 198
9.4 Host Variablesp. 199
9.4.1 Variable-Length Columnsp. 199
9.4.2 Data Type Compatibilityp. 200
9.4.3 To Supply a Valuep. 201
9.4.4 Host Structuresp. 202
9.5 Nullsp. 203
9.6 Error Handlingp. 206
9.6.1 SQLCA Fieldsp. 206
9.6.2 Wheneverp. 207
9.6.3 DSNTIARp. 208
9.7 Program1p. 210
Chapter 10. Embedded SQL--Advanced Topicsp. 219
10.1 Data Integrityp. 219
10.1.1 Loggingp. 219
10.1.2 Commit And Rollbackp. 220
10.1.3 What DB2 Does at Commit Timep. 222
10.1.4 Lockingp. 222
10.2 Cursor Operationsp. 223
10.2.1 Declaring the Cursorp. 224
10.2.2 Opening and Closing the Cursorp. 224
10.2.3 Retrieving a Row--Fetchp. 225
10.2.4 Update and Deletep. 227
10.2.5 Reestablishing Cursor Positionp. 229
10.3 Dynamic SQLp. 231
10.3.1 Dynamic SQL Compared to Static SQLp. 231
10.3.2 Examples of Dynamic SQL in DB2p. 231
10.4 Testing Considerationsp. 232
10.4.1 Synonyms in Testingp. 232
10.4.2 Requirements for Test Datap. 233
10.4.3 DSNTIAULp. 234
10.5 Submitting SQL in Batchp. 235
10.5.1 DSNTEP2p. 236
10.5.2 DSNTIADp. 236
10.6 Program2p. 237
Chapter 11. Accessing DB2 from a CICS Programp. 245
11.1 DB2-CICS Attachment Architecturep. 245
11.1.1 CICS Resource Control Tablep. 245
11.1.2 Types of Threadsp. 246
11.1.3 Functions Provided by the DB2-CICS Attachp. 246
11.1.4 The DSNC Transactionp. 247
11.1.5 Program Flow Through the DB2-CICS Attachp. 247
11.1.6 DB2-CICS Recovery and Restart--The Two-Phase Commitp. 248
11.2 Programming Considerationsp. 249
11.2.1 SQL Design Guidelinesp. 249
11.2.2 Cursors Within Pseudo-Conversational Programsp. 250
11.3 Security Considerationsp. 253
11.3.1 DB2-CICS Connectionp. 253
11.3.2 Attachment Facility and DB2 Commandsp. 253
11.3.3 Plan Execution and User Identificationp. 253
11.3.4 Test versus Production Environmentp. 254
11.4 Program3p. 254
Chapter 12. Program Preparation and Executionp. 261
12.1 DB21 Primary Option Menup. 264
12.2 DB21 Defaultsp. 265
12.3 Program Preparation Panelsp. 266
12.3.1 Precompile Panelp. 268
12.3.2 Bind Panelp. 270
12.3.3 Compile, Link, and Runp. 273
12.4 Bind/Rebind/Freep. 281
12.5 STDSQL(86) And NOFORp. 284
12.6 Program Preparation Outside of DB21p. 284
12.6.1 TSO Foreground Executionp. 285
12.6.2 Execution in Batchp. 285
12.7 CICS Considerationsp. 287
Part 5. Design and Implementation of a Database
Chapter 13. Logical Database Designp. 291
13.1 Introductionp. 291
13.1.1 A Variety of Approaches and Toolsp. 292
13.2 Identifying the Business Information Requirementsp. 293
13.2.1 Business Entitiesp. 293
13.2.2 Relationshipsp. 294
13.2.3 Conditional Relationshipsp. 296
13.3 Identify the Data Requirementsp. 297
13.3.1 Data Entities and Relationshipsp. 297
13.3.2 Define Primary Key Data Elementsp. 300
13.3.3 Define Foreign Key Data Elementsp. 300
13.3.4 Define NonKey Data Elementsp. 302
13.4 Normalizationp. 302
13.4.1 First Normal Formp. 302
13.4.2 Second Normal Formp. 303
13.4.3 Third Normal Formp. 305
13.4.4 Denormalizationp. 307
13.5 Referential Integrity Decisionsp. 307
Chapter 14. Data Definition and Data Controlp. 309
14.1 DDL--Creating DB2 Objectsp. 309
14.1.1 Storage Groupsp. 310
14.1.2 Databasep. 311
14.1.3 Tablespace and Tablep. 311
14.1.4 Indexp. 313
14.1.5 Primary and Foreign Keyp. 315
14.1.6 View and Synonymp. 317
14.2 Altering DB2 Objectsp. 318
14.2.1 Altering Storage Group, Index, Tablespacep. 318
14.2.2 Alter Tablep. 318
14.3 Dropping DB2 Objectsp. 319
14.4 Data Control Languagep. 319
14.4.1 Resources and Privilegesp. 320
14.4.2 Grouped Privilegesp. 320
14.4.3 Explicit and Implicit Authorizationp. 322
14.5 Data Control Language Statementsp. 322
14.5.1 Grantp. 322
14.5.2 Revokep. 323
14.6 Primary and Secondary Auth IDsp. 324
14.6.1 Setp. 325
14.7 DB2 Catalog Tables for Securityp. 325
Part 6. Miscellaneous Topics
Chapter 15. DB2 Performancep. 329
15.1 When a Request Is Made for Datap. 329
15.1.1 Initial Proceduresp. 329
15.1.2 SQL Processingp. 330
15.1.3 Commit and Thread Terminationp. 330
15.2 Data Base Servicesp. 330
15.2.1 Relational Data Systemp. 331
15.2.2 Data Managerp. 331
15.2.3 Buffer Managerp. 331
15.3 Indexes in DB2p. 332
15.3.1 Structure of Indexesp. 332
15.3.2 Clustered Indexp. 333
15.3.3 Benefits and Costs of Indexp. 335
15.4 Access Pathsp. 337
15.4.1 Tablespace Scansp. 337
15.4.2 Matchings and Nonmatching Index Scansp. 337
15.4.3 Index Onlyp. 338
15.4.4 Sequential Prefetchp. 338
15.4.5 List Prefetchp. 339
15.5 Optimizationp. 339
15.6 Effect of Predicates on Performancep. 340
15.6.1 Sargable and Nonsargable Predicatesp. 340
15.6.2 Indexable Predicatesp. 341
15.7 Access Paths of Queries with Multiple Tablesp. 343
15.7.1 Nested-Loop Joinp. 343
15.7.2 Merge-Scan Joinp. 344
15.8 Sortsp. 345
15.9 Lockingp. 345
15.9.1 Lock Sizep. 346
15.9.2 Lock Durationp. 346
15.9.3 Lock Typep. 347
Chapter 16. Performance Considerations and EXPLAINp. 349
16.1 The Explain Functionp. 349
16.1.1 Creating the Plan Tablep. 349
16.1.2 Querying the Plan Tablep. 350
16.2 Invoking Explainp. 352
16.3 Information Provided by Explainp. 353
16.4 Examplesp. 358
16.4.1 Query 10p. 358
16.4.2 Query 20p. 359
16.4.3 Query 30p. 360
Chapter 17. DB2 Commands and Utilitiesp. 363
17.1 DB2 Commandsp. 363
17.1.1 DB2 Command Executionp. 365
17.2 Utilitiesp. 367
17.2.1 Utility Executionp. 369
17.2.2 Utility Monitoring and Controlp. 370
17.3 The Load Utilityp. 372
17.4 The Runstats Utilityp. 376
17.5 The Copy Utilityp. 377
Chapter 18. Distributed Database Facilityp. 379
18.1 Concepts of Distributed Databasesp. 380
18.1.1 What is a Distributed DBMS?p. 380
18.1.2 Local and Remotep. 381
18.1.3 Commit Scope and Logical Unit of Workp. 382
18.1.4 Location Transparencyp. 382
18.1.5 Local Autonomyp. 383
18.2 Data Distribution in DB2 2.2p. 384
18.2.1 Communicating with Other Subsystemsp. 384
18.2.2 Three-Part Names for Tables and Viewsp. 385
18.2.3 Using Aliasesp. 385
18.2.4 Date, Time, and Other Special Registersp. 386
18.2.5 Referential Integrity Limitationsp. 387
18.3 Accessing Remote Datap. 387
18.3.1 Remote Table Name Translationp. 388
18.3.2 Query and Block Fetchp. 389
18.3.3 Updatep. 390
18.3.4 Authorization in Distributed Environmentp. 391
18.3.5 Program Preparationp. 391
18.3.6 Limitationsp. 392
Appendix A. Glossaryp. 393
Appendix B. DB2's System/Catalog Tablesp. 403
Appendix C. Syntaxp. 407
Appendix D. Predicate Types and Processingp. 417
Appendix E. Acronymsp. 419
Indexp. 421

Google Preview