You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Stefan Rehlig <re...@lemonage.de> on 2009/08/12 12:17:15 UTC
Alter database collation
Hi,
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?
Thanks for your input.
Stefan
Re: Alter database collation
Posted by Kathey Marsden <km...@sbcglobal.net>.
Stefan Rehlig wrote:
>
> Hi,
>
>
>
> 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?
>
>
>
There is a Jira issue filed for this:
https://issues.apache.org/jira/browse/DERBY-2856, but I don't think
anyone is working on it at the moment. Please vote for this issue. I am
not sure how hard it is, but if you want to get involved in the
development community, I am sure there are those who will help.
Kathey
Re: AW: Alter database collation
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Stefan Rehlig <re...@lemonage.de> writes:
> 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?
Yes, the export procedure works on the table level:
SYSCS_UTIL.SYSCS_EXPORT_TABLE
cf.
http://db.apache.org/derby/docs/10.5/ref/rrefexportproc.html
http://db.apache.org/derby/docs/10.5/ref/rrefexportproctablelobs.html
http://db.apache.org/derby/docs/10.5/tools/ctoolsimport16245.html
Sometimes the dblook(1) tool can help recreating the schema
http://db.apache.org/derby/docs/10.5/tools/ctoolsdblook.html
although it has some limitations (YMMV):
https://issues.apache.org/jira/secure/IssueNavigator.jspa?reset=true&&query=dblook&summary=true&description=true&pid=10594&status=1&status=3&status=4&component=11414
Thanks,
Dag
Re: AW: Alter database collation
Posted by Kathey Marsden <km...@sbcglobal.net>.
I have not used it for some time but found DDLUtils very useful for
transfering schema and data from Derby to Derby in the past:
http://db.apache.org/derby/integrate/db_ddlutils.html
Re: AW: Alter database collation
Posted by Rick Hillegas <Ri...@Sun.COM>.
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: 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 <re...@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
>
>
AW: Alter database collation
Posted by Stefan Rehlig <re...@lemonage.de>.
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 <re...@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
Re: Alter database collation
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Stefan Rehlig <re...@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