While creating the database with the Create Database Wizard, there will be a summary report of the operation when DB2 is about to create your database.

A Show command button lets you preview the create database command. You can click on Finish to execute the command now, or do it later on.

You can copy this command and paste it into a script, so you can create the database manually from the command line, if you wish.

With automatic storage, DB2 simplifies database file maintenance. DB2 uses the storage path you put in the default directory and manages automatically all table space containers for the database. You may add storage paths; DB2 will create, extend, or add containers in these storage paths. You don't have to worry about container file naming convention or extending containers to allow for free space.

In DB2 Express C version and Enterprise Server Edition (ESE), automatic storage is the default option present in the Control Center's create database dialog. Once a database is created with automatic storage, it cannot be reset after creation. Note the command used for an automatic storage database. No table space definitions are required, since DB2 will create the three main table spaces, CATALOG, USER, and SYSTEM Temporary table spaces.

CREATE DATABASE NAV
AUTOMATIC STORAGE YES
ON '/data/db2'
DBPATH ON '/data/db2'
ALIAS NAV
USING CODESET UTF-8
TERRITORY CA
COLLATE USING SYSTEM
PAGESIZE 4096
WITH 'Aviation Geo Data';

All that is needed for maintenance is to check for filesystem availability. Fortunately, there are tools in DB2 to help you do this; we'll see this later with the health monitor.

Perhaps you have your own naming convention and prefer to manage containers yourself; in that case, automatic storage should be off. With the Control Center, you just have to select I want to manage my space manually, on the first screen. You can create a database using the command center with the AUTOMATIC STORAGE NO option or manually from the command line, using a script. Note the three required table spaces and their container definitions:

CREATE DATABASE NAV AUTOMATIC STORAGE NO ON '/data/db2' ALIAS NAV USING CODESET UTF-8 TERRITORY CA COLLATE USING SYSTEM PAGESIZE 8 K CATALOG TABLESPACE MANAGED BY DATABASE USING ( FILE '/data/db2/db2inst1/NODE0000/nav/catalog.dbf' 6400 ) AUTORESIZE YES MAXSIZE 500 M USER TABLESPACE MANAGED BY DATABASE USING ( FILE '/data/db2/db2inst1/NODE0000/nav/user.dbf' 6400 ) AUTORESIZE YES MAXSIZE 500 M TEMPORARY TABLESPACE MANAGED BY DATABASE USING (
FILE '/data/db2/db2inst1/NODE0000/nav/temp.dbf' 6400 ) AUTORESIZE YES MAXSIZE 500 M;

Adaptive self-tuning memory, introduced with v9.1, removes the burden on the DBA to tune memory utilization. It is enabled, by default, for single partition databases. This allows DB2 to automatically distribute memory allocation for buffer pools, lock lists, package cache, and sort memory. For example, when less memory is required from buffer pools, it can be freed from buffer pools to allow more space for sort memory.

When instance and database directory permissions are created by DB2, you should not modify any of those. With automatic storage, you would normally find the database files under the following directory hierarchy:

Let's explain the directory hierarchy we just saw earlier:

You can define your containers or storage path in DB2 to point to a symbolic link. The operating system will follow it through. In this example, I moved a container but left a symbolic link to point to the new container location.

Use this carefully in sandbox environments first. This can be useful to get you out of a disk-based issue with minimum impact.

For example:

[db2inst1@nodedb21 ~]$ db2 connect to nav Database Connection Information Database server = DB2/LINUXX8664 9.7.4 SQL authorization ID = DB2INST1 Local database alias = NAV [db2inst1@nodedb21 ~]$ db2 quiesce database immediate DB20000I The QUIESCE DATABASE command completed successfully.

On the Linux command line, go to another filesystem, to which the instance owner has write access (/data1, in our case):

[db2inst1@nodedb21 ~]$ cd /data1

Create a directory to move a container to:

[db2inst1@nodedb21 data1]$ mkdir -p db2/db2inst1/NODE0000/nav

Go to this directory and copy the original container there:

[db2inst1@nodedb21 data1]$ cd db2/db2inst1/NODE0000/nav [db2inst1@nodedb21 nav]$ cp /data/db2/db2inst1/NODE0000/nav/nav_tbls.dbf

Go back to the original directory and rename the original container:

[db2inst1@nodedb21 data1]$ cd /data/db2/db2inst1/NODE0000/nav/ [db2inst1@nodedb21 nav]$ mv nav_tbls.dbf nav_tbls.dbf.scrap

Create a link:

[db2inst1@nodedb21 nav]$ ln -s /data1/db2/db2inst1/NODE0000/nav/nav_tbls.dbf nav_tbls.dbf 

Confirm link is done:

[db2inst1@nodedb21 nav]$ ls -al nav_tbls.dbf lrwxrwxrwx 1 db2inst1 dba 45 2011-07-01 16:29 nav_tbls.dbf -> /data1/db2/db2inst1/NODE0000/nav/nav_tbls.dbf 

Bring the database back on line:

[db2inst1@nodedb21 ~]$ db2 unquiesce database; DB20000I The UNQUIESCE DATABASE command completed successfully. 

If you query a table in that table space, the data will be there.

Make sure you use this feature wisely, as part of a well thought-out plan; otherwise, this introduces another level of complexity. You may want to use automatic storage instead.

The default codeset is UTF-8, except when defined otherwise. This cannot be changed after creation.

The default territory is US, except when defined otherwise. This cannot be changed after creation. The combination of the codeset and territory or locale values must be valid.

The default setting is SYSTEM. Ensure this setting is compatible with codeset and territory. This cannot be changed after creation.

There are no equivalent commands to manipulate control files in DB2. In our example, you will find them in /data/db2/db2inst1/NODE0000/SQL00003/.

The following files reflect the equivalent of Oracle's control file information. These files should not be tampered with. db2rhist.asc and db2rhist.bak contain historic information about backups, restores, and other changes to the database. db2tschg.his contains a history of table space changes. For each log file, it contains information that helps to identify which table spaces are affected by the log file. This information is used for table space recovery. You can browse it with a text editor.

The log control files contain information about active logs. The files, SQLOGCTL.LFH.1 and its mirror copy, SQLOGCTL.LFH.1, are located, in our example, in /data/db2/db2inst1/NODE0000/SQL00003/SQLOGDIR, which contains the actual log files.

SQL00003 is a directory containing db2 configuration files for the NAV database. Other databases will be mapped to SQL0000x, according to the respective order of database creation. These log control files will be used by db2 when processing recovery, to determine the starting point in the logs to begin the recovery.