Michael Sansoterra
-
Pipelined User-Defined Table Functions In DB2 For i
May 5, 2015 Michael Sansoterra
… Read morePipes are a fantastic invention as they are useful for conveying a substance in an efficient and well directed manner. Whether used for transporting sewage, carrying oil, or even bottling a duck (if you’re old enough to remember “The High and the Flighty” Foghorn Leghorn cartoon featuring the Ace Novelty company’s “Pipe Full of Fun Kit #7”); our world wouldn’t be the same without them.
Not to be left out, starting in IBM i 7.1 TR9 and IBM i 7.2 TR1, DB2 for i now offers a “PIPE” statement that is used to quickly populate rows in a user-defined table
-
Gimme, Gimme, Gimme! More Services And Global Variables Available For DB2 for i
April 7, 2015 Michael Sansoterra
… Read moreTo satiate the insatiable among us, IBM continues to provide new ways for DB2 for i SQL developers and database administrators to programmatically interrogate and interact with their operating environment through the use of “services” and global variables.
New “Services” Views
The IBM DB2 for i Services have been a great boon to developers and administrators because they give access to information about the IBM i OS environment via the simplicity of SQL. For a brief intro to services, see my article Make Your DB2 For i Apps Environment Aware. There are several new “services” available as of IBM
-
TR8 DB2 For i Enhancements, Part 2
December 3, 2014 Michael Sansoterra
… Read moreIn the first part of this series, I introduced several of the DB2 for i enhancements made in IBM i 7.1 Technology refresh 8 (and in IBM i 7.2).
These enhancements were of interest to developers and administrators. Likewise, this second set of enhancements we are about to review will be of interest to developers but may hold even greater interest for those in an administrative role.
QSYS2.GET_JOB_INFO enhancement
This DB2 for i service table function has two new enhancements:
1. The job parameter now accepts the special value ‘*’ to return information about the current job.
2. New
-
TR8 DB2 For i Enhancements, Part 1
November 12, 2014 Michael Sansoterra
… Read moreIBM i 7.1 Technology refresh 8 was released on June 6, 2014, and it has another group of small database enhancements worth reviewing. IBM i 7.2 users will find that they already have these features. This tip assumes that you have IBM i 7.2 or IBM i 7.1 with Database Group PTF Level 29 or higher installed.
QSYS2.GENERATE_SQL Stored Procedure
This is one sweet new stored procedure IBM has given us. Its purpose is to programmatically retrieve SQL source code into a source member.
This procedure can accept a boatload of parameters as shown below. Many of the parameters control
-
DB2 for i 7.2 Features and Fun: Part 3
September 10, 2014 Michael Sansoterra
… Read moreIn the second tip of this series, I explored three new features introduced in DB2 for i 7.2: KEEP IN MEMORY clause; AUTONOMOUS PROCEDURE; and the CHECK CONSTRAINT VIOLATION clause. This tip aims to keep the momentum going by exploring a handful of remaining miscellaneous features, in no particular order.
TABLE_NAME And TABLE_SCHEMA Scalar Functions
These two new functions exist in the SYSIBM schema and are used to lookup base information about an alias. Say an alias is defined on a DB2 remote system table on a system named WASHINGTON:
CREATE ALIAS DATALIB.WASHINGTON_TRANSACTIONS FOR WASHINGTON.ADVWORKS.TRANSACTIONHISTORY;
The following VALUES statement will
-
Creating A GUID In DB2 For i
August 27, 2014 Michael Sansoterra
… Read moreNote: The code accompanying this article is available for download here.
Normally, modesty would forbid me from saying this, but I love Four Hundred Guru. I was looking for a way to create a globally unique identifier (GUID) also known as a universally unique identifier (UUID) so that I could write some DB2 code compatible with SQL Server. Unfortunately DB2 doesn’t have a GUID/UUID function so I had to search the web to find an alternative. What did I stumble upon? A good ol’ FHG article showing how to create a GUID/UUID in RPG that could easily be
-
DB2 for i 7.2 Features And Fun: Part 2
July 23, 2014 Michael Sansoterra
… Read moreIn the first tip of this series, I covered three new features introduced in DB2 for i 7.2: ALTER TRIGGER, TRUNCATE TABLE, and the ability to vary the fractional precision of a TIMESTAMP column. This tip continues the adventure by exploring a few more new features in IBM i 7.2 (in no particular order).
KEEP IN MEMORY Clause For Tables And Index
When creating a new index or table, a new KEEP IN MEMORY clause is available to tell DB2 to keep the data in main storage when possible. (A table or index cached in this manner should, of
-
DB2 for i 7.2 Features and Fun, Part 1
June 30, 2014 Michael Sansoterra
… Read moreThe dump truck of database features driven by IBM is delivering yet again, unloading a full load of great enhancements for the beloved DB2 for i RDBMS. In addition to the new features included in the various IBM i 7.1 technology refresh (TR) releases, 7.2 has even more security, developer, and administrative friendly enhancements that I’ll be discussing in this and future tips.
TRUNCATE TABLE
The TRUNCATE TABLE statement is used for deleting all of the rows in a table in one fell swoop. You can think of this statement as SQL’s version of the Clear Physical File Member (CLRPFM)
-
DB2 for i 7.2 Functions, Functions, Functions
May 28, 2014 Michael Sansoterra
… Read moreIn this tip about new DB2 for i 7.2 features, I’m going to cover some noteworthy news about user-defined function (UDF) features. The feature list includes how a function name is now resolved based on casting rules, named parameters, parameter defaults and array parameters.
Function Name Resolution
From the earliest days of V4R4 when user-defined functions became available, I’ve had a BIG pet peeve. That pet peeve is how DB2 tries to figure out what function you want to use.
For a little background, remember that a function can be overloaded. That means scalar UDF ConvertDate can be defined
-
Using Built-In Global Variables In DB2 For i 7.2
May 14, 2014 Michael Sansoterra
… Read moreThe long awaited announcement of IBM i 7.2 comes with several enhancements to DB2 for i. In this tip, I’m going to explore the new set of built-in global variables that come with DB2 for i 7.2. If you’re unfamiliar with global variables, see New in DB2 for i 7.1: Use Global Variables to Track Environment Settings or the CREATE VARIABLE statement.
The list of built-in global variables can be divided into three groups:
Group 1: Client TCP/IP Info
CLIENT_IPADDR VARCHAR(128)
CLIENT_PORT INT
As their names imply, these global variables return the IP address and port of the current client’s