db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Evan Leonard <evan.leon...@gmail.com>
Subject Increasing the length of a blob column
Date Tue, 02 Jun 2009 20:45:46 GMT

Hello again,

As a corollary to my upgrade question, I have another issue I would  
like to get some input on.

Several old databases in production were created with blob columns at  
the then default blob size of 1mb.  How can I go about upgrading these  
columns to be longer?

This is the best approach I've come up with so far:

ALTER TABLE binarydata ADD COLUMN data2 blob(128M)
UPDATE binarydata SET data2 = data;
ALTER TABLE binarydata DROP COLUMN data RESTRICT;
RENAME COLUMN binarydata.data2 TO data;

The issue with this approach is that some deployed databases are  
nearly 1GB in size with a large portion of that being in this table  
I'm trying to adjust. When I ran this query as a test on such a  
database, the size of the db on disk balloon to over twice its  
starting size, and then failed because I was running it on a temp  
drive without enough storage to complete, so I haven't been able to  
fully test even if this will work.

Is there a good reason why blob columns can't have their size adjusted  
directly?  I've tried:

ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(128M)

but this failed with an error.

Any ideas?

Evan

Mime
View raw message