Table of
Contents
Concepts and Components
CO.1 INTRODUCTION.......................................... 42
CO.2 DB2 USAGE ARCHITECTURE................................ 43
SQL Data Definition...................................... 44
SQL Data Manipulation.................................... 45
Binding SQL DML into a Plan or Package................... 45
The Optimizer............................................ 46
SQL Data Control......................................... 47
SQLCODE and SQLSTATE..................................... 48
SQL99 Standard........................................... 49
Utilities................................................ 50
The Catalog Tables....................................... 50
Control Center .......................................... 51
The Directory............................................ 52
How Many Bytes?.......................................... 53
CO.3 DB2 INTERACTIVE....................................... 54
SQL Processing Using File Input (SPUFI).................. 54
Schema Definition in Batch............................... 58
CO.4 DB2 SUBSYSTEM
ARCHITECTURE............................ 60
Attaches and Threads..................................... 61
System Services Address Space............................ 62
Logging and Recovery Management.......................... 63
Tracking Recovery Information............................ 66
Fast Log Apply........................................... 67
Monitor Statistics for Resizing Log...................... 67
Instrumentation Facility Interface....................... 68
Database Services Address Space.......................... 71
CO.5 BUFFER POOLS.......................................... 72
When Data Sets are Opened................................ 74
Read Engines............................................. 75
Write Engines............................................ 76
CO.6 SORT POOLS............................................ 76
CO.7 RID POOL.............................................. 80
CO.8 EDM POOL.............................................. 82
Authorization and the EDM Pool........................... 87
DBDs in the EDM Pool..................................... 88
EDM Pool Size............................................ 89
Locating Plans and Packages.............................. 90
Space for Plans and Packages............................. 90
Compiled Assignment Procedures........................... 91
CO.9 WORKING STORAGE....................................... 92
CO.10 THREAD CREATION, USE,
AND TERMINATION................ 92
CO.11 IRLM ADDRESS SPACE................................... 94
CO.12 DISTRIBUTED DATA
FACILITY............................ 94
MultiBSite Update........................................ 95
CO.13 STORED PROCEDURE
ADDRESS SPACES...................... 97
CO.14 Changing DSNZPARM
Parameters......................... 97
CO.15 SUMMARY.............................................. 98
EXERCISES.................................................. 98
ANSWERS.................................................... 99
FIGURES................................................... 101
Creating STOGROUPs, Databases, and
Buffer Pools
CD.1 INTRODUCTION......................................... 103
CD.2 SPACE FOR TABLESPACES
AND INDEX SPACES............... 104
Create a Storage Group.................................. 104
Alter a Storage Group................................... 105
User Defined Data Sets.................................. 105
Comparison of Storage Groups and User Defined Data Sets. 107
Data Set Placement on DASD.............................. 108
CD.3 CREATING A DATABASE.................................. 109
Factors that Affect Placement of Objects in a Database.. 110
Database Size .......................................... 111
EBCDIC, ASCII, and UNICODE Encoding Schemes............. 112
CD.4 BUFFER POOLS......................................... 114
Advantages of Virtual Pools............................. 114
Disadvantages of Small Virtual Pools.................... 114
Page Size Depends on Buffer Pool Assignment............. 115
Buffer Management....................................... 117
Reuse of Buffer Pages................................... 119
Status of Buffer Pool Pages............................. 120
CD.5 BUFFER POOL HIT RATIOS............................... 120
CD.6 Data Spaces and
Hiperpools........................... 122
Managing within Limits.................................. 122
Virtual Pool, Data Spaces, and Hiperpools Comparison.... 122
Data Space Specifics.................................... 124
Hiperpool Specifics..................................... 125
OS/390 and z/OS Paging.................................. 126
Relative Cost and Speed of Storage Types................ 126
CD.7 CUSTOMIZING VIRTUAL
POOLS AND HIPERPOOLS............. 128
Size and Thresholds of Pools............................ 129
Creating, Deleting, and Changing Pools.................. 134
CD.8 DETERMINING USAGE OF
POOLS........................... 135
CD.9 GUIDELINES ON THE USE
OF MULTIPLE VIRTUAL POOLS...... 140
CD.10 ADJUSTING I/O PRIORITY.............................. 142
Test Results of Adjusting I/O Priority.................. 144
CD.11 SUMMARY............................................. 148
EXERCISES................................................. 148
ANSWERS................................................... 150
FIGURES................................................... 153
Creating Tablespaces
TS.1 INTRODUCTION......................................... 155
Three Types of Tablespaces.............................. 155
TS.2 SEGMENTED AND SIMPLE
TABLESPACES..................... 156
Advantages of Segmented Tablespaces..................... 157
Advantages of Multiple Tables in a Tablespace........... 157
Multiple Tables in a Simple Versus Segmented Tablespace. 158
Disadvantages of Multiple Tables in a Tablespace........ 159
Simple Tablespaces...................................... 160
TS.3 ANALYSIS OF TABLESPACE
PARAMETERS.................... 161
Primary and Secondary Space Allocation.................. 163
ERASE Parameter......................................... 165
TS.4 FREE SPACE FOR UPDATING
AND INSERTING ROWS........... 166
Search for Free Space................................... 166
Use of Free Space to Maintain Variable Length Rows...... 168
Estimating Free Space for Variable Length Rows.......... 169
Free Space to Maintain Clustering....................... 170
Estimate Free Space Needed between Reorganizations...... 171
Avoid Too Much Free Space............................... 172
TS.5 PARAMETERS THAT CONTROL
LOCKING...................... 173
Locksize for a Tablespace............................... 173
Maximum Number of Locks................................. 174
Control of Lock Limits and Escalation................... 174
Lock Limit per Thread................................... 175
Maximum Number of Rows on a Page........................ 175
TS.6 LOGICAL AND PHYSICAL
CLOSE OF DATA SETS.............. 176
Switch to Read-Only Status.............................. 177
Logical Close of Data Sets.............................. 177
Physical Close of Data Sets............................. 178
Limit the Number of Open Data Sets...................... 178
Definition of VSAM Data Sets............................ 179
TS.7 PARTITIONED TABLESPACES
AND INDEXES.................. 179
Create a Partitioned Tablespace......................... 180
The Partitioning Index.................................. 184
Advantages of Partitioning.............................. 187
Partitioning is Not a Panacea........................... 187
Estimating the Number of Partitions..................... 188
Limit Key for Each Partition............................ 189
Partitioning Index Alternatives......................... 191
Altering the Limit Key.................................. 195
Current and Historical Data............................. 197
TS.8 DATA COMPRESSION..................................... 198
Savings and Costs of Compression........................ 198
How to Use Compression.................................. 200
Impact of Compression on the Optimizer and by Program Type 200
Capacity Planning Estimates............................. 201
Very Short and Very Long Rows........................... 202
Updating and Logging Variable and Fixed Length Rows..... 202
Estimating DASD Savings................................. 203
Example of DSN1COMP Report.............................. 204
RUNSTATS Statistics..................................... 205
Compression/Decompression Dictionary.................... 206
Use of the Dictionary During Execution of SQL........... 209
Ziv-Lempel-Welch Compression Algorithm.................. 209
CPU and I/O Time Test Results........................... 214
TS.9 STRUCTURE OF
TABLESPACES............................. 218
Common Header and Trail Data............................ 218
Space Map Pages......................................... 219
Bit Map Describing Updated Pages........................ 221
Page Layout............................................. 222
TS.10 SUMMARY............................................. 224
EXERCISES................................................. 225
ANSWERS................................................... 227
FIGURES................................................... 231
Creating
Tables and Views
CT.1 INTRODUCTION......................................... 232
CT.2 CREATING TABLES...................................... 232
Data Types.............................................. 233
Numeric Data Types...................................... 235
Character Data Types.................................... 235
VARCHAR with Care....................................... 236
Numerals as Numeric Types............................... 240
Date and Time........................................... 241
Large Object Data Types and Requirements................ 242
ROWID Data Type......................................... 243
Identity Column......................................... 246
Domains................................................. 249
Creating and Using Distinct Types....................... 249
Use of Distinct Types to Avoid Invalid Operations....... 251
Cast To and From Distinct Types......................... 252
Table Check Constraints................................. 256
Definition of Constraints............................... 257
Rules for Defining Constraints.......................... 257
Referencing Another Table............................... 258
Adding Constraints to a Populated Table................. 258
ALTER TABLE Statement................................... 259
To Null or Not Null..................................... 260
Not Null................................................ 263
Not Null with Default................................... 263
Assignment of a Default Value........................... 264
Coded Character Set Identifier.......................... 265
Restrict on Drop........................................ 265
Data Capture............................................ 265
Auditing a Table........................................ 266
EDITPROC, FIELDPROC, and VALIDPROC...................... 267
IN Clause .............................................. 269
Row Length Considerations............................... 269
Creating LIKE Tables.................................... 270
COMMENT ON Statements................................... 270
LABEL ON Statements..................................... 271
CT.3 REFERENTIAL INTEGRITY................................ 272
Creating the Primary Key................................ 274
Creating Foreign Keys................................... 275
Dropping Constraints and Indexes........................ 277
RI Rules................................................ 277
Explicit and Implicit RI Rules.......................... 278
No Constraint Checking Required......................... 279
Foreign Keys and Nulls.................................. 280
CT.4 RELATIONSHIPS THAT
REQUIRE SPECIAL HANDLING.......... 282
Self-Referencing Constraint............................. 282
Cycles.................................................. 284
Delete-Connected Tables................................. 287
Invalid Delete with Subselect........................... 289
CT.5 DB2 VERSUS APPLICATION
ENFORCED INTEGRITY............ 291
SQL Insert that Can Violate RI ......................... 291
SQL Update that Can Violate RI.......................... 292
Performance of DB2 Enforced and Application Enforced Constraints 292
Application Enforced Integrity.......................... 294
Checking for Constraint Violations...................... 295
CT.6 VIEWS................................................ 296
Ease of Use and Performance............................. 298
Resolution of Views..................................... 298
Cost of Views........................................... 299
View Restrictions ...................................... 300
WITH CHECK OPTION Parameter............................. 302
Privileges to Create a View............................. 303
Levels of Views......................................... 303
Table Restructuring..................................... 304
Views Not Theoretically Undatable....................... 305
CT.7 SYNONYMS, ALIASES, AND
QUALIFIERS.................... 307
Use of Synonyms......................................... 307
Use of an Alias......................................... 308
Use of a Qualifier...................................... 308
CT.8 DROPPING OBJECTS..................................... 309
Dependent Objects....................................... 310
CT.9 SUMMARY.............................................. 311
EXERCISES................................................. 312
ANSWERS................................................... 315
FIGURES................................................... 321
Index
Usage for Performance
IU.1 INTRODUCTION......................................... 323
Index Structure......................................... 323
Clustering Indexes...................................... 325
IU.2 BASIC INDEX USAGE.................................... 327
Matching Index Scan..................................... 327
Non-matching Index Scan................................. 328
IU.3 INDEX LOOKASIDE AND
ONE-FETCH ACCESS................. 328
Index Lookaside......................................... 328
One-Fetch Index Access.................................. 329
IU.4 LIST PREFETCH AND
MULTIPLE INDEX USAGE............... 330
List Prefetch........................................... 331
Multiple Index Processing............................... 331
Logical Partitioning Index Usage........................ 333
IU.5 COMPARING METHODS FOR
LOCATING DATA.................. 335
IU.6 USING COMPOSITE INDEXES.............................. 337
Index Screening......................................... 339
Index and Column Cardinalities.......................... 341
Using Index Cardinalities with Composite Indexes........ 342
Use of an Index with Update............................. 343
Index-Only Processing................................... 345
IU.7 DIRECT ROW ACCESS
WITHOUT INDEX USAGE OVERVIEW....... 347
IU.8 SUMMARY.............................................. 347
EXERCISES................................................. 348
ANSWERS................................................... 351
FIGURES................................................... 356
Index
Design for Performance
ID.1 INTRODUCTION......................................... 357
ID.2 INDEX DESIGN GUIDELINES.............................. 357
Columns that Should Be Indexed.......................... 358
Columns that Benefit from Clustering.................... 359
Analyze How Data is Used................................ 362
ID.3 DESIGNING COMPOSITE
INDEXES.......................... 364
Sequence of Columns in Composite Index Design........... 366
Analyzing Existing Indexes.............................. 367
ID.4 DATA-ONLY LOCKING.................................... 370
Locks Taken with RR..................................... 370
Latch Processing........................................ 371
ID.5 INDEX STRUCTURE
MODIFICATION......................... 371
Access During Structure Modification.................... 372
Insertion of Sequential Values.......................... 373
ID.6 COLUMNS THAT SHOULD NOT
BE INDEXED................... 374
Low Cardinality Indexes................................. 374
Skewed Distribution of Values........................... 376
Many Unknown Values..................................... 380
Management of a Synonym Chain........................... 380
RIDs Maintained in Sequence............................. 381
Additional Columns That Should Not Be Indexed........... 381
Logically Sequenced Indexed Values...................... 383
Reuse of Space From Deleted Values...................... 384
Suffix Truncation in Nonleaf Pages...................... 385
ID.7 CREATING AND ALTERING
INDEXES........................ 386
When to Create and Build Indexes........................ 388
Defer Building of Indexes............................... 389
Altering Indexes........................................ 390
ID.8 SUMMARY.............................................. 392
EXERCISES................................................. 393
ANSWERS................................................... 394
FIGURES................................................... 397
The Basics
of SQL Data Manipulation
SL.1 INTRODUCTION......................................... 399
SL.2 SELECT STATEMENTS.................................... 399
SELECT *................................................ 400
Naming a Result with AS................................. 401
Range SELECTS........................................... 403
String Searches......................................... 404
Negating a Predicate.................................... 405
SL.3 COLUMN FUNCTIONS AND
ARITHMETIC CALCULATIONS......... 406
MIN, MAX, and AVG....................................... 406
Math in the SELECT Clause............................... 407
COUNT Column Function................................... 407
DISTINCT Function....................................... 408
SL.4 DATA-TYPE CONVERSIONS................................ 409
SL.5 STRING MANIPULATION
FUNCTIONS........................ 411
Substring Function...................................... 411
Concatenation Function.................................. 411
STRIP Function.......................................... 412
COALESCE and VALUE Functions............................ 413
NULLIF Function......................................... 413
SL.6 DATE AND TIME FUNCTIONS
AND CALCULATIONS............. 414
YEAR and MONTH Calculations............................. 415
Last Day of Month Calculations.......................... 416
TIME Calculations....................................... 416
Subtracting DATE and TIME Values........................ 417
The DAYS Function....................................... 418
Converting to the DATE Data Type........................ 418
Use of Scalar Functions without Selecting from a One Row Table 420
SL.7 SELECTING BY GROUPS.................................. 421
SL.8 SEQUENCING THE
QUALIFYING ROWS....................... 422
SL.9 CASE EXPRESSIONS..................................... 423
SL.10 SUBSELECTS.......................................... 424
Subselect Returning Multiple Values..................... 425
Correlated Subselects................................... 428
SL.11 INTERSECTION AND
DIFFERENCE......................... 429
SL.12 NESTED TABLE
EXPRESSIONS............................ 431
SL.13 JOINS............................................... 432
Three-way Joins......................................... 434
Joining a Table with Itself............................. 435
SL.14 UNION............................................... 437
SL.15 INSERTING, UPDATING,
AND DELETING DATA.............. 438
Inserts................................................. 438
Updates................................................. 440
Update and Delete Based on Results of Subselect......... 441
Deletes................................................. 442
SL.16 SUMMARY............................................. 443
EXERCISES................................................. 443
ANSWERS................................................... 446
FIGURES................................................... 452
Concurrency Control
CC.1 INTRODUCTION......................................... 453
CC.2 DEADLOCKS AND TIMEOUTS............................... 456
Timeouts................................................ 458
Deadlocks and Timeouts in a Distributed Environment..... 459
CC.3 LOCK SIZES AND TYPES................................. 460
Determining the Lock Size............................... 460
Advantages and Costs of Row Locking..................... 460
Share, Update, and Exclusive Locks...................... 463
CC.4 LOCK