Ted Holt
-
Easily Calculating Statistical Functions
December 6, 2006 Hey, Ted
… Read moreThe code associated with this article is available for download.
Would you be interested in publishing a program that calculates statistical values, such as average, standard deviation, maximum value, and minimum value? I have such a program, which I use in to calculate average usage and standard deviation of the usage of the items we handle.
–Victor Pisman
Thanks to faithful reader Victor Pisman for an interesting program. When I think of calculating an average, I think in terms of accumulating an amount and counting before dividing. Victor took a different approach, and I like it.
Victor’s RPG program,
-
Create Database Files from SQL Queries
November 15, 2006 Hey, Ted
… Read moreWhile using interactive SQL/400 from a green-screen session, I can direct the output of an SQL command to a database file. Is there a way to direct the output of a dynamically created SQL command to a database file from a batch job?
–Rich
Yes, you have a few options. First, let’s review the steps you use in your interactive session, for the benefit of the readers.
From the interactive session, press F13 to access the Services panel. From there select option 1 (change session attributes), and enter 3 at the SELECT output prompt. Fill in file name, library, and
-
Stream Files and End-of-Line Delimiters, Take Three
November 15, 2006 Hey, Ted
… Read moreI do a bit of development in Cygwin under Windows and then move it to the iSeries using Qshell. It turns out that Qshell doesn’t like the carriage-return/linefeed combination (CRLF) in shell scripts, so I often need to change CRLF to LF–as you wrote about in two previous articles–in bulk. Here are some methods I use to convert end-of-line delimiters in stream files.
The first method is a simple Perl command.
perl -i.bak -pe 's/rn/n/g' *
This command adds the extension .bak to the end of the files and strips the CRLF, putting in LF instead. By using a wildcard,
-
Finding Large IFS Directories
November 8, 2006 Hey, Ted
… Read moreThe Integrated File System (IFS) continues to grow in importance in our shop. That means it also continues to grow in size. How can we determine which directories are largest when it comes time to clean them up?
–Dan
Use the Start Qshell (STRQSH or QSH) command to open Qshell. Use cd to navigate to any directory, then try this command.
ls -alsF | grep /$ | sort -r
Clear as mud, huh? Let’s look at it in detail. The ls command lists the files in a directory. In this case, the directory to be listed is the current directory.
-
Stuffing Five Digits Into Four, Take 2
October 25, 2006 Hey, Ted
… Read moreYour recent article, Stuffing Five Digits into Four, is a trip in the way, way back machine for me. I had to do this very thing on System/3 in RPG II! I’m writing to let you know that there is a way to stuff those five digits into four using native I/O.
As you’ve discovered, RPG’s ‘B’ data type limits you to 4 digits; it’s in the manual, but that’s where data types ‘B’ and ‘I’ differ. ‘B’ is intended to preserve x digits, from 0000 to 9999 (including sign) but the newer ‘I’ is intended to emulate the
-
The SPACE Function Takes Up a Lot of Space
October 18, 2006 Hey, Ted
… Read moreI have to build a file for input to another system. All fields are fixed-length, and numeric values are to be edited and right-adjusted. SQL’s CHAR function takes care of the editing requirement, but the result is left-adjusted. How do I right-adjust an edited numeric value? Or will I have to rewrite my program using native I/O operations?
–Sarah
On the surface, this seemed like an easy request. It turned to be a little more complicated than I had thought.
I suggested Sarah use the SPACE function to generate enough leading blanks to force right alignment. For example, the following
-
CL Odds and Ends
October 11, 2006 Hey, Ted
… Read moreI hope this question is not too simple for a newsletter that claims to be for gurus. An RPG program or module retains variables, open data paths, and other resources from one invocation to the next, as long as the LR indicator is not on. CL programs and modules, on the other hand, suffer from Alzheimer’s disease. They don’t remember anything from one invocation to the next. How do you handle this limitation?
–Richard
I can help you with part of this problem, Richard. Create a temporary data area to hold the variable values from one call to the next.
-
Use SQL to Remove Extra Spaces
October 11, 2006 Hey, Ted
… Read moreThe SQL sorting tip you published on September 13 was a good one. I had seen a similar tip written by Craig Mullins in Quest Software’s Pipeline Newsletter. Mullins had two other interesting tips on the same page. I thought your readers might like to see them as well.
–Tom
The Web page to which Tom refers is at http://www.quest-pipelines.com/newsletter-v7/0606_F.htm. Craig Mullins uses the technique about which I wrote to sort data on three-letter day abbreviations.
Another tip he presents involves using the REPLACE function to remove extra spaces within a character string. IBM added the REPLACE function
-
Kevin Juenemann’s Quick Query Utility
October 4, 2006 Hey, Ted
… Read moreNote: The source code for this article is available for download
Your QF command works great when you want to see the raw data, but sometimes you need only some of the columns, or need to join files to get usable data rather than codes. Many of our customers don’t have interactive SQL installed on their systems, so I created this utility and sent it to all our customers for those times a one-off report is needed quickly.
I can enter an SQL Select statement and a printer ID, and the utility will create a Query Management Query and run
-
Remove Blank Lines from a Stream File
October 4, 2006 Hey, Ted
… Read moreWorking with IFS files, especially those we get from other systems, greatly increases my appreciation of physical files. Opening a stream file is like opening a Christmas present–we never know what we’ll get. Anyway, one source typically sends us stream files that often have blank lines in them, which messes up our process. Is there any way to delete those blank lines?
–Ray
Well, Ray, I don’t know of a way to delete them, but if you’re willing to copy the stream file to another stream file and omit the blank lines in the process, you can take advantage of