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 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 Become a CL guru and fully leverage the abilities of your system. List Price $79.95 Now On Sale
|
|
||
 |
|
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 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 Check out this Unix-style shell and utilities command interface for OS/400. List Price $79.95
Now On Sale
|
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