Moving DB2 data file to different location in windows
Hi All,
This is my first blog in SCN. I want to share some information about DB2 database. Every one facing a situation when the hard disk is getting full when the DB is growing up. In this situation they need to migrate the system to other hardware or else they need to add some addition hardware to system.
Here i am explaining the concept about how to move the data file of DB2 database to some other location or drive in windows environment.
Before doing any kind of operation in your system kindly take a complete offline backup of your DB and OS.
Step 1 : Stop your SAP server.
Step 2 : Login to system using DB2<SID> user.
Step 3 : Stop the DB and start ones again using the below command .
db2stop and db2start.
Step 4 : Move to the kernel location and run the below command for generating a control file.
brdb6brt -s <db2sid> -bm RETRIEVE
Ones you will execute the above command it will generate a <SID>_NODE0000.scr file.
Step 5 : Move that file to some other location and rename this file to .clp format.
Now you can able to modify to that file. Open that file to any editor and modify the storage_path parameter in file
Here i am giving a example of the parameter
STORAGE_PATH=<CURRENT STORAGE PATH>, <NEW STORAGE PATH>
STORAGE_PATH=D:\DB2\<SID>\SAPDATA1,F:\DB2\<SID>\SAPDATA1
Step 6 : Ones you have finish the editing in .CLP file then rename it one again to .SCR file
Before running the relocation command create the same directory structure in the newly created drive for moving the data file.
and copy the data file form the old location to new location.
Step 7 : Now move to location where you kept the .SCR file and run the relocation command.
db2relocatedb -f <DB2 SID>_NODE0000.scr
Step 8 : Rename the old sap data file to some other name.
Step 8 : Now start SAP and login.
Step 9 : Use the t-code DBACOCKPIT and check the data file location.
That’s it you have to do only.
Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks,
Pradeep Kumar
Hi,
Good to know !!! .
we simply add container in new file system if some file system full or in case of Auto storage , we can add File system and add storage path and rebalance it .
Is this special case where we need to do above procedure.
Thanks
Manas Behra
Hi Manas,
This is the case when your drive is full and no space for adding extra container to data file.
In that situation we have to follow this activity by adding addition hard drive or adding space through SAN.
Thanks,
Pradeep Kumar
Hi Pradeep
Its really very good information.
what about the unix plateform . have you ever tested this on unix.
small doubt..if we have 6 sapdata file in a drive and we want to move only 3 out of 6.
what shall we do. is the same process will work by modifying path for only 3 sapdata files
Thanks
Sadiq
Hi Sadiq,
Thanks for your comment.
We can move any no of data file to other location. We need to specify in .clp file.
Thanks,
Pradeep
Hi, Predeep,
Looks very simple steps and nice document. But i face a error while moving the data file other location.
I have my SAP server in windows and Db2 database, due to space constraint i wanted to move sapdata3 & sapdata4 to other drive. As per steps i generated the the scr file, changed the file format to .clp, added new location, changed again to .scr file format and tried to execute the relocate command. But i finding error as mentioned below.
command execute : db2relocatedb -f h:\SCRFILE\SMM_NODE0000.scr
DBT1020N The configuration file contains an invalid entry at line "1". Reason c
ode = "11".
~Viswanathan G