Using the rawDBReader optional tool

Overview

Derby is a stable, well-tested database engine. Nevertheless, it is possible for a Derby database to become corrupt. If a Derby database becomes corrupt but remains bootable (for instance, a single index becomes inconsistent), then the damage may be located by querying the SYSCS_UTIL.SYSCS_CHECK_TABLE system table function and the damage may be repaired by running the SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure. These tools are documented in the Derby Developer's Guide.

However, if the database is unbootable, then the best approach is to restore the database from a recent backup. If a backup isn't available, then you may be able to extract some data out of the corrupt database by using the rawDBReader optional tool. The rawDBReader tool is not guaranteed to retrieve all of the data. In some situations, the tool may retrieve data which was deleted before the database became unbootable. The rawDBReader optional tool is a last resort to salvage something.

When running the rawDBReader tool, you will work with two databases. Both of these databases must be on the machine where you are running the tool.

• corruptDB - This is the corrupt or unbootable database whose data you are trying to salvage.
• healthyDB - This is a new database which you create in order to hold the retrieved data.

The rawDBReader tool works by copying all of your user data from the corrupt database into a totally separate, healthy database. Each table in the corrupt database will be copied to a fresh table in the healthy database. The healthy target table will have the same schema name, the same table name, and the same column names as the original, corrupt table.

There are three steps to using rawDBReader:

• load - You call SYSCS_REGISTER_TOOL with arguments specific to your situation. This will prepare the healthy database to accept data from the corrupt database. This will also generate a text file containing a series of commands. This is the recovery script.
• extract - You then run the recovery script in order to retrieve your data from the corrupt database and copy the data into the healthy database.
• unload - Finally, you can remove the tool via another call to SYSCS_REGISTER_TOOL.

These steps are described in greater detail below.

Loading the tool

To run the rawDBReader tool, you must be signed on to the machine where the corrupt database resides. Your classpath must contain derby.jar, derbytools.jar, and derbyoptionaltools.jar.

To load the rawDBReader tool, connect to the healthy database and issue the following statement:

call syscs_util.syscs_register_tool
(
  'rawDBReader',
  true,
  $recoveryScript,
  $controlSchema,
  $schemaPrefix,
  $corruptDBPath,
  $corruptEncryptionAttributes,
  $corruptDatabaseOwner,
  $corruptDatabaseOwnerPassword
);

Where the arguments have these meanings:

• $recoveryScript - The name of a file into which a recovery script will be written.
• $controlSchema - The name of a schema which will be created in the healthy database and which will hold table functions and views for querying the core catalogs of the corrupt database.
• $schemaPrefix - A string which will be prefixed to the names of user schemas which will be created in the healthy database. For every schema in the corrupt database, a corresponding schema will be created in the healthy database. These schemas will hold functions and views which can be used to extract data out of the corrupt database.
• $corruptDBPath - The file path to the corrupt database directory.
• $corruptEncryptionAttributes - The encryption directives with which the corrupt database was created. May be null.
• $corruptDatabaseOwner - The name of the owner of the corrupt database.
• $corruptDatabaseOwnerPassword - The password of the owner of the corrupt database. May be null.

The schema prefix is just a tag which helps the tool create unique schema names that won't conflict with the names of user schemas. The control schema is a separate schema whose name should not conflict with any user schemas. If the corrupt database has the following user schemas...

S1
S2

...then the healthy database will have the following schemas after loading the rawDBReader tool and after running the recovery script:

S1
S2
$controlSchema
$schemaPrefixS1
$schemaPrefixS2

For instance, if the corrupt database was created without encryption and without specifying a database owner, then the following command would load the rawDBReader optional tool:

call syscs_util.syscs_register_tool
(
  'rawDBReader',
  true,
  'recoverMyData.sql',
  'CONTROL',
  'BAD_',
  'tmpdbs/corruptDB',
  null,
  'APP',
  null
);

If, on the other hand, the corrupt database was created with encryption and with credentials for a database owner, then you would load the tool with a command like the following statement:

call syscs_util.syscs_register_tool
(
  'rawDBReader',
  true,
  'recoverMyData.sql',
  'CONTROL',
  'BAD_',
  'tmpdbs/corruptDB',
  'bootPassword=DBpassword',
  'dbo',
  'DBO_password'
);

Running the recovery script

Loading the tool will write a recovery script containing statements which will create schemas and tables in the healthy database. The schemas and tables correspond to the user schemas and tables in the corrupt database. The script will also contain statements which extract data out of the corrupt tables into their healthy counterparts. Here's a sample recovery script:

connect 'jdbc:derby:tmpdbs/healthyDB';

create schema "S1";

-- siphon data out of c490.dat
create table "S1"."T1" as select * from "BAD_S1"."T1" with no data;
insert into "S1"."T1" select * from "BAD_S1"."T1";

create schema "S2";

-- siphon data out of c4a0.dat
create table "S1"."T2" as select * from "BAD_S1"."T2" with no data;
insert into "S1"."T2" select * from "BAD_S1"."T2";

-- siphon data out of c4b0.dat
create table "S2"."T1" as select * from "BAD_S2"."T1" with no data;
insert into "S2"."T1" select * from "BAD_S2"."T1";

-- siphon data out of c4c0.dat
create table "S2"."T2" as select * from "BAD_S2"."T2" with no data;
insert into "S2"."T2" select * from "BAD_S2"."T2";

Unloading the tool

You can unload the tool after you have run the recovery script and copied data out of the corrupt database into the healthy database. Note that you must specify the same control schema and schema prefix which you specified when you loaded the tool:

call syscs_util.syscs_register_tool
(
  'rawDBReader',
  false,
  'CONTROL',
  'BAD_'
);