IDS Forum

More Smart Large Object Problems

Posted By: DAVID GROVE
Date: Friday, 2 December 2016, at 5:45 p.m.

Informix 12.10
Solaris 10

Until recently, I have not been too involved with sblobs. Now that I have to deal with them, I am finding continual problems-- both theoretical and administrative/management (as in "lack of")-- with these things. Basic violations of fundamental Relational Theory appears to be rampant.

Recently, I wanted to replace some drives and as a result, needed to replace a smaller sbspace with a larger one on a different drive.

I had an existing 8GB sbspace. I created a new 30GB sbspace. Then, using the query helpfully supplied by Mr. Renaut ( http://members.iiug.org/forums/ids/index.cgi/read/38162 ) to identify all tables with sblobs, and then applying the method described in the IBM Technote "How to move a tables's sblobs" to all the tables identified by the query, I moved all the sblobs to the new sbspace.

The sblobs, which occupied just a little less than 8 GB in the old sbspace, now require 12GB in the new sbspace.

I encountered this expansion before when doing dbimport and dbexport, and the explanation is that when multiple tables reference the same sblob (i.e., have a row and column with the same LO HANDLE), the actual sblob is exported (and imported) twice.

Now, I did not do an export/import to move the sblobs. Just used the method described in the Technote (it uses the LOCOPY function.)

Could it be that this is the exact same problem as when using dbexport/dbimport?

If so, this is, IMO, grossly wrong. A Relational Database should not denormalize data, of its own accord. If that is what happened, then data identity and referential integrity for those sblobs is destroyed. That means there WILL BE deletion anomalies (and insertion anomalies and update anomalies), as dexplicated historically at great length and depth by Codd & Date. (OK, developed by Codd, explained by Date.) When someone deletes one of these photos, another table that references its own copy of the same photo will not have its photo deleted. There will be multiple photos for the same individual. Users won't know which is correct. Etc. Etc.

Someone please tell me I am totally misunderstanding this and that there is some other explanation (and a remedy).

What can I do to make Informix honor referential integrity for sblobs. I fear that, since the deed is done, I have a situation that is not repairable. I mean, if one LO HANDLE (being used in multiple tables to reference a single sblob) has now become multiple LO HANDLEs, each referencing a different instantiation of the same sequence of bits, then it can't be "walked back". I fear big trouble.

I truly hope I am going off half cocked, and am misunderstanding the situation. Someone slap me down by telling me I am totally off base. But, if not, what is the solution to the problem of the same object being replicated with a different LO HANDLE, when using the Informix-supplied, built-in functions? Is there any way to return our multiple tables that reference the same object to having the same LO HANDLE, referencing a SINGLE instance of the object?

Thank you.

DG

Messages In This Thread

• More Smart Large Object Problems
  DAVID GROVE -- Friday, 2 December 2016, at 5:45 p.m.
  • Re: More Smart Large Object Problems
    DAVID GROVE -- Friday, 2 December 2016, at 7:09 p.m.
    • Re: More Smart Large Object Problems
      Martin M. Graney -- Friday, 2 December 2016, at 9:06 p.m.
    • Re: More Smart Large Object Problems
      Madison Pruet -- Friday, 2 December 2016, at 9:21 p.m.
  • Re: More Smart Large Object Problems
    Andreas Legner -- Saturday, 3 December 2016, at 7:04 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.