db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Increasing the length of a blob column
Date Wed, 03 Jun 2009 17:19:13 GMT
I logged JIRA DERBY-4256, as unfortunately derby does not currently 
support alter table to expand the length of the blob or clob fields.
I included in it the necessary work to make derby support such a
feature.  Without doing the development work I think you are stuck
with the kind of process you describe below.

If you can come up with the space to complete it I think you can get
your db size back down if you add a compress table call at the end
of your work.  But the db is definitely going to grow in the
intermediate steps, both by having multiple copies of the blob in
the row.  Also a lot of logging will happen so wherever you log
directory is going to grow during this process.  Log space can't
be reclaimed until transactions are committed so you may be growing
space there also.

/mikem

Evan Leonard wrote:
> 
> 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