find out the disk space free of DB2 data disk before creating index by Toad

Dear all,

is there any way to check out how many disk space left by Toad for DB2? we are using freeware and we can only run script for that.

what is the script for DB2 V8.1 for this feature? must check each tablespace one by one ?

We want to do before creating index and after creating index to make sure that we are not running out of space.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi marrowyung,

The limiting factor is the device where the tablespace is stored.  DB2 will continue to extend the tablespace, as needed, for data and/or indexes.

I recommend that you schedule a job to regularly run on the database server and run *df*.  Email the output back to you and/or another sysadmin. This won't scream LOW SPACE at you, but if the available space on the critical drives remains stable and below a safe threshhold, the database is safe.  Depending on your environment the threshhold should probably be between 80 and 95%


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
what is df  ?

just db2 > df ?

anyway to check the threshhold ?
0
Kent OlsenDBACommented:
df is a unix/linux command.  It must be run from the unix shell prompt, not the db2 command line processor.
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

marrowyungSenior Technical architecture (Data)Author Commented:
this is the result of df:

Filesystem           1k-blocks      Used Available Use% Mounted on
/dev/dsk/c1t0d0s0     22189417   6938772  15028751  32% /
/dev/dsk/c1t0d0s5     12106732   1248900  10736765  11% /var
swap                   7158304        64   7158240   1% /var/run
swap                   7166880      8640   7158240   1% /tmp
/dev/dsk/c1t0d0s7    102749135  78027353  23694291  77% /space
/dev/dsk/c1t1d0s6    141178367  40169685  99596899  29% /recover

Open in new window


is the "77% /space" in the remaining space?

someone said:

"The /space partition contains both db2 database and some other backups. We cannot say for sure how much space is left on the database exactly."

is that right?

by doing "list tablespaces show detail "

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

Tablespace ID                        = 1
Name                                 = TEMPSPACE1
Type                                 = Database managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal
Total pages                          = 87500
Useable pages                        = 87456
Used pages                           = 64
Free pages                           = 87392
High water mark (pages)              = 64
Page size (bytes)                    = 4096
Extent size (pages)                  = 32
Prefetch size (pages)                = 32
Number of containers                 = 1

Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = Database managed space
Contents                             = Any data
State                                = 0x0000
   Detailed explanation:
     Normal
Total pages                          = 596508
Useable pages                        = 596448
Used pages                           = 461632
Free pages                           = 134816
High water mark (pages)              = 464672
Page size (bytes)                    = 16384
Extent size (pages)                  = 32
Prefetch size (pages)                = 32
Number of containers                 = 1
Minimum recovery time                = 2013-10-11-00.30.01.000000

Tablespace ID                        = 3
Name                                 = SYSTOOLSPACE
Type                                 = System managed space
Contents                             = Any data
State                                = 0x0000
   Detailed explanation:
     Normal
Total pages                          = 220
Useable pages                        = 220
Used pages                           = 220
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                        = 4
Name                                 = TEMP32K
Type                                 = Database managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal
Total pages                          = 9600
Useable pages                        = 9584
Used pages                           = 32
Free pages                           = 9552
High water mark (pages)              = 32
Page size (bytes)                    = 32768
Extent size (pages)                  = 16
Prefetch size (pages)                = 16
Number of containers                 = 1

Tablespace ID                        = 5
Name                                 = SYSTOOLSTMPSPACE
Type                                 = System managed space
Contents                             = User Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal
Total pages                          = 1
Useable pages                        = 1
Used pages                           = 1
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

Open in new window


any problem you can see ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Kdo, any more suggestion ?
0
Kent OlsenDBACommented:
Hi marrowyung,

Apologies....   Don't know how I didn't see your responses here.

All of the tablespaces appear normal.  I've edited the report to show the key items, and converted the "Total pages" value to bytes.  The entire database is about 10G, pretty small and manageable by today's standards.

Name                                 = SYSCATSPACE
Total pages                          = 87500     (258,400,000)
Useable pages                        = 87456
Used pages                           = 42816
Free pages                           = 44640
Page size (bytes)                    = 4096

Name                                 = TEMPSPACE1
Total pages                          = 87500     (258,400,000)
Useable pages                        = 87456
Used pages                           = 64
Free pages                           = 87392
Page size (bytes)                    = 4096

Name                                 = USERSPACE1
Total pages                          = 596508     (9,773,187,072)
Useable pages                        = 596448
Used pages                           = 461632
Free pages                           = 134816
Page size (bytes)                    = 16384

Name                                 = SYSTOOLSPACE
Total pages                          = 220     (901,120)
Useable pages                        = 220
Used pages                           = 220
Free pages                           = Not applicable
Page size (bytes)                    = 4096

