DB2 pureXML Cookbook: Master the Power of the IBM Hybrid Data Server

by Matthias Nicola, Pav Kumar-Chatterjee

Overview

DB2 pureXML Cookbook

Master the Power of the IBM Hybrid Data Server

Hands-On Solutions and Best Practices for Developing and Managing XML Database Applications with DB2

More and more database developers and DBAs are being asked to develop applications and manage databases that involve XML data. Many are utilizing the highly praised DB2 pureXML technology from IBM. In the DB2 pureXML Cookbook, two leading experts from IBM offer the practical solutions and proven code samples that database professionals need to build better XML solutions faster. Organized by task, this book is packed with more than 700 easy-to-adapt “recipe-style” examples covering the entire application lifecycle–from planning and design through coding, optimization, and troubleshooting. This extraordinary library of recipes includes more than 250 XQuery and SQL/XML queries. With the authors’ hands-on guidance, you’ll learn how to combine pureXML “ingredients” to efficiently perform virtually any XML data management task, from the simplest to the most advanced.

Coverage includes

• pureXML in DB2 9 for z/OS and DB2 9.1, 9.5, and 9.7 for Linux, UNIX, and Windows
• Best practices for designing XML data, applications, and storage objects Importing, exporting, loading, replicating, and federating XML data
• Querying XML data, from start to finish: XPath and XQuery data model and languages, SQL/XML, stored procedures, UDFs, and much more
• Avoiding common errors and inefficient XML queries
• Converting relational data to XML and vice versa
• Updating and transforming XML documents
• Defining and working with XML indexes
• Monitoring and optimizing the performance of XML queries and other operations
• Using XML Schemas to constrain and validate XML documents
• XML application development–including code samples for Java, .NET, C, COBOL,PL/1, PHP, and Perl

Product Details

ISBN-13:	9780138150471
Publisher:	IBM Press
Publication date:	08/25/2009
Pages:	756
Product dimensions:	7.30(w) x 9.20(h) x 1.80(d)

About the Author

Matthias Nicola is a Senior Software Engineer for DB2 pureXML at IBM’s Silicon Valley Lab. His work focuses on all aspects of XML in DB2, including XQuery, SQL/XML, XML storage, indexing, and performance. Matthias also works closely with customers and business partners, assisting them in the design, implementation, and optimization of XML solutions. Matthias has published more than a dozen articles on various XML topics (see www.matthiasnicola.de) and is a frequent speaker at DB2 conferences. Prior to joining IBM, Matthias worked on data warehousing performance for Informix Software. He received his doctorate in computer science from the Technical University of Aachen, Germany.

Pav Kumar-Chatterjee has worked with DB2 since 1991 on DB2 for z/OS and since 2000 on DB2 for Linux, UNIX, and Windows. He is currently employed by IBM as a technical sales specialist for Information Management in the United Kingdom. He has helped customers implement the XML Extender product with DB2 V8 and has presented on DB2 and XML in the United Kingdom and around Europe.

Table of Contents

Chapter 1 Introduction 1

1.1 Anatomy of an

1.2 Differences Between

1.3 Overview of DB2 pure

1.4 Benefits of DB2 pure

1.5

1.5.1 When the Schema Is Volatile 12

1.5.2 When Data Is Inherently Hierarchical in Nature 12

1.5.3 When Data Represents Business Objects 12

1.5.4 When Objects Have Sparse Attributes 13

1.5.5 When Data Needs to be Exchanged 13

1.6 Summary 13

Chapter 2 Designing

2.1 Choosing Between

2.2

2.3 Choosing the Right Document Granularity 22

2.4 Using a Hybrid

2.5 Summary 25

Chapter 3 Designing and Managing

3.1 Understanding

3.2 Understanding pure

3.3

3.3.1 Storage Objects for

3.3.2 Defining Columns,Tables, and Table Spaces for

3.3.3 Dropping

3.3.4 Improved

3.4 Using

3.4.1 Monitoring and Configuring

3.4.2 Potential Benefits and Drawbacks of

3.5 Compressing

3.6 Examining

3.7 Reorganizing

3.8 Understanding

3.9

3.9.1

3.9.2

3.10

3.11

3.11.1 Storage Objects for

3.11.2 Characteristics of

3.11.3 Tables with Multiple

3.11.4 Naming and Storage Conventions 64

3.12 Utilities for

3.12.1 REPORT TABLESPACESET for

3.12.2 Reorganizing

3.12.3 CHECK DATA for

3.13

3.13.1 Controlling the Memory Consumption of

