Error -9810 -12048 when deleting/updating/unloadin

-------------------------------------------------------------------------------- Dear all: We're facing a disturbing error when trying to delete, update or unload one row with a blob field. When you try to modify or unload it, IDS returns the following error: 9810: Smart-large-object error. 12048: Smart Large Objects: unique ID does not match. Smart large objectprobably deleted. I have run the oncheck -cD and -cI, but no errors were detected. The only option I see is to build a new table, an load the rest of the rows, but I'd like to know if anybody knows another way to solve it. FYI: We are running IDS 9.40 FC5 on HP-UX 11i Thank you in advance for your assistance.

At some point someone has probably deleted the smart blob space blobs where once these smart blobs resided with onspaces -d -f and now you have invalid LOHandles. OPTION 1 (RECOMMENDED) Your best option would be, as you have already identified, to unload/load all rows but the ones with bad smart blobs. For the rows with bad smart blobs, select all columns except for the smart blob column, just select a blank string "" to get no value in the unload file (assuming you are using dbaccess UNLOAD), then reload these rows into a new table. To get rid of the bad rows, you should be able to delete all but the rows with bad smart blobs, then try to drop the table, you might find you need to put the table into raw mode and change the blob to no log ( alter table table_name put blob_col in ( sbspace) (no log). OPTION 2 (NOT RECOMMENDED) The only other way I know of is fairly hairy chested and involves creating a shadow table with exactly the same data types except that the smart blobs columns become char(76) data type columns. You then need to change the partnum of the original table to that of the shadow table (update systables set partnum = partnum_of_shadow_table where tabname = table_with_bad_rows.. very ugly), bounce the database server, then set the char(76) column to null for the rows with bad smart blobs, (ie: update table_with_bad_rows set char76_column = null where uniqueID inlist_of_uniqueIDs of rows with bad smart blobs (!! DON'T USE ROWID though !!). Then you have to swap the partnums back and restart the database server again. You then need to delete the rows in systables, syscolumns (and possibly other system catalog tables) which were related to the shadow table and bounce the DBServer once more. As you might surmise, this option is not supported by IBM and generally is NOT RECOMMEDNDED. There are a few other gotchas to look out for as well with this option, fragmented tables springs to mind. If you are bold enough to try it out, make a full level 0 backup BEFORE you start. Stuart McCann Integrated Spatial Services Unit Information Communication & Technology Department of Lands, Bathurst Phone: (02) 63328285 stuart.mccann@lands.nsw.gov.au -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LUIS VENTURA Sent: Thursday, 11 October 2007 3:59 AM To: ids@iiug.org Subject: Error -9810 -12048 when deleting/updating/unloadin [10099] ------------------------------------------------------------------------ -------- Dear all: We're facing a disturbing error when trying to delete, update or unload one row with a blob field. When you try to modify or unload it, IDS returns the following error: 9810: Smart-large-object error. 12048: Smart Large Objects: unique ID does not match. Smart large object probably deleted. I have run the oncheck -cD and -cI, but no errors were detected. The only option I see is to build a new table, an load the rest of the rows, but I'd like to know if anybody knows another way to solve it. FYI: We are running IDS 9.40 FC5 on HP-UX 11i Thank you in advance for your assistance. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Department of Lands. This email message has been swept by MIMEsweeper for the presence of computer viruses. ***************************************************************