• Guru: SQL Functions Can Do Non-function Things

  January 27, 2020 Ted Holt

  We all know that the SQL SELECT statement only retrieves data, right? If you want to modify data, you have to use INSERT, UPDATE, DELETE, or MERGE, correct? Well, to quote the great George Gershwin, It Ain’t Necessarily So. You can modify data from a SELECT statement, and maybe sometimes you should. Here’s how it’s done.

  I’ll illustrate with a SELECT statement that will run the Reorganize Physical File (RGZPFM) command over physical files that have at least 10 percent deleted records. I could do this with plain ol’ CL, of course, and that’s probably the approach I would …

  Read more

• Guru: RDi V9.6, Part 6 – The New Object Table Gets Even Better

  January 20, 2020 Susan Gantner

  RDi V9.6 seems to be the gift that keeps on giving. I started this series about this release of RDi almost two years ago. You may have thought my last (fifth) tip in the series was the last on this subject, but it turns out there’s still more!

  I wrote an entire tip earlier on the new and greatly improved Object Table view. In a related tip, when discussing the PDM perspective, I said that I thought there were a few enhancements still needed to make the perspective a good tool for easing the transition from PDM for RDi …

  Read more

• Guru: Formatting Numbers and Dates/Times/Timestamps in SQL

  January 13, 2020 Paul Tuohy

  In this article, I want to share with you an SQL scalar function that I happen to have been using quite a bit recently. At times, when using an SQL select statement, you may want to format a number or date. Something along the same lines as using the %EDITC or %EDITW built in functions in RPG or the EDTCDE or EDTWRD keywords in DDS. In SQL we can use the VARCHAR_FORMAT or TO_CHAR (they are synonyms for each other – both work exactly the same way) scalar function to provide similar functionality.

  Since they are synonyms for each other, …

  Read more

• Guru 2020: Suggested Resolutions, One Prediction

  January 6, 2020 Ted Holt

  Do you eat candy made of underwear? How are your telepathy and teleportation skills? How long ago did you give up eating? How many choppers are on the family helipad? Is your chauffeur a gorilla? Read about these and other bizarre predictions for 2020 here.

  A new year is invariably accompanied by resolutions and predictions. I don’t intend to make any of either. However, if you’re into making resolutions, I’ve got some suggestions that you can take or leave, as you wish. As for predictions, I’ve got one that can’t miss.

  For RPG programmers who are still looking for …

  Read more

• Guru: More End Of Year Feedback

  December 9, 2019 Ted Holt

  You are busy. The people you serve need you to do more than one human being can do. You don’t have time to look for comments or updates to the articles we run in this august publication or any other. For this reason, I was pleased to publish some of your feedback in last week’s issue. This week I am pleased to share a bit more.

  In response to Guru: MERGE, Chicken, and Eggs, John asked a good question and made a good point:

  How is using this merge technique under commitment control any different than just doing the …

  Read more

• 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

Previous Articles Next Articles