Michael Sansoterra
-
Guru: Emulate Control Break Reporting With DB2 For i SQL
May 13, 2019 Michael Sansoterra
When generating ad hoc user reports, it’s often the case that developers don’t have the time or luxury to write a high-level language program or to use a reporting tool such as DB2 Web Query or Crystal Reports to format the data appropriately. But when a user is given a raw dump of a SELECT statement’s result set in Excel, it can be quite ugly to wade through and format.
The user may have to do things like add total rows or remove duplicate values, which can be quite time consuming. Thankfully, DB2 for i SQL offers a way to …
Read more -
Guru: IBM i Save File Compression Options
April 1, 2019 Michael Sansoterra
As I finished populating some test tables with a large volume of data on a small and transient IBM i partition in the cloud, I thought life was good. But my countenance fell as I realized the tables plus OS hogged over 70 percent of the disk space. I wondered how to get all the data into a single save file for safe keeping.
The buzzer in my mind was loud and clear: it ain’t gonna work, you don’t have enough room. As I loathed the thought of using multiple save files to save my test data, I remembered that …
Read more -
Guru: Create A UDTF Wrapper For A Stored Procedure Result Set
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 -
Guru: Arranging Query Logic in DB2 for i Routines
January 7, 2019 Michael Sansoterra
Hey, Mike:
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 -
Guru: DB2 For i XML Composition And The IFS
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 -
Guru: The XML Composition Conundrum of XMLNAMESPACES or XMLATTRIBUTES
April 3, 2017 Michael Sansoterra
Hey, Mike:
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?
–N.C.
The problem here is that the xsi:schemaLocation in your example is not a namespace, but …
Read more -
Guru: How To Use Global Variables In SQL Scripts
January 23, 2017 Michael Sansoterra
Hey, Mike:
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);
–Guru Reader
Unfortunately, GLOBAL VARIABLES cannot …
Read more -
Assorted DB2 for i Questions
August 16, 2016 Michael Sansoterra
… Read moreShown 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", -
Using Lateral Correlation To Define Expressions In DB2 For i
August 2, 2016 Michael Sansoterra
… Read moreThe 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.
The Problem
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
-
New OLAP Aggregate Functions In DB2 For i, Part 2
July 19, 2016 Michael Sansoterra
… Read moreIn 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