DB2实验报告3.docx
《DB2实验报告3.docx》由会员分享，可在线阅读，更多相关《DB2实验报告3.docx（17页珍藏版）》请在冰豆网上搜索。
DB2实验报告3
吉林大学
DB2实验报告
班级：
姓名：
学号：
姓名
学号
实验项目
3.Createobjects
实验性质
□演示性实验□验证性实验
操作性实验□综合性实验
实验地点
机器编号
指导教师
实验时间
年月日时分
一、实验综述
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.
2.VerifythecreationofyourARTISTStableandthetabledescription.
Important:
Ensurethedefinitionofyourtableiscorrect.Itcouldcauseunexpectedfailuresinlaterlabsifyourdefinitionsarenotdonecorrectly.
Showyourworkbelowbyprintingscreen.
3.Youwillcreatetheremainderofthetablesrequiredusingascriptfilecalledcrtables.ForWindows,itislocatedintheC:
\labfiles\cf23directory.
ExaminetheDB2statementsinthefileandanswerthefollowingquestions.
InwhichtablespacewillboththeindexesanddatafortableSTOCKbeplaced?
ForCONCERTS?
ForREORDER?
4.Executethecrtablesscripttocreatethetables.
Showyourworkbelowbyprintingscreen.
5.Verifythatalltables（ALBUMS,STOCK,CONCERTS,andREORDER）werecreatedsuccessfully.
Showyourworkbelowbyprintingscreen.
6.InformationabouteachcolumninatablecanbefoundbyaccessingtheSYSCAT.COLUMNSview.
EnteraninteractiveSQLstatementtoselectthecolumnsTABNAME,COLNAME,andTYPENAMEfromSYSCAT.COLUMNSforalltableswiththeTABSCHEMAof‘student’,orderthedatabyTABNAMEandCOLNO,andpipetheoutputtoafilenamedtabchk.file.
Showyoursqlstatementsandyourworkbelowbyprintingscreenforthelaststep.
7.Checkyouroutputagainstthetabchk.masterfile.
Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutputorthefollowinglabsmaynotworkcorrectly!
Showyourworkbelowbyprintingscreen.
8.Informationaboutatable'sdefinitiontoatablespacecanbefoundbyaccessingtheSYSCAT.TABLESview.EnteranSQLstatementtoselectthecolumnsTABNAME,TBSPACE,andINDEX_TBSPACEfromSYSCAT.TABLESforalltableswiththeTABSCHEMAof‘student’,orderthedatabyTABNAME,andpipetheoutputtoafilenamedtbschk.file.
Showyoursqlstatementsandyourworkbelowbyprintingscreenforthelaststep.
9.Checkyouroutputagainstthetbschk.masterfile.
Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!
Showyourworkbelowbyprintingscreen.
10.Grantselectprivilegeontablesthatareownedbystudenttopublicbyexecutingascriptfile,grants（anoperatingsystemcommandfile）forlocaladministration.
Showyourworkbelowbyprintingscreen.
Section2-CreateIndexes
1.CreateanindexcalledITEMontheITEMNOcolumninSTOCKtable.
Showyourworkbelowbyprintingscreen.
2.CreateauniqueindexcalledITEMNOontheITEMNOcolumninALBUMStable.
Showyourworkbelowbyprintingscreen.
3.Selecttheinformationfromthecatalogtablesaboutyourindexes.Thecolumnsyoushouldselectarethefirst18charactersofTABNAME,UNIQUERULE,thefirst18charactersofINDNAME,andthefirst30charactersofCOLNAMESfromSYSCAT.INDEXES,whereINDSCHEMAisstudent,orderbyTABNAMEandINDNAME.
IfUNIQUERULE="U",thenonlyuniquevaluesareallowed.
IfUNIQUERULE="D",thenduplicatevaluesareallowed.
IfUNIQUERULE="P",thenitisaPrimarykey.
Showyourworkandyournewindexesbelowbyprintingscreen.
4.Usetheselectyouexecutedinthepreviousstepandroutetheoutputtoindchk.file.Checkyourresultsagainstindchk.master.
Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!
Showyourworkbelowbyprintingscreen.
Section3-CreateViews
1.Createaviewcalledmusicthatwillselecttitle,classification,namefromalbums,artistswhereartists.artno=albums.artno.
Showyourworkbelowbyprintingscreen.
2.Createanotherviewcalledinventorybyexecutingthescriptfile,crview.
Showyourworkbelowbyprintingscreen.
3.InformationaboutviewscanbefoundintheSYSCAT.VIEWSandSYSCAT.TABLESviews.TablesandviewsforthecurrentusercanbeshownwithaLISTTABLESstatement.ATYPEofVisaview.
Issueoneofthefollowingstatementsandverifyyourviewsarelisted.
•LISTTABLESstatementOR
•SelectcolumnsTABSCHEMA,TABNAME,andTYPEfromtheSYSCAT.TABLESviewwithaTABSCHEMAofstudentOR
•SelectcolumnsVIEWSCHEMA,VIEWNAMEfromSYSCAT.VIEWSwithDEFINERofstudent
Showyourworkbelowbyprintingscreen.
Section4-CreateAlias
1.CreateanaliascalledsingersfortheARTISTStable,andanaliascalledemptystockfortheREORDERtable.
Showyourworkbelowbyprintingscreen.
2.InformationaboutaliasescanbefoundintheSYSCAT.TABLESview.Tables,views,andaliasesforthecurrentusercanalsobeshownwiththeLISTTABLESstatement.ATYPEofAisanalias.Issueoneofthefollowingstatementsandverifyyouraliasesarelisted.
•LISTTABLESstatementOR
•SelectcolumnsTABNAMEandTYPEfromtheSYSCAT.TABLESviewwithaTABSCHEMAofstudent.
Showyourworkbelowbyprintingscreen.
Section5-AddReferentialIntegrity
1.AltertheALBUMStableanddefinereferentialintegrityrelationshipsithaswithothertables.
•Addaprimarykeyontheitemnocolumn
•AddaforeignkeycalledfkartnoontheartnocolumnwhichreferencesaprimarykeyintheARTISTStable
•ThedeleterulebetweenARTISTSandALBUMSshouldbedeletecascade
Showyourworkbelowbyprintingscreen.
2.NowaltertheSTOCKtabletodefineitsreferentialintegrityrelationships,byexecutingthescriptfile,crri.
Showyourworkbelowbyprintingscreen.
3.InformationaboutreferentialintegritycanbefoundbyaccessingtheSYSCAT.REFERENCESview.TabledependenciescanalsobeaccessedthroughtheSYSCAT.TABLESview.EnteraninteractiveSQLstatementtoselectCONSTNAME,TABNAME,REFTABSCHEMA,REFTABNAME,DELETERULEfromtheSYSCAT.REFERENCESviewwithaTABSCHEMAofstudent.
Showyourworkbelowbyprintingscreen.
4.Runaselecttoretrievethefirst18charactersofTABNAME,andthePARENTSandCHILDRENcolumnsfromSYSCAT.TABLESwheretheTABSCHEMAisequaltoyouruserid.OrdertheresultsbyTABNAMEanddirectyouroutputtoafilenamedrichk.file.TheSQLcanbefoundinafilenamedrichk.sql.
Checkyouroutputagainsttherichk.masterfile.
Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!
Showyourworkbelowbyprintingscreen.
Section6-AddCheckConstraints
1.AltertheSTOCKtableandaddacheckconstrainttoit.Thecheckconstraintshouldhavethefollowingcharacteristics.
•Itshouldbenamedcctype.
•Thebusinessruleitshouldenforceistoonlyallowthevaluesof'D','C',or'R'inthetypecolumn.
Makesurethealphanumericliteralsaretypedinuppercaseletters.
Showyourworkbelowbyprintingscreen.
2.InformationaboutcheckconstraintscanbefoundbyaccessingtheSYSCAT.CHECKS,SYSCAT.COLCHECKS,SYSCAT.TABCONST,andSYSCAT.TABLESviews.IssueSQLto:
SelectCONSTNAME,TABNAME,COLNAMEfromtheSYSCAT.COLCHECKSview.
SelectCONSTNAME,TABNAME,TYPEfromtheSYSCAT.TABCONSTview.
IftheTYPE=“K”,thenitisacheckconstraint.IftheType=“P”,thenitisaprimarykey.IftheType=“F”,thenitisaforeignkey.
Showyourworkbelowbyprintingscreen.
3.Tocheckyourwork,runaselectstatementthatselectsthefirst100charactersoftheTEXTcolumnfromSYSCAT.CHECKSwhereCONSTNAME='CCTYPE',anddirectyouroutputtoafilenamedckchk.file.TheSQLcanbefoundinafilenamedckchk.sql.Checkyouroutputagainsttheckchk.masterfile.
Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!
Showyourworkbelowbyprintingscreen.
Section7-CreateaTrigger
1.Createatriggerwhichhasthefollowingcharacteristics.
•Itshouldbenamedreorder
•ItshouldfireafteranupdateoftheqtycolumnontheSTOCKtable,ifthenewvalueofqtyis<=5
•Newshouldbereferencedasn
•Thetriggeredactionshouldinsertthevaluesn.itemnoandcurrenttimestampintotheREORDERtable
•Foreachrowmodedb2sql
Showyourworkbelowbyprintingscreen.
2.InformationabouttriggerscanbefoundbyaccessingtheSYSCAT.TRIGGERSandSYSCAT.TRIGDEPviews.
EnterinteractiveSQLtoresearchtheREORDERtrigger:
SelectTRIGNAME,TABNAME,andTRIGEVENTcolumnsfromtheSYSCAT.TRIGGERSview.
TRIGEVENTdescribestheeventthatfiresthetriggerasIforinsert,Dfordelete,orUforupdate.
SelectTRIGNAME,BTYPE,BSCHEMA,BNAMEcolumnsfromSYSCAT.TRIGDEPview.BTYPEandBSCHEMAgivethenameoftheobjecteddependedonbythetrigger.BTYPEdescribesthetypeofbaseobjectasAforali
升级会员 