• Top Ten New IBM i RFEs

  May 22, 2017 Alex Woodie

  Should IBM open source its database drivers, or add support for .NET? Should it add the latest SSL ciphers to IBM i 7.1? These are some of the changes that it has received through its new Web-based requests for enhancement (RFE) program on its developerWorks site.

  The new RFE functionality is only a few months old, but it’s already attracting the attention of IBM i professionals who want IBM to change things about the stack. It’s also getting attention from other IBM i pros who get to vote on them.

  Here are the top ten RFEs so far, ranked by …

  Read more

• Guru: How To Cancel A Bad SQL Update

  May 15, 2017 Ted Holt

  In Three Ways To Manage Unmatched Data I wrote about the use of the RAISE_ERROR function to force a SELECT statement to cancel when unmatched data is considered a fatal error. Another good use of RAISE_ERROR is to force an UPDATE statement to cancel when an invalid condition occurs.

  To illustrate, imagine that you and I work in a factory. All factories have inventory. The people we serve purchase some inventory items and manufacture others. Our job is to write a program that will allow certain people to zero out the inventory balance for certain types of purchased items.

  The …

  Read more

• Guru: The Three Ways to Insert

  May 8, 2017 Ted Holt

  The INSERT statement is THE (as in the only) SQL way to add new data to a relational database table. At the risk of sounding like a GEICO commercial, “Everybody knows that.” Well, did you know that the INSERT statement supports three distinct ways to add new rows to a table?

  To illustrate the three forms of INSERT, imagine that you and I work for a small company that stores goods in, and ships goods from, a warehouse. Since the company has only one warehouse, there has never been a need for a warehouse ID column in any of …

  Read more

• Guru: SQL Table Functions Can Do Non-function Things

  May 1, 2017 Ted Holt

  We IBM i developers owe a great debt to Scott Forstie. He’s responsible for the wonderful DB2 for i Services and IBM i Services, which give us SQL interfaces for many functions of the operating system. Like IBM, we can write SQL interfaces to help us with non-database tasks. I recently did exactly that.

  While I can’t say that I never use the Start SQL Interactive Session (STRSQL) command, I can say that I prefer to use GUI SQL clients, in particular the Run SQL Scripts utility that is part of IBM i Access Client Solutions (ACS). I do get …

  Read more

• Guru: Three Ways To Manage Unmatched Data

  April 24, 2017 Ted Holt

  Heaven forbid that I would ignore a failed RPG CHAIN (random read) operation. I always take appropriate action. Which action I take depends on the situation. The same applies to outer joins that don’t find matching data in a secondary table. Here are three ways to deal with unmatched data in an outer join using SQL.

  To illustrate, let’s use three tables from an overly simplified general ledger system. The first is a table of departments into which the business is divided. The second is a chart of accounts. The third is a transaction file that feeds the general ledger. …

  Read more

• SQL To The Rescue

  April 10, 2017 Bob Cozzi

  Since the early 1990s, developers have been using SQL embedded in RPG as a way to retrieve just the right data for the task at hand. Certainly it was at least a decade before it became commonplace to see SQL embedded in RPG, but now, 15 years later, it is in fact the go-to method for data access, or at least you can see it from here.

  I was an early adopter of SQL, but an off-again, on-again user of embedded SQL. Originally, I felt the first format RPG with quasi-free format SQL (and all those plus signs to continue …

  Read more

• Guru: SQL Facts Of UNION And ORDER BY

  April 10, 2017 Ted Holt

  UNION and ORDER BY are powerful SQL features, but put the two together and you may get some strange and frustrating error messages. Fortunately for us DB2 for i professionals, there are easy ways to make the two collaborate and cooperate. Today is a great day to be sure we understand them.

  We need some data for examples. For some of the queries, I use a customer master table (file) and a vendor master table. These are a company’s trading partners, so the two have many attributes in common, especially names and addresses. I also use two sales history tables, …

  Read more

• Conditional SQL I/O, Take 2

  March 20, 2017 Ted Holt

  Bob writes, “Hey, Ted! I hope you can teach an old dog a new trick. I am trying to replace the CHAIN operation with SQL. I chain once to a file to read a certain record. If that record is not found, I chain again to retrieve a default record. How can I make SQL do a second read to the same file?”

  This is not a hard thing to do, as SQL has no problem joining more than once to the same table. I’ll show you two methods to retrieve your data. The first method is the easier one, …

  Read more

• SQL And Database Shine As Next Tech Refresh Approaches

  February 20, 2017 Dan Burger

  Twelve inches of fresh snow has piled up on your driveway and all you have is a shovel. That’s minimal functionality and, depending on your fitness level, your snow removal performance may be less than desirable. At best, you’re on the low end of the functionality and performance continuum and you’re wishing for better.

  So it is with the DB2 for i database and the programmers who shovel data there. Any help moving data from where it is to where it’s needed will likely be appreciated. Ditto for accelerating application behavior and gaining application functionality.

  This brings us to the …

  Read more

• Guru: SQL PL Labels

  February 13, 2017 Ted Holt

  SQL PL, the powerful, easy-to-learn procedural database language that IBM ships with all versions of DB2, allows you to identify any executable statement or compound statement with a label. I can’t think of a better day than today for us to think about why we might want to do that.

  As a minimalist programmer, I avoid useless code, and that includes unnecessary labels. In the following paragraphs, I will list only the cases that require labels.

  But first, a few ground rules.

  • As with CL, a label name is followed by a colon when used to identify a statement.

  …

  Read more

Previous Articles Next Articles