04
Mon, Nov
1 New Articles

Set-at-a-time Power Programming

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

You can boost productivity by mastering tools that work with data in sets, rather than by individual records. This article explains how you can solve complex query requests quickly by combining sets of data.

I learned to program at Itawamba Community College, in Tupelo, Mississippi. The year was 1982, the computer was an IBM System/3 Model 12, and the language was RPG
II. I was taught to program in terms of single record I/O. That is, each read against a file

copied the contents of one record into my program, and each write or update to a file sent output to only one record.

Since then, relational databases have become the industry standard. Relational databases are designed to work with sets of data, yet many programmers who produce business applications still think only in record-at-a-time terms.

I want to show some ways set-at-a-time processing has helped me on many occasions to solve real-world problems quickly. If you have never thought in terms of setat-a-time processing, I hope this will be of benefit to you.

What Is a Set?

A set is a group of things that share one or more common attributes. For example, where you work, there are lots of sets of people:

• People who wear glasses
• People who don’t wear glasses
• People who have red hair
• People who arrive late, take extended lunch breaks, and leave early
• Female people who are over two meters tall and have Hispanic surnames The list could go on and on, because there are so many different ways to classify things.

A set can be empty. An empty set means that no one fits all the criteria required for membership in the set. The last example in the list above, female people who are over two

meters tall and have a Hispanic surname, would likely be empty in many places of employment, because not many people fit that description.

An entity can have membership in more than one set. An employee might belong to these sets:

• Salaried employees
• Male salaried employees
• Purchasing agents
• Employees who participate in the corporate stock purchase plan
• People who know how to open the microwave door when the latch gets stuck

How to Process Sets

To process records one at a time, you need a compiler or interpreter with operations like read, read prior, write, update record, and delete record. When you process sets of records, you need other tools and operators.

To process sets, you need relational tools, like SQL, OPNQRYF, or Query/400. You tell relational tools what you intend to do and what set of data you want them to do it to. For example, you can delete all employees who were terminated before January 1 of the current year, or give a 20 percent pay increase to all RPG programmers who have been contacted by at least four headhunters within the last week.

Not all relational tools are created equal. SQL will read, write, update, and delete sets of records. Query/400 only reads and writes. OPNQRYF can’t do any actions, but depends on an accompanying HLL program or CPYFRMQRYF command to do that. I’m going to use SQL to illustrate set-at-a-time programming. SQL supports the four data retrieval and manipulation operations just mentioned, requires no source code or interactive screens to be filled, and, when combined with a CL driver program, makes quick work of some complicated programming tasks. Since SQL/400 doesn’t work with CL, I’ll be using the Execute SQL Statement (EXCSQLSTM) command we published in December 1994 (see “The EXCSQLSTM Utility”). If you don’t have the code, you can get it from the Midrange Computing Resource CD or from our Web site at http://www.midrangecomputing.com.

I’m aware that a lot of shops don’t use SQL, but I hope more shops will begin to use it as a result of reading this article.

Now let’s look at a couple of examples of set-at-a-time programming.

When Is an Outer Join Not an Outer Join?

Inner joins differ from outer joins in how they treat unmatched data. An inner join does not include records from the primary file if no match is found in the secondary file(s). A left outer join, the only kind of outer join DB2/400 supports, includes data from the primary file, even if no matching data is in the secondary file(s).

Suppose some important person asks you for a list of all inventory items with a price code of PT or PV, and any sales of those items. This important person wants to see all items of those classes, whether they have been sold or not. You might use an outer join like that in Figure 1.

This join works great. You can see the results in Figure 2. Notice that item K287 was included, even though it hasn’t yet been sold.

Now, suppose the VIP tells you he only wishes to see sales of June 1998. He still wants to see all items with a class code of PT or PV. Your first impulse might be to add a where clause to restrict the table, as in Figure 3, but that won’t yield the results you want, because the where clause references a field in a secondary file.

In fact, different relational database management systems could yield completely different results in a case like this. Many of them will see that a where clause references a

secondary file and will change the query to run an inner join instead of a left outer join. Others go crazy. I ran this query in SQL/400 and got erroneous results that made no sense at all.

Some relational DBMSs allow mixed join types in one query. That is, you can join files A and B with an inner join, and B and C with a left outer join, in the same query. DB2/400 permits only one join type in each query, so that’s not an option in this case.

Set-at-a-time programming can eliminate this confusion. Figure 4 contains a short CL program that manipulates sets of records. The first EXCSQLSTM command combines June 1998 sales data for items of the desired classes from the invoice header and invoice detail files. I store the invoice information in a temporary file called TEMP01, in library QTEMP.

Then, I join the item master file to the temporary file. I use a left outer join here, because I want all items of classes PT and PV to appear in the result set, even if there were no June sales.

How do you like that? This program produces a report from only two executable statements.

Another Example

I’m going to complicate the example for two reasons. First, I want to show you that complicated queries are not really complicated; and second, most of the set-at-a-time programs I’ve had to write were not this simple.

Management has decided to raise prices. They have selected some items of types PT and PV and keyed the proposed new prices into a spreadsheet. Your job is to produce a report to aid them in making their decision. They will look at your report, adjust prices in their spreadsheet, give you the new spreadsheet, and ask for a fresh report. This process will continue until they’re satisfied with the new prices.

The report must show the following: item number; item description; how many sales were made of each item; the quantity sold between July 1, 1997, and June 30, 1998; how much those items sold for (actual sales dollars, not unit price); how much the items would have sold for at the new proposed prices; and the difference between the two sales figures. If there is no new price, use the current sales figures. Total the sales figures and difference columns, and print some asterisks to the right of any line where the increase is greater than 10 percent. Sort it by actual sales dollars in descending order.

I once had a guy ask me for something like this back in my S/36 days. I told him he needed a magician, not a programmer. However, since I started working with the S/38 and AS/400, I’ve never turned these requests down.

Anyway, let’s look at how you’d solve this problem. First, you need to determine where the data is. You already know about the ITEMS, INVHDR, and INVDTL files, but you don’t have a file for the new prices. You create another file, NEWPRICE, save the spreadsheet in a comma-separated values (CSV) file, and upload the CSV file into NEWPRICE, using a data transfer program like Client Access.

Now that all the data is on the AS/400 in database files, you’re ready to start designing the program. There are several ways you could extract and combine the data, but here’s one, shown in Figure 5 using EXCSQLSTM and Query/400.

This program uses EXCSQLSTM to build work files in QTEMP. I’ve named the work files “TEMPnnn”, where nnn is a three-digit number. I’ve incremented the numbers by 10, because the people who request these reports change their requirements as they work with the reports, and I often have to add a new work file to the series. I like the work files to be named in the order I build them, so I leave room for insertion.

Here’s how the program works:
1. Get all items of types PT and PV, along with new prices. If there is no new price for an item, set the new price to zero.

2. Summarize the sales information for the desired items over the specified period of time. For any items that have no sales records, add records with zero sales figures. This lets me use an inner join to join to the item information. My experience is that less tends to go wrong with inner joins than with outer joins, so I prefer to use inner joins when possible.

3. Combine the item and sales data.
4. Build the calculated columns: projected sales, projected increase, and large increase flag.

5. Print the report. Since EXCSQLSTM has no method for accumulating column totals, I use Query/400 for this step.

The resulting report is shown in Figure 6.

Complexity Doesn’t Have to Be Difficult

Working with sets has helped me do things I couldn’t have done with the usual tools. Studies have shown that a given programmer is capable of producing a certain number of lines of code per day, regardless of the language with which he’s working. That is, programmer Smith can produce 100 lines of assembler language, or 100 lines of RPG, or 100 lines of EXCSQLSTM. Obviously, the more each line of code does for you, the more productive you are.

Think how much harder it would be to develop the report in Figure 6 using RPG and printer files, and work files developed with DDS.

