Blob Text Byte
A user had many tables whose row size exceeded the page size (lots of CHAR(2000) and TEXT columns) and asked whether to move them to blob/sbspaces or convert to VARCHAR/LVARCHAR. Replies: TEXT is awkward (not directly updatable); CLOB in an sbspace is an option but heavy overhead for ~2K strings. Art Kagel suggested LVARCHAR (with MAX_FILL_DATA_PAGES set to 1), or better, redesigning the schema into child/parallel tables. Converting CHAR to TEXT/CLOB can't be done with a simple ALTER; it needs a new column/table populated by a host program (e.g. ESQL/C with loc_t handling). He also confirmed creating dbspaces with larger page sizes (up to 16K) helps rows under ~16,356 bytes, but wider rows still need remainder pages. No single outcome chosen by the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone, In my production environment, I have a lot of tables with row buffer than page size. It's because we have a lot of char(2000), and text columns. They are on the same place that normal rows. I dont know what can be more efficent: change those columns to a blob ou sblos space, or change those columns to a varchar or lvarchar? First of all, Can I do it? Change a column char (2000) to a text in a blob space? Thanks, André Luiz Rufino
Hello, André. Text fields are very limited, specific, and cannot be updated, for example, in a direct way. Check the link below: http://pic.dhe.ibm.com/infocenter/informix/v121/topic/com.ibm.sqlr.doc/ids_sqr_1 45.htm Lvarchar fields will allow you to use the same dbspaces as the table itself, and can be converted on the fly, but will demand you more storage than a limited field. If you need a proficient way to store the fields, I would recommend CLOB field, in a specific sbspace. It will mostly depends of what kind of information are you recording, what is it used for, if you need to update it sometimes, you could explain that, and our friends should help you to the best way to go. Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: andre_rufino@msn.com > Subject: Blob Text Byte [30471] > Date: Tue, 11 Jun 2013 07:43:05 -0400 > > Hi everyone, > In my production environment, I have a lot of tables with row buffer than page > size. It's because we have a lot of char(2000), and text columns. They are on > the same place that normal rows. I dont know what can be more efficent: change > those columns to a blob ou sblos space, or change those columns to a varchar > or lvarchar? First of all, Can I do it? Change a column char (2000) to a text > in a blob space? > Thanks, > André Luiz Rufino > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Well. Is there a way to use a column (that is text today) and convert it to byte? I really need to storage a text context in a better place. I got a lot of reminder pages today... André > To: ids@iiug.org > From: alexandre@briug.org > Subject: RE: Blob Text Byte [30472] > Date: Tue, 11 Jun 2013 08:26:58 -0400 > > Hello, André. > Text fields are very limited, specific, and cannot be updated, for example, in > a direct way. Check the link below: > > http://pic.dhe.ibm.com/infocenter/informix/v121/topic/com.ibm.sqlr.doc/ids_sqr_1 45.htm > > Lvarchar fields will allow you to use the same dbspaces as the table itself, > and can be converted on the fly, but will demand you more storage than a > limited field. > > If you need a proficient way to store the fields, I would recommend CLOB > field, in a specific sbspace. > > It will mostly depends of what kind of information are you recording, what is > it used for, if you need to update it sometimes, you could explain that, and > our friends should help you to the best way to go. > > Hope it helps. > Regards. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 > > IBM Information Management Informix Technical Professional > > IBM Infosphere DataStage Technical Professional > Informix Senior DBA - Orizon Brasil > BRIUG website administrator > Informix independent consultant > > > To: ids@iiug.org > > From: andre_rufino@msn.com > > Subject: Blob Text Byte [30471] > > Date: Tue, 11 Jun 2013 07:43:05 -0400 > > > > Hi everyone, > > In my production environment, I have a lot of tables with row buffer than > page > > size. It's because we have a lot of char(2000), and text columns. They are > on > > the same place that normal rows. I dont know what can be more efficent: > change > > those columns to a blob ou sblos space, or change those columns to a varchar > > or lvarchar? First of all, Can I do it? Change a column char (2000) to a > text > > in a blob space? > > Thanks, > > André Luiz Rufino > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
You can easily make these columns LVARCHAR(2000), however, note that you may not save as much disk space as you think you will, especially if the very wide columns are mostly full. If they are not, you MUST set MAX_FILL_DATA_PAGES to '1' in your ONCONFIG file otherwise the engine will only put another row on an existing page if the maximum length of the new row would fit in the free space on the pages. With this parameter set then the engine will put a new row on a page as long as the current size of the row plus some allowance for growth will fit. This may still leave some unused space on a page even if a small row might fit there. However, my Data Architect head tells me that you should probably reexamine those very wide columns. Why were they created: 1. to hold a single string entity that may or may not exist but will nearly fill the column if it does - Make a child table with the wide CHAR column(s) in it. 2. to hold a string that may grow or shrink over time if its value is changed - Change to an LVARCHAR (or VARCHAR if less than 255 bytes) 3. to allow for the entry of ad hoc text of varying length - Change to a child table containing an additional sequence number key column and a MUCH smaller CHAR type column (say 60 to 100 bytes) to hold multiple lines of text one per row. - This has the advantage of not limiting the user to <N> bytes of text (I personally HATE those little comment boxes on web sites that have a little number under them that counts down from 1000 while I type. Much better to let the users enter whatever they want. You can never waste more than 59 bytes (plus the key lengths) if you only use part of one of the child table comments! - Another added bonus is that you can retain any formatting that the user used to enter the text which might make it easier to read for the consumer of the user comments! Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 7:43 AM, André Luiz Rufino <andre_rufino@msn.com>wrote: > Hi everyone, > In my production environment, I have a lot of tables with row buffer than > page > size. It's because we have a lot of char(2000), and text columns. They are > on > the same place that normal rows. I dont know what can be more efficent: > change > those columns to a blob ou sblos space, or change those columns to a > varchar > or lvarchar? First of all, Can I do it? Change a column char (2000) to a > text > in a blob space? > Thanks, > André Luiz Rufino > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c285146197c204dee09be8
Oops forgot, converting the CHAR column to a TEXT or CLOB type is not trivial and cannot be done using a simple ALTER. You would have to create a new TEXT or CLOB column (or a whole new table), copy the string into the TEXT or CLOB (or the whole row to the new table) using a host application (can't do it in SQL directly), then drop the old CHAR column or drop the old table and rename the new one and recreate any relational constraints on it. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 7:43 AM, André Luiz Rufino <andre_rufino@msn.com>wrote: > Hi everyone, > In my production environment, I have a lot of tables with row buffer than > page > size. It's because we have a lot of char(2000), and text columns. They are > on > the same place that normal rows. I dont know what can be more efficent: > change > those columns to a blob ou sblos space, or change those columns to a > varchar > or lvarchar? First of all, Can I do it? Change a column char (2000) to a > text > in a blob space? > Thanks, > André Luiz Rufino > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c37420b535b104dee0b14e
Yes, I imagined that. Any host aplicattion can do that ? PHP, Java for example? > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Blob Text Byte [30475] > Date: Tue, 11 Jun 2013 09:15:56 -0400 > > Oops forgot, converting the CHAR column to a TEXT or CLOB type is not > trivial and cannot be done using a simple ALTER. You would have to create > a new TEXT or CLOB column (or a whole new table), copy the string into the > TEXT or CLOB (or the whole row to the new table) using a host application > (can't do it in SQL directly), then drop the old CHAR column or drop the > old table and rename the new one and recreate any relational constraints on > it. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 7:43 AM, André Luiz Rufino > <andre_rufino@msn.com>wrote: > > > Hi everyone, > > In my production environment, I have a lot of tables with row buffer than > > page > > size. It's because we have a lot of char(2000), and text columns. They are > > on > > the same place that normal rows. I dont know what can be more efficent: > > change > > those columns to a blob ou sblos space, or change those columns to a > > varchar > > or lvarchar? First of all, Can I do it? Change a column char (2000) to a > > text > > in a blob space? > > Thanks, > > André Luiz Rufino > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c37420b535b104dee0b14e > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
With a bit of care, yes. You would have to read the data from the 'char' version of the table, insert a record mapping the text from the char column into a BLOB type host variable. In ESQL/C foor a dumb TEXT blob you would PREPARE the INSERT, DESCRIBE it, change the LOCTYPE of the TEXT column to LOC_MEMORY or LOC_USER (preparing the open, close, and read functions and linking them to the loc_t structure for the column) and either execute the INSERT or open a CURSOR for it and PUT rows to the cursor FLUSHing it periodically. Not sure that I would use a BLOB for a text field that's only 2K though. It's a lot of overhead and programming to support it. See my other message or other suggestions. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 9:42 AM, André Luiz Rufino <andre_rufino@msn.com>wrote: > Yes, I imagined that. Any host aplicattion can do that ? PHP, Java for > example? > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: Blob Text Byte [30475] > > Date: Tue, 11 Jun 2013 09:15:56 -0400 > > > > Oops forgot, converting the CHAR column to a TEXT or CLOB type is not > > trivial and cannot be done using a simple ALTER. You would have to create > > a new TEXT or CLOB column (or a whole new table), copy the string into > the > > TEXT or CLOB (or the whole row to the new table) using a host application > > (can't do it in SQL directly), then drop the old CHAR column or drop the > > old table and rename the new one and recreate any relational constraints > on > > it. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 7:43 AM, André Luiz Rufino > > <andre_rufino@msn.com>wrote: > > > > > Hi everyone, > > > In my production environment, I have a lot of tables with row buffer > than > > > page > > > size. It's because we have a lot of char(2000), and text columns. They > are > > > on > > > the same place that normal rows. I dont know what can be more efficent: > > > change > > > those columns to a blob ou sblos space, or change those columns to a > > > varchar > > > or lvarchar? First of all, Can I do it? Change a column char (2000) to > a > > > text > > > in a blob space? > > > Thanks, > > > André Luiz Rufino > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a11c37420b535b104dee0b14e > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01493c1e2a620304dee1bb8d
Well, my situation is a "little" bit diferent. There is a list of tables bellow. It is ordering by row size Tables RowSize log_audit_logix 32204log_dados_trigger_antiga 32081wb_teste_email 30003frm_form_component 12738frm_comp_property_value 12156frm_comp_property_val_param 12154frm_component_property 12106frm_component 12103frm_param_component 11308crm_parametro_funcao 10106wb_cont_arq_wms 10000h_wb_cont_arq_wms 10000 I was thinking about one thing. Could It be good if create a different page size dbspace? André > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Blob Text Byte [30478] > Date: Tue, 11 Jun 2013 10:36:59 -0400 > > With a bit of care, yes. You would have to read the data from the 'char' > version of the table, insert a record mapping the text from the char column > into a BLOB type host variable. In ESQL/C foor a dumb TEXT blob you would > PREPARE the INSERT, DESCRIBE it, change the LOCTYPE of the TEXT column to > LOC_MEMORY or LOC_USER (preparing the open, close, and read functions and > linking them to the loc_t structure for the column) and either execute the > INSERT or open a CURSOR for it and PUT rows to the cursor FLUSHing it > periodically. > > Not sure that I would use a BLOB for a text field that's only 2K though. > It's a lot of overhead and programming to support it. See my other > message or other suggestions. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 9:42 AM, André Luiz Rufino > <andre_rufino@msn.com>wrote: > > > Yes, I imagined that. Any host aplicattion can do that ? PHP, Java for > > example? > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: Blob Text Byte [30475] > > > Date: Tue, 11 Jun 2013 09:15:56 -0400 > > > > > > Oops forgot, converting the CHAR column to a TEXT or CLOB type is not > > > trivial and cannot be done using a simple ALTER. You would have to create > > > a new TEXT or CLOB column (or a whole new table), copy the string into > > the > > > TEXT or CLOB (or the whole row to the new table) using a host application > > > (can't do it in SQL directly), then drop the old CHAR column or drop the > > > old table and rename the new one and recreate any relational constraints > > on > > > it. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 7:43 AM, André Luiz Rufino > > > <andre_rufino@msn.com>wrote: > > > > > > > Hi everyone, > > > > In my production environment, I have a lot of tables with row buffer > > than > > > > page > > > > size. It's because we have a lot of char(2000), and text columns. They > > are > > > > on > > > > the same place that normal rows. I dont know what can be more efficent: > > > > change > > > > those columns to a blob ou sblos space, or change those columns to a > > > > varchar > > > > or lvarchar? First of all, Can I do it? Change a column char (2000) to > > a > > > > text > > > > in a blob space? > > > > Thanks, > > > > André Luiz Rufino > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a11c37420b535b104dee0b14e > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e01493c1e2a620304dee1bb8d > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Yes. You can create dbspaces with a pagesize of any multiple of the server's default pagesize (so 4K on AIX, Windows, and Mac, but 2K everywhere else). So, 10K pages would work for tables with a rowsize smaller than 10212 bytes (10240 - 24 bytes overhead and less 4 bytes for the row's slot table entry), 12K pages for rows narrower than 12,260 bytes, 14K pages for rows narrower than 14,308 bytes, and 16K for rows up to 16,356 bytes wide. Rows wider than 16,536 will have to have at least one remainder page, there's no way around that. I still have to think you could redesign that schema to avoid such wide rows. Even if you broke the row into several parallel tables containing columns used by subsets of your application code. I did that once on a project at a client and turned a 4 hour report run into a 5 minute one, improved the responsiveness of the interactive application that maintained the table, and vastly improved the user experience by being able to add partial save points for the user who had to fill out a multi-screen form as bonuses! Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 11:32 AM, André Luiz Rufino <andre_rufino@msn.com>wrote: > Well, my situation is a "little" bit diferent. There is a list of tables > bellow. It is ordering by row size > Tables RowSize > log_audit_logix 32204log_dados_trigger_antiga 32081wb_teste_email > 30003frm_form_component 12738frm_comp_property_value > 12156frm_comp_property_val_param 12154frm_component_property > 12106frm_component 12103frm_param_component 11308crm_parametro_funcao > 10106wb_cont_arq_wms 10000h_wb_cont_arq_wms 10000 > I was thinking about one thing. Could It be good if create a different page > size dbspace? > André > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: Blob Text Byte [30478] > > Date: Tue, 11 Jun 2013 10:36:59 -0400 > > > > With a bit of care, yes. You would have to read the data from the 'char' > > version of the table, insert a record mapping the text from the char > column > > into a BLOB type host variable. In ESQL/C foor a dumb TEXT blob you would > > PREPARE the INSERT, DESCRIBE it, change the LOCTYPE of the TEXT column to > > LOC_MEMORY or LOC_USER (preparing the open, close, and read functions and > > linking them to the loc_t structure for the column) and either execute > the > > INSERT or open a CURSOR for it and PUT rows to the cursor FLUSHing it > > periodically. > > > > Not sure that I would use a BLOB for a text field that's only 2K though. > > It's a lot of overhead and programming to support it. See my other > > message or other suggestions. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 9:42 AM, André Luiz Rufino > > <andre_rufino@msn.com>wrote: > > > > > Yes, I imagined that. Any host aplicattion can do that ? PHP, Java for > > > example? > > > > > > > To: ids@iiug.org > > > > From: art.kagel@gmail.com > > > > Subject: Re: Blob Text Byte [30475] > > > > Date: Tue, 11 Jun 2013 09:15:56 -0400 > > > > > > > > Oops forgot, converting the CHAR column to a TEXT or CLOB type is not > > > > trivial and cannot be done using a simple ALTER. You would have to > create > > > > a new TEXT or CLOB column (or a whole new table), copy the string > into > > > the > > > > TEXT or CLOB (or the whole row to the new table) using a host > application > > > > (can't do it in SQL directly), then drop the old CHAR column or drop > the > > > > old table and rename the new one and recreate any relational > constraints > > > on > > > > it. > > > > > > > > Art > > > > > > > > Art S. Kagel > > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Tue, Jun 11, 2013 at 7:43 AM, André Luiz Rufino > > > > <andre_rufino@msn.com>wrote: > > > > > > > > > Hi everyone, > > > > > In my production environment, I have a lot of tables with row > buffer > > > than > > > > > page > > > > > size. It's because we have a lot of char(2000), and text columns. > They > > > are > > > > > on > > > > > the same place that normal rows. I dont know what can be more > efficent: > > > > > change > > > > > those columns to a blob ou sblos space, or change those columns to > a > > > > > varchar > > > > > or lvarchar? First of all, Can I do it? Change a column char > (2000) to > > > a > > > > > text > > > > > in a blob space? > > > > > Thanks, > > > > > André Luiz Rufino > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > --001a11c37420b535b104dee0b14e > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --089e01493c1e2a620304dee1bb8d > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > *******************************************************************************