Example: Reloading a database by using solidDB tools
This example demonstrates how a database can be loaded into a new database by using the solidDB tools.
The database reload procedure can be useful, for example, for minimizing the database file size by removing unused space that is created during delete and update operations; the reload rewrites the database without the unused space.
Overview
1 Extract data definitions from the old database.
2 Extract data from the old database.
3 Replace the old database with a new one.
4 Load data definitions into a new database.
5 Load data into the new database.
Reloading the database: Walkthrough
In this example, the server name is solidDB and the protocol used for connections is TCP/IP, using port 1964 (network name is tcp 1964). The database has been created with the user name dbadmin and the password password.
1 Data definitions are extracted with solidDB Data Dictionary (soldd).
Use the following command to extract an SQL script that contains definitions for all tables, views, triggers, indexes, procedures, sequences, and events.
soldd "tcp 1964" dbadmin password
The soldd command lists all data definitions into one SQL file; the default file name is soldd.sql.
User and role definitions are not listed for security reasons. If the database contains users or roles, they must be appended into the extracted SQL file.
2 All data is extracted with solidDB Export (solexp).
Use the following command to extract the control and data files for all tables.
solexp "tcp 1964" dbadmin password *
The export creates control files (table_name.ctr) and data files (table_name.dat) for each table. The default file name is the same as the exported table name. In 16-bit environments, file names longer than eight letters are concatenated.
3 Backup the existing database.
It is recommended that a backup is created of the old database before it is deleted. This can be done using solidDB Remote Control (solcon).
Use the following command to create a backup using solcon:
solcon -eBACKUP "tcp 1964" dbadmin password
The option -e precedes an administration command.
4 A new database is created to replace the old one.
You can create a replacement database by deleting the solid.db file and all sol#####.log files from the appropriate directories. When solidDB is started for the first time after this, a new database is created.
5 Data definitions are imported into the new database by using the solidDB SQL Editor (solsql).
Use the following command to execute the SQL script created by solidDB Data Dictionary (soldd).
solsql -fSOLDD.SQL "tcp 1964" dbadmin password
This command loads the data definitions into the new, empty database. Definitions are retrieved with the option -f from the file soldd.sql. Connection parameters are the same as in the earlier examples.
The previous two steps can be performed together by starting the solidDB server with the following command:
solid -Udbadmin -Ppassword -x execute:soldd.sql
6 Data is loaded into the new database by using the solidDB Speed Loader (solload).
Use the following command to load data into the new database: