Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan.
June 11, 2018 Michael Sansoterra
Hey, Mike! Regarding Composing An XML Document From Relational Data, Part 1, I have built an XML document using DB2 and i. When I run the query, I get a worthless result set. How do I use the SQL XML functions to get a usable XML file?
This question comes from reader RA, and he doesn’t exaggerate. The result set from his XML-based query looks like this:
....+....1....+....2....+....3....+....4....+ ************Beginning of data************** XMLDATA -------- #CGULIB# 1 RECORD(S) SELECTED. ************End of Data********************
Notice that DB2 for i does nothing to make the XML attractive to human eyes as the entire XML …Read more
April 3, 2017 Michael Sansoterra
After reading the IT Jungle Guru tips on DB2 XML Composition (see Related Stories below), I’ve been able to build XML from relational data. I have everything working but now I need to enclose all data in a “Document” tag after the XML declaration like the example here:
<?xml version="1.0" encoding="utf-8"?> <Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://MyCompany.com" xsi:schemaLocation="http://MyCompany.com validate.xsd">
I tried using XMLNAMESPACES but can’t get it to produce the “xsi:schemaLocation” tag. How can I construct the document tag I need?
The problem here is that the xsi:schemaLocation in your example is not a namespace, but …Read more
January 23, 2017 Michael Sansoterra
I’m writing SQL scripts to do some administrative work. These scripts are run in iNavigator’s RunSQL scripts utility and use the special CL: directive to execute an IBM i command. I stumbled across the CREATE VARIABLE statement and wondered if I could use an SQL variable to substitute a library name within the OS commands.
For example, if I define something like the following in iNavigator:
CREATE OR REPLACE VARIABLE QGPL/LIB CHAR(10) DEFAULT 'JOE';
Can I then use the LIB variable in a CL: command as follows?
CL: DSPLIB LIB(LIB) OUTPUT(*PRINT);
Unfortunately, GLOBAL VARIABLES cannot …Read more
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
SELECT XMLAGG( XMLROW(NAME as "Name", Color as "Color",
August 2, 2016 Michael Sansoterra
The SQL implementation in DB2 for i is second to none. However, one irritating thing common to various SQL dialects is the need to repeat expressions in query. As SQL matured over the years, techniques such as nested table and common table expressions became available to, among other things, reduce repetitive expressions. This tip illustrates the use of the LATERAL correlation as another way to avoid repetition.
Say you’re writing a report for a grocery wholesaler, where markup on food items is small and discounts are even smaller. Looking for orders that were not priced correctly, you’re tasked
July 19, 2016 Michael Sansoterra
In my last article, I promised to tell you about four new OLAP aggregate functions: FIRST_VALUE, LAST_VALUE, nTH_VALUE and RATIO_TO_REPORT. These valuable functions can reference data from other rows in a query result set relative to the current row. This tip covers the fourth function in the list, RATIO_TO_REPORT.
The RATIO_TO_REPORT Function
The new RATIO_TO_REPORT function is heaven sent, as it makes it easy for developers to avoid writing an UGLY arithmetic expression to do a simple calculation common in business. RATIO_TO_REPORT simply divides a numeric column or expression value in the current row by a SUM aggregate of
July 12, 2016 Michael Sansoterra
In my last tips about the new OLAP features in DB2 for i 7.3, I discussed the OLAP Aggregation Specification and the new LAG and LEAD OLAP functions. In this article and the next one, I continue the discussion of new OLAP features by highlighting four new OLAP aggregate functions: FIRST_VALUE, LAST_VALUE, nTH_VALUE, and RATIO_TO_REPORT. The value of these functions is that they can reference data from other rows in a query result set relative to the current row.
The new OLAP aggregate functions differ from normal aggregate functions (such as MIN, MAX, SUM, and AVG) in that they can
June 14, 2016 Michael Sansoterra
In DB2 for i 7.2 TR4 and IBM i 7.3, IBM has made a special user-defined table function (UDTF) enhancement that should be shouted from the rooftops. This enhancement is referred to as an inline table function.
Consider the simple example of this UDTF named Get_Customer_Orders:
CREATE OR REPLACE FUNCTION Get_Customer_Orders ( @CustomerID INT, @StartDate DATE, @EndDate DATE) RETURNS TABLE ( SalesOrderId INT, CustomerId INT, OrderDate DATE, ShipDate DATE, SUBTOTAL DEC(19,4)) LANGUAGE SQL NO EXTERNAL ACTION DISALLOW PARALLEL SET OPTION COMMIT=*NONE,USRPRF=*OWNER,DATFMT=*ISO RETURN SELECT SalesOrderId,CustomerId, OrderDate,ShipDate,SubTotal FROM SalesOrderHeader WHERE CustomerId=@CustomerId AND OrderDate BETWEEN @StartDate AND @EndDate;
What does DB2 do when
May 31, 2016 Michael Sansoterra
Database devs, you can kiss many of your cursors goodbye, because DB2 for i has two sweet new functions named LAG and LEAD that will allow you to extract a column value from a different row within the result set (relative to the current row) without programmatically looping through each row.
From the current row, LAG instructs DB2 to look backward in the result set a specified number of rows and retrieve a value. LEAD allows DB2 to look ahead to a specified row beyond the current row and extract a value. In DB2 parlance, these new functions belong to
May 17, 2016 Michael Sansoterra
I was writing up some exciting stuff on the new IBM i 7.3 features when I realized I never finished reporting about other recent DB2 features! Below are highlights of some newer features offered starting in DB2 for i 7.2 TR3 and 7.1 TR11.
Assign A System Name To A Global Variable
Recall that when you create a global variable, behind the scenes SQL Server implements the variable’s logic and value retrieval logic as a service program. The following variable definition with a long name (>10 characters):
CREATE OR REPLACE VARIABLE DEV.AVENGERS_TOWER VARCHAR(32) DEFAULT (SELECT ADDRESS FROM DEV.HERO WHERE