mopdf.com

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 ibm.com/developerWorks/Enabling 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

ibm.com/developerWorks/developerWorks 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 ibm.com/developerWorks/AliasesSystem 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 1.1.5.0IBM 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 support.htmlwww-912.ibm.com/s m/s dir/SLINE003.NSF/PTFbyNumber/SF99368Next (planned) updateJune, 2016July, 2016 Version 1.1.6.0New 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

ibm.com/developerWorks/developerWorks 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