Replace sbspace with larger?

Informix 12.10 Solaris 10 I have an sbspace consisting of one chunk (symbolic link). I would like to replace that drive and make a much larger sbspace (still only one chunk). Will the following work for replacing the sbspace (a "last checkpoint" restore is acceptable): 1) Do a checkpoint and go to quiescent mode. 2) Take a fresh level 0 backup. 3) Point the sym link to the new drive. 4) Do a cold restore. 5) Make the new chunk extendable, and extend it to new capacity. 6) Bring Informix back to multiuser mode. Thank you. DG

Original post: Informix 12.10 Solaris 10 I have an sbspace consisting of one chunk (symbolic link). I would like to replace that drive and make a much larger sbspace (still only one chunk). Will the following work for replacing the sbspace (a "last checkpoint" restore is acceptable): 1) Do a checkpoint and go to quiescent mode. 2) Take a fresh level 0 backup. 3) Point the sym link to the new drive. 4) Do a cold restore. 5) Make the new chunk extendable, and extend it to new capacity. 6) Bring Informix back to multiuser mode. Thank you. DG Response: I don't think this will work as I just tried to mark a chunk in a smart blobspace as extendable and got this: execute function task("modify chunk extendable","2"); (expression) FAILED: Storage Provisioning - BLOBspace, Smart BLOBspace, and mirrored chunks cannot be extended. 1 row(s) retrieved. Was trying it out on 12.10.xC6. Jacques Renaut IBM Informix Advanced Support

Thank you so much. That is highly significant. I had not tried that, because that database is on a production machine. I do plan to find another environment where I can mock up a test, however. But, your results would seem to be absolute and unambiguous. Informix would appear simply NOT to permit extension of sbspace chunks. Does this mean that the only way to consolidate an old sbspace into a new sbspace with (fewer) larger chunks, is to do an ASCII export/import (whether using IBM's utilities, or Art's)? DG

not sure, I have not tried that, but the idea would be to a) add a new (larger) sdbspace on separate partitions b) alter the table(s) to place the blob cloumns in this new dbspace c) drop the old dbspace Might work ... all in-place Marcus Haarmann Von: "DAVID GROVE" <david.grove@alaska.gov> An: "ids" <ids@iiug.org> Gesendet: Dienstag, 15. November 2016 20:46:24 Betreff: Re: Replace sbspace with larger? [38151] Thank you so much. That is highly significant. I had not tried that, because that database is on a production machine. I do plan to find another environment where I can mock up a test, however. But, your results would seem to be absolute and unambiguous. Informix would appear simply NOT to permit extension of sbspace chunks. Does this mean that the only way to consolidate an old sbspace into a new sbspace with (fewer) larger chunks, is to do an ASCII export/import (whether using IBM's utilities, or Art's)? DG ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Original post: not sure, I have not tried that, but the idea would be to a) add a new (larger) sdbspace on separate partitions b) alter the table(s) to place the blob cloumns in this new dbspace c) drop the old dbspace Might work ... all in-place Marcus Haarmann Von: "DAVID GROVE" <david.grove@alaska.gov> An: "ids" <ids@iiug.org> Gesendet: Dienstag, 15. November 2016 20:46:24 Betreff: Re: Replace sbspace with larger? [38151] Thank you so much. That is highly significant. I had not tried that, because that database is on a production machine. I do plan to find another environment where I can mock up a test, however. But, your results would seem to be absolute and unambiguous. Informix would appear simply NOT to permit extension of sbspace chunks. Does this mean that the only way to consolidate an old sbspace into a new sbspace with (fewer) larger chunks, is to do an ASCII export/import (whether using IBM's utilities, or Art's)? DG Response: I had thought about the alter table and modifying the put clause to specify a new smart blob space, however, I don't think that actually moves the old existing smart blobs out of the current smartblob space. I think that only changes where new smartblobs would go. I would say that would need to be tested for sure. I do know that onload/onunload would not work as that utility is documented to not work for tables with smartblobs. You might be able to do something tricky with alter table using the put to modify the smartblob space for the column, and then also run an alter fragment init (which I think would trigger an entire table rebuild, which then might also move the smartblobs from the old smartblob space to the new). Again, that would need to be tested, but if this did trigger an entire table rebuild which did move the smartblobs, you would need to be aware of the logical logging requirements for this action. Otherwise, yeah I believe the only remaining options would be the ascii unload/loads. Jacques Renaut IBM Informix Advanced Support

I guess the essence of the issue is how to move an sbspace. I, too, had thought using ALTER, but, 1) I also think it would not move existing objects; and, 2) To move manually, I would need to be absolutely sure to identify every table that has a column whose object is stored in the sbspace in question. I'm not certain how to do the second item. That is, how to identify every table, without fail, that has objects that are stored in a particular sbspace. With dbspaces, one can inspect (using Server Studio , for example) a dbspace and determine what objects are contained therein. If I could do that for an sbspace, I could ascertain what tables have objects stored in the sbspace, and then be sure to manually manipulate all those tables to accomplish the move. Is there a way to determine what tables store objects in a particular sbspace? Thank you, once again. DG

original post: I guess the essence of the issue is how to move an sbspace. I, too, had thought using ALTER, but, 1) I also think it would not move existing objects; and, 2) To move manually, I would need to be absolutely sure to identify every table that has a column whose object is stored in the sbspace in question. I'm not certain how to do the second item. That is, how to identify every table, without fail, that has objects that are stored in a particular sbspace. With dbspaces, one can inspect (using Server Studio , for example) a dbspace and determine what objects are contained therein. If I could do that for an sbspace, I could ascertain what tables have objects stored in the sbspace, and then be sure to manually manipulate all those tables to accomplish the move. Is there a way to determine what tables store objects in a particular sbspace? Thank you, once again. DG Response: I'm sure that it would be possible to determine which tables store objects in a particular sbspace (or given a particular table report the sbspace name for any clob/blob columns). That sort of information can be extracted using the functions documented in the datablade api programmers manual (specifically you can get the smart blob space name of a large object with the mi_lo_specget_sbspace() function). However, the issue would be there is no directly supplied tool that would do that (and I'm not sure if there's any 3rd party tool that might do this), but I believe it could be written using those api calls. It's probably not too bad if you only consider clob and blob data types specifically...however, large opaque types that might spill over into smartblob spaces would probably make it more difficult to generalize. Jacques Renaut IBM Informix Advanced Support

Thank you, Jacques. I was vaguely aware of that (and similar) funcitons that one could use to develop some code to do that. I can talk to our developers. I should have been clearer: I was referring to SQL commands. For one-off, ad hoc stuff, it sure is nice to be able to do things interactively from the SQL command line. The "admin" or "task" command structures are examples, but, I don't think they provide the functionality being discussed in this case. I thought I might be able to cobble up some queries-- maybe from system tables-- that would permit me to enumerate all tables that have large objects. Since we have only one sbspace, I know where all the objects will be! Then just go through the tables one by one, and move the objects. DG

Maybe the right answer is to partially follow the original plan of restoring to the new device using the symbolic link change and then, instead of making the existing chunk extendable, just adding new chunks to the sbspace to achieve the same result. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > JACQUES RENAUT > Sent: Tuesday, November 15, 2016 16:11 PM > To: ids@iiug.org > Subject: Re: Replace sbspace with larger? [38155] > > original post: > > I guess the essence of the issue is how to move an sbspace. > > I, too, had thought using ALTER, but, > 1) I also think it would not move existing objects; and, > 2) To move manually, I would need to be absolutely sure to identify > every table that has a column whose object is stored in the sbspace in > question. > > I'm not certain how to do the second item. That is, how to identify > every table, without fail, that has objects that are stored in a > particular sbspace. > With dbspaces, one can inspect (using Server Studio , for example) a > dbspace and determine what objects are contained therein. If I could do > that for an sbspace, I could ascertain what tables have objects stored > in the sbspace, and then be sure to manually manipulate all those > tables to accomplish the move. > > Is there a way to determine what tables store objects in a particular > sbspace? > > Thank you, once again. > > DG > > Response: > > I'm sure that it would be possible to determine which tables store > objects in a particular sbspace (or given a particular table report the > sbspace name for any clob/blob columns). That sort of information can > be extracted using the functions documented in the datablade api > programmers manual (specifically you can get the smart blob space name > of a large object with the > mi_lo_specget_sbspace() function). However, the issue would be there is > no directly supplied tool that would do that (and I'm not sure if > there's any 3rd party tool that might do this), but I believe it could > be written using those api calls. It's probably not too bad if you only > consider clob and blob data types specifically...however, large opaque > types that might spill over into smartblob spaces would probably make > it more difficult to generalize. > > Jacques Renaut > IBM Informix Advanced Support > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you. OK, I have an existing chunk size of 8 Gib. I want to replace it with a new chunk of 32 Gib. So, I could use onspaces to create a new chunk of 8GiB in the new 32 GiB disk slice. Then add a second chunk of size 24 GiB that is just the remainder of the disk slice (point to the same slice and use an offset of 8 Gib). That would be workable. Just seems "inelegant" that Informix would make me have to do this. DG

Inelegantly replying to my own post, but, might be helpful to future readers. I found this IBM Tech Note: http://www-01.ibm.com/support/docview.wss?uid=swg21692357 That helps with the actual moving of sblobs. Now all I need to do is formulate queries to enumerate all tables with sblobs being stored in a designated sbspace. Or, due to the simplicity of our case (only one sbspace), formulate a query to enumerate all tables that contain an sblob. DG

Perhaps this query will return all tables that have at least one column that is an sblob: SELECT DISTINCT tabname FROM syscolumns c, systables t WHERE c.tabid = t.tabid AND c.coltype=41; So, I would ripple through this list of tables returned by the above query, and then use LOCOPY to get the sblobs into the new sbspace. Then, delete the old sbspace. (All after ALTERing the tables, first, to PUT sblobs in the new sbspace.) DG

Hi David, incidentally I am a little familiar with that technote, and also with the=20 one linked from it at its bottom: How to determine which sbspace an sblob=20 resides in? ... which might be of help with your newest question. (Sorry I didn't pay enough attention to this discussion earlier.) Andreas From: "DAVID GROVE" <david.grove@alaska.gov> To: ids@iiug.org Date: 16.11.2016 00:42 Subject: Re: Replace sbspace with larger? [38159] Sent by: ids-bounces@iiug.org Inelegantly replying to my own post, but, might be helpful to future=20 readers.=20 I found this IBM Tech Note:=20 http://www-01.ibm.com/support/docview.wss?uid=3Dswg21692357=20 That helps with the actual moving of sblobs.=20 Now all I need to do is formulate queries to enumerate all tables with=20 sblobs=20 being stored in a designated sbspace. Or, due to the simplicity of our=20 case=20 (only one sbspace), formulate a query to enumerate all tables that contain = an=20 sblob.=20 DG=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Original post: Thank you, Jacques. I was vaguely aware of that (and similar) funcitons that one could use to develop some code to do that. I can talk to our developers. I should have been clearer: I was referring to SQL commands. For one-off, ad hoc stuff, it sure is nice to be able to do things interactively from the SQL command line. The "admin" or "task" command structures are examples, but, I don't think they provide the functionality being discussed in this case. I thought I might be able to cobble up some queries-- maybe from system tables-- that would permit me to enumerate all tables that have large objects. Since we have only one sbspace, I know where all the objects will be! Then just go through the tables one by one, and move the objects. DG Reply: Oh...well I came up with a query like this that should return the names of any tables that use clobs or blobs in a particular database. select tabname from systables a, syscolumns b, sysxtdtypes c where a.tabid = b.tabid andb.extended_id = c.extended_id and (c.name = "clob" or c.name = "blob"); Jacques Renaut IBM Informix Advanced Support

Now that I see your query, I also see that my proposed query is incorrect. Thank you. I'm thinking that I can run SQL using the LOCOPY function, as described in the IBM Tech note "How to move a table's sblobs" on each of the tables returned by your query. I'm also thinking that that operation will (as the name "LOCOPY" suggests) copy, not literally "move", the sblobs. That is, the blobs will still remain in the original sbspace. But, then I could safely use onspaces to delete (by force, if necessary) the original sbspace. However, in the spirit of paranoia and "trust, but verify", it would be nice to be able to check through all the sblobs in that sbspace to see if there be any whose LOHandles are stored in any table OTHER than the list of those returned by your query. Of course, this should be impossible, but, since DBAs are paranoid, I'd still like to check. So... QUESTION: Any ideas on how I can examine an sbspace and determine which tables reference objects physically stored in that sbspace? Thank you. Regards, DG

orignial query: Now that I see your query, I also see that my proposed query is incorrect. Thank you. I'm thinking that I can run SQL using the LOCOPY function, as described in the IBM Tech note "How to move a table's sblobs" on each of the tables returned by your query. I'm also thinking that that operation will (as the name "LOCOPY" suggests) copy, not literally "move", the sblobs. That is, the blobs will still remain in the original sbspace. But, then I could safely use onspaces to delete (by force, if necessary) the original sbspace. However, in the spirit of paranoia and "trust, but verify", it would be nice to be able to check through all the sblobs in that sbspace to see if there be any whose LOHandles are stored in any table OTHER than the list of those returned by your query. Of course, this should be impossible, but, since DBAs are paranoid, I'd still like to check. So... QUESTION: Any ideas on how I can examine an sbspace and determine which tables reference objects physically stored in that sbspace? Thank you. Regards, DG Response: So I think as Andreas mentioned, in the link you mentioned "http://www-01.ibm.com/support/docview.wss?uid=swg21692357"; at the bottom there is a "related information" section which has a link to this article: How to determine which sbspace an sblob resides in - http://www-01.ibm.com/support/docview.wss?uid=swg21692353 that article appears to include a query which takes the large object structure and casts it to various things and then will give you the smartblob space number for where that smart blob is stored...so that query should then be able to be used for verification. The issue would be it would need to be run against every table with a smartblob, and it would do a sequential scan of every row for all those tables to verify the smartblob space number for every large object in the table. Jacques Renaut IBM Informix Advanced Support

If you UPDATE your table using LOCOPY as described, this will literally=20 "move" the sblobs, from old to new space. Well, what it actually does, it=20 will create a new copy in new sbspace and decrement old sblobs's reference = count by 1; should this reference count go to 0 through this, the old=20 sblob would be deleted. So if your old sblobs would not be deleted by=20 this procedure, this means there are other references to them. As for which table a specific sblob belongs to: internally an sblob (its=20 LO header) has two fields 'tabid' and 'colno', but these a) aren't=20 reliable and b) aren't exposed anywhere (afaik). In general an sblob=20 doesn't even need to belong to any table, or it could be pointed to by=20 multiple ones, and (I guess) if a table or rows/fields containing sblobs=20 would be copied to other tables/rows/fields (not using LOCOPY, hence just=20 incrementing ref. counts), that info would be ultimately useless anyway. Cheers, Andreas From: "DAVID GROVE" <david.grove@alaska.gov> To: ids@iiug.org Date: 16.11.2016 19:14 Subject: Re: Replace sbspace with larger? [38163] Sent by: ids-bounces@iiug.org Now that I see your query, I also see that my proposed query is incorrect. = Thank you.=20 I'm thinking that I can run SQL using the LOCOPY function, as described in = the=20 IBM Tech note "How to move a table's sblobs" on each of the tables=20 returned by=20 your query. I'm also thinking that that operation will (as the name=20 "LOCOPY"=20 suggests) copy, not literally "move", the sblobs. That is, the blobs will=20 still remain in the original sbspace. But, then I could safely use=20 onspaces to=20delete (by force, if necessary) the original sbspace.=20 However, in the spirit of paranoia and "trust, but verify", it would be=20 nice=20 to be able to check through all the sblobs in that sbspace to see if there = be=20 any whose LOHandles are stored in any table OTHER than the list of those=20 returned by your query. Of course, this should be impossible, but, since=20 DBAs=20 are paranoid, I'd still like to check. So...=20 QUESTION: Any ideas on how I can examine an sbspace and determine which=20 tables=20 reference objects physically stored in that sbspace?=20 Thank you.=20 Regards,=20 DG=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20