Scribd
Upload
505 views

DB2 Sample Database - Modifying Storage Path - Db2talk

The document discusses modifying the storage path for a DB2 sample database. It describes a two-step process: 1) using db2sampl to create the sample database with a default storage path, then 2) using db2look to extract the DDL and modify the storage path clause, drop the original database, and recreate it with the modified DDL to set a custom storage path. An example is provided that changes the storage path and demonstrates that new tablespaces are then stored in the custom location.

Uploaded by

prakash_6849
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
505 views

DB2 Sample Database - Modifying Storage Path - Db2talk

The document discusses modifying the storage path for a DB2 sample database. It describes a two-step process: 1) using db2sampl to create the sample database with a default storage path, then 2) using db2look to extract the DDL and modify the storage path clause, drop the original database, and recreate it with the modified DDL to set a custom storage path. An example is provided that changes the storage path and demonstrates that new tablespaces are then stored in the custom location.

Uploaded by

prakash_6849
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

db2talk

DB2Linux,UnixandWindowsAdministrationandDevelopment
DECEMBER28,2015BYPAVANKRISTIPATI

DB2SampleDatabaseModifyingstoragepath
Inanearlierblogpost,welookedathowtocreateasampledatabaseinDB2LUW.Wealsolookedatchangingthedefaultstosuit
ourneedschangedthedefaultdatabasenamefromSAMPLEtowhateverwewantedandalsochangedthedatabasepath
(DBPATH).
Hereisthelinktothatblogpost:hps://db2talk.com/2015/09/08/creatingadb2luwlinuxunixandwindowssandboxdatabase/
(hps://db2talk.com/2015/09/08/creatingadb2luwlinuxunixandwindowssandboxdatabase/)
Thedatabasepath(DBPATHinthecreatedatabasecommand)isthelocationwhereahierarchicaldirectorystructureiscreated.
Thestructureholdsthefollowinglesneededfortheoperationofthedatabase:
Buerpoolinformation
Tablespaceinformation
Storagepathinformation
Databasecongurationinformation
Historyleinformationregardingbackups,restores,loadingoftables,reorganizationoftables,alteringoftablespaces,and
otherdatabasechanges
Logcontrolleswithinformationaboutactivelogs
But,whatifwewanttochangethestoragepath?i.e.,thepathwheredatabasecontents(tablespacecontainers)arestored.By

But,whatifwewanttochangethestoragepath?i.e.,thepathwheredatabasecontents(tablespacecontainers)arestored.By
default,DBPATHandthedatabasestoragepathdefaulttothesamevalue.
Whencreatingthesampledatabase,thereisnodirectwaytomodifydatabasestoragepath.So,wewilltakea2stepapproach.
Step1:Wewillcreatethesampledatabaseaswedidintheabovementionedblogpost.

$db2sampldbpath/db2home/sample

Creatingdatabase"SAMPLE"onpath"/db2home/sample"...
Connectingtodatabase"SAMPLE"...
Creatingtablesanddatainschema"DB2INST1"...
CreatingtableswithXMLcolumnsandXMLdatainschema"DB2INST1"...

'db2sampl'processingcomplete.

Step2:Wewillnowuseaniftyfeatureindb2looktoextracttheDDLtocreatethedatabaseandthenmodifythestoragepath
clause,dropthesampledatabasewecreatedintherststepandthencreatethedatabaseagainwithmodiedDDLthathas
updatedstoragepath.
Belowisthecommand(anditsoutput)toextractDDLtocreatedatabasesample.


$db2lookdsamplecreatedb
Nouseridwasspecified,db2looktriestouseEnvironmentvariableUSER
USERis:DB2INST1
ThisCLPfilewascreatedusingDB2LOOKVersion"10.5"
Timestamp:Thu24Dec201501:04:40PMEST
DatabaseName:SAMPLE
DatabaseManagerVersion:DB2/LINUXX8664Version10.5.6
DatabaseCodepage:1208
DatabaseCollatingSequenceis:IDENTITY
Alternatecollatingsequence(alt_collate):null
varchar2compatibility(varchar2_compat):OFF
Bindingpackageautomatically...
Bindissuccessful
Bindingpackageautomatically...
Bindissuccessful

GenerateCREATEDATABASEcommand

CREATEDATABASESAMPLE
AUTOMATICSTORAGEYES
ON'/db2home/sample'
DBPATHON'/db2home/sample/'
USINGCODESETUTF8TERRITORYUS
COLLATEUSINGIDENTITY
PAGESIZE8192
DFT_EXTENT_SZ32
CATALOGTABLESPACEMANAGEDBYAUTOMATICSTORAGE
EXTENTSIZE4
NOFILESYSTEMCACHING
AUTORESIZEYES
INITIALSIZE32M
MAXSIZENONE
TEMPORARYTABLESPACEMANAGEDBYAUTOMATICSTORAGE

TEMPORARYTABLESPACEMANAGEDBYAUTOMATICSTORAGE
EXTENTSIZE32
FILESYSTEMCACHING
USERTABLESPACEMANAGEDBYAUTOMATICSTORAGE
EXTENTSIZE32
NOFILESYSTEMCACHING
AUTORESIZEYES
INITIALSIZE32M
MAXSIZENONE
;
CONNECTTOSAMPLE;
COMMITWORK;
CONNECTRESET;
TERMINATE;

Wewillnowmodifytheabovehighlightedareato/db2inst1/db/data,dropthedatabaseandthenrunthemodiedDDLto
createdatabase.


CREATEDATABASESAMPLEAUTOMATICSTORAGEYES
ON'/db2inst1/db/data/'
DBPATHON'/db2home/sample/'
USINGCODESETUTF8TERRITORYUS
COLLATEUSINGIDENTITYPAGESIZE8192
DFT_EXTENT_SZ32
CATALOGTABLESPACEMANAGEDBYAUTOMATICSTORAGE
EXTENTSIZE4NOFILESYSTEMCACHING
AUTORESIZEYESINITIALSIZE32MMAXSIZENONE
TEMPORARYTABLESPACEMANAGEDBYAUTOMATICSTORAGE
EXTENTSIZE32FILESYSTEMCACHING
USERTABLESPACEMANAGEDBYAUTOMATICSTORAGE
EXTENTSIZE32NOFILESYSTEMCACHING
AUTORESIZEYESINITIALSIZE32MMAXSIZENONE
DB20000ITheCREATEDATABASEcommandcompletedsuccessfully.

Now,whenwecreateatablespacethathasautomaticstorage,itsstoragepathwouldbeat/db2inst1/db/data.
Hereisanexample:


$db2"createtablespacedata"
DB20000ITheSQLcommandcompletedsuccessfully.

$db2listtablespaces
TablespacesforCurrentDatabase
............

TablespaceID=3
Name=DATA
Type=Databasemanagedspace
Contents=Allpermanentdata.Largetablespace.
State=0x0000
Detailedexplanation:
Normal
.............
$db2listtablespacecontainersfor3

TablespaceContainersforTablespace3

ContainerID=0
Name=/db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000003/C0000000.LRG
Type=File

Conclusion:
Inthisblogpost,wehavelookedathowwecanmodifytheDatabaseStoragePathforasampledatabasethatwascreatedearlier.
ThisentrywaspostedinDB210.5.Bookmarkthepermalink.

ThisentrywaspostedinDB210.5.Bookmarkthepermalink.
BlogatWordPress.com.|TheMistyLakeTheme.

You might also like