To close this subseries, I’d like to share a few more CREATE INDEX options and revisit what we’ve discussed so far. The last few articles are the base upon which the next subseries will be built.

It’s almost time to move on to the new subseries of SQL 101: making the database more user-friendly. But we can (and I’d add have to) start right now by getting rid of the not-so-user-friendly names the system generates by default for our SQL objects.

Exploring a Few CREATE INDEX Options

Let’s start by fixing the ugly, system-generated name of my Index_Students_By_Name index into something more agreeable. Well, not too agreeable, because it can only be a maximum of 10 characters long. In order to do that, I’ll use the FOR SYSTEM NAME expression, inserted right after the index name, just as the FOR COLUMN followed the column name on the view syntax. Here’s what my index looks like after this change:

CREATE INDEX UMADB_CHP3.Index_Students_By_Name FOR SYSTEM NAME IDX_STM_NM

   ON             UMADB_CHP3.PFSTM (STNM)

;

A programmer familiar with the naming convention quickly identifies this as an Index over PFSTM, using STNM as a key. Of course, this doesn’t work at all with longer keys, so you might need to use the respective description (created using a LABEL ON statement, if you recall what was discussed earlier in this subseries) to explain to whomever looks at the Index using native tools, such as PDM, exactly what the index is. It’s important to mention that FOR SYSTEM NAME and KEEP IN MEMORY, presented later in this article, work only on V7.1 or higher. I hope you’re running a higher version of the OS than V7.1, but knowing the IBM i world, there are probably older systems running older versions of the OS out there, so I feel compelled to make this remark.

Now let’s say I wanted an index by name, but in reverse alphabetical order. Just like in a regular ORDER BY clause, I can use ASC and DESC after each column name to specify the order. This means that my index, modified to sort the student name in reverse alphabetical order, looks like this:

CREATE INDEX UMADB_CHP3.Index_Students_By_Name FOR SYSTEM NAME IDX_STM_NM

   ON             UMADB_CHP3.PFSTM (STNM DESC)

;

I’ve also mentioned that a typical index has a larger page size than a logical file. However, the default value for PAGESIZE, the keyword used to specify that value, is determined by the length of the key and has a minimum value of 64Kb. What I didn’t say at the time is that you can choose the page size by specifying the intended value on the CREATE INDEX statement! It’s as simple as writing this:

CREATE INDEX UMADB_CHP3.Index_Students_By_Name FOR SYSTEM NAME IDX_STM_NM

   ON             UMADB_CHP3.PFSTM (STNM DESC)

   PAGESIZE 128

;

This creates an Index with a 128Kb page size. Naturally, this affects performance, but it’s not the only way to “juice up” your indexes. You can also use a couple of keywords to use a faster disk unit (a solid-state disk, or SSD, to be more precise), if available, and to bring the index data into the main storage pool. Let’s change the sample index to include these options:

CREATE INDEX UMADB_CHP3.Index_Students_By_Name FOR SYSTEM NAME IDX_STM_NM

   ON             UMADB_CHP3.PFSTM (STNM DESC)

   PAGESIZE 128

   UNIT     SSD

   KEEP IN MEMORY YES

;

Finally, let me add one more thing, which is so obvious that you’re probably wondering what it looks like in SQL: the DDL equivalent to a logical file with the UNIQUE keyword. It’s obvious because it’s the same keyword, inserted in the middle of the CREATE INDEX statement. Here’s an example that creates an index over PFSTM while ensuring that the student name is unique across the table:

CREATE UNIQUE INDEX UMADB_CHP3.Index_Students_By_Name

   FOR SYSTEM NAME IDX_STM_NM

   ON             UMADB_CHP3.PFSTM (STNM DESC)

   PAGESIZE 128

   UNIT     SSD

   KEEP IN MEMORY YES

;

There are other interesting keywords to explore, and I’ll discuss some of them later, properly contextualized with an example.

DDL Recap Subseries Recap

I hope this has been an interesting subseries, filled with novelties for some readers, while providing a solid revision of DDL for others. Here’s a summary of what I’ve tried to explain here:

• How to convert a physical file to an SQL table, using the Generate SQL System i Navigator (ACS) option
• How to use LABEL ON statements to add descriptions to tables and columns
• How to use the ALTER TABLE statement to add columns, change default values, and add primary keys
• Why and how to create views

Why a view is not (necessarily) the same as a logical file “SQL 101: DDL Recap—Are Views and Logical Files the Same Thing?”

• Why and how to create indexes “SQL 101: DDL Recap—Are Views and Logical Files the Same Thing?”
• How to add a few extras to your CREATE INDEX statements (this article)

What’s Next for SQL 101?

As mentioned, the DDL recap subseries is of paramount importance for what’s coming in this TechTip series. It’s nearly impossible to make your database more user-friendly (e.g., get rid of those *0001 names, use longer table and column names, etc.) without what was discussed in the seven articles that comprise this subseries. I strongly recommend that you read them and, if the any of the topics is new to you, bookmark them for future reference.

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