IBM®
Skip to main content
    Country/region [select]      Terms of use
 
 
    
     Home      Products      Services & solutions      Support & downloads      My account     
 
developerworks > My developerWorks >  Dashboard > DB2 XML > ... > BootCamps > BootCampsNY
developerWorks
Log In   View a printable version of the current page.
Overview Connect Spaces Forums Wikis
BootCampsNY
Browse space Browse Space    
Added by db2xml42, last edited by db2xml42 on Jan 28, 2010  (view change)
Labels: 
(None)

Some Bootcamps in the US North East and in Bangalore

Many other bootcamps have taken place - even in the New York and US North East area. If you are interested in having a bootcamp in your area, please contact Susan Malaika

Photos from the NYC pureXML BootCamp in October 2008







Questions and Answers from the Bootcamp in February 2009

Handy Reference

  • Question: Some of you asked about handy pureXML references
  • Reply: Google pureXML Wiki

Bootcamp scripts

  • Question: Some of you asked about the bootcamp scripts
  • Reply: We are looking into whether we can make them available - in the meantime here is the labs document you used
  • Reply updated: Here are the bootcamp scripts carefully prepared for you by your favorite instructors - Hurrah!

INLINE LENGTH

  • Question: One or more of you asked about INLINE LENGTH usage
  • Reply: INLINE LENGTH is a single parameter - so if you say INLINE, you say LENGTH

NOT NULL in column definitions used in an XMLTABLE

  • Question: One of you asked about having NOT NULL in the column definitions in an XMLTABLE
  • Reply: No you cannot specify NOT NULL, but instead you can have a DEFAULT clause. If an empty sequence is returned and no default-clause was specified, a null value is assigned to the column.

Java and pureXML

  • Question: One or more of you are working with Java ...
  • Reply: We have a number of articles on using Java with pureXML -the latest is here - Work with GPX XML in DB2 9.5 using JDBC. The article is accompanied by sample code. Note we will be putting out an update to the sample code because it is using a deprecated class.

HADR and pureXML

  • Question: One or more of you asked about the intersection of HADR and pureXML
  • Reply: HADR fully supports XML because XML is always logged and hence always replicated to the standby node of a HADR pair.

Indexes with REJECT constraint

  • Question: One or more of you asked about indexes wth REJECT INVALID VALUES
  • Reply: See http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.xml.doc/doc/c0050602.html
    • In summary :
      • For Index CREATE with REJECT INVALID VALUES and there is existing data:
        • (1) if the datatype of existing data in the path are all compatible with the constraint (a missing path is compatible) the index creation will proceed and the existing data will be indexed
        • (2) if the datatype of any existing data in the path is incompatible with the constraint, index creation will get an error
      • For Index CREATE with REJECT INVALID VALUES and then an insertion or update is performed, the insertion or update of the row itself will be rejected if any of the data in the path are datatype-incompatible with the constraint

VARCHAR hashed collisions

  • Question: One or more of you asked about collisions with VARCHAR hashed
  • Reply: The index is used to get all matching rows and then DB2 checks which rows actually match the search string

Best Practices Managing XML Data (and the examples are derivatives)

  • Question: Two or more of you are working with derivatives ....
  • Reply: Here is an excellent document that provides information on Best Practices with pureXML and the examples are FpML (derivatives) - Best Practices Managing XML Data

pureXML performance including compression

Using XML as a parameter to an SP

  • Question: Does XML have to be well formed in a Stored Procedure parameter? Does it get parsed then? or only when inserted to DB2?
  • Answer - Yes the document must be well formed on input to the SP with parameter of type XML.
    • A typical SP call with XML input data looks like:
      • CALL foo(XMLPARSE(DOCUMENT '<test><a>1234</a></test>')); the XML document must be well-formed (the XMLPARSE is forcing the check)
    • This call would not work:
      • CALL foo('<test><a>1234</a></test>'); since the input value will be seen by DB2 as of type VARCHAR.

Choosing between SQL/XML and XQuery

Contrasting XQuery Update in Memory with XQuery Update on Disk

Index Eligibility Question

  • Question: Some of you asked about index eligibility where there is a predicate at a higher level
  • Reply: The index is eligible - see example:
db2 => create table testabc(xmldoc xml)
DB20000I  The SQL command completed successfully.

db2 => create index testabc_idx on testabc(xmldoc) generate key using xmlpattern '/a/b/c' as sql varchar(10)
DB20000I  The SQL command completed successfully.

db2 => insert into testabc values ('<a><b name="fred"><c>test</c></b></a>')
DB20000I  The SQL command completed successfully.

db2 => set current explain mode explain
DB20000I  The SQL command completed successfully.

db2 => select * from testabc where xmlexists('$XMLDOC/a/b[@name="fred"]/c[. = "test"]')

Optimizer Plan:

                                       Rows
                                     Operator
                                       (ID)
                                       Cost

                                     0.0001
                                     RETURN
                                      ( 1)
                                     53.0664
                                       |
                                     0.0001
                                     NLJOIN
                                      ( 2)
                                     53.0664
                                    /       \-\
                             0.01               0.01
                            FETCH               XSCAN
                             (--)               ( 7)
                           0.107778            52.9586
                          /        \
                  0.01             1
                 RIDSCN    Table:
                  ( 4)     MARTIN 
                0.0317687  TESTABC
                   |
                  0.01
                  SORT
                  ( 5)
                0.0312672
                   |
                  0.01
                 XISCAN
                  ( 6)
                0.0302138
               /         \
         0                   1
 Index:              Table:
 MARTIN              MARTIN 
 TESTABC_IDX         TESTABC

Photos from the NYC pureXML BootCamp in February 2009








Questions and Answers from the Boston Bootcamp in April 2009

Encryption with XML on DB2 zOS

  • Question: Can edit procs be used for XML encryption with DB2 zOS?
  • Answer: Not at the moment. The topic is being investigated.

XQuery on DB2 zOS

  • Question: Will DB2 zOS support more XML features?
  • Answer: The future direction of DB2 zOS is to support more XML features

Elements and attributes

  • Question: When do I use elements? When do I use attributes?
  • Answer: You can find some advice here : http://xml.coverpages.org/elementsAndAttrs.html - The advice can be summarised by the editor's note: John Cowan provides an excellent summary|, ending with Michael Kay says: Beginners always ask this question. Those with a little experience express their opinions passionately. Experts tell you there is no right answer

Default values for XML

  • Question: Can default values be provided for missing XML content?
  • Answer: Yes, one way is to use XML Schema validation to set the defaults.

XML Declaration

  • Question: Is the xml declaration <?xml version="1.0"?> required for well-formedness?
  • Answer: No, see http://www.w3.org/TR/REC-xml for full definition of well formed XML.

Reorg

  • Question: When do I reorg XML?
  • Answer: If you delete a lot of XML, you may need to reorg. Otherwise it is not usually necessary to reorgXML. See also this forum entry

XML compression performance

  • Question: What is the impact of performance of XML compression in DB2 9.7?
  • Answer: In general, the positive impact of (a) reducing I/O, and (b) holding more data in the Buffer Pool is much more significant than the extra CPU cycles to compress/decompress - Unless your system is already 100% CPU bound and I/O is not an issue. See also Taming a Terabyte of XML Data
    • From "Buffer Pool Performance" Adjusted by DB2's self-tuning memory manager, the combined size of all buffer pools reached 46 GB (out of 64GB physically memory). Since the database size after compression was about 440GB, the ratio between buffer pools and database size is 10.5 percent (46GB/440GB). Figure 5 shows that the buffer pools for custacc and order indexes had a hit ratios between 95 and 100 percent. The hit ratio for the custacc and order tables was between 60 percent and 70 percent. Without DB2's compression, this hit ratio would have been lower and performance would have been worse.
    • From "Lessons Learnt": DB2 compression was critical. Without compression more disks and more memory would have been required to achieve the same performance. Compression reduced the required I/O, which is a benefit that far outweighed the extra CPU cycles to compress and decompress data.

XML schema versions

  • Question: How can I work with 10 versions of the ACORD schema and associated instances concurrently? ACORD instances do contain the version in an attribute
  • Answer: Of course you can store all versions of the instances in one column. For automating the cross refercing of the version, you can create a table with the SQLID for the 10 ACORD schemas registered to DB2, associating each one with the appropriate version in the attribute in the instance document. You can then write a general purpose trigger that does the XML validation consulting the version association table - or you can hard code the version associations in the trigger.

XML schema validation

  • Is there a limit on the size of the document that can be validated?
  • We are not aware of a limit

pureXML application

Publishing a table as XML

  • Question: Can I customize the XMLROW function that publishes a table as XML?
  • Answer: Yes - see the documentation to customize the element and attribute names

Bello

pureXML mastery test

  • Question: How do I take the pureXML mastery test in class?
  • Answer:
    • Have your computers at this url: http://ibt.prometric.com/ibm. Student logs into Prometric. If they do not have a Prometric id, they will need to register first - the category of id is IBM Information Management Assessments Tests.
    • Under Candidate/Test Delivery menu, student chooses Take Test
    • In the Private Test box, Proctor enters the appropriate key code
    • Proctor chooses Take this test & will be asked to sign in with their Prometric id and password plus the appropriate location for the test
    • Once that has been accepted, Proctor will be asked to enter a code for the test. The test is ready.

Photos from the Boston pureXML BootCamp in April 2009

We had two extra special guests at the bootcamp:






The pureXML BootCamp in August 2009 at Palisades

The pureXML Cookbook arrived in time for the Palisades pureXML Bootcamp - Hurrah!!


Questions and Answers from the Palisades Bootcamp in August 2009

Bootcamp Scripts

  • Question: Some of you asked about the bootcamp scripts?
  • Reply: You can find the scripts and labs from an earlier bootcamp here

XML for Data Exchange Slides

  • Question: Where can I find the additional slides shown in the first presentation of the bootcamp?
  • Answer: You can find them here along with a recording

XML Technologies on zOS Slides

  • Question: Where can I find the presentation on XML Technologies for zOS that was given on day 1 of the bootcamp?
  • Answer: You can find it here

Runstats on DB2 zOS Slides

  • Question: Where can I find the runstats presentation given on day 1 of the bootcamp?
  • Answer: You can find it here

DB2 pureXML zOS Performance Slides

  • Question: Where can I find the DB2 pureXML zOS performance presentation given on day 2 of the bootcamp?
  • Answer: You can find it here

Data Power and DB2 Slides

  • Question: Where can I find the Data Power and DB2 presentation given on day 3 of the bootcamp?
  • Answer: You can find it here

Hints and Tips for Performance Slides

  • Question: Which hints and tips presented on day 3 of the bootcamp apply to DB2 zOS?
  • Answer: Tips 2-5 apply to DB2 UNIX/Windows 
    Tip 1: Choose your XML document granularity wisely
Tip 2: Use DMS and larger pages for better for XML performance (LUW)
Tip 3: Exploit storage options for XML: inlined, compressed, or a separate tablespace (LUW)
Tip 4: How to configure DB2 for fast bulk inserting of XML data (LUW)
Tip 5: Use the new snapshot monitor elements to examine XML performance  (LUW)
Tip 6: Be aware of XML schema validation overhead 
Tip 7: In XPath expressions, use fully specified paths as much as possible 
Tip 8: Define lean XML indexes, and avoid indexing everything 
Tip 9: Put document filtering predicates in XMLEXISTS instead of XMLQUERY 
Tip 10: Use square brackets [ ] to avoid Boolean predicates in XMLEXISTS 
Tip 11: Use RUNSTATS to collects statistics for XML data and indexes 
Tip 12: Use SQL/XML publishing views to expose relational data as XML 
Tip 13: Use XMLTABLE views to expose XML data in relational format 
Tip 14: For short queries or OLTP applications, use SQL/XML statements with parameter markers 
Tip 15: Avoid code page conversion during XML insert and retrieval

Articles on DB2 pureXML on zOS

Learning about XML

Cobol and pureXML

Having Trouble with my XML - Binary Zeroes - Watch Out!

  • Question: I'm having trouble with my XML - getting parsing errors. I can't see what's wrong. Help!
  • Answer: Look out for binary zeoes. XML that contains a character that is all zeroes is not well formed XML. Look out for the Cobol INITIALIZE verb in particular which assigns binary zeroes.

XML Toolkit for zOS

XML Technologies on zOS

Red Book on XML Technologies on zOS

  • Question: When will the Red Book on XML Technologies on zOS become available?
  • Answer: A first draft is expected in the beginning of September 2009 on the Internet

Mashup Center

