I have an instance that has three dbspaces that contain data:

1) datadbs contains various database tables
2) imgdbs contains scanned images in BLOB fields
3) archdbs contains archived documents, also in BLOB fields

I was trying to use sysmaster table to look at the number of pages and then doing a calculation to get the total size of each. The calculation seems to be fine for the data dbspace. But the two dbspaces that contain blobs are showing much too small.

Here is the calculation I used:

SELECT dbsname, ROUND(SUM((nptotal * sh_pagesize)/1024)) Kb"
FROM sysptprof p, sysptnhdr h, sysshmvals"
WHERE p.partnum = h.partnum GROUP BY dbsname"

I end up with these results (abbreviated for clarity):

dbsname system
kb 64

dbsname imgdbs
kb 200

dbsname archdbs
kb 200

dbsname rootdbs
kb 2400

dbsname datadbs
kb 44800

dbsname proddb
kb 78151992

dbsname tempdbs
kb 3200

The number I get for "proddb" database is expected (around 80gb), but the contents of the imgdbs and archdbs are way smaller than I expected. So I believe I am not selecting the right data to display.

Has anyone done something similar?

Essentially, I want to find out if the database is small enough to fit on certain media, or in the designated folder on disk. That would get reported if it exceeds or is nearing max capacity so I can arrange to increase the disk space, or replace the USB
flash drive with a larger capacity, etc.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)