Sizes w/o 3D
Ok, now that I have that figured out.
Apologies for not using a test group on that. Long lines will wrap with =
a "=3D"
I trust the system tables (sysextents) less that I trust SMI tables.
cheers
j.
-- Reports size in MB
-- Note: These functions use npused, which is the number of=20
-- pages used. =20
-- Alternatives include npdata (number of pages of data, i.e. w/o=20
-- bitmap pages) and
-- nptotal - which is number of pages allocated
CREATE FUNCTION tabsize(tabname char(128)) RETURNING integer;
DEFINE p_size INTEGER;
-- Fragmented tables have partnum in sysfragments
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, sysfragments b, systables c
WHERE c.tabname=3D tabname
AND b.tabid=3D c.tabid
AND b.partn=3D a.partnum
AND c.partnum=3D 0;
-- Non-fragmented tables have partnum in systables
IF p_size IS NULL THEN
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, systables c
WHERE c.tabname=3D tabname
AND c.partnum=3D a.partnum;
END IF;
RETURN p_size;
END FUNCTION;
-- Report size of an index
CREATE FUNCTION idxsize(idxname char(128)) RETURNING integer;
DEFINE p_size INTEGER;
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, sysfragments b
WHERE indexname=3D idxname
AND b.partn=3D a.partnum;
RETURN p_size;
END FUNCTION;
-- Report size of the dbspace in GB
CREATE FUNCTION DBSIZE(dbspace char(128)) RETURNING INTEGER;
DEFINE SIZE INTEGER;
-- Figure out size of database
SELECT (sum(chksize*a.pagesize)/(1024*1024*1024))=20
INTO SIZE
FROM sysmaster:syschunks a, sysmaster:sysdbspaces b
WHERE a.dbsnum=3D b.dbsnum
AND name=3D dbspace;
RETURN SIZE;
END FUNCTION;
-- Report the size of a table partition (must be a fragmented table)
CREATE FUNCTION partsize(partname char(128)) RETURNING integer;
DEFINE p_size INTEGER;
-- Fragmented tables have partnum in sysfragments
SELECT SUM(a.npused * a.pagesize)/(1024*1024)
INTO p_size
FROM sysmaster:sysptnhdr a, sysfragments b
WHERE a.partnum=3D b.partn
AND b.partition=3D partname
AND b.indexname is null;
RETURN p_size;
END FUNCTION;