XPath Tool

  • Question: What was the XPath tool you showed us?
  • Answer: It was a Firefox addon called XPather - which can be used to try out XPaths

Whitespace

  • Question: Why would I preserve whitespace in XML?
  • Answer: The most common reason is to preserve the format and layout of the XML, e.g., to preserve line endings, to maintain the layout of comments in the XML so the XML is human readable in a variety tools. For example when you go into the Windows notepad editor (a tool that is not sensitive to XML) to view the XML, the XML and any comments will appear on one long line if you use strip whitespace. In an XML sensitive tool such as a Web Browser, the XML is laid out hierarchically even if the whitepace is stripped.

Whitespace Best Practice

  • Question: Should I recommend to the developers to use "strip whitespace" or "preserve whitespace"?
  • Answer: It depends on the XML application and how the XML is used. preserve whitespace needs more storage. preserve whitespace is useful if the formatting and layout of XML is significant, for display purposes.

Images in XML Documents

  • Question: Can I store images inside XML documents?
  • Answer: Yes, through Base64 encoding, but it will "fluff up" the XML.

Namespaces

  • Question: Can I have multiple namespaces in a document?
  • Answer: Yes

Matching Data Types Best Practice

  • Question: There is a best practice to match XML index data type with the XML data type specified in a query. With access plans created at bind time, how will the optimizer know in advance what data type is going to be used in a query? and whether it matches the data type in the index definition?
  • Answer: The plan will be aware of the data type at bind time because the data type is specified in a cast statement in the query.

Trailing Spaces

  • Question: Are trailing spaces important when determining if a VARCHAR XML index finds a match for a query?
  • Answer: Yes trailing spaces are important. The index will not find a match if there are trailing spaces in an XML document, and the query does not include the trailing spaces.

Creating an XML Document on the Fly

  • Question: Can we create a XML document on the fly by extracting data from relational table?
  • Answer: Yes, using the XML publishing functions

SQL/XML Query with fn:contains

  • Question: Can we use fn:contains to return documents that contain a specific substring in a specific element?
  • Answer: Yes. Consider this XML sample stored in the DESCRIPTION column in the PRODUCT Table: 
    <product xmlns="http://posample.org" pid="100-201-01">
  <description>
    <name>Ice Scraper, Windshield 4 inch</name>
    <details>Basic Ice Scraper 4 inches wide, foam handle</details>
    <price>3.99</price>
  </description>
</product>
  • To retrieve all the documents that contain "substring" in a specific element, we can use fn:contains function in the square brackets of XMLEXISTS (similar to a WHERE clause that returns TRUE or FALSE). fn:contains checks for the "substring" and returns TRUE or FALSE for each document. If inside the XMLEXISTS brackets fn:contains returns:
    • FALSE, an empty sequence item will be returned by XPath, and XMLEXISTS will return FALSE.
    • TRUE, an item will be returned by XPath, and XMLEXISTS will return TRUE.
  • Example 1: SQL/XML query that returns the documents in the DESCRIPTION column in the PRODUCT table that contain "4" in their element "name": 
    select DESCRIPTION from PRODUCT
