How to convert a blob column type clob to blob in

I have a table in DB2 with column type CLOB, I would like to convert this to a BLOB type. My approach here is to create a new column with BLOB type, copy all the data from CLOB column to BLOB column, drop the CLOB column and rename the BLOB column. However, I am not sure how to do the second step i.e. update data from CLOB column to BLOB column. What is the function of DB2 that will let me do this? Thanks in advance.

回答1:

As you are on Db2-LUW V11.1 and AIX, you can consider using the stored procedure ADMIN_MOVE_TABLE to do the work on-line. Test carefully on development and testing environments before promoting to production. Generally, it is easier to use the stored procedure than to do it manually, especially if you are learning, although you need SQLADM or DBADM rights, and carefully study the documentation.

One important detail is that you should ensure your table stores its data, index(es), and LONG data in separate tablespaces. That is best practice and applies regardless of which method you choose to implement.

See the ADMIN_MOVE_TABLE documentation at: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055069.html

If you don't want to use the stored procedure, you can also do it manually, although the method you choose may differ according to the number-of-rows in table and average-length-of-CLOB-column.

For tiny row-counts with small average-length-of-CLOB column:

- add the blob column with appropriate size to the table (alter table ... add column )
- populate the new column with an UPDATE statement set blobcolumn = blob(clobcolumn).
- alter table ... drop column clobcolumn
- offline reorg table
- runstats

For non-trivial row counts or large CLOBS

- create a new dedicated LONG tablespace if necessary
- create a new table, with the required blob column, and without the clob column, ensuring the LONG IN clause specifies correct tablespace
- declare a cursor for SELECT from oldtable , using BLOB(clobcolumn)
- use load from cursor to populate the new table
- runstats new table
- drop old table
- rename new table to be same as old table.

回答2:

I think you can directly change the column type in your Db2 table:

ALTER TABLE {TABLE NAME} ALTER COLUMN {COLUMN NAME} SET DATA TYPE BLOB({SIZE})

CLOB is very similar to BLOB, main difference is that CLOB is used to store large textual contents(which have character encoding info), BLOB is just a long binary string(e.g. a binary file).