How to enumerate tables referencing BLOBs in a particular sbspace?

Informix 12.10.FC14 Solaris 10 1/13 I have an Informix instance that contains several databases. Only one of these databases contains any BLOBs (smart large objects). There are 12 tables that contain, in total, over 650,000 BLOBs (mostly photos). I wanted to move all the sblobs to a new sbspace. So, I created an SPL that used a FOREACH loop to ripple through every table that had at least one sblob column, and, for that table, executed an UPDATE command that used LOCOPY() to assign the BLOB column(s) to themselves. At first, I thought it worked like a charm. I used oncheck -pe to count the number of LOs, and there were over 650K. Just to be sure, I also used the same oncheck command to count the number of LOs that remained in the original sbspace. I was expecting to find 0. But, there are 76 LOs remaining. They are all very tiny (3 - 5 pages). So, I have a mystery. Just what are these objects, and what table(s) are referencing them? Thus, my question: How can I determine a list of table(s)/column(s) that are referencing these mysterious LOs? IOW, I have some LOs in an sbspace. How do I know what table(s) they "belong to"? Thank you. David Grove ------------------------------ David Grove ------------------------------ #Informix

Are these remaining 76 LOs even referenced by anyone? -> oncheck -cS <sbspace_name> -> Ref Cnt column > 0 ? ------------------------------ Andreas Legner ------------------------------

Well, I'll be. All 76 of them have Reference Count = 0. How can that be? What does it even mean to have an object stored in an sbspace that is invisible, inaccessible, non-retrievable, whose existence is unknown and unknowable (by any SQL command). How can this even happen? If the reference count to a smart large object becomes zero, shouldn't it be deleted from the sbspace? Is there a way to make these zombies go away? Thank you for asking the question, and causing me to check that. It would not have (and did not) occurred to me that there could be objects stored that no table in the database even "knew" about. I mean, how would they even get there, if not INSERTed into a table? Does a tree falling in the wilderness make a sound? No doubt I lack full knowledge of the whole LO thing. Actually, I admit I do have that deficiency. :) Regards, DG P.S. I guess this gets philosophical real fast. Certainly, it does help to bring in to focus the difference between the concepts of a Primary Key (which is isomorphic to existence, uniqueness, and identity) and an Object ID (which has nothing to do with any of those attributes-- it's just a handle with which to manipulate an object, which may or may not be unique, etc.). ------------------------------ David Grove ------------------------------

"Is there a way to make these zombies go away?" I should have researched first (before posting that question). The answer is: 'onspaces -cl' Thank you, again. DG ------------------------------ David Grove ------------------------------

After I got rid of the LOBs with a reference count of 0, I looked some more at the output of 'oncheck -cS <blobspace>'. I observed the following three lines in the thousands of lines of that output: Large Objects ID Ref Size Allocced Creat Last Sbs# Chk# Seq# Cnt (Bytes) Pages Extns Flags Modified ---- ---- ----- ---- ---------- -------- ----- ----- ------------------------ 4 4 419173 1 0 0 0 L-N-H Wed Jun 10 11:55:55 2020 4 4 419177 1 0 0 0 L-N-H Wed Jun 10 11:55:55 2020 4 4 419201 1 0 0 0 L-N-H Wed Jun 10 11:55:55 2020 Q1 : What does it mean to have a reference count of "1" for an object of size "0 bytes", allocated "0 pages", and with "0 extents"? Q2 : How can I determine what columns of what tables are referring to these (alleged) objects? Thank you for any comments. DG ------------------------------ David Grove ------------------------------

The Question I originally raised about how to enumerate tables/columns that reference a particular SBLOB would seem to have an answer. IBM Tech Support directed me to this thread in the forum: https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home/digestviewer/viewthread?GroupId=4147&MessageKey=9560395b-149e-485b-b203-033d203e84fb&CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=digestviewer The final post by Milan Refaj has the answer. He posted a script that generates a query that will display the table, column, and rowid of all tables that reference a particular sblob in a particular sbspace. DG ------------------------------ David Grove ------------------------------

Regarding "Q1:" above. I was curious about it. Wondered whether there might be a bug in 'oncheck' (reporting a non-reference to nonexistent objects). Or maybe some mysterious objects exist that I am not aware of and don't understand. So I opened a low-level (Priority 4) Tech Support case. They said they didn't know. It's not important enough to escalate it, so case closed. DG ------------------------------ David Grove ------------------------------

Hi David, try this, with yourdb and yoursbspace: $ rm a $ touch a $ dbaccess mydb <<! create table test_sblob (i int, b blob) put b in (mysbspace); insert into test_sblob values (1, filetoblob("a", "client"));! $ oncheck -cS mysbspaceYou're simply looking at a zero length (aka. empty) sblob, as a valid value in a table's sblob column ;-) It will, btw., indeed not consume any user space in your sbspace, only one LO header in the LO_hdr_partn metadata partition. BR, Andreas ------------------------------ Andreas Legner ------------------------------