This chapter gives you information on data security and database maintenance. It is divided into following topics
- making backups
- restoring backups
- recovering from abnormal shutdown
- logging
- creating checkpoints
- closing and opening the database
- changing database location
- running several servers on one computer
- entering timed commands
Backups are made to secure the information stored in your database files. If you have lost your database files because of a system failure, you can continue working with the backup database.
You can make backups manually by using SOLID Remote Control, or you can automate the backup operations to be run according to a fixed schedule. To automate backups, see the chapter Entering timed commands at the end of this chapter.
NOTE. Be sure to have enough disk space in the backup directory. You will need space for your database and log files.
To Make a Backup Manually Using SOLID Remote Control
- Connect to the server you want to backup.
- Go to the BACKUP page and press the Backup button.
- Enter the directory where you want the backup copy to be created, or use the suggested backup directory.
- Press the OK button.
SOLID Server uses a multiversioning technique allowing backups to be made on-line. You need not close the database file or shut down the server. However, it is advisable to automate your backups to be run at non-busy hours. After completing the backup, copy your backup files on tape using your backup software for protection against disk crashes.
NOTE 1. The backup directory entered must be a valid path name in the server operating system! For example, if the server runs on a UNIX operating system, path separators must be slashes, not backslashes.
NOTE 2. The time needed for making a backup is the time that passed between the messages Backup started and Backup completed successfully, which arrive to your SOLID Remote Control MESSAGES page.
Before starting the backup process, a checkpoint is created automatically. This guarantees that the state of a backup database is from the moment the backup process was started. The following files will be copied to the backup directory:
- database file(s)
- the configuration file (solid.ini)
- the log file(s) modified or created after the previous backup (parameter BackupCopyLog is set yes by default)
The unnecessary log files are deleted from original directory after successful backup (parameter BackupDeleteLog is set yes by default).
To Correct a Failed Backup
When a backup is completed, you will see a message appear on the backup list in SOLID Remote Control. The first column in this list tells you the status of the backup, which can be either OK or Failed.
If the backup failed, an error message appears on the message list. Correct the cause of the error and try again. The most common causes for failed backups are
- the backup media is out of disk space
- the backup directory does not exist
- a database directory is defined as the backup directory
There are two alternative ways to restore a backup. You can either:
- return to the state when backup was created or
- revive a backup database to the current state by using log files to add data inserted or updated after the backup was made.
To Return to the State when the Backup was Made
- Shut down SOLID Server, if it is running.
- Delete all log files from the log file directory. The default log file names are sol00001.log, sol00002.log, etc.
- Copy the database file(s) from the backup directory to the database file directory.
- Start your SOLID Server.
This method will not perform any recovery because no log files exist.
To Revive a Backup Database to the Current State
- Shut down SOLID Server, if it is running.
- Copy the database file(s) from the backup directory to the database file directory.
- Copy the log files from the backup directory to the log file directory. If there are logfiles with the same file names, do not replace those logfiles in the log file directory with logfiles from the backup directory.
- Start your SOLID Server.
SOLID Server will automatically use the log files to perform a roll-forward recovery.
Recovering from Abnormal Shutdown
If the server was closed abnormally, i.e. if it was not shut down using the procedures described earlier, SOLID Server will automatically use the log files to perform a roll-forward recovery during the next start up. No administrative procedures are needed to start the recovery.
The message Starting roll-forward recovery appears. After the recovery has been completed, a message will indicate how many transactions were recovered. If no transactions were made since the last checkpoint, this is indicated by the following message
0 transactions recovered.
Logging guarantees that no committed operations are lost in case of a system failure. When an operation is executed in the server, the same operation is also saved to a log file. The log file is used for recovery in case the server is shut down abnormally.
A backup operation will copy the log and database files to the backup directory and delete the log files from the database directory. You may change the default behavior by changing the parameters BackupCopyLog and BackupDeleteLog in the General section of parameters in solid.ini.
The log file can be written using different modes. The modes differ in the speed and security they offer. The default ping-pong method is the best choice in most cases.
You can set the logging mode using the LogWriteMode parameter in the Logging section of parameters. The different modes are listed in Appendix B Configuration Parameters. The log manager of SOLID Server can run in four different operation modes. The choice of log method depends on the log file media and the level of security needed. The available logging methods are:
- Ping-pong method
This method uses the last two allocated disk blocks in the log file to write the newest and second-newest version of the same logical incomplete disk block. The ping-pong method toggles between these two blocks until one block becomes full.
- Write-once method
If the configuration parameter CommitMaxWait is set at zero, this method will write each log record immediately to the disk. An incomplete record is always padded with blanks, and the record is written to the disk. This is the method of choice when the log file storage media is, for example, a magnetic tape drive or a WORM, and when the update rate is low or when only one client at a time makes updates. If the server runs on a single thread, this method of logging should not be used.
If the parameter CommitMaxWait is greater than zero, the logging works in group-commit mode. The server waits for the time specified in CommitMaxWait for someone else to complete the disk block. When the disk block is completed, it is written to the disk. If time out has expired instead, the block will be padded with blank records. This is the most efficient algorithm when the database update rate is extremely high and the updates are performed by several threads.
- Overwriting method
This method rewrites incomplete blocks at each commit until it becomes full. It may be used when data loss from the last log-file disk block is affordable.
TIP For both security and performance reasons, it is a good idea to keep log files and database files on different physical disk devices. If one disk drive is damaged, you will lose either your database files or log files but not both.
Checkpoints are used to store a consistent state of the database onto the database file. Checkpoints are needed for speeding up the roll-forward recovery after a system failure. In the roll-forward recovery, the database will start recovering transactions from the last checkpoint. The longer it has been since the last checkpoint was created, the more operations are recovered from the log file(s).
To speed up recoveries, checkpoints should be created frequently; however, the server performance is reduced during the creation of a checkpoint. Furthermore, the speed of checkpoint creation depends on the amount of database cache used; the more database cache is used, the longer the checkpoint creation will take. Consider these issues when deciding the frequency of checkpoints. See Appendix B Configuration Parameters for a description of the use of CacheSize parameter.
SOLID Server has an automatic checkpoint creation daemon, which creates a checkpoint after a certain number of writes to the log files. The default checkpoint interval is every 5000 log writes. You may change the value of the parameter CheckpointInterval in the General section of parameters. To learn how to change a parameter value, see the chapter SOLID Server Parameter Settings in this guide.
Before and after a large database operation, you may want to create a checkpoint manually. You can create checkpoints manually by using SOLID Remote Control or you can automate the checkpoint creation using timed commands. To automate checkpoints, see the chapter Entering timed commands at the end of this chapter. Use the following procedure to create a checkpoint manually.
NOTE. There can be only one checkpoint in the database at a time. When a new checkpoint is created, the older checkpoint is automatically erased.
To Create a Checkpoint Manually Using SOLID Remote Control
- Connect to the server for which you want to create a checkpoint.
- Select the BACKUP page.
- Press the Checkpoint button.
- Press the Yes button in the Do you want to create a checkpoint? dialog box.
- Creation starts immediately. When it is completed, the message Checkpoint creation completed appears on the status line. You can view this message also on the Messages page.
On non-graphical user interfaces, you can create a checkpoint using the SOLID Remote Control (Teletype) program. Issue the command checkpoint to start checkpoint creation.
In some cases you may want to prevent users from connect to the server. For example, when you are shutting down a server, you may want to prevent new users from connecting to the server. After closing the database, only connections from SOLID Remote Control will be accepted. Closing the database does not affect existing user connections.
To Close the Database Using SOLID Remote Control
- Connect to the server you want to close.
- Wait for the STATUS page to appear.
- Press the Close... button.
- Answer Yes to the question Do you want to close the database?
After this, the database is closed and no new connections are accepted (clients will get SOLID Error Message 14506). This is indicated by the message text No new connections allowed.
To Open a Closed Database Using SOLID Remote Control
- Connect to the server you want to open.
- Wait for the STATUS page to appear.
- Press the Open... button.
- Answer Yes to the question Do you want to open the database?
After this, the database is opened and new connections are accepted. This is indicated by the message New connections allowed.
Changing a database location in SOLID Server is as easy as copying a file from one directory to another.
NOTE. To copy a database file, you need to shut down the server to release the operating system file locks on the database file and log files.
To Change Database Location
- Verify that SOLID Server is not running.
- Copy the database and log files to the target directory.
- Copy the solid.ini file to the target directory. Check that the database file directory, log file directory and backup directory are correctly defined in the configuration file solid.ini.
- Start SOLID Server using the target directory as the current working directory using the command line option -c directory-name.
Running Several Servers on One Computer
In some cases, you may want to run two or more databases on one computer. For example, you may need a configuration with a production database and a test database running on the same computer.
SOLID Server is able to use one database per database server, but you can start several servers each using its own database file. To make these servers use different databases, either start the server processes from the directories your databases are located in or give the locations of configuration files by using the command line option -c directory-name to change the working directory. Remember to use different network names for each server.
In the SOLID Server Windows version, there is a limitation to using several servers in one computer. Windows memory management allows only one SOLID Server to be run at a time. You can, however, trick Windows by renaming the other SOLID executable. For example, you can run SOLID.EXE and SOLID2.EXE at the same time without problems.
SOLID Server has a built-in timer, which allows you to automate your administrative tasks. You can use timed commands to execute system commands, to create backups, checkpoints and database status reports, to open and close databases, to disconnect users or to shut down servers.
To Enter a Timed Command Using SOLID Remote Control
- Connect to the server.
- Go to the TIMER page, and press the New button.
- Select the command from the Commands list.
- Enter the time when you want the command to be executed.
NOTE. The format used is HH:MM (24-hour format).
- Select a day for the timed command, or select All to have it executed every day.
- Enter the command arguments in the Argument text box.
- Press the Save button to enable the command.
To Enter a Timed Command Manually
Timed commands can be entered manually by editing the At parameter of the [Srv] section in the solid.ini file. The syntax is:
At-string := timed-command[, timed-command]
timed-command := [day] HH:MM command argument
day := sun | mon | tue | wed | thu | fri | sat
If the day is not given, the command is executed daily.
Example:
[Srv]
At=20:30 makecp,21:00 backup,sun 23:00 shutdown
Arguments and the Defaults for the Different Timed Commands
USING SOLID DATABASE TOOLS
This chapter describes SOLID Database Tools, a set of utilities for performing various database tasks. Not all SOLID Tools are necessarily part of the standard product delivery, and their availability on some platforms may be limited. For information about SOLID Database Tools, contact your SOLID sales representative or Solid Online Services on our Web server. The service is located at:
http://www.solidtech.com/
Command Line Arguments
This paragraph lists and describes the available command line arguments that can be used with all SOLID Database Tools. The tool-specific options are listed with the usage of each tool.
NOTE. When there is a contradiction in the command line, the tool gives you a list of the possible options as a result. Please check the command line you entered.
Command Line Arguments:
SOLID SpeedLoader
SOLID SpeedLoader is a tool for loading data from external ASCII files into a SOLID database. SOLID SpeedLoader can load data in a variety of formats and produce detailed information of the loading process into a log file. The format of the import file, i.e., the file containing the external ASCII data, is specified in a control file.
There are two versions of SOLID SpeedLoader, the standalone version and the network version. They both perform the same task but in different manners.
- In the standalone version, data is loaded directly into the database file. This enables maximum performance; the server must be shut down while the loading occurs.
- In the network version, data is loaded into the database through the SOLID Server program. This enables online operation of the database during the loading. The data to be loaded does not have to reside in the server computer.
The control file provides information on the structure of the import file. It gives the following information:
- the name of the import file
- the format of the import file
- the table and columns to be loaded
NOTE. Each import file requires a separate control file. SOLID SpeedLoader loads data into one table at a time.
The control file format is somewhat similar to control file structures found in other database management systems, i.e., Oracle and DB/2.
NOTE 1. The table must exist in the database in order to perform data loading.
NOTE 2. Schema support is not currently available in SOLID Speedloader.
The import file must be of ASCII type. The import file may contain the data either in a fixed or a delimited format:
- In fixed-length format data records have a fixed length, and the data fields inside the records have a fixed position and length.
- In delimited format data records can be of variable length. Each data field and data record is separated from the next with a delimiting character such as a comma (this is what SOLID Export produces). Fields containing no data are automatically set to NULL.
Data fields within a record may be in any order specified by the control file.
NOTE 1. Data in the import file must be of a suitable type. E.g., numbers that are presented in a Float format cannot be loaded into a field of Integer or Smallint type.
NOTE 2. Data of Varbinary and Long Varbinary type are hexadecimal encoded in the import file.
During loading, SOLID SpeedLoader produces a log file containing the following information:
- the date and time of the loading
- loading statistics such as the number of rows successfully loaded, the number of failed rows, and the load time if it has been specified with the option
- any possible error messages
If the log file cannot be created, the loading process is terminated. By default the name of the log file is generated from the name of the import file by substituting the file extension of the import file with the file extension .log. For example, my_table.ctr creates the log file my_table.log. To specify another kind of file name, use the option -l.
A configuration file is not required for SOLID SpeedLoader. The configuration values for the server parameters are included in the SOLID Server configuration file solid.ini.
Client copies of this file can be made to provide connection information required for SOLID Speedloader. If no server name is specified in the command line, SOLID SpeedLoader will choose the server name it will connect to from the server configuration file. E.g., to connect to a server using the NetBIOS protocol and with the server name SOLID, the following lines should be included in the configuration file:
[Com]
Connect=netbios SOLID
SOLID SpeedLoader is invoked with the command solload or solloads followed by various arguments. If you invoke SOLID SpeedLoader with no arguments, you will see a summary of the arguments with a brief description, i.e. their usage. The command line syntax is:
solload [options] [server-name] <user-name> <password> <control-file>or
solloads [options] [server-name] <user-name> <password> <control-file>The control file syntax has the following characteristics:
- keywords must be given in capital letters
- comments can be included using the standard SQL double-dash (--) comment notation
- statements can continue from line to line with new lines beginning with any word
SOLID SpeedLoader reserved words must be enclosed in quotes if they are used as data dictionary objects, that is, table or column names. The following list contains all reserved words for the SOLID SpeedLoader control file:
The control file begins with the statement LOAD DATA followed by several statements that describe the data to be loaded. Only comments or the OPTIONS statement may optionally precede the LOAD DATA statement.
The following table describes the full syntax of the control file.
The following paragraphs explain syntax elements and their use is in detail.
CHARACTERSET
The CHARACTERSET keyword is used to define the character set used in the input file. If the CHARACTERSET keyword is not used or if it is used with the parameter NOCONVERT or NOCNV, no conversions are made. Use the parameter ANSI for the ANSI character set, MSWINDOWS for the MS Windows character set, PCOEM for the ordinary PC character set, IBMPC for the IBM PC character set, and SCAND7BIT for the 7-bit character set containing Scandinavian characters.
DATE, TIME, and TIMESTAMP
These keywords can be used in two places with different functionality:
- When one of these keywords is used as a part of the load-data-part element, it defines the format used in the import file for inserting data into any column of that type.
- When a keyword appears as a part of a column definition it specifies the format used when inserting data into that column.
NOTE 1. Masks used as part of the load-data-part element must be in the following order: DATE, TIME, and TIMESTAMP. Each is optional.
NOTE 2. Data must be of the same type in the import-file, the mask, and the column in the table into which the data is loaded.
The following table shows the available data masks:
Data Type
Available Data Masks
DATE
YYYY/YY-MM/M-DD/D
TIME
HH/H:NN/N:SS/S
TIMESTAMP
YYYY/YY-MM/M-DD/D HH/H:NN/N:SS/S
In the above table, year masks are YYYY and YY, month masks MM and M, day masks DD and D, hour masks HH and H, minute masks NN and N, and second masks SS and S. Masks within a date mask may be in any order, e.g., a date mask could be MM-DD-YYYY. If the date data of the import file is formatted as 1995-01-31 13:45:00, use the mask YYYY-MM-DD HH:NN:SS.
PRESERVE BLANKS
The PRESERVE BLANKS keyword is used to preserve all blanks in text fields.
into-table-part
The into-table-part element is used to define the name of the table and columns that the data is inserted into.
FIELDS TERMINATED BY
The FIELDS TERMINATED BY keyword is used to define the character used to distinguish where fields end in the input file.
The ENCLOSED BY keyword is used to define the character that precedes and follows data in the input file.
POSITION
The POSITION keyword is used to define a field's position in the logical record. Both start and end positions must be defined.
NULLIF
The NULLIF keyword is used to give a column a NULL value if the appropriate field has a specified value. An additional keyword specifies the value the field must have. The keyword BLANKS sets a NULL value if the field is empty; the keyword NULL sets a NULL value if the field is a string 'NULL'; the definition 'string' sets a NULL value if the field matches the string 'string'; the definition '((start : end) = 'string')' sets a NULL value if a specified part of the field matches the string 'string'.
Loading Fixed-format Records
Examples of the control file when loading data from a fixed-format import file:
-- EXAMPLE 1
LOAD DATA
INFILE 'EXAMP1.DAT'
INTO TABLE SUPPLIERS (
NAME POSITION(01:19) CHAR,
ADDRESS POSITION(20:40) VARCHAR,
ID POSITION(41:48) INTEGER ) -- EXAMPLE 2
OPTIONS (SKIP = 10, ERRORS = 5)
-- Skip the first ten records. Stop if
-- errorcount reaches five.
LOAD DATA
INFILE 'sample.dat'
-- import file is named sample.dat
INTO TABLE TEST1 (
ID INTEGER POSITION(1-5),
ANOTHER_ID INTEGER POSITION(8-15),
DATE1 POSITION(20:29) DATE 'YYYY-MM-DD',
DATE2 POSITION(40:49) DATE 'YYYY-MM-DD' NULLIF NULL)Loading Variable-length Records
Examples of the control file when loading data from a variable-length import file:
-- EXAMPLE 1
LOAD DATA
INFILE 'EXAMP2.DAT'
INTO TABLE SUPPLIERS
FIELDS TERMINATED BY ','
(NAME VARCHAR, ADDRESS VARCHAR, ID INTEGER) -- EXAMPLE 2
OPTIONS (SKIP=10, ERRORS=5)
-- Skip the first ten records. Stop if
-- errorcount reaches five.
LOAD
DATE 'YYYY-MM-DD HH:NN:SS'
-- The date format in the import file
INFILE 'sample.dat'
-- The import file
INTO TABLE TEST1
-- data is inserted into table named TEST1
FIELDS TERMINATED BY X'2C'
-- Field terminator is HEX ',' == 2C
-- This line could also be:
-- FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '[' AND ')'
-- Fields may also be enclosed
-- with '[' and ')'
(
ID INTEGER,
ANOTHER_ID DECIMAL(2),
DATE1 DATE(20) DATE 'YYYY-MM-DD HH:NN:SS',
DATE2 NULLIF NULL
)
-- ID is inserted as integer
-- ANOTHER_ID is a decimal number with 2
-- digits.
-- DATE1 is inserted using the datestring
-- given above
-- The default datestring is used for DATE2.
-- If the column for DATE2 is 'NULL' a NULL is
-- inserted.Running a Sample Load Using Solload
To Run a Sample Load Using Solload
- Start your SOLID Server.
- Create the table using the sample.sql script and your SOLID SQL Editor.
- Start loading using the following command line:
solload "shmem solid" dba dba delim.ctrThe user name and password are assumed to be 'dba'. To use the fixed length control file, use the following command line:
solload "shmem solid" dba dba fixed.ctrThe output of a successful loading using delim.ctr will be:
SOLID Speed Loader v.02.20.0004 (C) Copyright Solid Information Technology Ltd 1992-1997 Load completed successfully, 19 rows loaded.The output of a successful loading using fixed.ctr will be:
SOLID Speed Loader v.02.20.0004 (C) Copyright Solid Information Technology Ltd 1992-1997 Load completed successfully, 19 rows loaded.Running a Sample Load Using Solloads
To Run a Sample Load Using Solloads
- Start your SOLID Server.
- Create the table using the sample.sql script and your SOLID SQL Editor.
- Shut down SOLID Server.
- Copy the database SOLID.DB to the directory where the SOLLOADS program exists.
- Start loading using the following command line:
solloads dba dba delim.ctrThe user name and password are assumed to be 'dba'. To use the fixed length control file, use the following command line:
solloads dba dba fixed.ctrThe output of a successful loading using delim.ctr will be:
SOLID Speed Loader v.02.20.0004 (C) Copyright Solid Information Technology Ltd 1992-1997 Load completed successfully, 19 rows loaded.The output of a successful loading using fixed.ctr will be:
SOLID Speed Loader v.02.20.0004 (C) Copyright Solid Information Technology Ltd 1992-1997 Load completed successfully, 19 rows loaded.Hints to Speed up Loading
The following hints can be used to ensure that loading is done with maximum performance:
- SOLID SpeedLoader (standalone) is faster than SOLID SpeedLoader (network) because data is loaded directly into the database file, solid.db.
- If the network version is used, it is faster not to load data over the network, i.e., connect locally if possible.
- Increasing the number of records committed in one batch speeds up the load. By default, commit is done after each record.
- If the standalone version is used, it is faster to disable logging. Do not use logging with the network version.
To disable logging the LogEnabled parameter needs to be used. The following lines in the solid.ini file will disable logging:
[Logging]
LogEnabled=noAfter the loading has been completed, remember to enable logging again. The following line in the solid.ini file will enable logging:
[Logging]
LogEnabled=yes
NOTE. Running the server with logging disabled is strongly discouraged. If logs are not written, no recovery can be made if an error occurs due to power failure, disk error etc.
SOLID Export
SOLID Export is a product for unloading data from a SOLID database to ASCII files. SOLID Export produces both the import file, i.e., the file containing the exported ASCII data, and the control file that specifies the format of the import file. SOLID SpeedLoader can directly use these files to load data into a SOLID database.
NOTE. The user name used for performing the export operation must have select rights on the table exported. Otherwise no data is exported.
SOLID Export is invoked with the command solexp. If you invoke solexp with no arguments, you'll see a summary of the arguments with a brief description. The command line syntax is:
solexp [options] [servername] <username> <password> <tablename|*>
NOTE 1. The symbol * can be used to export all tables with one command. However, it cannot be used as a wildcard.
NOTE 2. The -tTABLENAME (Export table) option is still supported in order to keep old scripts valid.
SOLID Data Dictionary
SOLID Data Dictionary is a product for retrieving data definition statements from a SOLID database. SOLID Data Dictionary produces an SQL script that contains data definition statements describing the structure of the database. The generated script contains definitions for tables, views, procedures, sequences, and events.
NOTE 1. User and role definitions are not listed for security reasons.
NOTE 2. The user name used for performing the export operation must have select right on the tables. Otherwise the connection is refused.
Invoking SOLID Data Dictionary
SOLID Data Dictionary is invoked with the command soldd. If you invoke soldd with no arguments, you'll see a summary of the arguments with a brief description. The command line syntax is:
soldd [options] [servername] <username> <password> [tablename]Example:
soldd -odatabase.sql "tcp database_server 1313" dbadmin f1q32j4
NOTE 1. If no table name is given, all definitions are listed to which the user has rights.
NOTE 2. The -ttablename option is still supported in order to keep old scripts valid.
SOLID Remote Control (Teletype)
With SOLID Remote Control (Teletype), commands can be given at the command line, command prompt, or by executing a script file that contains the commands.
NOTE. The user performing the administration operation must have administrators rights, or the connection will be refused.
Invoking SOLID Remote Control (Teletype)
SOLID Remote Control (Teletype) is invoked with the command solcon. On Novell Netware, you start SOLID Remote Control (Teletype) with the command load solcon at the command prompt. SOLID Remote Control (Teletype) connects to the first server specified in the Connect parameter in the solid.ini file. If you start SOLID Remote Control (Teletype) with no arguments, you'll be prompted for the database administrators user name and password. The command line syntax is:
solcon [options] [servername] [username password]
Option
Description
-c<dir>
Change working directory
-e<string>
Execute command string
-f<filename>
Execute command string from file
-h, -?
Help = Usage
You can give the connection information at the command line to override the connect definition in solid.ini.
Example:
solcon "spx solid"Also the administrator's user name and password can be given at the command line.
Example:
solcon "tcp localhost 1313" admin iohi4yUsing SOLID Remote Control (Teletype)
After the connection to the server has been established, the command prompt appears.
Available commands are described in the following table:
You can execute all commands either using this interface or giving them at the command line with the -e option or in a text file with the
-f option. Commands can be given using either the complete command name or its abbreviation.SOLID SQL Editor (Teletype)
With SOLID SQL Editor (Teletype), statements can be given at the command line, command prompt, or by executing a script file that contains the SQL statements.
NOTE. The user performing SQL statements must have appropriate user rights on the corresponding tables, or the connection will be refused.
Starting SOLID SQL Editor (Teletype)
SOLID SQL Editor (Teletype) is started by entering the command solsql. On Novell Netware, you start SOLID SQL Editor (Teletype) with the command load solsql at the command prompt. SOLID SQL Editor (Teletype) connects by default to the first server specified in the Connect parameter in solid.ini file and prompts for a user name and password. The command line syntax is:
solsql [options] [servername] [username] [password] [filename]
NOTE. If user name and password are given as command line arguments also the server name must be given as a command line argument. Also if the name of the SQL script file is given as a command line argument (not with the option -f), the server name, user name and password must also be given as command line arguments.
Using SOLID SQL Editor (Teletype)
Executing SQL Statements
After the connection to the server has been established a command prompt appears. SOLID SQL Editor (Teletype) executes SQL statements terminated by a semicolon.
Example:
create table testtable (value integer, name varchar); insert into testtable (value, name) values (31, Duffy Duck); select value, name from testtable; drop table testtable;Exiting SOLID SQL Editor
To exit from SOLID SQL Editor (Teletype) enter the command:
exit;Executing an SQL Script
To execute an SQL script from a file, the name of the script file must be given as a command line parameter:
solsql server-name user-name password file-nameAll statements in the script must be terminated by a semicolon. SOLID SQL Editor (Teletype) exits after all statements in the script file have been executed.
Example:
solsql "tcp localhost 1313" admin iohe4y tables.sql
NOTE. Remember to commit work at the end of the SQL script or before exiting SOLID SQL Editor (Teletype). If an SQL-string is executed with the option -e, commit can only be done using the -a option.
Tools Sample: Reloading a Database
This example demonstrates how a SOLID Server database can be reloaded to a new one. At the same time the use of each SOLID tool is introduced with an example. This reload is a useful procedure since it shrinks the size of the database file solid.db to a minimum.
To Reload the Database:
- Extract data definitions from the old database.
- Extract data from the old database.
- Replace the old database with a new one.
- Load data definitions into a new database.
- Load data into the new database.
Walkthrough
In this example the server name is SOLID and the protocol used for connections is Shared Memory. Therefore, the network name is "ShMem SOLID". The database has been created with the user name "dbadmin" and the password "password".
- Data definitions are extracted with SOLID Data Dictionary. Use the following command line to extract an SQL-script containing definitions for all tables, views, procedures, sequences, and events. The default for the extracted SQL-file is soldd.sql.
soldd "ShMem SOLID" dbadmin password
With this command all data definitions are listed into one file, soldd.sql (the default name). As mentioned earlier, user and role definitions are not listed for security reasons. If the database contains users or roles, they need to be appended into this file.- All data is extracted with SOLID Export. The export results in control files (files with the extension .ctr) and data files (files with the extension .dat). The default file name is the same as the exported table name. In 16-bit environments, file names longer than eight letters are concatenated. Use the following command line to extract the control and data files for all tables.
solexp "ShMem SOLID" dbadmin password *
With this command data is exported from all tables. Each tables data is written to an import file named table_name.dat. A separate control file table_name.ctr is written for each table name.- A new database can be created to replace the old one by deleting the solid.db and all sol####.log files from the appropriate directories. When SOLID Server is started for the first time after this, a new database is created.
NOTE. It is recommended that a backup is created of the old database before it is deleted. This can be done using SOLID Remote Control (Teletype).
- Use the following command line to create a backup using SOLID Remote Control (Teletype):
solcon -eBACKUP "ShMem SOLID" dbadmin password
With this command a backup is created. The option -e precedes an administration command.- Load data definitions into the new database. This can be done using SOLID SQL Editor (Teletype). Use the following command line to execute the SQL-script created by SOLID Data Dictionary.
solsql -fSOLDD.SQL "ShMem SOLID" dbadmin password
With this command, data definitions are loaded 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 SOLID Server with the following command line. The option -x creates a new database, executes commands from a file, and exits. User name and password are defined as well.
solid -Udbadmin -Ppassword -x execute:soldd.sql- Load data into the new database. This can be done with either version of SOLID Speedloader. Solload.exe is the network version and solloads.exe the standalone version. In this example, the database directory is C:\Solid. Therefore, using solloads.exe requires changing the working directory if the extracted data is not in the same directory with the database. To load several tables into the database a batch file containing a separate command line for each table is recommended. In Unix-based operating systems and in OS/2, using the wildcard symbol * is possible. Use either of the following command lines to load data into the new database.
solload "ShMem SOLID" dbadmin password table_name.ctr
With this command data for one table is loaded using the network version. The server is online.
solloads -cc:\solid dbadmin password c:\temp\table_name.ctr
With this command data for one table is loaded using the standalone version. The server is shut down. The database file is located in c:\solid\ as instructed with the option -c, and the control file is located in c:\temp\.
NOTE. No server name is needed with solloads.exe since the server is off-line. SOLID SpeedLoader (network) has the required functionality of a database server embedded.
Batch files that can be used are:
- Shell scripts in Unix environments
- .com -scripts in VMS
- .cmd -scripts in OS/2
- .bat -scripts in DOS, Windows 3.x, 95, and NT
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.