• Guru: A Simple, Effective Way To Compare Data Using SQL

  April 20, 2020 Ted Holt

  One thing I have learned over the years is that no matter how much technology changes, people always ask the same questions like: “Why does the computer say we have 50 widgets when we only have 45?” and “Can you tell me who changed the due date of order number 12345?” and “How do sales of widgets this year compare to the same time last year?” It is the last of these that I wish to address today.

  Comparing data is a very common activity. Everybody compares data: sales people, production people, purchasing people, and of course management people. This …

  Read more

• Guru: Copy From Stream File FMTOPT(*NOCHK)

  April 13, 2020 Bob Cozzi

  Using SQL frequently, as I do, I tend to look for solutions to problems that can be resolved using the database language (i.e., SQL). Sometimes I have to invent a missing piece of the puzzle. Often that entails writing a User-Defined Function (UDF) for use in SQL, but sometimes a CL command is the better choice.

  Recently I had a situation where what the client calls an “EDI File” was being downloaded using sftp on the IBM server running i. This file is created at a vendor that runs IBM mainframes (an actual mainframe by the way) and the file …

  Read more

• Guru: SQL Checks For Control Breaks

  April 6, 2020 Ted Holt

  A control break occurs when the combined value of one or more fields changes from one row (record) to the next when reading a data set sequentially. I used to write RPG programs with control breaks often. Now that reports are less common, I write them less often, but that’s not to say I never write a program with control breaks.

  When I first learned to handle control breaks in RPG, I used the L1 through L9 level indicators. These worked wonderfully and fed my family for several years. When I moved from the System/36 world to the S/38 (and …

  Read more

• Guru: Calling RPG Programs From Python, Part 1

  March 30, 2020 Mike Larsen

  In a prior article, I showed how to pass parameters to a Python script and execute the script from an RPG program. Based on feedback and my own curiosity, I wanted to see how I could pass parameters to an RPG program and call it from Python. After a bit of research, I found the Python interface itoolkit.

  itoolkit is an open source project provided by IBM as an interface to the XMLSERVICE toolkit, which allows us to call RPG programs, service programs, CL programs, and PASE Shell commands. itoolkit can be installed using an SSH terminal with …

  Read more

• Guru: Maximum Number Of Jobs And Job Table Warning Customization

  March 23, 2020 Dawn May

  The QMAXJOB system value specifies the maximum number of jobs that can exist on your IBM i partition. The default value is 163,520, with the valid range being 32,000 through 970,000. If you reach the maximum number of jobs as defined by this system value, very bad things can happen to your system. You can always sign on to the console as the system reserves one job table entry for the console, but if you hit the limit on the maximum number of jobs, you will not be able to start any new jobs. In the worst case, it will …

  Read more

• 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

Previous Articles Next Articles