Interesting: Dbspace names cropping up as databases
Hi Family.
In trying to find out how much space (in pages) each database in a
server occupies, I ran across something interesting: The names of
dbspaces are cropping up when I look for data on databases.
A distilled query shows this:
select dbsname, count(*) ntables
from systabnames
group by dbsname
order by ntables, dbsname
This yields:
dbsname ntables
imm_frag1 1
imm_frag2 1
immdbs 1
indexdbs 1
logdbs 1
rootdbs 1
tmp_dbs1 1
tmp_dbs2 1
sysutils 39
bndb_matrix 43
bndb 53
sysmaster 56
book 63
imm 221
14 row(s) retrieved.
Those first 8 entries are the names of dbspaces. The remaining 6 are
the names of dbspaces.
Of course, I can eliminate the dbspace names by adding a join (or
subquery) on sysmaster:sysdatabases, but I as curious about what the
dbspace names are doing here.
The query that sparked by curiosity was a join of systabnames and
systabinfo seeking the number of pages occupied by all tblspaces
belonging to each database.
select ta.dbsname,
sum(ti_nptotal) total_pages,
trunc((500+sum(ti_nptotal))/500) total_mb,
sum(ti_npused) used_pages
from sysmaster:systabnames ta,
sysmaster:systabinfo ti
where ta.partnum = ti.ti_partnum
group by dbsname
order by dbsname ;
It gave me 14 rows: 6 databases, 8 dbspaces. I have edited the output
to mark the names of dbspaces where I had expected a database name.
dbsname total_pages total_mb used_pages
bndb 436 1 181
bndb_matrix 536 2 344
book 604 2 282
imm 1310672 2622 1309775
*imm_frag1 50 1 14
*imm_frag2 50 1 14
*immdbs 350 1 350
*indexdbs 50 1 34
*logdbs 50 1 2
*rootdbs 200 1 192
sysmaster 580 2 305
sysutils 328 1 137
*tmp_dbs1 50 1 2
*tmp_dbs2 50 1 2
Since the ti_nptotal is a multiple of 50, it reasonable to assume the
table is actually the TBspace tblspace for each dbspace. In fact, when
I added tabname to the query and joined the query with sysdbspaces, I
got the string "TBLSpace" for each of the tabname entries.
Just though someone else might find it interesting.
--
+---- Jacob Salomon DBA JSalomon@bn.com --------------------+
|(In perpetual pursuit of undomesticated semi-aquatic avians)|
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.