Encoded-vector index (EVI) only access in IBM DB2 foriUsing encoded vector indexes to accelerate the projection ofcolumns returned by an SQL query with DB2 for iScott L. ForstieJanuary 29, 2016IBM DB2 for i continues to enhance the SQL Query Engine (SQE) in many ways. Onerecent enhancement delivers a new way that SQE can use an encoded-vector index (EVI) toaccess the data needed by a query through an index implementation. This new capability iscalled EVI only access (EOA) where the data can be reached by only accessing an index. Thisarticle provides some of the foundational details you’ll need to use EOA.EVI only accessIBM DB2 for i continues to enhance the SQE in many ways. One recent, in November 2015,enhancement delivers a new way that SQE can use an EVI to access the data needed by a querythrough an index implementation. This new capability is called EVI only access (EOA) where thedata can be reached by only accessing an index. This article provides some of the foundationaldetails you'll need to use encoded-vector index only access.EVIs are a patented technology invented by the IBM Research team and they're unique to IBMDB2 for i. What can an EVI do for you? Indexes are a permanent structure, maintained by thedatabase to provide a fast answer to your question (get the data needed by a query). EVIs canbe used as part of selection, where the resultant rows are identified. EVIs used for selection aregenerally considered more beneficial when the column has low cardinality (number of uniquevalues). In 2012, IBM i 7.1 was enhanced to allow EVIs to include and maintain aggregate detailsuch as summary, average, and count values over important columns.With this enhancement to support EOA, EVIs can be used for projection of column values.Projection of column values are the columns found between the SELECT and the FROM clausesin your SQL query. Where should you use EOA? As you might expect, I'll have to respond with"It depends". Anyone answering a performance-related question always takes this safe path. Thereality of performance is that it truly does depend upon factors, some of which are client-specific.Like others, I'll lean on the "It depends", but also encourage you to get educated and then spendtime evaluating your options. Copyright IBM Corporation 2016Encoded-vector index (EVI) only access in IBM DB2 for iTrademarksPage 1 of 10

developerWorks EOAA QAQQINI query option controls whether or not SQE can consider costing and using EVIs forEOA. The control is named ALLOW EVI ONLY ACCESS, and it can be configured to be set tothe following values:1. *DEFAULT – In IBM i 7.1, *NO is the default value. In IBM i 7.2, *YES is the default value.2. *YES – EOA is eligible to be considered by SQE3. *NO – EOA is not eligible to be considered by SQEAs you can see, the default behavior differs between IBM i 7.1 and 7.2. Why is this? Becausemany IBM i 7.1 users prefer operations and query implementations to remain unchanged, wedecided to have the support set to off by default on that release. To override this decision, simplyupdate your favorite QAQQINI file to use ALLOW EVI ONLY ACCESS with *YES or updateQUSRSYS/QAQQINI to enable EOA across the entire partition. If you're using IBM i 7.2, you don'tneed to do anything to enable EOA, other than applying the DB2 PTF Group.The DB2 PTF Group which included EOA enablement is shown in Table 1. To use EOA, your DB2PTF Group level needs to match or exceed the levels in the table.Table 1: DB2 PTF Group levels which enabled EOADB2 for i enhancementIBM i 7.2SF99702 Level 9EVI Only Access (EOA)IBM i 7.1SF99701 Level 38EVI RRN probeWhen processing the projects of column values, a table probe operation is used to retrieve aspecific row from a table based upon its row number. The row number is provided to the tableprobe access method by some other operation that generates a row number for the table. Withthe new EOA capability, the column value can now be retrieved from the EVI using an EVI relativerecord number (RRN) probe. The EVI RRN probe is an index only access method that is used toprovide selected columns by retrieving the value from the EVI instead of using a table probe toaccess the table. Retrieving the value from the EVI should provide better I/O characteristics thanthe random I/O associated with a table probe operation.This access method is used in conjunction with a radix index probe, radix index scan, or EVI probeoperation. The radix index probe, radix index scan, or EVI probe operation is used to select therows and then the RRN of the selected row is used to probe into EVIs to retrieve any selectedvalues that were not provided by the index used for selection. The EVI RRN probe can accessmultiple EVIs to provide selected values.Table 2 provides a summary of the new EVI RRN probe with its associated advantages,considerations, examples, and other information.Table 2: EVI RRN probe attributesData access methodEncoded-vector index (EVI) only access in IBM DB2 for iEVI RRN probePage 2 of 10 DescriptionThe encoded-vector index is quickly probed based on the RRNsprovided by the underlying index access.Advantages Provides the potential to extract all the data from the EVI indexkey values, thus eliminating the need for a table probe Provides better paging characteristics than a table probeConsiderations Only single-key EVIs are considered for this implementation All selected columns must have a single column EVI created The EVIs must fit in the query's fair share of optimizer memoryLikely to be used When the table row size is wide, the number of select columnsis small compared to the number of columns in the table and thequery requires a table probe to retrieve columnsExample SQL statementSELECT QUANTITY, ORDERPRIORITY, EXTENDEDPRICEFROM ITEM FACTWHERE SHIPMODE 'TRUCK'ORDER BY ORDERDATE DESCLIMIT 50OFFSET 0;Example CREATE INDEX SQL statementsCREATE ENCODED VECTOR ITEM FACT QUANTITY EVION ITEM FACT ( QUANTITY ASC )WITH 65537 DISTINCT VALUES;CREATE ENCODED VECTOR ITEM FACT ORDERPRIORITY EVION ITEM FACT ( ORDERPRIORITY ASC )WITH 65537 DISTINCT VALUES;CREATE ENCODED VECTOR ITEM FACT EXTENDEDPRICE EVION ITEM FACT ( EXTENDEDPRICE ASC )WITH 65537 DISTINCT VALUES;CREATE INDEX ITEM FACT SHIPMODE RADIXON ITEM FACT ( SHIPMODE ASC );Database monitor and plan cache record indicating useA QQRID 3001 index used record for each EVI with QQRCOD 'I8'SMP parallel enabledYesAlso referred to asTable probe, preloadVisual Explain iconPrior to encoded vector index only access, the recommendation had been to only create EVIsfor column with low cardinality (small number of distinct values). This recommendation has nowchanged. EVI RRN probe can be used for columns with high cardinality (large number of distinctvalues). However, when creating the EVI, the WITH integer DISTINCT VALUES clause should beused to set the initial width of the EVI vector appropriately and to minimize maintenance time if thedatabase manager needs to use a wider vector. Refer to the CREATE INDEX statement in SQLreference for more details.Table 3 shows the three database Navigator product options. The information and examples withinthis article were built using the Navigator product found within IBM i Access for Windows. Somerefer to this product as System i Navigator, while others call it IBM i Navigator. In this article, theterm IBM i Navigator is used.Table 3: Database Navigator product overviewProductsIBM i Access for WindowsEncoded-vector index (EVI) only access in IBM DB2 for iIBM i Access Client SolutionsIBM i NavigatorPage 3 of 10

