• Guru: SQL Can Generate A Series

  March 16, 2020 Ted Holt

  Most of my work is run-of-the-mill, same-old-same-old, more-of-the-same. I’m not complaining. Most of life is mundane and routine, and that’s as it should be. Too much icing ruins the cake. But sometimes I get a challenge, and when that happens, programming can be fun.

  Today’s article comes out of such an experience. I needed a table with a week’s worth of dates in it. I could have written an RPG program, but I knew that SQL could handle the task. Today I’ll show you a couple of methods that you can use to generate a series of whole numbers and …

  Read more

• Guru: In Pursuit Of Reasonable Data

  March 9, 2020 Ted Holt

  My awareness of the indifference of computers toward the reasonableness of data dawned when I enrolled in the university. The band director told me to sign up for band and jazz band for zero semester hours. He said the stupid computer wouldn’t know that no one takes a course for zero hours, wouldn’t flag me to obtain permission from the dean to carry an overload, yet would grant me the credit. He was right.

  Love ‘em or hate ‘em, we live with them, we learn to get around them when they limit us, and we deal with them when they …

  Read more

• Guru: Practicing Safe Hex in RPG

  March 2, 2020 Jon Paris

  In this tip I’m going to address a question that arises regularly on RPG-oriented Internet lists, namely: “Is there an easy way to convert a character string to its hexadecimal equivalent?”

  One answer, of course, would be to write your own routine using lookup tables, but there is a far easier way. We can take advantage of the system’s hex MI APIs. These were originally surfaced for use by C and C++ but, thanks to the joys of ILE, can be used by any ILE language. Not only that, RPG’s prototyping support makes them really easy to use. In fact, …

  Read more

• Guru: Alternate SQL Row-Selection Criteria Revisited

  February 24, 2020 Ted Holt

  At the RPG & DB2 Summit last October, I chanced to overhear Rob Bestgen of IBM telling Paul Tuohy of System i Developer about a practice that he and others in IBM Lab Services had seen, a practice which they found horrifying. “Oh, no!” I thought. “I’ve written about that for itjungle.com!” Are you ready to be horrified?

  Rob was telling Paul that they have seen SQL queries in which portions of the WHERE clause are enabled or disabled through the use of switches. I did not invent this technique — I realized more than a few years ago that …

  Read more

• Guru: Using Identity Columns For Complex Keys

  February 17, 2020 Paul Tuohy

  In September 2019, Ted Holt published an article that demonstrated how an identity column can be used to generate a unique key in a table. In this article, I want to expand on that theme and examine another use of an identity column — replacing complex keys.

  Using an identity column in place of a complex key makes for much faster joins between tables, as you are joining based on two numbers as opposed to values of multiple columns. Identity columns also make for joins that are easier to comprehend, since they are based on just one column. This approach …

  Read more

• Guru: Copy OUTQ To PDF

  February 3, 2020 Bob Cozzi

  A long time ago I created a CL command named Copy from OUTQ (CPYOUTQ). This command allowed you to selectively copy spooled files from one OUTQ to either another OUTQ or to the IFS as a PDF or text file. My customers use it all the time for monthly archiving of spooled files and redistribution of output. You may have it on your own system.

  Being one of the handful of original advocates for the so called “Openness APIs” for IBM OS/400 (now IBM i), I quickly embraced the system APIs and have used them extensively throughout the decades. One …

  Read more

• 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

Previous Articles Next Articles