DB2实验报告.docx

DB2实验报告.docx

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

DB2实验报告

吉林大学

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.

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

执行SQL语句结果如下：

2.VerifythecreationofyourARTISTStableandthetabledescription.

Important:

Ensurethedefinitionofyourtableiscorrect.Itcouldcauseunexpectedfailuresinlaterlabsifyourdefinitionsarenotdonecorrectly.

Showyourworkbelowbyprintingscreen.

3.Youwillcreatetheremainderofthetablesrequiredusingascriptfilecalledcrtables.ForWindows,itislocatedintheC:

\labfiles\cf23directory.

ExaminetheDB2statementsinthefileandanswerthefollowingquestions.

InwhichtablespacewillboththeindexesanddatafortableSTOCKbeplacedForCONCERTSForREORDER

4.Executethecrtablesscripttocreatethetables.

Showyourworkbelowbyprintingscreen

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

Showyourworkbelowbyprintingscreen.

6.Informationabouteachcolumninatablecanbefoundbyaccessingtheview.

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

Showyoursqlstatementsandyourworkbelowbyprintingscreenforthelaststep.

7.Checkyouroutputagainstthefile.

Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutputorthefollowinglabsmaynotworkcorrectly!

Showyourworkbelowbyprintingscreen.

8.Informationaboutatable'sdefinitiontoatablespacecanbefoundbyaccessingtheview.EnteranSQLstatementtoselectthecolumnsTABNAME,TBSPACE,andINDEX_TBSPACEfromforalltableswiththeTABSCHEMAof‘student’,orderthedatabyTABNAME,andpipetheoutputtoafilenamed.

Showyoursqlstatementsandyourworkbelowbyprintingscreenforthelaststep.

9.Checkyouroutputagainstthefile.

Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!

Showyourworkbelowbyprintingscreen.

10.Grantselectprivilegeontablesthatareownedbystudenttopublicbyexecutingascriptfile,grants（anoperatingsystemcommandfile）forlocaladministration.

Showyourworkbelowbyprintingscreen.

Section2-CreateIndexes

1.CreateanindexcalledITEMontheITEMNOcolumninSTOCKtable.

Showyourworkbelowbyprintingscreen.

2.CreateauniqueindexcalledITEMNOontheITEMNOcolumninALBUMStable.

Showyourworkbelowbyprintingscreen.

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

IfUNIQUERULE="U",thenonlyuniquevaluesareallowed.

IfUNIQUERULE="D",thenduplicatevaluesareallowed.

IfUNIQUERULE="P",thenitisaPrimarykey.

Showyourworkandyournewindexesbelowbyprintingscreen.

4.Usetheselectyouexecutedinthepreviousstepandroutetheoutputto.Checkyourresultsagainst.

Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!

Showyourworkbelowbyprintingscreen.

Section3-CreateViews

1.Createaviewcalledmusicthatwillselecttitle,classification,namefromalbums,artistswhere=.

Showyourworkbelowbyprintingscreen.

2.Createanotherviewcalledinventorybyexecutingthescriptfile,crview.

Showyourworkbelowbyprintingscreen.

3.Informationaboutviewscanbefoundintheandviews.TablesandviewsforthecurrentusercanbeshownwithaLISTTABLESstatement.ATYPEofVisaview.

Issueoneofthefollowingstatementsandverifyyourviewsarelisted.

•LISTTABLESstatementOR

•SelectcolumnsTABSCHEMA,TABNAME,andTYPEfromtheviewwithaTABSCHEMAofstudentOR

•SelectcolumnsVIEWSCHEMA,VIEWNAMEfromwithDEFINERofstudent

Showyourworkbelowbyprintingscreen.

Section4-CreateAlias

1.CreateanaliascalledsingersfortheARTISTStable,andanaliascalledemptystockfortheREORDERtable.

Showyourworkbelowbyprintingscreen.

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

•LISTTABLESstatementOR

•SelectcolumnsTABNAMEandTYPEfromtheviewwithaTABSCHEMAofstudent.

Showyourworkbelowbyprintingscreen.

Section5-AddReferentialIntegrity

1.AltertheALBUMStableanddefinereferentialintegrityrelationshipsithaswithothertables.

•Addaprimarykeyontheitemnocolumn

•AddaforeignkeycalledfkartnoontheartnocolumnwhichreferencesaprimarykeyintheARTISTStable

•ThedeleterulebetweenARTISTSandALBUMSshouldbedeletecascade

Showyourworkbelowbyprintingscreen.

2.NowaltertheSTOCKtabletodefineitsreferentialintegrityrelationships,byexecutingthescriptfile,crri.

Showyourworkbelowbyprintingscreen.

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

Showyourworkbelowbyprintingscreen.

4.Runaselecttoretrievethefirst18charactersofTABNAME,andthePARENTSandCHILDRENcolumnsfromwheretheTABSCHEMAisequaltoyouruserid.OrdertheresultsbyTABNAMEanddirectyouroutputtoafilenamed.TheSQLcanbefoundinafilenamed.

Checkyouroutputagainstthefile.

Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!

Showyourworkbelowbyprintingscreen.

Section6-AddCheckConstraints

1.AltertheSTOCKtableandaddacheckconstrainttoit.Thecheckconstraintshouldhavethefollowingcharacteristics.

•Itshouldbenamedcctype.

•Thebusinessruleitshouldenforceistoonlyallowthevaluesof'D','C',or'R'inthetypecolumn.

Makesurethealphanumericliteralsaretypedinuppercaseletters.

Showyourworkbelowbyprintingscreen.

2.Informationaboutcheckconstraintscanbefoundbyaccessingthe,,,andviews.IssueSQLto:

SelectCONSTNAME,TABNAME,COLNAMEfromtheview.

SelectCONSTNAME,TABNAME,TYPEfromtheview.

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

Showyourworkbelowbyprintingscreen.

3.Tocheckyourwork,runaselectstatementthatselectsthefirst100charactersoftheTEXTcolumnfromwhereCONSTNAME='CCTYPE',anddirectyouroutputtoafilenamed.TheSQLcanbefoundinafilenamed.Checkyouroutputagainstthefile.

Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!

Showyourworkbelowbyprintingscreen.

Section7-CreateaTrigger

1.Createatriggerwhichhasthefollowingcharacteristics.

•Itshouldbenamedreorder

•ItshouldfireafteranupdateoftheqtycolumnontheSTOCKtable,ifthenewvalueofqtyis<=5

•Newshouldbereferencedasn

•ThetriggeredactionshouldinsertthevaluesandcurrenttimestampintotheREORDERtable

•Foreachrowmodedb2sql

Showyourworkbelowbyprintingscreen.

2.Informationabouttriggerscanbefoundbyaccessingtheandviews.

EnterinteractiveSQLtoresearchtheREORDERtrigger:

SelectTRIGNAME,TABNAME,andTRIGEVENTcolumnsfromtheview.

TRIGEVENTdescribestheeventthatfiresthetriggerasIforinsert,Dfordelete,orUforupdate.

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

Showyourworkbelowbyprintingscreen.

Section8-OPTIONAL-WorkingwithXML

1.IftheSAMPLEdatabasehasnotyetbeencreate,createitnow.

Showyourworkbelowbyprintingscreen.

2.ConnecttotheSAMPLEdatabase.

Showyourworkbelowbyprintingscreen.

3.UseXQUERYandthedb2-fn:

xmlcolumnfunctiontoretrievealloftheXMLdocumentsfromthecustomertable’sinfocolumn.

Showyourworkbelowbyprintingscreen.