LucidDbUpgrade

Overview

This page explains how to upgrade an instance of LucidDB. There are two methods, physical and logical. The physical method copies the binary database files and catalogs from one location to another. The logical results in exporting tables to text files then using DDL to recreate the table structures and finally import the files using the flat file wrapper functionality of Lucid.

Before reading on, here are some important points to note:

• Upgrade is currently a manual procedure requiring some care. (Adding automated upgrade support would be a very useful contribution to LucidDB!)
• Best practice is to make a backup of your existing installation before attempting any upgrade.
• Upgrade is UNLIKELY to work from version 0.9.3 to 0.9.4 due to some underlying boost/STLPort library upgrades. The safest upgrade from 0.9.3 to 0.9.4 would use a logical level export and rebuilding of the original DDL/setup (see EXPORT_SCHEMA_TO_FILE).

Procedure - Physical Upgrade

1. Unpack and install the new LucidDB version in a new location. So, for example, you might have an old version installed as /home/sderkins/luciddb-0.7.1. In that case, unpack the new version in /home/sderkins/luciddb-0.7.2. (We'll use those two paths as examples in the rest of this procedure; replace them with your site-specific locations.) Run the install script, but do not attempt to start the new server at the same time as the old server.
2. If the old server is not already running, start it now.
3. Connect to the old server via sqllineClient and issue this command to export the catalog metadata in XMI form (saving it to a file in the old server installation directory):

   CALL SYS_ROOT.EXPORT_CATALOG_XMI('/home/sderkins/luciddb-0.7.1/FarragoCatalogDump.xmi');

   (This may take a little while, depending on how big your schemas are; a server with Mondrian's FOODMART schema loaded and analyzed could take about half a minute; the resulting export file size is about 6MB.)
4. !quit from sqllineClient. Do not execute any other commands on the old instance after this.
5. Shut down the old server and verify that it was a clean shutdown. See LucidDbColdBackupRestore for how to do this, and what to do if the shutdown failed.
6. In the new server location (after running the install script), run bin/sqllineEngine (instead of the usual lucidDbServer). This will start up LucidDB in a no-network maintenance mode process with the usual sqlline command-line interface.
7. Issue this command to prepare the server for catalog migration:

   ALTER SYSTEM REPLACE CATALOG;

   (This may take a while; it also exports some metadata to a system-defined location for use on restart. "Session closed" errors are okay.)
8. !quit from sqllineEngine. Do not execute any other commands (if you try it, you'll get internal errors).
9. Copy the physical database file (db.dat) from the old server location to the new one, overwriting the empty one that just got installed, e.g.

   cp -f /home/sderkins/luciddb-0.7.1/catalog/db.dat /home/sderkins/luicddb-0.7.2/catalog/db.dat

10. Copy the catalog metadata XMI (created in step 3) from the old server location to the new catalog location, e.g.

    cp -f /home/sderkins/luciddb-0.7.1/FarragoCatalogDump.xmi /home/sderkins/luciddb-0.7.2/catalog/FarragoCatalogDump.xmi

    (be sure to force an overwrite since step 7 will have created an unneeded file in the target location).
11. Special case for upgrade from 0.9.3 to 0.9.4: copy from /home/sderkins/luciddb-0.9.3/plugin/applib.jar to /home/sderkins/luciddb-0.9.4/plugin. If you forget to to do this, the catalog.sql step below will fail. This is a one-time special case; this will not be necessary for upgrades from 0.9.4 and after. Also, you can delete the copy of applib.jar once the upgrade has completed successfully.
12. Start the new server in the usual way (lucidDbServer). (This will take time proportional to the export in step 3, since it has to import the metadata into the new catalog.)
13. Special case for upgrade from 0.9.2 to 0.9.3: using sqllineClient first run the command

    drop routine APPLIB.ADD_HOURS_TIMESTAMP

14. Using sqllineClient, execute the install/catalog.sql from the new server installation, e.g.

    sqllineClient --run=/home/sderkins/luciddb-0.7.2/install/catalog.sql

15. Check the sqlline output to verify that there were no errors while running catalog.sql.
16. Shut the new server down and back it up so that if anything goes wrong subsequently, you won't have to repeat the upgrade process.

Notes

• If you used relative paths in any SQL object definitions (e.g. the location of a flatfile directory or .jar file), you may encounter problems with the new server not being able to find those objects. This may even interfere with the migration procedure above (e.g. executing catalog.sql). In general, avoiding relative paths in object definitions is a good practice; if you can't avoid them for some reason, you may need to take compensating measures (e.g. create symlinks or carry out the upgrade in-place rather than side-by-side).
• If your db.dat file is very large, you may prefer to move it rather than copy it during step 9. This assumes that you have backed the old server up before starting.
• If you have tables with a GENERATED...AS... column (auto increment), after upgrading the next version your subsequent inserts with begin with the next multiple of 1000.

Restore Across Versions

Suppose you have an old backup taken from version 0.7.1, and now you want to restore it onto 0.7.2. As noted in LucidDbColdBackupRestore, you can't do this via the direct restore procedure since the versions don't match. Instead, do this:

1. Re-install a fresh copy of 0.7.1
2. Restore your old backup in that environment
3. Upgrade that environment to a fresh copy of 0.7.2, using the procedure above.

(Attempting the same thing in the downgrade direction is unlikely to work.)

Procedure - Logical Upgrades

The procedure described here was used to upgrade from LucidDB 0.9.3 to 0.9.4.
This same procedure can be used to crossover between 32bit and 64bit versions of LucidDB.

1. Create a directory on your local filesystem to hold your logical backup files. This will contain a collection of csv and bcp text files for each of the tables in the schema. In our example we have a schema called TESTING. We create a directory called TESTING named after our schema to keep things straight when we point Lucid's flat file wrapper to the directory to interpret all the files as those that should show up as foreign tables to be the source for our restore process.

mkdir /media/LinuxBackup/TESTING

2. To tell LucidDB to export the tables in our schema to csv and bcp files we follow the instructions here EXPORT_SCHEMA_TO_FILE and issue the command as follows

CALL SYS_ROOT.EXPORT_SCHEMA_TO_FILE('LOCALDB', 'TESTING', false, null, '%', '/media/LinuxBackup/TESTING', true, true, ',', '.csv'); 

When this call is finished, you will find two files for each table found in the schema TESTING. One will be a .csv file containing all the raw row data of each table. The other a .bcp file which describes the metadata of the columns. This completes the backup portion of the process.

3. Now the tables need to be restored in the newest version of LucidDB (0.9.4). This process begins by creating what LucidDB calls a 'SERVER', which will act as a bridge translating 'files' into 'tables' so we can use them as a source for restoring into true, local tables.

CREATE SERVER file_dump_testing
FOREIGN DATA WRAPPER SYS_FILE_WRAPPER
OPTIONS (
DIRECTORY '/media/LinuxBackup/TESTING',
FILE_EXTENSION 'csv',
CONTROL_FILE_EXTENSION 'bcp',
FIELD_DELIMITER ',',
LINE_DELIMITER '\n',
QUOTE_CHAR '"',
WITH_HEADER 'YES');

4. Let us create a schema called MIDDLE_GROUND for all of the CSV files to be presented as foreign tables.

CREATE SCHEMA "MIDDLE_GROUND";

Then set the active schema to MIDDLE_GROUND.

SET SCHEMA 'MIDDLE_GROUND';

5. To make the .csv files actually appear in MIDDLE_GROUND, we have to run the following SQL command

IMPORT FOREIGN SCHEMA BCP
FROM SERVER file_dump_testing
INTO "MIDDLE_GROUND";

If you issue a !tables command in sqlineClient, numerous tables from the backup will appear in the schema MIDDLE_GROUND.
It is now possible to use regular SQL queries to access the content of these .csv files.

6. Our original schema was called TESTING. We need to create that schema and the table structures for the tables we want to restore.

CREATE SCHEMA "TESTING";

SET SCHEMA 'TESTING';

This next command will need to be run for each table you wish to restore from MIDDLE_GROUND into TESTING.
The function is defined in APPLIB and documentation for it is located here AppLib_CREATE_TABLE_AS

 CALL APPLIB.CREATE_TABLE_AS('TESTING', 'DIM_RESIDENT', 'select * from "MIDDLE_GROUND"."DIM_RESIDENT"',true);

This library function generally does a good job interpreting the metatdata from source tables and generating DDL to replicate the table structure faithfully in your destination schema, but sometimes it will fail. It either succeeds 100% or fails 100% leaving no negative impacts on the system. If it fails, you need to retrieve the CREATE TABLE statement for your destination table to execute manually. One instance that caused a failure in CREATE_TABLE_AS appeared in SQL generated from Pentaho Aggregate Designer which included parenthesis in table names.

After running these commands, you can clean up by dropping the server connection you created in LucidDB earlier with the cascade option.

DROP SERVER file_dump_testing CASCADE;

This will have removed the flat file wrapper connection and all tables imported into MIDDLE_GROUND as foreign tables. Tables in schema TESTING are still present. The schema called MIDDLE_GROUND is also present and can be dropped if desired.

Logical Upgrade Notes

There were several things as side notes that may make your upgrade a little easier.

1. If you want to run both versions of LucidDB, 0.9.3 and 0.9.4 simultaneously, you will need one of them to listen for connections on a different port. Start one of them up and issue command:

   ALTER SYSTEM SET "serverHttpPort" = 3333;

   Then next time you start up that luciddbServer it will use that port. You might want to update the url in the file luciddb-0.9.4/bin/sqllineClient to have a jdbc url that connects on the new port. Example:

    jdbc:luciddb:http://localhost:3333

   Follow the link for documentation on the system parameter serverHttpPort.
2. We created a script which had all of the necessary SQL to facilitate the upgrade from 0.9.3 to 0.9.4. We told sqllineClinet to run the script from the command line in Linux by running:

   echo '!run /opt/pentaho/data-integration/LucidMigration.sql' | ./sqllineClient

   This fed each line of our script into sqllineClient and then to LucidDB. More information on the !run command can be found here:

Downloads - Using Pentaho Data Integration to generate a SQL script for moving tables

A Pentaho Data Integration 4.2.0 Transformation (.ktr) file was created that can automatically generate the necessary SQL described in the logical upgrade steps above. Media:LucidMigrationSQL.zip

Pentaho Data Integration (PDI) 4.2.0 is an opensource, free, java based, ETL (Extract, Transform and Load) software for integrating information from various sources and changing it up for use in small ways and even loading data warehouses. Download a copy at [Download PDI 4.2.0].
Although Pentaho Data Integration is generally very easy to use, there are [printed books] covering it and [PDI Community Forums]