Re: How do I identify a tables dbspace?
David Ferguson wrote:
>
> G'day all. Our site routinely performs "alter table to index" for
> increased performance. I would like to run an SQL query to check
> that enough space is available in the tables dbspace as part of an
> automatic script. It's a bummer to have the command run for 4 hours
> and then have it rollback because of a free space problem. Any way
> to get this info out of the sysmaster tables?
>
> I think I can write the query once I know how to identify the dbspace
> that the table belongs to.
>
> Any comments, suggestions, help would be greatly appreciated.
>
> Thanks, Dave
Try this query, or appropriate variants thereof, againse the sysmaster
database:
select syschunks.dbsnum, name, sum(nfree) free_pages
from syschunks, sysdbspaces
where syschunks.dbsnum = sysdbspaces.dbsnum
group by syschunks.dbsnum, name
This gives you a list of dbspaces and the number of free pages in each.
The next question is: How much space does your "create index" command
need? Besides the size of the index alone, there is the temp space
involved in the creation of an index.
No answer for that one from this end of universe..
--
-- Jake (Querying minds want to know..)
. .
_..-'( )`-.._
./'. '||\\\\. }\\_/{ .//||` .`\\.
./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\.
./'..|'.|| |||||\\`````` \\'@'/ ''''''/||||| ||.`|..`\\.
./'.||'.|||| ||||||||||||. | .|||||||||||| ||||.`||.`\\.
/'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\
'.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.`
'.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.`
|/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\|
V V V }' `\\ /' `{ V V V
\\ \\ \\ V / / /
+-----------------------------------------------------------+
| Impeccable Logic: A thought process which successfully |
| resists chicken bites |
+-----------------------------------------------------------+