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

DATA for XML 69 3.13 XML Parsing and Memory Consumption in DB2 for z/OS 71 3.13.1 Controlling the Memory Consumption of XML Operations 71 3.13.2 Redirecting XML Parsing to zIIP and zAAP 72 3.14 Summary 73 Chapter 4 Inserting and Retrieving XML Data 75 4.1 Inserting XML Documents 76 4.1.1 Simple Insert Statements 76 4.1.2 Reading XML Documents from Files or URLs 79 4.2 Deleting XML Documents 82 4.3 Retrieving XML Documents 83 4.4 Handling Documents with XML Declarations 85 4.5 Copying Full XML Documents 86 4.6 Dealing with XML Special Characters 87 4.7 Understanding XML Whitespace and Document Storage 89 4.7.1 Preserving XML Whitespace 91 4.7.2 Changing the Whitespace Default from "Strip" to "Preserve" 93 4.7.3 Storing XML Documents for Compliance 94 4.8 Summary 95 Chapter 5 Moving XML Data 97 5.1 Exporting XML Data in DB2 for Linux, UNIX, and Windows 98 5.1.1 Exporting XML Documents to a Single File 98 5.1.2 Exporting XML Documents as Individual Files 100 5.1.3 Exporting XML Documents as Individual Files with Non-Default Names 102 5.1.4 Exporting XML Documents to One or Multiple Dedicated Directories 102 5.1.5 Exporting Fragments of XML Documents 104 5.1.6 Exporting XML Data with XML Schema Information 105 5.2 Importing XML Data in DB2 for Linux, UNIX, and Windows 106 5.2.1 IMPORT Command and Input Files 107 5.2.2 Import/Insert Performance Tips 108 5.3 Loading XML Data in DB2 for Linux, UNIX, and Windows 109 5.4 Unloading XML Data in DB2 for z/OS 111 5.5 Loading XML Data in DB2 for z/OS 114 5.6 Validating XML Documents during Load and Insert Operations 116 5.7 Splitting Large XML Documents into Smaller Documents 116 5.8 Replicating and Publishing XML Data 118 5.9 Federating XML Data 120 5.10 Managing XML Data with HADR 121 5.11 Handling XML Data in db2look and db2move 122 5.12 Summary 123 Chapter 6 Querying XML Data: Introduction and XPath 125 6.1 An Overview of Querying XML Data 126 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/XML, and XQuery 131 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 XML Data with SQL/XML 159 7.1 Overview of SQL/XML 160 7.2 Retrieving XML Documents or Document Fragments with XMLQUERY 161 7.2.1 Referencing XML Columns in SQL/XML Functions 162 7.2.2 Retrieving Element Values Without XML Tags 163 7.2.3 Retrieving Repeating Elements with XMLQUERY 164 7.3 Retrieving XML Values in Relational Format with XMLTABLE 165 7.3.1 Generating Rows and Columns from XML Data 165 7.3.2 Dealing with Missing Elements 167 7.3.3 Avoiding Type Errors 168 7.3.4 Retrieving Repeating Elements with XMLTABLE 169 7.3.5 Numbering XMLTABLE Rows Based on Repeating Elements 173 7.3.6 Retrieving Multiple Repeating Elements at Different Levels 174 7.4 Using XPath Predicates in SQL/XML with XMLEXISTS 177 7.5 Common Mistakes with SQL/XML Predicates 181 7.6 Using Parameter Markers or Host Variables 183 7.7 XML Queries with Dynamically Computed XPath Expressions 185 7.8 Ordering a Query Result Set Based on XML Values 186 7.9 Converting XML Values to Binary SQL Types 187 7.10 Summary 188 Chapter 8 Querying XML Data with XQuery 189 8.1 XQuery Overview 190 8.2 Processing XML Data with FLWOR Expressions 191 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/XML 197 8.3.1 Traversing XML Documents 197 8.3.2 Using XML Predicates 198 8.3.3 Result Set Cardinalities in XQuery and SQL/XML 200 8.3.4 Using FLWOR Expressions in SQL/XML 201 8.4 Constructing XML Data 202 8.4.1 Constructing Elements with Computed Values 202 8.4.2 Constructing XML Data with Predicates and Conditions 204 8.4.3 Constructing Documents with Multiple Levels of Nesting 206 8.4.4 Constructing Documents with XML Aggregation in SQL/XML Queries 207 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 XML Data:Advanced Queries & Troubleshooting 233 9.1 Aggregation and Grouping of XML Data 233 9.1.1 Aggregation and Grouping Queries with XMLTABLE 234 9.1.2 Aggregation of Values within and across XML Documents 236 9.1.3 Grouping Queries in SQL/XML versus XQuery 237 9.2 Join Queries with XML Data 239 9.2.1 XQuery Joins between XML Columns 240 9.2.2 SQL/XML Joins between XML Columns 242 9.2.3 Joins between XML and Relational Columns 248 9.2.4 Outer Joins between XML Columns 250 9.3 Case-Insensitive XML Queries 252 9.4 How to Avoid "Bad" Queries 253 9.4.1 Construction of Excessively Large Documents 253 9.4.2 "Between" Predicates on XML Data 254 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 XML from Relational Data 267 10.1 SQL/XML Publishing Functions 268 10.1.1 Constructing XML Elements from Relational Data 269 10.1.2 NULL Values, Missing Elements, and Empty Elements 274 10.1.3 Constructing XML Attributes from Relational Data 275 10.1.4 Constructing XML Documents from Multiple Relational Rows 277 10.1.5 Constructing XML Documents from Multiple Relational Tables 281 10.1.6 Comparing XMLAGG,XMLCONCAT, and XMLFOREST 284 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 XMLROW and XMLGROUP 286 10.1.10 GUI-Based Definition of SQL/XML Publishing Queries 289 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 XML Declarations for Constructed XML Data 292 10.4 Inserting Constructed XML Data into XML Columns 294 10.5 Summary 295 Chapter 11 Converting XML to Relational Data 297 11.1 Advantages and Disadvantages of Shredding 297 11.2 Shredding with the XMLTABLE Function 301 11.2.1 Hybrid XML Storage 303 11.2.2 Relational Views over XML Data 305 11.3 Shredding with Annotated XML Schemas 306 11.3.1 Annotating an XML Schema 306 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 XML Document at a Time 312 11.3.5 Decomposing XML Documents in Bulk 315 11.4 Summary 318 Chapter 12 Updating and Transforming XML Documents 321 12.1 Replacing a Full XML Document 322 12.2 Modifying Documents with XQuery Updates 324 12.3 Updating the Value of an XML Node in a Document 326 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 XML Nodes in a Document 331 12.5 Deleting XML Nodes from a Document 333 12.6 Renaming Elements or Atttributes in a Document 334 12.7 Inserting XML Nodes into a Document 335 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 XML Nodes in the Same Document 343 12.9.1 Snapshot Semantics and Conflict Situations 343 12.9.2 Converting Elements to Attributes and Vice Versa 345 12.10 Modifying XML Documents in Queries 346 12.11 Modifying XML Documents in Insert Operations 349 12.12 Modifying XML Documents in Update Cursors 350 12.13 XML Updates in DB2 for z/OS 351 12.14 Transforming XML Documents with XSLT 352 12.14.1 The XSLTRANSFORM Function 353 12.14.2 XML to HTML Transformation 356 12.15 Summary 358 Chapter 13 Defining and Using XML Indexes 361 13.1 Defining XML Indexes 362 13.1.1 Unique XML Indexes 364 13.1.2 Lean XML Indexes 365 13.1.3 Using the DB2 Control Center to Create XML Indexes 366 13.2 XML Index Data Types 367 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 XML Indexes to Evaluate Query Predicates 373 13.3.1 Understanding Index Eligibility 373 13.3.2 Data Types in XML Indexes and Query Predicates 374 13.3.3 Text Nodes in XML Indexes and Query Predicates 375 13.3.4 Wildcards in XML Indexes and Query Predicates 376 13.3.5 Using Indexes for Structural Predicates 377 13.4 XML Indexes and Join Predicates 379 13.5 XML Indexes on Non-Leaf Elements 383 13.6 Special Cases Where XML Indexes Cannot be Used 385 13.6.1 Special Cases with XMLQUERY 385 13.6.2 Parent Steps 385 13.6.3 The let and return Clauses 386 13.7 XML Index Internals 387 13.7.1 XML Index Keys 387 13.7.2 Logical and Physical XML Indexes 389 13.8 XML Index Statistics 390 13.9 Summary 393 Chapter 14 XML Performance and Monitoring 395 14.1 Explaining XML Queries in DB2 for Linux,UNIX, and Windows 396 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 XML Query Execution Plans 403 14.2 Explaining XML Queries in DB2 for z/OS 409 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 XML Query Execution Plans 414 14.3 Statistics Collection for XML Data 417 14.3.1 Statistics Collection for XML Data in DB2 for z/OS 417 14.3.2 Statistics Collection for XML Data in DB2 for Linux, UNIX, and Windows 418 14.3.3 Examining XML Statistics with db2cat 419 14.4 Monitoring XML Activity 424 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 XML Performance 428 14.5.1 XML Document Design 428 14.5.2 XML Storage 429 14.5.3 XML Queries 430 14.5.4 XML Indexes 432 14.5.5 XML Updates 433 14.5.6 XML Schemas 434 14.5.7 XML Applications 434 14.6 Summary 435 Chapter 15 Managing XML Data with Namespaces 437 15.1 Introduction to XML Namespaces 437 15.1.1 Namespace Declarations in XML Documents 439 15.1.2 Default Namespaces 442 15.2 Exploring Namespaces in XML Documents 444 15.3 Querying XML Data with Namespaces 447 15.3.1 Declaring Namespaces in XML Queries 448 15.3.2 Using Namespace Declarations in SQL/XML Queries 451 15.3.3 Using Namespaces in the XMLTABLE Function 452 15.3.4 Dealing with Multiple Namespaces per Document 454 15.4 Creating Indexes for XML Data with Namespaces 456 15.5 Constructing XML Data with Namespaces 460 15.5.1 SQL/XML Publishing Functions and Namespaces 460 15.5.2 XQuery Constructors and Namespaces 462 15.6 Updating XML Data with Namespaces 463 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 XML Schemas 471 16.1 Introduction to XML Schemas and Their Usage 472 16.1.1 Valid Versus Well-Formed XML Documents 473 16.1.2 To Validate or Not to Validate,That Is the Question! 474 16.1.3 Custom Versus Industry Standard XML Schemas 474 16.2 Anatomy of an XML Schema 476 16.3 An XML Schema with Include and Import 479 16.4 Registering XML Schemas 483 16.4.1 Registering XML Schemas in the DB2 Command Line Processor 484 16.4.2 Registering XML Schemas from Applications via Stored Procedures 486 16.4.3 Registering XML Schemas from Java Applications via JDBC 488 16.4.4 Two XML Schemas Sharing a Common Schema Document 489 16.4.5 Error Situations and How to Resolve Them 490 16.5 Removing XML Schemas from the Schema Repository 492 16.6 XML Schema Evolution 493 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 XMLSCHEMA Command 495 16.7 Granting and Revoking XML Schema Usage Privileges 499 16.8 Document Type Definitions (DTDs) and External Entities 501 16.9 Browsing the XML Schema Repository (XSR) 502 16.9.1 Tables and Views of the XML Schema Repository 503 16.9.2 Queries against the XML Schema Repository 508 16.10 XML Schema Considerations in DB2 for z/OS 510 16.11 Summary 512 Chapter 17 Validating XML Documents against XML Schemas 513 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 XML Schema 530 17.7.2 Validation against Multiple XML Schemas 531 17.7.3 Using a Default XML Schema 532 17.7.4 Overriding XML Schema References 532 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 XML Schema for a Validated Document 538 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 XML in Stored Procedures, UDFs, and Triggers 547 18.1 Manipulating XML in SQL Stored Procedures 548 18.1.1 Basic XML Manipulation in Stored Procedures 548 18.1.2 A Stored Procedure to Store XML in a Hybrid Manner 550 18.1.3 Loops and Cursors 553 18.1.4 A Stored Procedure to Update a Selected XML Element or Attribute 554 18.1.5 Three Tips for Testing Stored Procedures 555 18.2 Manipulating XML in User-Defined Functions 556 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 XML Data to a Relational Table 558 18.2.4 A UDF to Modify an XML Document 559 18.3 Manipulating XML Data with Triggers 561 18.3.1 Insert Triggers on Tables with XML Columns 562 18.3.2 Delete Triggers on Tables with XML Columns 563 18.3.3 Update Triggers on XML Columns 564 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 XML Full-Text Search with the DB2 Net Search Extender 581 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 XML Document 587 19.5.6 Search Conditions on Sibling Branches of an XML Document 588 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 XML Data 592 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 XML Full-Text Search in DB2 for z/OS 596 19.9 Summary 596 Chapter 20 Understanding XML Data Encoding 597 20.1 Understanding Internal and External XML Encoding 599 20.1.1 Internally Encoded XML Data 599 20.1.2 Externally Encoded XML Data 600 20.2 Avoiding Code Page Conversions 601 20.3 Using Non-Unicode Databases for XML 601 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 XMLTABLE and XMLCAST 604 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 XML Applications with DB2 609 21.1 The Value of DB2 pureXML for Application Development 610 21.1.1 Avoid XML Parsing in the Application Layer 610 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 XML Support in JDBC 3.0 615 21.3.2 XML Support in JDBC 4.0 619 21.3.3 Comprehensive Example of Manipulating XML Data with JDBC 4.0 621 21.3.4 Creating XML Documents from Application Data 627 21.3.5 Binding XML Data to Java Objects 629 21.3.6 IBM pureQuery 629 21.4 .NET Applications 631 21.4.1 Querying XML Data in .NET Applications 632 21.4.2 Manipulating XML Data in .NET Applications 633 21.4.3 Inserting XML Data from .NET Applications 635 21.4.4 XML Schema and DTD Handling in .NET Applications 636 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 XML Application Development Tools 651 21.9.1 IBM Data Studio Developer 652 21.9.2 IBM Database Add-ins for Visual Studio 656 21.9.3 Altova XML Tools 656 21.9.4 <oXygen/> 658 21.9.5 Stylus Studio 659 21.10 Summary 659 Chapter 22 Exploring XML Information in the DB2 Catalog 661 22.1 XML-Related Catalog Information in DB2 for Linux, UNIX, and Windows 661 22.1.1 Catalog Information for XML Columns 661 22.1.2 The XML Strings and Paths Tables 662 22.1.3 The Internal XML Regions and Path Indexes 663 22.1.4 Catalog Information for User-Defined XML Indexes 664 22.1.5 Catalog Information for XML Schemas 667 22.2 XML-Related Catalog Information in DB2 for z/OS 667 22.2.1 Catalog Information for XML Storage Objects 667 22.2.2 Catalog Information for XML Indexes 671 22.2.3 Catalog Information for XML Schemas 672 22.3 Summary 673 Chapter 23 Test Your Knowledge-The DB2 pureXML Quiz 675 23.1 Designing XML Data and Applications 675 23.2 Designing and Managing Storage Objects for XML 677 23.3 Inserting and Retrieving XML Data 680 23.4 Moving XML Data 681 23.5 Querying XML 682 23.6 Producing XML from Relational Data 686 23.7 Converting XML to Relational Data 687 23.8 Updating and Transforming XML Documents 688 23.9 Defining and Using XML Indexes 689 23.10 XML Performance and Monitoring 692 23.11 Managing XML Data with Namespaces 693 23.12 XML Schemas and Validation 694 23.13 Performing Full-Text Search 696 23.14 XML Application Development 697 23.15 Answers 700 Appendix A Getting Started with DB2 pureXML 703 A.1 Exploring the Structure of XML Documents 703 A.1.1 Exploring XML Documents in the DB2 Control Center 703 A.1.2 Exploring XML Documents in the CLP 704 A.1.3 Exploring XML Documents in SPUFI 705 A.2 Tips for Running XML Operations in the CLP 706 Appendix B The XML Sample Database 709 B.1 XML Sample Database on DB2 for Linux, UNIX, and Windows 709 B.2 XML Sample Tables on DB2 for z/OS 710 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 pureXML with Other Products 726 Index 727