Michael Sansoterra
-
New OLAP Aggregate Functions In DB2 for i, Part 1
July 12, 2016 Michael Sansoterra
… Read moreIn 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
-
Inline Table Functions In DB2 For i
June 14, 2016 Michael Sansoterra
… Read moreIn 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
-
LAG And LEAD Functions In DB2 for i 7.3
May 31, 2016 Michael Sansoterra
… Read moreDatabase 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
-
DB2 for i 7.2 TR3 and 7.1 TR11 Features
May 17, 2016 Michael Sansoterra
… Read moreI 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
-
OLAP Aggregation Specification In DB2 For i 7.3
May 10, 2016 Michael Sansoterra
… Read moreIf I could use one word to describe the new DB2 for i OLAP features in IBM i 7.3, it would be “Booyah”! (According to the Urban dictionary, “booyah” is “used in order to abruptly express great joy, usually brought on by victory or some other sort of accomplishment.”) Though there are many new features in i 7.3 under the umbrella of OLAP, for now I’m going to demonstrate the new OLAP aggregation specification.
It was way back in 2006 in V5R4 that DB2 for i developers were first treated to OLAP (Online Analytical Processing) functions. If you’re unfamiliar with
-
Surge of Services in DB2 for i, Part 2
April 19, 2016 Michael Sansoterra
… Read moreContinuing where we left off in Part 1, a slew of new IBM i services (and enhancements to existing services) were released in IBM i 7.2 TR3 and IBM i 7.1 TR11. This article continues to detail some of the important new services.
As always, keep in mind that just an overview is given here and that the developerWorks documentation or IBM i Services in the Knowledge Center should be consulted in order to understand the full capability of each service.
This view runs a real-time query against the IBM Preventative Service Planning (PSP) website and can indicate
-
Surge Of Services In DB2 For i, Part 1
April 5, 2016 Michael Sansoterra
… Read moreThe IBM i 7.2 TR3 and 7.1 TR11 technology refreshes come with a glut of new services. If you are responsible for IBM i administration, you are guaranteed to be tickled pink with these great enhancements. My examples are to call general attention to the availability of these services and give ideas on how they may be used. However, I still recommend going to the IBM documentation on the DeveloperWorks site (or the IBM knowledge center) to get full information on the parameters and all columns used by these special views and table functions.
For clarification, there are actually
-
Retrieving Data From All Members With SQL
March 1, 2016 Michael Sansoterra
… Read moreNote: The code accompanying this article is available for download here.
Everyone knows that DB2 for i SQL doesn’t play well with legacy multi-member database files. The CREATE ALIAS statement allows SQL to access a particular member of a file. But what if you want to access all members in a file using SQL as though they were a single member? A reader recently inquired about this topic and further wanted to know if there was a built-in SQL function to return each row’s member name.
NOTE: For clarification, I’m not discussing members of a DB2 partitioned table.
Here
-
Parsing Delimited Text Data Natively In SQL, Part 2: Java To The Rescue
February 9, 2016 Michael Sansoterra
… Read moreNote: The code accompanying this article is available for download here.
In Part 1 of this series, I introduced ParseFileContent, a 100 percent SQL user-defined table function (UDTF) that can parse delimited text (as a CLOB or IFS file) and format the results as rows and columns. As an advantage over tools like CPYFRMIMPF, this UDTF can streamline a text based import process by allowing CASTs, JOINs, and data integrity checks to be done while the import is running. As useful as ParseFileContent is, it does suffer a few drawbacks.
ParseFileContent is not particularly fast and it can’t handle
-
Parsing Delimited Text Data Natively in SQL, Part 1
January 26, 2016 Michael Sansoterra
… Read moreNote: The code accompanying this article is available for download here.
A common task for database developers is to accept a delimited text file, parse it, and dump it into a database table. This tip demonstrates a user-defined table function (UDTF) that can accomplish this task based on delimited text data stored in a CLOB or in an IFS file.
The UDTF is called ParseFileContent and it accepts three parameters: CLOB data to parse, row delimiter character(s), and column delimiter character(s). The UDTF returns a ROWID column that represents the line number of the text data and 250 columns