DB2CommonServer:Technology,Progress,&Directions BruceLindsay IBMAlmadenResearchCenter650HanyRoad,SanJose,California95124099 e-mail:bruce@ Abstract-Advancesinhardwarecapacityandapplicationrequirementsforincreasinglysophisticatedanalysisandexploitationofinformationresourcescontinuetocreatenewchallengesfordatabasemanagementsystems.DB2Commonserver,Release2,addressestheevolvinghardwareenvironmentandapplicationrequirementswithfacilitiesandfeaturesthatsupportbibperformancedatabaseexploitationandextendthepoweroftheRelationalModel Whiletherearemanychallengespresentedbytheneedsofdatabaseuserstomoreeffectivelyexploittheirinformationresourcesw,eshallconcentrateononlyafewoftheplishmentsanddirectionsofDB2CommonServer,Release2.icswediscussinclude:thenewstoragearchitecturedesignedtoaddressthegrowingpixformancegapbetweentheCPUandthedisks,theintroductionofUserDefinedtypesandfunctions;extensionstoSQLthatenhancetheuser’sabilitytoextractandmanipulateinformationinthedatabase:andtheimplementationofdataintegrityfacilitiesthatenhancedatavalue. TheCPU-Z-SI/OGap:anemergingproblem TherapidincreasesinCPUpower,coupledwiththeavailabilityofSMPplatforms,hasoutpacedtheperformanceincreasesofdisksystems.InordertobalanceCPUcapacitywithdiskperformancep,arallelI/ostreamsareneededtokeeptheCPUbusy.DB2CommonServerhasintroducedanewstoragearchitecturewhichallowstables,indexes,andLargeOBjectstobestripedacrossmultipledisks.Largeblockandparallelread-aheadisusedtomatchCPUprocessingtothedisklatencies.FuturedevelopmentwillexploitDBMSknowledgeofdataplacementosupportparallelqueryexecutiondrivenbyparallelI./OstreamsBorndifferentdisks. PsrmieaionthatthetheVLDSandnoticeEndowment. tocopywithout feeallorpartofthiemalcrisl iagrtintedprovided copiesarenotmadeordistributed formsrcirl advantage. copyright noticeandthetitleofthepublication andit-dateappear, iagiventhatcopyingiabypermission oftheVeryLargeDataBus ToCOPYothorwiao, ortorepublish. rc~~uireeafeeand/oraoscirl ObjectsMeetDatabase:persistenceisn’tjustdisks Theimportanceoftype-baseddatabehaviorfordevelopingapplicationsthatmodeltheapplicationdomainmustbecarefullyintegratedintotherealmofpersistentdatamanagementM.anyoftheobject-orientedtechniquessuitablefor(non-persistent)programminglanguageenvironmentsarenotappropriateinthedomainofdatabasesT.obegintheevolutiontowardsobject-oriented,hierarchialtypesystemsD,B2CommonServerintroducesfacilitiestodefinesimpleDISTINCTtypeswhicharerepresentedbyvaluesofbasetypes.Inaddition,stronglytypeduser-definedfunctionsoverbaseandDISTINCTtypesallowuserstoextendthebehaviorofbasetypesandhebehaviorofDISTINCTtypes.Asweevolvetowardsprehensivesupportforabstractdatatypes,considerablecareisneededtopreservetheabilityofapplicationstoworkwiththedataelementsthattheysupplyandextractfromthedambase.Acontinuingchallengeistomontype-basedbehaviorforvaluesinSQLqueriesandforvaluesintheapplication program. SQLExpressivenesse:rtractinginformationvalue Toenhancetheabilityofuserstoextractthemaximumvaluefromtheirstoreddata,DB2CommonServerhasextendedtheSQLlanguagetosupportseveralusefulconstructs.NewsupportforLargeOBject(LOB)basetypesallowsuserstostoreupto2giga-bytesofdatainasingletablefield.LOBSareimplementedusingspecialstorageandrecoverymechanismsthat(optionally)avoidloggingofLOBvaluesandallowthesystemtodeferesstoLOBdatauntilthedataisneededbytheapplication. SupportfortheANSSQL2CASEexpressionhasbeenimplementedinDB2CommonServer.CASEexpressionsallowuserstocodeconditionalexpressionswithinaqueryandcanbeusedtoremapvaluesandgivedatadependentresults.WehavealsoextendedSQLsyntaxtoallowtableexpressionswithintheFROMclauseorattheheadofaquery.Theseview-liketableexpressionasllowqueriesthatwouldotherwiserequirethedefinitionofapermanenvtiew.Tableexpressionsarealso 694 usedinthedefinitionofrecursivequeries.DB2CommonServersupportforrecursivequeriesenablesdatabaseputetransitiveclosures(andotherrecursiveexpressions)withoutrepeatedinteractionsbetweentheapplicationandthedatabase. DataIntegrity:thedevilisinthedetails DB2CommonServerdeliverssupportforcheckconstraintst,riggers,andextendsthesupportofreferentialconstraints.Thesefacilitiesallowuserstospecifydeclarativeconstraintsthatareautomaticallyincorporatedintoqueriesthatmodifythedatabase.Checkconstraintsinsurethatfieldvaluessatisfyaspecifiedpredicate.Referentialconstraintsenforceparent/childrelationshipsandaresupported(correctly)forcyclesofparent/childtables.Triggersrespondtomodificationsandhavethepower(anddangerSo)fproductionrulesystems. DB2CommonServer(unlikesomesystems)enforcestherelationalsetsemanticsforupdatesleadingtocheckorreferentialconstraintprocessing.Setsemanticsrequirethatthefinalstateofthedatabasemustbeindependentoftheorderinwhichasetoftuplesareupdated.Wheneveratablecanaffectwhatgetschangedandisalsoaffectedbychangess,pecialcaremustbetakentoinsureconsistenatnddeterministicresults.- Conclusions:lotsisdone,e Release2ofDB2CommonServerdeliversexcitingnewfunctiontorelationaldatabaseusers.Inaddition,advancedoptimizationandexecutiontechniquesmakeitaleaderinrawperformanceaswellasincostpertransaction.MuchofthenewfunctioninRelease2isderivedfromtechnologydevelopedintheStarburstprojectattheIBMAlmadenResearchCenter.ThetechnologyoftheStarburstsystemhasenabledustopushplexitybaniersthatplaguemanyfullfunctiondatabasesystems.Forthisreason,onecanexpectrapidevolutionoffunctionandadditionalperformanceenhancementisnfuturereleasesofDB2CommonServer. 695