db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: AW: Alter database collation
Date Fri, 14 Aug 2009 14:26:05 GMT
Hi Stefan,

By defining some table functions, you could coalesce steps (1) and (4) into

4'. Copy the data from the old database into the new database via a 
series of INSERT INTO SELECT * FROM statements, where you insert into a 
new table by selecting from a table function attached to the old table. 
This would give you some performance gain since you wouldn't have to 
indirect through the exported files. In addition, you wouldn't have to 
delete the dumped data files at the end.

In defining these table functions, you could use a lot of machinery from 
the demo subdirectory java/demo/vtis. In particular, look at the 
java/demo/vtis/sql/demoForeignDbmsVtis.sql script for an example of how 
you register these table functions using the registerQueryRowVTIs() 
procedure. The template for defining these table functions lives here: 
java/org/apache/derbyDemo/vtis/example/VTIs.java. You would have to 
declare one table function for each table. The declarations would look 
something like this:

         jdbcDriverName = "org.apache.derby.jdbc.EmbeddedDriver",
         query = "select * from MyTable"
    public  static  ResultSet   MyTable( String connectionURL ) throws 
    { return QueryVTIHelper.instantiateQueryRowVTI( connectionURL ); }

The inserts would look something like this:

insert into MyTable
select s.*
from table( "MyTable"( 'jdbc:derby:myOldDatabase' ) ) s

Hope this helps,

Stefan Rehlig wrote:
> Hi,
> thanks for your answer. the steps for exporting/importing would be:
> 1. export all data
> 2. create new database with collation attribute
> 3. create schema in the new db without indexes nor constraints
> 4. import all data
> 5. create indexes and constraints
> Do I have to export/import each table separately? Do you have any guidelines/experience
for exporting/importing a whole database?
> Thx for your help
> Stefan
> -----Ursprüngliche Nachricht-----
> Von: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM] 
> Gesendet: Mittwoch, 12. August 2009 19:42
> An: Derby Discussion
> Betreff: Re: Alter database collation
> Stefan Rehlig <rehlig@lemonage.de> writes:
>> we’re using derby in one of our products. With the new version of
>> our product we are facing a collation problem. For creating new
>> databases on application setup we use the collation option
>> TERRITORY_BASED. Older versions of our app did not use the collation
>> parameter. So we need to find a way for altering the collation of
>> these existing databases to territory based. Do you see any way to
>> achieve this?
> Could you side-step the issue using export/import?
> Dag

View raw message