• Guru: Retrieving The Long And Short Object Name

  August 14, 2023 Bob Cozzi

  Many releases ago, IBM i received “Long SQL Names” for files and libraries. These new longer names (up to 128 characters) were well received by SQL enthusiasts, but largely ignored by the mainstream IBM i developer. As each version of IBM i emerged, more and more shops experienced one or more objects with a longer-than-10-character name.

  Recently I created a file named BOAT_TRAFFIC. This name is clearly longer than 10 characters. I used SQL DDL (the CREATE or REPLACE TABLE statement) to create the file. Using SQL DDL is the only real way to create an object with a long …

  Read more

• Guru: Object Usage Statistics

  July 10, 2023 Bob Cozzi

  A long time ago in a system far, far away, IBM added the Date Last Used to the Object Description for all objects on the system. The term “last used” means different things to different object types; for *FILE objects it means the file was opened or its description was changed, for *PGM objects it means the program was called, for other objects it generally means what you might think it means (viewed, retrieved/read, updated, etc.) The only exceptions are Device Descriptions which get updated when a *DEVD changes from “Vary On Pending” to some other status.

  The Date Last …

  Read more

• Guru: Binding Directory Entries

  June 5, 2023 Bob Cozzi

  I assume you’ve heard about *BNDDIR (Binding Directory) objects introduced circa 1994 with OS/400 V3R2. The infamous QC2LE binding directory is used by a huge number of RPG applications to access C runtime and unblocked MI functions such as system cvthc, cpybytes, and matmatr. You have probably seen RPG IV source code with the BNDDIR(‘QC2LE’) keyword on the header specification.

  I was one of the first developers outside of IBM to use Binding Directories for my own code. When I go back and look at my own RPG IV code created prior to mid-2007, well over 90 percent of it …

  Read more

• Guru: Watch Out For This Pitfall When Working With Integer Columns

  May 22, 2023 Gregory Simmons

  Remember that awesome jungle game where you had to guide the hero through a series of increasingly hard obstacles to gather treasure? Jumping over snakes, scorpions, and rolling logs, swinging on vines over alligators –your timing had to be just right. Pitfall Harry survived in a world of 255 screens, each of which were 160 x 192 pixels and a dazzling 128 colors! Well today, I want to make you aware of a pitfall which caught me off guard a while back. I hope this article will help you avoid my pitfall.

  There I was, writing my code, happy as …

  Read more

• Guru: Find Unused Objects On IBM i Using SQL

  May 15, 2023 Bob Cozzi

  I have a client that uses SQL iQuery for nearly all “Report” type applications since SQL iQuery Version 2. They asked if they could create a report that listed all the unused objects in their user libraries. They also wanted the option to output to Excel-compatible format if desired.

  Since SQL iQuery allows you to output the results of your SELECT statements to any of its supported formats, Excel-compatible is given.

  Note: the code for this article is avaialble on GitHub at this link.

  There were a couple of approaches. I could use our other product, SQL Tools OBJECT_LIST …

  Read more

• Guru: Speeding Up RPG By Reducing I/O Operations, Part 1

  May 8, 2023 Gregory Simmons

  Perhaps one of the easiest ways to speed up an RPG program is to reduce the number of I/O operations it needs to perform. In this article let’s explore one simple method for moving toward dataset processing.

  Here I have a simple RPG program. Okay, admittedly, we don’t often get to write “simple” RPG programs, but for this example, I have stripped the RPG program down to just the read loop so I can demonstrate the conversion.

  1     Dcl-f AcctMstr Usage(*Input) Keyed;
  2     Dcl-pr entry ExtPgm('RPGRPT1');
  3       n Packed(3:0);
  4     End-Pr;
  
  5     Dcl-pi entry;
  6       inBranch Packed(3:0);
  7     End-pi;
  
  8     

  …

  Read more

• Guru: String Manipulation Using SQL

  October 24, 2022 Mike Larsen

  Recently, I was working with an employee file where first and last name were concatenated with a comma separator. A requirement of the project was to parse the name and populate a different table where first and last name are separate columns. I won’t address the design of the employee file, but I will show how I completed the request.

  In Figure 1, I use the position string function via SQL to extract the first name.

  

   

   

  Figure 1. Extract first name

   

  In this example, I use the trim, substring, and posstr functions to position to the first …

  Read more

• Guru: Beware of SQL Precompiler Variables

  September 26, 2022 Ted Holt

  In a famous Henny Youngman joke, a patient says, “Doctor, it hurts when I do this,” to which the doctor replies, “Then don’t do that.” Corny jokes aside, I have spent decades trying to identify programming practices that hurt when I do them, and having identified them, cease to do them. A case in point is the misuse of the variables that the SQL precompiler defines in my RPG programs, variables such as SQLCODE, SQLSTATE, and SQLER3.

  “So what,” I hear you ask, “is the problem with these variables? ” Well, they’re global, and global variables are evil.   Global …

  Read more

• Guru: The SND-MSG Op Code And Message Subfiles

  September 12, 2022 Ted Holt

  If you’re one of the many IBM i programmers who still writes green-screen applications, think about how your programs communicate with the users. How do you tell a user that a value that he’s just entered is invalid, or that he needs to press a command key to proceed? I’ve seen several methods, but a common one — and my favorite — is to communicate through a message subfile.

  The nice thing about message subfiles is that I can report two or more messages at one time. I like the computer to find as many errors as possible and let …

  Read more

• Guru: Regular Expressions, Part 2

  August 15, 2022 Mike Larsen

  In the first part of this series, I showed how to replace characters in a string using SQL and regular expressions. This time, I’ll show other regular expressions that are available for us to use. The regular expression functions I’ll show are REGEXP_COUNT and REGEXP_LIKE and the examples come from production programs that I’ve recently implemented.

  In the first example (Figure 1), I want to get a count of the number of occurrences of a pattern within a string. To make this more interesting, I’m searching for two different patterns within a sting. To accomplish this objective, I used …

  Read more

Previous Articles Next Articles