WHERE XMLEXISTS('declare default element namespace "http://posample.org"; 
$po/product/description/name[contains(.,"4")]' passing PRODUCT.DESCRIPTION as "po")
  • Example 2: SQL/XML query that returns the "name" elements for documents in the DESCRIPTION column in the PRODUCT table containing the substring "Ice Scraper" in their "name" elements: 
    select XMLQUERY('declare default element namespace "http://posample.org"; 
$po/product/description/name' passing DESCRIPTION as "po") from PRODUCT
WHERE XMLEXISTS('declare default element namespace "http://posample.org"; 
$po/product/description/name[contains(.,"Ice Scraper")]' passing PRODUCT.DESCRIPTION as "po")

Storing XML versus Relational

  • Question: When should I store XML?
  • Answer: A simple rule of thumb is to consider storing XML if the object or entity in the external world is represented as XML, and it is often exposed as XML when retrieved from the database. Of course there are other considerations, but this guideline provides a starting point.

Designing XML versus Relational

  • Question: Are there guidelines for designing XML?
  • Answer: For relational data there are the well established data normalization guidelines. There are some XML design guidelines on the Internet, see for example:
  • You'll find a lot more if you look around. If you are involved with an industry consortium that defines an industry XML schema, you will find they have guidelines too.

Designing XML for Schema Evolution

  • Question: How do I design an XML schema for evolution?
  • Answer: Here are some points to keep in mind:
    • Use namespaces that indicate the schema version
    • Consider including a version attribute in the instance documents
    • Preserve XPaths for existing elements and attributes across schema versions

VARCHAR Hashed on DB2 UNIX/Windows

  • Question: Can one varchar hashed index entry resolve to multiple documents?
  • Answer: Yes - this question came up in an earlier bootcamp here

Multiple Documents in one XDA page on DB2 for UNIX/Windows

  • Question: Can an multiple XML documents be stored on the same XDA page in DB2 for UNIX/Windows?
  • Answer: Yes

XML Indexes

Index Statistics on DB2 zOS

  • Question: Are there statistics for XML index usage in DB2 zOS?
  • Answer: Yes, in DB2 zOS 9 - real time statistics are available for index usage including XML usage

Padded Indexes on DB2 zOS

Indexing XML on DB2 zOS

  • Question: For XML index, since there is a 1:m ratio (1 document can have multiple indexes), is there any limitation on max number of XML value index entries? or does it just depend on the indexspace storage?
  • Answer: There is no limitation on the number of index entries per XML document. The size of the indexspace is the limit.

Index ANDing and ORing between relational and XML on DB2 zOS

  • Question: Is Index ANDing and ORing supported between relational and XML?
  • Answer: Yes

IX_EXTENSION_TYPE on DB2 zOS?

XML Schema Validation

  • Question: Is DB2 XML validation cheaper than other methods of XML validation?
  • Answer: DB2 uses the common shared technologies for validation - so we would expect XML validation to perform in a similar way to other XML validation software

XMLCAST on DB2 zOS?

  • Question: Is XMLCAST available on DB2 zOS?
  • Answer: Yes, XMLCAST is available in DB2 9 zOS, as well as XMLTABLE post GA

LOB Locator for XML on DB2 zOS

  • Question: Is there something like a LOB Locator for XML on DB2 zOS?
  • Answer: You can do something like this pseudo code for insert:
SET :lob_locator = XMLLOBpiece1...
LOOP
SET :lob_locator = :lob_locator || XMLLOBpiece2
until done

INSERT INTO XTABLE(:lob_locator);
  • Answer: You can do something like this psedo code for select:
DECLARE CURSOR c FOR ...
SELECT XMLSERIALIZE( xmlc as BLOB(10M)) FROM ....
...
FETCH c into :lob_locator
  • or you can do something like this for select:
DECLARE CURSOR c FOR ...
SELECT xmlc FROM ....
...
FETCH c WITH CONTINUE into :fixed_buffer
LOOP:
check SQLCODE
consume the content in :fixed_buffer
if not finished then
FETCH C CONTINUE into :fixed_buffer
else break
goto LOOP

SPUFI on DB2 zOS

  • Question: Can I use SPUFI with XMLQUERY and XMLEXISTS on DB2 zOS?
  • Answer: Yes

Partitioned Datasets Unload for XML on DB2 zOS (half million entries limit)

  • Question: How do I unload XML with DB2 zOS?
  • Answer - Performance wise: HFS is the best choice at this point.
  • Answer - For PDS unload:
    • PDS and PDS/e do not span volumes and have a 524,286 member limit so the maximum rows unloaded in one job with UNLOAD XML using PDS FRVs is 524,286. As a result if a table contains more than 524,286 rows, UNLOAD jobs using PDS would need to be split into multiple jobs using the WHEN condition (provided that the data can easily be split via partition range or a unique column). If splitting UNLOAD jobs is not possible, UNLOAD FRV using HFS is the best alternative as there is no limit on number of rows unloaded to an HFS FRV (file reference variable).
  • Answer - For HFS unload:
    • There is a 2GB file size limit to UNLOAD to a HFS file. HFS data sets can expand into multiple volumes. The support was provided in
      DFSMS/MVS 1.5. The single volume restriction for HFS data sets has been removed. HFS data sets can now span up to 59 volumes, with up to 255 total extents for all volumes, and up to 123 extents per volume.

Unload and XML > 32K on DB2 zOS

  • Question: If the XML is > 32K, will you get an error or truncate in the UNLOAD Utility if using SYSREC only?
  • Answer: Get an error

MATCHCOLS on DB2 zOS

  • Question: What would you see in MatchCOLS for an XML index
  • Answer: 1

XML in Partitioned DB for DB2 zOS

  • Question: Can I use XML in a partitioned DB?
  • Answer: Yes, XML can be stored in a table partition. Partition by range is a table partition. It used to be an index-controlled partition. It was relaxed in V8, and is called table partition (without a partitioning index).

Locking using XML on DB2 zOS

  • Question: Is it possible to disable LOCK when using XML? Since we need to touch docID index, nodeid index and XML tablespace. Will there be too many LOCKs so lock escalation aould happen easily
  • Answer: LOCK cannot be disabled. With XML locks, page locks were eliminated for select. Page locks will be removed for insert also (APAR pending). Further improvements are planned for future releases.

Buffer Pools on DB2 zOS

  • Question: Can I use 32k bufferpool for XML Tablespace?
  • Answer: No, we cannot. XML tablespace always use 16K page.

Buffer Pools Best Practice on DB2 zOS

  • Question: What is the best practice recommended by IBM if most XML document size > 16K? 16K Bufferpool does not seem very helpful.
  • Answer: 16K is the page size, not the bufferpool size itself. For XML docs > 16K, DB2 will use multiple pages. Bufferpool is not for single pages.

Group Buffer Pools on DB2 zOS

  • Question: Is there any side effect for not using GBP (Group Buffer Pool) for XML tablespace?
  • Answer: No difference from regular tablespace.

pureXML Forum

pureXML Telcons (pureXML Devotees)

  • Question: Are there pureXML telcons?
  • Answer: Yes - see pureXML Devotees. The calls are recorded so you can play earlier recordings.

pureXML Industry Bundles

  • Question: Where can I find the pureXML Industry Bundles?
  • Answer: You can Google pureXML Alphaworks and take first hit. Select download now. If you want bundles for a different industry, please contact us as there a lot more bundles.

pureXML Industry Demos (interactive)

  • Question: Where can I find the pureXML Industry Demos?
    Answer: You can Google pureXML Alphaworks and take first hit. Select View Demo and follow the links.

Universal Services for pureXML

IBM Academic Initiative for System Z

  • Question: Where can I find out about the IBM Academic Initiative for System Z?
  • Answer: You can see details of the program here and you can read a brochure about some of the participating universities here

Special Guests at the Palisades Bootcamp in August 2009

Bobbie Cochrane, Bill Carey and Nagesh Subrahmanyam joined us. Bobbie spoke about Mashup Center. Bill Carey described the XML technologies on zOS. Nagesh is working on a redbook for on XML for zOS sponsored by Bill. An extra special visitor was Jane Man who flew in from California.

Some remote special guests were Guogen Zhang who talked about the performance of XML on DB2 zOS, Koshy John who talked about runstats, Michael Schenker who talked about Data Power.

Photos from the Palisades pureXML BootCamp in August 2009










The pureXML BootCamp in October 2009 at Manhattan

Questions and Answers from the Manhattan Bootcamp in October 2009

pureXML

  • Question: What does the term pureXML mean?
  • Answer: pureXML is the name given to the XML support in DB2 (to store, index, and query XML)

Specifying DD name on LOAD

  • Question: On IMPORT can the file be named indirectly through a DD name in the JCL?
  • Answer: IMPORT is only for DB2 on UNIX and Windows. DB2 LOAD requires the user to specify a valid data set name as the input.

Upper Case indexes for XML

  • Question: Are upper case indexes supported for pureXML?
  • Answer: For DB2 zOS, track track APARs PK80732/PK80735 (pre-conditioning and enabling apars for performance enhancements, including upper-case index support).

XPath

  • Question: What is the difference between
    • query db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo/addr[ city='Toronto' ]/name;
    • xquery db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo[ addr/city='Toronto' ]/name
  • Answer: The first returns the "name" child element from "addr". The second returns the "name" from "customerinfo". So in our example the first has zero rows returned, and second has 3 rows returned.

Locking APAR for DB2 zOS

  • Question: I heard there is a locking APAR for DB2 zOS. where can I find it?
  • Answer: See PK68265 - also see locking question at the Palisades Bootcamp

Logging

  • Question: Is XML logged on DB2 zOS?
  • Answer: XML is either logged or not logged depending on the base table log attribute

Cobol

  • Question: what does pureXML mean to Cobol programers?
  • Answer: You can use a declarative language SQL/XML to manipulate XML easily and efficiently from regular Cobol applications as an alternative to parsing the XML from Cobol - also see this article

MQT

  • Question: Can XML be used with MQTs?
  • Answer: No

XML Update

  • Question: Is XML update through XPath available on zOS?
  • Answer: Not yet

XQuery functionality on zOS

Elements and attributes not in the schema when validating

  • Question: When validating, can I have additional elements and attributes that do not appear in the corresponding XML schema in an instance document?
  • Answer: No not in the namespace that corresponds to the schema being used for validation - but you can have additional elements and attributes in that same instance document in another namespace that corresponds to a different schema

Help with Migration to DB2 9

  • Question: Is there any help with Migration to DB2 9 on zOS?
  • Answer: Yes - contact your local DB2 specialist who can arrange for a DB2 migration workshop suitable for DBAs and database application developers










The pureXML BootCamp in October 2009 in Albany



Questions and Answers from the Albany Bootcamp in October 2009

pureXML

  • Question: How often should runstats be executed for pureXML?
  • Answer: The same as for relational data. Some DBAs use real-time statistics to determine, if there are 20-30% changes since last time, then they do re-org with stats.

Code Tables

  • Question: What happens with code tables with pureXML?
  • Answer: You can keep them as relational and write SQL/XML to check that the appropriate XML content appears in a code table. Some people also create code tables in XML, in order to ease the exchange of the codes across different systems.

Data Power

Cobol XML

  • Question: Does Cobol have a function to produce XML subdocuments from an XML document?
  • Answer: No

Cursor with XMLTABLE

  • Question: Can I use a cursor with XMLTABLE?
  • Answer: Yes - e.g., DECLATE CURSOR XMLTABLECURSOR SELECT ...FROM XMLTABLE (.. PASSING DOCUMENT :tobeshredded ) COLUMNS ....) XT; OPEN XMLTABLECURSOR; FETCH .....

XMLTABLE - returning rows for elements that have no sub-elements

  • Question: With XMLTABLE on slide 10, you demonstrated that you need to get down to the lowest detail level which in that case was employee. With the query on that slide you would not get a row for any departments that had no employees. Could you give me an example of a query that would return a row for a department with no employees as well as the departments with employees?
  • Answer: For DB2 on UNIX and Windows: 
    create table dept (deptdoc XML); 

-- insert a dept without employees: 
insert into dept(deptdoc) values(' 
<dept DID="202"> 
</dept>'); 

-- insert a dept with employees: 
insert into dept(deptdoc) values(' 
<dept DID="101"> 
   <employee id="901"> 
      <name> 
        <first>John</first> 
        <last>Doe</last> 
      </name> 
      <office>344</office> 
   </employee> 
   <employee id="902"> 
      <name> 
        <first>Peter</first> 
        <last>Pan</last> 
      </name> 
      <office>216</office> 
   </employee> 
</dept>'); 


SELECT X.* 
FROM dept, 
 XMLTABLE ('$d/dept/(employee, .[not(employee)]/. )' passing deptdoc as "d" 
    COLUMNS 
      DID       INTEGER     PATH '(../@DID, @DID)', 
      empID     INTEGER     PATH '@id', 
      firstname VARCHAR(20) PATH 'name/first', 
      lastname  VARCHAR(30) PATH 'name/last', 
      office    INTEGER     PATH 'office') AS X; 

       
DID         EMPID       FIRSTNAME            LASTNAME                       OFFICE 
----------- ----------- -------------------- ------------------------------ ----------- 
       101         901 John                 Doe                                    344 
       101         902 Peter                Pan                                    216 
       202           - -                    -                                        - 

 3 record(s) selected.

    For DB2 on zOS:

    SELECT X.*                                               
FROM XXXX,                                               
 XMLTABLE ('$d/dept/employee' passing X1 as "d"          
    COLUMNS                                              
      DID       INTEGER     PATH '../@DID',              
      empID     INTEGER     PATH '@id',                  
      firstname VARCHAR(20) PATH 'name/first',           
      lastname  VARCHAR(30) PATH 'name/last',            
      office    INTEGER     PATH 'office') AS X          
                                                         
Union                                                    
                                                         
SELECT Y.*                                               
FROM XXXX,                                               
 XMLTABLE ('$d/dept[not(employee)]/.' passing X1 as "d"  
    COLUMNS                                              
      DID       INTEGER     PATH '@DID',                 
      empID     INTEGER     PATH '@id',                  
      firstname VARCHAR(20) PATH 'name/first',           
      lastname  VARCHAR(30) PATH 'name/last',            
      office    INTEGER     PATH 'office') AS Y;

Photos from the Bangalore pureXML Mini-BootCamp in December 2009

The photos include the famous golf course which is referred to in the first sentence of Thomas Friedman's The World is Flat - the photo was taken from the IBM Building:

No one ever gave me directions like this on a golf course before: "Aim at either Microsoft or IBM." I was standing on the first tee at the KGA Golf Club in downtown Bangalore, in southern India, when my playing partner pointed at two shiny glass-and-steel buildings off in the distance, just behind the first green. The Goldman Sachs building wasn't done yet; otherwise he could have pointed that out as well and made it a threesome. HP and Texas Instruments had their offices on the back nine, along the tenth hole. That wasn't all. The tee markers were from Epson, the printer company, and one of our caddies was wearing a hat from 3M. Outside, some of the traffic signs were also sponsored by Texas Instruments, and the Pizza Hut billboard on the way over showed a steaming pizza, under the headline "Gigabites of Taste!"



Questions and Answers from the Bangalore Mini-Bootcamp in December 2009

Performance

DBAs

  • Question: What do DBAs need to know about pureXML?
  • Answer: The Best Practices for pureXML mentioned above cover much of what DBAs need to know

BLOB and pureXML

Migrating from XML in BLOB

  • Question: How do I migrate from XML in BLOB (or from VARCHAR FOR BIT DATA) to XML datatype?
  • Answer: Of course queryability is limited with BLOB. You could use the XML Extender to extract key fields into relational columns, and do the same later more efficiently with pureXML. To migrate, you simply export the LOB column and then import or LOAD into an XML column. You can use "load from cursor" to move XML docs from a LOB column to an XML column, avoiding the export step. You can also cross-load to XML from VARCHAR FOR BIT DATA.

Forms and pureXML

Document LENGTH

EDI to XML conversion

Hibernate

  • Question: How do I use Hibernate and pureXML?
  • Answer: You can use XMLTABLE view to access XML data as relational, or you can refer to this article https://www.hibernate.org/466.html

Projects

  • Question: I'd like to work on open source projects on pureXML. Is there a list?
  • Answer: We can start compiling a list - e.g., various load functions - from the row function

Further Study

  • Question: I'd like to have further study of pureXML?
  • Answer: You can take professional education - It can be arranged to take place at your location:
    • Descriptions of courses for IT professionals and managers are available at
    • Visit ibm.com/training or call IBM training at 800-IBM-TEACH (426-8322) for scheduling and enrollment in the US.
      • Courses for DB2 for LUW:
        • CL12 Query XML Data with DB2 9
        • CL14 Manage XML Data with DB2 9
      • Courses for DB2 for z/OS:
        • CV12 Query XML Data with DB2 9 for z/OS
        • CV26 Manage XML Data with DB2 9 for z/OS
  • You can attend a full pureXML bootcamp in person (bootcamps are scheduled on demand) or you can take it on-line:
    • Go to : http://eb90.elearn.ihost.com/wps/portal/ibm
    • If you have already registered, then log in on the first screen
      • Click on 'Preferences' on the left hand menu
      • In the 'access code' (you will be provided with what to type)
      • If you have not registered, then on the first screen, in the second paragraph, you will see a hyper link 'self-register with an Access Code'.
      • Click that link and enter your access code (which you will be provided with)
      • Click on continue. It will bring you to a registration screen.
      • Once logged in, in order to locate the pureXML Bootcamp course (CAMPXML-240L),
        • click on 'available courses' from the left hand menu.
        • Do a search on 'Data Mgmt Curriculum' in the title field. You should see the list of courses appear.
        • click on the pureXML Bootcamp link, enroll and then you can launch the course.
        • Make sure to change security setting to accept the browser pop-up to allow the course to be launched.

 
    About IBM      Privacy      Contact