3.13.2 Redirecting

3.14 Summary 73

Chapter 4 Inserting and Retrieving

4.1 Inserting

4.1.1 Simple Insert Statements 76

4.1.2 Reading

4.2 Deleting

4.3 Retrieving

4.4 Handling Documents with

4.5 Copying Full

4.6 Dealing with

4.7 Understanding

4.7.1 Preserving

4.7.2 Changing the Whitespace Default from “Strip” to “Preserve” 93

4.7.3 Storing

4.8 Summary 95

Chapter 5 Moving

5.1 Exporting

5.1.1 Exporting

5.1.2 Exporting

5.1.3 Exporting

5.1.4 Exporting

5.1.5 Exporting Fragments of

5.1.6 Exporting

5.2 Importing

5.2.1 IMPORT Command and Input Files 107

5.2.2 Import/Insert Performance Tips 108

5.3 Loading

5.4 Unloading

5.5 Loading

5.6 Validating

5.7 Splitting Large

5.8 Replicating and Publishing

5.9 Federating

5.10 Managing

5.11 Handling

5.12 Summary 123

Chapter 6 Querying

6.1 An Overview of Querying

6.2 Understanding the XQuery and XPath Data Model 128

6.2.1 Sequences 128

6.2.2 Sequence in, Sequence out 130

6.3 Sample Data for XPath, SQL/

6.4 Introduction to XPath 132

6.4.1 Analogy Between XPath and Navigating a File System 133

6.4.2 Simple XPath Queries 133

6.5 How to Execute XPath in DB2 137

6.6 Wildcards and Double Slashes 140

6.7 XPath Predicates 142

6.8 Existential Semantics 147

6.9 Logical Expressions with and, or, not() 148

6.10 The Current Context and the Parent Step 151

6.11 Positional Predicates 153

6.12 Union and Construction of Sequences 154

6.13 XPath Functions 155

6.14 General and Value Comparisons 156

6.15 XPath Axes and Unabbreviated Syntax 157

6.16 Summary 157

Chapter 7 Querying

7.1 Overview of SQL/

7.2 Retrieving

7.2.1 Referencing

7.2.2 Retrieving Element Values Without

7.2.3 Retrieving Repeating Elements with

7.3 Retrieving

7.3.1 Generating Rows and Columns from

7.3.2 Dealing with Missing Elements 167

7.3.3 Avoiding Type Errors 168

7.3.4 Retrieving Repeating Elements with

7.3.5 Numbering

7.3.6 Retrieving Multiple Repeating Elements at Different Levels 174

7.4 Using XPath Predicates in SQL/

7.5 Common Mistakes with SQL/

7.6 Using Parameter Markers or Host Variables 183

7.7

7.8 Ordering a Query Result Set Based on

7.9 Converting

7.10 Summary 188

Chapter 8 Querying

8.1 XQuery Overview 190

8.2 Processing

8.2.1 Anatomy of a FLWOR Expression 191

8.2.2 Understanding the for and let Clauses 193

8.2.3 Understanding the where and order by Clauses 194

8.2.4 FLWOR Expressions with Multiple for and let Clauses 195

8.3 Comparing FLWOR Expressions, XPath Expressions, and SQL/

8.3.1 Traversing

8.3.2 Using

8.3.3 Result Set Cardinalities in XQuery and SQL/

8.3.4 Using FLWOR Expressions in SQL/

8.4 Constructing

8.4.1 Constructing Elements with Computed Values 202

8.4.2 Constructing

8.4.3 Constructing Documents with Multiple Levels of Nesting 206

8.4.4 Constructing Documents with

8.5 Data Types, Cast Expressions, and Type Errors 208

8.6 Arithmetic Expressions 212

8.7 XQuery Functions 214

8.7.1 String Functions 215

8.7.2 Number and Aggregation Functions 218

8.7.3 Sequence Functions 220

8.7.4 Namespace and Node Functions 222

8.7.5 Date and Time Functions 224

8.7.6 Boolean Functions 226

8.8 Embedding SQL in XQuery 227

8.9 Using SQL Functions and User-Defined Functions in XQuery 229

8.10 Summary 230

Chapter 9 Querying

9.1 Aggregation and Grouping of

9.1.1 Aggregation and Grouping Queries with

9.1.2 Aggregation of Values within and across

9.1.3 Grouping Queries in SQL/

9.2 Join Queries with

9.2.1 XQuery Joins between

9.2.2 SQL/

9.2.3 Joins between

9.2.4 Outer Joins between

9.3 Case-Insensitive

