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:
@QueryRow
(
jdbcDriverName = "org.apache.derby.jdbc.EmbeddedDriver",
query = "select * from MyTable"
)
public static ResultSet MyTable( String connectionURL ) throws
SQLException
{ 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,
-Rick
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: [email protected] [mailto:[email protected]]
Gesendet: Mittwoch, 12. August 2009 19:42
An: Derby Discussion
Betreff: Re: Alter database collation
Stefan Rehlig <[email protected]> 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
