How to determine if I have DB2 database with Automatic Storage?

by | Aug 29, 2007

Did somebody create a DB2 database for you and you are just getting started to work on inherited legacy? How do you find if the database was created with automatic storage YES or NO?

Let us do a simple exercise.

My laptop has two drives C: and D: and I have DB2 9 installed.

C:\>db2 create database sample2 on C:,D:
C:\>db2 connect to sample2
C:\>db2 list active databases

Output on my machine was:


                           Active Databases

Database name                              = SAMPLE2
Applications connected currently           = 1
Database path                              = C:\DB2\NODE0000\SQL00004\

Now go to your windows explorer and check following folders:

C:\DB2\NODE0000\SAMPLE2
D:\DB2\NODE0000\SAMPLE2

In Sample2 directory on C: and D:, you have table spaces created on 2 storage paths that you specified when creating SAMPLE2 database.

Check folder C:\DB2\NODE0000\SQL00004\ and this is where your database was created. How do you know how SQL token SQL0004 is linked to the SAMPLE2 database? You got this from the output of LIST ACTIVE DATABASES command.

How do I know about storage paths if some one else had created the database for me? This simple exercise should help you.

Method – A

C:\>db2 connect to sample2
C:\>db2 update monitor switches using bufferpool on lock on sort on statement on table on timestamp on uow on

With above command, I turned on monitor switches for this current DB2 session. I discuss this elsewhere to explain it in more detail. It is not necessary to turn on all switches and above was just an example.

C:\>db2 get snapshot for all on sample2

You will see the following as a part of your snapshot output.

Number of automatic storage paths          = 2
Automatic storage path                     = c:
      File system ID                       = 2149054632
      Storage path free space (bytes)      = 50386874368
      File system used space (bytes)       = 49438912512
      File system total space (bytes)      = 100027113472
Automatic storage path                     = d:
      File system ID                       = 2623384267
      Storage path free space (bytes)      = 9186639872
      File system used space (bytes)       = 70837075968
      File system total space (bytes)      = 80023715840

Method – B

C:\>db2pd -db sample2 -storagepaths

Database Partition 0 -- Database SAMPLE2 -- Active -- Up 0 days 00:11:16

Database Storage Paths:
Number of Storage Paths       2

Address    PathName
0x02C10680 d:
0x02C10180 c:

Method – C

C:\>db2 connect to sample2
C:\>db2 "select * from sysibmadm.dbpaths"

The output from above query will give you information about DB_STORAGE_PATH, LOGPATH, LOCAL_DB_DIRECTORY and DBPATH.