Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan.
February 4, 2019 Michael Sansoterra
In the tip, Arranging Query Logic in DB2 for i Routines, I addressed a reader’s question about how to create a user-defined table function (UDTF) in DB2 for i that would return the same result set as an existing stored procedure. The purpose of having the UDTF would be to do additional processing on a result set, such as joining the result set with another query or dumping the result set to a temporary table for analysis. To accomplish this, I suggested moving the stored procedure query logic into a UDTF and then replace the query within the stored …Read more
January 7, 2019 Michael Sansoterra
We use a stored procedure to return the result set to Java and display the results to a screen. I would like to make this stored procedure put the result set into a temporary table and then do some processing on the temporary table. Is this possible and if so how can I achieve this? Thanks.
Your dilemma is understandable: stored procedure result sets are great when data needs to be returned to a client. However, once generated, the result set cannot be joined, sorted, or stored in a temporary table. Thankfully, a user-defined table function (UDTF) allows …Read more
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