• Guru: End Of Year Feedback

  December 2, 2019 Ted Holt

  The year has flown by. Before we know it, it will be 2020. The century is flying by, too. We’ve almost consumed a fifth of it. That seems like a good excuse to see what we might glean from some of your feedback. It’s been a while. More to come next week!

  Several readers wrote regarding the need to remove hard-coded values from programs. Jim brought up the problem of compile-time tables and arrays.

  I find cases where data is hard coded (state names, product categories are a few examples) for tables or arrays in dozens of programs.

  I wish …

  Read more

• Guru: Better Check Constraints

  November 11, 2019 Ted Holt

  This article has three purposes. If you use check constraints in your database, the purpose is to help you make better use of check constraints. If you don’t use check constraints, the purpose is to encourage you to use them and to point you in the right direction. If you already know all this stuff, the purpose is to goad you to email me and teach me something I don’t know.

  The purpose of check constraints is to keep invalid data out of the database. That may seem unnecessary. Isn’t that what the applications are supposed to do? Yes, but …

  Read more

• Guru: Remove Extra Blanks, Or Why I Attend Conferences

  October 28, 2019 Ted Holt

  Have you been to a conference lately? If not, you may be shortchanging yourself. I attend several conferences every year and I get immense benefit from them. I learn a lot, I get a break from the day-to-day, and best of all, I build relationships with other people.

  I recently attended the RPG & DB2 Summit in Minneapolis, where I met a bright young developer named Kevan Robinson. He was kind enough to share his version of a tip that I shared with him and other attendees. It’s a technique that I learned ages ago from Craig Mullins, a mainframe …

  Read more

• Guru: Debugging Common Table Expressions

  October 21, 2019 Ted Holt

  I cannot say enough good things about common table expressions. Words like wonderful and marvelous don’t begin to describe them. However, CTEs do add a bit of complexity to an SQL query, and when the result set doesn’t contain the correct results, any common table expression can be the culprit. Fortunately, debugging queries with common table expressions is not difficult.

  To illustrate what I mean, let’s assume we have a query that retrieves shipment information for one day. It involves a few tables:

  • a one-row table containing a shipment date
  • a table of shipment header information
  • a customer master table

  …

  Read more

• Guru: Move Objects with Access Client Solutions

  October 14, 2019 Dawn May

  Sending objects from one IBM i partition to another is a common task. Access Client Solutions’ IFS task makes it simple to copy or send objects. The IFS task was introduced in ACS in July 2016, so this is not a new development, but it may not be widely known.

  Perhaps you need to send a save file to another IBM i partition. There are several ways to do this. FTP and Secure FTP are often used, but using FTP to transfer IBM i objects involves all sorts of complications. The IBM i Knowledge Center dedicates an entire section on …

  Read more

• Guru: Edit Result Sets in Run SQL Scripts

  October 7, 2019 Paul Tuohy

  Before getting into the detail in this article, I want it to be clear that I do NOT (in any way) advocate the direct editing of data in a production database. But when it comes to a test database, then the ability to directly edit data is invaluable.

  Back in the days of System i Navigator, you could right click on a table, select the Edit option and a window would open containing the contents of the table. You could directly edit the contents of any cell. Rows could be inserted or deleted using the Rows option on the menu. …

  Read more

• Guru: MERGE, Chicken, And Eggs

  September 30, 2019 Ted Holt

  Which came first: the chicken or the egg? I don’t have time to ponder such trivialities. However, I am glad to know that SQL has a way to help me with chicken-and-egg database updates, i.e., when two statements need to run but each politely needs for the other to go first.

  Suppose you support an IBM i system that keeps up with inventory. It has an item master table (physical file) that stores general information such as a description, the standard cost, and the list price of an item.

  create table ItemMaster
    (ItemNumber     char(6),
     Revision       dec(3),
     Description    char(20),
     Cost           dec(5,2),
     

  …

  Read more

• Guru: Parsing JSON With DATA-INTO And A Twist

  September 23, 2019 Mike Larsen

  One of my recent projects required me to parse JSON returned from a web service. On the surface this sounded like a pretty easy task, but I quickly ran into a challenge. The JSON being retuned didn’t have a top-level element, and since I wanted to load the JSON into a data structure, my program couldn’t handle it.

  After some searching, I found that some of my options included changing the code in the parser (JSONPARSE) or using a totally different parser altogether. While both of these are viable options, I decided to take a slightly different route.

  Before I …

  Read more

• Guru: Enumerated Data Types In RPG

  September 16, 2019 Ted Holt

  IT has changed a lot since I entered the field several decades ago, but some things have not changed. I would read in those early days that COBOL was dead, and I read the same thing now. Yet COBOL is 60 years old and still going strong. Back then I heard RPG criticized as “Real Poor Garbage”. These days I hear it scorned as “legacy”, which I assume is supposed to mean the same thing. Yet today’s RPG is better than any of its predecessors for business programming.

  RPG supposedly does not have the features of modern languages. Maybe not, …

  Read more

• Guru: I’m A Number, You’re A Number, Everybody’s A Number

  September 9, 2019 Ted Holt

  Do you, like Bob Seger, sometimes feel that you are nothing more than a number? Me too. That’s because to many people, that’s exactly what we are. And if there’s one thing that computers are good at, it’s assigning numbers — to orders, to accounts, to invoices, to transactions, and of course, to people. Since we have to make the computer assign numbers, we may as well learn the modern way to do it.

  In my earliest days of programming, I would store the last assigned of a series of numbers in a data file. (The S/34 and S/36 …

  Read more

Previous Articles Next Articles