EVIs Arrive in V4R3
Chief among the DB2/400 improvements in V4R3 is support for encoded vector indexes (EVI), which promises better performance for AS/400 data warehousing and analysis.
An index is an organized set of information that provides a way to rapidly find an entry in a large table. Encoded vector indexes in V4R3 replace the bit-mapped indexes of past OS releases, requiring less storage and scaling better.
"The bit-mapped index is a general purpose index," explains Kent Milligan, DB2/400 specialist at IBM Partners in Development. "They do a real good job when you just have a small set of values, like customer number and order number. But they have their shortcomings when you have to analyze a large group of values."
Bitmapped indexes are used for each distinct value in a table. With encoded vector indexes, each value is assigned a code, then there’s one index for each array of codes rather than for each value. For example, if you were compiling sales information for the 50 states, a bitmapped index would be required for each of the 50 states. But in V4R3, one encoded vector index could be created for all 50 states.
"[Encoded vector indexes] scale better in terms in terms of rows accessed and the distribution of key values you’re searching on," says Milligan. "It’s not just for the largest AS/400 customers. Anybody doing decision support or data warehousing will benefit. I see a big benefit for ad hoc queries."
In addition to the introduction of encoded vector indexes there are several SQL improvements in V4R3. The size limit of SQL packages is being removed and users can now update one table based on values from another table. There’s also multisystem subquery support, allowing users to perform subqueries over a distributed file and a C++ precompiler to allow SQL to be embedded in a C++ program. Finally, ALIAS support has been introduced in this version, allowing the user to specify an alternate name for a database file or member to be used through SQL.