IDS Forum

Re: Convert BYTE column to BLOB

Posted By: Art Kagel
Date: Friday, 31 March 2017, at 9:55 a.m.

In Response To: Re: Convert BYTE column to BLOB (Marcus Haarmann)

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

Messages In This Thread

• Convert BYTE column to BLOB
  DAVID GROVE -- Thursday, 30 March 2017, at 1:17 p.m.
  • Re: Convert BYTE column to BLOB
    Marcus Haarmann -- Friday, 31 March 2017, at 3:14 a.m.
    • Re: Convert BYTE column to BLOB
      Art Kagel -- Friday, 31 March 2017, at 9:55 a.m.
      • Re: Convert BYTE column to BLOB
        DAVID GROVE -- Friday, 31 March 2017, at 12:40 p.m.
        • Re: Convert BYTE column to BLOB
          Art Kagel -- Friday, 31 March 2017, at 12:57 p.m.
    • Re: Convert BYTE column to BLOB
      DAVID GROVE -- Friday, 31 March 2017, at 12:54 p.m.
      • Re: Convert BYTE column to BLOB
        Art Kagel -- Friday, 31 March 2017, at 1:00 p.m.
        • Re: Convert BYTE column to BLOB
          DAVID GROVE -- Friday, 31 March 2017, at 7:54 p.m.
          • Re: Convert BYTE column to BLOB
            DAVID GROVE -- Friday, 31 March 2017, at 8:08 p.m.
            • Re: Convert BYTE column to BLOB
              Art Kagel -- Saturday, 1 April 2017, at 9:09 p.m.
        • Re: Convert BYTE column to BLOB
          Marcus Haarmann -- Saturday, 1 April 2017, at 1:39 a.m.
    • Re: Convert BYTE column to BLOB
      Madison Pruet -- Friday, 31 March 2017, at 4:56 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.