Copyright © 1992-96 Solid Information Technology Ltd. All rights reserved.
The information in this document is subject to change without notice and does not represent a commitment on the part of Solid Information Technology Ltd. No part of this manual may be reproduced or transmitted in any form or by any means, electronically or mechanically, without the written permission of Solid Information Technology Ltd.
SOLID ServerTM, SOLID Bonsai TreeTM, SOLID Remote ControlTM and SOLID SQL EditorTM are trademarks of Solid Information Technology Ltd. Other product and company names mentioned are trademarks or registered trademarks of their respective companies.
Document number SSAG-2.10-0596
Installing SOLID Server
Installing SOLID Drivers
Creating a New Database
Connecting to SOLID Server
Viewing the SOLID Server Message Log
Shutting Down SOLID Server
Making Backups
Restoring Backups
Recovering from Abnormal Shutdown
Logging
Creating Checkpoints
Closing the Database
Changing Database Location
Running Several Servers on One Computer
Entering Timed Commands
Using SOLCON and SOLSQL
ADMINISTRATION WITH SQL STATEMENTS
About SOLID SQL Syntax
Managing User Privileges and Roles
Managing Tables
Managing Indexes
Primary Keys
Foreign Keys
Communication between Client and Server
Network Names for Servers
Network Name for Clients
Communication Protocols
Shared Memory
TCP/IP
NetBIOS
Named Pipes
DECnet
IPX/SPX
A Summary of Protocols
Tuning SQL Statements and Applications
Tuning Memory Allocation
Tuning I/O
Sorting
Tuning Checkpoints
Problem Categories
SOLID SQL API Problems
SOLID ODBC Driver Problems
UNIFACE Driver for SOLID Server Problems
Communication between Client and Server
APPENDIX B CONFIGURATION PARAMETERS
Welcome to SOLID ServerTM, a care-free component DBMS for high-volume database applications and industrial products. As a care-free component, SOLID Server is easy to embed and easy to administer. Designed for high-volume applications, it is easy to distribute and easy to install.
Open solution. SOLID Server offers high performance within SQL standards - ANSI SQL2, Microsoft ODBC, SAG CLI and X/Open SQL - making it easy to embed into your applications.
High-power technology. SOLID Server is the first RDBMS that is fully scalable from stand-alone Windows notebook PCs to symmetric multiprocessing and RISC UNIX environments. It provides all the features you would expect to find in an industrial-strength database server. Multi-thread architecture, stored procedures, row level transaction management - you name it.
High-volume deployment.
SOLID Server is a full-featured client/server database
management system that requires just a few megabytes of disk space.
Everything can be installed from two or three diskettes, so SOLID
Server is simple to distribute and installation takes just
a few minutes -
Database Technology Made Easy.
This Administrator's Guide is designed to make the administration of your SOLID Server even smoother. You can use this guide to learn how to use your SOLID Server, or you can refer to it whenever you need to know how a specific administrative procedure is performed. This guide is divided into following parts:
Introduction
Introduction to SOLID Server familiarizes you with the background and components of your SOLID database management system.
Administration
The following chapters give you step-by-step instructions on how to perform various administrative procedures:
Advanced features
Next chapters give you additional information about SOLID Server including Parameter Settings , Performance Tuning, SOLID Server Diagnostic Tools and Problem Reporting.
Appendixes
The Appendixes give you detailed information about error
messages, configuration parameters and SOLID SQL functionality.
The Glossary of Terms explains some of the terminology
used in the SOLID Server documentation.
SOLID Server documentation is distributed both as printed material and as electronic documentation. Additional product information and technical documentation are also available on-line through the Internet.
Printed manualsDocumentationprinted
SOLID Online ServicesDocumentationon-line
Solid Online Services
on Web server offer the latest product and technical information
free of charge. The service is located at
http://www.solidtech.com/
SOLID Server was developed for the 90s, the era of client/server solutions setting new requirements for database management systems. In this era, users are expecting better solutions combining performance, standards, reliability and care-free administration.
Furthermore, applications are delivered in high volumes to various platforms starting from small laptops to heavy-weight RISC processor environments. They often combine audio and video data, still pictures, text documents, and other large objects.
SOLID Server - developed to meet all these requirements - is the database server for today's demanding applications.
SOLID Server featuresSOLID :
The SOLID database management system consists of the following components:
|
| SOLID Server SOLID Serveris the database server program. SOLID Server is: |
|
|
SOLID Remote Control SOLID Remote Control is a program for administration of SOLID Servers. It allows you to: | |
SOLCON is a teletype version of SOLID Remote Control program.
|
|
SOLID SQL Editor SOLID SQL Editor is a tool for executing SQL queries and commands. It has an easy-to-use graphical user interface. With SOLID SQL Editor, you can: | |
SOLSQL is a teletype version of SOLID SQL Editor program.
|
| SOLID License Wizard SOLID License Wizardis used to update your SOLID Server license. It generates a new SOLID Server license when you enter the information supplied to you in an activation letter. | |
This chapter covers the basic SOLID Server administrative tasks. It tells you how to:
If you have not yet installed SOLID Server, follow these quick-start instructions, or refer to the Read Me First notice delivered with the product diskettes for a detailed description of the installation.
To install your SOLID Server
For installation instructions for Novell Netware, UNIX, VAX/VMS and Open VMS operating systems, refer to the Read Me First notice.
Some environments include SOLID Server drivers (ODBC, UNIFACE) on a separate diskette. To install the drivers:
|
|
You start SOLID Server by clicking the icon labeled SOLID Server in the SOLID Server program group. | |
When SOLID Server is started, it checks if a database already exists in the SOLID directory, that is, the directory where you installed SOLID executables. If a database file is found, SOLID Server will automatically open that database. If not, which is the case when you start the server for the first time, a new database will be created.
On UNIX operating systems ,
you start the server by entering the command solid
at the command prompt. When you start the server for the first
time, enter the command solid
-f at the command
prompt to force the server to run in the foreground.
On Novell Netware, you start the server by entering the command
load solid.nlm
at the command prompt.
On Open VMS, you start the server by entering the command run
solid at the command
prompt
|
| If a database does not exist, SOLID Server will automatically start creating a new database. In the Windows environment, creating the database begins with a dialog prompting for the database administrator's username and password. | |
To create a new database
The username and password are case insensitive. The username must have at least two characters; the password at least three. You can use lower case letters from a to z, upper case letters from A to Z and the underscore character '_', and numbers from 0 to 9.
NOTE. You must remember your username and password to be able to connect to SOLID Server. There are no default usernames; the username you enter when creating the database is the only username available for connecting to the new database.
|
| After accepting the database administrator's username and password, SOLID Server will create a new database. By default the database will be created as one file (solid.db) into the SOLID directory, where the executables are located. This is indicated by a dialog. The time that the database creation process takes depends on the hardware platform you are using. |
After the database has been created, SOLID Server starts listening to the network for client connection requests. In the Windows environment, you will see a SOLID Server icon, but in most environments SOLID Server will run invisibly in the background as a daemon process.
NOTE. Create immediately a backup of your database to be able to use the log files to perform a roll-forward recovery.
In OS/2, UNIX, Novell Netware, VAX/VMS and Open VMS operating
systems,
if you do not have an existing database, the following message
appears:
Database does not exist. Do
you want to create a new database (y/n)?
Answer y(es),
and SOLID Server will prompt for the database administrator's
username and password. When they have been accepted, a new database
will be created.
NOTE. If in the Windows environment you double-click the icon of a running SOLID Server, nothing will happen. SOLID Server is a background process that only reacts to messages from clients through the communication interface.
After starting your SOLID Server, you can test the configuration by connecting to the server. You can connect to the server from your workstation by using either SOLID Remote Control or SOLID SQL Editor.
NOTE. You need to have the privileges of SYS_ADMIN_ROLE or SYS_CONSOLE_ROLE to be able to connect to a server using SOLID Remote Control.
To start SOLID Remote Control
|
SOLID Remote |
| |
To disconnect from the server
Close the window or choose Exit form the File menu.
Refer to on-line help for detailed instructions on using SOLID Remote Control.
On OS/2, UNIX, VAX/VMS, Open VMS and Novell Netware platforms you use the SOLCON program to test the configuration. See the chapter Using SOLCON and SOLSQL for detailed information.
To start SOLID SQL Editor
|
SOLID SQL Editor Icon
Dialog 3. Logon dialog box. |
| |
To execute a predefined query
|
|
| |
To load and execute a SQL script
|
SQL button to execute current statement
SQL button to execute all SQL statements |
| |
Refer to on-line help for detailed instructions on SOLID SQL Editor.
SOLID Server writes all error and info messages to a text file. This message log file is named solmsg.out and it is located in the SOLID directory. You can view this file using any text editor or file viewer. The error messages and their explanations are listed in Appendix A SOLID Server Error Messages of this document and in electronic format as the SOLID Server Error Messages Help.
You can shut down SOLID Server either by
All the shutdown mechanisms will start the same routine, which writes all buffered data to the database file, frees cache memory and finally terminates the server program. Shutting down a server may take a while since the server must write all buffered data from main memory to the disk.
To shut down the server using SOLID Remote Control
NOTE. You need to drop all users to enable the shutdown process.
To shut down the server from the server computer in the Windows operating system
On OS/2, UNIX, VAX/VMS, Open VMS and Novell Netware use the SOLCON program to shut down the server. See the chapter Using SOLCON and SOLSQL for detailed information.
This chapter gives you information on data security and database maintenance. It is divided into following topics
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
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:
The unnecessary log files are deleted from original directory after successful backup (parameter BackupDeleteLog is set yes by default).
To correct a failed backupbackupsfailed
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
There are two alternative ways to restore a backup. You can either:
To return to the state when backup was made
This method will not perform any recovery because no log files exist.
To revive a backup database to the current state
SOLID Server will automatically use the log files to perform a roll-forward recovery.
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 BackupCopyLogparametersBackupCopyLog and BackupDeleteLogparametersBackupDeleteLog 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 parameterparametersLogWriteMode 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:
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 CheckpointIntervalparametersCheckpointInterval 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 manuallycheckpointscreating manually using SOLID Remote Control
On non-graphical user interfaces, you can create a checkpoint using the SOLCON 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
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
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 a database location
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
NOTE. The format used is HH:MM (24-hour format).
Arguments and the defaults for the different timed commandsarguments for timed commands
| Command | Argument | Default |
| backup | backup directory | the default backup directory that is set in the configuration file |
| throwout | user name, all | no default, argument compulsory |
| checkpoint | no arguments | no default |
| shutdown | no arguments | no default |
| report | report file name | no default, argument compulsory |
| system | system command | no default |
| open | no arguments | no default |
| close | no arguments | no default |
The SOLCON and SOLSQL programs are teletype versions of SOLID Remote Control and SOLID SQL Editor. This chapter describes how these programs are used.
You start SOLCON by entering the command solcon at the command prompt. On Novell Netware you start SOLCON with the command load solcon at the command prompt. SOLCON connects to the first server specified in the Connect parameter in solid.ini file and prompts for the database administrator's username and password.
SOLCON usageSOLCONusage
solcon [options] [servername [username pwd]]
Command line optionsSOLCONoptions
| Option | Description | Example |
| -c directory | Option -c changes the working directory. | solcon -c d:\solid |
| -e command | A single command can be given at the command line with the option -e. SOLCON exits after completing the given command. | solcon -e shutdown |
| -f file-name | You can execute commands written in a text file by giving the name of the file with the -f option. Each command must be in its own separate line. SOLCON exits after completing the execution of commands in the file. | solcon -f adm.txt |
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 username and password can be given at the command line.
Example:
solcon "tcp localhost 1313" admin iohi4y
After the connection to the server has been established the command prompt appears. Entering the command help displays the following text:
Available commands:
exit ex Exits from SOLID Remote Control.
help ? Displays this text.
shutdown sd Stops SOLID Server.
open ope Opens server connections, new
connections are allowed.
close clo Closes server connections, no new
connections are allowed.
throwout to Throws out users from SOLID
Server.
userlist ul Displays user list.
makecp mcp Makes a checkpoint.
backup bak Takes a backup copy of the
database.
backuplist bls Displays a status list of last
backups.
status sta Displays server statistics.
report rep Generates a report of server info
to a file.
messages mes Displays server messages.
monitor mon Sets server monitoring on or off.
version ver Displays server version info.
errorcode ec Displays a description of an error
code.
hotstandby hsb Executes hot standby command.
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.
Enter the command exit at your SOLCON interface prompt to exit from SOLCON program.
Enter the command backup at your SOLCON interface prompt to create a backup into your backup directory. To create a backup into a specified directory enter the command backup directory-name at your prompt.
Displaying status list of backup
To display the status list of the last backup enter the command backuplist at your SOLCON interface prompt.
Enter the command makecp at your SOLCON interface prompt to create a checkpoint.
Enter the command throwout user-name or throwout user-id at your SOLCON interface prompt to disconnect a user. You can disconnect all users with the command throwout all.
To display all connected users enter the command userlist at your SOLCON interface prompt.
To display the status of the server enter the command status at your SOLCON interface prompt.
To display the server messages enter the command messages at your SOLCON interface prompt.
To generate a server status report to the file named file-name enter the command report file-name at your SOLCON interface prompt.
To show information about the SOLID Server version and license enter the command version at your SOLCON interface prompt.
Displaying a description of an error code
To display a description of error code enter the command errorcode at your SOLCON interface prompt.
You start SOLSQL by entering the command solsql at the command prompt. On Novell Netware you start SOLSQL with the command load solsql at the command prompt. SOLSQL connects by default to the first server specified in the Connect parameter in solid.ini file and prompts for a username and password.
You can give the connection information at the command line to override the connect definitions in solid.ini.
SQLSQL usageSOLSQLoptions
solsql [options] [servername [username pwd]]
Example:
solsql "spx solid"
Also the username and password can be given at the command line.
Example:
solsql "tcp localhost 1313" admin iohi4y
Command line optionsSOLSQLoptions
| Option | Description |
| -a | Immediately commits every SQL statement. |
| -c directory | Changes working directory. |
| -f file-name | Input file name. |
| -o file-name | Option -o outputs only to a specified file. |
| -s | Silent mode, rows are not printed. |
| -S | Prints executed SQL statements. |
| -t | Prints elapsed time per SQL statement. |
Executing SQL statements
After the connection to the server has been established a command prompt appears. SOLSQL 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 SOLSQL enter command
quit;
Executing a SQL script
To execute a 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-name
All statements in the script must be terminated by a semicolon. SOLSQL exits after all statements in the script file have been executed.
Example
solsql "tcp localhost 1313" admin iohe4y tables.sql
This chapter tells you how to manage users, tables, and indexes using SQL statements. You can use SOLID SQL Editor and many ODBC compliant tools for executing these SQL statements.
To automate these tasks, you may want to save the SQL statements to a file. You can use these files for rerunning your SQL statements later or as a document of your users, tables, and indexes.
The SQL syntax is based on the ANSI X3.135-1989 level 2 standard including important ANSI X3.135-1992 (SQL2) extensions. User and role management services missing from previous standards are based on the ANSI SQL3 draft. For a more formal definition of the syntax, refer to Appendix D SOLID Server SQL Syntax of this document. For the full description of the SOLID SQL API, refer to the SOLID SQL API Help file. The same information is included in SOLID Server Programmer's Guide and SOLID Server Programmer's Reference.
You can use SOLID SQL Editor and many ODBC compliant SQL tools to modify user privileges. Users and roles are created and deleted using SQL statements or commands. A file consisting of several SQL statements is called a SQL script.
In the SOLID directory, you will find an SQL scriptSQL scripts called users.sqlSQL scriptsusers.sql, which gives an example of creating users and roles. You can open, view and run it using SOLID SQL Editor. To create your own users and roles, you can make your own script describing your user environment.
NOTE. All SQL statements must be terminated with a semicolon (;).
When using SOLID Server in a multi-user environment, you may want to apply user privileges to hide certain tables from some users. For example, you may not want an employee to see the table in which employee salaries are listed, or you may not want other users to mess with your test tables.
SOLID Server allows you to apply five different kinds of user privileges. A user may be able to view, delete, insert, update or reference information in a table or view. Any combination of these privileges may also be applied. A user who has none of these privileges to a table is not able to use the table at all.
Privileges can also be granted to an entity called a role. A role is a group of privileges that can be granted to users as one unit. SOLID Server allows you to create roles and assign users to certain roles.
NOTE. Same string can not be used both as a user name and a role name.
The following user and role names are reserved:
| Reserved name | Description |
| PUBLIC | You can use this role to grant privileges to all users. When user privileges to a certain table are granted to the role PUBLIC, all current and future users have the specified user privileges to this table. This role is granted automatically to all users. |
| SYS_ADMIN_ROLE | This is the default roleuser rolesadministrator role for the database administrator. This role has administration privileges to all tables, indexes and users. This is also the role of the creator of the database. |
| _SYSTEM | This is the schema name of all system tables and views. |
| SYS_CONSOLE_ROLE | This roleuser roles system console role has right to use SOLID Remote Control, but does not have other administration privileges. |
Below are some examples of SQL commands for administering users, roles and user privileges.
Creating users
CREATE USER <user_name> IDENTIFIED BY <password>;
Only an administrator has the privilege to execute this statement. The following example creates a new user named CALVIN with the password HOBBESpasswordsentering.
CREATE USER CALVIN IDENTIFIED BY HOBBES;
Deleting usersusersdeleting
DROP USER <user_name>;
Only an administrator has the privilege to execute this statement. The following example deletes the user named CALVIN.
DROP USER CALVIN;
Changing a password
ALTER USER <user_name> IDENTIFIED BY <new password>;
The user <user_name> and the administrator have the privilege to execute this command. The following example changes CALVIN's password to GUBBESpasswordsentering.
ALTER USER CALVIN IDENTIFIED BY GUBBES;
Creating rolesuser rolescreating
CREATE ROLE <role_name>;
The following example creates a new user role named GUEST_USERS.
CREATE ROLE GUEST_USERS;
Deleting rolesuser rolesdeleting
DROP ROLE <role_name>;
The following example deletes the user role named GUEST_USERS.
DROP ROLE GUEST_USERS;
Granting privileges to a user or a roleuser privileges granting
GRANT <user_privilege> ON <table_name> TO <user_name or role_name>;
The possible user privileges on tables are SELECT, INSERT, DELETE, UPDATE, REFERENCES and ALL. ALL will give a user or a role all five privileges mentioned above. EXECUTE privilege will give a user a right to execute a stored procedure. A new user has not any privileges.
The following example grants INSERT and DELETE privileges on a table named TEST_TABLE to the GUEST_USERS role.
GRANT INSERT, DELETE ON TEST_TABLE TO GUEST_USERS;
The following example grants EXECUTE privilege on a stored procedure named SP_TEST to user CALVIN.
GRANT EXECUTE ON SP_TEST TO CALVIN;
Granting privileges to a user by giving the user a roleuser rolesgranting privileges to
GRANT <role_name> TO <user_name>;
The following example gives the user CALVIN the privileges that are defined for the GUEST_USERS role.
GRANT GUEST_USERS TO CALVIN;
Revoking privileges from a user or a roleuser privilegesrevoking
REVOKE <user_privilege> ON <table_name> FROM <user_name or role_name>;
The following example revokes the INSERT privilege on the table named TEST_TABLE from the GUEST_USERS role.
REVOKE INSERT ON TEST_TABLE FROM GUEST_USERS;
Revoking privileges by revoking the role of a useruser rolesrevoking the role of a user
REVOKE <role_name> FROM <user_name>;
The following example revokes the privileges that are defined for the GUEST_USERS role from CALVIN.
REVOKE GUEST_USERS FROM CALVIN;
Granting administrator privileges to a useruser privilegesgranting administrator privileges
GRANT SYS_ADMIN_ROLE TO <user_name>;
The following example grants administrator privileges to CALVIN, who now has all privileges to all tables.
GRANT SYS_ADMIN_ROLE TO CALVIN;
SOLID Server has a dynamic data dictionary that allows you to create, delete and alter tables on-line. SOLID Server tables are managed using SQL commands.
In the SOLID directory, you can find an SQL script named sample.sqlSQL scriptssample.sql, which gives an example of managing tables. You can view and run the script using SOLID SQL Editor.
Below are some examples of SQL statements for managing tablesSQL statementsexamples for managing tables. For a formal definition of the SOL syntax of SOLID Server, refer to Appendix D SOLID Server SQL Syntax of this document.
TIP. If you want to see the names of all tables in your database, issue the SQL statement SELECT * FROM TABLES or use predefined command TABLES from SOLID SQL Editor. The table names can be found in the column TABLE_NAME.
Below are some examples of SQL commands for administering tables.
Creating tables
CREATE TABLE <table_name> (<column> <column type> [,<column> <column type>]...);
All users have privileges to create tables.
The following example creates a new table named TEST with the column I of the column type INTEGER and the column TEXT of the column type VARCHAR.
CREATE TABLE TEST (I INTEGER, TEXT VARCHAR);
Removing tablestablesremoving
DROP TABLE <table_name>;
Only the creator of the particular table or users having SYS_ADMIN_ROLE have privileges to remove tables.
The following example removes the table named TEST.
DROP TABLE TEST;
Adding columns to a tablecolumnsadding to a table
ALTER TABLE <table_name> ADD COLUMN <column_name> <column type>;
Only the creator of the particular table or users having SYS_ADMIN_ROLE have privileges to add or delete columns in a table.
The following example adds the column C of the column type CHAR(1) to the table TEST.
ALTER TABLE TEST ADD COLUMN C CHAR(1);
Deleting columns from a tablecolumnsdeleting from a table
ALTER TABLE <table_name> DROP COLUMN <column_name>;
The following example statement deletes the column C from the table TEST.
ALTER TABLE TEST DROP COLUMN C;
|
SOLID SQL Editor: | NOTE. If the autocommit mode is set OFF, you need to commit your work before you can modify the table you altered. To commit your work after altering a table, use the SQL statement COMMIT WORK;, or press the Commit button in SOLID SQL Editorcommitting workafter altering tabletablescommitting work after altering. If the autocommit mode is set ON - this is the default in SOLID SQL Editor - the transactions are committed automatically. |
Indexes are used to speed up access to tables. The database engine uses indexes to access the rows in a table directly. Without indexes, the engine would have to search the whole contents of a table to find the desired row. There are two kinds of indexes: non-unique indexes and unique indexes. A unique index is an index where all key values are unique. You can create as many indexes as you like to a single table. However, adding indexes slows down updates on that table.
SOLID Server allows you to create and delete indexes using the following SQL statementsSQL statementsexamples for managing indexes. For a formal definition of the syntax of these statements, refer to Appendix D SOLID Server SQL Syntax of this document.
Below are some examples of SQL commands for administering indexes.
Creating an index on a table
CREATE INDEX <index_name> ON <table_name> (<column_name> [ASC | DESC]); Only the creator of the particular table or users having SYS_ADMIN_ROLE have privileges to create or delete indexes.
The following example creates an index named X_TEST on the table TEST to the column I.
CREATE INDEX X_TEST ON TEST (I);
Creating a unique index to a tableindexescreating a unique index
CREATE UNIQUE INDEX <index_name> ON <table_name> (<column_name>);
The following example creates a unique index named UX_TEST on the table TEST to the column I.
CREATE UNIQUE INDEX UX_TEST ON TEST (I);
Deleting an indexindexesdeleting
DROP INDEX <index_name>;
The following example deletes the index named X_TEST.
DROP INDEX X_TEST;
|
SOLID SQL Editor: | NOTE. If the autocommit mode is set OFF, you need to commit your work before you can modify the table on which you altered the indexes. To commit your work after modifying indexes, use the SQL statement COMMIT WORK;, or press the Commit button in SOLID SQL Editorcommitting workafter altering tabletablescommitting work after altering. If the autocommit mode is set ON - this is the default in SOLID SQL Editor - the transactions are committed automatically. |
A primary key is a column or combination of columns that uniquely identify each record in a table. Primary keys like indexes speed up access to tables. The difference between primary keys and indexes in SOLID Server is that the primary key cluster data in the database according to the key values.
This behavior differs from the default clustering in SOLID Server, where the data is clustered according to the insertion time only.
A foreign key is a column or group of columns within a table that refers to, or relates to, some other table through its values. The foreign key must always include enough columns in its definition to uniquely identify a row in the referenced table. The main reason for defining foreign keys is to ensure that rows in one table always have corresponding rows in another table; that is, to ensure that referential integrity of data is maintained.
The database server and client transfer information between each other through the computer network using a communication protocol.
To establish a connection from a client to a server they both have to be able to use the same communication protocol. The client has to know the network name of the server and often also the location of the server in the network. The client process uses the network name to specify which server it will connect to.
This chapter will give you information on how to administer network names.
The network name of a server consists of a communication protocol and a server name. This combination identifies the server in the network. The network names are defined in configuration file solid.ini in [Com] section with the Listen parameter. The solid.ini file should be located in the server program's working directory or in the directory set by the SOLIDDIR environment variable.
A server may use an unlimited number of network names. To make establishing connections easier all components of network names are case insensitive.
Network names are managed on the NETWORK page in SOLID Remote Control or directly by editing the server configuration file solid.ini. An example of an entry in solid.ini:
[Com] Listen = tcpip 1313, nmpipe solid
The example contains two network names which are separated by a comma. The first one uses the protocol TCP/IP and the service port 1313, the other one uses the Named Pipes protocol with the name 'solid'. In our example the 'tcpip' and 'nmpipe' are communication protocols while '1313' and 'solid' are server names.
If the Listen parameter is not found SOLID uses environment dependent defaults as network names.
NOTE. When a database server process is started it
publishes the network names it starts to listen. This information
is also written to a file named solmsg.out
in the located in the same directory as solid.ini
file.
NOTE. Network names must be unique within one host computer. For
example you cannot have two SOLID Servers running, both
listening to the same TCP/IP port in one host, but it is possible
that the same port number is in use in different hosts. Exceptions
to this are the NetBIOS and IPX/SPX protocols, which require that
used server names are unique throughout the whole network.
To add a network name for the server using SOLID Remote Controlnetwork namesadding
To modify a network namenetwork namesmodifying
To remove a network name from the servernetwork namesremoving
NOTE. The modifications to network names become active
immediately after pressing the Save
or
Remove
buttonnetwork namesactivating modifications.
HINT. You can use
the Enabled
check box to select the network names you use. You can disable
and enable a protocol by checking the Enabled
checkbox in the Edit
dialog box.
The network name of a client consists of a communication protocol, an optional host computer name and a server name. By this combination the client specifies the server it will establish a connection to. The communication protocol and the server name must match the ones that the server is using in its network listening name. Most protocols need additionally the host computer name to be specified if the client and server are running on different machines. All components of the client's network name are case insensitive.
The client's network names are defined in the configuration file solid.ini in the [Com] section with the Connect parameter. The solid.ini file should be located in the client program's working directory or in the directory set by the SOLIDDIR environment variable.
The following connect line in the solid.ini of the client workstation will connect a client application using the TCP/IP protocol to a SOLID Server running on a host computer named 'spiff' and listening with the name (port number in this case) '1313'.
[Com] Connect = tcpip spiff 1313
If the Connect parameter is not found in the configuration file solid.ini the client uses the environment dependent default instead. The defaults for the Listen and Connect parameters are selected so that the client will always connect to a local SOLID Server listening with a default network name. So the local communication (inside one machine) does not necessarily need a configuration file for establishing a connection.
NOTE 1. When the connection is requested by a client
application program using the SQLConnect
function the network name of the server is given as a Data Source
Name parameter for that function. If the given name is not an
empty string its contents are used as a network name and the Connect
parameter in the configuration file is omitted. If an empty string
is passed the possibly existing Connect
parameter is used.
NOTE 2. In the Windows (3.x, 95, NT) operating system, the connection
can be made by using the SOLID ODBC driver. When a client
program is using the SOLID ODBC driver, the network name
of the server is given as the ODBC Data Source Name and the Connect
parameter in the configuration file is not used.
A client process and SOLID Server communicate with each other by using computer networks and network protocols. A network operating system - for example, IBM LAN Server or Novell NetWare - is not necessarily needed. You only need a functioning communication protocol for both ends. Supported communication protocols depend on the type of computer and network you are using.
The following paragraphs describe the supported communication protocols and common environments that may be used and also show the required forms of network names for the various protocols.
Usually the fastest way two processes can exchange information is to use Shared Memory. This can be used only when the server and client processes are both running in the same computer. The Shared Memory protocol uses a shared memory location for moving data from one process to another.
To use the Shared Memory protocol in SOLID Server, select ShMem from the list of protocols in SOLID Remote Control and enter a server namecommunication protocolsShared Memory. The server name has to be unique only in this computer.
The format used in the solid.ini filenetwork namesShared Memory
Server Listen = shmem <server name> Client Connect = shmem <server name>
NOTE 1. Server names must be character strings less
than 128 characters long.
NOTE 2. This protocol cannot generally be used if the client and
server programs run under different operating systems in the same
computer. For example, you cannot use this protocol to communicate
from a client process running under a Windows OS/2 session to
a server running on the same computer under native OS/2.
ShMem32ShMem32
There is a special protocol ShMem32 available when connecting from a Windows 16-bit application to a 32-bit server running on a Windows NT or Windows 95 operating system. This implementation uses the general thunk facility of Win32 API to make the 16- to 32-bit calls possible.
This is the suggested way of connecting from 16-bit applications, such as SOLID Remote Control and SOLID SQL Editor, to a native Windows NT or Windows 95 SOLID Server (32-bit) running in the same machine.
ShMem32 is also available SOLID Server for Windows 16-bit when it is run in Windows 95 or Windows NT. When run in such an environment SOLID Server automatically starts to listen to both 16-bit and 32-bit ShMem protocols with the following specification
Listen = ShMem SOLID
Client applications first try to connect using the 16-bit version but if it does not succeed, they try ShMem32 with the following specification
Connect = ShMem32 SOLID
The TCP/IP protocol is typically used for communicating to a server process running under a UNIX operating system. When starting a server using the TCP/IP protocol, you must reserve a port number for it. You will find reserved port numbers in the /etc/services file of your system. Select a free number greater than 1024 since smaller numbers are usually reserved for the operating system.
To use the TCP/IP protocol, select TCP/IP in the list of protocols in SOLID Remote Control and enter a non-reserved port number.
The format used in the solid.ini filenetwork namesTCP/IP
Server Listen = tcpip <server port number>
Client Connect = tcpip [host computer name]
<server port number>
NOTE 1. If the server is running in the same computer
with the client program, the host computer name need not be specified.
The client computer has to have the used host name listed in its
etc/hosts
file or it must
be recognized by the DNS (Domain Name Server). You can also give
the host computer's TCP/IP address in dotted decimal format (e.g.:
102.53.94.97) instead of its host name.
NOTE 2. On Windows NT and UNIX the TCP/IP protocol is usually
included in the operating system. On other environments (like
Windows, OS/2, VAX/VMS) the TCP/IP software needs to be installed
to the system. For a list of supported TCP/IP software, contact
your SOLID Server dealer.
NOTE 3. Using TCP/IP in Windows needs a third party Windows Sockets
package.
Using Windows SocketsWindows Sockets
In the Windows operating system a third party software package containing winsock.dll file is required. The SOLID Server Windows Sockets driver has been tested with the following third party Windows Sockets packages:
The NetBIOS protocol is commonly used in the Windows (3.x, 95, NT) and OS/2 operating systems.
To use the NetBIOS protocol, select NetBIOS in the list of protocols in SOLID Remote Control, and enter a non-reserved server name.
The format used in the solid.ini filenetwork namesNetBIOS
Server Listen = netbios [-aLANA_NUMBER] <server name> Client Connect = netbios [-aLANA_NUMBER] <server name>
NOTE 1. The server name must be a character string
at most 16 characters long. It may not begin with an asterisk
(*).
NOTE 2. In the above format the optional -aLANA_NUMBER is used
to override the default value of the LANA number. The default
LANA number is 0 in Windows environments, 1 in OS/2 environments.
NOTE 3. In Windows NT the available LANA_NUMBERs can be checked
using the Network Setup found in the Control Panel. The default
value 0 may not be generally very good. You should choose the
one(s) where the protocol stack matches the other computers you
are using. The LANA_NUMBER (Network Route: Nbf->Elnk3->Elnk31)
that uses NetBEUI as a transport usually functions quite smoothly
when used for SOLID communication.
NOTE. 4. The server names have to be unique in the whole network.
Establishing a connection or starting the listening using the
NetBIOS protocol may be somewhat slow because of the checks needed
for uniqueness.
Named Pipes is a protocol commonly used in the Windows (WfW, 95, NT) and OS/2 operating systems.
Windows for Workgroups and Windows 95 support Named Pipes only in client end communication. Windows NT supports Named Pipes both in server and client end communication.
In OS/2 Named Pipes can always be used locally inside one machine. OS/2 3.0 Warp Connect contains the Lan Requester and hereby also supports a Named Pipes network client. Using Named Pipes from inside another OS/2 system (1.x, 2.x or 3.0 Warp) over a network needs additionally the Lan Requester client software to be installed. To run Named Pipes the server always needs components from the Lan Server product to be installed.
The format used in the solid.ini filenetwork namesNamed Pipes
Server Listen = nmpipe <server name>
Client Connect = nmpipe [host computer name]
<server name>
NOTE 1. The server names must be character strings
at most 50 characters long.
NOTE 2. If the server is running in the same computer with the
client program, the host computer name should not be specified.
The only exception to this is Windows NT: if a Windows 16-bit
client application (such as SOLID SQL Editor) is connecting
to a native Windows NT process (SOLID Server) the host
machine name must be specified.
NOTE 3. In order to connect to the SOLID Server for Windows
NT through NamedPipes user must have at least the same rights
as the user, who started the server. For example if an administrator
starts the server only users with administrator's rights are able
to connect to the server through NamedPipes. Similarly if a user
with normal user's rights starts the server all users with greater
rights are able to connect the server through NamedPipes. If a
user doesn't have proper rights, SOLID Communication Error 21306
message will be given. This has been fixed in SOLID Server
version 2.1.
NOTE 4. It is not recommended to use the Named Pipes communication
from SOLID Remote Control. The asynchronous nature of SOLID
Remote Control communication may cause problems with Named
Pipes.
The DECnet protocol is used to connect to a server running on a OpenVMS system. To use this protocol in Windows, you need to have PATHWORKS version 4.0 or later installed to your client computer. To use this protocol in OS/2, you need to have PATHWORKS version 2.0 or later installed to your client computer.
To use the DECnet protocol, select DECnet in the list of protocols in SOLID Remote Control and enter a non-reserved server name.
The format used in the solid.ini filenetwork namesDECnet
Server Listen = decnet <server name> Client Connect = decnet <node name> <server name>
NOTE. To establish a connection the DECnet node name of the server machine is configured to your node database. The node name can be given either as a node number such as '1.1' or as a node name such as 'VAX1'.
The IPX/SPX protocol is used to communicate with SOLID Server for Novell Netware.
SOLID Server for Novell Netware starts listening with the default listening name SOLID if no listening name is specified in the configuration file solid.ini. When SOLID Server starts, it prints out the network and node information of the server machine.
The SOLID Server listening name can be given as a character string or as a socket number. If the given network name is a valid socket number i.e. hex number with exactly 4 characters (e.g. 400F) SOLID Server starts listening in the given port. If the network name could not be interpreted as a socket number it is treated as a server name character string and is published using Novell NetWare SAP (Service Advertising Protocol).
Connecting to a SOLID Server using SAP needs specifying only the correct server name in Connect parameter. If the server is listening using some given port, the full NLM server info (see comment below) has to be given.
To use the IPX/SPX protocol, select IPX/SPX in the list of protocols in SOLID Remote Control and enter a non-reserved server name.
The format used in the solid.ini filenetwork names IPX/SPX
Server Listen = spx {<server name> | <socket number>}
Client Connect = spx {<NLM server info> |
<server name>}
NOTE 1. The server names must be less than 48 characters
long.
NOTE 2. In the above format, <NLM server info> stands for
a string containing the network number, the node number and the
socket number separated by colons. For example, <NLM server
info> for network 1, node 1, socket number 1313 is 00000001:000000000001:1313.
You can abbreviate the information be removing the leading zeros.
The previous server info could also be written as 1:1:1313.
<server name> stands for an alphanumeric string.
NOTE 3. The possibility to use socket numbers as the listening
name is supported mainly for historical reasons. SAPing is intended
to be the primary method.
NOTE 4. After removing a network name or shutting down SOLID Server
using SOLID Remote Control the server name used may still
remain reserved for up to one minute although everything completes
successfully. The error 'network name in use' is displayed if
SOLID Server is restarted immediately. This is a 'normal'
NetWare SAP feature and happens more often if your network consists
of more than one NetWare server. Propagating the SAP cancellation
packets to every network node may take a while.
The following tables summarize the possible operating systems and required forms for network names for the various communication protocols.
SERVER protocols and network names
| Protocol | Server OS | Network name in solid.ini file |
| Shared
Memory |
Windows 3.x WfW Windows 95 Windows NT OS/2 | Listen = shmem <server> |
| NetBIOS | Windows 3.x
WfW Windows 95 Windows NT OS/2 | Listen = netbios <server> |
| Named Pipes | Windows NT
OS/2 1 | Listen = nmpipe <server> |
| IPX/SPX | Novell Netware | Listen = spx <server>
Listen = spx <socket number> |
| TCP/IP | Windows 3.x WfW Windows 95 Windows NT UNIX OS/2 4 VAX/VMS 2 OpenVMS 2 | Listen = tcpip <port> |
| DECnet | Windows 3.x 3
WfW 3 VAX/VMS OpenVMS | Listen = decnet <server> |
CLIENT protocols and network names
| Protocol | Client OS | Network name in solid.ini file |
| Shared
Memory |
Windows 3.x WfW Windows 95 Windows NT OS/2 | Connect = shmem <server> |
| NetBIOS | Windows 3.x
WfW Windows 95 Windows NT OS/2 | Connect = netbios <server> |
| Named Pipes | WfW
Windows 95 Windows NT OS/2 | Connect = nmpipe [host] <server> |
| IPX/SPX | Novell Netware Windows 3.x 1
OS/25 WfW 1 | Connect = spx <server> Connect = spx <NLM server info> |
| TCP/IP | Windows 3.x WfW Windows 95 Windows NT UNIX OS/26 VAX/VMS 4 OpenVMS 4 | Connect = tcpip [host] <port> |
| DECnet | Windows 3.x 2
WfW 2 VAX/VMS OpenVMS OS/2 3 | Connect = decnet <host> <server> |
1) requires WfW with IPX/SPX or native Novell client support
2) requires Pathworks for DOS/Windows v 4.1 or later
3) requires Pathworks for OS/2 2.0 or later
4) requires TCP/IP product (available from Digi