Change thedata accessmethodchosenbytheserver.See“Dataaccessmethods:Summary”onpage 5.
v Createthecorrectindexesandusethemeffectively. SeeChapter5,“Using indexesto speedaccessto largetables,” onpage105.
Queryoptimizationisaniterativeprocess.Dothefollowingasneededto optimizeyourqueries.
Gatherstatisticsaboutyourqueries
Therearevarious waystogather statisticsaboutyour queries.Thefollowingisa samplingof theways thatstatisticscanbegathered:
v “Verify theperformanceof SQLapplications”onpage60 v
“Examinequery optimizerdebugmessagesinthejoblog”onpage60
v “GatherinformationaboutembeddedSQLstatements withthePRTSQLINFcommand”onpage68 v “Gatherstatisticsabout yourquerieswiththedatabasemonitor”onpage 69
v “Gatherstatisticsabout yourquerieswithmemory-resident databasemonitorAPIs”onpage78 v “Viewtheeffectivenessof yourquerieswithVisual Explain”onpage82
v “MonitoringyourdatabaseperformanceusingSQLPerformancemonitors iniSeriesNavigator”onpage 81
Controltheprocessingofyour queries:
v “Change theattributesofyourqueries withtheChangeQueryAttributes(CHGQRYA) command”on page84
v
“Controlqueriesdynamically withthequeryoptions fileQAQQINI”onpage 84
v “Controllong-running querieswiththeDB2UDBforiSeriesPredictiveQueryGovernor” onpage 94 v “Controlparallel processingforqueries”onpage98
Comparingthedifferenttools:
Youmaywanttocheckoutthe“Queryoptimizationtools:Comparisontable”onpage102to learn:
v Whatinformationeach toolcanyieldaboutyourquery v Whenintheprocessaspecifictoolcananalyzeyourquery v
Thetaskseachtoolcan performto improveyourquery Foradditionaltipsandtechniques:
Ifyouareexperiencedwithqueryoptimization,youmay wantto referto alist of“Generalquery optimizationtips”onpage 34.
Also,thefollowingtopicsprovideprogrammingtips andtechniquesforoptimizing yourapplicationsfor queryperformance:
v Chapter6,“Applicationdesigntips fordatabaseperformance,”onpage 121 v Chapter7,“Programmingtechniquesfor databaseperformance,”onpage127 v Chapter8,“GeneralDB2UDBforiSeriesperformanceconsiderations,”onpage 133
Verify the performance of SQL applications
YoucanverifytheperformanceofanSQLapplicationbyusingthefollowingcommands:
DSPJOB
YoucanusetheDisplayJob(DSPJOB)commandwiththeOPTION(*OPNF)parametertoshow theindexesandtablesbeingused byanapplicationthatisrunninginajob.
Youcanalsouse DSPJOBwiththeOPTION(*JOBLCK)parameterto analyzeobjectandrowlock contention.Itdisplaystheobjectsandrowsthatarelockedandthename ofthejobholdingthe lock.
SpecifytheOPTION(*CMTCTL)parameter ontheDSPJOBcommandtoshowtheisolationlevel thattheprogramisrunning,thenumberof rowsbeinglockedduringatransaction, andthe pendingDDLfunctions.Theisolationleveldisplayed isthedefaultisolationlevel.Theactual isolationlevel,used foranySQLprogram,isspecifiedontheCOMMITparameter ofthe CRTSQLxxxcommand.
PRTSQLINF
ThePrintSQLInformation(PRTSQLINF)commandletsyouprintinformationabout theembedded SQLstatementsinaprogram,SQLpackage,orservice program.Theinformationincludesthe SQLstatements,theaccessplansusedduringtherunningof thestatement,anda listofthe commandparametersusedto precompilethesourcememberfortheobject.Formore information onprintinginformationabout SQLStatements,seethePRTSQLINFsectionin“Gatherinformation aboutembeddedSQLstatementswiththePRTSQLINFcommand”onpage68.
STRDBMON
YoucanusetheStartDatabaseMonitor(STRDBMON)commandtocaptureto afileinformation abouteverySQLstatementthatruns.See“Gatherstatistics aboutyourquerieswiththedatabase monitor”onpage69for moreinformation.
CHGQRYA
YoucanusetheChangeQueryAttribute(CHGQRYA)commandtochangethequery attributesfor thequery optimizer.Amongtheattributesthatcanbechangedbythiscommandarethepredictive querygovernor,parallelism,andthequeryoptions.
STRDBG
YoucanusetheStartDebug(STRDBG)commandtoputajobintodebugmodeand,optionally, addasmanyas20programs and20classfilesand20service programstodebugmode.Italso specifiescertainattributesof thedebuggingsession.For example,it canspecifywhetherdatabase filesinproductionlibrariescanbeupdatedwhileindebugmode.
Examine query optimizer debug messages in the job log
Queryoptimizerdebugmessagesissueinformationalmessagestothejoblogabouttheimplementation of aquery.Thesemessagesexplainwhathappenedduringthequeryoptimizationprocess.Forexample,you canlearn:
v Whyanindexwasorwasnotused v Whyatemporary resultwasrequired v
Whetherjoinsandblockingareused
v Whattypeof indexwasadvisedbytheoptimizer v Status ofthejob’squeries
v Indexes used v Status ofthecursor
Theoptimizerautomatically logsmessagesfor allqueriesitoptimizes,including SQL,calllevelinterface, ODBC,OPNQRYF,andSQLQueryManager.
Viewingdebugmessages:
Toviewthemessages, putyourjobinto debugmode usingoneof thefollowingmethods:
v Usethefollowingcommand:
STRDBG PGM(Library/program) UPDPROD(*YES)
STRDBG placesinthejobloginformationabout allSQLstatementsthatrun.
v
Set theQRYOPTLIB parameterontheChange QueryAttributes(CHGQRYA)commandtoa userlibrary wheretheQAQQINIfileexists.Set theparameterontheQAQQINIfileto MESSAGES_DEBUG,andset thevalueto*YES.Thisoptionplacesquery optimizationinformationinthejoblog.
PressingF10fromthecommandEntrypaneldisplaysthemessagetext.Toseethesecond-leveltext, pressF1 (Help).Thesecond-leveltextsometimes offershintsfor improvingqueryperformance.
See“Queryoptimizationperformanceinformationmessages”and“Queryoptimizationperformance
informationmessagesandopendatapaths”onpage66for thespecificmeaningsof thedebugmessages.
SeeViewingtheJobLogintheSQLProgrammingConceptsbook forinformationonviewingthejoblog withiSeriesNavigator.
Query optimization performance information messages
Youcanevaluatethestructure andperformanceofthegivenSQLstatementsinaprogramusing
informationalmessages putinthejoblogbythedatabasemanager.ThemessagesareissuedforanSQL programor interactiveSQLwhenrunninginthedebugmode.Thedatabasemanagermaysendanyof the followingmessageswhenappropriate.Theampersandvariables(&1,&X)arereplacementvariablesthat containeitheranobjectnameor someothersubstitutionvaluewhenthemessageappearsinthejoblog.
Themessagesare:
v “CPI4321-Accesspathbuiltfor file&1.”onpage62
v “CPI4322-Accesspathbuiltfromkeyedfile&1” onpage63
v “CPI4323-TheOS/400queryaccessplanhasbeenrebuilt”onpage63 v
“CPI4324-Temporaryfilebuiltfor file&1”onpage63 v “CPI4325-Temporaryresultfilebuilt forquery” onpage 64 v
“CPI4326-File&1 processedinjoin position&11”onpage 64 v “CPI4327-File&13processedinjoinposition&10”onpage64 v “CPI4328-Accesspathoffile&4 wasused byquery”onpage64 v “CPI4329-Arrivalsequenceaccesswasusedfor file&1”onpage64 v “CPI432A-Queryoptimizertimedoutforfile &1”onpage 64
v “CPI432B-Subselectsprocessedasjoinquery” onpage 65
v “CPI432C-Allaccesspathswereconsideredfor file&1”onpage65 v “CPI432D-Additionalaccesspathreasoncodeswereused” onpage65 v “CPI432E-Selectioncolumnsmappedto differentattributes”onpage65 v
CPI432FAccess pathsuggestionfor file&1.
v CPI4330&6 tasksusedfor parallel&10scanoffile &1.
v CPI4331&6 tasksusedfor parallelindexcreatedoverfile &1.
v CPI4332&1 hostvariablesusedinquery.
v CPI4333Hashingalgorithmusedto processjoin.
v CPI4334QueryimplementedasreusableODP.
v CPI4335Optimizerdebugmessagesfor hashjoinstep &1follow:
v CPI4336Groupprocessinggenerated.
v CPI4337Temporaryhashtable builtfor hashjoinstep &1.
v “CPI4338-&1Accesspath(s)usedfor bitmapprocessing offile&2” onpage 65 v CPI4339Queryoptionsretrieved fromLibrary&1.
v CPI433AUnable toretrievequeryoptions file.
v CPI433CLibrary&1notfound.
v CPI4341Performing distributedquery.
v
CPI4342Performing distributedjoinforquery.
v CPI4345Temporarydistributedresultfile &4builtfor query.
v CPI4346Optimizerdebugmessagesfor queryjoinstep &1of&2 follow:
v CPI4347Querybeingprocessedinmultiplesteps.
v CPI4348TheODPassociatedwiththecursorwashardclosed.
v CPI4349Fast pastrefreshof thehostvariablevalues isnotpossible.
v CPI434A&1 Startingoptimizerdebugmessagefor query&2.
v CPI434B&1Endingdebugmessagefor query&2.
v CPI434CTheOS/400Queryaccessplan wasnotrebuilt.
v “SQL7910-AllSQLcursorsclosed”onpage 66 v “SQL7911-ODPreused”onpage66
v
“SQL7912-ODP created”onpage66 v “SQL7913-ODP deleted”onpage 67 v “SQL7914-ODP notdeleted”onpage67
v “SQL7915-Accessplanfor SQLstatementhasbeenbuilt”onpage67 v “SQL7916-Blockingusedfor query”onpage67
v “SQL7917-Accessplannotupdated”onpage67 v “SQL7918-Reusable ODPdeleted”onpage 67
v “SQL7919-DataconversionrequiredonFETCHorembeddedSELECT”onpage67 v “SQL7939-DataconversionrequiredonINSERTorUPDATE”onpage67
Thesemessages providefeedbackonhowa querywasrunand,insomecases,indicatethe improvementsthatcanbemade tohelp thequery runfaster.
Themessagescontainmessagehelpthatprovidesinformationabout thecauseforthemessage,object namereferences,andpossibleuserresponses.
Thetimeatwhich themessageissentdoes notnecessarilyindicatewhentheassociatedfunctionwas performed.Somemessagesaresentaltogetheratthestartof aqueryrun.
Thecausesanduserresponsesforthefollowingmessages areparaphrased.Theactualmessagehelpis morecompleteandshouldbeused whentryingtodeterminethemeaningandresponsesfor each message.
Thepossibleuser actionfor eachmessagearedescribedinthefollowingsections:
CPI4321 - Access path built for file &1.
Thismessageindicatesthatatemporaryindexwascreatedtoprocess thequery. Thenew indexis createdbyreadingallof therowsinthespecifiedtable.
Thetimerequiredtocreateanindexoneachrunofa querycanbesignificant.Considercreating alogical file(CRTLF)oranSQLindex(CREATEINDEXSQLstatement):
v Over thetable namedinthemessagehelp.
v Withkeycolumnsnamedinthemessagehelp.
v Withtheascendingordescendingsequencingspecified inthemessagehelp.
v Withthesortsequencetablespecifiedinthemessagehelp.
Considercreatingthelogicalfilewithselectoromitcriteriathateither matchor partiallymatchthequery’s predicatesinvolvingconstants.Thedatabasemanagerwillconsiderusingselector omitlogicalfileseven thoughtheyarenotexplicitlyspecifiedonthequery.
Forcertainqueries,theoptimizermaydecideto createanindexevenwhenanexistingonecanbeused.
Thismightoccurwhenaquery hasanorderingcolumn asa keycolumnfor anindex,andtheonlyrow selectionspecifiedusesa differentcolumn.Iftherowselectionresultsinroughly20%oftherowsormore tobereturned,thentheoptimizermay createanew indextogetfasterperformancewhenaccessingthe data.Thenewindexminimizestheamountofdatathatneedsto beread.
CPI4322 - Access path built from keyed file &1
Thismessageindicatesthatatemporaryindexwascreatedfromtheaccesspathofanexistingkeyed tableorindex.
Generally,thisactionshouldnottakeasignificantamountoftime orresourcebecauseonlyasubset of thedatainthetable needsto beread.Thisisnormallydonetoallow theoptimizerto useanexisting indexforselectionwhilecreatingonefor ordering,grouping,orjoin criteria.Sometimesevenfaster performancecanbeachievedbycreatinga logicalfileorSQLindexthatsatisfiestheindexrequirement statedinthemessagehelp.
Formoredetail,seethepreviousmessage,CPI4321.
CPI4323 - The OS/400 query access plan has been rebuilt
Thismessagecanbesent foravariety ofreasons.Thespecificreasonisprovided inthemessagehelp.
Mostof thetime, thismessageissentwhenthequeriedtable environmenthaschanged,makingthe currentaccessplanobsolete.Anexampleofthetable environmentchangingiswhenanindexrequiredby thequerynolongerexistsontheserver.
Anaccessplancontainstheinstructionsfor howaquery isto berunandliststheindexesfor runningthe query.If aneededindexisnolongeravailable,thequeryisagainoptimized,anda newaccessplanis created,replacingtheoldone.
Theprocessofagainoptimizingthequery andbuildinganew accessplan atruntimeisafunctionof DB2 UDBfor iSeries.Itallowsaqueryto berunasefficientlyaspossible,usingthemost currentstate ofthe databasewithout userintervention.
Theinfrequentappearanceof thismessageisnotacausefor action.Forexample,thismessagewillbe sentwhenanSQLpackageisrunthefirsttimeafterarestore,oranytime theoptimizerdetectsthata changehasoccurred(suchasanewindexwascreated),thatwarrantsanimplicitrebuild.However, excessiverebuilds shouldbeavoidedbecauseextraqueryprocessing willoccur.Excessive rebuildsmay indicatea possibleapplicationdesignproblemorinefficientdatabasemanagementpractices.See CPI434C.
CPI4324 - Temporary file built for file &1
Beforethequeryprocessingcouldbegin,thedatainthespecifiedtable hadtobecopiedintoatemporary physicaltableto simplifyrunningthequery.Themessagehelpcontainsthereasonwhythismessagewas sent.
Ifthespecifiedtableselectsfewrows,usuallylessthan1000rows,thentherowselectionpartof the query’simplementationshouldnottakea significantamountof resourceandtime.Howeverif thequery is takingmoretimeandresourcesthancanbeallowed,considerchangingthequery sothatatemporary tableisnotrequired.
Onewaytodothisisbybreakingthequeryintomultiplesteps.ConsiderusinganINSERTstatementwith asubselectto selectonlytherowsthatarerequiredintoa table,andthenusethattable’srowsforthe restofthequery.
CPI4325 - Temporary result file built for query
Atemporaryresulttablewascreatedtocontain theintermediate resultsofthequery.Theresultsare storedinaninternaltemporarytable(structure). Thisallowsfor moreflexibilitybytheoptimizerinhow to processandstoretheresults.Themessagehelpcontainsthereasonwhyatemporaryresulttableis required.
Insomecases,creatinga temporaryresulttableprovidesthefastestwaytorunaquery. Otherqueries thathavemanyrowsto becopied intothetemporary resulttable cantakeasignificantamountoftime.
However,if thequery istakingmoretimeandresourcesthancanbeallowed,considerchangingthequery sothatatemporaryresulttableisnotrequired.
CPI4326 - File &1 processed in join position &11
Thismessageprovidesthejoinpositionofthespecifiedtable whenanindexisusedtoaccessthetable’s data.Joinpositionpertainsto theorderinwhichthetablesarejoined.SeetheJoin optimizationsection fordetails.
CPI4327 - File &13 processed in join position &10
Thismessageprovidesthename ofthetableandthejoinpositionwhentableaccessscanmethodis usedto selectrowsfromthetable.
Seethepreviousmessage,CPI4326,forinformationonjoinpositionandjoinperformancetips.
CPI4328 - Access path of file &4 was used by query
Thismessagenamesanexistingindexthatwasused bythequery.
Thereasontheindexwasused isgiveninthemessagehelp.
CPI4329 - Arrival sequence access was used for file &1
Noindexwasused toaccessthedatainthespecifiedtable.Therowswerescannedsequentiallyinarrival sequence.
Ifanindexdoes notexist,youmaywanttocreateonewhosekeycolumnmatchesoneofthecolumnsin therowselection.Youshouldonlycreateanindexif therowselection(WHEREclause) selects20%or fewerrowsinthetable.Toforce theuseofanexistingindex,changetheORDERBYclauseof thequery tospecifythefirstkeycolumnof theindex,or ensurethatthequery isrunningunder afirstI/O
environment.
CPI432A - Query optimizer timed out for file &1
Theoptimizerstopsconsideringindexeswhenthetimespentoptimizing thequeryexceedsaninternal valuethatcorrespondstotheestimatedtimeto runthequery andthenumberofrowsinthequeried tables.Generally,themorerowsinthetables,thegreaterthenumberofindexesthatwillbeconsidered.
Whentheestimatedtimetorunthequeryisexceeded,theoptimizerdoes notconsideranymoreindexes andusesthecurrentbestmethodtoimplementthequery. Eitheranindexhasbeenfound togetthebest performance,oranindexwillhave tobecreated.Iftheactualtimeto executethequeryexceedsthe estimatedruntime thismayindicatetheoptimizerdidnotconsiderthebestindex.
|
Themessagehelp containsa listofindexesthatwereconsideredbefore theoptimizertimedout.By viewingthislistof indexes,youmay beable todetermineiftheoptimizertimedoutbeforethebestindex wasconsidered.
Toensurethatanindexisconsidered foroptimization,specifythelogical fileassociatedwiththeindexas thetabletobequeried.Theoptimizerwillconsidertheindexof thetable specifiedonthequery orSQL statementfirst. RememberthatSQLindexescannotbequeried.
Youmaywanttodeleteanyindexesthatarenolongerneeded.
CPI432B - Subselects processed as join query
Twoormore SQLsubselectswerecombinedbythequery optimizerandprocessedasa joinquery.
Generally,thismethodofprocessing isagoodperformingoption.
CPI432C - All access paths were considered for file &1
Theoptimizerconsideredallindexesbuiltoverthespecifiedtable.Sincetheoptimizerexaminedall indexesfor thetable,it determinedthecurrentbestaccesstothetable.
Themessagehelp containsa listoftheindexes.Witheachindexareasoncodeisadded.Thereason codeexplainswhytheindexwasorwasnotused.
CPI432D - Additional access path reason codes were used
MessageCPI432AorCPI432Cwasissuedimmediatelybefore thismessage.Becauseofmessagelength restrictions,someofthereasoncodesusedbymessagesCPI432AandCPI432Careexplained inthe messagehelp ofCPI432D.Usethemessagehelpfromthis messageto interprettheinformationreturned frommessageCPI432Aor CPI432C.
CPI432E - Selection columns mapped to different attributes
Thismessageindicatesthatthequeryoptimizerwasnotableto considertheusageofanindextoresolve oneormoreof theselectionspecificationsof thequery. Iftherewasanindexavailablewhich otherwise
Thismessageindicatesthatthequeryoptimizerwasnotableto considertheusageofanindextoresolve oneormoreof theselectionspecificationsof thequery. Iftherewasanindexavailablewhich otherwise