Name                                 = TEMP32K
Total pages                          = 9600     (314,572,800)
Useable pages                        = 9584
Used pages                           = 32
Free pages                           = 9552
Page size (bytes)                    = 32768

Name                                 = SYSTOOLSTMPSPACE
Total pages                          = 1     (4,096)
Useable pages                        = 1
Used pages                           = 1
Free pages                           = Not applicable
Page size (bytes)                    = 4096

Open in new window

                                           



Regarding the output of *df*, the file system at /space is about 100G, with 23G free.  

Filesystem           1k-blocks      Used Available Use% Mounted on
/dev/dsk/c1t0d0s7    102749135  78027353  23694291  77% /space

Open in new window


So the free space on the device is more than twice the size of the database.  I don't see any problem here at all.

The only caution is that you have no control of other processes that may also be using that device and at some point during the day (or night) a process could use up all of the free space and later release it.  Creating and copying backup files could do this.  But unless your DB2 database is active and needs to grow while the disk is in a 100% in use state, there should be no ill effects.

It is unusual to have a production DB2 database defined this way though.  A better approach would be to create a 20G device that has only the DB2 files.  Your system admin should be able to do this, mount the device at /space/IBM (or whatever the top node of the DB2 files is) and copy the DB2 files to it.  That would ensure that no process other that DB2 can fill up the disk.


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
So the Available one over there meansure in KB?

So for TEMPSPACE1, the totaly size is, page size x total page used ?

"So the free space on the device is more than twice the size of the database.  I don't see any problem here at all.

nice ! and we only use one disk you mean ?

"The only caution is that you have no control of other processes that may also be using that device and "

if I use the ROOT account i should see everytihng ?

"It is unusual to have a production DB2 database defined this way though. "

so you means our DB2 is storing something else? how can you tell ?
0
Kent OlsenDBACommented:
>> So the Available one over there measure in KB?

Yes.  The title bar shows "1k-blocks" so the total storage in bytes is 1024 times the value shown.  With large disks that can be a little tough to read.  *df -m* should give you the same report, displaying space in 1M units instead of 1K.


>> So for TEMPSPACE1, the totaly size is, page size x total page used ?

Correct.  There may be an extra block in the front of the tablespace that DB2 uses for its own purposes.  I don't remember if it's part of the count though.  Either way, the overall file size is within 1 block of what DB2 reports.


>> nice ! and we only use one disk you mean ?

My guess is that you're using a SAN to manage space.  So "one disk" is a subjective term.  :)


>>"The only caution is that you have no control of other processes that may also be using that device and "
>>if I use the ROOT account i should see everything ?

Unless you're the sysadmin, you don't know how a large (shared) device is being used.  It could be used as temporary space to hold database backups, system backups, etc.  Even though there's more than 20G free now, the nightly jobs could use up the space and the release it.  There could be issues if one of the tablespaces needs to extend while the disk is full.


>> "It is unusual to have a production DB2 database defined this way though. "
>> so you means our DB2 is storing something else? how can you tell ?

Production database files are usually kept on a separate device (or devices) so that other jobs can't use up the disk space.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Production database files are usually kept on a separate device (or devices) so that other jobs can't use up the disk space. "

yes you are right and usually the box usually only get DB2 running and nothing else.

in our box, the application server is running on it too.

"The title bar shows "1k-blocks" so the total storage in bytes is 1024 times the value shown.  With large disks that can be a little tough to read.  *df -m* should give you the same report, displaying space in 1M units instead of 1K."

this one will show the value in MB, right ? as the column is already 1K .

"My guess is that you're using a SAN to manage space.  So "one disk" is a subjective term.  :)"

from "result of df:"

2,3,6,7 is a disk, right? so we should have 4 disks ?

"Unless you're the sysadmin,"

so from here, it means sysadmin and ROOT is different account and sysadmin is bigger than ROOT?
0
Kent OlsenDBACommented:
>>in our box, the application server is running on it too.

"The title bar shows "1k-blocks" so the total storage in bytes is 1024 times the value shown.  With large disks that can be a little tough to read.  *df -m* should give you the same report, displaying space in 1M units instead of 1K."

>>this one will show the value in MB, right ? as the column is already 1K .

Correct.  The report is in 1K or 1M blocks, depending on the -m parameter.



>> 2,3,6,7 is a disk, right? so we should have 4 disks ?

4 logical disks.  Technically, they're just "mount points".  They could be physical disks, RAID disk or SAN storage.  There could be 4 physical disks or more than a dozen physical disks that are configured to be 4 logical devices.


>> so from here, it means sysadmin and ROOT is different account and sysadmin is bigger than ROOT?

sysadmin and root are the same thing.  root is the sysadmin user on unix systems.
0
marrowyungSenior Technical architecture (Data)Author Commented:
">> 2,3,6,7 is a disk, right? so we should have 4 disks ?

4 logical disks.  Technically, they're just "mount points".  They could be physical disks, RAID disk or SAN storage.  There could be 4 physical disks or more than a dozen physical disks that are configured to be 4 logical devices."

that's why I don't understand why you seems so clear that we only have one disk.

or you just list one out:

/dev/dsk/c1t0d0s7    102749135  78027353  23694291  77% /space

and I misunderstand you are sure that we only have one disk.....

"sysadmin and root are the same thing.  root is the sysadmin user on unix systems. "

but there is a sysadmin role only in DB2 but not unix ?
0
Kent OlsenDBACommented:
I think that terminology is confusing things.  Disk can mean a physical disk or a logical disk.  The output from *df* shows the logical disks, 1 per line.  We don't see the physical disks, though the file system naming suggests that the storage is assigned from a SAN.

Filesystem           1k-blocks      Used Available Use% Mounted on
/dev/dsk/c1t0d0s0     22189417   6938772  15028751  32% /
/dev/dsk/c1t0d0s5     12106732   1248900  10736765  11% /var
swap                   7158304        64   7158240   1% /var/run
swap                   7166880      8640   7158240   1% /tmp
/dev/dsk/c1t0d0s7    102749135  78027353  23694291  77% /space
/dev/dsk/c1t1d0s6    141178367  40169685  99596899  29% /recover

Open in new window



Here's the original output.  It shows 6 mount points.  Each mount point (line) is a logical device.  Early on you said that the DB2 files are on /space.  Since that is where the DB2 files reside, that's the only logical disk that we care about.

And all of the possible admin roles sometimes get lumped together.  sysadmin is often generic and applies to one of the highest level admin roles for database, network, unix, linux, Windows, Active Directory, etc.  My comment about the sysadmin knowing how the space is used probably makes more sense if you read it as root knowing how the space is used.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Early on you said that the DB2 files are on /space."

Sorry, the result of df don't said this, right?

"the sysadmin knowing how the space is used probably makes more sense if you read it as root knowing how the space is used. "

Just wondering how the sysadmin role can see everyting, but this is on UNIX side but not DB2 right, right?
0
Kent OlsenDBACommented:
No.  Df just tells you the configured devices, size, and free space.  But you can use the mount point to back into some information.

Filesystem           1k-blocks      Used Available Use% Mounted on
/dev/dsk/c1t0d0s0     22189417   6938772  15028751  32% /
/dev/dsk/c1t0d0s5     12106732   1248900  10736765  11% /var
swap                   7158304        64   7158240   1% /var/run
swap                   7166880      8640   7158240   1% /tmp
/dev/dsk/c1t0d0s7    102749135  78027353  23694291  77% /space
/dev/dsk/c1t1d0s6    141178367  40169685  99596899  29% /recover

Open in new window

If the path to the DB2 files starts with /space, the files are on c1t0d0s7.
If the path to the DB2 files starts with /recover, the files are on c1t1d0s6.
If the path to the DB2 files starts with /var, the files are on c1t0d0s5.
If the path to the DB2 files starts with anything else, the files are on c1t0d0s0.

Note that you may have sym links that redirect a path, so this may seem to not apply.  But if you use the physical path, this will hold up.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"If the path to the DB2 files starts with /space, the files are on c1t0d0s7.
If the path to the DB2 files starts with /recover, the files are on c1t1d0s6.
If the path to the DB2 files starts with /var, the files are on c1t0d0s5.
If the path to the DB2 files starts with anything else, the files are on c1t0d0s0."

yeah, waht I don't understand is, how can we know if the path to the DB2 file start with xxx?
0
Kent OlsenDBACommented:
Can you read the table sysibmadm.dbpaths?  You might need DBA authority.

  SELECT path from sysibmadm.dbpaths

That will show the path names to all of the database directories.
0
marrowyungSenior Technical architecture (Data)Author Commented:
it said

Lookup Error - DB2 Database Error: ERROR [42704] [IBM][DB2/SUN] SQL0204N  "SYSIBMADM.DBPATHS" is an undefined name.
0
Kent OlsenDBACommented:
That's either another Version 9  feature or you don't have permission to read the table.  :(
0
marrowyungSenior Technical architecture (Data)Author Commented:
The one is rOOT account already ! whatelse permission we need ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
thanks for that, just finish an Oracle trainning for the whole week, Oracle is good !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.