ERserver. iseries. DB2 Universal Database for iseries - Database Performance and Query Optimization

(1)

iSeries

DB2

Universal

Database

for

iSeries

-Database

Performance

and

Query

Optimization

(2)

(3)

iSeries

DB2

Universal

Database

for

iSeries

-Database

Performance

and

Query

Optimization

(4)

©CopyrightInternationalBusinessMachinesCorporation2000,2001,2002.Allrightsreserved.

(5)

About

DB2

UDB

for

iSeries

Database

Performance

and

Query

Optimization

Thisbookexplainsto programmersanddatabaseadministrators: v

Howto usethetoolsandfunctionsthatareavailable inDB2UDBforiSeriesforgettingthebest performanceoutofyour databaseapplications

v Howto runqueriesthatmakefulluseofthecapabilitiesof theDB2UDBfor iSeriesintegrated database.

FormoreinformationonDB2UDBforiSeriesguidelinesandexamplesfor implementationinan applicationprogrammingenvironment,seethefollowinginformationintheDatabase andFilesSystems categoryof theiSeriesInformationCenter:

v SQLReference

v SQLProgrammingConcepts

v SQLProgrammingwithHostLanguages v

SQLCallLevelInterfaces(ODBC) v DatabaseProgramming

v Query/400Use v ODBC

v SQLJ

JavaDatabaseConnectivity(JDBC)informationcanbefoundintheIBM®Developer KitforJava™under ProgrammingintheiSeries™_Information_Center.

Foradditionalinformationonadvanceddatabasefunctions,seetheDATABASE2/400AdvancedDatabase Functionsbook,GG24-4249.

Who

should

read

the

Database

Performance

and

Query

Optimization

book

Thisinformationisforprogrammersanddatabaseadministratorswhounderstandbasicdatabase

applicationsandwantto understandhowto tunequeries.Thisinformationshowshow tousetheavailable toolsfor debuggingqueryperformanceproblems.

Youshouldbefamiliarwithlanguagesandinterfaces,includingthefollowing: v COBOLforiSeries

v ILE COBOLfor iSeries v iSeriesPL/I

v

ILE Cfor iSeries v ILE C++

v VisualAge ®

C++foriSeries v REXX

v RPG III(partofRPG foriSeries) v ILE RPGfor iSeries

v Query/400

v TheOPNQRYFcommand v Calllevelinterfaces(CLI) v

(10)

v JDBC

Assumptions

relating

to

SQL

statement

examples

TheexamplesofqueriesthatareshowninthisbookarebasedonthesampletablesinAppendixA,″DB2 UDBfor iSeriesSampleTables,″ oftheSQLProgrammingConceptsbook.FortheSQLexamples,

assumethefollowing:

v They areshownintheinteractiveSQLenvironment ortheyarewritteninILECorinCOBOL.EXEC SQLandEND-EXECareusedtodelimit anSQLstatementinaCOBOLprogram.Adescription ofhow to useSQLstatementsinaCOBOLprogramisprovidedin″CodingSQLStatementsinCOBOL Applications″of theSQLProgrammingwithHostLanguagesbook.Adescriptionof howto useSQL statementsinanILECprogramisprovided in″CodingSQLStatementsinCApplications″of theSQL ProgrammingwithHostLanguagesbook.

v EachSQLexampleisshownonseverallines,witheachclauseof thestatementonaseparateline. v SQLkeywordsarehighlighted.

v TablenamesprovidedinAppendixA,″DB2UDBforiSeriesSampleTables″oftheSQLProgramming Conceptsuse thecollection CORPDATA.TablenamesthatarenotfoundinAppendixA,″DB2 UDBfor iSeriesSampleTables,″shouldusecollectionsyoucreate.

v Calculatedcolumnsareenclosedinparentheses,(),andbrackets,[]. v TheSQLnamingconvention isused.

v

TheAPOSTandAPOSTSQLprecompileroptionsareassumedalthoughtheyarenotthedefaultoptions inCOBOL.CharacterstringconstantswithinSQLandhost languagestatementsaredelimitedby apostrophes (’).

v Asortsequenceof *HEXisused,unlessotherwisenoted.

v ThecompletesyntaxoftheSQLstatementisusuallynotshown inanyoneexample.For thecomplete description andsyntaxofanyof thestatementsdescribedinthis guide,seetheSQLReference. Whenevertheexamplesvaryfromtheseassumptions,itisstated.

Becausethisguideisfortheapplicationprogrammer,most oftheexamplesareshownasiftheywere writteninanapplicationprogram.However,manyexamplescan beslightlychangedandruninteractively byusinginteractive SQL.Thesyntaxof anSQLstatement,whenusinginteractiveSQL, differsslightly fromtheformatofthesamestatementwhenit isembeddedinaprogram.

How

to

interpret

syntax

diagrams

Throughoutthis book,syntaxisdescribedusingthestructuredefinedasfollows:

v Readthesyntaxdiagramsfromleftto right,fromtoptobottom,followingthepathof theline. The───symbol indicatesthebeginningofastatement.

The───symbolindicatesthatthestatementsyntaxiscontinuedonthenextline. The───symbolindicatesthata statementiscontinuedfromthepreviousline. The───symbol indicatestheendofastatement.

Diagramsof syntacticalunitsotherthancompletestatementsstartwiththe───symbolandendwith the───symbol.

v Requireditemsappearonthehorizontalline(themainpath).

required_item

v Optionalitemsappearbelowthemain path. required_item

optional_item

(11)

If anoptional itemappearsabovethemain path,thatitemhasnoeffectontheexecutionofthe statementandisusedonly forreadability.

required_item

optional_item

v If youcanchoosefromtwoormore items,they appearvertically, inastack.

If youmustchooseoneof theitems,oneitem ofthestackappearsonthemain path. required_item required_choice1

required_choice2

If choosingoneof theitemsisoptional,theentire stackappearsbelowthemainpath. required_item

optional_choice1 optional_choice2

If oneof theitemsisthedefault,it willappearabovethemain pathandtheremainingchoiceswillbe shownbelow. required_item default_choice optional_choice optional_choice

v Anarrow returningtotheleft,abovethemainline, indicatesanitem thatcanberepeated.

required_item repeatable_item

If therepeat arrowcontainsacomma,youmust separaterepeateditemswitha comma.

required_item

,

repeatable_item

Arepeat arrowaboveastackindicatesthatyoucanrepeat theitemsinthestack.

v Keywordsappearinuppercase(forexample,FROM).Theymust bespelled exactlyasshown.Variables appearinalllowercase letters(for example,column-name).Theyrepresent user-suppliednamesor values.

v If punctuationmarks,parentheses,arithmeticoperators,or othersuchsymbolsareshown,youmust enterthem aspartof thesyntax.

What’s

new

for

V5R2

Themajornewfeaturescovered inthis bookinclude: v Arrival sequencejoins

v

Redesignedthequeryengine v Statisticsmanager

Code

disclaimer

information

Thisdocumentcontainsprogramming examples.

IBMgrantsyouanonexclusivecopyrightlicenseto useallprogrammingcodeexamplesfromwhichyou cangeneratesimilarfunction tailoredtoyourown specificneeds.

| | | | | | | |

(12)

Allsample codeisprovidedbyIBMforillustrativepurposesonly.Theseexampleshavenotbeen

thoroughlytestedunder allconditions.IBM,therefore,cannotguaranteeor implyreliability, serviceability, orfunctionof theseprograms.

Allprograms containedhereinareprovidedto you″ASIS″withoutany warrantiesofanykind.Theimplied warrantiesofnon-infringement, merchantabilityandfitness foraparticularpurposeareexpressly

(13)

Chapter

1. Database

performance

and

query

optimization:

Overview

Thegoalofdatabaseperformancetuningisto minimizetheresponsetimeof yourqueriesandtomake thebestuseofyourserver’s resourcesbyminimizingnetworktraffic, diskI/O, andCPUtime.This goal canonlybeachievedbyunderstandingthelogical andphysicalstructureofyourdata, understandingthe applicationsusedonyourserver,andunderstandinghowthemanyconflictingusesofyourdatabasemay impactdatabaseperformance.

Thebestwayto avoidperformanceproblemsistoensurethatperformanceissuesarepartofyour ongoingdevelopmentactivities.Manyofthemostsignificantperformanceimprovementsarerealized throughcarefuldesignat thebeginningofthedatabasedevelopmentcycle.Tomosteffectively optimize performance,youmustidentify theareasthatwillyieldthelargestperformanceincreasesoverthewidest varietyofsituationsandfocusyouranalysisonthoseareas.

InV5R2,DB2UDBfor iSeriesredesignedthequery engine,whichshouldprovideperformance

improvementfor manySQLread-onlyqueries.Aredbookwillbepublishedinlate2002thatwillprovide detailsontheperformanceimprovements, whichtypesofqueriescantakeadvantageof theredesign,and howto aidtheoptimizerintakingadvantageofthenewimprovements.TheSQLQueryEnhancements redbookcontainsinformationaboutthenewquery engine.

Understandingaccesspathsandthequeryoptimizer

SinceiSeriesautomaticallymanagesmanyhardware resources,andusesacost-based optimization formulatodeterminethemost efficientaccessplanforrunninganSQLstatement,it isimportant toknow howtheserverdeterminesthemost efficientaccessmethodandwhatfactorsdeterminetheir selectionby theserver.Thesetopicsarecovered inDataaccessmethods.Inaddition,aclear understandingofthe iSeriesquery optimizerwillalso helpyoudesignqueriesthatleverage thequery optimizer’scost estimationanddecision-makingrules.

Improvingyour queries

Onceyouarefamiliarwiththeseconcepts,youcanincrementallyimproveyourqueriesbyreviewingthe materialfoundinthefollowingtopics:

Topic Description

Optimizingqueryperformanceusing queryoptimizationtools

Describeshowyoucanusequeryoptimizationtoolstoimprovedataretrieval timesbygatheringstatisticsaboutyourqueriesorcontrollingtheprocessingof yourqueries.Withtheresultsthatthesetoolsprovide,youcanthenchangethe dataaccessmethodchosenbytheserverorcreatethecorrectindexesanduse themeffectively.

Usingindexestospeedaccessto largetables

Describestheindex-basedretrievalmethodforaccessingtablesandhowto createeffectiveindexesbyavoidingsuchthingsasnumericconversions, arithmeticexpressions,characterstringpadding,andtheuseoflikepatterns. Increasingdatabaseperformance

throughapplicationdesign

Describeshowthecorrectdesignofuserapplicationscanimproveperformance. Applicationdesignconsiderationsincludeparameterpassingtechniques,using livedata,reducingthenumberofopenoperations,andretainingcursor positions.

Improvingdatabaseperformance usingprogrammingtechniques

Describeshowthecorrectprogrammingtechniquescanimproveperformance. Amongthetechniquescoveredare:usingtheOPTIMIZEclause,usingFETCH nROWS,usingINSERTnROWS,controllingthedatabasemanagerblocking, optimizingthenumberofcolumnsselectedwithSELECTstatements,eliminating redundantvalidation,andpaginginteractivelydisplayeddata.

| | | | |

(14)

GeneraliSeriesperformance Describessomegeneralserverconsiderationsandhowtheyaffectthe performanceofyourqueries.

Creating

queries

Youcancreatequeriesthroughanyofthefollowinginterfaces: v

SQL

v OpenQueryFile(OPNQRYF)command v Opendatabaseconnectivity (ODBC) v QueryforiSeries

v JavaDatabaseConnectivity(JDBC) v Calllevelinterface(CLI)

(15)

Chapter

2. Data

access

on

DB2

UDB

for

iSeries:

data

access

paths

and

methods

ThissectionintroducesthedataaccessmethodsthatDB2Universal DatabaseforiSeriesandthe LicensedInternalCodeuseto processqueriesandaccessdata.Thedataaccessmethods aregrouped intononkeyed,keyed,andtemporaryresultfileaccessmethods.

TheiSeriesfundamentallyusestwomethods toretrievedataspecifiedinaquery;throughanindex(keyed accessmethods) ordirectlythroughthetable(nonkeyedaccessmethods).Theseaccessmethods canbe combinedinmanywaystoretrievedata.Adataaccessmethodcan beatable scan,anindex,a

combinationof both,oranencodedvectorindex.

Table

scan

Atablescan,orarrivalsequence,usestheorderof rowsasthey arestoredinthetable tolocatedatathat isspecifiedina query.Processingtablesusingthetable scanissimilar toprocessingsequential ordirect filesontraditionalsystems.

Index

Anindex,orkeyedsequenceaccesspath,providesaccesstoa tablethatisarrangedaccordingtothe contentsof keycolumns.Thekeyedsequenceistheorderinwhich rowsareretrieved.Theaccesspathis automaticallymaintainedwheneverrowsareaddedtoor deletedfromthetable,orwheneverthecontents oftheindexcolumnsarechanged.Thebestexamplesof anindexisanindexthatiscreatedwiththe CREATEINDEXstatement,ora keyedlogical filethatiscreatedwiththeCRTLFcommand.

Columnsthataregood candidatesforindexesare: v

Columnsthatarefrequentlyreferencedinrowselectionpredicates. v Columnsthatarefrequentlyreferencedingroupingorordering. v

Columnsthatareusedto jointables(see“Joinoptimization”onpage35).

Encoded

vector

index

Anencodedvectorindexprovidesaccessto adatabasetable byassigningcodestodistinctkeyvalues andthenrepresentingthesevaluesinanarray.Theelementsof thearraycanbe1, 2,or 4bytesin length,dependingonthenumberofdistinctvaluesthatmustberepresented.Becauseof theircompact sizeandrelative simplicity,encoded vectorindexesprovideforfaster scansthatcanbemoreeasily processedinparallel.

YoucreateencodedvectorindexesbyusingtheCREATEENCODEDVECTORINDEXstatement.See Whatareencoded vectorindexesforinformationontheuseandmaintenanceofencodedvectorindexes. Foradditionalinformationabout acceleratingyourqueries withencoded vectorindexes

,goto theDB2 UniversalDatabasefor iSeriesweb pages.

Data

access:

data

access

methods

TheLicensedInternalCodeandDB2UniversalDatabase foriSeriessharetheworkonaccessmethods. TheLicensedInternalCodedoes thelow-levelprocessing whichincludesselection,joinfunctions, hashing,andindexcreation.

(16)

Thequeryoptimizationprocesschoosesthemostefficientaccessmethodforeach queryandkeepsthis informationintheaccessplan.Thetypeofaccessisdependentonthenumberofrows,theexpected numberofpagefaults1,andothercriteria.

Youcanusethetoolsandtipsthataredescribedlaterinthisbook toinfluencethewayinwhichthequery optimizerimplementsyourqueries.

Theoptimizerusesanyofthefollowingmethods toretrievedata.See“Dataaccessmethods:Summary” onpage 5fora summaryof thesemethods:

v Tablescanmethod(a dataspaceisaninternalobjectthatcontainsthedataina table) v Paralleltable prefetchmethod

v Indexscan-keyselectionmethod v Indexscan-keypositioningmethod v Paralleltable orindexpreload v Index-from-index method v Indexonlyaccessmethod v Hashingmethod

v

Bitmapprocessingmethod v Sort accessmethod

Accessingdatawiththe DB2UDBSymmetricMultiprocessingmethods

TheDB2UDBSymmetricMultiprocessingfeatureprovidestheoptimizerwithadditionalmethodsfor retrievingdatathatinclude parallelprocessing.

Symmetricalmultiprocessing(SMP)isa formof parallelismachievedonasingleserverwheremultiple processors(CPUandI/Oprocessors) thatsharememoryanddiskresourceworksimultaneouslytowards achievingasingleendresult.Thisparallelprocessing meansthatthedatabasemanagercanhavemore thanone(orall)of theserverprocessorsworkingona singlequerysimultaneously.Theperformanceofa CPUboundquerycan besignificantlyimprovedwiththisfeatureonmultiple-processorserversby

