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
