DB2实验报告文档格式.docx

DB2实验报告文档格式.docx

《DB2实验报告文档格式.docx》由会员分享，可在线阅读，更多相关《DB2实验报告文档格式.docx（21页珍藏版）》请在冰点文库上搜索。

实验地点

机器编号

指导教师

实验时间

年月日时分

一、实验综述

1.实验目的及要求

Thisexerciseisanonlinelabwhichcreatesobjectsusedinadatabase.

Attheendofthelab,studentsshouldbeableto:

•Createtables•Addreferentialintegrityconstraintstoatable

•Createindexes•Addcheckconstraintstoatable

•Createviews•Addtriggerstoatable

•Createanalias•AccessSystemCataloginformationaboutobjects

•RetrieveanXMLdocument

2.实验设备、软件

PC, 

windows 

XPProfessional, 

DB2 

9 

Express-c

二、实验过程（实验步骤、记录、数据、分析）

Section1-CreateTables

1.CreateyourARTISTStable.Thetableshouldhavethefollowingcharacteristics.

•Tableschemaofstudent

•TableNameofartists

•Regulardatatogointablespacedms01

•Indexestogointablespacedms02

•Longdatatogointablespacedms03

•Thecolumnartnoshouldbedefinedasaprimarykey

•Columnsdefinedlikethefollowing:

（artnosmallintnotnull,

namevarchar（50）,

classificationchar

（1）notnull,

bioclob（100K）loggedcompact,

pictureblob（500k）notloggedcompact）

Showyourworkbelowbyprintingscreen.

手动创建表空间dms01,dms02,dms03，如下：

执行SQL语句结果如下：

2.VerifythecreationofyourARTISTStableandthetabledescription.

Important:

Ensurethedefinitionofyourtableiscorrect.Itcouldcauseunexpectedfailuresinlaterlabsifyourdefinitionsarenotdonecorrectly.

3.Youwillcreatetheremainderofthetablesrequiredusingascriptfilecalledcrtables.ForWindows,itislocatedintheC:

\labfiles\cf23directory.

ExaminetheDB2statementsinthefileandanswerthefollowingquestions.

InwhichtablespacewillboththeindexesanddatafortableSTOCKbeplacedForCONCERTSForREORDER

4.Executethecrtablesscripttocreatethetables.

Showyourworkbelowbyprintingscreen

5.Verifythatalltables（ALBUMS,STOCK,CONCERTS,andREORDER）werecreatedsuccessfully.

6.Informationabouteachcolumninatablecanbefoundbyaccessingtheview.

EnteraninteractiveSQLstatementtoselectthecolumnsTABNAME,COLNAME,andTYPENAMEfromforalltableswiththeTABSCHEMAof‘student’,orderthedatabyTABNAMEandCOLNO,andpipetheoutputtoafilenamed.

Showyoursqlstatementsandyourworkbelowbyprintingscreenforthelaststep.

7.Checkyouroutputagainstthefile.

Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutputorthefollowinglabsmaynotworkcorrectly!

8.Informationaboutatable'

sdefinitiontoatablespacecanbefoundbyaccessingtheview.EnteranSQLstatementtoselectthecolumnsTABNAME,TBSPACE,andINDEX_TBSPACEfromforalltableswiththeTABSCHEMAof‘student’,orderthedatabyTABNAME,andpipetheoutputtoafilenamed.

9.Checkyouroutputagainstthefile.

Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!

10.Grantselectprivilegeontablesthatareownedbystudenttopublicbyexecutingascriptfile,grants（anoperatingsystemcommandfile）forlocaladministration.

Section2-CreateIndexes

1.CreateanindexcalledITEMontheITEMNOcolumninSTOCKtable.

2.CreateauniqueindexcalledITEMNOontheITEMNOcolumninALBUMStable.

3.Selecttheinformationfromthecatalogtablesaboutyourindexes.Thecolumnsyoushouldselectarethefirst18charactersofTABNAME,UNIQUERULE,thefirst18charactersofINDNAME,andthefirst30charactersofCOLNAMESfrom,whereINDSCHEMAisstudent,orderbyTABNAMEandINDNAME.

IfUNIQUERULE="

U"

thenonlyuniquevaluesareallowed.

D"

thenduplicatevaluesareallowed.

P"

thenitisaPrimarykey.

Showyourworkandyournewindexesbelowbyprintingscreen.

4.Usetheselectyouexecutedinthepreviousstepandroutetheoutputto.Checkyourresultsagainst.

Section3-CreateViews

1.Createaviewcalledmusicthatwillselecttitle,classification,namefromalbums,artistswhere=.

2.Createanotherviewcalledinventorybyexecutingthescriptfile,crview.

3.Informationaboutviewscanbefoundintheandviews.TablesandviewsforthecurrentusercanbeshownwithaLISTTABLESstatement.ATYPEofVisaview.

Issueoneofthefollowingstatementsandverifyyourviewsarelisted.

•LISTTABLESstatementOR

•SelectcolumnsTABSCHEMA,TABNAME,andTYPEfromtheviewwithaTABSCHEMAofstudentOR

•SelectcolumnsVIEWSCHEMA,VIEWNAMEfromwithDEFINERofstudent

Section4-CreateAlias

1.CreateanaliascalledsingersfortheARTISTStable,andanaliascalledemptystockfortheREORDERtable.

2.Informationaboutaliasescanbefoundintheview.Tables,views,andaliasesforthecurrentusercanalsobeshownwiththeLISTTABLESstatement.ATYPEofAisanalias.Issueoneofthefollowingstatementsandverifyyouraliasesarelisted.

•LISTTABLESstatementOR

•SelectcolumnsTABNAMEandTYPEfromtheviewwithaTABSCHEMAofstudent.

Section5-AddReferentialIntegrity

1.AltertheALBUMStableanddefinereferentialintegrityrelationshipsithaswithothertables.

•Addaprimarykeyontheitemnocolumn

•AddaforeignkeycalledfkartnoontheartnocolumnwhichreferencesaprimarykeyintheARTISTStable

•ThedeleterulebetweenARTISTSandALBUMSshouldbedeletecascade

2.NowaltertheSTOCKtabletodefineitsreferentialintegrityrelationships,byexecutingthescriptfile,crri.

3.Informationaboutreferentialintegritycanbefoundbyaccessingtheview.Tabledependenciescanalsobeaccessedthroughtheview.EnteraninteractiveSQLstatementtoselectCONSTNAME,TABNAME,REFTABSCHEMA,REFTABNAME,DELETERULEfromtheviewwithaTABSCHEMAofstudent.

4.Runaselecttoretrievethefirst18charactersofTABNAME,andthePARENTSandCHILDRENcolumnsfromwheretheTABSCHEMAisequaltoyouruserid.OrdertheresultsbyTABNAMEanddirectyouroutputtoafilenamed.TheSQLcanbefoundinafilenamed.

Checkyouroutputagainstthefile.

Section6-AddCheckConstraints

1.AltertheSTOCKtableandaddacheckconstrainttoit.Thecheckconstraintshouldhavethefollowingcharacteristics.

•Itshouldbenamedcctype.

•Thebusinessruleitshouldenforceistoonlyallowthevaluesof'

D'

'

C'

or'

R'

inthetypecolumn.

Makesurethealphanumericliteralsaretypedinuppercaseletters.

2.Informationaboutcheckconstraintscanbefoundbyaccessingthe,,,andviews.IssueSQLto:

SelectCONSTNAME,TABNAME,COLNAMEfromtheview.

SelectCONSTNAME,TABNAME,TYPEfromtheview.

IftheTYPE=“K”,thenitisacheckconstraint.IftheType=“P”,thenitisaprimarykey.IftheType=“F”,thenitisaforeignkey.

3.Tocheckyourwork,runaselectstatementthatselectsthefirst100charactersoftheTEXTcolumnfromwhereCONSTNAME='

CCTYPE'

anddirectyouroutputtoafilenamed.TheSQLcanbefoundinafilenamed.Checkyouroutputagainstthefile.

Section7-CreateaTrigger

1.Createatriggerwhichhasthefollowingcharacteristics.

•Itshouldbenamedreorder

•ItshouldfireafteranupdateoftheqtycolumnontheSTOCKtable,ifthenewvalueofqtyis<

=5

•Newshouldbereferencedasn

•ThetriggeredactionshouldinsertthevaluesandcurrenttimestampintotheREORDERtable

•Foreachrowmodedb2sql

2.Informationabouttriggerscanbefoundbyaccessingtheandviews.

EnterinteractiveSQLtoresearchtheREORDERtrigger:

SelectTRIGNAME,TABNAME,andTRIGEVENTcolumnsfromtheview.

TRIGEVENTdescribestheeventthatfiresthetriggerasIforinsert,Dfordelete,orUforupdate.

SelectTRIGNAME,BTYPE,BSCHEMA,BNAMEcolumnsfromview.BTYPEandBSCHEMAgivethenameoftheobjecteddependedonbythetrigger.BTYPEdescribesthetypeofbaseobjectasAforalias,Fforfunctioninstance,Tfortable,orVforview.

Section8-OPTIONAL-WorkingwithXML

1.IftheSAMPLEdatabasehasnotyetbeencreate,createitnow.

2.ConnecttotheSAMPLEdatabase.

3.UseXQUERYandthedb2-fn:

xmlcolumnfunctiontoretrievealloftheXMLdocumentsfromthecustomertable’sinfocolumn.