Table Of Content[vertical list of authors]
© Copyright ,.
[cover art/text goes here]
Copyright
Contents
i
Copyright
ApacheSoftwareFoundationTuningDerbyApacheDerby
2
Copyright
Copyright
Copyright1997,2006TheApacheSoftwareFoundationoritslicensors,asapplicable.
LicensedundertheApacheLicense,Version2.0(the"License");youmaynotusethisfile
exceptincompliancewiththeLicense.YoumayobtainacopyoftheLicenseat
http://www.apache.org/licenses/LICENSE-2.0
Unlessrequiredbyapplicablelaworagreedtoinwriting,softwaredistributedunderthe
Licenseisdistributedonan"ASIS"BASIS,WITHOUTWARRANTIESORCONDITIONS
OFANYKIND,eitherexpressorimplied.SeetheLicenseforthespecificlanguage
governingpermissionsandlimitationsundertheLicense.
3
Copyright
About this guide
ForgeneralinformationabouttheDerbydocumentation,suchasacompletelistofbooks,
conventions,andfurtherreading,seeGettingStartedwithDerby.
Purpose of this guide
Thisguide,TuningDerby,explainshowtosetpropertiestoconfigureandtunesystems,
databases,specifictablesandindexes,andqueries.Thisguidealsoprovides
performancetuningtipsandanin-depthstudyofqueryoptimizationandperformance
issues.
Audience
ThisbookisareferenceforDerbyusers,typicallyapplicationdevelopers.Derbyusers
whoarenotfamiliarwiththeSQLstandardortheJavaprogramminglanguagewill
benefitfromconsultingbooksonthosetopics.
Derbyuserswhowantahow-toapproachtoworkingwithDerbyoranintroductionto
DerbyconceptsshouldreadtheDerbyDeveloper'sGuide.Thisbookisforuserswho
wanttooptimizeandtunetheirapplication'sperformance.
How this guide is organized
Thisguideincludesthefollowingsections:
• WorkingwithDerbyproperties
Anoverviewofhowyousetproperties.
• Performancetipsandtricks
QuicktipsonhowtoimprovetheperformanceofDerbyapplications.
• Tuningdatabasesandapplications
Amorein-depthdiscussionofhowtoimprovetheperformanceofDerby
applications.
• DMLstatementsandperformance
Anin-depthstudyofhowDerbyexecutesqueries,howtheoptimizerworks,and
howtotunequeryexecution.
• Selectivityandcardinalitystatistics
• Internallanguagetransformations
ReferenceonhowDerbyinternallytransformssomeSQLstatementsfor
performancereasons.Notofinteresttothegeneraluser.
4
Copyright
Working with Derby properties
Properties overview
Derbyletsyouconfigurebehaviororattributesofasystem,aspecificdatabase,ora
specificconglomerate(atableorindex)throughtheuseofproperties.
Examplesofbehaviororattributesthatyoucanconfigureare:
• Whethertoauthorizeusers
• Pagesizeoftablesandindexes
• Whereandwhethertocreateanerrorlog
• Whichdatabasesinthesystemtoboot
Scope of properties
YouusepropertiestoconfigureaDerbysystem,database,orconglomerate.
• system-wide
Mostpropertiescanbesetonasystem-widebasis;thatis,yousetapropertyfor
theentiresystemandallitsdatabasesandconglomerates,ifthisisapplicable.
Someproperties,suchaserrorhandlingandautomaticbooting,canbeconfigured
onlyinthisway,sincetheyapplytotheentiresystem.(Forinformationaboutthe
Derbysystem,seetheDerbyDeveloper'sGuide.)
Whenyouchangetheseproperties,theyaffectanytablesorindexescreatedafter
thischange.
• database-wide
Somepropertiescanalsobesetonadatabase-widebasis.Thatis,thepropertyis
truefortheselecteddatabaseonlyandnotfortheotherdatabasesinthesystem
unlessitissetindividuallywithineachofthem.
Whenyouchangetheseproperties,theyaffectanytablesorindexescreatedafter
thischange.
• conglomerate-specific
BeginningwithDerbypropertiesrelatingtoconglomeratescannotbespecifiedas
partofthecreate-statementfortheobject.Theseproperties(forexample,
derby.storage.pageSize)mustbesetatthedatabaselevelpriortoexecutingthe
create-statement.Thesestorage-relatedpropertiestakeeffectwhenyoucreatea
tableorindex,andcannotbechangedduringthelifetimeoftheconglomerate
Persistence of properties
Adatabase-widepropertyalwayshaspersistence.Thatis,itsvalueisstoredinthe
database.Typically,itisineffectuntilyouexplicitlychangethepropertyoruntilyouseta
system-widepropertywithprecedenceoverdatabase-wideproperties(seePrecedence
ofproperties).
Asystem-widepropertymighthavepersistence,dependingonhowyousetit.Ifyousetit
programmatically,itpersistsonlyforthedurationoftheJVMoftheapplicationthatsetit.
Ifyousetitinthederby.propertiesfile,apropertypersistsuntil:
• Thatvalueischanged
• Thefileisremovedfromthesystem
• Thedatabaseisbootedoutsideofthatsystem
Precedence of properties
5
Copyright
Thesearchorderforpropertiesis:
1. System-widepropertiessetprogrammatically(asacommand-lineoptiontotheJVM
whenstartingtheapplicationorwithinapplicationcode)
2. Database-wideproperties
3. System-widepropertiessetinthederby.propertiesfile
Thismeans,forexample,thatsystem-widepropertiessetprogrammaticallyoverride
database-widepropertiesandsystem-widepropertiessetinthederby.propertiesfile,and
thatdatabase-widepropertiesoverridesystem-widepropertiessetinthederby.properties
file.
Protectionofdatabase-wideproperties
Thereisoneimportantexceptiontothesearchorderforpropertiesdescribedabove:
Whenyousetthederby.database.propertiesOnlypropertytotrue,database-wide
propertiescannotbeoverriddenbysystem-wideproperties.
Thispropertyensuresthatadatabase'senvironmentcannotbemodifiedbythe
environmentinwhichitisbooted.Anyapplicationrunninginanembeddedenvironment
cansetthispropertytotrueforsecurityreasons.
Dynamic versus static properties
Mostpropertiesaredynamic;thatmeansyoucansetthemwhileDerbyisrunning,and
theirvalueschangewithoutrequiringarebootofDerby.Insomecases,thischange
takesplaceimmediately;insomecases,ittakesplaceatthenextconnection.
Somepropertiesarestatic,whichmeanschangestotheirvalueswillnottakeeffectwhile
Derbyisrunning.Youmustrestartorsetthembefore(orwhile)startingDerby.
Formoreinformation,seeDynamicorstaticchangestoproperties.
Ways of setting Derby properties
Thissectioncoversthedifferentwaysofsettingproperties.
System-wide properties
Youcansetsystem-widepropertiesprogrammatically(asacommand-lineoptiontothe
JVMwhenstartingtheapplicationorwithinapplicationcode)orinthetextfile
derby.properties.
Changingthesystem-widepropertiesprogrammatically
Youcansetpropertiesprogrammatically-eitherinapplicationcodebeforebootingthe
Derbydriverorasacommand-lineoptiontotheJVMwhenbootingtheapplicationthat
startsupDerby.Whenyousetpropertiesprogrammatically,thesepropertiespersistonly
forthedurationoftheapplication.Propertiessetprogrammaticallyarenotwrittentothe
derby.propertiesfileormadepersistentinanyotherwaybyDerby.
Note:Settingpropertiesprogrammaticallyworksonlyfortheapplicationthatstartsup
Derby;forexample,foranapplicationinanembeddedenvironmentorfortheapplication
serverthatstartsupaserverproduct.Itdoesnotworkforclientapplicationsconnecting
toaserverthatisrunning.
As a parameter to the JVM command line:
Youcansetsystem-widepropertiesasparameterstotheJVMcommandlinewhen
startinguptheapplicationorframeworkinwhichDerbyisembedded.
• IBMApplicationDeveloperKits
WiththeIBM®SDK,yousetJVMsystempropertiesbyusinga-DflagontheJava
commandline.Forexample:
6
Copyright
java -Dderby.system.home=C:\home\Derby\
-Dderby.storage.pageSize=8192 JDBCTest
ForotherJVMs,seetheJVM-specificdocumentationonsettingsystemproperties.
Using a properties object within an application or statement:
Inembeddedmode,yourapplicationrunsinthesameJVMasDerby,soyoucanalsoset
systempropertieswithinanapplicationusingaPropertiesobjectbeforeloadingthe
DerbyJDBCdriver.Thefollowingexamplesetsderby.system.homeonWindows.
Properties p = System.getProperties();
p.put("derby.system.home", "C:\databases\sample");
Note:IfyoupassinaPropertiesobjectasanargumenttothe
DriverManager.getConnectioncallwhenconnectingtoadatabase,thosepropertiesare
usedasdatabaseconnectionURLattributes,notaspropertiesofthetypediscussedin
thisbook.
Changingthesystem-widepropertiesbyusingthederby.propertiesfile
Youcansetpersistentsystem-widepropertiesinatextfilecalledderby.properties,which
mustbeplacedinthedirectoryspecifiedbythederby.system.homeproperty.There
shouldbeonederby.propertiesfilepersystem,notperdatabase.Thefilemustbe
createdinthesystemdirectory.Inaclient/serverenvironment,thatdirectoryisonthe
server.(FormoreinformationaboutaDerbysystemandthesystemdirectory,see"Derby
System"intheDerbyDeveloper'sGuide.)
Derbydoesnot:
• Providethisfile
• Automaticallycreatethisfileforyou
• Automaticallywriteanypropertiesorvaluestothisfile
Instead,youmustcreate,write,andeditthisfileyourself.
Thefileshouldbeintheformatcreatedbythejava.util.Properties.savemethod.
Thefollowingisthetextofasamplepropertiesfile:
derby.infolog.append=true
derby.storage.pageSize=8192
derby.storage.pageReservedSpace=60
Propertiessetthiswayarepersistentforthesystemuntilchanged,untilthefileis
removedfromthesystem,oruntilthesystemisbootedinsomeotherdirectory(inwhich
caseDerbywouldbelookingforderby.propertiesinthatnewdirectory).Ifadatabaseis
removedfromasystem,system-widepropertiesdonot"travel"withthedatabaseunless
explicitlysetagain.
Verifyingsystemproperties
Youcanfindoutthevalueofasystempropertyifyousetitprogrammatically.Youcannot
findoutthevalueofasystempropertyifyousetitinthederby.propertiesfile.
Forexample,ifyousetthevalueofthederby.storage.pageSizesystem-wide
propertyinyourprogramoronthecommandline,thefollowingcodewillretrieveitsvalue
fromtheSystemPropertiesobject:
Properties sprops = System.getProperties();
System.out.println("derby.storage.pageSize value: "
+ sprops.getProperty("derby.storage.pageSize"));
7
Copyright
Database-wide properties
Database-wideproperties,whichaffectasingledatabase,arestoredwithinthedatabase
itself.ThisallowsdifferentdatabaseswithinasingleDerbysystemtohavedifferent
propertiesandensuresthatthepropertiesarecorrectlysetwhenadatabaseismoved
awayfromitsoriginalsystemorcopied.
Note:Youshouldusedatabase-widepropertieswhereverpossibleforeaseof
deployment.
Yousetandverifydatabase-widepropertiesusingsystemprocedureswithinSQL
statements.
Tosetaproperty,youconnecttothedatabase,createastatement,andthenusethe
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTYprocedure,passingthenameofthe
propertyandthevalue.
Tocheckthecurrentvalueofaproperty,youconnecttothedatabase,createa
statement,andthenusetheSYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTYfunction,
passinginthenameoftheproperty.
Ifyouspecifyaninvalidvalue,Derbyusesthedefaultvaluefortheproperty.
SeetheDerbyReferenceManualformoreinformationonhowtousethesesystem
functionsandprocedures.
In a client/server environment
Inaclient/serverenvironment,youmustsetthesystempropertiesfortheserver's
system.Thatmeanswhenyouareusingthederby.propertiesfile,thefileexistsinthe
server'sderby.system.homedirectory.Clientapplicationscansetdatabase-widebecause
theyaresetviaSQLstatements.Clientapplicationscansetdynamicsystem-wide
propertiesinanSQLstatement,asshownintheexampleinUsingapropertiesobject
withinanapplicationorstatement.
Table1.Summaryofwaystosetproperties
Typeofproperty Howyousetit
System-wide • Inderby.properties
• Programmatically(asacommand-lineoptiontotheJVM
whenstartingtheapplicationorwithinapplicationcode)
Database-wide UsingsystemproceduresandfunctionsinanSQLstatement
Dynamic or static changes to properties
Note:Propertiessetinthederby.propertiesfileandonthecommandlineofthe
applicationthatbootsDerbyarealwaysstatic,becauseDerbyreadsthisfileandthose
parametersonlyatstartup.
Onlypropertiessetinthefollowingwayshavethepotentialtobedynamic:
• Asdatabase-wideproperties
• Assystem-widepropertiesviaaPropertiesobjectintheapplicationinwhichthe
Derbyengineisembedded
SeeDerbyproperties,forinformationaboutspecificproperties.
Properties case study
8
Description:starting up the application or framework in which Derby is embedded. • IBM
Application Developer Kits. With the IBM® SDK, you set JVM system properties
by
