Re: getting full volume info

Posted in 2008

Topics: Storage & Space Management, SQL Development & Query Writing, Transactions, Locking & Isolation

Floyd Wellershaus — Wed, 22 Oct 2008 09:55:41 -0500 (CDT) — source: Usenet: comp.databases.informix

Pretty cool query. Thanks. I found this one, and the allocated_tables value you have agrees with the output of this. database sysmaster; select dbsname,sum( pe_size*4096 ) total_bytes --for 4k page size from systabnames, sysptnext where partnum = pe_partnum and dbsname='whatever' group by 1 order by 2 desc ----- Original Message ----- From: "LIGHT SCANS" <light_scans@yahoo.com> Sent: Wed, October 22, 2008 10:35 Subject:Re: getting full volume info Hello Floyd, This gives grand totals of what you're looking for (but it assumes that you have 4 kb page size): DATABASE sysmaster; SET ISOLATION TO DIRTY READ; -- Used_by_Data excludes indexes! SELECT ROUND(SUM(ti_npdata )*0.004096,0) Used_by_Data__Mb, -- Allocated_Tables includes indexes! ROUND(SUM(ti_nptotal)*0.004096,0) Allocated_Tables FROM systabinfo; SELECT ROUND(SUM(chksize)*0.004096,0) Allocated_Chunks FROM syschunks; _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list ----- End of original message -----

↪ replying to Floyd Wellershaus

Mark Scranton (Xtivia Inc.) — Wed, 22 Oct 2008 15:00:06 -0700 (PDT) — source: Usenet: comp.databases.informix

On Oct 22, 10:55 am, "Floyd Wellershaus" <fl...@fwellers.com> wrote: > Pretty cool query. Thanks. > I found this one, and the allocated_tables value you have agrees with the > output of this. > > database sysmaster; > select dbsname,> sum( pe_size*4096 ) total_bytes --for 4k page size > from systabnames, sysptnext > where partnum = pe_partnum > and dbsname='whatever' > group by 1 > order by 2 desc > > ----- Original Message ----- > From: "LIGHT SCANS" <light_sc...@yahoo.com> > Sent: Wed, October 22, 2008 10:35 > Subject:Re: getting full volume info > > Hello Floyd, > > This gives grand totals of what you're looking for (but it assumes > that you have 4 kb page size): > > DATABASE sysmaster; > SET ISOLATION TO DIRTY READ;> > -- Used_by_Data excludes > indexes! > SELECT ROUND(SUM(ti_npdata )*0.004096,0) Used_by_Data__Mb, > -- Allocated_Tables includes > indexes! > ROUND(SUM(ti_nptotal)*0.004096,0) Allocated_Tables > FROM systabinfo; > > SELECT ROUND(SUM(chksize)*0.004096,0) Allocated_Chunks > FROM syschunks; > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > ----- End of original message ----- You can also pull page size for each dbspace if release is IDS 10+. sysmaster:sysdbspaces has a "pagesize" column that is INT. HTH - Mark Scranton Xtivia Inc. www.markscranton.com

↪ replying to Mark Scranton (Xtivia Inc.)

Doug Lawry — Thu, 23 Oct 2008 18:01:57 +0100 — source: Usenet: comp.databases.informix

"Mark Scranton (Xtivia Inc.)" <mark.scranton@gmail.com> wrote in message news:15871095-9731-4b07-a735-64a303956281@v72g2000hsv.googlegroups.com... > On Oct 22, 10:55 am, "Floyd Wellershaus" <fl...@fwellers.com> wrote: >> Pretty cool query. Thanks. >> I found this one, and the allocated_tables value you have agrees with the >> output of this. >> >> database sysmaster; >> select dbsname,>> sum( pe_size*4096 ) total_bytes --for 4k page size >> from systabnames, sysptnext >> where partnum = pe_partnum >> and dbsname='whatever' >> group by 1 >> order by 2 desc >> >> ----- Original Message ----- >> From: "LIGHT SCANS" <light_sc...@yahoo.com> >> Sent: Wed, October 22, 2008 10:35 >> Subject:Re: getting full volume info >> >> Hello Floyd, >> >> This gives grand totals of what you're looking for (but it assumes >> that you have 4 kb page size): >> >> DATABASE sysmaster; >> SET ISOLATION TO DIRTY READ;>> >> -- Used_by_Data excludes >> indexes! >> SELECT ROUND(SUM(ti_npdata )*0.004096,0) Used_by_Data__Mb, >> -- Allocated_Tables includes >> indexes! >> ROUND(SUM(ti_nptotal)*0.004096,0) Allocated_Tables >> FROM systabinfo; >> >> SELECT ROUND(SUM(chksize)*0.004096,0) Allocated_Chunks >> FROM syschunks; >> _______________________________________________ >> Informix-list mailing list >> Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list >> >> ----- End of original message ----- > > You can also pull page size for each dbspace if release is IDS 10+. > sysmaster:sysdbspaces has a "pagesize" column that is INT. > > HTH - > Mark Scranton > Xtivia Inc. > www.markscranton.com Indeed. Here's a useful function: CREATE FUNCTION page_size(dbspace VARCHAR(255)) RETURNING INTEGER AS page_size; -- Determine a dbspace's page size in KB -- A dbspace name or part number can be specified -- Doug Lawry, 03/08/2008 DEFINE page_size INTEGER; LET page_size = NULL; IF DBINFO('version', 'major') :: INT < 10 THEN SELECT sh_pagesize INTO page_size FROM sysmaster:sysshmvals; ELSE IF dbspace MATCHES '[1-9]*' THEN LET dbspace = DBINFO('dbspace', dbspace); END IF SELECT pagesize INTO page_size FROM sysmaster:sysdbspaces WHERE name = dbspace; END IF RETURN page_size / 1024; END FUNCTION; So your query becomes: database sysmaster; select dbsname,sum(pe_size) * page_size(dbsname) total_kb from systabnames, sysptnext where partnum = pe_partnum group by 1 order by 2 desc Regards, Doug Lawry

Glossary

isolation level