db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4256) allow alter table to increase the maximum size of a blob and a clob.
Date Wed, 03 Jun 2009 17:04:07 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mike Matrigali updated DERBY-4256:
----------------------------------


I logged this issue based on the following discussion list posting:

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


> allow alter table to increase the maximum size of a blob and a clob.
> --------------------------------------------------------------------
>
>                 Key: DERBY-4256
>                 URL: https://issues.apache.org/jira/browse/DERBY-4256
>             Project: Derby
>          Issue Type: New Feature
>    Affects Versions: 10.5.1.1
>            Reporter: Mike Matrigali
>            Priority: Minor
>
> Allow new syntax that allows user to alter the maximum length of a blob and/or clob column:
> ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(128M)
> The syntax should match up with the existing functionality to increase the size of varchar
fields.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message