Example: Reloading a database using solidDB® tools
This example demonstrates how a database can be reloaded to a new one using the solidDB® tools.
The database reload procedure can be useful, for example, for minimizing the database file size by removing gaps (unused space) that are created during delete and update operations; the reload rewrites the database without gaps.
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 “tcpip 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 containing definitions for all tables, views, triggers, indexes, procedures, sequences, and events.
soldd "tcpip 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 "tcpip 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 A new database is created to replace the old one.
You can create a replacement database by deleting the solid.db and all sol#####.log files from the appropriate directories. When solidDB® is started for the first time after this, a new database is created.
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 "tcpip 1964" dbadmin password
The option -e precedes an administration command.
4 Data definitions are imported into the new database using the solidDB® SQL Editor (solsql).
Use the following command to execute the SQL script created by solidDB® Data Dictionary (soldd).
solsql -fSOLDD.SQL "tcpip 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
5 Data is loaded into the new database using the solidDB® Speed Loader (solload).
Use the following command to load data into the new database: