This Doesn’t Have to Be the End, Darling

If you’ve ever created and run a report using Query/400, then you’ve seen this line at the bottom of your report:

* * * E N D O F R E P O R T * *

In general, most folks can live with this line on their report. There are times, however, when you don’t want to see this message. Here’s an easy way to replace it. Type in the Work with Message Description command (WRKMSGD MSGID(QRX1905) MSGF (QQRYMSG)) on a command line and press Enter. You’ll get a list of messages from the QQRYMSG message file, starting with message number QRX1905. This is the “* * * E N D O F R E P O R T * * ” message. Enter a 2 in front of this message ID to change it. Now, you can make the message equal to all blanks or put in your own customized message. Whatever you enter (blanks or something else) will appear on all Query/400 reports from this point forward until a PTF or new release changes it back. You can also change message number QRX1901, which is the query endof-report message that gets sent to your screen in an interactive query session.

— Shannon O’Donnell Associate Technical Editor Midrange Computing

Quantifiably Quicker Queries

We’ve all experienced a system that has ground to a halt from an out-of-control query. Usually, this happens because a poorly designed query was created and run. Sometimes, though, a long-running query can simply be due to a large number of records in a database. Whatever the reason, it would be nice to know ahead of time how long a query will take to run, as well as to receive suggestions on how to speed it up. Relax! There is a method you can use to determine how long a query will run, as well as to get suggestions on how to speed up the query processing.

Although it is little known, and even less used since the days of the S/38, there has existed a means to predict the amount of processing cycles a query will use. This is what’s known as the Query Predictive Governor. IBM didn’t make this feature available to the

general AS/400 population until recently, supposedly because it wasn’t 100 percent accurate. Having used it myself many times, however, I can tell you that its accuracy is close enough for the purpose of this tip. The Predictive Query Governor measures, or “predicts,” the number of CPU cycles a given query will take to process, and this tool can be used to fine-tune your queries.

If the number of CPU cycles used exceeds the limit set in the system value Query Processing Time Limit (QQRYTIMLMT) or at the job level set by the Change Query Attributes (CHGQRYA) command, the query will halt. You can use the query’s failure to find out how to improve performance, but to do so, you need to ensure that the query fails. The easiest way to do this is to set the query processing time limit to 0 in either the system value QQRYTIMLMT or in the job.

Now, run your query interactively. When you attempt to run the query after setting this value to 0, it will immediately time-out and fail with error message CPF427F: “Estimated query processing time xx exceeds limit 0,” in which xx is the actual number of CPU cycle seconds used by the query. Answer this message with a “C” to cancel it, and then display the job log. The job log will tell you how many CPU cycles were used by the query. To potentially improve the performance of the query, you must do one more thing.

In the job log, you may also see a message that says something like “Access path built for file FILENAME,” in which FILENAME is the name of the primary file used by the query. Position your cursor over this message and press the F1 key to display the second-level help. As you read through the message, you will see, beginning at the bottom of the page, a message telling you what fields from the primary file were used to build the new access path. This information is your key to improving the query’s performance.

Create a permanent index of this access path either by creating a logical file or by using the SQL command CREATE VIEW, and run your query again. Make sure you again set the query time limit to 0 before you run the query. You should now see a message showing you that the number of CPU cycles used by the query is significantly less than the number used on the first go-round. You now have a fine-tuned query!

— Shannon O’Donnell Associate Technical Editor Midrange Computing

Why Do You Need Client Access When You’ve Got FTP?

Here is a quick and easy way to download a source program from your AS/400 to your PC using TCP/IP and FTP. The best thing about it is that you don’t need to use Client Access/400’s Data Transfer programs. This method is especially useful for those installations that don’t have Client Access/400.

1. In Windows 95/NT, click on the Start button.
2. Click on RUN.
3. Type in FTP and click on OK. A DOS window will be opened on your desktop so you can enter FTP commands.

4. At the FTP> prompt, type OPEN 999.999.99.99 (999.999.99.99 indicates the IP address of your AS/400. This could also be the host name if you have defined a Host Table on your PC).

5. Enter your AS/400 user ID and password when prompted.
6. To download a source member named MYRPG1 from source file QRPGLESRC in library QGPL and rename it to MYRPG1.TXT on your PC, enter the following:

GET QGPL/QRPGLESRC.MYRPG1 C:MYRPG1.TXT

This command will “get” the source member from the AS/400 and copy it to the PC’s hard drive to a file named MYRPG1.TXT. Optionally, you can omit the drive letter, colon, and backslash, and the file will be copied to your Windows95/NT Desktop.

7. To end your FTP session, type QUIT or BYE. — Alon Fluxman
American Service Finance This email address is being protected from spambots. You need JavaScript enabled to view it.

OV/400 Is Dead! Long Live OV/400!

Office Vision/400, IBM’s host word processing software, has lots of neat features (such as a floating cursor point) that are not available in DDS. It’s hard to try to replicate this type of functionality with the standard block-mode data transfer capability available to you with standard DDS.

There is a way to put a dumb terminal into OV/400-like mode. Use the User Defined (USRDFN) keyword in your DDS display file to send the Write Structured Field (X’F3’) command along with Text Support commands. When you use the USRDFN keyword in your display file, you are telling OS/400 that your program will handle all formatting, reading, and writing of the 5250 data stream to the display. The IBM 5494 Remote Control Unit: Functions Reference manual contains all the specifications you will need to make this work.

Some of the more interesting Work Station Function (WSF) commands available to you on dumb terminals using the USRDFN keyword are as follows:

• Define Audit Window—activates pop up balloon help as the cursor moves over a
• Define Command Key—allows you to program the DEL or other keys to perform textprompt/ locate/copy/move/delete/hyphenate

• Read Text Screen—allows you to capture every keystroke
• Define Special Characters—allows you to display required carriage return symbols

• Define Fake DP Command Key—allows you to respond to combinations, such as CMD/U (underline), CMD/B (bold), CMD/J (normal).

Now that IBM has announced the eventual end of support for OV/400, maybe some creative individual will find a new use for the WSF APIs.

— Joe Pluta Nexgen Software Technologies, Inc. This email address is being protected from spambots. You need JavaScript enabled to view it.

Who’s Calling, Please?

If your program needs to know the name of the program that called it, one method you could use is to send a message and then immediately receive the reply. This would return the name of the program that the message was funneled back up to. However, this isn’t the best way to go about this task because the method fails when your program hits the top of the call stack. You’ll get error message CPF24A3: “Value for call stack counter parameter not valid.”

Your program could run at the top of the call stack if it is the transfer group program in a secondary group job, or if it is a routing entry program that you invoke with routing data.

Here’s a better way to get the caller’s name. First, run the REXX procedure shown in Figure 1. It will create a small MI language program in library QGPL named RTVCALL.

When a program needs to know its caller’s name, call QGPL/RTVCALL, passing it one 20-byte parameter. RTVCALL will return the name of the calling program in the first

field

10 bytes and the name of the library in which it is stored in the last 10 bytes. If your program is at the top of the call stack, RTVCALL returns blanks in the parameter.

— Gene Gaunt This email address is being protected from spambots. You need JavaScript enabled to view it.

Your Confirmation Number Is...

For many interactive programs, pressing the Enter key means, “Edit the values I’ve entered, and if OK, proceed with the transaction.” Often, though, I want the screen format to redisplay the current values and issue a message like “Press Enter to confirm” before proceeding. An example of this would be a transaction that accepts a company code, and when Enter is pressed, the full company name is displayed so that I can be sure the code I entered matches the company I intended. If I press the Enter key again, then I want the program to proceed with the transaction. However, if I change the company code, I want the screen to redisplay with the new company name. In other words, I want the format to redisplay whenever some field values change.

A technique I use to keep this simple is to define a data structure consisting of the display fields I want to test for change, and a character field to record the most recent value of this data structure. Assume I have a display format with the fields X_COY#, X_AREA, and X_AMT. I want the format to redisplay if either X_COY# or X_AREA change. I code the data structure as shown in Figure 2.

I don’t have to define X_COY# and X_AREA in the data structure because they are already defined in the DDS of the display format. Similarly, the data structure X_DS needs no further definition as it consists of two subfields that are already defined. Finally, X_DS_CPY needs no specific definition as it is defined like X_DS.