developerWorks i Navigatoror IBM i NavigatorACSNavigator for iWhere does it run?Windows PC InstallAnywhere Java can be usedBrowserServed from IBM i 7.1 and 7.2Recent service level?IBM i Access Windows ServicePack7.1 – SI57907Version HTTP Server for i PTF Group:7.2 - SF99713 Level 127.1 - SF99368 Level 37Best of breed features, for thedatabase userRun SQL scriptsVisual ExplainMany other featuresRun SQL scriptsSQL Performance CenterPDI PerspectivesOmniFind administrationWebpage to ws utions m/s dir/SLINE003.NSF/PTFbyNumber/SF99368Next (planned) updateJune, 2016July, 2016 Version PTF Group every 2 to3monthsIndex advice for EVI RRN probe plansIndex Advisor may advise EVI indexes for use with the EVI RRN probe plan. To limit excessiveindex advice, SQE won't consider advising EOA unless there's at least one single column EVI overone or more of the projection columns for the query.The EVI RRN probe plan advice requires one or more indexes to be created as a dependentset. If any of the EVI indexes are missing, the optimizer won't be able to cost and choose thesedependent indexes for implementation of the EVI RRN probe.The Reason Advised column will have reason code 'I8' if you query QSYS2/SYSIXADV directly.If you use IBM i Navigator to examine the index advice, you'll see something similar to the oneshown in Figure 1.Figure 1. Index advice and EOAThe advice is on a per table basis for the query.Indexes are advised only when the following statements are true: At least one of the columns in the select list of the query needs to have an existing singlecolumn EVI. The number of columns that do not match to an existing EVI must be less than 20.Encoded-vector index (EVI) only access in IBM DB2 for iPage 4 of 10 All columns must be eligible to be an index key.When Index Advisor shows a highly dependent advice, use of the exact match capability fromShow Statements to find the query in the plan cache is helpful. Once found, use Visual Explain todiscover the dependent index advice specific to that query.EVI RRN probe plan advice exampleIn the following example, assume that the following indexes already exist. A radix index over the column ORDERDATE An EVI index over the column ORDERPRIORITYSELECT QUANTITY, ORDERPRIORITY, EXTENDEDPRICE FROM ITEM FACTWHERE SHIPMODE 'TRUCK'ORDER BY ORDERDATE DESCLIMIT 50OFFSET 0;The Index Advisor will advise an EOA style EVI index over all over missing columns: QUANTITYand EXTENDEDPRICE. While EOA style index advice appears in the IBM i Navigator IndexAdvisor, if you have a specific query, you should use the Index Advisor of Visual Explain to quicklyidentify the missing indexes.Figure 2 shows the EVI advice generated for the query, when examining the advice using VisualExplain.Figure 2. EOA and index advice within Visual ExplainWhenever you are wondering why an existing index was not used by SQE, you can look in twoplaces to gain some additional insight.1. Analyze: If you capture an SQL performance monitor, you can use IBM i Navigator or IBM iAccess Client Solutions to analyze the monitor. If you drill into the statements detail for SQE,Encoded-vector index (EVI) only access in IBM DB2 for iPage 5 of 10