9.4 How to Avoid “Bad” Queries 253

9.4.1 Construction of Excessively Large Documents 253

9.4.2 “Between” Predicates on

9.4.3 Large Global Sequences 256

9.4.4 Multilevel Nesting SQL and XQuery 257

9.5 Common Errors and How to Avoid Them 258

9.5.1 SQL16001N 259

9.5.2 SQL16002N 259

9.5.3 SQL16003N 260

9.5.4 SQL16005N 261

9.5.5 SQL16015N 262

9.5.6 SQL16011N 263

9.5.7 SQL16061N 263

9.5.8 SQL16075N 264

9.6 Summary 264

Chapter 10 Producing

10.1 SQL/

10.1.1 Constructing

10.1.2 NULL Values, Missing Elements, and Empty Elements 274

10.1.3 Constructing

10.1.4 Constructing

10.1.5 Constructing

10.1.6 Comparing

10.1.7 Conditional Element Construction 284

10.1.8 Leading Zeros in Constructed Elements and Attributes 285

10.1.9 Default Tagging of Relational Data with

10.1.10 GUI-Based Definition of SQL/

10.1.11 Constructing Comments, Processing Instructions, and Text Nodes 290

10.1.12 Legacy Functions 290

10.2 Using XQuery Constructors with Relational Input 290

10.3

10.4 Inserting Constructed

10.5 Summary 295

Chapter 11 Converting

11.1 Advantages and Disadvantages of Shredding 297

11.2 Shredding with the

11.2.1 Hybrid

11.2.2 Relational Views over

11.3 Shredding with Annotated

11.3.1 Annotating an

11.3.2 Defining Schema Annotations Visually in IBM Data Studio 311

11.3.3 Registering an Annotated Schema 311

11.3.4 Decomposing One

11.3.5 Decomposing

11.4 Summary 318

Chapter 12 Updating and Transforming

12.1 Replacing a Full

12.2 Modifying Documents with XQuery Updates 324

12.3 Updating the Value of an

12.3.1 Replacing an Element Value 326

12.3.2 Replacing an Attribute Value 327

12.3.3 Replacing a Value Using a Parameter Marker 328

12.3.4 Replacing Multiple Values in a Document 328

12.3.5 Replacing an Existing Value with a Computed Value 329

12.4 Replacing

12.5 Deleting

12.6 Renaming Elements or Atttributes in a Document 334

12.7 Inserting

12.7.1 Defining the Position of Inserted Elements 335

12.7.2 Defining the Position of Inserted Attributes 336

12.7.3 Insert Examples 337

12.8 Handling Repeating and Missing Nodes 340

12.9 Modifying Multiple

12.9.1 Snapshot Semantics and Conflict Situations 343

12.9.2 Converting Elements to Attributes and Vice Versa 345

12.10 Modifying

12.11 Modifying

12.12 Modifying

12.13

12.14 Transforming

12.14.1 The XSLTRANSFORM Function 353

12.14.2

12.15 Summary 358

Chapter 13 Defining and Using

13.1 Defining

13.1.1 Unique

13.1.2 Lean

13.1.3 Using the DB2 Control Center to Create

13.2

13.2.1 VARCHAR(n) 367

13.2.2 VARCHAR HASHED 368

13.2.3 DOUBLE and DECFLOAT 369

13.2.4 DATE and TIMESTAMP 369

13.2.5 Choosing a Suitable Index Data Type 369

13.2.6 Rejecting Invalid Values 371

13.3 Using

13.3.1 Understanding Index Eligibility 373

13.3.2 Data Types in

13.3.3 Text Nodes in

13.3.4 Wildcards in

13.3.5 Using Indexes for Structural Predicates 377

13.4

13.5

13.6 Special Cases Where

13.6.1 Special Cases with

13.6.2 Parent Steps 385

13.6.3 The let and return Clauses 386

13.7

13.7.1

13.7.2 Logical and Physical

13.8

13.9 Summary 393

Chapter 14

14.1 Explaining

14.1.1 The Explain Tables in DB2 for Linux, UNIX, and Windows 396

14.1.2 Using db2exfmt to Obtain Access Plans 397

14.1.3 Using Visual Explain to Display Access Plans 400

14.1.4 Access Plan Operators 401

14.1.5 Understanding and Analyzing

14.2 Explaining

14.2.1 The Explain Tables in DB2 for z/OS 409

14.2.2 Obtaining Access Plan Information in SPUFI 410

14.2.3 Using Visual Explain to Display Access Plans 411

14.2.4 Access Plan Operators 413

14.2.5 Understanding and Analyzing

14.3 Statistics Collection for

14.3.1 Statistics Collection for

14.3.2 Statistics Collection for

14.3.3 Examining

14.4 Monitoring

14.4.1 Using the Snapshot Monitor in DB2 for Linux, UNIX, and Windows 424

14.4.2 Monitoring Database Utilities 427

14.5 Best Practices for

14.5.1

14.5.2

14.5.3

14.5.4

14.5.5

14.5.6

14.5.7

14.6 Summary 435

Chapter 15 Managing

15.1 Introduction to

15.1.1 Namespace Declarations in

15.1.2 Default Namespaces 442

15.2 Exploring Namespaces in

15.3 Querying

15.3.1 Declaring Namespaces in

15.3.2 Using Namespace Declarations in SQL/

15.3.3 Using Namespaces in the

15.3.4 Dealing with Multiple Namespaces per Document 454

15.4 Creating Indexes for

15.5 Constructing

15.5.1 SQL/

15.5.2 XQuery Constructors and Namespaces 462

15.6 Updating

15.6.1 Updating Values in Documents with Namespaces 464

15.6.2 Renaming Nodes in Documents with Namespace Prefixes 465

15.6.3 Renaming Nodes in Documents with Default Namespaces 467

15.6.4 Inserting and Replacing Nodes in Documents with Namespaces 468

15.7 Summary 469

Chapter 16 Managing

16.1 Introduction to

16.1.1 Valid Versus Well-Formed

16.1.2 To Validate or Not to Validate,That Is the Question! 474

16.1.3 Custom Versus Industry Standard

16.2 Anatomy of an

16.3 An

16.4 Registering

16.4.1 Registering

16.4.2 Registering

16.4.3 Registering

16.4.4 Two

16.4.5 Error Situations and How to Resolve Them 490

16.5 Removing

16.6

16.6.1 Schema Evolution Without Document Validation 494

16.6.2 Generic Schema Evolution with Document Validation 494

16.6.3 Compatible Schema Evolution with the UPDATE

16.7 Granting and Revoking

16.8 Document Type Definitions (DTDs) and External Entities 501

16.9 Browsing the

16.9.1 Tables and Views of the

16.9.2 Queries against the

16.10

16.11 Summary 512

Chapter 17 Validating

17.1 Document Validation Upon Insert 514

17.2 Document Validation Upon Update 518

17.3 Validation without Rejecting Invalid Documents 519

17.4 Enforcing Validation with Check Constraints 520

17.5 Automatic Validation with Triggers 523

17.6 Diagnosing Validation and Parsing Errors 525

17.7 Validation during Load and Import Operations 530

17.7.1 Validation against a Single

17.7.2 Validation against Multiple

17.7.3 Using a Default

17.7.4 Overriding

17.7.5 Validation Based on schemaLocation Attributes 534

17.8 Checking Whether an Existing Document Has Been Validated 534

17.9 Validating Existing Documents in a Table 535

17.10 Finding the

17.11 How to Undo Document Validation 540

17.12 Considerations for Validation in DB2 for z/OS 540

17.12.1 Document Validation Upon Insert 541

17.12.2 Document Validation Upon Update 542

17.12.3 Validating Existing Documents in a Table 543

17.12.4 Summary of Platform Similarities and Differences 543

17.13 Summary 544

Chapter 18 Using

18.1 Manipulating

18.1.1 Basic

18.1.2 A Stored Procedure to Store

18.1.3 Loops and Cursors 553

18.1.4 A Stored Procedure to Update a Selected

18.1.5 Three Tips for Testing Stored Procedures 555

18.2 Manipulating

18.2.1 A UDF to Extract an Element or Attribute Value 557

18.2.2 A UDF to Extract the Values of a Repeating Element 557

18.2.3 A UDF to Shred

18.2.4 A UDF to Modify an

18.3 Manipulating

18.3.1 Insert Triggers on Tables with

18.3.2 Delete Triggers on Tables with

18.3.3 Update Triggers on

18.4 Summary 564

Chapter 19 Performing Full-Text Search 567

19.1 Overview of Text Search in DB2 568

19.2 Sample Table and Data 570

19.3 Enabling a Database for the DB2 Net Search Extender 571

19.4 Managing Full-Text Indexes with the DB2 Net Search Extender 572

19.4.1 Creating Basic Text Indexes 572

19.4.2 Creating Text Indexes with Specific Storage Paths 573

19.4.3 Creating Text Indexes with a Periodic Update Schedule 574

