The Default Database


The CREATE DATABASE command allows a user to specify the drive or directory on which to create the database, depending on the operating system. The Linux and UNIX operating systems allow a user to specify the directory in which to create the database. On the Windows operating system, a user can specify only the drive on which to create the database. If no drive or directory is specified, the database will be created on the path specified by the DFTDBPATH instance (database manager) configuration parameter.

On the drive or directory specified in the CREATE DATABASE command, DB2 will create a series of subdirectories, as shown in Figure 1.2. The first subdirectory is named after the instance owner for the instance in which the database was created. Under this subdirectory, DB2 will create a directory that indicates which database partition the database was created in. For a nonpartitioned database, the directory will be NODE0000. For a partitioned database, the directory will be named NODE xxxx where xxxx will be the database instance's four-digit partition number, as designated in the db2nodes.cfg file. For example, for partition number 43, this directory would be NODE0043.

Figure 1.2. The default database structure.

graphics/01fig02.gif

NOTE

In Windows, instances do not really have an instance owner, but the name of the instance, i.e., DB2, will be used in place of the instance owner's ID.


Because more than one database can be created on the same drive or directory, each database must have its own unique subdirectory. Under the NODE xxxx directory, there will be an SQL xxxxx directory for every database that was created on the drive/directory. For example, there are two databases, MYDB and SAMPLE, that were both created on the C: drive on Windows, so there will be two directories: SQL00001 and SQL00002.

To determine under which directory the database was created, enter the command LIST DATABASE DIRECTORY ON C:. This will produce output like the following:

  Database 1 entry:   Database alias                           = MYDB   Database name                            = MYDB   Database directory                       = SQL00002   Database release level                   = a.00   Comment   Directory entry type                     = Home   Catalog database partition number        = 0   Database partition number                = 0   Database 2 entry:   Database alias                           = SAMPLE   Database name                            = SAMPLE   Database directory                       = SQL00001   Database release level                   = a.00   Comment                                  =   Directory entry type                     = Home   Catalog database partition number        = 0   Database partition number                = 0  

In the example above, the database SAMPLE would have been created in the SQL00001 directory, and the database MYDB would have been created in the SQL00002 directory under the NODE xxxx directory.

Under the database's SQL0000 x directory, DB2 will create one directory for each of the three default table spaces, unless the table spaces were defined to use different containers in the CREATE DATABASE command.

By default, the system catalog table space will use the directory SQLT0000.0, the system temporary table space will use the directory SQLT0001.0, and the default user table space (USERSPACE1) will use the directory SQLT0002.0.

There is also a subdirectory named SQLOGDIR to hold the database log files. This location can be changed once the database has been created.

Given the following command, execute in the instance named db2inst1 .

  create database sample on /database  

On the Linux or UNIX server where database partition 0 is defined, the following directory structures are created:

  /database/db2inst1/NODE0000/sqldbdir   /database/db2inst1/NODE0000/SQL00001  

On the server where database partition 1 is defined, the following directory structures are created:

  /database/db2inst1/NODE0001/sqldbdir   /database/db2inst1/NODE0001/SQL00001  

These directories would be created as illustrated in Figure 1.3.

Figure 1.3. Directory structure for multi-partitioned database.

graphics/01fig03.gif

If a second database is created in the same instance (i.e., db2inst1) on the same path using the command:

  create database sample on /database  

the directory structure would then look like Figure 1.4.

Figure 1.4. Directory structure for two databases in a multi-partitioned database.

graphics/01fig04.gif

NOTE

The sqldbdir directory contains the database directory, i.e., a listing of all databases that are in the drive / path.




Advanced DBA Certification Guide and Reference for DB2 UDB v8 for Linux, Unix and Windows
Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows
ISBN: 0130463884
EAN: 2147483647
Year: 2003
Pages: 121

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net