developerWorks can access the Indexes Considered action to see the indexes considered by SQE and abasic explanation of why the index was not used. Figure 3 shows only that reason code 20. Averbose reason might appear in a future IBM i release.2. Visual Explain: The same information about indexes considered can be found under theInformation About the Plan Performed section and the List of Indexes Optimized detail.Figure 4 shows that the EVIs are not used by the optimizer for reason 20.With EOA support, the indexes considered detail has been extended to include two additionreason codes: Reason code 20: The access path cannot be used because Encoded Vector Index does notfit in memory.User response: Consider using a private pool, as discussed in the next section. Reason code 21: The access path cannot be used because not all referenced columns hadan Encoded Vector Index.User response: Create single column EVIs over all referenced columns.Note: Refer to the QQ1000 column of the QQQ3007 table within IBM Knowledge Center forexplanations of reasons 1 to 19.Figure 3. IBM i Navigator analyze indexes consideredEncoded-vector index (EVI) only access in IBM DB2 for iPage 6 of 10 Figure 4. Visual Explain indexes considered and not usedFair share of memoryBecause indexes can be very large, SQE has taken a conservative approach when consideringwhether to use EOA. If the memory impact for an EOA implementation would exceed the user'sfair share of memory, SQE will avoid using EOA.To mitigate this issue, you can configure a private storage pool and use the QAQQINIMEMORY PREFERENCE option to direct SQE to use that pool for your query. This is a usefultuning technique for queries beyond EOA, but it is a critical success factor for those who need touse EOA with large data. See the end of this article for a pointer to memory preference detail.Figure 5 shown a screen capture from Visual Explain containing the query implementation after wehave created the advised EVI indexes and mitigated the fair share memory issue.Encoded-vector index (EVI) only access in IBM DB2 for iPage 7 of 10

developerWorks 5. Visual Explain of an EOA implementationGerman football and EOAThese two topics have more in common than you might imagine. To demonstrate how EOA mightbe used, we need to look no further than the German Bundesliga football league. In this fictionalexample, we have a very large database table consisting of one row for each fan. The row givesus information such as the fan's name, email address, and of course, the team for which the fanroot, cheer and encourage through thick and thin, ups and downs, wins and losses.The query for this example is quite simple. Find the email addresses of all the fans of the Eintrachtclub, based in Frankfurt. Figure 5 contains a visual explanation of EOA in action.We begin our examination at red circle numbered 1 in Figure 5. Using a traditional EVI built overthe column FCName (that is the football club name), we are able to efficiently find the rows inthe table that matches our selection criteria. The traditional EVI has maintained a symbol tablecontaining the various football club names and the associated symbol table code for that value.Because there are only 18 football clubs in Bundesliga, we can conclude that the cardinality ofFCName is very low.At the red circle numbered 2, we see SQE using the EVI symbol table code (5), scanning the EVIvector and identifying the rows that match 'Eintracht'. Well done SQE, cheers from the crowd!At the red circle numbered 3, we see EOA appear. The vector scan in the previous step has notonly identified the selection rows, but because the vector is constructed in a relative row order,we can use that insight to quickly identify the same rows in vector of any associated EVIs. In theexample, such an EVI is built over the Email Address column. EOA can access column valuesEncoded-vector index (EVI) only access in IBM DB2 for iPage 8 of 10 starting with the EVI vector and moving up to the EVI symbol table. As shown in the example, thisis the opposite order of processing, as compared to using EVIs for selection of rows.At red circle numbered 4, we see that the EVI vector code is used to find the column value in theEVI symbol table. This EOA style EVI could have a very high level of cardinality and still be useful.It is important to understand this point, because EVIs were traditionally targeted to lower cardinalityselection of data. The other consideration is that if you know that your EOA style EVI index willhave higher cardinality, you should use the WITH xx DISTINCT VALUES so you can start with a wideEVI vector. While DB2 for i can expand the vector size as cardinality pushes it wider, it is better tostart with the widest vector if you anticipate many unique values to appear in the column.Figure 6. Bundesliga football and EOAFinally, at red circle numbered 5, our query returns the results by accessing only two encodedvector indexes. DB2 for i and SQE has used EOA to "ein Tor schießen", which translates to"score a goal"!SummaryThe goal of this article is to raise awareness to EOA and provide of the basic knowledge to helpyou get started. Indexes aren't free, so you probably should not just go ahead and build singlecolumn EVIs over the columns of your favorite table. Instead, look for an under-performing queryand take EOA for a test drive.Encoded-vector index (EVI) only access in IBM DB2 for iPage 9 of 10

developerWorks you're responsible for the indexing strategy, you should consider using a couple of resources tohelp you along the way. QAQQINI memory preference by pool nameDatabase performance and query optimizationBOOM! Another IBM i 7.1 Technology Refresh: (EVI aggregates)IBM DB2 for i – indexing methods and strategies white paperDB2 for i SQL Performance Workshop Copyright IBM Corporation -vector index (EVI) only access in IBM DB2 for iPage 10 of 10

Show Statements to find the query in the plan cache is helpful. Once found, use Visual Explain to discover the dependent index advice specific to that query. EVI RRN probe plan advice example In the following example, assume that the following indexes