05
Tue, Nov
5 New Articles

Putting User Indexes into Practice

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

If you’ve ever needed to display the same information in several different ways but don’t want the overhead of multiple logical files, user indexes may be the answer. This simple program demonstrates how user indexes can be used to sort information any number of ways, yet minimize file I/O. Use this program as a shell and create powerful inquiries for your users quickly.

A subfile is one of the most powerful tools available to AS/400 programmers. With a subfile, you can display common information in a single line, in multiple lines, or in multiple columns. A subfile can be for display only or for maintenance. What more could we want?

Well, how about a key that is more meaningful than relative record number? How about two or more sequencing options for a list? How about being able to insert a record in the middle of a subfile?

Until these features are added to subfile processing, how can we accomplish these tasks? User indexes!

Indexes? Indeed!

A user index is a type of object that is designed for indexing (sorting) and searching data. User indexes hold data, as do physical files, and can be saved and restored, even among different systems. As you insert data into a user index, the data is sorted. This feature of user indexes lets you sort without having to create work files.

User indexes are manipulated through APIs, not CL commands. I’ve provided a list of the user index APIs in Figure 1. See the Object APIs manual for more information about them.

A Sample Application of User Indexes

To illustrate how easy it is to use user indexes, I created a simple application consisting of a physical file of sales representatives, a display file, and an RPG IV inquiry program. I can’t include the source code here, but you can download it from Midrange Computing’s World Wide Web site at http://www. midrangecomputing.com/mc/98/10.

Figure 2 shows you the display that my program presents.

The physical file is keyed on sales rep number. Building a simple inquiry program to display this file in key sequence would be easy, but users might benefit more by seeing the data in other sentences.

A typical solution would be to determine all possible sort sequences needed and create logical files with those keys. Instead, I’ve chosen not to create logical files, but to load user indexes for each view. To keep it simple, the physical file contains three fields: Sales Rep Number, Name, and Territory. I created views indexed by rep number; rep name and number; and territory, rep name, and rep number. I designed my program to let users enter a 1, 2, or 3 to select the sequence in which they wish to view the data.

One of the first tasks my program does is to create the three user indexes in QTEMP. To do this, I use the API QUSCRTUI (Create User Index), which is similar in function to the Create Physical File (CRTPF) command. The parameters needed are described in Figure 3.

Each user index has a different key length, depending on the lengths of the fields I use to load the index. The first user index has a key length of five since it contains only a sales rep number. The last user index’s key is 65 bytes long because it is indexed over all three fields. All three user indexes have the same entry length because each user index will contain the same three data fields.

After the user indexes are created, I read the Sales Rep file sequentially , loading each record into all three user indexes.

Each user index will contain exactly the same data, but since each has a different index (key), they will be retrieved in different sequences. To write entries into a user index, I used the API QUSADDUI (Add User Index Entries). The parameters needed are listed in Figure 4.

With the user indexes loaded, it is time to load the subfile according to the view the user selected. To retrieve entries from a user index, I use the API QUSRTVUI (Retrieve User Index Entries). Any number of entries can be retrieved at a time, but I prefer to retrieve a number equal to the subfile page size (DDS keyword SFLPAG) so that I can load a page with each retrieval. My program could easily be modified to build the subfile with each press of the Page Down (Roll Up) key, as each retrieval would return the next page of data. API QUSRTVUI needs the parameters listed in Figure 5.

After each retrieval of entries into the receiver variable, the data returned must be divided into individual entries. For each entry returned, a subfile record is written.

To change views, simply press the number of the view desired. The subfile is immediately reordered, according to the sequence requested.

Impress the Users!

From here, the program could easily be modified to use any file. A selection field could be added to allow the user to do database maintenance or to display sales figures or any other feature needed. Additional views could be added simply by creating additional user indexes, adding entries to the user indexes, and retrieving them to build the subfile when selected.

Try this program and see how easy user indexes are to work with. Then, take my example program and turn it into something useful for your users!

Reference

System API Reference OS/400 Object APIs (SC41-5865, CD-ROM QB3AMQ01)

API Name Function

QUSCRTUI Create User Index QUSADDUI Add User Index Entries QUSRTVUI Retrieve User Index Entries QUSRMVUI Remove User Index Entries QUSRUIAT Retrieve User Index Attributes QUSDLTUI Delete User Index

Figure 1: User index APIs




Figure 2: The inquiry program can display sales reps in three sequences



Putting_User_Indexes_into_Practice03-00.png 900x675

Qualified user index name The first 10 characters contain the user index name; the last 10 characters contain the library name. Extended attribute Objects of type *FILE have an extended attribute of PF (physical file), LF (logical file), etc. I always specify *Blanks. Entry length attribute F = fixed-length entries; V = variable-length entries. This example uses fixed-length entries.

Entry length This is the total length of the data to be stored in the user index, including the index. In this example, all three user indexes have a length of 65, the sum of the lengths of the three fields in the physical file.

Key insertion This specifies whether or not the inserts to the index are by key. 0 = no insertion by key; 1 = insertion by key. To retrieve entries in key sequence, specify 1.

Key length This is the key (index) length for the user index. In this example, each user index has a different value, corresponding to the length of the field(s) that make up the key/index.

Immediate update This stipulates whether or not to update auxiliary storage on each update to the user index. 0 = no immediate update;

1 = immediate update. I specify 1 to ensure that the index is always correct. This option functions much like the MAINT(*IMMED) parameter of the CPYF command.

Optimization 0 = optimize for random references; 1 = optimize for sequential references. I specified 1 in this example because I want to load all entries from the user index in key/index sequence.

Public authority Any valid authority value will work here. I normally specify *ALL to keep things simple. Text description I specify either a simple description or *Blanks.

Replace This specifies *YES/*NO. I normally specify *YES. When creating a user index, if *YES is specified and the user index already exists, the user index is replaced.

Error code This is the standard error data structure used by most IBM APIs. By checking the error message ID portion of the data structure, you can trap any errors generated by the APIs.

Figure 3: Parameters of the QUSCRTUI API

Returned library name If you specify *LIBL or *CURLIB as the library in the qualified user index name parameter, this field will tell which library the user index was found in.

Number of entries added This output parameter indicates the number of entries successfully added to the user index. Qualified user index name The first 10 characters contain the object name; the last 10 characters contain the library name. Insert type 1 = insert unique argument; 2 = insert with replacement; 3 = insert without replacement. 1 is only valid for non-keyed user indexes. Since user index entries cannot have duplicate keys, options 2 and 3 determine what happens when an entry with a duplicate key is added to the user index. When 2 is specified, adding an entry with a duplicate key replaces the previous entry. When 3 is specified, adding an entry with a duplicate key causes an error message to be returned in the APIErrDS data structure. I always ensure that the entries I add have unique keys before I attempt to add them to the user index. I have included the sales rep number field as part of the key to each user index. This field is a unique key on the sales rep file, so every entry added to each user index will have a unique key.

Index entries This is the data structure containing the actual entry to be added to the user index. Because the key structure is different for each user index, I created three different data structures (U1DS, U2DS, U3DS) to contain the entries to be added to each user index.

Length of index entries This is the length of the previous field. Entry lengths and offsets This is used only if the user index contains variable-length entries. This example uses fixed-length entries, so it is ignored.

Number of entries This is the number of entries to be added to the user index at one time. I always specify 1 to keep things simple. Error code This is the error data structure used by most APIs.

Figure 4: Parameters of the QUSADDUI API

Receiver variable When entries are retrieved, they are first placed into the receiver variable. From here, they must be divided into individual entries. This field must be large enough to hold all entries being retrieved, plus 8 bytes. In this example, I am retrieving 15 entries of 65 bytes ((15 * 65) + 8) = minimum of 983 bytes. Length of receiver variable This specifies the length of previous parameter.

Entry lengths and entry offsets This must be ((maximum number of entries * 8) + 8), but it is normally needed only for variable-length entries.

In this example, I specified the field with a length of 128 = ((15 * 8) + 8). Length of entry lengths and entry offsets This specifies the length of previous parameter. Number of entries returned This specifies the number of entries returned to my program. This number will be less than or equal to the

Maximum number of entries parameter. Returned library name If you specify *LIBL or *CURLIB as the library in the qualified user index name, this field will tell which library the user index was found in.

Qualified user index name The first 10 characters contain the object name; the last 10 characters contain the library name. Format This is the format of the receiver variable. Only one value is currently supported: IDXE0100. Maximum number of entries I specified 15 to match a single page of values.

Search type This is used with the Search Criteria to determine what to retrieve. 1 = equal; 2 = greater than; 3 = less than;

4 = greater than or equal; 5 = less than or equal; 6 = first; 7 = last; 8 = between. I specified 2 = greater than to indicate that I wanted the next entry after the last retrieved.

Search criteria This specifies the key/index to use when retrieving entries. Length of search criteria This specifies the length of previous parameter. Search criteria offset If Search Type 8 = between is specified, this parameter indicates the offset from the “from” value to the “to” value in the search criteria parameter. Since I did not use Search Type 8, this parameter is ignored. Error code This is the error data structure used by most APIs.

Figure 5: Parameters of the QUSRTVUI API

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: