Usable space available in an sbspace?

Informix 12.10 Solaris 10 I'm finding it challenging to be working with sblobs and sbspaces. Even the terminology is a bit confusing to me. For example, A BYTE type is a blob, but a BLOB type is not a blob but a smart blob. Or maybe I have that wrong. But, that's a rabbit trail. Q: Is there a way to determine how much usable space remains in an sbspace? We have an sbspace that is used to store photos. Server Studio showed it as nearly full (over 90%). We deleted about 150,000 rows in the photo table. Now the photo table is in a dbspace, and the photo column is type BLOB, and so, is actually a LOHandle that references the actual sblob which is stored in the (we only have one) sbspace. Now, many (probably most, but maybe not all) of the photos are also contained (well, their LOHandles) in another table. The two tables have many rows that refer to the same physical sblob. After the deletion of the 150K rows in the table, Server Studio shows the same amount of usage in the sbspace. I'm thinking that this is because 1) deleting rows in one of the tables wouldn't actually delete the sblob, if it were also being referenced by the other table; and, 2) even if some sblobs were actually deleted, this operation is still just marking those objects as deleted. No "giving back extents" or repacking or shrinking. So, even though there some of the existing space might now be available to be overwritten for new storage, the space already previously allocated would still be at whatever the "high water mark" was/is. I think. But, I want to know, for sure. Definitively. Q: How can I determine, for sure, how much space is currently available for actual new storage of photos (sblobs) in that existing sbspace? Thank you. DG

Clarifying correction: "Now the photo table is in a dbspace, and the photo column is type BLOB, and so, is actually a LOHandle that references the actual sblob which is stored in the (we only have one) sbspace." Should be: "Now the photo table is in a dbspace, and the photo column is type BLOB, so, it is actually a LOHandle (that references the actual sblob) which is stored in the table; the actual sblog is stored in the (we only have one) sbspace." DG

Doesn't look like anyone has risen to this. sbchunks are special. In sysmaster:syschunks, aside from pages unallocated (nfree) there are also pages allocated but unused (udfree). On top of which, when you delete a "row" or object, you are not necessarily making a page available for re-use, so it may not be counted in the above. This latter case is no different than a regular data page, where space may have freed up, but is not counted as free until after a page compression. I don't have a real good answer for you. Some code I wrote last week to deal with an sbchunk includes: SELECT sum(c.nfree*d.pagesize)+sum(c.udfree*d.pagesize) as ct FROM sysmaster:sysdbspaces d, sysmaster:syschunks c WHERE d.dbsnum = c.dbsnum AND d.name = <dbsname> AND is_sbchunk=1 -- kind of moot is you know the dbsname. cheers j. On 11/16/16 2:08 PM, DAVID GROVE wrote: > Informix 12.10 > Solaris 10 > > I'm finding it challenging to be working with sblobs and sbspaces. Even the > terminology is a bit confusing to me. For example, A BYTE type is a blob, but > a BLOB type is not a blob but a smart blob. Or maybe I have that wrong. But, > that's a rabbit trail. > > Q: Is there a way to determine how much usable space remains in an sbspace? > > We have an sbspace that is used to store photos. Server Studio showed it as > nearly full (over 90%). We deleted about 150,000 rows in the photo table. Now > the photo table is in a dbspace, and the photo column is type BLOB, and so, is > actually a LOHandle that references the actual sblob which is stored in the > (we only have one) sbspace. Now, many (probably most, but maybe not all) of > the photos are also contained (well, their LOHandles) in another table. The > two tables have many rows that refer to the same physical sblob. > > After the deletion of the 150K rows in the table, Server Studio shows the same > amount of usage in the sbspace. > > I'm thinking that this is because > 1) deleting rows in one of the tables wouldn't actually delete the sblob, if > it were also being referenced by the other table; and, > 2) even if some sblobs were actually deleted, this operation is still just > marking those objects as deleted. No "giving back extents" or repacking or > shrinking. > > So, even though there some of the existing space might now be available to be > overwritten for new storage, the space already previously allocated would > still be at whatever the "high water mark" was/is. > > I think. > > But, I want to know, for sure. Definitively. > > Q: How can I determine, for sure, how much space is currently available for > actual new storage of photos (sblobs) in that existing sbspace? > > Thank you. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >