HowTo Migrate PLANOUT DerbyDB

Karsten.nachbaur@opencom.de

Use Squirrel to export data.

1. Download and install (everything) Squirrel http://squirrel-sql.sourceforge.net/

2. Add Derby-Driver

3. Add alias to destination database

4. Connect to database
DANGER: Stop PLANOUT-Application-Server first.

5. Open the Object-Browser, switch to PLANOUT and open the table folder. Select all Tables.

6. By context menue choose “Scripts” then “Create Data Script”

7. Save the results to a file

Prepare exportfile for MS SQL

1. Open the file with an editor. For example: http://notepad-plus-plus.org/

2. Replace expression ‘"PLANOUT".’ with nothing.

3. Replace expression ‘{ts’ with ‘CAST(‘

4. Replace expression ‘{d’ with ‘CAST(‘

5. Replace expression ‘{t’ with ‘CAST(‘

6. Replace expression ‘}’ with ‘ AS datetimeoffset)’

7. Search for ‘BLOB(x’ and modify the T_USER-rows like this
,BLOB(x'761C02D6D1EC73DA30A64D15B100D6F7'),
,0x761C02D6D1EC73DA30A64D15B100D6F7,

Prepare PLANOUT-database on MS SQL-Server

1. Install PLANOUT and start PLANOUT-Server to create schema on the database.

2. Stop PLANOUT-Server

3. Start SQL Server Management Studio an logon SQL-Server with user planout

4. Use the destination database
SQL> USE planout
SQL> GO

5. Delete all tablecontent !DANGER! EXEC sp_MSforeachtable "delete from ? "

6. Disable foreign key constraints. EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

7.

Import data

1. Load exportfile with SQL Server Management Studio

2. Execute file

3. Pray

4. At least: EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

THAT’S GREAT!