• 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

• Ruby And DSLs And Blocks

  April 21, 2015 Aaron Bartell

  When I started with Ruby I was immediately drawn in by its syntax–simple, elegant, and easy to peruse. At the time I didn’t know this was an intentional mantra of the language put in place by Yukihiro “Matz” Matsumoto, Ruby’s creator. Matz said that he was “trying to make Ruby natural, not simple,” in a way that mirrors life. Further, Matz stated “Ruby is simple in appearance, but is very complex inside, just like our human body.” Let’s take this out of metaphor-land and into tangible code by introducing the DSL, or Domain Specific Language, aspect of Ruby. First, what

  …

  Read more

• Joining On Ranges

  April 21, 2015 Ted Holt

  People are ingenious. Where there’s a need, they find a way. Unfortunately, some of the ways they find don’t jibe well with relational databases, which makes my work life messy. Fortunately, I know a few ways to clean it up, and here’s one of them. Suppose you work for a company that sells office supplies. One day someone walks into your office and asks for a report of sales of boxes. “Sure thing!” you reply. “How do I know that an item is a box?” “Easy,” he replies. “The item number begins with 134.” Easy, indeed. select ItemNumber, Description from

  …

  Read more

• An SQL Pivot Procedure

  April 21, 2015 Paul Tuohy

  The basic concept of a pivot is that row values are transformed into column values. I have often wished that DB2 had a pivot procedure. But until that day arrives I will have to make do with a workaround that you might find useful too. The workaround is in the form of a stored procedure that can be used to pivot any column in any table. Before getting to the stored procedure, let’s look at a simple pivot example. These examples are using the SALES table in the DB2 sample database. To create the DB2 sample database on your system,

  …

  Read more

• A Fastpath To Open A Specific Member In RSE

  April 7, 2015 Susan Gantner

  One of the first tips I ever wrote for Four Hundred Guru was about keyboard shortcuts for RSE. It was written so long ago, I was talking about WDSC. None of the Rational variants of the product name had come to be at that time. It even predates my downloadable cheat sheet for My Favorite Keyboard Shortcuts for RSE. As you can see if you look at the cheat sheet, I’ve added a lot more favorites since the original article. The shortcuts in those original tips still work with the recent Rational versions of RSE with one exception.

  …

  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

• IBM i Toolkit for Ruby, iit For Short

  April 7, 2015 Aaron Bartell

  What if I told you that the best developers in the world are available to work on your next project? What if I told you that they will work for free? Well, they are and they will. In the following paragraphs, I tell you why and how. The best developers in the world will help you with your projects, for free, because of the magic of open source. Open source efforts have to rank right up there on the list of abnormalities of the human race. Why? Because so much is given away at high cost (time, which in turn

  …

  Read more

• RubyGems Are The Foundation Of Success

  March 31, 2015 Aaron Bartell

  In my previous article, I discussed a RubyGem named xmlservice but didn’t really dive into what Gems are, why they exist, and how we can use them to save a tremendous amount of time. That’s what this article is about. But before we dive into geekdom, it is worth digressing into a perspective I’ve gained by observing the top open-source-language camps. I often get asked “Why Ruby? Isn’t XYZ language more popular?” Usually the XYZ language is PHP or Java, as I think it is safe to say PHP is the most popular web language out there and Java

  …

  Read more

• Dynamic Lists In Static SQL Queries

  March 31, 2015 Ted Holt

  Hey, Ted: We have an SQL query that can take from one to 13 two-byte codes that become part of an IN clause for row selection. We have no idea which codes they will need to see beforehand; it’s an interactive thing. We are using dynamic SQL. I would like to find a static solution. Any suggestions? –Harold Sure, Harold. I do this sort of thing from time to time, and I use static SQL to do it. Let’s say those two-byte codes are state abbreviations, like the state abbreviations we use in the USA. You probably have a form

  …

  Read more

• Handling Constraints Revisited

  March 31, 2015 Paul Tuohy

  I recently had an email from Doug, who was making use of some code I had published way back in February 2010 in my article Handling Constraint Violations in RPG. The article discussed how to trap a constraint violation caused by a WRITE/UPDATE/DELETE operation and, more importantly, how to determine the name of the constraint that caused the violation. Doug had come up with an instance where my code was not working! After going through the usual stages of denial, anger, and some expensive therapy, I read the rest of the email. Doug’s code worked fine with an RPG

  …

  Read more

Previous Articles Next Articles