18
Thu, Jul
2 New Articles

More Smoke and Mirrors: Views and Virtual Fields

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

A certain business advertised for an accountant and invited only three out of the many applicants for an interview. The interviewer asked the first applicant, “How much is two plus two?” The applicant looked surprised but answered the question, “Four.” “Thanks for coming by,” said the interviewer, “but we can’t use you.”

The second applicant was ushered into the office and asked the same question. “Obviously, I’m too good to work for you,” he answered and left in a huff.

The third applicant was hired because, when he was asked the sum of two and two, he glanced around to make sure no one was listening, then whispered, “How much do you need it to be?”

Sometimes, computer programmers need to be like that third applicant. I’m not suggesting that anyone falsify information. I mean that sometimes programmers need to make data appear to be organized in ways that are not consistent with reality. In relational databases, this is done with views. On the AS/400, views are implemented with logical files and SQL views.

This is a powerful mechanism for application development and maintenance and, unfortunately, is widely underused in AS/400 shops. It’s not that AS/400 shops don’t use logical files. They do. Some shops have logical files out the wazoo. But to many programmers, logical files are nothing more than alternate sorting sequences. In the articles “Use Logical Files to Ease the Y2K Conversion” (MC, January 1999) and “Logical Files: More than Alternate Sort Sequences” (MC, April 2000), I dealt with the power of logical files. This article continues my discussion of that topic.

 

Why Views?

 

Views allow different users with different needs to picture data in ways that are meaningful to them. Consider three users who require access to the employee master file. One of the users is Bob, the personnel manager. Another is Chuck, a foreman in the factory. The last user is PAY250, an RPG (or COBOL) program that computes gross pay for salaried employees.

If you were to ask Bob to describe the employee master file, he would respond, “It tells me each employee’s name, mailing address, clock number, telephone number, job


description, and department number. I can view all pertinent payroll information for both salaried and hourly employees, such as how much they make per pay period or per hour. I can see their deduction and insurance information. And I can see information about their participation in our stock purchase and retirement plans. The file is in order by last name and first name.”

Chuck wouldn’t agree with that. He’d say, “Oh, no. That’s not the way it is at all. That file doesn’t have nearly that much information. It only has each employee’s name, clock number, telephone number, job description, and department number. It’s in order not by employee name but by department and then by clock number. And it shows hourly, not salaried, people.”

Program PAY250 can’t talk, but, if it could, it would disagree with both Bob and Chuck. It would say, “The employee master file has all the information Bob says it does, but it’s in order by clock number. And by the way, there are only salaried people in the file.”

The three different views appear to conflict, but they don’t. The fact is, the employee master file can be all of these things and more, thanks to the magic of views.

The easiest type of view to create is one that contains a subset of fields and/or a subset of records. (I won’t belabor this idea, since it was the thrust of the other two articles.)

Figure 1 defines a subset of order detail information. First, notice that five fields are listed in the record format. Physical file DVORDDTL may have more fields, but these five fields are the only ones a user of this logical file can access. Understand that you do not have to list fields. If you don’t, the logical file will have the record format of the physical file. That may be what you want in some cases, but I have decided, after years of designing databases and writing programs, to always list fields. If I add a field to a physical file and decide that a logical file needs the new field, I add the new field to the logical file’s DDS, rebuild the logical file, and recompile the programs that use the logical file.

Second, notice that the last line in Figure 1 selects records that have a positive value in the quantity (QTY) field. A user of this logical file does not see returned items, which are indicated by a negative value in the QTY field.

 

Renamed Fields

 

Another useful way to view data is by alternate field names. Suppose you’ve limited all database field names to RPG III’s standard length of six characters. However, now your shop is doing new development in RPG IV, so you’d like to use 10-character field names instead. As Figure 2 illustrates, logical files do not have to refer to fields by the same name the physical file uses. Whereas the unit price field is named ITUPRC in the physical file, users of this logical file refer to it as ITPRICE instead. I’ve found that this technique has been a big help since I began writing programs in RPG IV for my clients.

 

Virtual Fields

 

Users are notorious for burying information inside data. It’s not uncommon in a business for a “newbie” to ask a question like, “How can I tell if an item is made of aluminum?” and to be told, “The third digit of the item number is a 7.” This sort of informal business logic is passed along by word of mouth and is lost when knowledgeable people are downsized, die, quit, or otherwise leave the company.

What newbies, and everyone else for that matter, need are fields that make such information explicit. I don’t know what the formal name for such fields is. I have heard them referred to as “derived” fields, but I call them virtual fields, since they’re not really stored as separate fields in the physical file.

Figure 3 shows a logical file that contains information about the items on a sales order. Notice the Raw Material (RAWMATL) field. The substring keyword, SST, means


 

Subsets

 

that this field is part of another field and takes three keywords: the name of the field containing the data, the position where the substring begins, and the length of the substring. In this case, RAWMATL is defined as the third character of the ITEM field. There is not really a field called RAWMATL in the database. That just appears to be the case. Users can reference this field as they can any of the other fields. Users can view this field, select records based on values in this field, and sort by this field. Programs can declare and read this file and reference the RAWMATL field. The one exception is that the field can’t be updated. The “I” in column 38 of the example signifies “input only.” Users must change the ITEM field instead. Changing the third character of ITEM automatically changes RAWMATL.

 

Concatenated Fields

 

