DB2 universal database v8 handbook for Windows, Unix, and Linux

목차 (Table of Contents)

• CONTENTS
• Foreword = xxi
• Preface = xxiii
• CHAPTER 1 What's New = 1
• Product changes = 2
• Manageability Enhancements = 3
• Load Enhancements = 3
• Wizards = 3
• Flush Package Cache = 3
• Logging Enhancements = 4
• Backup and Recovery Enhancements = 4
• New Database Maintenance Mode = 4
• REORGCHK Enhancements = 4
• RUNSTATS Enhancements = 5
• Management by Exception Monitoring = 5
• Event Monitor and Snapshot Enhancements = 5
• Performance Enhancements = 5
• Multidimensional Clustering(MDC) = 5
• Prefetching Enhancements = 6
• Catalog and Authorization Caching = 6
• Asynchronous I／O Enhancements = 6
• Java SP and UDF Performance Enhancements = 6
• Connection Concentrator = 6
• Common Client Enhancement = 7
• Full 64-bit Support = 7
• Declared Global Temporary Table(DGTT) Enhancements = 7
• Materialized Query Table(MQT) = 7
• User-Maintained MQTs = 8
• Nickname Support = 8
• Type-2 Indexes = 8
• Availability Enhancements = 8
• Online Table Reorganization = 8
• Online Index Reorganization = 9
• Dynamic Online Configuration Parameters = 9
• Online Bufferpool Enhancements = 9
• DMS Container Enhancements = 9
• Seviceability Enhancements = 10
• Data Warehouse Enhancements = 10
• Application Development Enhancements = 10
• SQL in External UDFs and Methods = 10
• CALL Statement = 10
• Development Center = 11
• SQL Enhancements = 11
• Summary = 11
• CHAPTER 2 DB2 v8 Product Overview and Architecture = 13
• Enterprise Server Edition = 13
• Database Partitioning Feature(DPF) = 14
• DB2 Workgroup Server Edition(WSE) = 15
• Workgroup Server Unlimited Edition(WSUE) = 15
• DB2 Personal Edition(PE) = 16
• DB2 Universal Developers Edition(UDE) = 16
• DB2 Personal Developers Edition(PDE) = 16
• DB2 Warehouse Manager = 16
• DB2 Intelligent Miner Products = 16
• DB2 Spatial Extender = 16
• DB2 Net Search Extender = 16
• New Client = 17
• DB2 Architecture = 18
• Coordinating Agent(db2agent) = 20
• Subagents(db2agntp) = 20
• Bufferpools = 20
• Prefetchers(db2pfchr) = 20
• Page Cleaners(db2pclnr) = 21
• Logs(db2loggr) = 21
• Deadlock Detector = 21
• Connection Concentrator = 22
• Summary = 22
• CHAPTER 3 Logical and Physical Design = 25
• Business Model = 25
• Business Entities = 26
• Business Rules = 28
• Entitites and Relationships = 28
• Entity = 28
• Relationship = 28
• Candidate Key = 28
• Primary Key = 29
• Relation = 29
• Foreign Key = 29
• Cardinality = 29
• One-to-One Relationship(A＝＞B and B＝＞A) = 29
• One-to-Many Relationship(A＝＞B but B not ＝＞A) = 29
• Many-to-Many Relationship(A not ＝＞B and B not ＝＞A) = 30
• Special Entity Relationships = 30
• Characteristic Entity = 30
• Associative Entity = 31
• Normalization = 31
• Functional Dependencies = 31
• Key = 32
• First Normal Form = 32
• Second Normal Form = 33
• Third Normal Form(NF) = 34
• Boyce-Codd Normal Form(BCNF) = 34
• Domain Key／Normal Form(DK／NF) = 35
• Constraint = 35
• Key = 35
• Domain = 35
• Unified Modeling Language(UML) = 36
• Logical Design Outputs = 36
• Physical Design = 37
• Denormalization = 37
• Creation of Indexes = 37
• Creation of Tablespaces and Tables = 38
• Tablespace and Breakout Strategy = 38
• Bufferpool Strategy = 39
• Summary = 39
• CHAPTER 4 Application Development = 41
• Getting Started = 41
• DB2 Administration Client = 42
• DB2 Application Development Client = 42
• DB2 Personal Developers' Edition(PDE) = 42
• DB2 Universal Developers' Edition = 42
• DB2 Development Center = 43
• DB2 Visual Explain = 43
• Development Center = 46
• Java Thread-Safe Routines = 55
• Java Common Client = 56
• SQL Enhancements = 56
• Declared Global Temporary Table(DGTT) Enhancements = 56
• SQL Assist = 56
• SQL Enhancements = 57
• Instead Of Triggers = 57
• New Built-in Functions = 57
• Informational Constraints = 57
• Insert Through Union All Views = 57
• Summary Tables = 57
• Current Refresh Age Special Register = 59
• Materialized Query Table(MQT) = 59
• User-Maintained MQTs = 60
• eXtensible Markup Language = 60
• Summary = 61
• CHAPTER 5 Type-2 Indexes and Multidimensional Clustering = 63
• Benefits of Type-2 Indexes = 63
• Type-2 Indexes and Next-Key Locking = 64
• Migration Considerations for Type-2 Indexes = 64
• Suggested Migration Strategy for Type-2 Indexes = 65
• Multidimensional Clustering = 65
• MDC Table Considerations = 69
• Summary = 70
• CHAPTER 6 High Performance Tablespace Design and I／O Strategies = 71
• Tablespaces = 71
• Tables = 73
• Storage Models = 73
• System Managed Space = 74
• Database Managed Space = 75
• Extents = 75
• Prefetching = 77
• Prefetching Performance = 77
• Tables and Tablespace Consierations = 78
• The Life of an I／O Request = 79
• Redundant Array of Independent Disks(RAID) = 81
• RAID-5 = 83
• Tablespace Container Management = 85
• DB2 and IBM Enterprise Storage System(ESS SHARK) = 86
• ESS Terminology = 86
• Cache Management = 86
• Nonvolatile Storage = 87
• Configuring DB2 for Optimal Performance on ESS = 88
• Tying It All Together = 90
• ESS Storwatch Specialist = 90
• Keeping a Map of Your Database = 91
• ESS Storage Expert = 92
• The I／O Bottleneck = 92
• PCI Bus = 93
• PCI Express(Arapahoe, 3GIO) = 93
• PCI-X = 93
• InfiniBand Comes to the Rescue = 94
• IBA Link Speeds = 96
• Summary = 96
• CHPATER 7 Utilities and Commands = 97
• Reorganizing Indexes／Tables = 98
• Command Parameters = 99
• Examples = 103
• Utility Guidelines and Exceptions = 104
• REORGCHK Utility = 105
• Command Parameters = 106
• Database Loggin = 110
• Crash Recovery = 111
• Version Recovery = 111
• Circular Logging = 111
• Archival Logging = 111
• Roll-forward Recovery = 113
• Important Facts Regarding Logs = 113
• Backup Database Utility = 114
• Types of Backup = 115
• Command Parameters = 115
• Examples = 118
• Restore Database Utility = 119
• Command Parameters = 121
• Examples = 125
• Utility Guidelines and Exceptions = 127
• Roll-forward Database = 127
• Command Parameters = 127
• Query Status = 130
• Database Partition Number = 131
• Tablespace = 131
• Examples = 133
• Utility Guidelines and Exceptions = 135
• Archive Log Command = 136
• Command Parameters = 136
• Utility Guidelines and Exceptions = 137
• Compatibilities = 138
• List History = 138
• Command Parameters = 138
• Examples = 139
• Utility and Guidelines and Exceptions = 139
• List Tablespaces = 140
• Command Parameters = 141
• Set Tablespace Containers Command = 142
• Command Parameters = 143
• Examples = 143
• Utility Guidelines and Exceptions = 144
• DB2TBST - Get Tablespace State Command = 144
• Command Parameters = 144
• Examples = 144
• RUNSTATS Utility = 144
• Command Parameters = 145
• Utility Guidelines and Exceptions = 148
• Examples = 150
• LOAD Utility = 151
• Command Parameters = 152
• LOAD QUERY Command = 163
• Command Parameters = 164
• Examples = 164
• Utility Guidelines and Exceptions = 165
• QUIESCE Command = 166
• Command Parameters = 167
• Examples = 167
• Utility Guidelines and Exceptions = 168
• UNQUIESCE Command = 168
• Command Parameters = 169
• Examples = 169
• Migrate Database Utility = 169
• Command Parameters = 170
• Examples = 170
• Utility Guidelines and Exceptions = 170
• INSPECT Utility = 170
• Command Parameters = 171
• Utility Guidelines and Exceptions = 173
• Summary = 174
• CHAPTER 8 Tuning Bufferpools = 175
• Introduction = 175
• Maintaining Bufferpools = 176
• Creating Bufferpools = 176
• Altering Bufferpools = 177
• Dropping Bufferpools = 177
• Monitoring Bufferpool Performance = 178
• Monitor Switches = 178
• Snapshot Commands = 178
• DB2 Event Monitors = 179
• Formulas for Determining Bufferpool Efficiency and Effectiveness = 180
• I／O Performance Measurements : ORMS, ARMS, SRMS = 185
• Monitoring and Tuning Tables, Bufferpools, and Tablespaces = 186
• Table Activity = 186
• Table Space Activity = 187
• Assigning Table Spaces to Bufferpools : General Concepts = 187
• Optimum Bufferpool Assignments = 188
• Sizing Bufferpools = 188
• Block Prefetch I／O, Special Considerations = 189
• Summary = 189
• CHAPTER 9 Tuning Configuration Parameters = 191
• Autonomic Computing = 192
• Online Configuration Parameters = 193
• DB2 Memory Areas = 194
• Database Manager Shared Memory = 195
• Instance Memory(instance_memory) = 196
• Group Heap Ratio(group_heap_ratio) = 196
• Sort Heap Threshold for Shared Sorts = 197
• Global Control Block = 197
• Monitor Heap(mon_heap_sz) = 197
• Audit Buffer(audit_buf_sz) = 197
• FCM Connection Entries(fcm_num_connect) = 197
• FCM Message Anchors(fcm_num_anchors) = 198
• FCM Request Block(fcm_num_rqdb) = 198
• FCM Bufferpool = 198
• Java Heap Size(java_heap_sz) = 198
• Database Memory(database_memory) = 199
• Database Global Memory = 199
• Utility Heap(util_heap_sz) = 200
• Backup Buffer(backbufsz) = 200
• Restore Buffer(restbufsz) = 200
• Package Cache Size(pckcachesz) = 200
• Bufferpool Size(buffpage) = 201
• Extended Memory Cache = 201
• Lock List(locklist) = 201
• Database Heap(dbheap) = 202
• Log Buffer(logbufsz) = 202
• Catalog Cache(catalogcache_sz) = 202
• Application Group Memory(appgroup_mem_sz) = 203
• Application Global Memory(app_ctl_heap_sz) = 203
• Agent Private memory = 204
• Application Heap(applheapsz) = 204
• Agent Stack Size(agent_stack_sz) = 205
• Statistics Heap(stat_heap_sz) = 205
• Sort Heap(sortheap) = 205
• DRDA Heap(drda_heap_sz) = 206
• UDF Memory(udf_mem_sz) = 206
• Statement Heap(stmtheap) = 206
• Query Heap Size(query_heap_sz) = 206
• Client IO Block(rqrioblk) = 206
• Application Support Layer Heap(aslheapsz) = 207
• Agent Parameters = 207
• Coordinating Agents = 207
• MAXAGENTS = 207
• MAXCAGENTS = 208
• MAXAPPLS = 208
• Agent Monitoring = 208
• Configuration Advisor = 209
• Summary = 215
• CHAPTER 10 Monitoring = 217
• Enabling Monitoring = 217
• Database Manager Snapshot = 220
• Database Snapshot = 225
• Application Snapshot = 230
• Dynamic SQL Snapshot = 235
• Resetting Monitor Switches = 236
• Snapshots through New SQL Functions = 236
• Working with SQL Snapshot Function = 237
• Event Monitoring = 239
• Database Events = 240
• Table Events = 240
• Deadlock Events = 240
• Tablespace Events = 241
• Bufferpools Events = 241
• Connection Events = 241
• Statement Events = 241
• Transactions Events = 241
• Event Monitor Creation = 242
• Create Event Monitor Options = 242
• WHERE event condition = 242
• WRITE TO = 243
• Table Options = 243
• Event Monitor Scope = 244
• Global = 244
• Local = 244
• Event Monitor Catalog Tables = 244
• Write-to-Table-Event Monitors = 244
• General Consideration for Write-to-Table Event Monitors = 247
• DB2 Event Analyzer(db2eva) = 247
• Health Monitor and Health Center = 248
• Summary = 251
• CHAPTER 11 Problem Determination = 253
• Connectivity Problems = 253
• Performance and Application Problems = 255
• DB2 Code(Defect) Problems = 255
• DB2 Problem Determination Aids = 257
• DB2DIAG.LOG File = 257
• Format = 257
• DB2DIAG.LOG SQLCA Entries = 260
• DB2 Administration Notification Log = 261
• System Logs = 263
• CLI TRace = 264
• DB2 Trace = 264
• DRDA Trace = 265
• Dumps = 265
• Dr. Watson = 266
• Traps = 267
• Call Stack Traces = 267
• Sending Information to DB2 Support = 268
• Summary = 270
• CHAPTER 12 Understanding and Tuning DB2 Sort = 271
• DB2 Sort Memory Areas = 272
• SORTHEAP = 272
• Types of Sorts = 272
• Shared Sort = 273
• SHEAPTHRES = 278
• Sizing Sort Memory Areas = 278
• Agent Private Sort(sortheap DBCFG parameter) = 279
• Monitoring Sort Performance = 280
• Snapshot Monitoring = 280
• Event Monitors = 282
• Elimating Sorts = 285
• SQL Explain Tools = 287
• Changing SQL = 288
• Allow Reverse Scans = 288
• Summary = 289
• CHAPTER 13 Enterprise Server Edition - Database Partitioning Feature = 291
• When Should You Use Partitioned Databases? = 291
• DB2 v8.1 ESE Improvements = 294
• New Partitioning Terminology = 294
• Database Partitioning = 295
• DB2NODES.CFG File = 296
• Partitioning Keys = 299
• Partitioning Map Concepts = 299
• Database Partition Group = 300
• Collocated Joins = 301
• Partitioned Database Join Strategies = 302
• Directed Inner Table Join Strategy = 302
• Directed Inner and Outer Table Joins = 304
• Broadcast Table Joins - Inner and Outer = 305
• The End Result - Performance = 307
• Dynamic Bitmap Index Anding(DBIA) = 307
• Index Considerations in a DPF Environment = 309
• OLTP Environments = 309
• BI／DW Environments = 309
• Load Utility Considerations in a DPF Environment = 310
• Load Utility Opeations = 310
• Partitioned DB CONFIG Options = 313
• Monitoring Load Operations = 318
• Restarting or Terminating a Failed Load Operation = 318
• Adding a Database Partition = 320
• Improving Import Performance with Buffered Inserts = 320
• Summary = 321
• Appendix A DB2 Catalog Views = 323
• SYSCAT.ATTRIBUTES = 323
• SYSCAT.BUFFERPOOLDBPARTITIONS = 325
• SYSCAT.BUFFERPOOLS = 325
• SYSCAT.CASTFUNCTIONS = 326
• SYSCAT.CHECKS = 326
• SYSCAT.COLAUTH = 327
• SYSCAT.COLCHECKS = 328
• SYSCAT.COLDIST = 328
• SYSCAT.COLDGROUPDIST = 329
• SYSCAT.COLGROUPDISTCOUNTS = 330
• SYSCAT.COLGROUPS = 330
• SYSCAT.COLOPTIONS = 331
• SYSCAT.COLUMNS = 331
• SYSCAT.COLUSE = 337
• SYSCAT.CONSTDEP = 337
• SYSCAT.DATATYPES = 338
• SYSCAT.DBAUTH = 340
• SYSCAT.DBPARTITIONGROUPDEF = 341
• SYSCAT.DBPARTITIONGROUPS = 342
• SYSCAT.EVENTMONITORS = 342
• SYSCAT.EVENTS = 344
• SYSCAT.EVENTTABLES = 344
• SYSCAT.FULLHIERARCHIES = 345
• SYSCAT.FUNCMAPOPTIONS = 346
• SYSCAT.FUNCMAPPARMOPTIONS = 346
• SYSCAT.FUNCMAPPINGS = 347
• SYSCAT.HIERARCHIES = 347
• SYSCAT.INDEXAUTH = 348
• SYSCAT.INDEXCOLUSE = 348
• SYSCAT.INDEXDEP = 349
• SYSCAT.INDEXES = 350
• SYSCAT.INDEXEXPLOITRULES = 357
• SYSCAT.INDEXEXTENSIONDEP = 358
• SYSCAT.INDEXEXTENSIONMETHODS = 358
• SYSCAT.INDEXEXTENSIONPARMS = 359
• SYSCAT.INDEXEXTENSIONS = 360
• SYSCAT.INDEXOPTIONS = 360
• SYSCAT.KEYCOLUSE = 361
• SYSCAT.NAMEMAPPINGS = 361
• SYSCAT.PACKAGEAUTH = 362
• SYSCAT.PACKAGEDEP = 362
• SYSCAT.PACKAGES = 364
• SYSCAT.PARTITIONMAPS = 368
• SYSCAT.PASSTHRUAUTH = 368
• SYSCAT.PREDICATESPECS = 369
• SYSCAT.PROCOPTIONS = 369
• SYSCAT.PROCPARMOPTIONS = 370
• SYSCAT.REFERENCES = 370
• SYSCAT.REVTYPEMAPPINGS = 371
• SYSCAT.ROUTINEAUTH = 373
• SYSCAT.ROUTINEDEP = 374
• SYSCAT.ROUTINEPARMS = 374
• SYSCAT.ROUTINES = 376
• SYSCAT.SCHEMAAUTH = 383
• SYSCAT.SCHEMATA = 384
• SYSCAT.SEQUENCEAUTH = 385
• SYSCAT.SEQUENCES = 385
• SYSCAT.SERVEROPTIONS = 387
• SYSCAT.SERVERS = 387
• SYSCAT.STATEMENTS = 388
• SYSCAT.TABAUTH = 388
• SYSCAT.TABCONST = 390
• SYSCAT.TABDEP = 390
• SYSCAT.TABLES = 391
• SYSCAT.TABLESPACES = 397
• SYSCAT.TABOPTIONS = 398
• SYSCAT.TBSPACEAUTH = 398
• SYSCAT.TRANSFORMS = 399
• SYSCAT.TRIGDEP = 400
• SYSCAT.TRIGGERS = 401
• SYSCAT.TYPEMAPPINGS = 402
• SYSCAT.USEROPTIONS = 403
• SYSCAT.VIEWS = 404
• SYSCAT.WRAPOPTIONS = 405
• SYSCAT.WRAPPERS = 405
• SYSCAT.COLDIST = 405
• SYSCAT.COLUMNS = 407
• SYSCAT.INDEXES = 409
• SYSCAT.ROUTINES = 414
• SYSCAT.TABLES = 416
• APPENDIX B DB2 Information on the Web = 419
• DB2-Listserver = 419
• Newsgroup = 419
• Newsletters = 419
• BITPIPE = 419
• Data Warehousing = 420
• ITWORLD = 420
• Sysopt = 420
• Tidal Wire = 420
• e-zines = 420
• Web Sites = 420
• Association for Computing Machinery(ACM) Transactions on Databases = 420
• International Society of Electrical and Electronic Engineers(IEEE) Computer Society = 420
• DB2 Online Technical Support = 420
• DB2 Zone = 421
• DB-HQ = 421
• IBM Redbooks = 421
• DB2 UDB v8 for Linux, UNIX, and Windows Home Page = 421
• User GRoup = 421
• International DB2 Users Group = 421
• Vendor Web Sites = 421
• BMC Software = 421
• Embarcadero Technologies = 421
• Computer Associates = 421
• Candle Corporation = 421
• Compuware = 421
• Quest = 422
• Veritas Software = 422
• APPENDIX C DB2 Limits = 423
• Identifier Length Limits = 423
• Numeric Limits = 425
• String Limits = 425
• Datetime Limits = 426
• Database Manager Limits = 426
• Database Manager Page Size-Specific Limits = 429
• APPENDIX D DB2 Registry and Environmental Variables = 431
• General Registry Variables = 432
• System Environment Variables = 436
• Communications Variables = 438
• Command-Line Variables = 442
• MPP Configuration Variables = 443
• SQL Compiler Variables = 444
• Performance Variables = 448
• Miscellaneous Variables = 455
• Bibliography = 461
• Index = 465