How can I use new sblob space

Hi, I created a new sblob space and created a table with a clob column and specified that it use the new sblob space. But it still puts everything in the default sblob space. create table newtable{ id serial8,notes clob } put notes in (newsblob) ... I vaguely remembered something about needing to run a L0 backup so I did that; didn't make any difference. I'm afraid to change the default in the onconfig; we've got another app that has its own db and sblob space on that server. What am I doing wrong? I'm using version 10.00.FC6. Thanks, Bevis

On Jan 25, 2:12 pm, "Bevis Kennedy" <bkenn...@utah.gov> wrote: > I created a new sblob space and created a table with a clob column and specified > that it use the new sblob space. But it still puts everything in the default sblob space. > > create table newtable{ > id serial8,> notes clob} > put notes in (newsblob) ... > > I vaguely remembered something about needing to run a L0 backup so I did that; > didn't make any difference. I'm afraid to change the default in the onconfig; we've > got another app that has its own db and sblob space on that server. > What am I doing wrong? > > I'm using version 10.00.FC6. I'm going to presume you didn't actually cut'n'paste your SQL since your table body is shown as a big comment (inside { ... }). I also don't see what you're doing wrong. When you add a smart blob space, the onspaces program reminds you to do a level 0 archive of the root dbspace - so the L0 backup is necessary. However, that's all it takes in my experience, and to make sure I wasn't forgetting anything, I tried it out. In fact, I checked by creating a smart blobpsace called sbspace (and it got used according to onstat -d) and then, just to make sure defaults weren't kicking in, I created a second smart blobspace called auxspace, recreated the table to use it, and was able to show that there was less free space after inserting a smart blob into the table than there was before. Now, admittedly I was using IDS 11.10.FC1 on Solaris 10 - so there might be a bug that you're running into. OTOH, I'm not convinced that's likely. This is the SQL I used for the second experiment: + drop table blob_data; + create table blob_data(i integer not null, d datetime year to second not null, b blob not null) put b in (auxspace); + insert into blob_data values(1, current, filetoblob('/work1/jleffler/ bin/sqlcmd.64','client')); Before, 'onstat -d' showed 9475 free pages; after, it showed 9185 free pages. FWIW: my ONCONFIG file had empty values in SBSPACENAME and SYSSBSPACENAME. If you become convinced that non-empty values in either or both of those are material to your problem, post to c.d.i saying so and I'll check again. -=JL=-

It turns out that 10.00.FC6 will not allow me to use a sblob space unless that sblob is defined in SBSPACENAME. And then ONLY the sblob space defined in SPSPACENAME. We have an open ticket with IBM; apparently this is a known issue. IBM is checking to see if this problem was fixed with FC7 or if we will have to move to IDS 11. Thanks for you help. Bevis >>> Jonathan Leffler <jonathan.leffler@gmail.com> 1/26/2008 9:15 AM >>> On Jan 25, 2:12 pm, "Bevis Kennedy" <bkenn...@utah.gov> wrote: > I created a new sblob space and created a table with a clob column and specified > that it use the new sblob space. But it still puts everything in the default sblob space. > > create table newtable{ > id serial8,> notes clob} > put notes in (newsblob) ... > > I vaguely remembered something about needing to run a L0 backup so I did that; > didn't make any difference. I'm afraid to change the default in the onconfig; we've > got another app that has its own db and sblob space on that server. > What am I doing wrong? > > I'm using version 10.00.FC6. I'm going to presume you didn't actually cut'n'paste your SQL since your table body is shown as a big comment (inside { ... }). I also don't see what you're doing wrong. When you add a smart blob space, the onspaces program reminds you to do a level 0 archive of the root dbspace - so the L0 backup is necessary. However, that's all it takes in my experience, and to make sure I wasn't forgetting anything, I tried it out. In fact, I checked by creating a smart blobpsace called sbspace (and it got used according to onstat -d) and then, just to make sure defaults weren't kicking in, I created a second smart blobspace called auxspace, recreated the table to use it, and was able to show that there was less free space after inserting a smart blob into the table than there was before. Now, admittedly I was using IDS 11.10.FC1 on Solaris 10 - so there might be a bug that you're running into. OTOH, I'm not convinced that's likely. This is the SQL I used for the second experiment: + drop table blob_data; + create table blob_data(i integer not null, d datetime year to second not null, b blob not null) put b in (auxspace); + insert into blob_data values(1, current, filetoblob('/work1/jleffler/ bin/sqlcmd.64','client')); Before, 'onstat -d' showed 9475 free pages; after, it showed 9185 free pages. FWIW: my ONCONFIG file had empty values in SBSPACENAME and SYSSBSPACENAME. If you become convinced that non-empty values in either or both of those are material to your problem, post to c.d.i saying so and I'll check again. -=JL=- _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list