19.4.4 Creating Text Indexes for Specific Parts of Each Document 576

19.4.5 Creating Text Indexes with Advanced Options 578

19.4.6 Updating and Reorganizing Text Indexes 579

19.4.7 Altering Text Indexes 580

19.5 Performing

19.5.1 Full-Text Search in SQL and XQuery 581

19.5.2 Full-Text Search with Boolean Operators 583

19.5.3 Full-Text Search with Custom Document Models 585

19.5.4 Advanced Search with Proximity, Fuzzy, and Stemming Options 586

19.5.5 Finding the Correct Match within an

19.5.6 Search Conditions on Sibling Branches of an

19.5.7 Text Search in the Presence of Namespaces 588

19.6 DB2 Text Search 590

19.6.1 Enabling a Database for DB2 Text Search 590

19.6.2 Creating and Maintaining Full-Text Indexes for DB2 Text Search 591

19.6.3 Writing DB2 Text Search Queries for

19.6.4 Full-Text Search with XPath Expressions 593

19.6.5 Full-Text Search with Wildcards 594

19.7 Summary of Text Search Administration Commands 594

19.8

19.9 Summary 596

Chapter 20 Understanding

20.1 Understanding Internal and External

20.1.1 Internally Encoded

20.1.2 Externally Encoded

20.2 Avoiding Code Page Conversions 601

20.3 Using Non-Unicode Databases for

20.4 Examples of Code Page Issues 602

20.4.1 Example 1: Chinese Characters in a Non-Unicode Code Page ISO-8859-1 602

20.4.2 Example 2: Fetching Data from a Non-Unicode Code Database into a Character Type Application Variable 603

20.4.3 Example 3: Encoding Issues with

20.4.4 Example 4: Japanese Literal Values in a Non-Unicode Database 605

20.4.5 Example 5: Data Expansion and Shrinkage Due to Code Page Conversion 605

20.5 Avoiding Data Loss and Encoding Errors in Non-Unicode Databases 606

20.6 Summary 606

Chapter 21 Developing

21.1 The Value of DB2 pure

21.1.1 Avoid

21.1.2 Storing Business Objects in an Intuitive Format 612

21.1.3 Rapid Prototyping 612

21.1.4 Responding Quickly to Changing Business Needs 613

21.2 Using Parameter Markers or Host Variables 613

21.3 Java Applications 615

21.3.1

21.3.2

21.3.3 Comprehensive Example of Manipulating

21.3.4 Creating

21.3.5 Binding

21.3.6 IBM pureQuery 629

21.4 .NET Applications 631

21.4.1 Querying

21.4.2 Manipulating

21.4.3 Inserting

21.4.4

21.5 CLI Applications 636

21.6 Embedded SQL Applications 639

21.6.1 COBOL Applications with Embedded SQL 640

21.6.2 PL/1 Applications with Embedded SQL 643

21.6.3 C Applications with Embedded SQL 645

21.7 PHP Applications 647

21.8 Perl Applications 650

21.9

21.9.1 IBM Data Studio Developer 652

21.9.2 IBM Database Add-ins for Visual Studio 656

21.9.3 Altova

21.9.4 &#lt;oXygen/&#gt; 658

21.9.5 Stylus Studio 659

21.10 Summary 659

Chapter 22 Exploring

22.1

22.1.1 Catalog Information for

22.1.2 The

22.1.3 The Internal

22.1.4 Catalog Information for User-Defined

22.1.5 Catalog Information for

22.2

22.2.1 Catalog Information for

22.2.2 Catalog Information for

22.2.3 Catalog Information for

22.3 Summary 673

Chapter 23 Test Your Knowledge–The DB2 pure

23.1 Designing

23.2 Designing and Managing Storage Objects for

23.3 Inserting and Retrieving

23.4 Moving

23.5 Querying

23.6 Producing

23.7 Converting

23.8 Updating and Transforming

23.9 Defining and Using

23.10

23.11 Managing

23.12

23.13 Performing Full-Text Search 696

23.14

23.15 Answers 700

Appendix A Getting Started with DB2 pure

A.1 Exploring the Structure of

A.1.1 Exploring

A.1.2 Exploring

A.1.3 Exploring

A.2 Tips for Running

Appendix B The

B.1

B.2

B.3 Table customer–Column info 710

B.4 Table product–Column description 712

B.5 Table purchaseorder–Column porder 713

Appendix C Further Reading 717

C.1 General Resources for All Chapters 717

C.2 Chapter-Specific Resources 718

C.3 Resources on the Integration of DB2 pure

Index 727