Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Move DB2 datafiles to different file system 1

Status
Not open for further replies.
SantaMufasa

SantaMufasa

Technical User
Jul 17, 2003
12,588
US
In Oracle, if I wanted to move datafiles from their current locations to different filesystem locations, I would:

1) Shutdown the database instance
2) "mv" the datafile(s) to new location(s)
3) "startup mount" the database
4) "ALTER DATABASE
RENAME DATAFILE "<file-1 old location>" to "<file-1 new location>"...
5) "ALTER DATABASE OPEN"

What would be the counterpart steps in DB2?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Sort by date Sort by votes
Does the fact that no one has responded to my enquiry after several days mean:

A) DB2 does not support database file relocation,
B) DB2 might support relocation, but how to make that happen is beyond anyone's experience set?

I mean no disrespect by the question...I just need to know how I should proceed on this since I was unable to find references in this topic in DB2 references.

Looking forward to your guidance,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Santa,

I would guess b) is the most likely reason (it's certainly my reason), although it could be a further option:

c) It's holiday time and everybody is still on the beach [sunshine]

Marc

 
Upvote 0 Downvote
That's right...I forgot that in August, Europe plays, while Yanks continue to work. <grin>

I'll be patient until folks return home.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Nah.. you talk about "datafiles". Is 1 "datafile" in your perception 1 table (includeing indexes)?
DB2 takes another approach. DB2 (LUW) talks about tablespaces which can contain 1 to many tables. A tablespace can occupy 1 to may disks. Indexes can be located in the same tablespace as the actual table, or have a separate, dedicated, one.

So your initial question only provokes more questions for a DB2-guy/girl.

what does he want?:
- relocate a tablespace to anther disk
- take 1 table out of tablespace1 and store it in tablespace2
- recreate a tablespace using different technique (SMS/DMS/raw-disk)
- add another disk (=container) to a existing tablespace
- etc etc

and yes: we can do that all. just not describe it all in 3 lines usable in all possible configurations.
 
Upvote 0 Downvote
Sorry for my delay in responding. (Other life/work issues took precedence for several days, and now this issue is back on the "front burner".
Truusvlugindewind said:
what does he want?:
- relocate a tablespace to anther disk...
Click to expand...
Yes, I believe that's what I need (...but I could be wrong).


The explicit commands that located tablespaces (and other files) in the "old" filesystem that is going away are:
Code: 
create database <db_name> on '/usr/data2/db2/<db_name>' collate using identity

create temporary tablespace tempspace8k1 pagesize 8192 managed by system using ('/usr/data2/db2/<db_name>/db2v91/NODE0000/SQL00001/SQLT8K00.0') bufferpool bp8k \ 
 extentsize 64 prefetchsize 64

create tablespace userspace8k1 pagesize 8k managed by system using ('/usr/data2/db2/<db_name>/db2v91/NODE0000/SQL00001/USERSPACE8K1.0') bufferpool bp8k extentsize 64 prefetchsize 64
This resulted in these seven tablespaces (as documented by the following "LIST TABLESPACES SHOW DETAIL" command:
Code: 
db2 => LIST TABLESPACES SHOW DETAIL

           Tablespaces for Current Database

 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 196608
 Useable pages                        = 196604
 Used pages                           = 195996
 Free pages                           = 608
 High water mark (pages)              = 195996
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 475136
 Useable pages                        = 475104
 Used pages                           = 473248
 Free pages                           = 1856
 High water mark (pages)              = 473248
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 3
 Name                                 = TEMPSPACE8K1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 8192
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1

 Tablespace ID                        = 4
 Name                                 = USERSPACE8K1
 Type                                 = System managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 29
 Useable pages                        = 29
 Used pages                           = 29
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 8192
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1

 Tablespace ID                        = 5
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8192
 Useable pages                        = 8188
 Used pages                           = 280
 Free pages                           = 7908
 High water mark (pages)              = 280
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 64
 Number of containers                 = 1

 Tablespace ID                        = 6
 Name                                 = SYSTOOLSTMPSPACE
 Type                                 = System managed space
 Contents                             = User Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 64
 Number of containers                 = 1

The '/usr/data2/db2' ("old") file system is disappearing in favour of a ("new") file system named '/dbsrv/db2/data1'. The files that we must relocate from the "old" system to the "new" system are:
Code: 
<old_file_system>/<db_name>/db2v91/NODE0000/<db_name>/T0000000
    C0000000.CAT
<old_file_system>/<db_name>/db2v91/NODE0000/<db_name>/T0000001
    C0000000.TMP
        SQLTAG.NAM
<old_file_system>/<db_name>/db2v91/NODE0000/<db_name>/T0000002
    C0000000.LRG
<old_file_system>/<db_name>/db2v91/NODE0000/<db_name>/T0000005
    C0000000.LRG
<old_file_system>/<db_name>/db2v91/NODE0000/<db_name>/T0000006
    C0000000.UTM
        SQLTAG.NAM
<old_file_system>/<db_name>/db2v91/NODE0000/SQL00001
    SQLBP.1
    SQLBP.2
    SQLDBCON
    SQLDBCONF
    SQLINSLK
    SQLOGCTL.LFH
    SQLOGDIR
        S0000000.LOG
        ...
        S0000019.LOG
    SQLOGMIR.LFH
    SQLSGF.1
    SQLSGF.2
    SQLSPCS.1
    SQLSPCS.2
    SQLT8K00.0
        SQLTAG.NAM
    SQLTMPLK
    USERSPACE8K1.0
        SQL00002.DAT
        SQL00002.INX
        SQL00003.DAT
        SQL00003.INX
        SQLTAG.NAM
    db2event
        db2detaildeadlock
            00000000.evt
            db2event.ctl
    db2rhist.asc
    db2rhist.bak
<old_file_system>/<db_name>/db2v91/NODE0000/sqldbdir
    sqldbbak
    sqldbdir
    sqldbins
So our objective is to move all of the DB2 files that reside on the "old" file system to the "new" file system without disrupting any processing.

Does this provide enough information to advise me what to do to reach our file-relocation objectives?

I sincerely appreciate time you spend in formulating advice to me.





[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Your still talking about "DB2 files". Stop confusing me.

A tablespace contains 1 or more tables. You can move those tables from 1 tablespace to the other and drop the old tablespace when finisted

or

make a backup and use the "restore redirect" technique to rebuild you tablespace on antoher volume.

Some reading for the weekend :
 
Upvote 0 Downvote
First, Truusvlugindewind, I appreciate your spending time to help me in this endeavor.

Second,
Truusvlugindewind said:
Your still talking about "DB2 files". Stop confusing me.
Click to expand...
Truusvlugindewind, there is no benefit to you or to me in my confusing you. To try avoid confusion, I listed, in my previous post, the actual files about which I was speaking, which represent standard DB2-related files, whose purpose and usage is clearly familiar to DB2 DBA technicians.


Having been an Oracle DBA Instructor for Oracle University from 1988 through 1997 and as the senior principal in a firm that manages databases for companies worldwide, I assure you that I know that database engines, such as DB2, Oracle, et cetera, at the user-interface level, do not talk about files, per se: they deal with tables, indexes, and tablespaces.

But as far as Unix (or any other operating system) is concerned, it only sees file systems, directories/paths, and files, and the engine's data-dictionary definitions create appropriate linkages between the engine's logical objects (tables, indexes, et cetera) and their physical storage in files at the operating-system level. Your excellent record here in this DB2 forum indicates that you are extremely technically astute and can easily make that technical linkage/transition without, as you say, confusion.

I appreciate the links that you provided me, above. I have read through each of them. I also noticed in those links that DB2 does not have a built-in method for easily transitioning "files" (that support the data in the database) from one location to another, without spending inordinate amounts of time exporting and importing data to accomplish the transition.

Therefore, for future readers of this thread who are like-minded in our unwillingness to spend inordinate amounts of time implementing an otherwise-trivial housekeeping chore, you may wish to use the solution that we ended up using:
1) Shutdown the DB2 instance
2) Move ("mv" from the highest-level directory) all directories, sub-directories, and files from their "old" locations to their "new" locations.
3) Create an operating-system logical link to "fool" DB2 into thinking it is still using the "old" locations:
Code: 
ln -s <old high-level directory> <new high-level directory counterpart>
4) Restart the DB2 instance.
5) Continue normal processing.
Click to expand...
This entire process smoothly and successfully occurred in about 4.5 minutes.

