IBM DB2 9 New Features

by Zikopoulos, Paul; Baklarz, George; Katsnelson, Leon; Eaton, Chris

• ISBN13: 9780072264593
• ISBN10: 0072264594
• Edition: 1st
• Format: Paperback
• Copyright: 2007-03-08
• Publisher: McGraw-Hill Education

Summary

Design and deploy integrated enterprise-class business solutionsEnhance productivity, profitability, and efficiency using IBM DB2 9 and the detailed information in this comprehensive resource. Written by experienced IBM data server professionals, IBM DB2 9 New Features covers the technical details of the major new aspects of DB2 9 alongside real-world business uses, syntax examples, and insider tips. Learn how to manipulate and manage XML data using pureXML, automate maintenance tasks, and seamlessly integrate Java, PHP, and Perl scripts. Plus, you'll get vital coverage of the brand-new DB2 Developer Workbench, enhanced .NET developer support, Ruby on Rails, and database security. Learn about all the auto-configuration features that make getting DB2 up and running faster than ever before Define, share, and validate data using XML and the unique pureXML DB2 Hybrid Storage Engine Improve runtime performance and optimize storage with the new DB2 Compression Technology Automatically handle DB2 memory allocation using the Self Tuning Memory Manager Use Automatic Storage, Autonomic Computing, and a host of other autonomic features that make managing DB2 a snap Create dynamic data server blueprints with Rational Data Architect version 6.1 Incorporate Java applications using the IBM WebSphere Application Server and DB2 data server Develop open-source scripts using PHP, Perl, Toad, and Ruby on Rails Utilize the DB2 Developer Workbench or exploit the enhanced .NET developer support Deploy the latest DB2 security and high availability features

Author Biography

Paul C. Zikopoulos, BA, MBA, is on the IBM Database Competitive Technology team. He is the coauthor of DB2:The Complete Reference.

George Baklarz, B Math, M Sc, is a senior manager in the DB2 Worldwide Pre-sales Support Group.

Leon Katsnelson works in the IBM Toronto Lab where he manages a team of IBM DB2 UDB product managers.

Chris Eaton is the senior product manager for DB2 Universal Database.

 

Table of Contents

Letters from Executives	p. xvii
Acknowledgments	p. xx
Introduction	p. xxiii
XML in the DB2 Hybrid Storage Engine
What Is XML?	p. 3
Components of on XML Document	p. 5
Parsing and Serialization	p. 6
Well-Formed and Valid XML	p. 7
XML Schema Definition Documents and DTDs	p. 8
Namespaces: Your Guide to Element Naming Collisions	p. 11
Helping a Namespace: A Universal Resource Identifier	p. 12
Default Namespaces	p. 13
XPath Expressions	p. 13
Examples of Navigating XML with an XPath Expression	p. 13
Defining How Your XML Looks	p. 15
Wrap Up	p. 16
When Do I Use pureXML?	p. 17
Why XML?	p. 18
Typical Use Cases for XML	p. 19
Why XML in a Relational Database?	p. 20
pureXML Solves Your Data Problems	p. 21
XML and Relational Rules of Thumb	p. 23
Our Rule of Thumb for When to Use an XML Model	p. 23
Our Rule of Thumb for When to Use a Relational Model	p. 24
Traditional Methods for Storing XML	p. 24
Why the Large Object Approach Doesn't Work	p. 25
Why Shredding XML to Relational Doesn't Work	p. 26
Wrap Up	p. 29
pureXML: The DB2 9 XML Database	p. 31
XML in DB2 9 in a Nutshell	p. 32
DB2 9 and Your XML Data	p. 33
Wrap Up	p. 52
Tuning XML Performance	p. 53
XML Performance Topics for Database Administrators	p. 54
Optimizing XML Data Storage	p. 54
Measuring XML Performance Using Snapshot Monitor	p. 56
Indexing Strategies for XML Performance	p. 57
Collecting Statistics for XML Data and Indexes	p. 59
Bulk Loading of XML Data	p. 59
XML Performance Topics for Database Programmers	p. 60
Adjusting the Structure of XML Documents to Improve Performance	p. 60
To Validate or Not to Validate: That Is the Question	p. 61
Better Performing XPath Expressions	p. 63
Use Predicates to Filter Your XML Data	p. 64
Use Parameter Markers	p. 66
Minimize Code Page Conversions	p. 67
Wrap Up	p. 68
Autonomics and Manageability
Up and Running Enhancements	p. 71
New Preconfigured Features in DB2 9	p. 72
Automatic Selection of Default I/O Prefetchers and Page Cleaners	p. 72
Configuration Advisor	p. 73
Self Tuning Memory Manager	p. 73
Statistics Collection	p. 74
Table and Index Reorganization	p. 75
Automatic Storage	p. 75
Copying Database Schemas	p. 75
Installation Enhancements in DB2 9	p. 76
Multiple DB2 Versions and Releases on a Server	p. 76
Installation and Maintenance Features for Windows	p. 77
New DB2 Client Packaging	p. 77
New Standalone ODBC Driver	p. 78
Wrap Up	p. 79
The Self Tuning Memory Manager	p. 81
The DB2 Memory Model 101	p. 83
Operating System Memory Management and STMM	p. 84
How Memory Was Tuned in Earlier DB2 Versions	p. 85
DB2 Memory Tuning in DB2 7	p. 85
DB2 Memory Tuning in DB2 8	p. 85
The STMM in DB2 9	p. 87
How STMM Works	p. 87
Using STMM in DB29	p. 96
Turn On STMM	p. 96
Define the Amount of Memory Allocated to DB2	p. 97
Configure Each Heap Supported by STMM	p. 98
STMM Enablement and Configurotion Parameters	p. 100
Disabling STMM	p. 101
DB2 9 Memory Model Changes in Support of STMM	p. 101
Getting STMM Details	p. 102
Reaping the Benefits of STMM	p. 102
Hitting a Home Run: Best Environments for STMM	p. 102
Hitting a Double: Next Best Environments for STMM	p. 104
How Well Does STMM Work?	p. 104
The Dropped Index Test	p. 105
The Two Databases Same Server Test	p. 106
The Poorly Tuned Buffer Pool Test	p. 107
Wrap Up	p. 108
Automatic Storage	p. 109
Table Space and Container Basics	p. 110
Automatic Storage Table Spaces in 8.2.2	p. 111
Automatic Storage Databases in 8.2.2	p. 111
Database Partitioning Feature Support in DB2 9	p. 112
Storage Paths and Expressions	p. 113
Adding Storage Paths in DPF	p. 114
Adding a Database Partition	p. 115
Automatic Growth in DPF	p. 115
Backup and Restore Considerations	p. 116
Monitoring Space Utilization	p. 117
Wrap Up	p. 118
Other Autonomic and Manageability Enhancements	p. 119
Autonomic Enhancements	p. 120
Automatic Defaults for Page Cleaners and Prefetchers	p. 120
Automatic Defaults for Table Space Characteristics	p. 121
Autonomics by Default	p. 122
Enhancements to Monitoring and Database Health	p. 125
New and Updated Monitor UDFs and Views	p. 125
SQL Access to Notification Logs	p. 128
Manageability Enhancements	p. 130
Copy and Drop Schema	p. 131
In-Place Alter Table Operations	p. 133
Database Administration Through SQL	p. 134
Get Error Messages in SQL PL via SQLERRM	p. 134
Storage Paths for Creating a Database in Windows	p. 135
MQTs Considered by the Optimizer but Not Chosen	p. 136
Wrap Up	p. 138
Application Development
Rational Data Architect	p. 141
Data Modeling	p. 144
Putting Theory into Practice	p. 145
Wrap Up	p. 148
DB2 and the .NET Developer	p. 151
.NET and the Database Programmer	p. 152
The DB2 and .NET Stack	p. 154
DB2 Implementation of AD0.NET	p. 155
Implementing DB2 CLR Procedures	p. 159
DB2 and Microsoft Visual Studio	p. 160
Versions	p. 161
Server Explorer and IBM Explorer	p. 162
Organizing and Managing Assets	p. 168
DB2 and .NET in the Palm of Your Hand	p. 170
Wrap Up	p. 170
DB2 and the Java Developer	p. 171
Evolving with Java	p. 172
JDBC, SQU, and Drivers that Implement Them	p. 173
Driver Types	p. 174
Stronger, More Functional Java	p. 175
Development Tools	p. 176
Java Meets XML	p. 181
Wrap Up	p. 183
DB2 and the Scripting Languages Developer	p. 185
DB2 on Rails	p. 186
Ruby, a Gem of a Language	p. 186
The Rails Framework for Web Applications	p. 188
Getting Started with DB2 on Rails	p. 192
Perl	p. 200
PHP	p. 203
Wrap Up	p. 205
DB2 and the XML Developer	p. 207
Getting Ready to Store XML	p. 209
XML and All That CRUD	p. 211
Creating Data	p. 211
Retrieving Data with SQL and XQuery	p. 213
Updating and Deleting Data	p. 215
Self-Describing XML	p. 217
The Other Way to Store XML Data	p. 218
The Other Way to Produce XML Data	p. 221
Wrap Up	p. 221
The DB2 Developer Workbenth	p. 223
The Eclipse Platform	p. 224
About the DB2 Development Center	p. 224
A Tour of the Developer Workbench Views and Features	p. 225
The Database Explorer View	p. 226
The Data Project Explorer View	p. 232
SQL Development	p. 233
Routine Development	p. 235
SQLJ Support	p. 239
Comparison Utility	p. 242
XML Development	p. 243
Teaming Support	p. 249
Visual Explain Facility	p. 249
Wrap Up	p. 249
Other Application Development Topics	p. 251
Toad: It's Not Just for Oracle Anymore	p. 252
Other Enhancements for Application Developers	p. 254
Deploying Your Applications	p. 256
Wrap Up	p. 259
Security Enhancements
Label-Based Access Control	p. 263
Security Levels	p. 264
LBAC Overview	p. 266
What LBAC Does Not Prevent	p. 267
SECADM Authority	p. 268
Defining Security Label Components	p. 269
Security Labels	p. 269
Defining Security Policies	p. 270
Set Label Comparisons	p. 271
Array Label Comparisons	p. 272
Tree-Label Comparisons	p. 273
Defining a Security Policy	p. 274
Defining Security Labels	p. 275
Creating Protected Tables	p. 276
Column Security	p. 277
Row Security	p. 278
Row and Column Security	p. 278
Table Security	p. 279
Granting Security Labels to Users	p. 280
LBAC Exemptions	p. 280
Bringing It All Together: Using Protected Tables	p. 283
Inserting with Protected Rows	p. 283
Selecting from Tables with Protected Rows	p. 286
Inserting with Protected Columns	p. 288
Selecting from Tables with Protected Columns	p. 289
Removing or Modifying LBAC Definitions	p. 291
Modifying Security Definitions	p. 292
Referential Integrity and Constraint Checking	p. 292
Referential Integrity	p. 292
Check Constraints	p. 292
Primary and Unique Key Constraints	p. 293
Wrap Up	p. 293
Additiond Security Enhancements	p. 295
SECADM Authority	p. 296
LDAP Security Plug-in	p. 297
LDAP Plug-in Availability	p. 297
Implementing LDAP	p. 298
Transfer Ownership	p. 298
SETSESSIONUSER Privilege	p. 299
RESTRICT on Database Create	p. 300
Wrap Up	p. 302
Sharp Enhancements
Table Partitioning	p. 305
Table Partitioning: What Is It and Why Use It?	p. 306
Creating a Range-Partitioned Table	p. 309
Defining Ranges	p. 310
Partition Elimination	p. 311
Inserts and Updates	p. 311
Open-Ended Ranges	p. 312
Special Values	p. 312
Inclusive and Exclusive Bounds	p. 313
Implicit Bounds	p. 314
Naming Partitions	p. 314
Handling NULL Values	p. 315
Computing Ranges: Short Syntax	p. 316
Open-Ended Ranges with Computed Ranges	p. 317
Partitioning on Multiple Columns	p. 318
Storage Mapping of Range-Partitioned Tables	p. 318
New Operations for Roll-Out and Roll-In	p. 321
Adding Partitions to an Existing Table	p. 321
Set Integrity Processing	p. 323
Data Availability During Attach and Set Integrity Processing	p. 324
Deleting Partitions from on Existing Table	p. 325
Data Availability During Attach and Set Integrity Processing	p. 327
Asynchronous Index Cleanup ofter DETACH	p. 328
Additional Considerations for Range Partitioning	p. 329
Migration from a UNION ALL View	p. 329
Using MDC with Table Partitioning	p. 330
Compression and Table Partitioning	p. 331
Wrap Up	p. 332
Larger Table Space Support	p. 333
Table Spaces	p. 334
Original Row Identifier Format	p. 334
DB2 9 Row Idenfifiers	p. 336
Creating Large Table Spaces	p. 337
Migrating to Large Table Spaces	p. 341
Considerations for Using Table Spaces	p. 345
Indexes	p. 346
Allow Reverse Scans	p. 346
Considerations for Using Large Indexes	p. 347
Wrap Up	p. 347
Row Compression	p. 349
Row Compression Basics	p. 351
How Compression Works	p. 351
Compressing Tables	p. 352
How Best to Create Compressed Tables	p. 354
Statistical Information on Compressed Tables	p. 355
Estimating Storage Sovings from Compression	p. 355
Dictionary Storage and Size	p. 357
Adding Data to a Compressed Table	p. 357
Using Compression with Table Partitioning	p. 358
Using Compression with the Database Partitioning Feature	p. 358
Compression Restrictions	p. 359
Rules of Thumb for Compressed Tables	p. 360
Wrap Up	p. 360
Storage Savings	p. 361
Performance Benefits	p. 361
High-Availability Enhancements	p. 363
Minimizing Unplanned Outages	p. 364
Connection Timeout	p. 364
Rebuild Partial Database	p. 367
Redirected Restore Scripting	p. 369
Engine Trap Data Collection	p. 374
Server Hang Data Collection	p. 375
Catch Errors and Gather Problem Determination Data	p. 376
Change Point in Time for Recover	p. 377
Rollforward Output Shows UTC or Local Time	p. 378
Minimizing Planned Outages	p. 380
In Place Alter Table	p. 380
Retrieve List of Files for Comprehensive Backup	p. 383
ADMIN_CMD for Wore Commond/Utility Processing	p. 385
Wrap Up	p. 388
Additional SHARP Enhancements	p. 389
Internet Protocol Version 6	p. 390
Operating System Support	p. 391
Migration Support	p. 393
Statistical Views	p. 394
Enabling Statistical Views	p. 395
Statistical View Example	p. 395
MQT Enhancements	p. 397
MQT Design	p. 398
MQT Performance Enhancements	p. 400
MQT Maintenance	p. 401
MQT and NULL Columns	p. 401
Large Page Size on AIX	p. 401
Fast Communication Manager	p. 402
SAP Optimization Enhancements	p. 402
SAP Optimizotion	p. 402
Automatic AVG_APPLS	p. 403
Limiting Query Temp Space	p. 403
Wrap Up	p. 404
Index	p. 405
Table of Contents provided by Ingram. All Rights Reserved.