distributingtheprocessorloadacrossmorethanoneprocessorontheserver.

ThefollowingmethodsareavailabletotheoptimizeroncetheDB2UDBSymmetricMultiprocessing featurehasbeen installedonyourserver:

v Paralleltable scanmethod

v Parallelindexscan-keyselectionmethod v Parallelindexscan-keypositioningmethod v Parallelindexonlyaccessmethod

v Parallelhashing method

v Parallelbitmapprocessingmethod

Additionalconsiderations:

Thefollowingtopicsprovideadditionalbackgroundinformationontheaccessmethods: v “Orderingquery results”onpage7

v “Enablingparallel processingforqueries” onpage7 v “Spreadingdata automatically”onpage 8

(17)

Data

access

methods:

Summary

Thefollowingtableprovidesasummaryofdatamanagementmethods thatarediscussedinthisbook.

Table1.Summaryofdataaccessmethods

AccessMethod

Selection

Process GoodWhen NotGoodWhen SelectedWhen Advantages

“Tablescan accessmethod” onpage8

Readsallrows. Selectioncriteria appliedtodatain dataspace. Approx.>20% rowsselected. Approx.<20% rowsselected. Noordering, grouping,orjoining andapprox.>20% rowsselected. Minimizespage I/Othrough pre-fetching. “Paralleltable prefetchaccess method”on page10 Dataretrieved fromauxiliary storageinparallel streams.Readsall rows.Selection criteriaappliedto dataindataspace. Approx.>20% rowsselected. 1. Adequate activememory available. 2. Querywould otherwisebe I/Obound. 3. Dataspread acrossmultiple diskunits. Approx.<20% rowsselected. QueryisCPU bound. Noordering, grouping,orjoining andapprox.>20% rowsselected,and theserverjobhas beenconfiguredto takeadvantageof I/Oparallelism.

Minimizeswait timeforpage I/Othrough paralleltable prefetching. “Paralleltable scanmethod” onpage11

Datareadand selectedinparallel tasks. Approx.>10% rowsselected, largetable. 1. Adequate activememory available. 2. Dataspread acrossmultiple diskunits. 3. DB2UDB Symmetric Multiprocessing installed. 4. Multi-processor server. Approx.<10% rowsselected. QueryisI/Obound onauniprocessor server. 1. DB2UDB Symmetric Multiprocessing installed. 2. CPUboundor runningona multiprocessor server. Significant performance especiallyon multiprocessors. “Indexscan-key selectionaccess method”on page13 Selectioncriteria appliedtoindex. Ordering, grouping,and joining. Largenumberof rowsselected. Indexisrequired andcannotuse indexscan-key positioningmethod. Dataspace accessedonly forrows matchingindex scan-key selection criteria. “Parallelindex scan-key selectionaccess method (availableonly whentheDB2 UDBSymmetric Multiprocessing featureis installed)”on page14 Selectioncriteria appliedtoindexin paralleltasks. Sizeofindexis muchlessthan thedataspace. DB2UDB Symmetric Multiprocessing mustbeinstalled. Largenumberof rowsselected. Whenorderingof resultsnotrequired.

BetterI/O overlapbecause paralleltasks performtheI/O. Canfullyutilize multiprocessor servers.

(18)

Table1.Summaryofdataaccessmethods (continued)

AccessMethod

Selection

“Indexscan-key positioning accessmethod” onpage15 Selectioncriteria appliedtorangeof indexentries. Commonlyused option. Approx.<20% rowsselected. Approx.>20% rowsselected. Selectioncolumns matchleft-mostkeys andapprox.<20% rowsselected. Indexand dataspace accessedonly forrows matching selection criteria. “Parallelindex scan-key positioning accessmethod (availableonly whentheDB2 UDBSymmetric Multiprocessing featureis installed)”on page19 Selectioncriteria appliedtorangeof indexentriesin paralleltasks. Approx.<20% rowsselected. DB2UDB Symmetric Multiprocessing mustbeinstalled. Largenumberof rowsselected. 1. Whenordering ofresultsnot required. 2. Selection columnsmatch left-mostkeys andapprox.< 20%rows selected. 1. Indexand dataspace accessed onlyforrows matching selection criteria. 2. BetterI/O overlap because parallel tasks performthe I/O. 3. Canfully utilizea multiprocessor servers. “Index-from-indexaccess method”on page22 Keyrow positioningon permanentindex. Buildstemporary indexover selectedindex entries. Ordering,grouping andjoining. Approx.>20% rowsselected. Noexistingindexto satisfyorderingbut existingindexdoes satisfyselectionand selectingapprox.< 20%rows. Indexand dataspace accessedonly forrows matching selection criteria. “Sortaccess method”on page28

Orderdataread usingtablescan processingor indexscan-key positioning.

Approx.>20% rowsselectedor largeresultsetof rows.

Approx.<20% rowsselectedor smallresultsetof rows.

Orderingspecified; eithernoindex existstosatisfythe orderingoralarge resultsetis expected.

Seetablescan andindex scan-key positioningin thistable. “IndexOnly AccessMethod” onpage21 Donein combinationwith anyoftheother indexaccess methods

Allcolumnsused inthequeryexist askeycolumns.

Approx.<20% rowsselectedor smallresultsetof rows.

Allcolumnsusedin thequeryexistas keycolumns. ReducedI/Oto thedataspace. “Paralleltableor indexbased preloadaccess method”on page22

Indexortabledata loadedinparallel toavoidrandom access. Excessiverandom activitywould otherwiseoccur againsttheobject andactive memoryis availabletohold theentireobject.

Activememoryis already

overcommitted.

Excessiverandom activitywouldresult fromprocessingthe queryandactive memoryisavailable whichcanholdthe entireobject. Randompage I/Oisavoided whichcan improveI/O boundqueries.

(19)

Table1.Summaryofdataaccessmethods (continued)

AccessMethod

Selection

“Hashingaccess method”on page23(Parallel ornon-parallel) Rowswith common correlateddata havingacommon value. Longerrunning groupingandjoin queries. Shortrunning queries. Joinorgrouping specified. Reduces randomI/O whencompared toindex methods.IfDB2 UDBSymmetric Multiprocessing isinstalled, possible exploitationof SMP parallelism. “Bitmap processing method”on page24 Keyposition/index scan-keyselection usedtobuild bitmap.Bitmap usedtoavoid touchingrowsin table. Selectioncanbe appliedtoindex andeitherapprox. >5%orapprox. <25%rows selectedoranOR operatoris involvedin selectionthat precludestheuse ofonlyoneindex.

Approx.>25% rowsselected.

Indexesmatch selectioncriteria.

Reducespage I/Otothedata space.Allows multipleindexes pertable.

Ordering

query

results

YoumustspecifyanORDERBYclause (orOPNQRYFKEYFLDparameter)toguarantee aparticular orderingoftheresults.Beforeparallelaccessmethodswere available,thedatabasemanagerprocessed tablerows(andkeyedsequences)inasequentialmanner.Thiscausedthesequencingoftheresultsto be somewhatpredictable(generallyrowswere retrievedintheorderinwhichthey werestoredinthe

dataspace)eventhoughorderingwasnotincludedintheoriginal queryrequest.Becauseparallel methods causeblocksof tablerowsandkeyvaluestobeprocessedconcurrently,theorderingof theretrieved resultsbecomesmorerandomandunpredictable.

AnORDERBYclauseistheonlywaytoguaranteethespecificsequencingoftheresults.However, an orderingrequestshouldonlybespecifiedwhenabsolutely required,becausethesortingof theresultscan increasebothCPUutilizationandresponsetime.

Enabling

parallel

processing

for

queries

Youmustenableparallel processingforquerieswhenyousubmitthem orwhenyoucodeyour applications.Theoptimizerdoes notautomatically useparallelismasthechosenaccessmethod. Youcanusethesystem-valueQQRYDEGREE,thequery optionsfile,ortheDEGREEparameteronthe ChangeQueryAttributes(CHGQRYA) commandto controlthedegreeofparallelismthatthequery optimizeruses.See“Controlparallelprocessing forqueries”onpage98forinformationonhowto control parallelprocessing.

Asetofdatabasesystemtasksiscreatedatserverstartupforusebythedatabasemanager.The databasemanagerusesthetaskstoprocess andretrievedatafromdifferentdiskdevices.Sincethese taskscanberunonmultipleprocessorssimultaneously,theelapsed timeofa querycanbereduced.Even thoughmuchof theI/OandCPUprocessingof aparallelquery isdone bythetasks,theaccountingof the | | | | | | | | | | | | | | | | | | | | | | | | | | | |

(20)

I/OandCPUresourcesusedaretransferredto theapplicationjob.ThesummarizedI/OandCPU resourcesfor thistypeof applicationcontinueto beaccuratelydisplayed bytheWorkwithActiveJobs (WRKACTJOB)command.

Spreading

data

automatically

DB2UniversalDatabasefor iSeriesautomatically spreadsthedataacrossthediskdevicesavailableinthe auxiliarystoragepool(ASP)wherethedataisallocated.Thisensuresthatthedataisspreadwithoutuser intervention.Thespreadingallowsthedatabasemanagerto easilyprocess theblocks ofrowsondifferent diskdevicesinparallel.

EventhoughDB2UniversalDatabasefor iSeriesspreads dataacrossdiskdeviceswithinanASP, sometimestheallocationof thedataextents (contiguoussets ofdata)mightnotbespreadevenly.This occurswhenthereisunevenallocationofspaceonthedevices,or whenanewdeviceisaddedtothe ASP.Theallocation ofthedataspacemaybespreadagainbysaving,deleting,andthenrestoringthe table.

Table

scan

access

method

Allrowsinthetable areread.Theselectioncriteriaareappliedtoeach row,andonly therowsthatmatch thecriteriaarereturnedtothecallingapplication.Therowsinthetableareprocessedinnoguaranteed order.Ifyouwanttheresultinaparticularsequence,youmustspecifytheORDERBYclause(or OPNQRYFKEYFLDparameter).

Tablescancanbeefficientfor thefollowing reasons:

v It minimizesthenumberofpageI/Ooperationsbecauseallrowsinagivenpageareprocessed,and once thepage isinmain storage,thepage isnotretrievedagain.

v Thedatabasemanagercaneasilypredictthesequenceofpagesfromthedataspace forretrieval.For this reason,thedatabasemanagercanscheduleasynchronousI/Oofthepagesintomainstoragefrom auxiliarystorage.Thisiscommonlyreferred toasprefetching.Thisisdonesothatthepageis

availableinmainstoragewhenthedatabasemanagerneedsto accessthedata.

Wherethetablescanaccessmethodismosteffective

Thisselectionmethodisgoodwhenalargepercentageof therowsistobeselected.Alargepercentage isgenerally20%or more.

Wherethetablescanaccessmethodisleasteffective

Tablescanprocessing isnotefficientwhenasmallpercentageof rowsinthetablewillbeselected. Becauseallrowsinthetableareexamined,thisleadsto unnecessaryuseof I/Oandprocessingunit resources.

Considerationsfortablescanaccess

Tablescanprocessing canbeadverselyaffectedwhenrowsareselectedfromatable thatcontains deletedrows.This isbecausethedeleteoperationonlymarksrowsasdeleted. Fortable scanprocessing, thedatabasemanagerreads allofthedeletedrows,eventhoughnoneof thedeletedrowsareever selected.You shouldusetheReorganizePhysicalFileMember(RGZPFM)CLcommandto eliminate deletedrows.ByspecifyingREUSEDLT(*YES)onthephysical file,youcanalsoreuse thedeletedrow space.AllSQLtablesarecreatedwithREUSEDLT(*YES).

PRTSQLINFcommandmessages

ThemessagescreatedbythePRTSQLINFCLcommandto describea queryinanSQLprogramwhich is usingthedataspaceselectionmethodwouldappearasfollows:

(21)

SQL4010 Table scan access for table 1.

Selectionalgorithmsfortablescanaccessmethod

TheLicensedInternalCodecanuseoneof twoalgorithmsforselectionwhena tablescanisprocessed, derived-columnselectionanddataspace-only selection.Thedataspace-only selectionhastwoforms -dataspaceloopinganddataspace-onlyfiltering. Dataspacelooping processeslargesets ofrecords efficiently,whiledataspace-onlyfiltering isanotherstepto eliminaterecordspriortoderivedoperations. Allaccessmethodsusedataspacefiltering, butdataspaceloopingisonlyused whena tablescanis processingahigh percentageofrecords.

Thefollowingpseudocodeillustratesthederivedcolumn selectionalgorithm:

DO UNTIL END OF TABLE

1. Address the next (or first) row

2. Map all column values to an internal buffer, performing all derived operations.

3. Evaluate the selection criteria to a TRUE or FALSE value using the column values as they were copied to internal buffer.

4. IF the selection is TRUE THEN

Copy the values from the internal buffer into the user’s answer buffer.

ELSE

No operation END

Thetable-scanselectionalgorithmisasfollows: DO UNTIL END OF TABLE

1. Calculate a search limit. This limit is usually the number of rows which are already in active memory, or have already had an I/O request done to be loaded into memory.

2. DO UNTIL (search limit reached or row selection criteria is TRUE) a. Address the next (or first) row

b. Evaluate any selection criteria which does not require a derived value directly for the dataspace row.

END

3. IF the selection is true THEN

a. Map all column values to an internal buffer, performing all derived operations.

b. Copy the values from the internal buffer into the user’s answer buffer.

ELSE

No operation END

Thetable-scanselectionalgorithmprovidesbetterperformancethanderivedcolumnselectionfor two reasons:

(22)

v Theloopinstep2of thetable-scanselectionalgorithmisgenerated intoanexecutable codeburst. Whena smallpercentageof rowsisactuallyselected,DB2UniversalDatabasefor iSerieswillbe runningthissmallprogramuntila rowisfound.

Guidelinesforcodingqueries

Noactionisnecessaryforqueriesthatusethetable scanselectionalgorithmofthetablescanaccess method.Anyqueryinterface canusethisimprovement.However,thefollowingguidelinesdetermine whetheraselectionpredicatecanbeimplementedasadataspaceselection:

v Theoptimizeralwaysensures thattheoperands foranyselectionitem arecompatible,thereforeyou canimprove yourqueriesbymakingsuretheoperandsarecompatiblebeforeprocessing thequery. v Neitheroperandof thepredicatecanbeanykindof aderivedvalue,function,substring,concatenation,

or numericexpression.

v Whenbothoperands ofa selectionpredicatearenumericcolumns,bothcolumnsmusthave thesame type, scale,andprecision;otherwise,oneoperandismappedintoaderivedvalue.Forexample,a DECIMAL(3,1) mustonlybecomparedagainstanotherDECIMAL(3,1)column.

v Whenoneoperandof aselectionpredicateisa numericcolumnandtheotherisaconstantorhost variable,thenthetypesmust bethesameandtheprecisionandscaleof theconstantorhost variable must belessthanorequaltothatof thecolumn.

v

Selectionpredicatesinvolvingpackeddecimalornumerictypesof columnscanonly bedone ifthetable wascreatedbytheSQLCREATETABLEstatement.

v Avarying-lengthcharacter columncannotbereferencedintheselectionpredicate.

v Whenoneoperandof aselectionpredicateisa charactercolumnandtheotherisaconstantorhost variable,thenthelengthof thehostvariablecannotbegreaterthanthatof thecolumn.

v Comparisonofcharacter-columndatamustnotrequireCCSIDor keyboardshift translation.

Itcanbeimportantto avoidderived-column selectionbecausethereductioninCPUandresponsetime for tablescanselectioncanbelarge,insomecasesashighas70-80%.Thequeriesthatwillbenefitthemost fromdataspaceonlyselectionarethosewherelessthan60%ofthetable isactuallyselected.Thelower thepercentageof rowsselected,themorenoticeabletheperformancebenefitwillbe.

Parallel

table

prefetch

access

method

DB2UniversalDatabasefor iSeriescan alsouseparalleltable prefetchprocessingto shortenthe processingtimethatisrequiredforlong-running, I/O-boundtable scanqueries.

Thismethodhasthesamecharacteristicsasthetablescanmethod;however,theI/Oprocessing isdone inparallel. Thisisaccomplishedbystartingmultipleinput streamsfor thetable toprefetchthedata.

Wheretheparalleltableprefetchaccessmethodis mosteffective

Thismethodismosteffective whenthefollowingaretrue: v Thedataisspreadacrossmultiplediskdevices.

v Theoptimizerhasdeterminedthatthequery willbeI/Obound. v

Thereisanampleamountof mainstorageavailableto holdthedatathatiscollectedfromeveryinput stream.

DB2UniversalDatabaseforiSeriesdataspreading

Asmentionedpreviously, DB2UniversalDatabaseforiSeriesautomatically spreadsthedataacrossthe diskdeviceswithout userintervention,allowingthedatabasemanagertoprefetchtable datainparallel. Thedatabasemanagerusestaskstoretrievedatafromdifferentdiskdevices.Usuallytherequestisfor anentire extent(contiguoussetofdata).This improvesperformancebecausethediskdevicecan use

(23)

smoothsequentialaccesstothedata.Becauseof thisoptimization,parallelprefetchcanpreloaddatato activememoryfasterthantheSETOBJACCCLcommand.

EventhoughDB2UniversalDatabasefor iSeriesspreadsdata acrossdiskdeviceswithinanASP, sometimestheallocationof thedataspaceextents maynotbespreadevenly.Thisoccurswhenthereis unevenallocationof spaceonthedevicesoranew deviceisaddedto theASP.Theallocation ofthe dataspacecanberespreadbysaving, deleting,andrestoringthetable.

Howthe queryoptimizerselectsqueriesthatusethismethod

Thequeryoptimizerselectsthecandidatequerieswhichcantake advantageof thistypeof

implementation.TheoptimizerselectsthecandidatesbyestimatingtheCPUtimerequiredtoprocess the queryandcomparingtheestimateto theamountof timerequiredfor inputprocessing.When the

estimatedinputprocessingtime exceedstheCPUtime,thequeryoptimizerindicatesthatthequerymay beimplementedwithparallelI/O.

IfDB2UDBSymmetricMultiprocessingisinstalled,thenthequeryoptimizerusuallypreferstheDB2UDB SymmetricMultiprocessingparallel methods.

Processingrequirements

Paralleltableprefetchrequiresthatinput andoutputparallelprocessing mustbeenabledbythesystem valueQQRYDEGREE,bythequery optionfile, orbytheDEGREEparameterontheChange Query Attributes(CHGQRYA)command.See“Controlparallelprocessingfor queries”onpage98for information onhow tocontrolparallelprocessing.Becausequeriesbeingprocessedwithparallel tableprefetch aggressivelyusemainstorageanddiskI/Oresources,thenumberof queriesthatuseparalleltable prefetchshouldbelimitedandcontrolled.Parallelprefetchusesmultiplediskarms, butitmakeslittleuse ofmultipleCPUsforanygivenquery. ParallelprefetchI/OwilluseI/Oresourcesintensely.Allowinga parallelprefetchqueryonaserverwithanovercommittedI/Osubsystemmayintensifythe

over-commitmentproblem.

Youshouldrunthejobinasharedstoragepoolwiththe*CALCpagingoptionbecausethiscausesmore efficientuseof activememory.DB2UniversalDatabasefor iSeriesusestheautomatedsystemtunerto determinehow muchmemorythis processisallowedtouse.At run-time,theLicensedInternalCodewill allowparalleltableprefetch tobeusedonly ifthememorystatisticsindicatethatitwillnotovercommitthe memoryresources.Formoreinformationonthepagingoption,see theAutomaticSystemTuningsection oftheWorkManagementtopic.

Paralleltableprefetchrequiresthatenoughmemorybeavailabletocachethedatathatisbeingretrieved bythemultipleinputstreams. Forlargetables,thetypical extentsizeis1MB.This meansthat2MBof memorymustbeavailabletousetwo inputstreamsconcurrently.Increasingtheamountofavailable memoryinthepoolallowsmoreinput streamstobeused.If plentyofmemoryisavailable,theentire dataspaceforthetablemay beloadedintoactivememorywhenthequeryisopened.

ThemessagescreatedbythePRTSQLINFcommandtodescribeaqueryinanSQLprogramwhichis usingtheparallel tableprefetchaccessmethodwould appearasfollows:

SQL4023 Parallel dataspace prefetch used.

Parallel

table

scan

method

DB2UniversalDatabasefor iSeriescan usethisparallelaccessmethodtoshortentheprocessingtime thatisrequiredfor long-runningtablescanquerieswhentheDB2UDBSymmetricMultiprocessingfeature isinstalled.Theparalleltable scanmethodreducestheI/Oprocessing timeliketheparalleltable prefetch accessmethod.In addition,if runningona serverthathasmorethan oneprocessor, thismethodcan

(24)

reducetheelapsed timeofa querybysplittingthetable scanprocessingintotasksthatcanberunonthe multipleprocessors simultaneously.Allselectionandcolumn processingisperformedinthetask.The application’sjobschedulestheworkrequeststo thetasksandmergestheresultsintotheresultbufferthat isreturnedto theapplication.

Wheretheparalleltablescanaccessmethodis mosteffective

Thismethodismosteffective whenthefollowingaretrue: v Thedataisspreadacrossmultiplediskdevices. v

Theserverhasmultipleprocessorsthatareavailable.

v Thereisanampleamountof mainstorageavailableto holdthedatabuffersandresultbuffers. v Whenused forlargetablesinanOLAPor batchenvironment.

Asmentionedpreviously, DB2UniversalDatabaseforiSeriesautomatically spreadsthedataacrossthe diskdeviceswithout userintervention,allowingthedatabasemanagertoprefetchtable datainparallel. Thisallowseachtaskto concentrateonitsshareof thestripeddatastoredaway.Thiswaythereisno contentiononany ofthetaskstogainaccessto thedataandperformtheirportionofthequery.

Thequeryoptimizerselectsthecandidatequeriesthatcantakeadvantageof thistypeof implementation. TheoptimizerselectsthecandidatesbyestimatingtheCPUtime requiredto processthequery and comparingtheestimatetotheamountof timerequiredforinput processing.Theoptimizerreduces its estimatedelapsedtimefor tablescanbasedonthenumberoftasksit calculatesshouldbeused.It calculatesthenumberoftasksbasedonthenumberof processorsintheserver,theamountofmemory availableinthejob’spool,andthecurrentvalueoftheDEGREEqueryattribute. Iftheparalleltablescan isthefastestaccessmethod,it isthenchosen.

Processingrequirements

ParalleltablescanrequiresthatSMPparallelprocessingmust beenabled eitherbythesystemvalue QQRYDEGREE,thequeryoptionfile,orbytheDEGREEparameterontheChangeQueryAttributes (CHGQRYA)command.See“Controlparallelprocessingfor queries”onpage 98for informationonhow to controlparallelprocessing.

Paralleltablescancannotbeused forqueriesthatrequireanyofthefollowing: v Specificationof the*ALLcommitment controllevel.

v Nested loopjoinimplementation.See“Nested loopjoinimplementation” onpage 35.

v Backwardscrolling.Forexample,paralleltable scancannotnormallybeused forqueriesdefinedbythe OpenQueryFile(OPNQRYF)command,whichspecifyALWCPYDTA(*YES)orALWCPYDTA(*NO), becausetheapplicationmightattempttopositiontothelastrowandretrievepreviousrows.

SQL-definedqueriesthatarenotdefinedasscrollablecanusethismethod.Paralleltable scancanbe used duringthecreationof atemporaryresult,suchasasortorhashoperation,nomatterwhat interface wasused todefinethequery.OPNQRYFcanbedefinedasnotscrollablebyspecifyingthe *OPTIMIZEparametervaluefortheALWCPYDTAparameter,whichenablestheusageofmost ofthe parallelaccessmethods.

v Restorationofthecursorposition.For instance,aqueryrequiring thatthecursorpositionberestoredas theresultoftheSQLROLLBACKHOLDstatementor theROLLBACKCLcommand.SQLapplications usingacommitment controllevelother than*NONEshouldspecify*ALLREADasthevaluefor precompilerparameterALWBLKto allowthismethodto beused.

(25)

Youshouldrunthejobinasharedstoragepoolwiththe*CALCpagingoption,asthiswillcausemore efficientuseof activememory.Formoreinformationonthepagingoptionsee theAutomaticSystem Tuningsectionof theWorkManagement topicintheiSeriesInformationCenter.

Paralleltablescanrequiresactivememorytobufferthedatathatisbeingretrieved,andtoseparate result buffersfor eachtask.Atypicaltotal amountofmemorythatisneededforeach taskisabout2 megabytes. Forexample,about8 megabytesofmemorymust beavailableinordertouse 4paralleltable scantasks concurrently.Increasingtheamountofavailable memoryinthepoolallowsmoreinputstreams tobeused. Queriesthataccesstableswithlargevarying lengthcharacter columns,orqueriesthatgenerateresult valuesthatarelargerthantheactualrowlengthof thetablemightrequiremorememoryfor eachtask. Theperformanceofparalleltable scancanbeseverelylimitedifnumerousrowlockingconflictsordata mappingerrorsoccur.

Index

scan-key

selection

access

method

Thisaccessmethodrequires indexes.Theentireindexisread,andanyselectioncriteriathatreferences thekeycolumnsoftheindexareappliedagainst theindex.Theadvantageofthismethodisthatthe dataspaceisonlyaccessedto retrieverowsthatsatisfy theselectioncriteriaappliedagainst theindex.Any additionalselectionnotperformedthroughtheindexscan-keyselectionmethodisperformedatthe

dataspacelevel.

Theindexscan-keyselectionaccessmethodcanbevery expensiveifthesearchconditionappliesto a largenumberofrowsbecause:

v Thewholeindexisprocessed.

v For everykeyselectedfromtheindex,arandomI/Otothedataspaceoccurs.

Normally,theoptimizerwouldchoose tousetable scanprocessing whenthesearchconditionappliesto a largenumberofrows.Theoptimizeronly choosestheindexscan-keyselectionmethodiflessthan20%of thekeysareselectedorifanoperationforcestheuseof anindex.Operationsthatmightforcetheuseof anindexinclude:

v Ordering v

Grouping v Joining

Inthesecases,theoptimizermaychooseto createatemporary indexrather thanuseanexistingindex. Whentheoptimizercreatesa temporaryindex,itusesa64Kpagesizeforprimarydialsandan8K page sizefor secondarydials.AnindexcreatedusingtheSQLCREATEINDEXstatementuses64Kpagesize. ForindexesthatarecreatedusingtheCRTLFcommand,orforSQLindexescreatedbefore V4R5M0,the indexsizeisnormally16K.

Theoptimizeralso processesasmuchof theselection aspossiblewhilebuildingthetemporaryindex. Nearlyalltemporaryindexesbuiltbytheoptimizerareselect/omitorsparseindexes.Finally,theoptimizer canusemultipleparalleltaskswhencreating theindex.Thepagesizedifference,corresponding

performanceimprovementfromswappinginfewerpages,andtheabilityto useparalleltasksto createthe indexmaybeenoughto overcometheoverhead costof creatinganindex.Dataspaceselectionisusedfor buildingof temporaryindexes.

Ifindexscan-keyselectionaccessmethodisusedbecausethequeryspecifiedordering(anindexwas required)thequery performancemightbeimprovedbyusingthefollowingparameterstoallowthe orderingtobedonewiththequery sort.

v For SQL,thefollowingcombinationsofprecompiler parameters:

(26)

– ALWCPYDTA(*OPTIMIZE)andCOMMIT(*NONE) v For OPNQRYF,thefollowingparameters:

– *ALWCPYDTA(*OPTIMIZE)andCOMMIT(*NO)

– ALWCPYDTA(*OPTIMIZE)andCOMMIT(*YES) andthecommitmentcontrollevelisstartedwitha commitlevelof*NONE, *CHG,or*CS

Whenaquery specifiesaselect/omitindexandtheoptimizerdecidesto builda temporaryindex,allofthe selectionfromtheselect/omitindexisputinto thetemporary indexafteranyapplicable selectionfromthe query.

Parallel

index

scan-key

selection

access

method

(available

only

when

the

DB2

UDB

Symmetric

Multiprocessing

feature

is

installed)

Fortheparallelindexscan-keyselectionaccessmethod, thepossiblekeyvalues arelogically partitioned. Eachpartitionisprocessedbyaseparatetask justasintheindexscan-keyselectionaccessmethod.The numberofpartitionsprocessedconcurrentlyisdeterminedbythequery optimizer.Becausethekeysare notprocessedinorder, thismethodcannotbeused bytheoptimizeriftheindexisbeingused for

ordering.Keypartitionsthatcontain alargerportionoftheexistingkeysfromtheindexarefurthersplitas processingof otherpartitionscomplete.

Wheretheparallelindexscan-keyselectionaccessmethodis mosteffective

Thefollowingexampleillustrates aquerywheretheoptimizercouldchoosetheindexscan-keyselection method:

CREATE INDEX X1 ON EMPLOYEE(LASTNAME,WORKDEPT)

DECLARE BROWSE2 CURSOR FOR SELECT * FROM EMPLOYEE WHERE WORKDEPT = ’E01’ OPTIMIZE FOR 99999 ROWS OPNQRYFexample: OPNQRYF FILE((EMPLOYEE))

QRYSLT(’WORKDEPT *EQ ’’E01’’’)

Iftheoptimizerchoosesto runthis queryinparallelwithadegreeof four,thefollowingmightbethelogical keypartitionsthatgetprocessedconcurrently:

LASTNAME values LASTNAME values leading character leading character partition start partition end

’A’ ’F’ ’G’ ’L’ ’M’ ’S’ ’T’ ’Z’

Iftherewere fewerkeysinthefirstandsecond partition,processingof thosekey valueswouldcomplete soonerthanthethird andfourth partitions.Afterthefirsttwo partitionsarefinished,theremainingkey valuesinthelast twomightbefurthersplit.Thefollowingshowsthefourpartitionsthatmightbe processedafterthefirstandsecondpartitionarefinishedandthesplitshaveoccurred:

LASTNAME values LASTNAME values leading character leading character partition start partition end

’O’ ’P’ ’Q’ ’S’ ’V’ ’W’ ’X’ ’Z’ Processingrequirements

(27)

Parallelindexscan-keyselectioncannotbeusedfor queriesthatrequireanyof thefollowing: v Specificationof the*ALLcommitment controllevel.

v Nested loopjoinimplementation.See“Nested loopjoinimplementation” onpage 35.

v Backwardscrolling.Forexample,parallelindexscan-keyselectioncannotbeusedforqueriesdefined bytheOpenQueryFile(OPNQRYF)commandwhichspecifyALWCPYDTA(*YES)or

ALWCPYDTA(*NO), becausetheapplicationmightattempttopositiontothelastrow andretrieve previousrows.OPNQRYFcanbedefinedasnotscrollablebyspecifyingthe*OPTIMIZEparameter valuefortheALWCPYDTAparameter,whichenables theusageof mostof theparallel accessmethods. SQLdefinedqueriesthatarenotdefinedasscrollablecanusethis method.Parallelindexscan-key selectioncanbeusedduringthecreationofa temporaryresult,suchasa sortor hashoperation,no matter whatinterface wasused todefinethequery.

v Restorationofthecursorposition(for instance,a queryrequiringthatthecursorpositionberestoredas theresultoftheSQLROLLBACKHOLDstatementor theROLLBACKCLcommand).SQLapplications usingacommitment controllevelotherthan *NONEshouldspecify*ALLREADasthevaluefor

precompilerparameterALWBLKto allowthismethodto beused. v Updateor deletecapability.

Youshouldrunthejobinasharedpoolwith*CALCpagingoptionasthiswillcausemoreefficientuse of activememory.FormoreinformationonthepagingoptionseetheAutomaticSystemTuningsectionof the WorkManagement topicintheiSeriesInformationCenter.

Parallelindexscan-keyselectionrequiresthatSMPparallel processingbeenabledeitherbythesystem valueQQRYDEGREE,thequery optionsfile,or bytheDEGREEparameter ontheChangeQuery

Attributes(CHGQRYA)command.See“Controlparallelprocessingfor queries”onpage98for information onhow tocontrolparallelprocessing.

Index

scan-key

positioning

access

method

Thisaccessmethodisvery similartotheindexscan-keyselectionaccessmethod.Theybothrequirea keyedsequenceindex.In theindexscan-keyselectionaccessmethod,processingstartsatthebeginning oftheindexandcontinuestotheend;allkeysarepagedin.Intheindexscan-keypositioningaccess method,selectionisagainsttheindexdirectly ona rangeofkeysthatmatchsomeoralloftheselection criteria.Onlythosekeysfromthisrangearepagedinandanyremainingindexselectionisperformedby theindexscan-keyselectionmethod.Anyselectionnotperformedthroughindexscan-keypositioningor indexscan-keyselectionisperformedat thedataspacelevel.Becauseindexscan-keypositioningonly retrievesasubset ofthekeysintheindex,theperformanceoftheindexscan-keypositioningmethodis betterthantheperformanceoftheindexscan-keyselectionmethod.

Wherethe indexscan-keypositioningaccessmethodismostefficient

Theindexscan-keypositioningmethodismost efficientwhenasmallpercentageofrowsaretobe selected(lessthanapproximately20%).If morethanapproximately20%oftherowsareto beselected, theoptimizergenerallychoosesto:

v Usetablescanprocessing(if indexisnotrequired) v

Useindexscan-keyselection(if anindexisrequired) v Usequerysortroutine(ifconditions apply)

Forqueriesthatdonotrequireanindex(noordering,grouping, orjoinoperations),theoptimizertriesto findanexistingindextouse forindexscan-keypositioning.If noexistingindexcanbefound,theoptimizer stopstryingto usekeyedaccessto thedata becauseit isfaster tousetable scanprocessing thanitisto buildanindexandthenperformindexscan-keypositioning.

(28)

Thefollowingexampleillustrates aquerywheretheoptimizercouldchoosetheindexscan-keypositioning method:

CREATE INDEX X1 ON EMPLOYEE(WORKDEPT)

DECLARE BROWSE2 CURSOR FOR SELECT * FROM EMPLOYEE WHERE WORKDEPT = ’E01’ OPTIMIZE FOR 99999 ROWS OPNQRYFexample:

OPNQRYF FILE((EMPLOYEE))

QRYSLT(’WORKDEPT *EQ ’’E01’’’)

Inthis example,thedatabasesupportusesX1to positionto thefirstindexentrywiththeWORKDEPT

valueequalto ’E01’.Foreach keyequalto’E01’, itrandomlyaccessesthedataspace2andselectsthe row.Thequeryends whentheindexscan-keyselectionmovesbeyondthekeyvalueofE01.

Notethatfor thisexampleallindexentriesprocessedandrowsretrievedmeettheselectioncriteria.If additionalselectionisaddedthatcannotbeperformedthroughindexscan-keypositioning(suchas selectioncolumnswhichdonotmatchthefirstkeycolumnsofanindexovermultiplecolumns)the optimizerusesindexscan-keyselectiontoperformasmuchadditionalselectionaspossible.Any remainingselection isperformed atthedataspacelevel.

ThemessagescreatedbythePRTSQLINFCLcommandto describethisquery inanSQLprogramwould appearasfollows:

SQL4008 Index X1 used for table 1.

SQL4011 Key row positioning used on table 1.

Theindexscan-keypositioningaccessmethodhasadditionalprocessing capabilities.Onesuchcapability istoperformrangeselectionacrossseveralvalues. Forexample:

CREATE INDEX X1 EMPLOYEE(WORKDEPT)

DECLARE BROWSE2 CURSOR FOR

SELECT * FROM EMPLOYEE

WHERE WORKDEPT BETWEEN ’E01’ AND ’E11’ OPTIMIZE FOR 99999 ROWS

OPNQRYFexample: OPNQRYF FILE((EMPLOYEE))

QRYSLT(’WORKDEPT *EQ %RANGE(’’E01’’ ’’E11’’)’)

Inthepreviousexample,thedatabasesupportpositionstothefirstindexentryequaltovalue’E01’and rowsareprocesseduntilthelast indexentry for’E11’isprocessed.

ThemessagescreatedbyPRTSQLINFCLcommandto describethis queryinanSQLprogramwould appearasfollows:

Multi-rangeindex scan-keypositioning

(29)

Afurtherextensionof thisaccessmethod, calledmulti-rangeindexscan-keypositioning,isavailable.It allowsfor theselectionofrowsformultiplerangesof valuesfor thefirstkey columnsof anindexover multiplecolumns.

CREATE INDEX X1 ON EMPLOYEE(WORKDEPT)

DECLARE BROWSE2 CURSOR FOR SELECT * FROM EMPLOYEE

WHERE WORKDEPT BETWEEN ’E01’ AND ’E11’ OR WORKDEPT BETWEEN ’A00’ AND ’B01’ OPTIMIZE FOR 99999 ROWS

QRYSLT(’WORKDEPT *EQ %RANGE(’’E01’’ ’’E11’’) *OR WORKDEPT *EQ %RANGE(’’A00’’ ’’B01’’)’)

Inthepreviousexample,thepositioningandprocessingtechniqueisusedtwice,once foreach rangeof values.

ThemessagescreatedbyPRTSQLINFCLcommandto describethis queryinanSQLprogramwould appearasfollows:

Allof theindexscan-keypositioningexampleshavesofaronly usedonekey, theleft-most key,of the index.Indexscan-keypositioningalsohandles morethanonekey(althoughthekeysmustbecontiguous totheleft-mostkey).

CREATE INDEX X2

ON EMPLOYEE(WORKDEPT,LASTNAME,FIRSTNME)

DECLARE BROWSE2 CURSOR FOR SELECT * FROM EMPLOYEE WHERE WORKDEPT = ’D11’ AND FIRSTNME = ’DAVID’ OPTIMIZE FOR 99999 ROWS OPNQRYFexample:

OPNQRYF FILE((EMPLOYEE)) QRYSLT(’WORKDEPT *EQ ’’D11’’ *AND FIRSTNME *EQ ’’DAVID’’’)

Becausethetwoselectionkeys(WORKDEPTandFIRSTNME)arenotcontiguous,thereisnomultiple keypositionsupportfor thisexample.Therefore,onlytheWORKDEPT=’D11’partoftheselectioncanbe appliedagainsttheindex(singlekeyindexscan-keypositioning).While thismaybeacceptable,itmeans thattheprocessing ofrowsstartswiththefirstkeyof ’D11’andthenusesindexscan-keyselectionto processtheFIRSTNME=’DAVID’against all9entrieswithWORKDEPT keyvalue=’D11’.

Bycreatingthefollowingindex,X3, theaboveexamplequerywouldrunusingmultiplekeysto dothe indexscan-keypositioning.

CREATE INDEX X3

ON EMPLOYEE(WORKDEPT, FIRSTNME, LASTNAME)

Multiplekey indexscan-keypositioningsupportcanapplyboth piecesofselectionasindexscan-key positioning.Thisimprovesperformanceconsiderably.Astartingvalueisbuilt byconcatenating thetwo selectionvaluesinto’D11DAVID’andselectionispositionedtotheindexentrywhoseleft-most twokeys havethatvalue.

(30)

ThemessagescreatedbythePRTSQLINFCLcommandwhenusedto describethis queryinanSQL programwouldlooklikethis:

Thisnextexampleshowsa moreinterestinguseofmultipleindexscan-keypositioning.

CREATE INDEX X3 ON EMPLOYEE(WORKDEPT,FIRSTNME)

DECLARE BROWSE2 CURSOR FOR SELECT * FROM EMPLOYEE WHERE WORKDEPT = ’D11’

AND FIRSTNME IN (’DAVID’,’BRUCE’,’WILLIAM’) OPTIMIZE FOR 99999 ROWS

QRYSLT(’WORKDEPT *EQ ’’D11’’

*AND FIRSTNME *EQ %VALUES(’’DAVID’’ ’’BRUCE’’ ’’WILLIAM’’)’)

ThequeryoptimizeranalyzestheWHEREclauseandrewrites theclauseintoanequivalentform: DECLARE BROWSE2 CURSOR FOR

WHERE (WORKDEPT = ’D11’ AND FIRSTNME = ’DAVID’) OR (WORKDEPT = ’D11’ AND FIRSTNME = ’BRUCE’) OR (WORKDEPT = ’D11’ AND FIRSTNME = ’WILLIAM’) OPTIMIZE FOR 99999 ROWS

QRYSLT(’(WORKDEPT *EQ ’’D11’’ *AND FIRSTNME *EQ ’’DAVID’’)

*OR (WORKDEPT *EQ ’’D11’’ *AND FIRSTNME *EQ ’’BRUCE’’) *OR (WORKDEPT *EQ ’’D11’’ *AND FIRSTNME *EQ ’’WILLIAM’’)’)

Intherewrittenformof thequerythereareactually3 separaterangesofkeyvalues fortheconcatenated valuesof WORKDEPTandFIRSTNME:

Index X3 Start value Index X3 Stop value ’D11DAVID’ ’D11DAVID’ ’D11BRUCE’ ’D11BRUCE’ ’D11WILLIAM’ ’D11WILLIAM’

Indexscan-keypositioningisperformedovereachrange, significantlyreducingthenumberofkeys selectedtojust3.Alloftheselectioncanbeaccomplishedthroughindexscan-keypositioning. Thecomplexityof thisrangeanalysiscanbetakentoafurther degreeinthefollowingexample: DECLARE BROWSE2 CURSOR FOR

SELECT * FROM EMPLOYEE WHERE (WORKDEPT = ’D11’

AND FIRSTNME IN (’DAVID’,’BRUCE’,’WILLIAM’)) OR (WORKDEPT = ’E11’

AND FIRSTNME IN (’PHILIP’,’MAUDE’)) OR (FIRSTNME BETWEEN ’CHRISTINE’ AND ’DELORES’ AND WORKDEPT IN (’A00’,’C01’))

QRYSLT(’(WORKDEPT *EQ ’’D11’’

(31)

*OR (WORKDEPT *EQ ’’E11’’

*AND FIRSTNME *EQ %VALUES(’’PHILIP’’ ’’MAUDE’’)) *OR (FIRSTNME *EQ %RANGE(’’CHRISTINE’’ ’’DELORES’’) *AND WORKDEPT *EQ %VALUES(’’A00’’ ’’C01’’))’)

ThequeryoptimizeranalyzestheWHEREclauseandrewrites theclauseintoanequivalent form: DECLARE BROWSE2 CURSOR FOR

WHERE (WORKDEPT = ’D11’ AND FIRSTNME = ’DAVID’) OR (WORKDEPT = ’D11’ AND FIRSTNME = ’BRUCE’) OR (WORKDEPT = ’D11’ AND FIRSTNME = ’WILLIAM’) OR (WORKDEPT = ’E11’ AND FIRSTNME = ’PHILIP’) OR (WORKDEPT = ’E11’ AND FIRSTNME = ’MAUDE’) OR (WORKDEPT = ’A00’ AND FIRSTNME BETWEEN

’CHRISTINE’ AND ’DELORES’) OR (WORKDEPT = ’C01’ AND FIRSTNME BETWEEN

’CHRISTINE’ AND ’DELORES’) OPTIMIZE FOR 99999 ROWS

QRYSLT(’(WORKDEPT *EQ ’’D11’’ *AND FIRSTNME *EQ ’’DAVID’’)

*OR (WORKDEPT *EQ ’’D11’’ *AND FIRSTNME *EQ ’’BRUCE’’) *OR (WORKDEPT *EQ ’’D11’’ *AND FIRSTNME *EQ

’’WILLIAM’’)

*OR (WORKDEPT *EQ ’’E11’’ *AND FIRSTNME *EQ ’’PHILIP’’) *OR (WORKDEPT *EQ ’’E11’’ *AND FIRSTNME *EQ ’’MAUDE’’) *OR (WORKDEPT *EQ ’’A00’’ *AND

FIRSTNME *EQ %RANGE(’’CHRISTINE’’ ’’DELORES’’)) *OR (WORKDEPT *EQ ’’C01’’ *AND

FIRSTNME *EQ %RANGE(’’CHRISTINE’’ ’’DELORES’’))’)

Inthequerythere areactually7separaterangesof keyvaluesfor theconcatenatedvaluesof WORKDEPTandFIRSTNME:

Index X3 Start value Index X3 Stop value ’D11DAVID’ ’D11DAVID’ ’D11BRUCE’ ’D11BRUCE’ ’D11WILLIAM’ ’D11WILLIAM’ ’E11MAUDE’ ’E11MAUDE’ ’E11PHILIP’ ’E11PHILIP’ ’A00CHRISTINE’ ’A00DELORES’ ’C01CHRISTINE’ ’C01DELORES’

Indexscan-keypositioningisperformedovereachrange. Onlythoserowswhosekeyvaluesfallwithin oneoftherangesarereturned.Alloftheselectioncanbeaccomplished throughindexscan-key positioning.Thissignificantly improvestheperformanceof thisquery.

Parallel

index

scan-key

positioning

access

method

(available

only

when

the

DB2

UDB

Symmetric

Multiprocessing

feature

is

installed)

Usingtheparallelindexscan-keypositioningaccessmethod,theexistingkey rangesareprocessedby separatetasksconcurrentlyinseparatedatabasetasks.Thenumberof concurrenttasksiscontrolledby theoptimizer.Thequery willstartprocessing thekey rangesofthequeryuptothedegreeofparallelism beingused.As processingofthoserangescompletes,thenextones onthelistarestarted.Asprocessing fora rangecompletesandtherearenomorerangesinthelist toprocess,rangesthatstillhavekeysleft toprocess aresplit,justasintheparallel indexscan-keyselectionmethod.Thedatabasemanager attemptsto keepallofthetasksthatarebeingused busy,each processingaseparate keyrange.

Whetherusingthesinglevalue,rangeof values,ormulti-rangeindexscan-keypositioning,therangescan befurther partitionedandprocessedsimultaneously.Because thekeysarenotprocessedinorder, this methodcannotbeused bytheoptimizeriftheindexisbeingused forordering.

ERserver. iseries. DB2 Universal Database for iseries - Database Performance and Query Optimization

iSeries

DB2

Universal

Database

for

iSeries

-Database

Performance

and

Query

Optimization

iSeries

DB2

Universal

Database

for

iSeries

-Database

Performance

and

Query

Optimization

Contents

About

DB2

UDB

for

iSeries

Database

Performance

and

Query

Optimization

Who

should

read

the

Database

Performance

and

Query

Optimization

book

Assumptions

relating

to

SQL

statement

examples

How

to

interpret

syntax

diagrams

What’s

new

for

V5R2

Code

disclaimer

information

Chapter

1.

Database

performance

and

query

optimization:

Overview

Creating

queries

Chapter

2.

Data

access

on

DB2

UDB

for