Check Your Query Selection

Sometimes in a CL program, you want to test whether or not records meeting certain criteria exist on a file and then do some special processing based on that result. In CL, you might use DCLF/RCVF, but you are allowed only one file per CL program. Another way is to write an RPG program to read the file and test the condition, but writing another program can be inconvenient. The Check Query Selection (CHKQRYSLT) command provides a convenient third choice.

CHKQRYSLT allows you to process a condition (via its QRYSLT parameter) against a file to test whether or not at least one record on that file matches the condition. The QRYSLT parameter of CHKQRYSLT has the exact same syntax as the QRYSLT parameter of Open Query File (OPNQRYF), including its ability to do complex conditions. Depending on the result of the test and the value in ESCAPE (another parameter of CHKQRYSLT), CHKQRYSLT issues either an escape message or a completion message. Specifying ESCAPE(*FOUND) causes CHKQRYSLT to issue a CPF9898 escape message if at least one record meets the QRYSLT criteria; otherwise, it issues a completion message. Specifying ESCAPE(*NOTFOUND)—the default—reverses the process by issuing either the CPF9898 escape message if no records meet the criteria or a completion message if records meet the criteria. The CL program can monitor for escape message CPF9898.

Figure 1 shows a sample program that uses CHKQRYSLT. The CL program issues a message if the user has not changed the password prior to the job date. The example program is not particularly useful other than to show how CHKQRYSLT works. In this example, the CL program very conveniently tests records on the OUTFILE from the Display User Profile (DSPUSRPRF) command.

Here is a more useful example: In a CL program, you can get a customer number and an item number from a display file and use CHKQRYSLT to verify that these two fields are on the Customer and Item master files respectively.

Here is another example: Before processing a group of records in batch, a CL program could use CHKQRYSLT to verify that such records are on file in order to avoid redundant processing.

CHKQRYSLT has two additional parameters. The STSMSG(*NONE) parameter suppresses OPNQRYF status messages from appearing at the bottom of the screen.

As you might guess, CHKQRYSLT uses OPNQRYF, but this parameter is in effect only while the command is processing. In addition, you can override the STSMSG default. The OPTALLAP parameter is for OPNQRYF optimization, if you wish. (For an

explanation of OPTALLAP, see “Programmer’s Toolbox: OPNQRYF Performance,” MC, May 1997.)

To create the utility, first compile the dummy file CH0001PF (which can be found on the MC Web site at www. midrangecomputing.com/mc), then compile the CHKQRYSLT command shown in Figure 2, and, finally, compile the CL program CH0001C shown in Figure 3. Note that this CL program uses the Forward Program Message (FWDPGMMSG) utility described in “How to Forward Messages in CL,” MC, January 1998.

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

/*--------------------------------------------------------------------*/

/* To compile: */
/* */

/* CRTCLPGM PGM(xxx/TSTCHKQRY) SRCFILE(xxx/QCLSRC) */
/* */

/*---------------------------------------------------------------------*/

PGM

DCL VAR(&USRPRF) TYPE(*CHAR) LEN(10)

DCL VAR(&JOBDATE6) TYPE(*CHAR) LEN(6)

DCL VAR(&JOBDATE7) TYPE(*CHAR) LEN(7)

RTVJOBA USER(&USRPRF) DATE(&JOBDATE6)

CVTDAT DATE(&JOBDATE6) TOVAR(&JOBDATE7) +

TOFMT(*CYMD) TOSEP(*NONE)

DSPUSRPRF USRPRF(&USRPRF) OUTPUT(*OUTFILE) +

OUTFILE(QTEMP/USRPRFP)

CHKQRYSLT FILE(QTEMP/USRPRFP) QRYSLT(' UPPWCC *CAT +

UPPWCD < "' *CAT &JOBDATE7 *CAT '"') +

ESCAPE(*FOUND)

MONMSG MSGID(CPF9898) EXEC(DO)

SNDPGMMSG MSG('You have not changed your password +

recently.')

ENDDO

ENDPGM /*---------------------------------------------------------------------*/

/* To compile: */
/* */

/* CRTCMD CMD(xxx/CHKQRYSLT) PGM(xxx/CHKQRYSLT) */
/* SRCFILE(xxx/QCMDSRC) */
/* */

/*---------------------------------------------------------------------*/

CHKQRYSLT: CMD PROMPT('Check Query Select Criteria')

PARM KWD(FILE) TYPE(OBJ) MIN(1) DTAARA(*YES) +

PROMPT('File to query (via OPNQRYF)' 1)

PARM KWD(QRYSLT) TYPE(*CHAR) LEN(2000) MIN(1) +

EXPR(*YES) PROMPT('Query selection +

expression' 3)

PARM KWD(MBR) TYPE(*NAME) LEN(10) DFT(*FIRST) +

SPCVAL((*FIRST)) PROMPT('Member' 2)

PARM KWD(ESCAPE) TYPE(*CHAR) LEN(10) RSTD(*YES) +

DFT(*NOTFOUND) VALUES(*FOUND *NOTFOUND) +

PROMPT('Escape w/CPF9898 if selection' 4)

PARM KWD(STSMSG) TYPE(*CHAR) LEN(7) RSTD(*YES) +

DFT(*NONE) VALUES(*NONE *NORMAL *USRPRF +

*SYSVAL *SAME) PMTCTL(*PMTRQS) +

PROMPT('OPNQRYF Status Messages' 5)

PARM KWD(OPTALLAP) TYPE(*CHAR) LEN(4) RSTD(*YES) +

DFT(*NO) VALUES(*YES *NO) PMTCTL(*PMTRQS) +

PROMPT('Optimize all access paths' 6)

OBJ: QUAL TYPE(*SNAME) LEN(10) EXPR(*YES)

QUAL TYPE(*SNAME) LEN(10) DFT(*LIBL) EXPR(*YES) +

SPCVAL((*LIBL)) PROMPT('Library')

Figure 1: This program shows how CHKQRYSLT works.

Figure 2: Use the CHKQRYSLT command to find out whether or not records exist in a file from a CL program.

/*---------------------------------------------------------------------*/

/* To compile: */
/* */

/* CRTCLPGM PGM(xxx/CH0001C) SRCFILE(xxx/QCLSRC) */
/* */

/*---------------------------------------------------------------------*/

CH0001C: PGM PARM(&FILE &QRYSLT &MBR &ESCAPE &STSMSG +

&OPTALLAP)

DCLF FILE(CH0001PF) /* Dummy filename */

DCL VAR(&ESCAPE) TYPE(*CHAR) LEN(10)

DCL VAR(&FILE) TYPE(*CHAR) LEN(20)

DCL VAR(&FILELIB) TYPE(*CHAR) LEN(10)

DCL VAR(&FILENAME) TYPE(*CHAR) LEN(10)

DCL VAR(&FOUND) TYPE(*CHAR) LEN(10)

DCL VAR(&MBR) TYPE(*CHAR) LEN(10)

DCL VAR(&NOTFOUND) TYPE(*CHAR) LEN(10)

DCL VAR(&OPTALLAP) TYPE(*CHAR) LEN(4)

DCL VAR(&QRYSLT) TYPE(*CHAR) LEN(2000)

DCL VAR(&RCDREAD) TYPE(*CHAR) LEN(1)

DCL VAR(&STSMSG) TYPE(*CHAR) LEN(7)

DCL VAR(&STSMSGSAVE) TYPE(*CHAR) LEN(7)
/******************************************************************************/

/* Initialization */
/******************************************************************************/

CHGVAR VAR(&FILENAME) VALUE(%SST(&FILE 1 10))

CHGVAR VAR(&FILELIB) VALUE(%SST(&FILE 11 10))

CHKOBJ OBJ(&FILELIB/&FILENAME) OBJTYPE(*FILE) +

MBR(&MBR) AUT(*USE)

IF COND(&ESCAPE *EQ *FOUND) THEN(DO)

CHGVAR VAR(&FOUND) VALUE(*ESCAPE)

CHGVAR VAR(&NOTFOUND) VALUE(*COMP)

ENDDO

ELSE CMD(DO) /* &ESCAPE *EQ *NOTFOUND */

CHGVAR VAR(&FOUND) VALUE(*COMP)

CHGVAR VAR(&NOTFOUND) VALUE(*ESCAPE)

ENDDO

RTVJOBA STSMSG(&STSMSGSAVE)

CHGJOB STSMSG(&STSMSG)

OVRDBF FILE(CH0001PF) TOFILE(&FILELIB/&FILENAME) +

MBR(&MBR) LVLCHK(*NO) SHARE(*YES)

OPNQRYF FILE((CH0001PF)) QRYSLT(&QRYSLT) +

IGNDECERR(*YES) OPTIMIZE(*FIRSTIO) +

OPTALLAP(&OPTALLAP) /* OPTALLAP(*YES) +

tells OPNQRYF to look at all logicals to +

find the most suitable. */
/******************************************************************************/

/* The Test. */
/******************************************************************************/

CHGVAR VAR(&RCDREAD) VALUE('1') /* Assume found. */

RCVF

MONMSG MSGID(CPF0864) EXEC(DO)

CHGVAR VAR(&RCDREAD) VALUE('0') /* Not found. */

ENDDO
/******************************************************************************/

/* Cleanup. */
/******************************************************************************/

CLOF OPNID(CH0001PF)

MONMSG MSGID(CPF4520) /* File not open */

DLTOVR FILE(*ALL)

CHGJOB STSMSG(&STSMSGSAVE)

RMVMSG CLEAR(*ALL) /* To tidy up the job log */

IF COND(&RCDREAD *EQ '0') THEN(DO)

SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('No +

records found for your selection +

criteria') MSGTYPE(&NOTFOUND)

ENDDO

ELSE CMD(DO)

SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('At +

least 1 record was found for your +

selection criteria') MSGTYPE(&FOUND)

ENDDO

RETURN
/******************************************************************************/

/* Error Handling: */
/******************************************************************************/

ERROR: FWDPGMMSG

MONMSG MSGID(CPF0000)

ENDPGM

Figure 3: CL program CH0001C is the command processing program for CHKQRYSLT.

What Model Is This?

Q: I see that the AS/400 model can be retrieved by viewing the system value QMODEL. Is there a way to find out the processor feature for the machine?

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

A: System value QPRCFEAT will give you the processor feature.

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

A: You can also use the command WRKHDWRSC TYPE(*PRC). Your system’s hardware feature options will appear in the resulting list.

— Shannon O’Donnell Senior Technical Editor

Midrange Computing

When Subfiles Expand...

Q: I have an expanding subfile that displays 15 records per page. The first page appears, I scroll up, the second page appears. I scroll back, the first page appears again (handled by the operating system).

Here’s the problem. I hit Enter, and the second page appears. What I want is for the page the user is on to appear. Where can I get the record number to place in the Subfile Record Number (SFLRCDNBR) field to make this happen?

— Greg Leister Design Systems Inc. This email address is being protected from spambots. You need JavaScript enabled to view it.

A: Use the Subfile Scroll (SFLSCROLL) keyword in your DDS subfile control record and move this value into your SFLRCDNBR field. The field associated with SFLSCROLL is the relative record number at the top of the current subfile page. For example, if your subfile shows 15 records per page and you are on page 2, the TOPRRN1 field will contain a value of 16. (See Figure 4.)

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

AAN01N02N03T.Name++++++RLen++TDpBLinPosFunctions+++++++++++++++++++++
A TOPRRN1 4S 0 H SFLSCROLL
A SFLRRN1 4S 0 H SFLRCDNBR(CURSOR)

Figure 4: Use the SFLSCROLL keyword to position your subfile on the proper page.

Need a Busy Signal?

I have worked on many applications that use a value in a data area to determine whether or not an application is running. The problem I have with this technique is that, if an application that checks/updates this data area ends abnormally, the value in the data area may be incorrect. That is, the next time a program runs and checks that data area, perhaps

to see whether it is busy, it may be getting bad data because the previous program didn’t get a chance to update the data area’s contents.

Rather than use a value within the contents of the data area, use the data area itself. Have your application allocate (*EXCL) the data area when it starts. If you do that, any other application that attempts to allocate that data area with an *EXCL lock will be unable to. In addition, the program can take some appropriate action to end its process or perhaps even wait until the data area’s lock is released by the first program.

What’s nice about this technique is that, if the first program terminates abnor-mally, the *EXCL lock will be released and the data area will no longer be allocated. Other programs can allocate the data area and put their own locks on it, thereby preventing programs from executing after they finish, but they will also prevent programs from attempting to get a lock on the data area.

— Greg Leister Design Systems Inc. This email address is being protected from spambots. You need JavaScript enabled to view it.

Treating Special Characters like They’re Special

If you use the AS/400’s HTTP server and have forms on your Web pages, you have probably noticed that special characters are not handled particularly well by the APIs provided by IBM (QtmhRdStin and QtmhCvtDb). These APIs translate special characters such as the “at” symbol (@), the slash (/), and the apostrophe (’) are translated into garbage. The reason this occurs is that the default configuration for the Common Gateway Interface (CGI) conversion mode is “mixed.” This tells the server to translate ASCII into EBCDIC unless the character is a special character. If it is a special character, the server translates it into hexadecimal.

Changing the conversion mode to EBCDIC fixes the problem. This can be done by using the Work with HTTP Configuration (WRKHTTPCFG) command and then adding the following line:

CGIConvMode %%EBCDIC%%

You can also change the settings online by entering http://youras400.com:2001/ in your browser and going to the Configuration and Administration menu. Figure 5 displays the screen for changing the settings.

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

Figure 5: Use your Web browser to fix the ASCII-to-EBCDIC conversion problem.

Phone Number Edit Code

I have developed for phone numbers an edit code that displays, in this order, the area code in parentheses, a space, and the phone number with a hyphen in the middle, like this: (123) 456-7890. The good thing about it is that missing phone numbers are displayed as blanks, not as (000) 000-0000. I assigned it to edit description 5 by using the Create Edit Description (CRTEDTD) command. (See Figure 6.) Now when you create a new display file field or printer file field and assign it to edit code 5, you’ll get a perfectly edited telephone number.

— Michael Daly

FreshPoint Inc. This email address is being protected from spambots. You need JavaScript enabled to view it.

Figure 6: Use the CRTEDTD command to create a perfectly edited telephone number.

Another Way to Avoid That Annoying Dial-up Window with TCP/IP

In the February 2000 issue of Midrange Computing, the TechTalk piece “Avoiding That Annoying Dial-up Window with TCP/IP” showed how to change registry entries to keep

your PC from trying to dial up your ISP every time you connect to your AS/400. Here’s an alternative that may work for you, too. From your Windows desktop, do the following:

1. Click My Computer.

2. Click Dial-Up Networking.

3. Delete all of your dial-up icons.

4. Start Client Access.

Using this method, you will get a new message about not using dial-up. Answer this question by stating that you will never use Dial-Up Networking again. This should prevent you from getting that annoying dial-up window.

— David H. Schlueter This email address is being protected from spambots. You need JavaScript enabled to view it.

Editor’s Note: Of course, using this method removes all Dial-Up Networking services from your PC, so use this technique only if you no longer need these services.

Excel Add-ins Are Easy!

Client Access provides a Microsoft Excel add-in for data transfer from the AS/400. I use Office 97 on NT workstations in my office and find this a very useful tool for putting AS/400 data into a spreadsheet. Once it’s there, this data is available to all of the Office 97 products, such as Word.

Installing the add-in couldn’t be easier, either. The Client Access online User Guide provides very good instructions on installing and using this add-in. Simply open the Client Access Express User Guide and type Excel Add-in in the Index search box. You’ll be presented some very short and easy instructions to follow on how to plug this add-in into your Excel spreadsheet.

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

Editor’s Note: If you already had Excel installed on your PC when you installed Client Access Express (Express client) and performed a Full or Typical install of Express client, the Data Transfer add-in was automatically installed for you. If you performed a custom install, you would need to select the Data Transfer Excel Add-in Support option manually.

Security Loophole with Group Profiles

If the security administrator’s user profile has the same group profile as the profiles the security administrator is creating, it is possible that any user in the group could use any other user’s profile belonging to that same group to submit jobs. This includes unauthorized use of the system administrator’s user profile for batch jobs. One does not need to know the password of the other user profiles to use them on the Submit Job (SBMJOB) command. With this type of abuse, one user could gain the rights of a more powerful user profile within the group when running a batch job.

When a system administrator who belongs to a group profile creates an object, OS/400 grants *ALL authority to the group by default. This rule applies to user profiles as

well, since they are also objects. Every user in the group is thus automatically granted access to every other user’s profile within the group.

On the SBMJOB command, there is a USER parameter for overriding the user profile running the job. This option allows one user to submit a job on behalf of another user, provided that the former has authority to use the latter’s user profile. In this case, everyone has *ALL authority to all profiles within the group.

How do you plug this loophole? One solution is to remove the security administrator from the group—that is, set the system administrator’s group profile (GRPPRF) to *NONE or assign it to a different group not used by “ordinary” users. Another solution is to issue the Revoke Object Authority (RVKOBJAUT) command:

RVKOBJAUT OBJ(user profile) OBJTYPE(*USRPRF) USER(group profile) AUT(*ALL)

You could use this command just after creating a new user profile assigned to the group. The Security Patrol column in the August 1996 issue of MC provides some background information. However, it does not specifically mention the loophole that exists when using group profiles.

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

Also Read

The Power of Writing Code in a Low-Code Solution

TechTip: Let’s Have a Date, Part 2

Going off the Rails