Blob Questions

Hello Folks, O/S AIX 6 IDS 11.70.FC5GE I have some blob questions. I have a table with 4 text columns that keeps running out of pages in the tablespace. Since fragmentation in Growth Edition is not an option, I want to move the blobs from the tablespace to a blobspace. I have 3 questions around that: 1. Will the blobs replicate in HDR if they reside in a blobspace? 2. Are there any java coding requirements to make this work in the application? 3. Do the blob pages count against the tablespace page limit? TIA, Dan

Inline... On Wed, Oct 22, 2014 at 9:17 PM, DAN MUELLER <ddmueller@intercall.com> wrote: > Hello Folks, > > O/S AIX 6 > IDS 11.70.FC5GE > > I have some blob questions. I have a table with 4 text columns that keeps > running out of pages in the tablespace. Since fragmentation in Growth > Edition > is not an option, I want to move the blobs from the tablespace to a > blobspace. > I have 3 questions around that: > > 1. Will the blobs replicate in HDR if they reside in a blobspace? > No. Only logged SLOBs 2. Are there any java coding requirements to make this work in the > application? > Between in table BLOBS and BLOBS in different tablespace no. From BLOBs to SLOBS possibly... I'd have to check... But I know customers who did it and apparently is was easy (apart the time it took to move them...) > 3. Do the blob pages count against the tablespace page limit? > No Regards.. > > TIA, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e0111bffee54917050608d0d1

Hi, if you have columns of type "text", these can be put into a blobspace, but HDR will not work ! And yes, by putting them in a blobspace will not count for the max pages limit. Just putting columns in another dbspace has no effect on the application. Better solution: modify the columns to be of type CLOB and put them in a smart Blobspace. A smart Blobspace can be logged and should work with HDR. Java Code changes are not required, if I recall correctly the API (setString/getString methods should work). We have done things like that with Oracle and Informix in the past. But I am not sure about the conversion of text column content to clob. Maybe you will have to write a program to do the conversion, I never did that (byte to blob can be done with a cast (::blob), but not vice-versa) What we did: table a ( col1 byte ); alter table a add (col1_blob blob);(either provide a storage clause or define a default sdbspace) update a set col1_blob = col1::blob .... The general strategy should be to add a new column to the existing table for each of the text colums, perform the updates (copy the old text content to the new columns) and then rename the columns and drop the old ones. That way, you can do the trick without interrupting the application. Hope this helps. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "DAN MUELLER" <ddmueller@intercall.com> An: ids@iiug.org Gesendet: Mittwoch, 22. Oktober 2014 22:17:59 Betreff: Blob Questions [34018] Hello Folks, O/S AIX 6 IDS 11.70.FC5GE I have some blob questions. I have a table with 4 text columns that keeps running out of pages in the tablespace. Since fragmentation in Growth Edition is not an option, I want to move the blobs from the tablespace to a blobspace. I have 3 questions around that: 1. Will the blobs replicate in HDR if they reside in a blobspace? 2. Are there any java coding requirements to make this work in the application? 3. Do the blob pages count against the tablespace page limit? TIA, Dan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Orginal post: Hello Folks, O/S AIX 6 IDS 11.70.FC5GE I have some blob questions. I have a table with 4 text columns that keeps running out of pages in the tablespace. Since fragmentation in Growth Edition is not an option, I want to move the blobs from the tablespace to a blobspace. I have 3 questions around that: 1. Will the blobs replicate in HDR if they reside in a blobspace? 2. Are there any java coding requirements to make this work in the application? 3. Do the blob pages count against the tablespace page limit? TIA, Dan Response: Well, I think the answer to 1 is going to stop this. 1) No...blobs in blobspaces do not get logged in the logical log, so they will not replicate via HDR. (They get backed up via ontape when you back up your logs, but the actual blob pages themselves do not get into the logical logs) 2) Don't know 3) No blobs in blobspace pages don't count towards the maximum number of pages tracke by 1 partition page/tablespace. Jacques Renaut IBM Informix Advanced Support APD Team

Dan: The answers to your three questions are the same: No. No. No. Blobspace blob pages do not replicate to HDR or RSS. Blobs look the same to application code whether they are in blobspace or tablespace. The blobspace pages are not part of the 16million page limit per partition. Three suggestions if you need HDR: 1) Use wider pages, that will reduce the impact of the blobs in tablespace. 2) Switch to smart blobs in a logged smartblob space. These will replicate. 3) Move the TEXT columns to child tables, one to each, duplicating the parent's primary key columns w/foreign keys to link them back to the parent. That will quadruple the number of pages available for the table(s). It will also give you flexibility. Eventually you could add column(s) to the parent table identifying which child table contains each blob object. If you try #3 you could possibly avoid modifying your applications by renaming the parent table and replacing it with a view that reassembles the original unified record from the four new tables. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Oct 22, 2014 at 4:17 PM, DAN MUELLER <ddmueller@intercall.com> wrote: > Hello Folks, > > O/S AIX 6 > IDS 11.70.FC5GE > > I have some blob questions. I have a table with 4 text columns that keeps > running out of pages in the tablespace. Since fragmentation in Growth > Edition > is not an option, I want to move the blobs from the tablespace to a > blobspace. > I have 3 questions around that: > > 1. Will the blobs replicate in HDR if they reside in a blobspace? > 2. Are there any java coding requirements to make this work in the > application? > 3. Do the blob pages count against the tablespace page limit? > > TIA, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11343368c8f56505060ae984