[cheers]

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Oh, and yes, Truusvlugindewind, please accept a
star.gif
for your indulgence and helping me confirm that my solution was the quickest method to resolve my need.[2thumbsup]

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Yeah, databases is all about objects and unix is all about files. True. I like you solution. It will only work on a unix/linux machine though so this solution is more something for a unix-forum. Windows DB2 users.............eh ..... who cares: its their own fault, she should have choosen a decent O.S.[/color green] :)

By the way: when you want to manage your database like this you should consider Solaris 10. Just create an entry point for each 'object' and start playing with ZFS.

offtopic: a database is a bit like a sailing boat. A database is nothing but files but you may not call them a file. A sailing boat is full of ropes but don't dare to call it a rope. (at least in dutch. You had to buy the whole crew beer when you said "touw" on board).
 
Upvote 0 Downvote
Truus,At least you have confirmed that you're dutch. :)
As a former dutch DB2 user/DBA I've been waiting for this confirmation :)
Are you really a lady?


Ties Blom

 
Upvote 0 Downvote
Jazeker, kaaskop hier.
Sorry, I'm a guy. This is one of the 1st forums I've ever joined and in that period "never use your own name on the internet" was adviced (also by me to my children). So I made up a nickname. Crox knows who I really am, he introducted my to this forum. My nickname is expained im my "member profile".
[dutch]
Toen ik als tiener op zeilkamp zat moest je een rondje geven voor de hele bemanning als je "touwtje" zei aan boord. Het zijn vallen, stagen e.d. Dit kan ik niet vertalen.
[/dutch]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

P
  • Locked
  • Question
DB2 LUW SYSCATSPACE is full
Replies
0
Views
379
prashant_0701
P
tekpr00
  • Locked
  • Question
Adding storage to DB2 PureScale Cluster
Replies
0
Views
294
tekpr00
tekpr00
C
  • Question
STRDBG not bringing up options to set breakpoints
Replies
10
Views
255
chuckw0325
C
gdg1976
  • Locked
  • Question
db2 sql extract double rows (different in case sensitive) 1
Replies
6
Views
243
mikrom
mikrom
TitanQ
  • Locked
  • Question
Move datafiles in HADR environment
Replies
0
Views
80
TitanQ
TitanQ

Part and Inventory Search

Sponsor

Back
Top