数据库-研究生课件：Viper-XML-DB2

DB2ViperTechnology

PureXMLIntegrationWorldwideInformationManagementTechnicalPresalesDisclaimer/TrademarksInformationconcerningnon-IBMproductswasobtainedfromthesuppliersofthoseproducts,theirpublishedannouncements,orotherpubliclyavailablesources.IBMhasnottestedthoseproductsandcannotconfirmtheaccuracyofperformance,compatibility,oranyotherclaimsrelatedtonon-IBMproducts.Questionsonthecapabilitiesofnon-IBMproductsshouldbeaddressedtothesuppliersofthoseproducts.AllstatementsregardingIBM'sfuturedirectionorintentaresubjecttochangeorwithdrawalwithoutnotice,andrepresentgoalsandobjectivesonly.Thisinformationmaycontainexamplesofdataandreportsusedindailybusinessoperations.Toillustratethemascompletelyaspossible,theexamplesincludethenamesofindividuals,companies,brands,andproducts.Allofthesenamesarefictitious,andanysimilaritytothenamesandaddressesusedbyanactualbusinessenterpriseisentirelycoincidental.TrademarksThefollowingtermsaretrademarksorregisteredtrademarksofothercompaniesandhavebeenusedinatleastoneofthepagesofthepresentation:ThefollowingtermsaretrademarksofInternationalBusinessMachinesCorporationintheUnitedStates,othercountries,orboth:AIX,AS/400,DataJoiner,DataPropagator,DB2,DB2Connect,DB2Extenders,DB2OLAPServer,DB2UniversalDatabase,DistributedRelationalDatabaseArchitecture,DRDA,eServer,IBM,IMS,iSeries,MVS,Net.Data,OS/390,OS/400,PowerPC,pSeries,RS/6000,SQL/400,SQL/DS,Tivoli,VisualAge,VM/ESA,VSE/ESA,WebSphere,z/OS,zSeriesMicrosoft,Windows,WindowsNT,andtheWindowslogoaretrademarksofMicrosoftCorporationintheUnitedStates,othercountries,orboth.

IntelandPentiumaretrademarksofIntelCorporationintheUnitedStates,othercountries,orboth.

UNIXisaregisteredtrademarkofTheOpenGroupintheUnitedStatesandothercountries.

JavaandallJava-basedtrademarksaretrademarksofSunMicrosystems,IheUnitedStates,othercountries,orboth.

Othercompany,product,orservicenamesmaybetrademarksorservicemarksofothers.PureXMLinDB2Standardscompliant+drivingthestandardsXML,XQuery,SQL/XML,XMLSchema…100%integratedinDB2leveragingperformance,scalability,reliability,availability…100%integratedwithSQLXMLisanewSQLtypeAccessrelationalandXMLdatainsamestatement100%integratedwithapplicationAPIs:JDBC,ODBC,.NET,embeddedSQL,PHP PureXMLinDB2FlexibilitybecausethatiswhatXMLisallabout..Anydocument,Anyschema,NotjusttheonesthataremappedtorelationaltablesXMLstorage:severaloptions:Native:XMLisparsedandstoredhierarchical.Shredded:usingannotatedSchemaBLOBSophisticatedXMLindexing"XANDORjoin"toevaluatemanypredicatesconcurrentlyBroadXQuerysupportBothembeddedinSQLandasaprimarylanguageSupportsDigitalSignaturesSignaturescanbevalidatedonretrieveddocumentsIntegrationofXML&RelationalCapabilitiesApplicationscombineXML&relationaldataNativeXMLdatatype(server&clientside)XMLCapabilitiesinallDB2componentsDB2SERVERCLIENTSQL/XMLXQueryDB2EngineXMLInterfaceRelationalInterfaceRelationalXMLDB2Storage:DB2Client/CustomerClientApplicationNativeXMLStorageDB2storesXMLinparsedhierarchicalformat(~DOM)

createtabledept(deptIDchar(8),…,deptdocxml);Relationalcolumns

arestoredinrelational

format(tables)XMLcolumnsare

storednatively

NoXMLparsing

forqueryevaluation!deptID…deptdoc"PR27"…<dept>…<emp>…</emp></dept>………DB2Storage<dept><employeeid=901><name>JohnDoe</name><phone>4085551212</phone><office>344</office></employee><employeeid=902><name>PeterPan</name><phone>4085559918</phone><office>216</office></employee></dept>Parsedeptnameemployeephoneid=901JohnDoeoffice408-555-1212344nameemployeephoneid=902PeterPanoffice408-555-9918216XMLtextrepresented

