marrowyung
asked on
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.
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.
ASKER
what is df ?
just db2 > df ?
anyway to check the threshhold ?
just db2 > df ?
anyway to check the threshhold ?
df is a unix/linux command. It must be run from the unix shell prompt, not the db2 command line processor.
ASKER
this is the result of df:
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 "
any problem you can see ?
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% /recoveris 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 = 1any problem you can see ?
ASKER
Kdo, any more suggestion ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ?
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 ?
>> 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.
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.
ASKER
"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?
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?
>>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.
"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.
ASKER
">> 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 ?
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 ?
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.
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.
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% /recoverHere'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.
ASKER
"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?
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?
No. Df just tells you the configured devices, size, and free space. But you can use the mount point to back into some information.
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.
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% /recoverIf 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.
ASKER
"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?
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?
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.
SELECT path from sysibmadm.dbpaths
That will show the path names to all of the database directories.
ASKER
it said
Lookup Error - DB2 Database Error: ERROR [42704] [IBM][DB2/SUN] SQL0204N "SYSIBMADM.DBPATHS" is an undefined name.
Lookup Error - DB2 Database Error: ERROR [42704] [IBM][DB2/SUN] SQL0204N "SYSIBMADM.DBPATHS" is an undefined name.
That's either another Version 9 feature or you don't have permission to read the table. :(
ASKER
The one is rOOT account already ! whatelse permission we need ?
ASKER
thanks for that, just finish an Oracle trainning for the whole week, Oracle is good !
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