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 John English <je...@brighton.ac.uk> on 2006/07/06 00:56:56 UTC
Migrating CLOBs to new DB schema
I have a database where I need to change the schema for a couple of crucial
tables. This involves changing the types of some fields, so I can't just do
an "alter table". The tables contain CLOBs, so I can't use the provided SYSCS
export/import procedures. This is what I have tried so far:
* Dumping the data to a file using the output of an IJ select as the basis
for the modified data and turning it into an insert statement. IJ barfs
when the CLOB data is a string constant >32K in size.
* Creating new tables alongside the existing ones, using "insert from select"
to populate them. The constraints are all wrong, I can't drop any tables
because they depend on other tables via constraints, and it's really really
horrible. I've also just realised why names for constraints are a good idea
-- mine have none, so dropping them is not easy. I'm learning the hard way
as usual.
Any other ideas that I could try?
----------------------------------------------------------------------
John English | mailto:je@brighton.ac.uk
Senior Lecturer | http://www.it.bton.ac.uk/staff/je
School of Computing & MIS | "Those who don't know their history
University of Brighton | are condemned to relive it" (Santayana)
----------------------------------------------------------------------
Re: Migrating CLOBs to new DB schema
Posted by John English <je...@brighton.ac.uk>.
J.English@bton.ac.uk wrote:
> I have a database where I need to change the schema for a couple of crucial
> tables. This involves changing the types of some fields, so I can't just do
> an "alter table". The tables contain CLOBs, so I can't use the provided
> SYSCS
> export/import procedures. This is what I have tried so far:
>
> * Dumping the data to a file using the output of an IJ select as the basis
> for the modified data and turning it into an insert statement. IJ barfs
> when the CLOB data is a string constant >32K in size.
I've now got it almost working by splitting the CLOB into <32K chunks and
concatenating:
'foo' || 'bar' || 'baz' ...
but one set of data barfs with error 54006 (the length resulting from CONCAT
operation is greater than 32700). It works fine with other data which is the
same sort of size. The things I'm concatenating are all string constants.
Anyone got any ideas here?
----------------------------------------------------------------------
John English | mailto:je@brighton.ac.uk
Senior Lecturer | http://www.it.bton.ac.uk/staff/je
School of Computing & MIS | "Those who don't know their history
University of Brighton | are condemned to relive it" (Santayana)
----------------------------------------------------------------------
Re: Migrating CLOBs to new DB schema
Posted by Kathey Marsden <km...@sbcglobal.net>.
John English wrote:
> I have a database where I need to change the schema for a couple of
> crucial
> tables. This involves changing the types of some fields, so I can't
> just do
> an "alter table". The tables contain CLOBs, so I can't use the
> provided SYSCS
> export/import procedures.
[snip what John tried]
>
> Any other ideas that I could try?
>
I had great luck recently with DDLUtils. I hit one small issue with
zero length Clobs which I think is resolved now.
http://db.apache.org/derby/integrate/db_ddlutils.html
Kathey