02
Sat, Nov
2 New Articles

TechTalk

General
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


TechTalk406-00.png 266x190

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.

TechTalk406-01.png 395x225

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.


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: