• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact

Michael Sansoterra

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan.

  • Pipelined User-Defined Table Functions In DB2 For i

    May 5, 2015 Michael Sansoterra

    Pipes 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

    …

    Read more
  • Gimme, Gimme, Gimme! More Services And Global Variables Available For DB2 for i

    April 7, 2015 Michael Sansoterra

    To 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

    …

    Read more
  • TR8 DB2 For i Enhancements, Part 2

    December 3, 2014 Michael Sansoterra

    In 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

    …

    Read more
  • TR8 DB2 For i Enhancements, Part 1

    November 12, 2014 Michael Sansoterra

    IBM 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

    …

    Read more
  • DB2 for i 7.2 Features and Fun: Part 3

    September 10, 2014 Michael Sansoterra

    In 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

    …

    Read more
  • Creating A GUID In DB2 For i

    August 27, 2014 Michael Sansoterra

    Note: 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

    …

    Read more
  • DB2 for i 7.2 Features And Fun: Part 2

    July 23, 2014 Michael Sansoterra

    In 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

    …

    Read more
  • DB2 for i 7.2 Features and Fun, Part 1

    June 30, 2014 Michael Sansoterra

    The 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)

    …

    Read more
  • DB2 for i 7.2 Functions, Functions, Functions

    May 28, 2014 Michael Sansoterra

    In 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

    …

    Read more
  • Using Built-In Global Variables In DB2 For i 7.2

    May 14, 2014 Michael Sansoterra

    The 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

    …

    Read more

Previous Articles Next Articles

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle