changing directory for data file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gopi - 11 Jan 2006 21:35 GMT

when installing db2, it only asks for installation directory and does
not ask where it is going to store data files. how can i change the
default directory after installation.

Reply to this Message

Mark A - 12 Jan 2006 00:45 GMT

> when installing db2, it only asks for installation directory and does not
> ask where it is going to store data files. how can i change the default
> directory after installation.

You can specify the location of data files when you create databases. See
create database command.

Reply to this Message

Norm - 12 Jan 2006 15:30 GMT

If you are at fixpack 9 or above, you can use command db2relocatedb

Norm

> > when installing db2, it only asks for installation directory and does not
> > ask where it is going to store data files. how can i change the default
> > directory after installation.
>
> You can specify the location of data files when you create databases. See
> create database command.

Reply to this Message

gopi - 12 Jan 2006 20:49 GMT

>>when installing db2, it only asks for installation directory and does not
>>ask where it is going to store data files. how can i change the default
>>directory after installation.
>
> You can specify the location of data files when you create databases. See
> create database command.

thanks.
running on windows, i can specify drive like "create db tempdb on d:",
but when i try to give complete path "create db tempdb on
d:\db2\tempdb" it does not accept. how can i specify a subdiretory ?

Reply to this Message

Gert van der Kooij - 12 Jan 2006 16:39 GMT

> when installing db2, it only asks for installation directory and does
> not ask where it is going to store data files. how can i change the
> default directory after installation.

My guess is that you're on Windows.
If so all instance files are placed in the installation directory and
all databases are automatically located on the same drive.

Each instance has his own directory within the installation
directory. If you want them on a different location you can drop the
instance with the db2idrop command and recreate it with the db2icrt
command.
To create instance DB2TEST on d:\instancedata you can use the command
'db2icrt DB2TEST -p d:\instancedata'. It's also possible to change
the DB2INSTPROF variable but then you also need to drop and recreate
the administration instance.

If you want your databases automatically created on another drive you
need to change the instance configuration with the command
'db2 update dbm cfg using DFTDBPATH e:' or any other drive you want.

If you want your databases created on different drives you can
specify it on the create database command with the 'ON <drive>'
option.

Hope this helps.

Reply to this Message

gopi - 12 Jan 2006 20:57 GMT

>>when installing db2, it only asks for installation directory and does
>>not ask where it is going to store data files. how can i change the
[quoted text clipped - 22 lines]
>
> Hope this helps.

thanks. it was very helpful.
how can i put different databses in different user-defined
subdirectries. it seems 'ON' option only allows me to specify the drive.

Reply to this Message

Gert van der Kooij - 12 Jan 2006 23:33 GMT

> >>when installing db2, it only asks for installation directory and does
> >>not ask where it is going to store data files. how can i change the
[quoted text clipped - 26 lines]
> how can i put different databses in different user-defined
> subdirectries. it seems 'ON' option only allows me to specify the drive.

That's right, only a drive can be specified. DB2 will auomatically
put all databases belonging to instance DB2 in the \DB2\NODE0000
directory when running with single partition DB2 on the drive
specified.

Reply to this Message

Eugene F - 13 Jan 2006 00:31 GMT

I am just wondering if DB2 automatic storage management would comes
into play here
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/c0012278.htm.
I am not sure though when that was intorduced in DB2, anybody?

-Eugene

Reply to this Message

Gert van der Kooij - 13 Jan 2006 01:00 GMT

> I am just wondering if DB2 automatic storage management would comes
> into play here
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/c0012278.htm.
> I am not sure though when that was intorduced in DB2, anybody?
>
> -Eugene

If I understand it right you can put the tablespace containers in
some directory which is called the storagepath, but all other data
like the logfiles and the database history file is still on the
'old' location.

I tested it with the command:
db2 create db teststor on d:\xx dbpath on d:

It created the the directory d:\DB2\NODE0000\SQL00007 with the
following files:
<DIR> .
<DIR> ..
<DIR> DB2EVENT
1.028 db2rhist.asc
1.028 db2rhist.bak
512 SQLBP.1
512 SQLBP.2
4.096 SQLDBCON
16.384 SQLDBCONF
9 SQLINSLK
24.576 SQLOGCTL.LFH
<DIR> SQLOGDIR
8.192 SQLOGMIR.LFH
135.168 SQLSGF.1
135.168 SQLSGF.2
196.608 SQLSPCS.1
196.608 SQLSPCS.2
0 SQLTMPLK

In the d:\xx directory these files/directories were created:
D:\xx\DB2
D:\xx\DB2\NODE0000
D:\xx\DB2\SQLCRT.FLG
D:\xx\DB2\NODE0000\SQLCRT.FLG
D:\xx\DB2\NODE0000\TESTSTOR
D:\xx\DB2\NODE0000\TESTSTOR\SQLCRT.FLG
D:\xx\DB2\NODE0000\TESTSTOR\T0000000
D:\xx\DB2\NODE0000\TESTSTOR\T0000001
D:\xx\DB2\NODE0000\TESTSTOR\T0000002
D:\xx\DB2\NODE0000\TESTSTOR\T0000000\C0000000.CAT
D:\xx\DB2\NODE0000\TESTSTOR\T0000000\SQLCRT.FLG
D:\xx\DB2\NODE0000\TESTSTOR\T0000001\C0000000.TMP
D:\xx\DB2\NODE0000\TESTSTOR\T0000001\SQLCRT.FLG
D:\xx\DB2\NODE0000\TESTSTOR\T0000001\C0000000.TMP\SQLTAG.NAM
D:\xx\DB2\NODE0000\TESTSTOR\T0000002\C0000000.USR
D:\xx\DB2\NODE0000\TESTSTOR\T0000002\SQLCRT.FLG

So it might help to store data on a different place but this could
also be done by specifying tablespace locations when creating the
database. The files which remain in d:\DB2\NODE0000 are the same in
both situations.

Reply to this Message

Phil Sherman - 13 Jan 2006 14:10 GMT

Have you considered changing operating systems for then database server?
Linux allows specifying the directory when creating a database.

Phil Sherman

>>> when installing db2, it only asks for installation directory and does
>>> not ask where it is going to store data files. how can i change the
[quoted text clipped - 24 lines]
> how can i put different databses in different user-defined
> subdirectries. it seems 'ON' option only allows me to specify the drive.

Reply to this Message