Think about the difficulty of changing this report. Users never ask me for what they want; they ask me for what they think they want. Also, we computer weenies don’t speak the same language the users do, so communication is difficult anyway. The only way to give the users what they want, oftentimes, is to give them something to look at, let them tell you what’s wrong with it, change it, and run it again. This is difficult with RPG programs, printer files, and DDS-defined work files, but easy with tools like EXCSQLSTM.

This may be off the subject, but I’ve been around programmers who never came to understand that users don’t care about the things we care about or think they care about. Users can get excited about an ugly report, as long as it tells them what they want to know.

So if you’re not writing set-at-a-time programs, give it some thought. Get some good tools, especially a good industrial-strength SQL package. EXCSQLSTM is free, but it’s slow. When you’re able to satisfy complex query requests quickly, users will cease to think of you as a programmer and begin to call you a wizard.

select it.itemno, it.desc, it.class, id.invno, ih.invdate,
ih.custno, id.qty, id.price

from items it left join invdtl id on it.itemno = id.itemno

left join invhdr ih on id.invno = ih.invno

where it.class in (‘PT’,’PV’)

Figure 1: This left outer join retrieves all desired items, even those for which there are no sales figures

ITEMNO DESC CLASS INVNO INVDATE CUSTNO QTY PRICE

J240 4” Widget PT 101 05/30/98 334 2 5.00 J240 4” Widget PT 102 06/01/98 334 1 5.00 J240 4” Widget PT 105 07/01/98 555 1 5.00 K138 Thingamajig PT 103 06/04/98 445 3 2.00 L909 Doomaflotchie PV 103 06/04/98 445 4 3.00 K287 #3 Whoozit PT null null null null null

Figure 2: Resulting data from the join in Figure 1

select it.itemno, it.desc, it.class, id.invno, ih.invdate,
ih.custno, id.qty, id.price

from items it left join invdtl id on it.itemno = id.itemno

left join invhdr ih on id.invno = ih.invno

where it.class in (‘PT’,’PV’)

and ih.invdate between ‘06/01/98’ and ‘06/30/98’

Figure 3: Outer joins don’t work correctly with record selection over a secondary file

PGM

EXCSQLSTM (‘+

select id.itemno, id.invno, ih.invdate, +
ih.custno, id.qty, id.price +

from invdtl id +

inner join invhdr ih +

on id.invno = ih.invno +

where ih.invdate between ‘’06/01/98’’ and ‘’06/30/98’’ +

and id.itemno in +

(select itemno +

from items +

where class in (‘’PT’’,’’PV’’)) +

‘) OUTPUT(*OUTFILE) OUTFILE(TEMP01)

EXCSQLSTM (‘+

select it.itemno, it.desc, it.class, t1.invno, t1.invdate, +

t1.custno, t1.qty, t1.price +

from items it left join temp01 t1 on it.itemno = t1.itemno +

where it.class in (‘’PT’’,’’PV’’) +

‘) OUTPUT(*PRINT)

ENDPGM

Figure 4: A two-step query solves the outer join problem

PGM

MONMSG MSGID(CPF2105) /* file not found */
MONMSG MSGID(CPF0000) EXEC(GOTO ABEND)

/* Get existing price info and new prices */

DLTF FILE(QTEMP/TEMP010)

EXCSQLSTM (‘+

select it.itemno, desc, +

decimal(coalesce(newprc,0),5,2) as newprc +

from items it left join newprice np +

on it.itemno = np.itemno +

where class in (‘’PT’’,’’PV’’) +

‘) OUTPUT(*OUTFILE) OUTFILE(TEMP010)

/* Get sales info */

DLTF FILE(QTEMP/TEMP020)

EXCSQLSTM (‘+

select itemno, +

decimal(count(*),5,0) as nbrofsales, +

decimal(sum(qty),5,0) as qtysold, +

decimal(sum(qty * price),7,2) as actualsls +

from invdtl id +

inner join invhdr ih +

on id.invno = ih.invno +

where ih.invdate between ‘’07/01/97’’ and ‘’06/30/98’’ +

and id.itemno in +

(select itemno +

from temp010) +

group by itemno +

‘) OUTPUT(*OUTFILE) OUTFILE(TEMP020)

