-12048 Smart Large Objects: unique ID does not

Hi all, Version : IDS 9.3 FC6 OS : Sun I have a table where one of the column is CLOB type. When i perform a delete on this table, i have the following error: -12048 Smart Large Objects: unique ID does not match. Smart large object probably deleted. According to IBM site, the explanation are as follow: The smart large object being referenced by the smart-large-object pointer has been deleted. Ensure that the smart-large-object pointer is valid and that the smart large object has not been deleted. If you are keeping a smart-large-object handle, then the reference count for the smart large object must be greater than 0 or the database server deletes the smart large object at session end. My questions are: 1. How can delete those record(s) with the error shown above. 2. As explain in the error message above, the pointer is referencing to a smart blob that has been deleted. I don't recall that those records has ever been deleted. What would has caused this error? Input is much appreciated.

Several things may have happened here, most likely dropping the smart blob space where once this clob resided with onspaces -d -f spaceName There is no built in way (as far as I am aware ) to "zap" or null out invalid LOHandles. You can, however, construct a dbspace and locate the rows with bad blobs/clobs in there using an alter fragment with expression to isolate rows with bad blobs as the alter fragment only copies the LOHandle, even if it is invalid. Then you can detach that fragment, drop the DBSpace (with onspace -d -f spacename) and remove any entries in the system catalogues to that table, you need to know what your doing. 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 PATRICK LEY Sent: Wednesday, 21 November 2007 2:11 AM To: ids@iiug.org Subject: -12048 Smart Large Objects: unique ID does not [10417] Hi all, Version : IDS 9.3 FC6 OS : Sun I have a table where one of the column is CLOB type. When i perform a delete on this table, i have the following error: -12048 Smart Large Objects: unique ID does not match. Smart large object probably deleted. According to IBM site, the explanation are as follow: The smart large object being referenced by the smart-large-object pointer has been deleted. Ensure that the smart-large-object pointer is valid and that the smart large object has not been deleted. If you are keeping a smart-large-object handle, then the reference count for the smart large object must be greater than 0 or the database server deletes the smart large object at session end. My questions are: 1. How can delete those record(s) with the error shown above. 2. As explain in the error message above, the pointer is referencing to a smart blob that has been deleted. I don't recall that those records has ever been deleted. What would has caused this error? Input is much appreciated. ************************************************************************ ******* 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. ***************************************************************