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
/*--------------------------------------------------------------------*/
/* 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
A: System value QPRCFEAT will give you the processor feature.
— Chris Scholbe
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.
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
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.
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
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.
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
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
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
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online