size of one specific database in IDS server?
Will Landstrom asked how to find the size of one database in an IDS 9.4 instance holding several. Kern Doe supplied a dbaccess script querying sysmaster sysextents and sysdatabases, summing extent size by database, to filter with grep. DL Redden's dbspace-usage queries covered the whole instance. Will thanked Kern; solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi everyone. Does anyone have a query (or some other method) to determine the size of 1 specific IDS 9.4 database in a Instance that contains multiple databases? Thanks
Will, put this in a script (namely perhaps dbsize), and run it...
if you only like to see info on one database, then dbsize | egrep
'Date|<dbname>'
FYI. script should work for most IDS versions on most Unix OS...
dbaccess sysmaster << EOF 2> /dev/null | grep -v "^$"
set isolation to dirty read;select today Date_asof, dbsname[1,18] dbname, round (sum(size) *
(
select sh_pagesize from sysshmvals
) /1024,0) size_KB
from sysextents a, sysdatabases b
where a.dbsname=b.name
group by dbsname
order by 3 desc
EOF
----- Original Message ----
From: WILL LANDSTROM <willlandstrom@yahoo.com>
To: ids@iiug.org
Sent: Tuesday, September 9, 2008 10:43:43 AM
Subject: size of one specific database in IDS server? [13309]
Hi everyone. Does anyone have a query (or some other method) to determine the
size of 1 specific IDS 9.4 database in a Instance that contains multiple
databases? Thanks
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
This is the SQL that I use for the 9 engine on HP-UX. The second query is looking at blob spaces so you if you have any you may need to use something other that 2048 in the calculations. SELECT TODAY, TRIM(a.name), --dbspace name SUM(b.chksize), --size in pages SUM(b.nfree), -- free pages ROUND(((SUM(b.chksize) - SUM(b.nfree)) / SUM(b.chksize)) * 100) --% used FROM sysmaster:sysdbspaces a, sysmaster:syschunks b WHERE a.is_temp = 0 and a.is_blobspace = 0 and a.is_sbspace = 0 and a.name not in ('rootdbs','plogdbs','logdbs') and a.dbsnum = b.dbsnum GROUP BY 1,2,3 UNION SELECT TODAY, TRIM(a.name), SUM(b.chksize), SUM(b.nfree * c.bpagesize / 2048), ROUND(((SUM(b.chksize) - SUM(b.nfree * c.bpagesize / 2048)) / SUM(b.chksize)) * 100) FROM sysmaster:sysdbspaces a, sysmaster:syschunks b, sysmaster:sysdbstab c WHERE a.is_blobspace = 1 and a.dbsnum = b.dbsnum and a.dbsnum = c.dbsnum GROUP BY 1,2,3 UNION SELECT TODAY, TRIM(a.name), SUM(b.chksize), SUM(b.nfree + b.udfree), ROUND(((SUM(b.chksize) - SUM(b.nfree + b.udfree)) / SUM(b.chksize)) * 100) FROM sysmaster:sysdbspaces a, sysmaster:syschunks b, sysmaster:sysdbstab c WHERE a.is_sbspace = 1 and a.dbsnum = b.dbsnum and a.dbsnum = c.dbsnum GROUP BY 1,2,3 ----- Original Message ---- From: WILL LANDSTROM <willlandstrom@yahoo.com> To: ids@iiug.org Sent: Tuesday, September 9, 2008 9:43:43 AM Subject: size of one specific database in IDS server? [13309] Hi everyone. Does anyone have a query (or some other method) to determine the size of 1 specific IDS 9.4 database in a Instance that contains multiple databases? Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I apologize.. I misread your question, what I sent was for the entire instance. ----- Original Message ---- From: WILL LANDSTROM <willlandstrom@yahoo.com> To: ids@iiug.org Sent: Tuesday, September 9, 2008 9:43:43 AM Subject: size of one specific database in IDS server? [13309] Hi everyone. Does anyone have a query (or some other method) to determine the size of 1 specific IDS 9.4 database in a Instance that contains multiple databases? Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Kern. That's great. Thanks a lot. Will