Database Sizes

Posted in 2007

Summary

Sue wanted per-database space usage (several databases share dbspaces) and a way to measure smart blob sizes without extracting them, on IDS 9.21. The suggested query joining systables to sysmaster:systabinfo (tabname, ti_npused) gives per-table page usage within a database, but doesn't cover sblobspaces, and LENGTH() on the blob failed with "Routine (length) cannot be resolved" because the smartblob type has no length routine. A respondent pointed to IBM's sblob DataBlade (developerWorks article) as the only way to get smart blob sizes, reportedly working on 9.4/10. No further confirmation from the poster.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Storage & Space Management, Stored Procedures & SPL

SUE SIMMONDS — Fri, 01 Jun 2007 07:55:47 — source: IIUG Forums & Mailing Lists

Is there an easy way to find out how much space/pages each database is actually using ? Using ISA I can see how much space/pages are allocated/used in each dbspace but in most cases there is more than 1 database in each dbspace and I need the data for individual databases. I also have 1 database which is split over numerous dbspaces which has a blob column. Is there any way to identify how large the blob is without extracting it from the database ?

↪ replying to SUE SIMMONDS

Obnoxio The Clown — Fri, 01 Jun 2007 08:07:43 — source: IIUG Forums & Mailing Lists

SUE SIMMONDS wrote: > Is there an easy way to find out how much space/pages each database is > actually using ? > > Using ISA I can see how much space/pages are allocated/used in each dbspace > but in most cases there is more than 1 database in each dbspace and I need the > data for individual databases. > > I also have 1 database which is split over numerous dbspaces which has a blob > column. Is there any way to identify how large the blob is without extracting > it from the database ? > I have the strangest sense of deja vu ... all over again. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The Clown

SUE SIMMONDS — Fri, 01 Jun 2007 08:35:50 — source: IIUG Forums & Mailing Lists

Apologies for the feeling of Deja-Vu but my Previous posting was on Non-Technical Forum not on the IDS Forum, so I have posted again in the correct place. Your previous posting said From each database you can issue: SELECT tabname, ti_npused FROM systables t, sysmaster:systabinfo i WHERE t.partnum = i.ti_partnum Unfortunately this does not include Smart Blob spaces used by some of my databases. And as far as identifying how large a blob is SELECT Length(blob) FROM table ??? this does not return the size of the blob it simply gives the following error Routine (length) cannot be resolved.

↪ replying to SUE SIMMONDS

Obnoxio The Clown — Fri, 01 Jun 2007 08:53:02 — source: IIUG Forums & Mailing Lists

SUE SIMMONDS said: > Apologies for the feeling of Deja-Vu but my Previous posting was on > Non-Technical Forum not on the IDS Forum, so I have posted again in the > correct place. > > Your previous posting said > >>From each database you can issue: > > SELECT tabname, ti_npused > FROM systables t, sysmaster:systabinfo i > WHERE t.partnum = i.ti_partnum> > Unfortunately this does not include Smart Blob spaces used by some of my > databases. > > And as far as identifying how large a blob is > > SELECT Length(blob) FROM table ??? > > this does not return the size of the blob it simply gives the following > error > > Routine (length) cannot be resolved. What version of IDS are you using? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The Clown

SUE SIMMONDS — Fri, 01 Jun 2007 08:56:35 — source: IIUG Forums & Mailing Lists

I am running 9.21

↪ replying to SUE SIMMONDS

Obnoxio The Clown — Fri, 01 Jun 2007 10:38:01 — source: IIUG Forums & Mailing Lists

SUE SIMMONDS wrote: > I am running 9.21 > > Why? It's WAY out of support. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to SUE SIMMONDS

Obnoxio The Clown — Fri, 01 Jun 2007 11:09:38 — source: IIUG Forums & Mailing Lists

SUE SIMMONDS said: > Apologies for the feeling of Deja-Vu but my Previous posting was on > Non-Technical Forum not on the IDS Forum, so I have posted again in the > correct place. > > Your previous posting said > >>From each database you can issue: > > SELECT tabname, ti_npused > FROM systables t, sysmaster:systabinfo i > WHERE t.partnum = i.ti_partnum> > Unfortunately this does not include Smart Blob spaces used by some of my > databases. > > And as far as identifying how large a blob is > > SELECT Length(blob) FROM table ??? > > this does not return the size of the blob it simply gives the following > error > > Routine (length) cannot be resolved. Ah. Your smartblob UDT apparently does not have a length routine then. :o( I would guess that there is some way of rooting it out of sysmaster, but since I don't have any UDT's (and I'm not creating some for the hell of it!) I can't help you much further. -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to SUE SIMMONDS

Penza Kenneth at MITTS — Fri, 01 Jun 2007 11:15:32 — source: IIUG Forums & Mailing Lists

Sue, The only way to get and information for smartblobs is to use the sblob datablade from http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle /db_sblob.html. I am using it to get the size of smartblob on IDS 9.4 and 10, but it should work on 9.2. Regards, Kenneth Penza Systems Engineer Systems and Database Services Service Management Department Malta Information Technology & Training Services Ltd Please read our Legal Notice: http://emailpolicy.mitts.gov.mt P Please consider your environmental responsibility before printing this e-mail. END OF TEXT -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SUE SIMMONDS Sent: 01 June 2007 10:36 To: ids@iiug.org Subject: Re: Database Sizes [9273] Apologies for the feeling of Deja-Vu but my Previous posting was on Non-Technical Forum not on the IDS Forum, so I have posted again in the correct place. Your previous posting said >From each database you can issue: SELECT tabname, ti_npused FROM systables t, sysmaster:systabinfo i WHERE t.partnum = i.ti_partnum Unfortunately this does not include Smart Blob spaces used by some of my databases. And as far as identifying how large a blob is SELECT Length(blob) FROM table ??? this does not return the size of the blob it simply gives the following error Routine (length) cannot be resolved. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary

dbspace