/* Add blank sales figures for items that did not get sold */

EXCSQLSTM (‘+

insert into temp020 +

select itemno, 0, 0, 0 +

from temp010 t10 +

where itemno not in +

(select itemno from temp020) +

‘)

/* Combine the data */

DLTF FILE(QTEMP/TEMP030)

EXCSQLSTM (‘+

select t10.itemno, desc, nbrofsales, +

qtysold, actualsls, +

decimal(qtysold * newprc,7,2) as projsls +

from temp010 t10 +

inner join temp020 t20 +

on t10.itemno = t20.itemno +

‘) OUTPUT(*OUTFILE) OUTFILE(TEMP030)

/* Set projected sales equal to actual sales if no new prices */

EXCSQLSTM (‘+

update temp030 +

set projsls = actualsls +

where projsls = 0’)

/* Calculate sales difference */

DLTF FILE(QTEMP/TEMP040)

EXCSQLSTM (‘+

select t30.*, +

decimal(projsls - actualsls,7,2) as increase, +

‘’ ‘’ as incflag +

from temp030 t30 +

order by actualsls desc +

‘) OUTPUT(*OUTFILE) OUTFILE(TEMP040)

/* Flag large increases */

EXCSQLSTM (‘+

update temp040 +

set incflag = ‘’***’’ +

where actualsls 0 +

and float(increase) / float(actualsls) >= 0.095’)

/* Change column headings */

EXCSQLSTM (‘+

label on qtemp/temp040 (+

itemno is ‘’Item number ‘’ , +

desc is ‘’Description ‘’ , +

nbrofsales is ‘’Number of sales ‘’ , +

qtysold is ‘’Qty sold ‘’ , +

actualsls is ‘’Actual sales ‘’ , +

projsls is ‘’Projected sales ‘’ , +

increase is ‘’Projected increase ‘’ , +

incflag is ‘’ ‘’ +

)’)

RUNQRY QRY(*LIBL/OUTJOIN2Q)

RETURN

ABEND:

FWDPGMMSG

MONMSG MSGID(CPF0000)
ENDPGM

Figure 5: The price increase report program

07/01/98 09:00:00 Price Increase Worksheet PAGE 1
Item number Description Number Qty Actual Projected Projected

of sales sold sales sales increase

J240 4” Widget 2 3 15.00 18.00 3.00 ***

L909 Doomaflotchie 1 4 12.00 12.00 .00

K138 Thingamajig 1 3 6.00 6.15 .15

K287 #3 Whoozit 0 0 .00 .00 .00

FINAL TOTALS

TOTAL 33.00 36.15 3.15
* * * E N D O F R E P O R T * * *

Company confidential

Figure 6: The price increase report, produced with set-at-a-time tools

TED HOLT

Ted Holt is IT manager of Manufacturing Systems Development for Day-Brite Capri Omega, a manufacturer of lighting fixtures in Tupelo, Mississippi. He has worked in the information processing industry since 1981 and is the author or co-author of seven books. 


MC Press books written by Ted Holt available now on the MC Press Bookstore.

Complete CL: Fifth Edition Complete CL: Fifth Edition
Become a CL guru and fully leverage the abilities of your system.
List Price $79.95

Now On Sale

Complete CL: Sixth Edition Complete CL: Sixth Edition
Now fully updated! Get the master guide to Control Language programming.
List Price $79.95

Now On Sale

IBM i5/iSeries Primer IBM i5/iSeries Primer
Check out the ultimate resource and “must-have” guide for every professional working with the i5/iSeries.
List Price $99.95

Now On Sale

Qshell for iSeries Qshell for iSeries
Check out this Unix-style shell and utilities command interface for OS/400.
List Price $79.95

Now On Sale

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: