DB2实验报告2.docx

DB2实验报告2.docx

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

DB2实验报告2

吉林大学

DB2实验报告

班级：

姓名：

学号：

姓名

学号

实验项目

2.Creatingdatabasesanddataplacement

实验性质

□演示性实验□验证性实验

操作性实验□综合性实验

实验地点

机器编号

指导教师

实验时间

年月日时分

一、实验综述

1.实验目的及要求

Duringthislab,youwillcreateyourdatabaseandyourtablespaces.Alsointhislab,youwillselectinformationontablespacesfromthesystemcatalog（SYSCAT）views.

Attheendofthelab,studentsshouldbeableto:

•Createadatabase

•Createatablespace

•Executeascriptfiletocreatemultipletablespaces

•AccesstheSYSCATviewscontainingtablespaceinformation

•Listtablespaceinformation

•Listcontainerinformation

2.实验设备、软件

PC, windows XPProfessional, DB2 9 Express-c

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

Section1-CreatingtheDatabase

1.CreatingadatabasecanbedonewiththeDB2commandcreatedatabase.UsetheonlinehelpfacilitytodisplaytheDB2commandsyntaxforcreatedatabase.

Showyourworkbelowbyprintingscreen.

2.Whatinformationcanyouspecifyonthecreatedatabasecommand?

Thenameofthedatabase,thelocationofthedatabase,analiasname,thecodesetandterritoryforstoringthedata,acollatingsequence,adefaultextentsize,automaticstorage,andtablespaceinformationcanbespecifiedonthecreatedatabasecommand.

3.Basedonwhatyoulearnedinlecture,whattablespacesarecreatedwhenadatabaseiscreated?

SYSCATSPACE,USERSPACE1,andTEMPSPACE1

Dependingonwhatyouspecifywhenyoucreatethedatabase,theremaybeafourthtablespacecreatedwithanameofSYSTOOLSPACE.ThisiscreatedifyouspecifyWithAutomaticMaintenanceinsteadofStandardwhenyoucreatethedatabase.

4.Whattypeoftablespace,SMSorDMS,willyourdatabaseusebydefaultforthesedefaulttablespaces?

DMStablespacesforSYSCATSPACEandUSERSPACE1,andSMSforTEMPSPACE1.NotethatinDB2UDBV8.2andearlierthedefaulttablespacesareSMS.

5.BeforewecreateourMUSICDBdatabase,rememberwhatyoulearnedinlectureaboutwhatsomeofthedefaultswillbe.

•Whatisthedefaultpaththatthedatabasewillbecreatedon?

•Whatwillbethedefaulttablespacetype（SMSorDMS）forthetablespacesthatwillbecreatedtohouseUserTables,CatalogTables,andTemporaryTables?

•WhatarethedefaultExtentandPrefetchsizes?

•WhatarethedefaultTerritory,CodeSetandCollatingSequencevalues?

•ThedatabasewillbecreatedontheC:

\DB2pathbydefault.

•ThedefaulttablespacetypeforUserTablesandCatalogTablesisDMS,andforTemporaryTablesisSMS.

•ThedefaultExtentandPrefetchsizesare324KBpageseach.

•Thedefault

6.CreateyourdatabasewithaDatabasenameofMUSICDBusingthedefaultsettings.Itwilltakeafewminutestocreatethedatabase.

Showyourworkbelowbyprintingscreenforthelaststep.

7.YoushouldnowhaveyourMUSICDBdatabasecreated.TheSystemDatabaseDirectorycontainsanentryforalldatabasesknownbythisinstance.ChecktheSystemDatabaseDirectoryforanentryfortheMUSICDBdatabasebyissuingthelistdbdirectorycommand.

Showyourworkbelowbyprintingscreen.

8.Asthereisalotofinformationreturnedfromtheselectstatementandthedatabasemanagerconfiguration,itmightbehelpfultostoretheinformationinafilecalledmyouttobeabletoexamineitstepbystep.Executethescriptagainandsavetheoutputinafilenamedmyout.

Showyourworkbelowbyprintingscreen.

8.WhatistheDatabasealiasnameandwherediditcomefrom?

TheDatabasealiasnameisYL1016.Itdefaultedtothedatabasenamesincenoaliaswasspecifiedwhenthedatabasewascreated.

9.WhatdoesaDirectoryentrytypeofIndirectmean?

ADirectoryentrytypeofIndirectmeansthedatabaseislocatedonthissystem,andthatthelocaldatabasedirectorycanbefoundhere.Youwillseethisifyouissuethelistdatabasedirectorycommandfromyourtelnetwindow.

10.WhatdoesaDirectoryentrytypeofRemotemean?

ADirectoryentrytypeofRemotemeansthedatabaseislocatedonanothersystem.YouwillseethisifyouissuethelistdatabasedirectorycommandfromtheWindowsclient.

11.It'stimetoconnecttoyourMUSICDBdatabase.Checkyourcurrentconnectionstatewiththegetconnectionstatecommand.

12.Whatistheconnectionstate?

Showyourworkbelowbyprintingscreen.

TheconnectionstateisConnectableandUnconnected.

13.ConnecttoyourMUSICDBdatabase.

14.DidyougetconnectedtoyourMUSICDBdatabase?

Showyourworkbelowbyprintingscreen.

15.Checkyourconnectionstateagain.Whatdoesitshow?

Showyourworkbelowbyprintingscreen.

16.EverydatabasehasitsownDatabaseConfigurationfilethatcontainsinformationaboutthedatabaseandtuningparameters.LookattheDatabaseConfigurationfileforyourMUSICDBdatabase.

Showyourworkbelowbyprintingscreen.

17.Findthedefaultvaluesfortwospecificconfigurationparametersforyourdatabase,LOCKLISTandMAXLOCKS.Sincetherearealargenumberofconfigurationparameters,wecanusegreptofindthespecificoneswewantwhenusingalocalconnection（theequivalentforDB2onWindowsis:

...|find/i“lock”）—theoption“i”meanscaseinsensitive.

Showyourworkbelowbyprintingscreen.

18.Valuesofsomeoftheparameterscanbechanged.Updatethefollowingparametersandspecifythevaluesshown.

•Changemaxlocksto20

•Changenum_freqvaluesto12

Showyourworkbelowbyprintingscreen.

19.Whendothesedatabaseconfigurationfilechangestakeeffect?

Fortheseconfigurationparameters,thechangetakeseffectimmediately.

NotethatwhenyouchangedthevalueforMAXLOCKS,thedefaultvalueforLOCKLISTisalsochanged—fromautomatictoanappropriatemanualvalue.

20.ChecktoseeiftheCurrentandDelayedValuesarethesameformaxlocksandnum_freqvalues.

Showyourworkbelowbyprintingscreen.

21.RepeatStep#17above,andrecordherethenewvaluesofLOCKLISTandMAXLOCKS.

22.Somedefaulttablespaceswerecreatedduringcreationofthedatabase.Listthetablespaceinformation.

Showyourworkbelowbyprintingscreen.

23.WhatarethetablespacenamesandwhatIDnumberisassociatedwiththetablespace?

SYSCATSPACE—ID0

TEMPSPACE1—ID1

USERSPACE1—ID2

SYSTOOLSPACE—ID3

Sinceyouqueriedthedatabasesizeinfo,thefourthtablespacenamedSYSTOOLSPACEwascreatedforyou.

TherearefourcasesthatSYSTOOLSPACEwillbeautomaticallycreatedonanactivedatabasestartingwithV8.2:

1.DBSummaryViewofthedatabaseisdisplayedintheControlCenter,orsimilarinformationisdisplayedfromthecommandlinebyissuing:

2.Createadatabasewithautomaticmaintenance.

3.Turnonautomaticmaintenanceforastandarddatabase（intheDBCFGfile）.

4.Forastandarddatabasewithoutautomaticmaintenance,whichhasnotbeenconnectedtothroughtheControlCenter,hmon（thehealthmonitor）willcreateonewhenitstartsevaluatinghealthindicators（bydefaultevery2hrs）.db2"CALLGET_DBSIZE_INFO（?

?

?

-1）"

Thus,eventually,aV9.1databasewillhaveaminimumoffourtablespaces.Theautomaticstatisticscollectionandreorganizationfeatures—availablestartingwithDB2UDBV8.2—storeworkingdataintablesinyourdatabase.ThesetablesarecreatedintheSYSTOOLSPACEtablespace.TheSYSTOOLSPACEtablespaceiscreatedautomaticallywithdefaultoptions.Storagerequirementsforthesetablesareproportionaltothenumberoftablesinthedatabaseandshouldbecalculatedasapproximately1KBpertable.Ifthisisasignificantsizeforyourdatabase,youmaywanttodropandre-createthetablespaceyourselfandallocatestorageappropriately.Theautomaticmaintenanceandhealthmonitortablesinthetablespaceareautomaticallyre-created.Anyhistorycapturedinthosetablesislostwhenthetablespaceisdropped.

24.Tablespacecontainerinformationcanbedisplayedwiththelisttablespacecontainerscommand.UsetheHelpfacilitytoshowtheDB2commandsyntax.

Showyourworkbelowbyprintingscreen.

25.ShowthecontainerinformationfortablespaceID0.Whattypeofcontaineristhisandwhereisitlocated?

Showyourworkbelowbyprintingscreen.

26.Listthenamesofthesystemcatalogtables.Whatarethesetables?

Showyourworkbelowbyprintingscreen.

27.Getmoredetailedinformationforthetablespacesandindicatewhichtablespacesaresettoautomaticsizeincrease.

Showyourworkbelowbyprintingscreen.

28.Verifywhichdefaultpathcontainersareassociatedwiththetemporarytablespaceandthedefaultusertablespace.

Showyourworkbelowbyprintingscreen.

29.RetrievedetailedcontainerinformationfromtheCatalogtables.Whatisthecontainertypesforeachcontainer?

Showyourworkbelowbyprintingscreen.

Section2-CreatingTableSpaces

1.Createyourfirsttablespace.Itshouldhavethefollowingcharacteristics:

•TablespacenameisDMS01

•TableSpaceTypeisRegular

•BufferPoolshouldbeIBMDEFAULTBP（whichisalsothedefault）

•TableSpacemanagementisDMS（alsocalledhighperformance）

•Containersizeshouldbe1006pageswith4KBpages

•ContainershouldbeaFile

•ContainerpathandfilenameshouldbeC:

\dms\dms01（Windows）

•Tablespaceextentsizeandprefetchsizeshouldbe4

Showyourworkbelowbyprintingscreenforthelaststep.

2.Verifyyournewtablespace（DMS01）bylistingtablespaces.

Showyourworkbelowbyprintingscreen.

3.OnyourWindowsDatabaseServer,ascriptfilenamedcrtblspcontainsSQLstatementstocreateyouradditionaltablespaces.

4.Executethescriptfiletocreateyourremainingtablespaces.

Makesureyouhavethefollowingoptionssetbeforeexecutingthescript:

•Autocommitshouldbeenable