In the body of the display program, I set X_DS_CPY to the value of X_DS immediately before I display the screen format, creating a snapshot of the screen fields prior to any changes the user may make (see Figure 3).

Finally, as part of my “Enter pressed” logic, I compare the “snapshot” X_DS_CPY with the current display field values in X_DS (see Figure 4).

The simplicity of this technique becomes apparent when I add new fields to the format. Say I want the format to redisplay if the field X_AMT changed. I just add it to the data structure X_DS, and no other logic change is necessary.

— John V. Thompson This email address is being protected from spambots. You need JavaScript enabled to view it.

Valueless Date Fields Must Have a Valid Value (and Other Mysteries)

Here are some quirks I’ve discovered with the way RPG IV works with fields of the date data type:

• The date data type fields can never be left blank or zeroed. The easiest way to set an acceptable value is to ‘Clear’ the date field. This cannot be done if the %NullInd = *ON.

• The %NullInd built-in function (BIF) can be used only with database fields. Program-defined fields may contain cleared data, but the %NullInd cannot be used to condition the contents of the data.

• The %NullInd BIF is similar to the modified data tag (MDT) that is associated with display fields. The indicator cannot be seen along with the data. You must do special testing to determine the condition of the data element.

• The data in the field cannot be used in any way when a data field has the null attribute set in the *ON condition. It is always best to test the condition of the null indicator prior to attempting to use the data. If the contents of a known “allow nulls” field is to be changed unconditionally, always set the null indicator to the *OFF condition first.

— Bill Green This email address is being protected from spambots. You need JavaScript enabled to view it.

Excuse Me, May I Share Your Subfile?

Q: Is it possible to read a subfile that resides in program A from another program that is called from program A?

— Michael Rosenberg This email address is being protected from spambots. You need JavaScript enabled to view it.

A: Yes, it is. Here’s the technique I use. It works with separate programs or procedures. I’ll illustrate using procedures.

You’ll need to make the display file share an open data path. To do that, use the Override with Printer File (OVRPRTF) command, specifying SHARE(*YES) before calling the first bound RPG module.

I use a CLLE module to perform the override and issue a call procedure (CALLPRC) to the first RPG module. This RPG module fills the subfile and displays it. I then select a record in the subfile to display. The first module then does a call bound (CALLB) procedure to the second RPG module. The second RPG module then does a Read Changed Record (READC) operation and reads the selected record.

— Mark McCall This email address is being protected from spambots. You need JavaScript enabled to view it.

Avoid Client Eyestrain/400

Many times after you configure a Client Access/400 PC5250 session and begin using it, you need to print whatever is on the screen to your local printer. If you are using the default printer settings, you may find that the printed output is so small, you can’t read it. There’s an easy way to fix this; just change the font used by PC5250 for printing to use Courier New. You can do this by following these steps: 1. Bring up the PC5250 display session.
2. Select File/Printer Control/Text on the PC5250 menu bar.
3. Click on the down arrow next to Font and select Courier New out of the list; then, click on the OK button to close the Printer Control dialog box.

Now, your screen prints should print in a readable size. — Shannon O’Donnell
Associate Technical Editor
Midrange Computing

%ELEMentary Array Techniques

I try to write code that won’t get messed up when I modify it. Here are two techniques I use to avoid changing code when working with arrays. (These techniques also apply to tables, which I rarely use.)

The first technique is to define array dimensions with named constants rather than with numeric literals. Suppose I write a program that accumulates numbers in an array of six elements. I might use code like that shown in Figure 5. Suppose that I later have to increase the number of elements from six to eight. I have to change four lines of code (not counting the compile-time array data). If I miss one, the program won’t work correctly.

If, instead, I code it as in Figure 6, I’ll make only one change. I’ll change the value of NbrOfRegions from 6 to 8.

There may be other modifications, of course. I may have to add a couple of newfields to a screen or a report line, for instance. I’ll need to add more regions to the compile-

time arrays. Those are changes I am not likely to overlook. At least my do-loops will continue to loop through all the elements, and the RegionName array will continue to have the same number of elements as SalesRegion.

The second technique is to use the %ELEM function to avoid using literals. %ELEM returns the number of elements in an array. As illustrated in Figure 7, using %ELEM ensures that alternating tables have the same number of elements.

