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

Description

• Copyright 2010
• Pages: 792
• Edition: 1st

• eBook
• ISBN-10: 0-13-703583-7
• ISBN-13: 978-0-13-703583-0

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



Sample Content

Table of Contents

Chapter 1 Introduction 1

1.1 Anatomy of an XML Document 2

1.2 Differences Between XML and Relational Data 4

1.3 Overview of DB2 pureXML 7

1.4 Benefits of DB2 pureXML over Alternative Storage Options for XML Data 10

1.5 XML Solutions to Relational Data Model Problems 11

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 XML Data and Applications 15

2.1 Choosing Between XML Elements and XML Attributes 15

2.2 XML Tags versus Values 19

2.3 Choosing the Right Document Granularity 22

2.4 Using a Hybrid XML/Relational Approach 24

2.5 Summary 25

Chapter 3 Designing and Managing XML Storage Objects 27

3.1 Understanding XML Document Trees 28

3.2 Understanding pureXML Storage 30

3.3 XML Storage in DB2 for Linux, UNIX, and Windows 33

3.3.1 Storage Objects for XML Data 33

3.3.2 Defining Columns,Tables, and Table Spaces for XML Data 36

3.3.3 Dropping XML Columns 40

3.3.4 Improved XML Storage Format in DB2 9.7 40

3.4 Using XML Base Table Row Storage (Inlining) 41

3.4.1 Monitoring and Configuring XML Inlining 43

3.4.2 Potential Benefits and Drawbacks of XML Inlining 47

3.5 Compressing XML Data 48

3.6 Examining XML Storage Space Consumption 51

3.7 Reorganizing XML Data and Indexes 53

3.8 Understanding XML Space Management: A Comprehensive Example 54

3.9 XML in Range Partitioned Tables and MDC Tables 57

3.9.1 XML and Range Partitioning 57

3.9.2 XML and Multidimensional Clustering 58

3.10 XML in a Partitioned Database (DPF) 59

3.11 XML Storage in DB2 for z/OS 60

3.11.1 Storage Objects for XML Data 61

3.11.2 Characteristics of XML Table Spaces 63

3.11.3 Tables with Multiple XML Columns 64

3.11.4 Naming and Storage Conventions 64

3.12 Utilities for XML Objects in DB2 for z/OS 65

3.12.1 REPORT TABLESPACESET for XML 67

3.12.2 Reorganizing XML Data in DB2 for z/OS 68

3.12.3 CHECK 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



Updates

Submit Errata

