Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII; format=flowed
Date: Fri, 29 May 2009 09:59:44 +0200
From: Kristian Waagan <Kristian.Waagan@Sun.COM>
Subject: Re: What happens during a database upgrade?
In-reply-to: <x6prdrzxcg.fsf@Sun.COM>
Sender: Kristian.Waagan@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <4A1F95F0.2080302@Sun.COM>
References: <23736685.post@talk.nabble.com> <4A1D4189.3010705@sun.com>
 <4A1D52E8.6030805@Sun.COM> <23761970.post@talk.nabble.com>
 <x6prdrzxcg.fsf@Sun.COM>
User-Agent: Thunderbird 2.0.0.21 (X11/20090323)

Knut Anders Hatlen wrote:
> David Goulden <relic@prepee.com> writes:
> 
>> For these tables we don't use ORDER BY, so hopefully the bug you mentioned
>> won't affect us. Out of curiosity, is there any advantage to doing a hard
>> upgrade? Except for the CLOB performance issue, we're quite happy with
>> version 10.4.
> 
> Hi David,
> 
> Off the top of my head, I think the advantages you'll get from a hard
> upgrade 10.4 -> 10.5 are faster length calculation for CLOBs and one new
> system procedure (SYSCS_UTIL.SYSCS_UPDATE_STATISTICS).
> 

Again, this is mostly correct.

For existing CLOBs you won't get the faster length calculation, even if 
you do a hard upgrade. CLOBs inserted into a 10.5 database using the 
JDBC methods that take a length argument will benefit. If you use the 
"length less" JDBC overrides, than you're back to the 10.4 situation 
when it comes to the improved length calculation.

Now, we don't really have a good way to upgrade the format of existing 
CLOBs yet. One possibility is to run this query:
     UPDATE myTable SET clobValue = clobValue || '' [WHERE 
length(clobValue) < someThreshold]
This will upgrade the format by concatenating the existing CLOB with the 
empty string. However, the concatenation will materialize the CLOB, so 
if you have large CLOBs you need to have a lot of memory in the server 
(2 GB++).

I have been looking into using a function that just returns the same 
CLOB passed in to it, but Derby doesn't support this yet. The advantage 
of that approach is that the CLOB hopefully won't be materialized. The 
implementation of this functionality has been started under DERBY-4066.


Regards,
-- 
Kristian