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:
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.