— Ted Holt Senior Technical Editor Midrange Computing

/*********************************************************************/

/* LANGUAGE - REXX */

/* FUNCTION - one-time procedure to create MI program QGPL/RTVCALL */

/* AUTHOR - Gene Gaunt */

/* */

/* use STRREXPRC to execute one time */

/* then call RTVCALL with one 20-byte parameter from HLL program */

/* RTVCALL returns program & library name of caller */

/*********************************************************************/

MI =,

'DCL SPCPTR ?NAME PARM; ' ,

'DCL DD NAME BAS(?NAME) CHAR(20); ' ,

'DCL OL MAIN (?NAME) EXT PARM MIN(1); ' ,

'DCL DD MAT AUTO CHAR(25632); ' ,

'DCL DD SIZE DEF(MAT) BIN(4) INIT(25632); ' ,

'DCL DD TOTAL DEF(MAT) POS(9) BIN(4); ' ,

'DCL DD LEVEL(200) DEF(MAT) POS(17) CHAR(128); ' ,

'DCL SYSPTR ?PGM(200) DEF(LEVEL) POS(33) AEO(128); ' ,

'DCL SPCPTR ?MAT AUTO INIT(MAT); ' ,

'DCL DD CALLER AUTO BIN(4); ' ,

' ENTRY * (MAIN) EXT; ' ,

' CPYBREP NAME, " "; ' ,

' MATINVS ?MAT, *; ' ,

' SUBN CALLER, TOTAL, 1; ' ,

'LOOP: SUBN(SB) TOTAL, 1 / NPOS(BYE); ' ,

' CMPPTRE(B) ?PGM(TOTAL), ?PGM(CALLER) / EQ(LOOP); ' ,

' MATPTR ?MAT, ?PGM(TOTAL); ' ,

' CAT NAME, MAT(12:10), MAT(44:10); ' ,

'BYE: PEND;'

Size = D2C(Length(MI),4)

"CALL QPRCRTPG (&MI &Size 'RTVCALL QGPL ' ",

" 'Retrieve Program Name of Caller ' '*NONE' ",

" ' ' ' ' 'QSYSPRT *LIBL' X'00000001' '*ALL' ",

" '*LIST *REPLACE ' X'00000002' ) "

Figure 1: A REXX procedure to retrieve the name of a calling program

D X_DS DS

D X_Coy#

D X_Area

D X_DS_Cpy S Like(X_DS)

Figure 2: List of fields that will cause screen redisplay if changed

C Eval X_DS_Cpy = X_DS

C Exfmt Scrfmt

Figure 3: Saving the screen field value

C Callp Edit

C If ErrorCount>0

C Return

C Endif

C If X_DS_Cpy X_DS

C* ...Display "Press Enter To Confirm" message

C Return

C Endif

Figure 4: Logic to test for changed screen fields

D RegX s 10i 0

D SalesRegion s 3 dim(6)

D ctdata

D RegionName s 12 dim(6)

D alt(SalesRegion)

D TotalSales s 11 2 dim(6)

C 1 do 6 RegX

... do something for each element of the arrays

C enddo

**

N North

S South

E East

MW Midwest

W West

PACPacific

Figure 5: This code is not easily changed

D RegX s 10i 0

D NbrOfRegions c const(6)

D SalesRegion s 3 dim(NbrOfRegions)

D ctdata

D RegionName s 12 dim(NbrOfRegions)

D alt(SalesRegion)

D TotalSales s 11 2 dim(NbrOfRegions)

C 1 do NbrOfRegions RegX

... do something for each element of the arrays

C enddo

**

N North

S South

E East

MW Midwest

W West

PACPacific

Figure 6: This code needs only one change if the number of Sales Regions changes

D TabSalesRegion s 3 dim(6)

D ctdata

D TabRegionName s 12 dim(%elem(TabSalesRegion))

D alt(TabSalesRegion)

... more code

**

N North

S South

E East

MW Midwest

W West

PACPacific

Figure 7: The %ELEM function ensures that these alternating tables have the same number of elements

Also Read

The Power of Writing Code in a Low-Code Solution

TechTip: Let’s Have a Date, Part 2

Going off the Rails