Assorted DB2 for i Questions

August 16, 2016 Michael Sansoterra

Shown below are assorted questions related to DB2 for i. If you have a question you’d like Four Hundred Guru to try to answer, be sure to visit the IT Jungle Contact page. Note that some of these questions were modified from their original form for clarity and simplicity.

Adding an attribute to an element based XML

Q: The FHG tips on composing XML documents from DB2 data (Part 1 and Part 2) do not describe how to add a custom attribute to element based XML. Consider the following query:

SELECT XMLAGG(
       XMLROW(NAME as "Name",
              Color as "Color",
              ProductNumber as "ProductId"
              OPTION ROW "Product"))
AS PRODUCT_XML
FROM (
SELECT * FROM ADVWORKS12.PRODUCT
) p

to generate this element based XML:

<Product>
  <Name>LLCrankarm</Name>
  <Color>Black</Color>
  <ProductId>CA-5965</ProductId>
</Product>

But, what if I want the product ID to be an attribute of the product element as shown below?

<Product ID="CA-5965">
  <Name>LL Crankarm</Name>
  <Color>Black</Color>
</Product>

A. As far as I can tell, there is no way to combine element and attribute based XML using the XMLROW function as demonstrated in the referenced Guru tips. However, by using the XMLELEMENT and XMLATTRIBUTES functions, the desired XML structure can be built as follows:

SELECT XMLAGG(
       XMLELEMENT(NAME "Product",
         XMLATTRIBUTES(ProductNumber AS ID),
         XMLELEMENT(NAME "Name",Name),
         XMLELEMENT(NAME "Color",Color)
       ))
FROM (
SELECT * FROM ADVWORKS12.PRODUCT 
) p

The downside of abandoning XMLROW is that the SQL statement can become quite large when using several XMLELEMENT functions for a large number of columns.

Deadlock handling differences between DB2 for i and DB2 LUW

Q: Does DB2 for i handle deadlocks in the same way as DB2 LUW?

A. For background, I present the Wikipedia deadlock definition: “In a transactional database, a deadlock occurs when two processes, each within its own transaction, updates two rows of data (that is, records) but in the opposite order.”

In DB2 LUW (and many other database engines such as SQL Server) when the database engine detects a deadlock it will kill one of the deadlocked processes and automatically rollback the transaction that has done the least amount of work. In DB2 for LUW, SQL0911 message is issued when a deadlock occurs:

The current transaction has been rolled back because of a deadlock or timeout

This message ID doesn’t exist in QSQLMSG in DB2 for i 7.2. In fact, I don’t see any references to the term deadlock in the SQL Reference manual. Further, when I create an artificial deadlock scenario between two SQL sessions in DB2 for i, I receive an SQL0913 (row or object in use) error in one of the sessions with no automatic rollback.

In short, I have never witnessed a deadlock in DB2 for i and it doesn’t look like it has the capacity for deadlock handling. I’m not sure why it doesn’t support deadlock handling similar to other DB2 products, but apparently the best it can do is issue an “object in use” error after the default timeout occurs. By default, this error provides the system operator with the opportunity to cancel or retry the process.

If you’re programming a cross-platform DB2 application, make sure to put in an error handler for the SQL0913 to support DB2 for i.

TIMESTAMP function behavioral differences among different SQL Environments

Q. I’m running IBM i 7.1 TR 11. The following statement fails in STRSQL but succeeds in System i Navigator:

VALUES TIMESTAMP(CHAR(LAST_DAY(DATE(CURRENT_DATE-1 MONTHS)))||'-21.05.00')

What gives?

A. The answer to this perplexing conundrum lies in the differences in the default date format (*DATFMT) setting. System i Navigator defaults this value to *ISO (yyyy-mm-dd) whereas STRSQL for a standard US IBM i is *MDY.

When accepting a character value as a parameter, the TIMESTAMP function is somewhat restrictive in what it accepts as a valid string representation. The date portion of the string should be in *ISO format in order for TIMESTAMP to accept the string representation and convert it to an actual timestamp data type.

However, the CHAR function will convert the date expression into a *MDY format value that the TIMESTAMP function will reject. One easy way to make sure the behavior is consistent among SQL environments running with different date formats is to force the CHAR function to always format the date in the ISO format by adding the optional ISO date format parameter:

VALUES TIMESTAMP(CHAR(LAST_DAY(DATE(CURRENT_DATE-1 MONTHS)),ISO)||'-21.05.00')

When the date portion is consistently in ISO format, the TIMESTAMP function will happily accept a string representation without complaining.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search