DB2 Sample Database – Modifying storage path

In an earlier blog post, we looked at how to create a sample database in DB2 LUW. We also looked at changing the defaults to suit our needs…changed the default database name from SAMPLE to whatever we wanted and also changed the database path (DBPATH).

Here is the link to that blog post: https://db2talk.com/2015/09/08/creating-a-db2-luw-linux-unix-and-windows-sandbox-database/

The database path (DBPATH in the create database command) is the location where a hierarchical directory structure is created. The structure holds the following files needed for the operation of the database:

  • Buffer pool information
  • Table space information
  • Storage path information
  • Database configuration information
  • History file information regarding backups, restores, loading of tables, reorganization of tables, altering of table spaces, and other database changes
  • Log control files with information about active logs

But, what if we want to change the storage path? i.e., the path where database contents (tablespace containers) are stored. By default, DBPATH and the database storage path default to the same value.

When creating the sample database, there is no direct way to modify database storage path. So, we will take a 2-step approach.

Step 1: We will create the sample database as we did in the above mentioned blog post.

$ db2sampl -dbpath /db2home/sample

  Creating database "SAMPLE" on path "/db2home/sample"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "DB2INST1"...
  Creating tables with XML columns and XML data in schema "DB2INST1"...

  'db2sampl' processing complete. 

Step 2:  We will now use a nifty feature in db2look to extract the DDL to create the database and then modify the storage path clause, drop the sample database we created in the first step and then create the database again with modified DDL that has updated storage path.

Below is the command (and its output) to extract DDL to create database sample.


$ db2look -d sample -createdb
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Thu 24 Dec 2015 01:04:40 PM EST
-- Database Name: SAMPLE
-- Database Manager Version: DB2/LINUXX8664 Version 10.5.6
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful
--------------------------------------------------------
-- Generate CREATE DATABASE command
--------------------------------------------------------
CREATE DATABASE SAMPLE
AUTOMATIC STORAGE YES
ON '/db2home/sample'
DBPATH ON '/db2home/sample/'
USING CODESET UTF-8 TERRITORY US
COLLATE USING IDENTITY
PAGESIZE 8192
DFT_EXTENT_SZ 32
CATALOG TABLESPACE MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 4
NO FILE SYSTEM CACHING
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
TEMPORARY TABLESPACE MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 32
FILE SYSTEM CACHING
USER TABLESPACE MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 32
NO FILE SYSTEM CACHING
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
;
CONNECT TO SAMPLE;
COMMIT WORK;
CONNECT RESET;
TERMINATE;

We will now modify the above highlighted area to ‘/db2inst1/db/data’, drop the database and then run the modified DDL to create database.


CREATE DATABASE SAMPLE AUTOMATIC STORAGE YES 
ON '/db2inst1/db/data/' 
DBPATH ON '/db2home/sample/' 
USING CODESET UTF-8 TERRITORY US 
COLLATE USING IDENTITY PAGESIZE 8192 
DFT_EXTENT_SZ 32 
CATALOG TABLESPACE MANAGED BY AUTOMATIC STORAGE 
EXTENTSIZE 4 NO FILE SYSTEM CACHING 
AUTORESIZE YES INITIALSIZE 32 M MAXSIZE NONE 
TEMPORARY TABLESPACE MANAGED BY AUTOMATIC STORAGE 
EXTENTSIZE 32 FILE SYSTEM CACHING 
USER TABLESPACE MANAGED BY AUTOMATIC STORAGE 
EXTENTSIZE 32 NO FILE SYSTEM CACHING 
AUTORESIZE YES INITIALSIZE 32 M MAXSIZE NONE
DB20000I The CREATE DATABASE command completed successfully.

Now, when we create a tablespace that has automatic storage, its storage path would be at ‘/db2inst1/db/data’.

Here is an example:


$db2 "create tablespace data"
DB20000I  The SQL command completed successfully.

$ db2 list tablespaces
Tablespaces for Current Database
............

 Tablespace ID                        = 3
 Name                                 = DATA
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
.............
$ db2 list tablespace containers for 3

            Tablespace Containers for Tablespace 3

 Container ID = 0
 Name = /db2inst1/db/data/db2inst1/NODE0000/SAMPLE/T0000003/C0000000.LRG
 Type                                 = File

Conclusion:

In this blog post, we have looked at how we can modify the Database Storage Path for a sample database that was created earlier.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s