Convert BYTE column to BLOB

Informix 12.10 Solaris 10 We have an old table with some photos that are stored as simple large objects (in a column type BYTE), and stored in the table. We would like to convert that column to hold smart large objects (column data type BLOB), and be stored in the default sbspace. I read the documentation, and, it appears that we could use ALTER TABLE. However, (I'm pretty sure) that would only effect the desired change for new data, and not the existing data. We want all the existing data to be converted, and to be moved (not just copied) to the default sbspace. Any suggestions on how to achieve this? Would it work to do a big UPDATE in which the column in question is SET to itself (and WHERE 1=1)? Is there a better way? Thank you. DG

Hi, we have done that before (converting byte to blob). But we did not do it in place, we added a new blob column with a different name to the table, ran an update script which converted the content (with a update tablename set newcol_blob = bytecolumn:blob where ....) and finally switched column names and and dropped the old byte column.That was done to a very large table and the conversion process was done in multiple steps (transactions). The benefit here is that the existing content is accessible while the conversion is taking place (for us it took like 20 hours). That is of course possible only if the content does not change while conversion takes place. We only had a short period when the columns were switched and the new column had some null values, because that were rows which were added only just some minutes before columns were switched. After a check that you have no null values, the old column can be dropped. In case you are heading to replication (HDR), do not forget to set the sblob transactional. Hope this helps. Marcus Haarmann Von: "DAVID GROVE" <david.grove@alaska.gov> An: "ids" <ids@iiug.org> Gesendet: Donnerstag, 30. März 2017 19:17:22 Betreff: Convert BYTE column to BLOB [38827] Informix 12.10 Solaris 10 We have an old table with some photos that are stored as simple large objects (in a column type BYTE), and stored in the table. We would like to convert that column to hold smart large objects (column data type BLOB), and be stored in the default sbspace. I read the documentation, and, it appears that we could use ALTER TABLE. However, (I'm pretty sure) that would only effect the desired change for new data, and not the existing data. We want all the existing data to be converted, and to be moved (not just copied) to the default sbspace. Any suggestions on how to achieve this? Would it work to do a big UPDATE in which the column in question is SET to itself (and WHERE 1=1)? Is there a better way? Thank you. DG ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I've used a similar method: - Create a new table with the BLOB type column - Rename the original table - Create a VIEW with the original name from a UNION of the two tables (use the bytetoblob() function to map the byte column in the original table to the new type in the view. - Create INSTEAD OF triggers on the VIEW that redirect inserts, updates, and deletes to the new table. - Create a task that copies a row from the original table the new one and deletes the original row. - When the original table is empty, you can drop the view and the original table and rename the new one if needed. This method will allow you to roll in new apps that understand the new type of the large object column immediately and copy the data in small batches over time. 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 Fri, Mar 31, 2017 at 3:14 AM, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > Hi, > > we have done that before (converting byte to blob). > But we did not do it in place, we added a new blob column with a different > name to the table, > ran an update script which converted the content (with a > update tablename set newcol_blob = bytecolumn:blob where ....) > and finally switched column names and and dropped the old byte column.> That was done to a very large table and the conversion process was done > in multiple steps (transactions). > The benefit here is that the existing content is accessible while the > conversion is > taking place (for us it took like 20 hours). > That is of course possible only if the content does not change while > conversion takes place. > We only had a short period when the columns were switched and the new > column > had some null values, > because that were rows which were added only just some minutes before > columns > were switched. > After a check that you have no null values, the old column can be dropped. > In case you are heading to replication (HDR), do not forget to set the > sblob > transactional. > > Hope this helps. > > Marcus Haarmann > > Von: "DAVID GROVE" <david.grove@alaska.gov> > An: "ids" <ids@iiug.org> > Gesendet: Donnerstag, 30. März 2017 19:17:22 > Betreff: Convert BYTE column to BLOB [38827] > > Informix 12.10 > Solaris 10 > > We have an old table with some photos that are stored as simple large > objects > (in a column type BYTE), and stored in the table. We would like to convert > that column to hold smart large objects (column data type BLOB), and be > stored > in the default sbspace. > > I read the documentation, and, it appears that we could use ALTER TABLE. > However, (I'm pretty sure) that would only effect the desired change for > new > data, and not the existing data. We want all the existing data to be > converted, and to be moved (not just copied) to the default sbspace. > > Any suggestions on how to achieve this? Would it work to do a big UPDATE in > which the column in question is SET to itself (and WHERE 1=1)? > > Is there a better way? > > Thank you. > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1148d220bfe143054c07280d

Thank you, Art. Looks like an innovative approach that provides continuous, seamless availability. For reasons unrelated to moving the data, this table (which is old and not used much) has been previously disabled in the application logic. Hence, for practical purposes, it is static, and can be manipulated without regard to availability. So, probably don't need this approach, this time. But, you can be sure I have stashed this away for future use, in other tables. Regards, DG

Thank you, Marcus. I appreciate the suggestion. Two questions, though... 1) Regarding, "update tablename set newcol_blob = bytecolumn:blob where ....)"... Just to be 100% sure I am understanding, that's a cast expression, right? Should the colon be a double colon? 2) When the operation is finished, if the original BYTE column is stored in the table, would that leave a table with huge holes in it? (That is, a very fragmented [in the disk storage meaning, not the Informix meaning] space on disk.) Would a table reorg be the way to fix it? Or perhaps make a new table, with a BLOB column (instead of BYTE), and use an "INSERT INTO <new_table> SELECT FROM <old_table>..." statement to populate it, before DROPing the original table. Regards, DG

It is an excellent method to disengage schema changes from application changes, though for that purpose I might leave the new table name different from the original for new applications replacing the original table with a VIEW that emulates the original table for old applications until it is not needed any longer. In this way you can move the schema change BEFORE the new code moves and after the new code is in place, if you have to roll back the code change the schema change doesn't have to be rolled back as well since you already know that the VIEW will allow the older version of the application to continue to work until the new code is fixed and rolled out again. Been doing things like this in production for over 20 years. It's why I never understood the need for schema-less or schema-on-read databases. B^) 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 Fri, Mar 31, 2017 at 12:40 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you, Art. > > Looks like an innovative approach that provides continuous, seamless > availability. > > For reasons unrelated to moving the data, this table (which is old and not > used much) has been previously disabled in the application logic. Hence, > for > practical purposes, it is static, and can be manipulated without regard to > availability. So, probably don't need this approach, this time. But, you > can > be sure I have stashed this away for future use, in other tables. > > Regards, > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114b2f1289d31e054c09b5c1

Marcus, David: FYI, you cannot cast a BYTE to a BLOB or TEXT to CLOB, no such cast exists. You have to use the bytetoblob() and texttoclob() functions to perform this kind of update. Now, one COULD define a CAST based on these functions, but it does not exist by default. 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 Fri, Mar 31, 2017 at 12:54 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you, Marcus. > > I appreciate the suggestion. > > Two questions, though... > > 1) Regarding, "update tablename set newcol_blob = bytecolumn:blob where > .....)"... > Just to be 100% sure I am understanding, that's a cast expression, right? > Should the colon be a double colon? > > 2) When the operation is finished, if the original BYTE column is stored in > the table, would that leave a table with huge holes in it? (That is, a very > fragmented [in the disk storage meaning, not the Informix meaning] space on > disk.) Would a table reorg be the way to fix it? Or perhaps make a new > table, > with a BLOB column (instead of BYTE), and use an "INSERT INTO <new_table> > SELECT FROM <old_table>..." statement to populate it, before DROPing the > original table. > > Regards, > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114a04b2d811eb054c09bda3

I think that you could detect which rows might have had any updates on the byte col by doing something like... select row_primary_key where ifx_checksum(byte_col, 0) != ifx_checksum(blob_col, 0); Madison Pruet Retired and Loving it On Friday, March 31, 2017 2:15 AM, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: Hi, we have done that before (converting byte to blob). But we did not do it in place, we added a new blob column with a different name to the table, ran an update script which converted the content (with a update tablename set newcol_blob = bytecolumn:blob where ....) and finally switched column names and and dropped the old byte column.That was done to a very large table and the conversion process was done in multiple steps (transactions). The benefit here is that the existing content is accessible while the conversion is taking place (for us it took like 20 hours). That is of course possible only if the content does not change while conversion takes place. We only had a short period when the columns were switched and the new column had some null values, because that were rows which were added only just some minutes before columns were switched. After a check that you have no null values, the old column can be dropped. In case you are heading to replication (HDR), do not forget to set the sblob transactional. Hope this helps. Marcus Haarmann Von: "DAVID GROVE" <david.grove@alaska.gov> An: "ids" <ids@iiug.org> Gesendet: Donnerstag, 30. März 2017 19:17:22 Betreff: Convert BYTE column to BLOB [38827] Informix 12.10 Solaris 10 We have an old table with some photos that are stored as simple large objects (in a column type BYTE), and stored in the table. We would like to convert that column to hold smart large objects (column data type BLOB), and be stored in the default sbspace. I read the documentation, and, it appears that we could use ALTER TABLE. However, (I'm pretty sure) that would only effect the desired change for new data, and not the existing data. We want all the existing data to be converted, and to be moved (not just copied) to the default sbspace. Any suggestions on how to achieve this? Would it work to do a big UPDATE in which the column in question is SET to itself (and WHERE 1=1)? Is there a better way? Thank you. DG ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Art, I am not in the least intending to be argumentative. Just not sure I fully understand you, or perhaps, the documentation; I find this example of an apparent use of a CAST, in the Knowledge Center for 12.10 ( https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.ddi.doc/ids _ddi_340.htm ): The following example shows how to use an explicit cast to convert a BYTE column value from the catalog table in the stores_demo database to a BLOB column value and update the catalog table in the superstores_demo database: UPDATE catalog SET advert = ROW ( (SELECT cat_photo::BLOB FROM stores_demo:catalog WHERE catalog_num = 10027), advert.caption) WHERE catalog_num = 10027 I guess I need to do some experiments. DG

I just did a little experiment. I created a new table (with a new name) which was an exact duplicate of the original table, except that, instead of the BYTE column, I created a BLOB column. Then I did an "INSERT INTO <new_table> SELECT... FROM <old_table> WHERE... ". For the BYTE column, I used the following: <BYTE_column>::BLOB. The query ran successfully, and when I do a SELECT from the new table, I get the proper values, including a BLOB that is a photo that I can view. DG

Hi Art, converting to blob with a cast works, I have done this multiple times. It is also documented. Vice versa does not work. Marcus Haarmann Von: "Art Kagel" <art.kagel@gmail.com> An: "ids" <ids@iiug.org> Gesendet: Freitag, 31. März 2017 19:00:51 Betreff: Re: Convert BYTE column to BLOB [38834] Marcus, David: FYI, you cannot cast a BYTE to a BLOB or TEXT to CLOB, no such cast exists. You have to use the bytetoblob() and texttoclob() functions to perform this kind of update. Now, one COULD define a CAST based on these functions, but it does not exist by default. 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 Fri, Mar 31, 2017 at 12:54 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you, Marcus. > > I appreciate the suggestion. > > Two questions, though... > > 1) Regarding, "update tablename set newcol_blob = bytecolumn:blob where > .....)"... > Just to be 100% sure I am understanding, that's a cast expression, right? > Should the colon be a double colon? > > 2) When the operation is finished, if the original BYTE column is stored in > the table, would that leave a table with huge holes in it? (That is, a very > fragmented [in the disk storage meaning, not the Informix meaning] space on > disk.) Would a table reorg be the way to fix it? Or perhaps make a new > table, > with a BLOB column (instead of BYTE), and use an "INSERT INTO <new_table> > SELECT FROM <old_table>..." statement to populate it, before DROPing the > original table. > > Regards, > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114a04b2d811eb054c09bda3 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Great! Just great! Now I have to figure out why when I tried it I got "No explicit cast byte to blob" or something like that. On my phone now. Will check it out again tomorrow. Art On Mar 31, 2017 20:08, "DAVID GROVE" <david.grove@alaska.gov> wrote: > I just did a little experiment. > > I created a new table (with a new name) which was an exact duplicate of the > original table, except that, instead of the BYTE column, I created a BLOB > column. Then I did an "INSERT INTO <new_table> SELECT... FROM <old_table> > WHERE... ". For the BYTE column, I used the following: <BYTE_column>::BLOB. > > The query ran successfully, and when I do a SELECT from the new table, I > get > the proper values, including a BLOB that is a photo that I can view. > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114a04b20b3ec0054c24b089