topic Re: Tablespace info for DB2 on HP in Operating System - HP-UX

Tablespace info for DB2 on HP

Bedare Nikhil — Fri, 04 Sep 2009 07:06:47 GMT

Hi all,
I have a HP machine with a db2 database installed.There are few tablespaces on this databases.HOw to check whether the tablespace has been created on raw volume or not ?

Thanks in advance,
Nikhil

Re: Tablespace info for DB2 on HP

Hakki Aydin Ucar — Fri, 04 Sep 2009 08:18:31 GMT

issue the followings:

db2 connect to your_DataBase
db2 list tablespaces show

Re: Tablespace info for DB2 on HP

Matti_Kurkela — Fri, 04 Sep 2009 08:33:20 GMT

I'm not very familiar with db2, but in most database engines it works like this:

You should be able to get a path name (or a list of them) from the database engine, identifying where that tablespace is stored. Ask a DataBase Admin to help if necessary.

If the pathname(s) refer to /dev/rdsk device(s), they are obviously on raw volume(s).

If the pathnames refer to something else, you'll need to find out whether they are regular files or something else (symbolic links to /dev/vg*/rlvol* devices, or maybe even manually-created copies of /dev/vg*/rlvol* device nodes). The easiest way to do this is to list the pathnames with a command like "ll ".

If they are regular files (the first letter of the "ll" output line shows a "-" sign), the tablespace is not on a raw volume.

If they are device nodes (the first letter of the "ll" output line shows a "c" or "b"), then the tablespace is on a raw volume.

If they are symbolic links (the first letter of the "ll" output line shows a "l"), find the target of the symbolic link at the end of the "ll" output line and examine it the same way.

Sometimes symbolic links or custom device nodes are used with raw volumes to make the administration easier. If raw volumes are used at your site, you should talk with the database admins to familiarize yourself with their preferred way of setting them up.

MK

Re: Tablespace info for DB2 on HP

Bedare Nikhil — Fri, 04 Sep 2009 09:29:29 GMT

so there is not command to check this ?

Re: Tablespace info for DB2 on HP

Hakki Aydin Ucar — Fri, 04 Sep 2009 09:55:10 GMT

There are lots of command around DB2 .
Did you try these commands ?? (There was a typo previous one.)

# db2 connect to
# db2 list tablespaces show detail

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 6393
Useable pages = 6393
Used pages = 6393
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1

Re: Tablespace info for DB2 on HP

Bedare Nikhil — Fri, 04 Sep 2009 14:20:44 GMT

From the above output,how do we know that the tablespace is created on a raw device ?

Re: Tablespace info for DB2 on HP

Hakki Aydin Ucar — Sat, 05 Sep 2009 11:54:35 GMT

I believe if you can see tablespace id(s) and names, it means they are created. You can see below at the end of list:
Contains tablespace(s):

You can then obtain the information for table space via the
# db2pd -tablespaces

Also When it comes to collecting information for a DB2 problem, the most important DB2 utility you need to run is db2support. The db2support utility is designed to automatically collect all DB2 and system diagnostic information available.
# db2support

And check my notes on db2 tablespace summaries below:

A tablespace is a place to
store tables.Tablespaces reside in database partition groups. Tablespace definitions and
attributes are recorded in the database system catalog.

For storing DB2 temporary tablespaces generally use directory /db2temp.

And /tablespaces holds all user tablespaces. For example to create multiple tablespace :

# connect to DB;
# create tablespace sample_mpl in IBMDEFAULTGROUP pagesize 4k

For Example;
To inspect table space USERSPACE1 in the sample database, issue
# db2dart sample /TSI 2

-where 2 is the table space ID for table space USERSPACE1.

Re: Tablespace info for DB2 on HP

Hakki Aydin Ucar — Mon, 07 Sep 2009 05:51:46 GMT

Nikhil,
you will find corrections/details for my previous post:

to find your DB Name, issue ;
# ls -l
note that database name as:
xxx.rpt

# db2pd -db xxx -tablespaces

# db2support . -d xxx -c