The opposite of substringing is concatenation, which makes two or more fields appear as if they were one field. This is another helpful way to make databases more meaningful. For example, one of the criteria for the first normal form is that all data must be atomic. That is, each field must contain only one datum. Suppose your information system allows for multiple companies within the same database. Each file, or at least most of them, would need a company number field. In many cases, the company number would be combined with other fields (e.g., vendor number and customer number) to form useful information.

Figure 4 shows how company number and customer number are combined by the CONCAT keyword to form what the imaginary people of this imaginary company call a customer account number. COMPANYNBR is a zoned-decimal field of two decimal places, and CUSTNBR is a five-digit, packed-decimal field. When concatenated, they form a seven-digit, zoned-decimal number. Because the company number is stored in its own field in the physical file, it can easily be used alone when necessary. Unlike substrung fields, concatenated fields can be updated.

 

More Power Through SQL

 

If you want more powerful ways to view data, you must leave behind the world of DDS and journey into the land of SQL. Both are interfaces to the same database engine, but IBM has not added new DDS keywords to make DDS do what SQL can.

The following is an SQL statement that creates a view called DVCUST01 over physical file DVCUST:

create view dvcust01 as

select custnbr,

trim(firstname) concat ‘ ‘

concat lastname as fullname

from dvcust

This view has two columns (fields): CUSTNBR and FULLNAME. CUSTNBR comes straight from the physical file, while FULLNAME is built with an expression. The TRIM function removes leading and trailing blanks from FIRSTNAME. The database engine concatenates to this result a blank and LASTNAME. The resulting value is the first name and last name, which are separated by a single blank.

Another example, which I won’t show, comes from a file of items on a sales order like the one referred to in Figure 1. Each record contains the number of the item being sold, the quantity the customer desires, and the price per unit. You could create a view that multiplies the quantity-ordered field by the unit-price field to get the extended price. Users could sort and select records based on the extended price.

Keep a few points in mind. First, DB2 UDB does allow you to create SQL views over physical files that were created from DDS.


Second, since a view is a logical file, you can declare SQL views in the F-specs of RPG programs. However, you won’t be able to update the calculated fields, such as FULLNAME in the previous example. The compiler won’t tell you that, nor will you get a runtime error if you change the field. Third, if you don’t have SQL, you can do as I do. Since none of my clients has SQL, I use the Execute SQL Statement (EXCSQLSTM) command to run SQL statements. (For more information on the EXCSQLTM command, see the article “The EXCSQLSTM Utility” in the December 1994 issue of MC.)

 

Conditional Field Values

 

One especially powerful feature of SQL is the CASE construct, which allows you to do conditional calculations. I wish IBM would add this feature to the Open Query File (OPNQRYF) command.

Figure 5 contains an SQL statement that creates a six-column view over an order- detail file. The first three and last two columns come straight from the physical file. The fourth column, METAL, is a 2-byte-character column whose value is based on the third digit of the ITEM field. Users can sort or select records based on the METAL field, believe it or not!

CASE has another syntax, which I don’t illustrate here. You can omit the expression between CASE and the first WHEN, and you can follow each WHEN with relational expressions. See the DB2 for AS/400 SQL Reference manual for more details.

 

Consider the User’s Point of View

 

There is more to say about views. For example, you can make data from two or more physical files appear to be stored in only one file. Maybe I’ll write about that in another article.

When designing a view, keep one question in mind: How does the user need to perceive this data? It is not usually difficult to create a fictitious world that makes more sense to the user than the physical one.

 

References and Related Materials

 

• DB2 for AS/400 SQL Reference (SC41-5612-03, CD-ROM QB3AQ903)

• “Logical Files: More than Alternate Sort Sequences,” Ted Holt, MC, April 2000

• “The EXCSQLSTM Utility,” Richard Shaler, MC, December 1994

• “Use Logical Files to Ease the Y2K Conversion,” Ted Holt, MC, January 1999

A UNIQUE

A R ORDDTL PFILE(DVORDDTL)

A ORDNBR

A LINENBR

A ITEM

A QTY

A UPRICE

A K ORDNBR

A K LINENBR

A S QTY COMP(GT 0)

Figure 1: Thanks to logical files, users can access only the fields and/or records that pertain to them.


A UNIQUE

A R ITEMREC PFILE(DVITEM)

A ITEMNUMBER RENAME(ITEM)

A ITDESC

A ITCLASS RENAME(ITCLAS)

A ITQOH

A ITPRICE RENAME(ITUPRC)

A K ITEMNUMBER

A UNIQUE

A R ORDDTL PFILE(DVORDDTL)

A ORDNBR

A LINENBR

A ITEM

A RAWMATL I SST(ITEM 3 1)

A COLHDG(‘Raw’ ‘matl’ ‘code’)

A QTY

A UPRICE

A K ORDNBR

A K LINENBR

A R CUST PFILE(EWCUST)

A CUSTACCT CONCAT(COMPANYNBR CUSTNBR)

A FIRSTNAME

A LASTNAME

A K CUSTACCT create view dvorddtl51 as

select ordnbr, linenbr, item,

case substr(item,3,1)

when ‘0’ then ‘Fe’

when ‘1’ then ‘Cu’

when ‘5’ then ‘Ni’

when ‘7’ then ‘Al’

else ‘??’

end as Metal,

qty,uprice

from dvorddtl

Figure 2: Logical files do not have to use the “real” names of the fields in the database.

Figure 3: Users see the RAWMATL field as a separate field in the database, but it isn’t.

Figure 4: Concatenation makes two or more fields look as if they were only one field.

Figure 5: With SQL’s CASE construct, you can conditionally assign values to a column.


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: