More Smart Large Object Problems

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

This is really serious for us. We're talking mug shots, etc., of criminals. Now, when someone updates a photo, none of the other tables that (allegedly) reference the same photo will be updated. Rapist/murderer John Doe will now have simultaneous, multiple, different photos. Please tell me I'm wrong. DG

Dear David: I will look at your previous emails to get a better idea of what you're doing, but I can say my company does the same thing (public safety), and we do not experience the issues you are describing when storing images in sbspaces. Best regards, Martin M. Graney Queues Enforth Development, Inc. 92 Montvale Ave Suite 4350 Stoneham, MA 02180-3647 781-870-1131 This electronic message contains information which may be privileged, confidential, or otherwise protected from disclosure. The information contained herein is intended for the addressee or recipient only. If you are not the addressee, or not the intended recipient, any disclosure, copying, distribution, or use of the contents of this message (including any attachments) is prohibited. If you have received this electronic message in error, please notify the sender immediately and destroy the original message and all copies. (Queues Enforth Development, Inc.) > On Dec 2, 2016, at 7:09 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > > This is really serious for us. We're talking mug shots, etc., of criminals. > Now, when someone updates a photo, none of the other tables that (allegedly) > reference the same photo will be updated. Rapist/murderer John Doe will now > have simultaneous, multiple, different photos. > > Please tell me I'm wrong. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --Apple-Mail-B8F817D1-2533-428B-8C9D-611124D91BF1

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } David, Do you have a good understanding where the rows are referencing the same SBLOB? Are they in the same table or in differing tables? If you have multiple rows referencing the same SBLOB and then unloaded/reloaded those rows, then you would have lost the many-to-one relationship that existed prior to the unload. From the sound of it, you need to re-establish that many-to-one relationship after the reload. If that is the goal, then your immediate goal is to reset the SBLOB reference so that the multiple rows are pointing to the same SBLOB (i.e.picture). Am I understanding the problem correctly? If this is the goal, then the first thing that you want to do is to find which rows have the same picture. You can use ifx_checksum do create a checksum of those rows which have the same data even though they are pointing to different instantiations of that picture. Assume that there is only one table and the columns are col1 primary key, col2 byte. Then you could do something like. Select ifx_checksum(col2, 0), col1 ordered by 1; This would give you a list of the rows which appear to have the same picture (col2) even though they are not in the same SBLOB. As an example suppose the output looked like 123 Row-23123. Row-431173. Row-601173. Row-1935 Then to link the two rows to the same SBLOB, you would need to execute something like ... Update tab1 set col2 = (select col2 from tab1 where col1 = 'Row-431') wherecol1 = "Row-23"; Of course you would need to do further verification that the pictures for Row-32 and Row-431 were indeed identical because while highly unlikely it is possible that two SBLOBS could have the same checksum (1 in 4 Billion probability). And needless to say, you need to do this in a test environment first. Sent from Yahoo Mail for iPad On Friday, December 2, 2016, 6:09 PM, DAVID GROVE <david.grove@alaska.gov> wrote: This is really serious for us. We're talking mug shots, etc., of criminals. Now, when someone updates a photo, none of the other tables that (allegedly) reference the same photo will be updated. Rapist/murderer John Doe will now have simultaneous, multiple, different photos. Please tell me I'm wrong. DG ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi David, it's not quite as you think: if two rows, in same or in different=20 tables, refer to the same sblob, this typically would be the result of a=20 copy from first row to second row, either whole row or only the sblob=20 field. Alternatively you'd have created the sblob using specific API and = then assigned it the two rows. If you just inserted the same photo into=20 two rows, you'd naturally have got two separate copies. Now, given an sblob referred to by two rows/tables, if you update this=20 sblob in one of these rows/tables, you't not modify that specific sblob,=20 but instead you'd create a new sblob, update that specific row/table's=20 sblob pointer to point to the new sblob, and decrement the old, unchanged=20 sblob's reference count. So the problem you're referring to is not new and not introduced by the=20 duplication of what used to be single sblobs. I think the idea of 1-to-n here really only was storage savings, but never = any guaranteed data sharing. HTH, Andreas From: "DAVID GROVE" <david.grove@alaska.gov> To: ids@iiug.org Date: 02.12.2016 23:46 Subject: More Smart Large Object Problems [38222] Sent by: ids-bounces@iiug.org Informix 12.10=20 Solaris 10=20 Until recently, I have not been too involved with sblobs. Now that I have=20 to=20 deal with them, I am finding continual problems-- both theoretical and=20 administrative/management (as in "lack of")-- with these things. Basic=20 violations of fundamental Relational Theory appears to be rampant.=20 Recently, I wanted to replace some drives and as a result, needed to=20 replace a=20 smaller sbspace with a larger one on a different drive.=20 I had an existing 8GB sbspace. I created a new 30GB sbspace. Then, using=20 the=20 query helpfully supplied by Mr. Renaut (=20 http://members.iiug.org/forums/ids/index.cgi/read/38162 ) to identify all=20 tables with sblobs, and then applying the method described in the IBM=20 Technote=20 "How to move a tables's sblobs" to all the tables identified by the query, = I=20 moved all the sblobs to the new sbspace.=20 The sblobs, which occupied just a little less than 8 GB in the old=20 sbspace,=20 now require 12GB in the new sbspace.=20 I encountered this expansion before when doing dbimport and dbexport, and=20 the=20 explanation is that when multiple tables reference the same sblob (i.e.,=20 have=20 a row and column with the same LO HANDLE), the actual sblob is exported=20 (and=20 imported) twice.=20 Now, I did not do an export/import to move the sblobs. Just used the=20 method=20 described in the Technote (it uses the LOCOPY function.)=20 Could it be that this is the exact same problem as when using=20 dbexport/dbimport?=20 If so, this is, IMO, grossly wrong. A Relational Database should not=20 denormalize data, of its own accord. If that is what happened, then data=20 identity and referential integrity for those sblobs is destroyed. That=20 means=20 there WILL BE deletion anomalies (and insertion anomalies and update=20 anomalies), as dexplicated historically at great length and depth by Codd=20 &=20 Date. (OK, developed by Codd, explained by Date.) When someone deletes one = of=20 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=20 same=20 individual. Users won't know which is correct. Etc. Etc.=20 Someone please tell me I am totally misunderstanding this and that there=20 is=20 some other explanation (and a remedy).=20 What can I do to make Informix honor referential integrity for sblobs. I=20 fear=20 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=20 single=20 sblob) has now become multiple LO HANDLEs, each referencing a different=20 instantiation of the same sequence of bits, then it can't be "walked=20 back". I=20 fear big trouble.=20 I truly hope I am going off half cocked, and am misunderstanding the=20 situation. Someone slap me down by telling me I am totally off base. But,=20 if=20 not, what is the solution to the problem of the same object being=20 replicated=20 with a different LO HANDLE, when using the Informix-supplied, built-in=20 functions? Is there any way to return our multiple tables that reference=20 the=20 same object to having the same LO HANDLE, referencing a SINGLE instance of = the=20 object?=20 Thank you.=20 DG=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20