The shortest possible answer is no. The short answer is almost. Keep reading to find out the not-so-short-but-correct answer and learn how to make proper use of your newly discovered SQL views.

We could debate this for hours (I actually witness this being debated—not for hours, of course), but the reality is that views and logical files (LFs) are not the same thing. The truth is that they are almost the same. You need to throw in a little SQL magic for views to have the same functionality as logical files. We’ll talk about that later in the article, but for now…

Repeat After Me: A View Is Not a Logical File

This may shock a few readers, because there’s a lot of literature that clearly states that a view and a logical file (LF) are the same thing. Well, they’re not exactly the same.

The first difference is related to performance: while an LF has a maximum page size of 8Kb, a view is capable of handling up to 64Kb. That’s eight times more! However, this may not always translate to an equivalent performance gain, as tests performed by Jon Paris and Susan Gantner for an article on DDS versus DDL showed. There are performance gains, to be sure, but views sometimes have quirky behavior, which IBM continues to work on eliminating.

Note that I’m not in any way implying that you should not use views—quite the contrary! I’m a big fan of modernization, not for the sake of modernization but because of the benefits it can bring. Performance improvements are an important part of that, and even if sometimes the final result is not exactly what was expected, modernizing your database is still worth the effort.

Anyway, the second and probably most important reason that a view is not an LF is almost counter-intuitive: while most LFs have a key, views cannot. Try creating a view using a SELECT statement ending with an ORDER BY clause, and you’ll see what I mean. The database engine will complain, and you won’t be able to create the view. Sure, you can say that a keyless LF and a view are the same thing. However, always keep in mind that most LFs have keys, and that’s why you should repeat after me: a view is not a logical file. So, how do you make it one?

The Missing Piece: SQL Indexes

If a view can’t have a key, what is SQL’s equivalent to a keyed LF? It’s not one but two things: a view and an index. Let’s create an index to complement the view created in the previous article.

The index is something most programmers are not entirely familiar with, so I’ll make an exception and discuss the syntax of this SQL statement in a bit of detail. In its simplest form, a CREATE INDEX statement can be written like this:

CREATE INDEX UMADB_CHP3.Index_Students_By_Name

   ON             UMADB_CHP3.PFSTM (STNM)

;

This creates an index over PFSTM, using STNM (the student name) as key. You can specify a multipart key by including multiple column names, separated by commas, between the parentheses. The following statement creates an index similar to the previous one, but with an expanded key that includes the student’s date of birth:

CREATE INDEX UMADB_CHP3.Index_Students_By_Name_And_Birth

   ON             UMADB_CHP3.PFSTM (STNM, STDB)

;

Running the SELECT statement over the View_Students_Classes_1 view should, theoretically, produce faster results. I say “theoretically” because there’s not a lot of data in this sample database’s tables, and the performance gain, if any, is negligible in this case.

For the moment, just know that View_Students_Classes_1 and Index_Students_By_Name allowed me to create an SQL version of a LF over PFSTM that uses STNM as a key.

A Side Note: Why Is It So Important to Keep the Source Statements Safe?

Let’s take a step back and analyze View_Students_Classes_1 and Index_Students_By_Name from a system’s point of view. If you try to use PDM to find these objects, you’ll have a bit of a surprise, because they’re not there. Instead, you’ll see LFs on UMADB_CHP3: VIEW_00001 and INDEX00001. This happens because I didn’t specify a system name for the view or the index—I’ll get to that in a moment. For now, let’s see how the system describes these objects.

If you type DSPFD UMADB_CHP3/VIEW_00001, you’ll see a LF without a key. By pressing Page Down, you’ll note that it also includes the SQL statement that was used to create the view, which is nice. However, don’t think even for a moment that you don’t have to keep the source code of a view stored safely somewhere, because what’s displayed here may not correspond to the source statement of the view. If the statement is long, as useful views tend to be, it simply won’t fit here.

Similarly, DSPFD UMADB_CHP3/INDEX00001 shows another LF, correctly identified by the system as “externally described.” You can Page Down all you want, but there’s no source statement here—one more reason to keep all DDL statements safe somewhere. Unlike the view, you’ll note that this “LF” has a key, just as a regular LF would.

There’s yet another good reason to keep the source code: if you want to change a view or an index, the logical choice would be using an ALTER statement to do so. The problem, as far as I know, is that there are no ALTER VIEW or ALTER INDEX statements: you need to use DROP VIEW or DROP INDEX followed by the respective CREATE statement.

It’s true that by using IBM Navigator for i’s Generate SQL option you can get the original source code, but if the SQL object is somehow damaged, this may not be possible. I can’t stress this enough: always keep your source statements stored in a safe location.

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale