Ibm Db2 9 New Features

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

Overview

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.

Design and deploy integrated enterprise-class business solutions

Enhance 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

Product Details

ISBN-13:	9780072264593
Publisher:	McGraw Hill LLC
Publication date:	02/15/2007
Pages:	422
Product dimensions:	7.20(w) x 9.00(h) x 0.96(d)

About the Author

McGraw-Hill authors represent the leading experts in their fields and are dedicated to improving the lives, careers, and interests of readers worldwide

McGraw-Hill authors represent the leading experts in their fields and are dedicated to improving the lives, careers, and interests of readers worldwide

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.

McGraw-Hill authors represent the leading experts in their fields and are dedicated to improving the lives, careers, and interests of readers worldwide

Table of Contents

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