asdocumenttreeTheDocumentTreeEfficientDocumentTreeStoragedeptnameemployeephoneid=901JohnDoeoffice408-555-1212344nameemployeephoneid=902PeterPanoffice408-555-991821601425=901JohnDoe3408-555-12123441425=902PeterPan3408-555-99182160dept4employee1name5id2phone3officeStringtableSYSIBM.SYSXMLSTRINGSReducedstorageFastcomparisons&navigation"Compression"Tagnamesencoded

asuniqueintegersHowtouseXMLinDB2?createtableT(iint,docxml)insertintoTvalues(22,?)selecti,docfromT

StorageDocumentisstoredinparsedrepresentationIBM’sversionofopen-sourceXercesisused.TheXQueryDataModelispersistedAlldataisstoredinUTF-8RegardlessofthedocumentencodingRegardlessofthelocaleRegardlessofthecodepageofthedatabaseAllXMLnodesaretypeannotated,accordingtotheXQueryspecXMLSchematypesifvalidatedDefaulttypesotherwise.OnenodeinthetreeCanbeelement,attribute,text…Contains:Name,namespace,namespaceprefix(stringIDs)Typeannotation(pathID)"pointer"toparentListof"pointerstochildren"Possiblyinlinedvalues(attribute/textvalues)XMLNodeStorageLayoutNodehierarchyofanXMLdocstoredonDB2pagesDocumentsthatdon’tfiton1page:splitintopages/regionsExample:Documentsplitinto3regions,storedon3pagesXMLStorage:"RegionsIndex"NodesarephysicallyconnectedRegionsarelogicallyconnectedKeyinRegionsindexisNIDE.g.Nid=pagepagepageRegionsindexnotuserdefined,defaultcomponentofXMLstorageConcurrencyControlAlltheSQLisolationlevelsaresupportedConcurrencycontrolperrow(asinSQL)Underthecover:DocumentsareversionedbecauseXQueryreferencesemanticsNeedtoavoidcopyingNeedtoseeconsistentdocumentsRegionsindexisusedtolocateversionsofdocsVersionsthatarenotneededanymorearereclaimedTransparentlymanagedbythesystemUseronlyseesSQLisolationlevelsXMLIndexesforHighQueryPerformanceDefine0,1ormultipleXMLValueIndexesperXMLcolumnXMLindexmaps:(pathID,value)(nodeID,rowID)Indexanyelementsorattributes,incl.mixedcontentIndexdefinitionusesanXMLpatterntospecifywhichelements/attributestoindex(andwhichnotto)Canindexallelements/attributes,

butnotforcedtodosoCanindexrepeatingelements

0,1ormultipleindexentriesperdocumentNewXML-specificjoinandqueryevaluationmethods,evaluatemultiplepredicatesconcurrentlywithminimalindexI/Oxmlpattern=XPath

withoutpredicates,

onlychildaxis(/)and

