What's my Vector, Victor?
If you remember the movie "Airplane," you probably remember that line. When you fly an aircraft using instruments (IFR), vectors are a concept you have to understand. They get you from point A to point B without bumping into anything (a bad thing to do in an airplane). I'm not sure that's what IBM had in mind when they added support for vector indexes to the AS/400's database, but they can be important to how your data is retrieved.
OK, we all know that we've had great database support on the 400 for years, so why is IBM making all these changes over the last few releases? The answer is pretty simple: market pressure! Databases are getting more involved in our systems. Oracle, Microsoft and Informix to name a few, have all added new capabilities to drive their databases into more shops and data warehousing is one area that's really driving database development. We're constantly asking new questions about our data and it just takes a lot of time to go through all the data in new ways.
Encoded vector indexes (EVI) are one of the newer (with V4R3) ways of handling this issue. Microsoft released SQL Server 7.0 not too long ago and one of the aspects Microsoft touted was its support for data warehousing applications (DW). What they really provided was a way for vendors to create multidimensional views of their data for DW applications. EVIs are IBM's way of handling this problem. Does this mean that you need to build large DW's to take advantage of them? Not at all, EVIs can be used for any size database.
EVIs are a newly patented technology developed by IBM and delivered first on the DB2/400 platform. The process seems complicated (and probably is in its implementation), but the idea is simple. Reduce the size and number of indexes without losing the pointers to the real data. If you haven't heard of it, don't feel bad. Most shops don't even know it exists. Unless a vendor has brought it to you, you'd never know about the benefits.
If you have lots of users who query your data extensively and your tables (files) are fairly large, EVI technology might be able to help. Basically, it creates a new type of index that the system uses when traversing your data in order to fulfill the query requirement.
Without getting too deep into index technology (binary radix indexes or EVI) let's see what they are and why we should care. First off, an index is just a smaller, more efficient way to point to the record we really want. With lots of data elements (fields) in our records, each time we need that record, we may need it from a different point of view. Indices point to the records and help order them for our queries and reports. As more and more records are added to the table, and more and more indices are added to the file, index maintenance becomes a burden to the system. It becomes more important to have ways to group data together, find it quickly, and order it in the way we need.
With EVI technology, the system stores the index in a more compact manner allowing the system to search through them quicker. From a practical standpoint, this can mean a five-fold improvement in query performance. Multiplied over the quantity of queries performed on your system, this can have a big overall performance impact and on users' response time.
These new indices actually look just like other logical files on your system. You can use DSPFD to display them and you can open them in programs. The interface to create them is a little different, however. You must use either SQL CREATE INDEX or the Client Access Operations Navigator functions to create them. Don't look for them in the DDS manual--as IBM adds new capabilities to the data management system, more and more will use the SQL interface and not the DDS one. DDS is too specific to the AS/400 (right or wrong) and SQL is more conventional.
Do you need a data warehouse to gain benefits? If you just want to take advantage of the index speed, try looking at some of your queries. Examine the indices they build and try building an EVI over them in the sequence the user uses. Then run the report or query with the STRDBG command over the job and the job log will provide a detailed review of the access paths the system analyzed and used for the query. Keep in mind that if you use join fields (concatenated ones) and they are part of the key, the system will always build the index on the fly.
Another thing you can try is using the Operations Navigators SQL performance analyzer to create an audit of jobs that are heavy users of indices. A whole new world will open up (for better or worse)! There's so much being added to the AS/400 it's almost impossible for most shops to keep up. This is one thing that's quick to try and relatively easy to see if it works and you really don't need to understand what it's doing to gain some benefits. Obviously, the more you do, the better you'll be at taking advantage of it.