descendent-or-selfaxis(//)

XMLValuesIndexCREATEINDEXChangesUsesexistingCREATEINDEXstatementmodifiedforXMLsupportCompositekeysconsistingofmultiplecolumnswithdifferentdatatypesarenotsupportedUsesanXMLpatternexpressiontoindexpathsandvaluesinXMLdocumentsstoredinasingleXMLcolumnOnlyindexesdocumentnodesthatsatisfytheXMLpatternexpressionXMLindexspecificationGENERATEKEYUSINGXMLPATTERNkeywordsXMLpatternexpressionOptionalnamespacedeclarationDatatypeCreateindexAgeUnitIndexont1(XMLDoc)generatekeysusingxmlpattern'/Person/Age/@unit'assqlvarchar(16);ASSQLVARCHAR(integer)CREATEindex-nameONtable-name(xml-column-name)GENERATEKEYUSINGXMLPATTERNxmlpatternUNIQUEtext()@attribute-tag@*///element-tag*///INDEXDOUBLEDATETIMESTAMPVARCHAR(HASHED)xmlpattern:xmlpattern

=XPath

withoutpredicates,

onlychildaxis(/)and

descendent-or-selfaxis(//)

CreateXMLIndexcreateindexxmlindex1ondept(deptdoc) generatekeyusingxmlpattern‘/dept/name’asvarchar(30);Declaration&useofnamespaceprefixsupported(notshownabove)XMLIndexing:Examplescreatetabledept(deptIDchar(8)primarykey,deptdoc

xml);

createuniqueindexidx1ondept(deptdoc)generatekeyusingxmlpattern'/dept/@bldg'assqldouble;createuniqueindexidx2ondept(deptdoc)generatekeyusingxmlpattern'/dept/employee/@id'assqldouble;createindexidx3ondept(deptdoc)generatekeyusingxmlpattern'/dept/employee/name'assqlvarchar(35);…xmlpattern'//name'assqlvarchar(35); (IndexonALL"name"elements)…xmlpattern'//@*'assqldouble; (IndexonALLnumericattributes)…xmlpattern'//text()‘assql

varchar(hashed); (IndexonALLtextnodes,hashcode)…xmlpattern‘/dept//name'assqlvarchar(35);…xmlpattern'/dept/employee//text()'assqlvarchar(128);(Alltextnodesunderemployee)<deptbldg=101><employeeid=901><name>JohnDoe</name><phone>4085551212</phone><office>344</office></employee><employeeid=902><name>PeterPan</name><phone>4085559918</phone><office>216</office></employee></dept>XMLFullTextSearchDB2NetSearchExtenderenhancedforXMLFullXML-awareindexofentiredocCreateindexmyIndexfortext

onmyTable(xmlcol);CanalsoindexpartialdocumentsSimpleusageselectxmlcolfrommyTablewhere

contains(xmlcol,‘sections("/book/section")"Brazil"')=1ComplexsearchcriteriaalsosupportedManagementisintegratedintheDB2ControlCenterImportingDataSimilartoIMPORTofLobs1)Filewithsqldata2)DirectorywhichcontainsXMLdatafiles1000,"<XDSFIL='C1.xml'/>"1001,"<XDSFIL='C2.xml'/>"1002,"<XDSFIL='C3.xml'/>"1003,"<XDSFIL='C4.xml'/>"1004,"<XDSFIL='C5.xml'/>"SampleInputoffileforimportingtoCUSTOMERXMLtableCIDINFOValueofCIDPointertoassociatedXMLDocumentFileName:s:\shared\IBM\data\import_customer.delImportCommandimportfroms:\shared\IBM\data\import_customer.delofdel

XMLfroms:\shared\IBM\data\customerdocs

ModifiedbyXMLCHAR

replaceintoXMLCUSTOMERFileToInputTheDirectorythatincludestheXMLFilesarethatarereferencedintheinputfileImportFlow1000,"<XDSFIL='C1.xml'/>"1001,"<XDSFIL='C2.xml'/>"1002,"<XDSFIL='C3.xml'/>"1003,"<XDSFIL='C4.xml'/>"1004,"<XDSFIL='C5.xml'/>"importfroms:\shared\IBM\data\import_customer.delofdel

XMLfroms:\shared\IBM\data\customerdocs

ModifiedbyXMLCHAR

replaceintoXMLCUSTOMERs:\shared\IBM\data\import_customer.dels:\shared\IBM\data\customerdocs\C1.xmls:\shared\IBM\data\customerdocs\C2.xmls:\shared\IBM\data\customerdocs\C3.xmls:\shared\IBM\data\customerdocs\C4.xmls:\shared\IBM\data\customerdocs\C5.xmlTable:XMLCustomer1000<?xmlversion="1.0"encoding="UTF-8"?><customerinfo><name>AmirMalik</name><addrcountry="UnitedStates"><street>555BaileyAve</street><city>SanJose</city><prov-state>California</prov-state><pcode-zip>95141</pcode-zip></addr><phonetype="work">408-555-1358</phone></customerinfo>1001<?xmlversion="1.0"encoding="UTF-8"?><customerinfo><name>KathySmith</name>…1002<?xmlversion="1.0"encoding="UTF-8"?><customerinfo><name>JimNoodle….1003<?xmlversion="1.0"encoding="UTF-8"?><customerinfo><na…1004<?xmlversion="1.0"encoding="UTF-8"?><customerinfo><name>BertandErnieInc………….XMLSchemaRepository(XSR)DatabaseneedsaSchemarepository:Stable&highperformanceaccesstoSchemasforvalidationatXMLinsert/updatetimeSupportforXMLSchemamanagementXSRXMLSchemasareregistered:Consistentsetof.xsddocumentAlsoDTDsandExternalentitiesUsedforentityreferenceresolutionanddefaultsNOTusedforvalidationRegisteredSchemaidentification:ASQL2-partnameTheURLtheSchemaisexternallyknownas(e.g.usedinschemaLocationattributes)The"primarynamespace"AlsousedbyShredStoresannotatedSchemaInternalformatstomakeShreddingefficient.Implementation:AlldataisstoredintheDB2CatalogBinaryrepresentationoftheSchemaforfastvalidationSchemaRegistrationSchemaRegistrationREGISTERXMLSCHEMA 'http://myPOschema/PO'FROM 'file://c:/TEMP/PO.xsd'AS user1.POschemaADDXMLSCHEMADOCUMENTTOuser1.POschemaADD'http://myPOschema/address'FROM'file://c:/TEMP/address.xsd'

COMPLETEXMLSCHEMA