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 Digital)
5) requires NetWare client for OS/2
6) requires IBM TCP/IP v 1.2.1 or later
NOTE. The previous tables contain the protocols and operating systems that were available when this guide was printed. For an updated list, contact your SOLID Server dealer.
By managing the parameters of your SOLID Server, you can modify the environment, performance and operation of the server process. This chapter explains SOLID Server parameters and their settings. The topic Parameter management in this chapter gives you step-by-step instructions on how to view and set the parameter values on the Parameters page in SOLID Remote Control.
When SOLID Server is started, it attempts to open the configuration file solid.ini in the current directory or in the directory set by the SOLIDDIR environment variable. The configuration values for the server parameters are included in this file. If the file does not exist, SOLID Server will use default settings for the parameters. Also, if a value for a parameter is not set in the solid.ini file, SOLID Server will use a default value for the parameter. The default values depend on the operating system you are using.
Generally, default settings offer good performance and operability, but in some cases modifying some parameter values can improve performance.
The following paragraphs will explain the most important SOLID Server parameters and their default settings. See Appendix B Configuration Parameters of this manual for a description of all parameters.
The parameter Connect in the [Com] section defines a network name for a client program. The client program will establish a connection to a server program with a similar Listen network name. The format for these parameters is explained in the chapter Communication protocols.
If the connect information is defined in the client program with the SQLConnect function, this parameter is ignored. In the Windows and Windows NT operating systems the connection can be made by using SOLID ODBC driver. When a client program is using SOLID ODBC driver the ODBC Data Source Name is used and the Connect parameter has no effect. The solid.ini file, which includes the Connect parameter, must be located in the client program's working directory or in the directory set by SOLIDDIR environment variable.
The following connect line will connect a client program using the TCP/IP protocol to a SOLID Server running in a computer named 'spiff' and server port number '1313'.
connect = tcpip spiff 1313
In SOLID Server data and indexes are stored in the same logical files. The term 'index file' is used here as a synonym for the term 'database file'.
The FileSpec parameter describes the location and the maximum size of the index file (database file)FileSpec. You can use it to define the location and maximum value the index file may grow to.
You can also use the FileSpec parameter to divide the index file into multiple files and onto multiple disksindex filesplitting to multiple disks. To do this, specify another FileSpec parameter identified by the number 2. The index file will be written to the second file if it grows over the maximum value of the first FileSpec parameter. The default value for this parameter is solid.db, 2147483647 (which equals 2 GB expressed in bytes).
FileSpec_1=SOLID.DB 2147483647
In the following example, the parameters divide the index file on the disks C:, D: and G: to be split after growing larger than 1 GB (=1073741824 bytes).
FileSpec_1=C:\SOLID\SOLID.1 1073741824 FileSpec_2=D:\SOLID\SOLID.2 1073741824 FileSpec_3=G:\SOLID\SOLID.3 1073741824
NOTE. The index file locations entered must be valid pathnames in the server operating system! For example, if the server runs on a UNIX operating system, path separators must be slashes instead of backslashes.
Splitting the index file on multiple disks will increase the performance of the server because multiple disk heads will access the data in your index file. There is no limit to the number of index files you may use.
Backups of the database, log files and the configuration file solid.ini are copied to the backup directory. The default directory 'backup' is a directory relative to your SOLID directory. For example if the parameter is
BackupDirectory= bu
then the backup will be written to a directory that is a sub-directory of the SOLID directory. You may also specify a absolute path name for the directory. For example:
BackupDirectory=e:\backup\solid
The backup directory must exist and it must have enough disk space for the backup files. It can be set to any existing directory except the database file directory, the log file directory or the working directory.
NOTE. The backup directory entered must be a valid pathname in the server's operating system! For example if the server runs on a UNIX operating system, path separators must be slashes instead of backslashes.
Log files are created automatically to the directory specified and by using the filename structure specified by the parameter FileNameTemplate in the Logging section. For example, the following setting
FileNameTemplate = d:\logdir\sol#####.log
instructs SOLID Server to create log files to directory d:\logdir and to name them sequentially starting from sol00001.log .
The TmpDir[1...N] parameter in the Sorter section specifies the directory (or directories) that can be used for the external sorter algorithm which is used for sorting processes that do not fit in main memory. All temporary files used by the external sort are created in this directory (or directories) and are automatically deleted. Setting this parameter enables the use of external sorter.
The CacheSize parameter (the default value depends on the server operating system) defines the amount of main memory the server allocates for the cache. Although SOLID Server is able to run with a small cache size, a larger cache size speeds up the server. The cache size needed depends on the size of the index file, the number of connected users, and the nature of the operations executed against the server. You can view your current CacheSize parameter value with SOLID Remote Control.
The Threads parameter in the [Srv] section defines the amount of threads the SOLID database engine will use in addition to the communication, I/O and log manager threads. The default value is two threads for database engine use.
The Info parameter in the [SQL] section specifies the tracing level on the SQL parser and optimizer as an integer between 0 (no tracing) and 8 (extensive trace outputting). Trace information will be output to the file named soltrace.out in the SOLID directory.
These parameters control the outputting of network trace information vital to solving possible network problems. By setting the parameter Trace to the value Yes, SOLID Server starts logging trace information on network messages to the file specified in the TraceFile parameter.
SOLID Server parameters and their values can be viewed and modified from SOLID Remote Control or by directly editing the solid.ini file in the SOLID directory.
To view current parameter values using SOLID Remote Controlparametersviewing current values
The parameters displayed are the parameters currently active in the server. If you have not set a parameter value, the displayed value is the default value for the parameter. The default values are set at start-up and depend on the operating system SOLID Server runs on.
To set a new parameter value using SOLID Remote Controlparameterssetting new values
On the PARAMETERS page, some parameters are shown in gray shade. These parameters have constant values. The values were set when the database was created, and they cannot be modified through SOLID Remote Control.
If you want to use different constant values, you have to create a new database. Before creating a new database, set new constant values by editing the solid.ini file in the SOLID directory.
The example below sets a new block size for the index file by adding the following lines to the solid.ini fileindex filechanging block size:
[Indexfile] Blocksize=4096
After editing and saving the solid.ini file, move the old database and log files, and start SOLID Server. The server program will create a new database with the new constant values from the solid.ini file.
This chapter discusses techniques that you can use to improve the performance of SOLID Server.
Tuning the SQL statements, especially in applications where complex queries are involved, is generally the most efficient means of improving the database performance.
You should tune your application before tuning the RDBMS because:
So, find out what data your application processes, what are the SQL statements used and what operations the application performs on the data.
SOLID Server provides the following tools that may be helpful in tuning applications:
For additional information on how to use these tools, refer to chapter SOLID Server Diagnostic Tools.
Indexes can be used to improve the performance of queries. A query that references an indexed column in its WHERE clause can use the index. If the query selects only the indexed column, the query can read the indexed column value directly from the index, rather than from the table.
If a table has a primary key, SOLID Server orders the rows on disk in the order of the values of the primary key. Otherwise the rows are ordered using the ROWID, i.e. the rows are stored on disk in the order they are inserted into the database.
Indexes improve the performance of queries that select a small percentage of rows from a table. You should consider using indexes for queries that select less than 15% of table rows.
If a query does not use an index, SOLID Server must perform a full table scan to execute the query. This involves reading all rows of a table sequentially. Each row is examined to determine whether it meets the criteria of the query's WHERE clause. Finding a single row with an indexed query can be substantially faster than finding the row with a full table scan. On the other hand, a query that selects more than 15% of a table's rows may be performed faster by a full table scan than by an indexed query.
To perform a full table scan, every block in the table is read. For each block every row stored in the block is read. To perform an indexed query the rows are read in the order in which they appear in the index, regardless of which blocks contain them. If a block contains more than one selected row it may be read more than once. So, there are cases when a full table scan requires less I/O than an indexed query.
An index can be made up of more than one column. Such an index is called a concatenated index.
Whether or not a SQL statement uses a concatenated index is determined by the columns contained in the WHERE clause of the SQL statement. A query can use a concatenated index if it references a leading portion of the index in the WHERE clause. A leading portion of an index refers to the first column or columns specified in the CREATE INDEX statement.
Example:
create index job_sal_deptno on emp(job, sal, deptno);
This index can be used by these queries:
select * from emp where job = 'clerk' and sal = 800 and deptno = 20; select * from emp where sal = 1250 and job = salesman; select job, sal from emp where job = 'manager' ;
The following query does not contain the first column of the index in its WHERE clause and cannot use the index:
select * from emp where sal = 6000;
The following list gives guidelines choosing columns to index:
Your operating system may store information in
Your operating system may also move information from one location to another. Depending on your operating system, this movement is called paging or swapping. Many operating systems page and swap to accommodate large amounts of information that do not fit into real memory. However, this takes time. Excessive paging or swapping can reduce the performance of your operating system and indicates that your system's total memory may not be large enough to hold everything for which you have allocated memory. You should either increase the amount of total memory or decrease the amount of database cache memory allocated.
The information used by SOLID Server is stored either in memory or on disk. Since memory access is faster than disk access, it is desirable for data requests to be satisfied by access to memory rather than access to disk.
The basic element of the database server memory management system is a pool of central memory buffers of equal size. The size of the memory buffers and their amount can be configured to meet the demands of different application environments.
Database cache uses available memory to store information that is read from the hard disk. When an application next time requests this information, the data is read from memory instead of from the hard disk. The default value of cache depends on the platform used and can be changed by changing the Cachesize parameter. Increasing the value is recommended when there are several concurrent users.
The following values can be used as a starting point:
NOTE. You should increase the value of Cachesize very carefully. Too large a value leads to very poor performance.
The performance of many software systems is inherently limited by disk I/O. Often CPU activity must be suspended while I/O activity completes.
Distributing I/O
Disk contention occurs when multiple processes try to access the same disk simultaneously. To avoid this move files from heavily accessed disks to less active disks until they all have roughly the same amount of I/O.
So:
SOLID Server does all sorting by default in memory. The amount of memory used for sorting is determined by the parameter SORTARRAYSIZE in the [SQL] section. If the amount of data to be sorted does not fit into the allocated memory, you may want to increase the value of the parameter SORTARRAYSIZE. If there is not enough memory to increase the value of SORTARRAYSIZE you should activate external sort that stores intermediate information to disk.
The external disk sort is activated by adding the following section and parameters in the configuration file solid.ini:
[sorter] TmpDir_1 = c:\tmp
Additional sort directories are added with similar definitions:
[sorter] TmpDir_1 = c:\tmp TmpDir_2 = d:\tmp TmpDir_3 = e:\tmp
Checkpoints affect:
Frequent checkpoints can reduce the recovery time in the event of a system failure. If the checkpoint interval is small, then relatively few changes to the database are made between checkpoints and relatively few changes must be recovered.
Checkpoints cause SOLID Server to perform I/O, so they momentarily reduce the runtime performance. This overhead is usually small.
SOLID Server provides several diagnostic tools for observing performance. These tools, SQL info facility and the EXPLAIN PLAN statement, are helpful in tuning your application and identifying inefficient SQL statements in your application.
SOLID Server provides also tools for tracing the communication between client and server and locating problems that may slow down your application. The Network trace facility can be used in the server end and the Ping facility in the client end.
Run your application with the SQL info facility enabled. The SQL info facility generates information for each SQL statement processed by SOLID Server.
| Info value | Information |
| 0 | no output |
| 1 | table, index and view info in SQL format |
| 2 | SQL execution graphs |
| 3 | some SQL estimate info, Solid selected key name |
| 4 | all SQL estimate info, Solid selected key info |
| 5 | Solid info also from discarded keys |
| 6 | Solid table level info |
| 7 | SQL info from every fetched row |
| 8 | Solid info from every fetched row |
The SQL info facility is turned on by setting a non-zero value to the Info parameter in the [SQL] section of the comfiguration file. The output is written to a file named soltrace.out in the SOLID directory.
Example:
[SQL] info= 1
The SQL info facility can also be turned on with the following SQL statement (this sets info on for only for the client that executes the statement):
SET SQL INFO ON LEVEL info-value FILE file-name
and turned off with the following SQL statement:
SET SQL INFO OFF
The EXPLAIN PLAN statement is used to show the execution plan
that the SQL optimizer has selected for a given SQL statement.
An execution plan is a series of primitive operations, and an
ordering of these operations, that SOLID Server performs
to execute the statement. Each operation in the execution plan
is called a unit.
| Unit | Description |
| JOIN UNIT | Join unit joins two or more tables. The join can be done by using loop join or merge join. Note that the join unit is generated also for queries that reference only a single table. In that case no join is executed in the join unit, the join unit just passes the rows without manipulating them. |
| TABLE UNIT | Table unit is used to fetch the data rows from a table. Table unit is always the last unit in the chain, since it is responsible for fetching the actual data from the index or table. |
| ORDER UNIT | Order unit is used to order rows for grouping or to satisfy ORDER BY. The ordering can be done in memory or using an external disk sorter. |
| GROUP UNIT | Group unit is used to do grouping and aggregate calculation. |
The syntax of the EXPLAIN PLAN statement is:
EXPLAIN PLAN FOR sql-statement
The table returned by the EXPLAIN PLAN statement contains the
following columns.
| Column name | Description |
| ID | The output row number, used only to guarantee that the rows are unique. |
| UNIT_ID | This is the internal unit id in the SQL interpreter. Each unit has a different id. The unit id is a sparse sequence of numbers, because the SQL interpreter generates unit ids also for those units that are removed during the optimization phase. If more than one row has the same unit id it means that those rows belong to the same unit. For formatting reasons the info from one unit may be divided into several different rows. |
| PAR_ID | Parent unit id for the unit. The parent id number refers to the id in the UNIT_ID column. |
| JOIN_PATH | For join unit there is a join path which specifies which tables are joined in the join unit and the join order for tables. The join path number refers to the unit id in the UNIT_ID column. It means that the input to the join unit comes from that unit. The order in which the tables are joined is the order in which the join path is listed. The first listed table is the innermost table in a loop join. |
| UNIT_TYPE | Unit type is the execution graph unit type. |
| INFO | Info column gives additional info. It may contain e.g. index usage, the database table name and constraints used in the database engine to select rows. Note that the constraints listed here may not match those constraints given in the SQL statement. |
The following texts may exist in the INFO column for different
types of units.
| Unit type | Text in Info column | Description |
| TABLE UNIT | <tablename> | The table unit refers to table <tablename>. |
| TABLE UNIT | <contraints> | The constraints that are passed to the database engine are listed. If for example in joins the constraint value is not known in advance, the constraint value is displayed as NULL. |
| TABLE UNIT | SCAN TABLE | Full table scan is used to search for rows. |
| TABLE UNIT | SCAN <indexname> | Index <indexname> is used to search for rows. If all selected columns are found from an index, sometimes it is faster to scan the index instead of the clustering key because the index has fewer disk blocks. |
| TABLE UNIT | PRIMARY KEY | The primary key is used to search rows. This differs from SCAN in that the whole table is not scanned because there is a limiting constraint to the primary key attributes. |
| TABLE UNIT | INDEX <indexname> | Index <indexname> is used to search for rows. For every matching index row, the actual data row is fetched separately. |
| TABLE UNIT | INDEX ONLY <indexname> | Index <indexname> is used to search for rows. All selected columns are found from the index, so the actual data rows are not fetched separately. |
| JOIN UNIT | MERGE JOIN | Merge join is used to join the tables. |
| JOIN UNIT | LOOP JOIN | Loop join is used to join the tables. |
| ORDER UNIT | NO ORDERING REQUIRED | No ordering is required, the rows are retrieved in correct order from the database engine. |
| ORDER UNIT | EXTERNAL SORT | External sorter is used to sort the rows. To enable external sorter, the temporary directory name must be specified in the Sorter section of the configuration file. |
| ORDER UNIT | FIELD <n> USED AS PARTIAL ORDER | Internal sorter (in-memory sorter) is used for sorting and the rows retrieved from the database engine are partially sorted with column number <n>. The partial ordering helps the internal sorter to avoid multiple passes over the data. |
| ORDER UNIT | NO PARTIAL SORT | Internal sorter is used for sorting and the rows retrieved in random order from the database engine. |
EXPLAIN PLAN FOR SELECT * FROM TENKTUP1 WHERE UNIQUE2_NI BETWEEN 0 AND 99;
| ID | UNIT_ID | PAR_ID | JOIN_
PATH | UNIT_ TYPE | INFO |
| 1 | 2 | 1 | 3 | JOIN UNIT | |
| 2 | 3 | 2 | 0 | TABLE UNIT | TENKTUP1 |
| 3 | 3 | 2 | 0 | FULL SCAN | |
| 4 | 3 | 2 | 0 | UNIQUE2_NI <= 99 | |
| 5 | 3 | 2 | 0 | UNIQUE2_NI >= 0 | |
| 6 | 3 | 2 | 0 |
Execution graph:
JOIN UNIT 2 gets input from TABLE UNIT 3
TABLE UNIT 3 for table TENKTUP1 does a full table scan with constraints UNIQUE2_NI <= 99 and UNIQUE2_NI >= 0
Example
1. Execution graph
EXPLAIN PLAN FOR SELECT * FROM TENKTUP1, TENKTUP2 WHERE TENKTUP1.UNIQUE2 > 4000 AND TENKTUP1.UNIQUE2 < 4500 AND TENKTUP1.UNIQUE2 = TENKTUP2.UNIQUE2;
| ID | UNIT_ID | PAR_ID | JOIN_
PATH | UNIT_ TYPE | INFO |
| 1 | 6 | 1 | 9 | JOIN UNIT | MERGE JOIN |
| 2 | 6 | 1 | 10 | ||
| 3 | 9 | 6 | 0 | ORDER UNIT | NO ORDERING REQUIRED |
| 4 | 8 | 9 | 0 | TABLE UNIT | TENKTUP2 |
| 5 | 8 | 9 | 0 | PRIMARY KEY | |
| 6 | 8 | 9 | 0 | UNIQUE2 < 4500 | |
| 7 | 8 | 9 | 0 | UNIQUE2 > 4000 | |
| 8 | 8 | 9 | 0 | ||
| 9 | 10 | 6 | 0 | ORDER UNIT | NO ORDERING REQUIRED |
| 10 | 7 | 10 | 0 | TABLE UNIT | TENKTUP1 |
| 11 | 7 | 10 | 0 | PRIMARY KEY | |
| 12 | 7 | 10 | 0 | UNIQUE2 < 4500 | |
| 13 | 7 | 10 | 0 | UNIQUE2 > 4000 | |
| 14 | 7 | 10 | 0 |
Execution graph:
JOIN UNIT 6 the input from order units 9 and 10 are joined using merge join algorithm
ORDER UNIT 9 orders the input from TABLE UNIT 8. Since the data is retrieved in correct order, no real ordering is needed
ORDER UNIT 10 orders the input from TABLE UNIT 7. Since the data is retrieved in correct order, no real ordering is needed
TABLE UNIT 8: rows are fetched from table TENKTUP2 using primary key. Constraints UNIQUE2 < 4500 and UNIQUE2 > 4000 are used to select the rows
TABLE UNIT 7: rows are fetched from table TENKTUP1 using primary key. Constraints UNIQUE2 < 4500 and UNIQUE2 > 4000 are used to select the rows
Example
2. Execution graph
SOLID Server provides the following tools for observing the communication between client and server:
You can use these tools to analyze the functionality of the networking between client and server.
By setting the [Com] section parameter Trace in the configuration file solid.ini to the value Yes, SOLID Server starts logging trace information on established network connections to the default trace file or file specified in the TraceFile parameter. These messages contain information about:
The Network trace facility is turned on either by editing the configuration file
[Com]
Trace={Yes|No}
; default No
TraceFile= file-name
; default soltrace.out
or using the environment variables SOLTRACE and SOLTRACEFILE to override the definitions in the configuration file. These files are created into the server's and client's working directories. Settings of SOLTRACE and SOLTRACEFILE environment variables have the same effect as the parameters Trace and TraceFile in the configuration file.
NOTE. Defining the TraceFile configuration parameter or the SOLTRACEFILE environment variable automatically turns on the Network trace facility.
The third alternative to turn on the Network trace
facility is to use the option -t and/or -ofilename as a
part of the network name. The option
-t turns on the Network trace facility. The option -o turns on
the facility and defines the name of the trace output file.
The precedence of the previous three alternatives is:
Using the environment variables overrides the definitions in the configuration file. Using the options -t or -o overrides all other definitions.
Example 1. Defining parameter trace in the configuration file
[Com] Connect=nmp SOLID Listen=nmp SOLID Trace=Yes
Example 2. Defining environment variables
set SOLTRACE=Yes
or
set SOLTRACEFILE=trace.out
Example 3. Using network name options
[com] Connect=nmp -t solid Listen=nmp -t solid
or
[com] Connect=nmp -oclient.out solid Listen=nmp -oserver.out solid
The Ping facility can be used to test the performance and functionality of the networking. The Ping facility is built in all SOLID clients and is turned on with the network name option -plevel. The output of the Ping facility is created into the client's working directory. The output file name is named using the parameter TraceFile or the environment variable SOLTRACEFILE or with the network name option -ofile-name.
The Ping facility levels are:
| Setting | Function | Description |
| 0 | no operation | do nothing, default |
| 1 | check that server is alive | exchange one 100 byte message (plus init/done RPCs) |
| 2 | basic functional test | exchange messages of sizes 0.1K, 1K, 2K..30K, increment 1K |
| 3 | basic speed test | exchange 100 messages of sizes 0.1K, 1K, 8K and display each subresult and total time |
| 4 | heavy speed test | exchange 100 messages of sizes 0.1K, 1K, 2K, 4K, 8K, 16K and display each subresult and total time |
| 5 | heavy functional test | exchange messages of sizes 1..30K, increment 1 byte |
Example 1
The client turns on the Ping facility by using the following network name:
nmp -p1 -oping.out SOLID
This runs the Ping facility at the level 1 into a file named ping.out. This test checks if the server is responding properly and is done during the connection to the server.
After the Ping facility has been run, the client exits with the following message:
SOLID Communication return code xxx: Ping test successful/failed, results are in file FFF.XX
Clients can always use the Ping facility at level 1. Using levels 2 to 5 is possible only if the server is using the Ping facility at least at the same level.
Example 2
If the server is using the following listen parameter
[com] Listen=nmp -p3 SOLID
clients can run the Ping facility at levels 1, 2 and 3, but not 4 and 5.
NOTE. Ping clients running at level greater than 1 may cause quite heavy network traffic. They will also slow down ordinary SQL clients connected to the same SOLID Server.
SOLID Server offers sophisticated diagnostic tools and methods for producing high quality problem reports with very limited effort. Use the diagnostic tools to capture all the relevant information about the problem.
All problem reports should contain the following files and information:
Most problems can be divided into the following categories:
The following pages include a detailed instructions to produce proper problem report for each problem type. Please follow the guidelines carefully.
If the problem concerns the performance of SOLID SQL API or a specific SQL statement, you should run SQL info facility at level 4 and include the generated soltrace.out file into your problem report. This file contains the following information:
If the problem concerns the performance of SOLID Server ODBC Driver, please include the following information:
If the problem concerns the cooperation of SOLID Server and any third party standard software package, please include the following information:
Use ODBC trace option to get a log of the ODBC statements and include it to your problem report.
If the problem concerns the performance of UNIFACE driver for SOLID Server, please include following information:
If the problem concerns the performance of the communication between client and server use the Network trace facility and include the generated trace files into your problem report. Please include the following information:
The error messages are divided into the following categories:
SQL Errors
These errors are caused by erroneous SQL statements and are detected by the SOLID SQL Parser. Administrative actions are not needed.
Database Errors
These errors are detected by the SOLID Database Engine and may demand administrative actions.
System Errors
These errors are detected by the operating system and demand administrative actions.
Table Errors
These errors are caused by erroneous SQL statements and detected by the SOLID Database Engine. Administrative actions are not needed.
Server Errors
These errors are caused by erroneous administrative actions or client requests. They may demand administrative actions.
Communication Errors
These errors are caused by network errors or faulty configuration of the SOLID Server software. These errors demand administrative actions.
Procedure Errors
These errors are caused by errors in the definition or execution of a stored procedure. Administrative actions are not needed.
See also:
See Appendix D SOLID Server SQL Syntax and Appendix C SOLID Server Data Types for more information.
| Error code | Description |
| SQL Error 1 | Parsing error 'syntax error' |
| The SQL parser could not parse the SQL string. Check the syntax of the SQL statement and try again. | |
| SQL Error 2 | Table <table> can not be opened |
| You may not have privileges to access the table and its data. | |
| SQL Error 3 | Table <table> can not be created |
| Table can not be created. You may not have privileges for this operation. | |
| SQL Error 4 | Illegal column definition <column> |
| A column type in your CREATE TABLE statement is illegal. Use a legal type for the column. | |
| SQL Error 5 | Table <table> can not be dropped |
| Table can not be dropped. Only the owner (i.e. the creator) can drop it. | |
| SQL Error 6 | Illegal value specified for column <column> |
| The value specified for column is invalid. Check the value for the column. | |
| SQL Error 7 | Insert failed |
| The server failed to do the insertion. You may not have INSERT privilege on the table or it may be locked. | |
| SQL Error 8 | Delete failed |
| The server failed to do the deletion. You may not have DELETE privilege on the table or the row may be locked. | |
| SQL Error 9 | Row fetch failed |
| The server failed to fetch a row. You may not have SELECT privilege on the table or there may be an exclusive lock on the row. | |
| SQL Error 10 | View <view> can not be created |
| You can not create this view. You may not have SELECT privilege on one or more tables in the query-specification of your CREATE VIEW statement. | |
| SQL Error 11 | View <view> can not be dropped |
| You can not drop this view. Only the owner (i.e. the creator) of the view can drop it. | |
| SQL Error 12 | Illegal view definition <view> |
| The view definition is illegal. Check the syntax of the definition. | |
| SQL Error 13 | Illegal column name <column> |
| Column name is illegal. Check that the name is not a reserved name. | |
| SQL Error 14 | Call to function <function> failed |
| Function call to function failed. Check the arguments and their types. | |
| SQL Error 15 | Arithmetics error |
| An arithmetics error occurred. Check the operators, values and types. | |
| SQL Error 16 | Update failed |
| The server failed to update a row. There may a lock on a row. | |
| SQL Error 17 | View is not updatable |
| This view is not updatable. UPDATE, INSERT and DELETE operations are not allowed. | |
| SQL Error 18 | Inserted row does not meet check option condition |
| You tried to insert a row, but one or more of the column values do not meet column constraint definition. | |
| SQL Error 19 | Updated row does not meet check option condition |
| You tried to update a row, but one or more of the column values do not meet column constraint definition. | |
| SQL Error 20 | Illegal CHECK constraint |
| A check constraint given to the table is illegal. Check the types of the check constraint of this table. | |
| SQL Error 21 | Insert failed because of CHECK constraint |
| You tried to insert a row, but the values do not meet the check option conditions. | |
| SQL Error 22 | Update failed because of CHECK constraint |
| You tried to update a row, but the values do not meet the check option conditions. | |
| SQL Error 23 | Illegal DEFAULT value |
| The DEFAULT value for the column given is illegal. | |
| SQL Error 24 | Incorrect number of items in VALUES list |
| The number of items given in VALUES list is different from the number of columns in the table. Check the number of items. | |
| SQL Error 25 | Duplicate columns in INSERT column list |
| You have included a column in column list twice. Remove duplicate columns. | |
| SQL Error 26 | At least one column definition required in CREATE TABLE |
| You need to specify at least one column definition in a CREATE TABLE statement. | |
| SQL Error 27 | Illegal REFERENCES column list |
| There are wrong number of columns in your REFERENCES list. | |
| SQL Error 28 | Only one PRIMARY KEY allowed in CREATE TABLE |
| You can use only one PRIMARY KEY in CREATE TABLE. | |
| SQL Error 29 | GRANT failed |
| Granting privileges failed. You may not have privileges for this operation. | |
| SQL Error 30 | REVOKE failed |
| Revoking privileges failed. You may not have privileges for this operation. | |
| SQL Error 31 | Multiple instances of a privilege type |
| You tried to grant privileges to a role or a user. You have included multiple instances of a privilege type in the list of privileges. | |
| SQL Error 32 | Illegal constant <constant> |
| Illegal constant was found. Check the syntax of the statement. | |
| SQL Error 33 | Column name list of illegal length |
| You have entered different number of columns in CREATE VIEW statement to the view and to the table. | |
| SQL Error 34 | UPDATE expression of illegal type |
| An expression in UPDATE statement has illegal type for a column. | |
| SQL Error 35 | Column names not allowed in ORDER BY for UNION |
| You can not use column name in an ORDER BY for UNION statement. | |
| SQL Error 36 | Nested aggregate functions |
| Nested aggregate functions can not be used. For example: SUM(AVG(<column>)). | |
| SQL Error 37 | Aggregate function with no arguments |
| An aggregate function was entered with no arguments. For example: SUM(). | |
| SQL Error 38 | UNION of tables with different row types |
| You have tried to select a union of tables with incompatible row types. The row types in a union of tables must be compatible. | |
| SQL Error 39 | COMMIT WORK failed |
| Committing a transaction failed. | |
| SQL Error 40 | ROLLBACK WORK failed |
| Rolling back a transaction failed. | |
| SQL Error 41 | Savepoint could not be created |
| A savepoint could not be created. | |
| SQL Error 42 | Could not create index <index> |
| An index could not be created. You may not have privileges for this operation. You need to be an owner of the table or have SYS_ADMIN_ROLE to have privileges to create index for the table. | |
| SQL Error 43 | Could not drop index <index> |
| An index could not be dropped. You may not have privileges for this operation. You need to be an owner of the table or have SYS_ADMIN_ROLE to have privileges to drop index from the table. | |
| SQL Error 44 | Could not create schema <schema> |
| A schema could not be created. | |
| SQL Error 45 | Could not drop schema <schema> |
| A schema could not be dropped. | |
| SQL Error 46 | Illegal ORDER BY specification |
| You tried to use an ORDER BY column that does not exist. Refer to an existing column in the ORDER BY specification. | |
| SQL Error 47 | Maximum length of identifier is 31 |
| You have exceeded the maximum length for the identifier. | |
| SQL Error 48 | Subquery returns more than one row |
| You have used a subquery that returns more than one row. Only subqueries returning one row may be used in this situation. | |
| SQL Error 49 | Illegal expression <expression> |
| You tried to insert or update a table using an aggregate function (SUM, MAX, MIN or AVG) as a value. This is not allowed. | |
| SQL Error 50 | Ambiguous column name <column> |
| You have referenced a column which is exists in more than one table. Use syntax <table>.<column> to indicate which table you want to use. | |
| SQL Error 51 | Non-existent function <function> |
| You tried to use a function which does not exist. | |
| SQL Error 52 | Non-existent cursor <cursor> |
| You tried to use a cursor which is not created. | |
| SQL Error 53 | Function call sequence error |
| A function was called in wrong order. Check the sequence and success of the function calls. | |
| SQL Error 54 | Illegal use of a parameter |
| A parameter was used illegally. For example: SELECT * FROM TEST WHERE ? < ?; | |
| SQL Error 55 | Illegal parameter value |
| A parameter has an illegal value. Check the type and value of the parameter. | |
| SQL Error 56 | Only ANDs and simple condition predicates allowed in UPDATE CHECK |
| All search condition predicates are not supported. | |
| SQL Error 57 | Opening the cursor did not succeed |
| Server failed to open a cursor. You may not have cursor open at this moment. | |
| SQL Error 58 | Column <column> is not referenced in group-by-clause |
| You tried to group rows using <column>. All columns in group-by-clause must be listed in your select-list. A star ('*') notation is not allowed with GROUP BY. | |
| SQL Error 59 | Comparison between incompatible types |
| You tried to compare values which have incompatible types. Incompatible types are for example an integer and a date value. | |
| SQL Error 60 | Reference to the insert table not allowed in the source query |
| You have referenced in subquery a table where you are inserting values. This is not allowed. | |
| SQL Error 61 | Reference to the update table not allowed in subquery |
| You have referenced in subquery a table where you are updating values. This is not allowed. | |
| SQL Error 62 | Reference to the delete table not allowed in subquery |
| You have referenced in subquery a table where you are deleting values. This is not allowed. | |
| SQL Error 63 | Subquery returns more than one column |
| You have used a subquery that returns more than one column. Only subqueries returning one column may be used. | |
| SQL Error 64 | Cursor <cursor> not updatable |
| The cursor opened is not updatable. | |
| SQL Error 65 | Insert or update tried on pseudo column |
| You tried to update a pseudo column (ROWID, ROWVER). Pseudo columns are not updatable. | |
| SQL Error 66 | Could not create user <user> |
| A user could not be created. You may not have privileges for this operation. | |
| SQL Error 67 | Could not alter user <user > |
| A user could not be altered. You may not have privileges for this operation. | |
| SQL Error 68 | Could not drop user <user > |
| A user could not be dropped. You may not have privileges for this operation. | |
| SQL Error 69 | Could not create role <role> |
| A role could not be created. You may not have privileges for this operation. | |
| SQL Error 70 | Could not drop role <role> |
| A role could not be dropped. You may not have privileges for this operation. | |
| SQL Error 71 | Grant <role> failed |
| Granting role failed. You may not have privileges for this operation. | |
| SQL Error 72 | Revoke <role> failed |
| Revoking role failed. You may not have privileges for this operation. | |
| SQL Error 73 | Comparison of vectors of different length |
| You have tried to compare row value constructors that have different number of dimensions. For example you have compared (a,b,c) to (1,1). | |
| SQL Error 74 | Expression * not compatible with aggregate expression |
| The aggregate expression can not be used with * columns. Specify columns using their names when used with this aggregate expression. This usually happens when GROUP BY expression is used with the * columns. | |
| SQL Error 75 | Illegal reference to table <table> |
| You have tried to reference a table which is not in the FROM list. For example: SELECT T1.* FROM T2. | |
| SQL Error 76 | Ambiguous table name <table> |
| You have used the syntax <table>.<column name>ambiguously. For example: SELECT T1.* FROM T1 A,T1 B WHERE A.F1=0; | |
| SQL Error 77 | Illegal use of aggregate expression |
| You tried to use aggregate expression illegally. For example: SELECT ID FROM TEST WHERE SUM(ID) = 3; | |
| SQL Error 78 | Row fetch failed |
| The server failed to fetch a row. You may not have SELECT privilege on the table or there may be an exclusive lock on the row. | |
| SQL Error 79 | Subqueries not allowed in CHECK constraint |
| You tried to use subquery in a check constraint. | |
| SQL Error 80 | Sorting failed |
| External sorter is out of disk space or cache memory. Modify parameters in configuration file solid.ini. | |
| SQL Error 81 | SET syntax results in error |
| SQL Error 82 | Improper type used with LIKE |
| SQL Error 83 | Syntax error |
| SQL Error 84 | Parser error <statement> |
| SQL Error 85 | Incorrect number of columns in subquery for INSERT |
| Warning code | Description |
| SQL Warning 1 | ANSI X3.135-1989 compatibility: DEFAULT should be before constraints |
| SQL Warning 2 | ANSI X3.135-1989 compatibility: illegal identifier '<identifier>' |
| The length of the identifier exceeds 18 characters. | |
| SQL Warning 3 | Rdb compatibility: illegal identifier '<identifier>' |
| The last character of the identifier is underscore ('_'). | |
| SQL Warning 4 | ANSI X3.135-1989 compatibility: illegal literal '<literal>' |
| You have used character 'e' instead of character 'E'. For example 1.234e-54 | |
| SQL Warning 5 | Oracle compatibility: use ANY instead of SOME |
| SQL Warning 6 | ANSI X3.135-1989 compatibility: token '!=' illegal, use '<>' |
| SQL Warning 7 | ANSI X3.135-1989 compatibility: use DISTINCT instead of UNIQUE |
| SQL Warning 8 | ANSI X3.135-1989 compatibility: syntax '+=' not allowed |
| SQL Warning 9 | ANSI X3.135-1989 compatibility: CHECK in UPDATE not allowed |
| SQL Warning 10 | ANSI X3.135-1989 compatibility: "identifier" syntax not allowed |
| You have surrounded identifier with double quote characters. | |
| SQL Warning 11 | ANSI X3.135-1989 compatibility: multiple DISTINCTs not allowed |
| SQL Warning 12 | ANSI X3.135-1989 compatibility: vector predicates not allowed |
| You have used a row value constructor. | |
| SQL Warning 13 | ANSI X3.135-1989 compatibility: LEFT OUTER JOIN not allowed |
| SQL Warning 14 | ANSI X3.135-1989 compatibility: AS <column id> not allowed |
| SQL Warning 15 | ANSI X3.135-1989 compatibility: FOR READ ONLY/UPDATE not allowed |
| SQL Warning 16 | ANSI X3.135-1989 compatibility: identifiers beginning with _ not allowed |
| You have used identifier beginning with underscore ('_') character. | |
| SQL Warning 17 | ANSI X3.135-1989 compatibility: syntax <table>.* not allowed |
| SQL Warning 18 | ANSI X3.135-1989 compatibility: syntax DATE '...' not allowed |
| SQL Warning 19 | Reserved word '<reserved word>' |
| SQL Warning 20 | ANSI X3.135-1989 compatibility: NULL not allowed in SELECT list |
| SQL Warning 21 | Use '+' instead of '||' |
| SQL Warning 22 | ANSI X3.135-1989 compatibility: UNIONs in VIEWs not allowed |
| Error code | Description |
| Database Error 10001 | Key value is not found |
| Internal error: a key value can not be found from the database index. | |
| Database Error 10002 | Operation failed |
| This is an internal error indicating that the index of the table accessed is in inconsistent state. Try to drop and create the index again to recover the error. | |
| Database Error 10004 | Redefinition |
| Unexpected failure occurred in the database engine. | |
| This error may also occur during recovery: either an index or a view has been redefined during recovery. The server is not able to do the recovery. Delete log files and start the server again. | |
| Database Error 10005 | Unique constraint violation |
| You have violated a unique constraint. This happens when you have tried to insert or update a column which has a unique constraint and the value inserted or updated is not unique. | |
| This may also occur when you create users, tables or roles having same names in separate transactions. | |
| Database Error 10006 | Concurrency conflict, two transactions updated or deleted the same row |
| Two separate transactions have modified a same row in the database simultaneously. This has resulted in a concurrency conflict. | |
| Database Error 10007 | Transaction is not serialisable |
| The transaction committed is not serialisable. | |
| Database Error 10010 | No checkpoint in database |
| This error occurs when the server has crashed in the middle of creating a new database. Delete the database and log files and try to create the database again. | |
| Database Error 10011 | Database headers are corrupted |
| The headers in the database are corrupted. This may be caused by a disk error or other system failure. Restore the database from the backup. | |
| Database Error 10012 | Node split failed |
| This is an internal error. | |
| Database Error 10013 | Transaction is read-only |
| You have tried to write inside a transaction that is set read-only. Remove the write operation or unset the read-only mode in the transaction. | |
| Database Error 10014 | Resource is locked |
| This error occurs when you are trying to use a key value in an index which has been concurrently dropped. | |
| Database Error 10016 | Log file is corrupted |
| One of the log files of the database is corrupted. You can not use these log files. Delete them and start the server again. | |
| Database Error 10017 | Too long key value |
| The maximum length of the key value has been exceeded. The maximum value is one third of the size of the index leaf. | |
| Database Error 10019 | Backup is active |
| You have tried to start a backup when a backup process is already in progress. | |
| Database Error 10020 | Checkpoint creation is active |
| You have tried to start a checkpoint when a checkpoint creation is already in progress. | |
| Database Error 10021 | Failed to delete log file |
| The deletion of a log file in making a backup has failed. Reasons for the failure can be: | |
| The log file has already been deleted from the operating system. | |
| The log file has a read-only attribute. | |
| Database Error 10023 | Wrong log file, maybe the log file is from another database |
| The log file in the database directory is from another SOLID Server database. Copy the correct log files to the database directory. | |
| Database Error 10024 | Illegal backup directory |
| The backup directory is either an empty string or a dot indicating that the backup will be created in the current directory. | |
| Database Error 10026 | Transaction is timed out |
| An idle transaction has exceeded the maximum idle transaction time. The transaction has been aborted. | |
| The maximum value is set in parameter AbortTimeOut in SRV section. The default value is 120 minutes. | |
| Database Error 10027 | No active search |
| Internal error. | |
| Database Error 10028 | Referential integrity violation, foreign key values exist |
| You tried to delete a row that is referenced from a foreign key. | |
| Database Error 10029 | Referential integrity violation, referenced column values do not exist |
| The definition of a foreign key does not uniquely identify a row in the referenced table. | |
| Database Error 10030 | Backup directory 'directory name' does not exist |
| Backup directory is not found. Check the name of the backup directory. | |
| Database Error 10031 | Transaction detected a deadlock, transaction is rolled back |
| Deadlock detected. If necessary, begin transaction again. | |
| Database Error 10032 | Wrong database block size specified |
| The block size of the database file differs from the blocksize given in the configuration file solid.ini. | |
| Database Error 10033 | Primary key unique constraint violation |
| Your primary key definition is not unique. | |
| Database Error 10034 | Sequence name <sequence> conflicts with an existing entity |
| Choose a unique name for a sequence. The specified name is already used. | |
| Database Error 10035 | Sequence does not exist |
| Check the name of the sequence. | |
| Database Error 10036 | Data dictionary operation is active for accessed sequence |
| Create or drop operation is active for the accessed sequence. Try again. | |
| Database Error 10037 | Can not store sequence value, the target data type is illegal |
| The valid target data types are INTEGER and BINARY. | |
| Database Error 10038 | Illegal column value for descending index |
| Corrupted data found in descending index. Drop the index and create it again. | |
| Database Error 10040 | Log file write failure, probably the disk containing the log files is full |
| Shut down the server and reserve more disk space for log files. | |
| Database Error 10041 | Database is read-only |
| Database Error 10042 | Database index check failed, the database file is corrupted |
| Database Error 10043 | Database free block list corrupted, same block twice in free list |
| Database Error 10044 | Primary key can not contain blob attributes |
| Database Error 10045 | This database is a hot standby slave, the database is read only |
| Database Error 10046 | Operation failed, data dictionary operation is active |
| Database Error 10047 | Replicated transaction is aborted |
| Database Error 10048 | Replicated transaction contains schema changes, operation failed |
| Error code | Description |
| System Error 11000 | File open failure |
| The server is unable to open the database file. Reason for the failure can be: | |
| The database file has been set read-only. | |
| You do not have rights to open the database file in write mode. | |
| Another SOLID Server is using the database file. | |
| Correct the error and try again. | |
| System Error 11001 | File write failure |
| Server is unable to write to the disk. The database files may have a read-only attribute set or you may not have rights to write to the disk. Add rights or unset read-only attribute and try again. | |
| System Error 11002 | File write failed, disk full |
| Server failed to write to the disk, because the disk is full. Free disk space or move the database file to another disk. You can also split the database file to several disks using the FileSpec_[1-N] parameter in IndexFile INDEXFILEsection. | |
| System Error 11003 | File write failed, configuration exceeded |
| Writing to the database file failed, because the maximum database file size set in FileSpec_[1-N] parameter is exceeded. | |
| System Error 11004 | File read failure |
| An error occurred reading a file. This may indicate a disk error in your system. | |
| System Error 11005 | File read beyond end of file |
| Internal error. | |
| System Error 11006 | File read failed, illegal file address |
| An error occurred reading a file. This may indicate a disk error in your system. | |
| System Error 11007 | File lock failure |
| The server failed to lock the database file. This error occurs in the Windows version, if you do not have SHARE.EXE loaded. To correct the failure: | |
| 1. Exit Windows | |
| 2. Load SHARE.EXE | |
| 3. Delete the database file SOLID.DB and log files. | |
| 4. Start Windows and launch SOLID Server. | |
| System Error 11008 | File unlock failure |
| Server failed to unlock a file. | |
| System Error 11009 | File free block list corrupted |
| Internal error. | |
| System Error 11010 | Too long file name |
| Filename specified in parameter FileSpec_[1-N] is too long. Change the name to a proper file name. | |
| System Error 11011 | Duplicate file name specification |
| Filename specified in parameter FileSpec_[1-N] is not unique. Change the name to a proper file name. | |
| System Error 11012 | License information not found, exiting from SOLID Server |
| Check the existence of your solid.lic file. | |
| System Error 11013 | License information is corrupted |
| Your solid.lic file has been corrupted. | |
| System Error 11014 | Database age limit of evaluation license expired |
| System Error 11015 | Evaluation license expired |
| System Error 11016 | License is for different CPU architecture |
| System Error 11017 | License is for different OS environment |
| System Error 11018 | License is for different version of this OS |
| System Error 11019 | License is not valid for this server version |
| System Error 11020 | License information is corrupted |
| System Error 11021 | Problem with Your license, please contact SOLID Information Technology Ltd. immediately |
| System Error 11022 | Desktop license is only for local <protocol> communication, cannot use protocol <protocol> for listening |
| Error code | Description |
| Table Error 13001 | Illegal character constant <constant> |
| An illegal character constant was found in the SQL statement. | |
| Table Error 13002 | Type CHAR not allowed for arithmetics |
| You have entered a calculation having a character type constant. Character constants are not supported in arithmetics. | |
| Table Error 13003 | Aggregate function <function> not available for ordinary call |
| Aggregate functions can not be used for ordinary function calls. | |
| Table Error 13004 | Illegal aggregate function <parameter> parameter |
| An illegal parameter has been given to an aggregate function. Aggregate function parameters can only be column names or numbers. | |
| Table Error 13005 | SUM and AVG not supported for CHAR type |
| Aggregate functions SUM and AVG are not supported for character type parameters. | |
| Table Error 13006 | SUM or AVG not supported for DATE type |
| Aggregate functions SUM and AVG are not supported for date type parameters. | |
| Table Error 13007 | Function <function> is not defined |
| The function you tried to use is not defined. | |
| Table Error 13009 | Division by zero |
| A division by zero has occurred. | |
| Table Error 13011 | Table <table> does not exist |
| You have referenced a table which does not exist or you do not have REFERENCES privilege on the table. | |
| Table Error 13013 | Table name <table> conflicts with an existing entity |
| Choose a unique name for a table. The specified name is already used. | |
| Table Error 13014 | Index <index> does not exist |
| You have referenced an index which does not exist. | |
| Table Error 13015 | Column <column> does not exist on table <table> |
| You have referenced a column in a table which does not exist. | |
| Table Error 13016 | User does not exist |
| You have referenced a user which does not exist. | |
| Table Error 13018 | Join table is not supported |
| Joined tables are not supported in this version of SOLID Server. | |
| Table Error 13019 | Transaction savepoints are not supported |
| Transaction savepoints are not supported in this version of SOLID Server. | |
| Table Error 13020 | Default values are not supported |
| Default column values are not supported in this version of SOLID Server. | |
| Table Error 13021 | Foreign keys are not supported |
| Foreign keys are not supported in this version of SOLID Server. | |
| Table Error 13022 | Descending keys are not supported |
| Descending keys are not supported in this version of SOLID Server. | |
| Table Error 13023 | Schema is not supported |
| Schema is not supported in this version of SOLID Server. | |
| Table Error 13025 | Update through a cursor with no current row |
| You have tried to update using cursor, but you do not have current row in the cursor. | |
| Table Error 13026 | Delete through a cursor with no current row |
| You have tried to delete using cursor, but you do not have current row in the cursor. | |
| Table Error 13028 | View <view> does not exist |
| You have referenced a view which does not exist. | |
| Table Error 13029 | View name <view> conflicts with an existing entity |
| Choose a unique name for a view. The specified name is already used. | |
| Table Error 13030 | No value specified for NOT NULL column <column> |
| You have not specified a value for a column which is defined NOT NULL. | |
| Table Error 13031 | Data dictionary operation is active for accessed table or key |
| You can not access the table or key, because a data dictionary operation is currently active. Try again after the data dictionary operation has completed. | |
| Table Error 13032 | Illegal type <type> |
| You have tried to create a table with a column having an illegal type. | |
| Table Error 13033 | Illegal parameter <parameter> for type <type> |
| The type of the parameter you entered is illegal in this column. | |
| Table Error 13034 | Illegal constant <constant> |
| You have entered an illegal constant. | |
| Table Error 13035 | Illegal INTEGER constant <constant> |
| You have entered an illegal integer type constant. Check the syntax of the statement and try again. | |
| Table Error 13036 | Illegal DECIMAL constant <constant> |
| You have entered an illegal decimal type constant. Check the decimal number and try again. | |
| Table Error 13037 | Illegal DOUBLE PREC constant <constant> |
| You have entered an illegal double precision type constant. Check the number and try again. | |
| Table Error 13038 | Illegal REAL constant <constant> |
| You have entered an illegal real type constant. Check the real number and try again. | |
| Table Error 13039 | Illegal assignment |
| You have tried to assign an illegal value for a column. | |
| Table Error 13040 | Aggregate <function> function is not defined |
| The aggregate function you tried to use is not supported. | |
| Table Error 13041 | Type DATE not allowed for arithmetics |
| DATE type columns or constants are not allowed in arithmetics. | |
| Table Error 13042 | Power arithmetic not allowed for NUMERIC and DECIMAL data type |
| Decimal and numeric data types do not support power arithmetics. | |
| Table Error 13043 | Illegal date constant <constant> |
| A date constant is illegal. The correct form for date constants is: YYYY-MM-DD. | |
| Table Error 13045 | Reference privileges are not supported |
| Reference privileges are not supported in this version of SOLID Server. | |
| Table Error 13046 | Illegal user name <user> |
| User name entered is not legal. A legal user name is at least 2 and at most 31 characters in length. A user name may contain characters from A to Z, numbers from 0 to 9 and underscore character '_'. | |
| Table Error 13047 | No privileges for operation |
| You have no privileges for the attempted operation. | |
| Table Error 13048 | No privileges to grant privileges for table <table> |
| You have no privileges to grant privileges for the table. | |
| Table Error 13049 | Column privileges cannot be granted WITH GRANT OPTION |
| Granting column privileges WITH GRANT OPTION is not supported in this version of SOLID Server. | |
| Table Error 13050 | Too long constraint value |
| Maximum constraint length has been exceeded. Maximum constraint length is 255 characters. | |
| Table Error 13051 | Illegal column name <column> |
| You have tried to create a table with an illegal column name. | |
| Table Error 13052 | Illegal comparison operator <operator> for a pseudo column <column> |
| You have tried to use an illegal comparison operator for a pseudo column. Legal comparison operators for pseudo columns are: equality '=' and non-equality '<>'. | |
| Table Error 13053 | Illegal data type for a pseudo column |
| You have tried to use an illegal data type for a pseudo column. Data type of pseudo columns is BINARY. | |
| Table Error 13054 | Illegal pseudo column data, maybe data is not received using pseudo column |
| You have tried to compare pseudo column data with non-pseudo column data. Pseudo column data can only be compared with data received from a pseudo column. | |
| Table Error 13055 | Update not allowed on pseudo column |
| Updates are not allowed on pseudo columns. | |
| Table Error 13056 | Insert not allowed on pseudo column |
| Inserts are not allowed on pseudo columns. | |
| Table Error 13057 | Index name <index> already exists |
| You have tried to create an index, but an index with the same name already exists. Use another name for the index. | |
| Table Error 13058 | Constraint checks were not satisfied on column <column> |
| Column has constraint checks which were not satisfied during an insert or update. | |
| Table Error 13059 | Reserved system name <name> |
| You tried to use a name which is a reserved system name such as PUBLIC and SYS_ADMIN_ROLE. | |
| Table Error 13060 | User name <user> not found |
| You tried to reference a user name which is not created. | |
| Table Error 13061 | Role name <role> not found |
| You tried to reference a role name which is not created. | |
| Table Error 13062 | Admin option is not supported |
| Admin option is not supported in this version of SOLID Server. | |
| Table Error 13063 | Name <name> already exists |
| You tried to use a role or user which already exists. User names and role names must all be different i.e. you can not have a user named HOBBES and a role named HOBBES. | |
| Table Error 13064 | Not a valid user name <user> |
| You tried to create an invalid user name. A valid user name has at least 2 characters and at most 31 characters. | |
| Table Error 13065 | Not a valid role name <role> |
| You tried to create an invalid role name. A valid user name has at least 2 characters and at most 31 characters. | |
| Table Error 13066 | User <user> not found in role <role> |
| You tried to revoke a role from a user and the user did not have that role. | |
| Table Error 13067 | Too short password |
| You have entered a too short password. Password length must be at least 3 characters. | |
| Table Error 13068 | Shutdown is in progress |
| You are unable to complete this operation, because server shutdown is in progress. | |
| Table Error 13070 | Numerical overflow |
| A numerical overflow has occurred. Check the values and types of numerical variables. | |
| Table Error 13071 | Numerical underflow |
| A numerical underflow has occurred. Check the values and types of numerical variables. | |
| Table Error 13072 | Numerical value out of range |
| A numerical value is out of range. Check the values and types of numerical variables. | |
| Table Error 13073 | Math error |
| A mathematical error has occurred. Check the mathematics in the statement and try again. | |
| Table Error 13074 | Illegal password |
| You have tried to enter an illegal password. | |
| Table Error 13075 | Illegal role name <role> |
| You have tried to enter an illegal role name. A legal role name is at least 2 and at most 31 characters in length. A user role may contain characters from A to Z, numbers from 0 to 9 and underscore character '_'. | |
| Table Error 13076 | NOT NULL must not be specified for added column <column> |
| You have tried to add a column to a table using ALTER TABLE statement. NOT NULL constraint is not allowed in ALTER TABLE statement when the table already includes data. | |
| Table Error 13077 | Last column can not be dropped |
| You have tried to drop the final column in a table. This is not allowed; at least one column must remain in the table. | |
| Table Error 13078 | Column already exist on table |
| You have tried to create a column which already exists in a table. | |
| Table Error 13079 | Illegal search constraint |
| Check the search condition. There may be mismatch between data types. | |
| Table Error 13080 | Incompatible types, can not modify column <column> from type <type> to type <type> |
| You have tried to modify column to a data type that is incompatible with the original definition, such as VARCHAR and INTEGER. | |
| Table Error 13081 | Descending keys are not supported for binary columns |
| You can not define descending key for a binary column. | |
| Table Error 13082 | Function <function>: parameter * not supported |
| You can not use parameter star (*) with ODBC Scalar Functions. | |
| Table Error 13083 | Function <function>: Too few parameters |
| The function expects more parameters. Check the function call. | |
| Table Error 13084 | Function <function>: Too many parameters |
| The function expects fewer parameters. Check the function call. | |
| Table Error 13085 | Function <function>: Run-time failure |
| An error was detected during the execution of the function. Check the parameters. | |
| Table Error 13086 | Function <function>: type mismatch in parameter <parameter number> |
| A erroneous type of parameter detected in the given position of the function call. Check the function call. | |
| Table Error 13087 | Function <function>: illegal value in parameter <parameter number> |
| An illegal value for a parameter detected in the given position of the function call. Check the function call. | |
| Table Error 13090 | Foreign key column <column> data type not compatible with referenced column data type |
| References specification error. Check that the column data type are compatible between referencing and referenced tables. | |
| Table Error 13091 | Foreign key does not match to the primary key or unique constraint of the referenced table |
| References specification error. Check that the column data type are compatible between referencing and referenced tables and that the foreign key is unique for the referenced table. | |
| Table Error 13092 | Event name <event> conflicts with an existing entity |
| Choose a unique name for an event. The specified name is already used. | |
| Table Error 13093 | Event <event>does not exist |
| You referenced to a nonexistent event. Check the name of event. | |
| Table Error 13094 | Duplicate column <column> in primary key definition |
| Duplicate columns are not allowed in a table-constraint-definition. Remove duplicate columns from the definition. | |
| Table Error 13095 | Duplicate column <column> in unique constraint definition |
| Duplicate columns are not allowed in a table-constraint-definition. Remove duplicate columns from the definition. | |
| Table Error 13096 | Duplicate column <column> in index definition |
| Duplicate columns are not allowed in CREATE INDEX statement. Remove duplicate columns. | |
| Table Error 13097 | Primary key columns must be NOT NULL |
| Error in a column-constraint-definition. Define primary key columns NOT NULL. For example: CREATE TABLE DEPT (DEPTNO INTEGER NOT NULL, DNAME VARCHAR, PRIMARY KEY(DEPTNO)); | |
| Table Error 13098 | Unique constraint columns must be NOT NULL |
| Error in a column-constraint-definition. Define unique columns NOT NULL. For example: CREATE TABLE DEPT4 (DEPTNO INTEGER NOT NULL, DNAME VARCHAR, UNIQUE(DEPTNO) ); | |
| Table Error 13099 | No REFERENCES privileges to referenced columns in table <table> |
| You do not have privileges to reference to the table. | |
| Table Error 13100 | Illegal table mode combination |
| You have defined illegal combination of locking. Check locking type of tables. | |
| Table Error 13101 | Only execute privileges can be used with procedures |
| Table Error 13102 | Execute privileges can be used only with procedures |
| Table Error 13103 | Illegal grant or revoke operation |
| Table Error 13104 | Sequence name <sequence> conflicts with an existing entity |
| Choose a unique name for a sequence. The specified name is already used. | |
| Table Error 13105 | Sequence <sequence>does not exist |
| You referenced to a nonexistent sequence. Check the name of sequence. | |
| Table Error 13106 | Foreign key reference exists to table <table> |
| Table Error 13107 | Illegal set operation |
| You tried to execute a non-existent set operation. |
| Error code | Description |
| Server Error 14501 | Operation failed |
| This error occurs when a timed command fails. Check the arguments of timed commands. | |
| Server Error 14502 | RPC parameter is invalid |
| A network error has occurred. | |
| Server Error 14503 | Communication error |
| A communication error has occurred. | |
| Server Error 14504 | Duplicate cursor name <cursor> |
| You have tried to declare a cursor with a cursor name which is already in use. Use another name. | |
| Server Error 14505 | Connect failed, illegal user name or password |
| You have entered either a user name or a password that is not valid. | |
| Server Error 14506 | Server is closed, no new connections allowed |
| You have tried to connect to a closed server. Connecting was aborted. | |
| Server Error 14507 | Maximum number of licensed user connections exceeded |
| You have tried to connect to a server which has all licenses currently in use. Connecting was aborted. | |
| Server Error 14508 | The operation has timed out |
| You have launched an operation that has been aborted. | |
| Server Error 14509 | Version mismatch |
| A version mismatch has occurred. The client and server are different versions. Use same versions in the client and the server. | |
| Server Error 14510 | Communication write operation failed |
| A write operation failed. This indicates a network problem. Check your network settings. | |
| Server Error 14511 | Communication read operation failed |
| A read operation failed. This indicates a network problem. Check your network settings. | |
| Server Error 14512 | There are users logged to the server |
| You can not shutdown the server now. There are users connected to the server. | |
| Server Error 14513 | Backup process is active |
| You can not shutdown the server now. The backup process is active | |
| Server Error 14514 | Checkpoint creation is active |
| You can not shutdown the server now. The checkpoint creation is active. | |
| Server Error 14515 | Invalid user id |
| You tried to drop a user, but the user id is not logged in to the server. | |
| Server Error 14516 | Invalid user name |
| You tried to drop a user, but the user name is not logged in to the server. | |
| Server Error 14517 | Someone has updated the at commands at the same time, changes not saved |
| You tried to update timed commands at the same time another user was doing the same. Your changes will not be saved. | |
| Server Error 14518 | Connection to the server is broken, connection lost |
| Possible network error. Reconnect to the server. | |
| Server Error 14519 | The user was thrown out from the server, connection lost |
| Possible network error. | |
| Server Error 14520 | Server is hotstandby slave, no connections are allowed |
| Server Error 14521 | Failed to create a new thread for the client |
| Server Error 14522 | HotStandby sync directory not specified |
| Server Error 14523 | HotStandby switch is already active |
| Server Error 14524 | HotStandby database has a different time stamp |
| Error code | Description |
| Communication Error 21300 | Protocol <protocol> is not supported |
| Protocol is not supported. | |
| Communication Error 21301 | Cannot load the dynamic link library <library> or one of its components |
| The server was unable to load the dynamic link library or a component needed by this library. Check the existence of necessary libraries and components. | |
| Communication Error 21302 | Wrong version of dynamic link library <library> |
| The version of this library is wrong. Update this library to a newer version. | |
| Communication Error 21303 | Network adapter card is missing or needed <protocol> software is not running |
| The network adapter card is missing or not functioning. | |
| Communication Error 21304 | Out of <protocol> resources |
| The network protocol is out of resources. Increase the protocols resources in the operating system. | |
| Communication Error 21305 | An empty or incomplete network name was specified |
| The network name specified is not legal. Check the network name. | |
| Communication Error 21306 | Server <network name> not found, connection failed |
| The server was not found. 1) Check that the server is running. 2) Check that the network name is valid. 3) Check that the server is listening given network name. | |
| Communication Error 21307 | Invalid connect info <network name> |
| The network name given as the connect info is not legal. Check the network name. | |
| Communication Error 21308 | Connection is broken (<protocol> <read/write> operation failed with code <internal code>) |
| The connection using the protocol is broken. Either a read or a write operation has failed with and internal error code <internal code>. | |
| Communication Error 21309 | Failed to accept a new client connection, out of <protocol> resources |
| The server was not able to establish a new client connection. The protocol is out of resources. Increase the protocol's resources in the operating system. | |
| Communication Error 21310 | Failed to accept a new client connection, listening of <network name> interrupted |
| The server was not able to establish a new client connection. The listening has been interrupted. | |
| Communication Error 21311 | Failed to start a selecting thread for <network name> |
| A thread selection has failed for <network name>. | |
| Communication Error 21312 | Listening info <network name> already specified for this server |
| A network name has already been specified for this server. A server can not use a same network name more than once. | |
| Communication Error 21313 | Already listening with the network name <network name> |
| You have tried to add a network name to a server when it is already listening with that network name. A server can not use a same network name more than once. | |
| Communication Error 21314 | Cannot start listening, network name <network name> is used by another process |
| The server can not start listening with the given network name. Another process in this computer is using the same network name. | |
| Communication Error 21315 | Cannot start listening, invalid listening info <network name> |
| The server can not start listening with the given listening info. The given network name is invalid. Check the syntax of the network name. | |
| Communication Error 21316 | Cannot stop the listening of <network name>. There are clients connected |
| You can not stop listening of this network name. There are clients connected to this server using this network name. | |
| Communication Error 21317 | Failed to save the listen information into the configuration file |
| The server failed to save this listening information to the configuration file. Check the file access rights and format of the configuration file. | |
| Communication Error 21318 | Operation failed because of an unusual <protocol> return code <code> |
| Possible network error. Create connection again. | |
| Communication Error 21319 | RPC request contained an illegal version number |
| Either the message was corrupted or there may be a mismatch between server and client versions. | |
| Communication Error 21320 | Called RPC service is not supported in the server |
| There maybe a mismatch between server and client versions. | |
| Communication Error 21321 | Protocol %s is not valid, try using switch '-a' for specifying another adapter id instead of %d |
| This is returned if the NetBIOS lan adapter id given in listen/connect string is not valid. | |
| Communication Error 21322 | The host machine given in connect info '%s' was not found |
| This is returned in clients if the host machine name given in connect info is not valid. | |
| Communication Error 21323 | Protocol <protocol> can not be used for listening in this environment. |
| This message is displayed if the server end communication using specified protocol is not supported. | |
| Communication Error 21324 | The process does not have the privilege to create a mailbox |
| Error code | Description |
| Warning Code 21100 | Illegal value <value> for configuration parameter <parameter>, using default |
| An illegal value was given to the parameter <parameter>. The server will use a default value for this parameter. | |
| Warning Code 21101 | Invalid protocol definition <protocol> in configuration file |
| The protocol is defined illegally in the configuration file. Check the syntax of the definition. |
| Error code | Description |
| Procedure Error 23001 | Undefined symbol <symbol> |
| You have used a symbol that has not been defined in a procedure definition. | |
| Procedure Error 23002 | Undefined cursor <cursor> |
| You have used a cursor that has not been defined in a procedure definition. | |
| Procedure Error 23003 | Illegal SQL operation <operation> |
| Procedure Error 23004 | Syntax error: parse error, line <line number> |
| Check the syntax of your procedure. | |
| Procedure Error 23005 | Procedure <procedure> not found |
| Procedure Error 23006 | Wrong number of parameters for procedure <procedure> |
| Procedure Error 23007 | Procedure name <value> conflicts with an existing entity. |
| Choose a unique name for a procedure. The specified name is already used. | |
| Procedure Error 23009 | Event <event> does not exist, line <line number> |
| Procedure Error 23010 | Incompatible event <event> parameter type, line <line number> |
| Procedure Error 23011 | Wrong number of parameter for event <event>, line <line number> |
| Procedure Error 23012 | Duplicate wait for event <event>, line <line number> |
| Procedure Error 23013 | Undefined sequence <sequence> |
| Procedure Error 23014 | Duplicate sequence name <sequence> |
| Procedure Error 23015 | Sequence <sequence> not found |
| Procedure Error 23016 | Incompatible variable type in call to sequence <sequence>, line <line number> |
| Procedure Error 23017 | Duplicate symbol <symbol> |
| You have duplicate definitions for a symbol. | |
| Procedure Error 23018 | Procedure owner <owner>not found |
| Procedure Error 23019 | Duplicate cursor name '<cursor>' |
| Procedure Error 23501 | Cursor <cursor> is not open |
| Procedure Error 23502 | Illegal number of columns in EXECUTE ... <procedure> in cursor <cursor> |
| Procedure Error 23503 | Previous SQL operation before <operation> failed in cursor <cursor> |
| Procedure Error 23504 | Cursor <cursor> is not executed |
| Procedure Error 23505 | Cursor <cursor> is not a SELECT statement |
| Procedure Error 23506 | End of table in cursor <cursor> |
| Procedure Error 23507 | Illegal type conversion in cursor <cursor> from type <data type> to type <data type> |
| Procedure Error 23508 | Illegal assignment, line <line number> |
| Procedure Error 23509 | In <procedure> line <line number> Stmt <statement> was not in error state in RETURN SQLERROR OF ... |
| Procedure Error 23510 | In <procedure> line <line number> Transaction cannot be set read only, because it has written already |
| Procedure Error 23511 | In <procedure> line <line number> USING part is missing for dynamic parameters for <procedure> |
| Procedure Error 23512 | In <procedure> line <line number> USING list is too short for <procedure>' |
| Error code | Description |
| Sorter Error 24001 | Sort failed due to insufficient configured TmpDir space |
| Sorter Error 24002 | Sort failed due to insufficient physical TmpDir space |
| Sorter Error 24003 | Sort failed due to insufficient sort buffer space |
| Sorter Error 24004 | Sort failed due to too long row (internal failure) |
| Sorter Error 24005 | Sort failed due to I/O error |
By managing the parameters of your SOLID Server, you can modify the environment, performance, and operation of the server.
When SOLID Server is started, it attempts to open the configuration file solid.ini in the current directoryconfiguration file. The configuration values for the server parameters are included in this file. If the file does not exist, SOLID Server will use the default settings for the parameters. Also, if a value for a parameter is not set in the solid.ini file, SOLID Server will use a default value for the parameter. The default values depend on the operating system you are using.
Generally, the default settings offer the best performance and operability, but in some special cases modifying a parameter will improve performance. You can change the parameters either by using the SOLID Remote Control parameter page or by editing the configuration file solid.ini. The configuration file format is similar to the format found in MS Windows and OS/2.
| [General] | Description | Default |
| MaxOpenFiles | the maximum number of files kept concurrently open during SOLID Server sessions | OS depend. |
| BackupDirectory | the directory for backup files | No default |
| BackupCopyLog | if set to yes, backup operation will copy log files to the backup directory | yes |
| BackupDeleteLog | if set to yes, old log files will be deleted after backup operation | yes |
| BackupCopyIniFile | if set to yes, solid.ini file will be copied to the backup directory | yes |
| Checkpoint
Interval | the number of inserts made in the database that causes automatic checkpoint creation | 5000 |
| MergeInterval | the number of index inserts made in the database that causes the merge process to start | Cache size depend. |
| Readonly | if set to yes, database is set to read-only mode | no |
| LongSequential
SearchLimit | the number of sequential fetches after which search is treated as long sequential search | 500 |
| SearchBuffer Limit | the maximum percentage of search buffers from the total buffered memory reserved for open cursors | 50 |
| Transaction HashSize | the hash table size for incomplete transactions | Cache size depend. |
| UseIOThreads | if set to yes, IO threads are used; if set to no, IO threads are not used | no |
| [IndexFile] [BLOBFile_1] | Description | Default |
| FileSpec_[1-N] | the file name followed with maximum size of that database file, for example:
c:\sol1.db 10000 | solid.db |
| BlockSize | the block size of the index file in bytes; use power of 2 kb: minimum 2048, maximum 16384 | 8192 |
| CacheSize | the size of database cache memory for the server in bytes; the minimum 512 kb | OS depend. |
| ExtendIncrement | the number of blocks that is allocated at one time when SOLID Server needs to allocate more space for the database file | 10 |
| ReadAhead | sets the number of prefetched index leafs during long sequential searches | 4 |
| PreFlushPercent | Percentage of page buffer which is kept clean by preflush thread |
| [Logging] | Description | Default |
| LogEnabled | whether logging is enabled or not | yes |
| LogWriteMode | the supported log write methods are:
0: the ping-pong method 1: the group commit method 2: the overwriting method 3: the lazy write method see chapter Database Maintenance for more specific information | 0 |
| BlockSize | the block size of log files | 2048 |
| CommitMaxWait | the maximum wait time for a COMMIT statement in milliseconds when using group commit method for log writing (LogWriteMode = 1) | 200 |
| MinSplitSize | when this file size is reached, logging will be continued to the following log file after a the next checkpoint | 1 MB |
| FileNameTemplate | the path and naming convention used when creating log files; template characters are replaced with sequential numbering; for example:
c:\solid\log\sol#####.log | sol#####.log |
| DigitTemplate Char | the template character that will be replaced in the name template of the log file | # |
| [Com] | Description | Default |
| Listen | the network name for server; the protocol and name that SOLID Server uses when starting listening to the network | OS depend. |
| Connect | the network name for client; the protocol and name that a SOLID Server client uses for server connection; in a Windows environment ODBC Data Source Name overrides the value of this parameter | OS depend. |
| MaxPhysMsgLen | the maximum length of a single physical network message in bytes; longer network messages will be split into smaller messages of this size | OS depend. |
| ReadBufSize | the buffer size in bytes for the data read from the network | OS depend. |
| WriteBufSize | the buffer size in bytes for the data written into the network | OS depend. |
| AllowYield | Windows only; controls whether communication is allowed to yield control to other running tasks or not | no |
| SelectThread | if set to yes a separate selector thread is started for every listening session | OS depend. |
| Trace | if parameter set to yes, trace information on network messages is written to a file specified with the TraceFile parameter | no |
| TraceFile | if parameter Trace is set to yes, trace information on network messages is written to a file specified with this parameter | soltrace.out |
| [Srv] | Description | Default |
| RowsPerMessage | the number of rows returned from the server in one network message | 10 |
| ConnectTimeOut | specifies the continuous idle time in minutes after that an connection is dropped | 480 |
| AbortTimeOut | specifies the time in minutes after that an idle transaction is aborted | 120 |
| Threads | the number of threads used for database access in SOLID Server | OS depend. |
| Echo | if set to yes contents of solmsg.out file are displayed also at the server's command window | no |
| ReadThreadMode | possible values are 0, 1 and 2, the meanings are following
0: network reads are done in database tasks 1: network reads are done in the server thread 2: network reads are done in separate read threads | OS depend. |
| Name | the informal name of the server, equivalent to the -n command line option | |
| AllowConnect | if set to yes only connections from Remote Control are allowed | no |
| MessageLogSize | defines the maximum size of the solmsg.out file in bytes | 100 000 |
| [SQL] | Description | Default |
| Info | set the level of informational messages [0-9] printed from the server; information is written into SOLTRACE.OUT in the server directory (0=no info, 9=all info) | 0 |
| SortArraySize | the size of the array that SQL uses when ordering result set; for optimal performance this should be as big as the biggest retrieved result set that cannot be ordered by key values; for large sotrs use external sorter | OS depend. |
| JoinPathSpan | the depth of the SQL parse tree that SQL optimiser will span through when selecting the correct joining strategy | 3 |
| ProcedureCache | the size of cache memory for parsed procedures in number of procedures | 5 |
| [Sorter] | Description | Default |
| MaxCacheUse
Percent | maximum percentage of cache pages used for sorting; range from 10% to 50% | |
| MaxMemPerSort | maximum memory available in bytes for one sort | |
| MaxFilesTotal | maximum number of files used for sorting | |
| TmpDir_[1-N] | name of the directory that contains temporary files created during sorting | No default |
The following abbreviations used in the following tables:
| Abbreviation | Description |
| DEFLEN | the defined length of the column;
e.g. for CHAR(24) it is, of course, 24 |
| DEFDIG | the defined maximum number of digits; e.g. for NUMERIC(10,3) it is 10 |
| DEFDEC | the defined number of digits to the right of the decimal point |
| MAXLEN | the maximum length of column |
| NA | not applicable |
| Data type | Range/ Size | Precision | Scale | Length | Display size |
| CHAR | no limit | DEFLEN | NA | DEFLEN | DEFLEN |
| VARCHAR | no limit** | DEFLEN | NA | DEFLEN | DEFLEN |
| LONG VARCHAR | no limit | MAXLEN | NA | MAXLEN | MAXLEN |
| ** no explicit limit, default is 254 | |||||
| Data type | Range/ size | Precision | Scale | Length | Display size |
| DECIMAL | 3.6e16 | 16 | DEFDEC | DEFDIG+2 | precision + 2 |
| NUMERIC | 3.6e16 | 16 | DEFDEC | DEFDIG+2 | precision + 2 |
| TINYINT | [-128, 127] | 3 | 0 | 1 | 4 |
| SMALLINT | [-32768, 32767] | 5 | 0 | 2 | 6 |
| INTEGER | [-2^31, 2^31 -1] | 10 | 0 | 4 | 11 |
| REAL | 1.7014117
e38 | 7 | NA | 4 | 13 |
| FLOAT | 8.9884657
e307 | 15 | NA | 8 | 22 |
| DOUBLE PRECISION | 8.9884657
e307 | 15 | NA | 8 | 22 |
| Data type | Range/ size | Precision | Scale | Length | Display size |
| BINARY | no limit | DEFLEN | NA | DEFLEN | DEFLEN x 2 |
| VARBINARY | no limit** | DEFLEN | NA | DEFLEN | DEFLEN x 2 |
| LONG VARBINARY | no limit | MAXLEN | NA | MAXLEN | MAXLEN x 2 |
| ** no explicit limit, default is 254 | |||||
| Data type | Range/ size | Precision | Scale | Length | Display size |
| DATE | no limit | 10 | NA | 6 | 10 |
| Data type | Range/ size | Precision | Scale | Length | Display size |
| TIME | no limit | 8 | NA | 6 | 8 |
| Data type | Range/ size | Precision | Scale | Length | Display size |
| TIMESTAMP | no limit | 16 | 9 | 16 | 19/29* |
| * size is 29 with a decimal fraction part | |||||
| Data type | Value |
| DOUBLE | 2.2250738585072014e-308 |
| REAL | 1.175494351e-38 |
The range of a numeric column refers to the minimum and maximum values the column can store. The size of non-numeric columns refers to the maximum length of data that can be stored in the column of that data type.
The precision of a numeric column refers to the maximum number of digits used by the data type of the column. The precision of a non-numeric column refers to the defined length of the column.
The scale of a numeric column refers to the maximum number of digits to the right of the decimal point. Note that for the approximate floating point number columns, the scale is undefined, since the number of digits to the right of the decimal point is not fixed.
The length of a column is the maximum number of bytes returned to the application when data is transferred to its default C type. For character data, the length does not include the null termination byte. Note that the length of a column may differ from the number of bytes needed to store the data on the data source.
The display size of a column is the maximum number of bytes needed to display data in character form.
The SOLID Server 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.
This appendix presents a simplified description of
the most common SQL statements including some examples. For the
full description of the SOLID SQL API, refer to the SOLID
SQL API Help file. The same information is included
in the SOLID Server Programmer's Guide and the
SOLID Server Programmer's Reference.
The description syntax uses these conversions:
ADMIN COMMAND 'command-name' command-name ::= EXIT | HELP | SHUTDOWN | OPEN | CLOSE | THROWOUT | USERLIST | MAKECP | BACKUP | BACKUPLIST | STATUS | REPORT | MESSAGES | MONITOR | VERSION | ERRORCODE | HOTSTANDBY
This SQL extension executes administrator commands. Syntax for the extension is
ADMIN COMMAND 'command-name'
where command-name is a teletype solcon command string. The result set contains two columns: RC INTEGER and TEXT VARCHAR(254). Integer column RC is a command return code (0 if success), varchar column TEXT is the command reply. The TEXT field contains same lines that a displayed on teletype solcon screen, one line per one result row.
ADMIN COMMAND 'USERLIST';
ALTER TABLE base-table-name
{ADD [COLUMN] column-identifier data-type |
DROP [COLUMN] column-identifier |
RENAME [COLUMN]
column-identifier column-identifier |
MODIFY [COLUMN]
column-identifier data-type} |
SET {OPTIMISTIC | PESSIMISTIC}
Usage
The structure of a table may be modified through the ALTER TABLE statement. Within the context of this statement, columns may be added, modified, or removed.
Individual tables can be set to optimistic or pessimistic with command ALTER TABLE base-table-name SET {OPTIMISTIC | PESSIMISTIC}. By default, all tables are optimistic. A database wide default can be set in the configuration file in General section with parameter Pessimistic=yes.
If transaction early validate is used, update and delete operations use locks for the early validation. Read operations do not lock, but update and delete operations lock the updated or deleted row.
Example
ALTER TABLE TEST ADD X INTEGER; ALTER TABLE TEST RENAME COLUMN X Y; ALTER TABLE TEST MODIFY COLUMN X SMALLINT; ALTER TABLE TEST DROP COLUMN X;
ALTER USER user-name IDENTIFIED BY password
Usage
The password of a user may be modified through the ALTER USER statement.
Example
ALTER USER MANAGER IDENTIFIED BY O2CPTG;
CALL procedure-name [parameter ...]
Usage
Stored procedures are called with statement CALL.
Example
CALL proctest;
COMMIT WORK
Usage
The changes made in the database are made permanent by COMMIT statement. It terminates the transaction.
Example
COMMIT WORK;
CREATE EVENT event-name
[(parameter-definition
[, parameter-definition ...])]
Usage
Event alerts are used to signal an event in the database. Events are simple objects with a name. The use of event alerts removes resource consuming database polling from applications.
An event object is created with SQL statement
CREATE EVENT event-name [parameter-list]
The name can be any user specified alphanumeric string. The parameter list specifies parameter names and parameter types. The parameter types are normal SQL types.
Events are dropped with SQL statement
DROP EVENT event-name
Events are generated and waited inside stored procedures. Special stored procedure statement is used for event generation and waiting.
The event is generated with stored procedure statement
POST EVENT event-name [parameters]
Event parameters must be local variables or parameters in the stored procedure where the event is posted. All clients that are waiting for the posted event will receive the event.
Event is waited using WAIT EVENT construct in a stored procedure:
wait-event-statement ::=
WAIT EVENT
[event-specification ...]
END WAIT
event-specification ::=
WHEN event-name (parameters) BEGIN
statements
END EVENT
Example of a procedure that waits events:
create procedure "event-wait(i1
integer)
returns (result varchar)
begin
declare i integer;
declare c char(4);
i := 0;
wait event
when test1 begin
result := 'event1';
return;
end event
when test2(i) begin
end event
when test3(i, c) begin
end event
end wait
if i <> 0 then
result := 'if';
post event test1;
else
result := 'else';
post event test2(i);
post event test3(i, c);
end if
end";
The creator of an event or the database administrator can grant and revoke access rights to an event to users and roles. Select access right gives wait access to an event. Insert access right gives raise access to an event.
Example
CREATE EVENT ALERT1(I INTEGER, C CHAR(4));
CREATE [UNIQUE] INDEX index-name
ON base-table-name
(column-identifier [ASC | DESC]
[, column-identifier [ASC | DESC]] ...)
Usage
Creates an index for a table based on the given columns. Keyword UNIQUE specifies that columns being indexed must contain unique values. Keywords ASC and DESC specify whether the given columns should be indexed in ascending or descending order. If not specified ascending order is used.
Example
CREATE UNIQUE INDEX UX_TEST ON TEST (I); CREATE INDEX X_TEST ON TEST (I, J);
CREATE PROCEDURE procedure-name
[(parameter-definition
[, parameter-definition ...])]
[RETURNS (parameter-definition
[, parameter-definition ...])]
BEGIN procedure-body END;
parameter-definition ::= parameter-name data-type
procedure-body ::= [declare-statement; ...]
procedure-statement; [procedure-statement; ...]
declare-statement ::= DECLARE variable-name
data-type
procedure-statement ::= prepare-statement |
exec-statement | fetch-statement |
control-statement
prepare-statement ::= EXEC SQL PREPARE
cursor-name sql-statement
execute-statement ::=
EXEC SQL EXECUTE
cursor-name
[USING (variable [, variable ...])]
[INTO (variable [, variable ...])] |
EXEC SQL [COMMIT | ROLLBACK] WORK |
EXEC SQL SET TRANSACTION (READ ONLY | READ WRITE)
fetch-statement ::= EXEC SQL FETCH cursor-name
control-statement ::=
SET variable-name = value |
variable-name := values |
WHILE expression
LOOP procedure-statement... END LOOP |
LEAVE |
IF expression THEN procedure-statement ...
[ ELSEIF procedure-statement ... THEN] ...
ELSE procedure-statement ... END IF |
RETURN | RETURN SQLERROR OF cursor-name
Usage
Stored procedures are simple programs, or procedures, that are executed in the server. The user can create a procedure that contains several SQL statements or a whole transaction, and execute it with a single call statement. Usage of stored procedures reduces network traffic and allows more strict control to access rights and database operations.
Procedures are created with statement
CREATE PROCEDURE name body
and dropped with statement
DROP PROCEDURE name
Procedures are called with statement
CALL name [parameter ...]
Procedures can take several input parameters and return a single row as a result. Result row is built from specified output parameters. Procedures are thus used in ODBC in the same way as SQL SELECT statement.
Procedures are owned by the creator of the procedure. Specified access rights can be granted to other users. The procedure has the creator's access rights to database objects when it is run.
The stored procedure syntax is a proprietary syntax modeled from SQL3 specifications and dynamic SQL. Procedure contains control statements and SQL statement.
The following control statements are available in the procedures:
| Control statement | Description |
| declare variable-name data-type | Declares a variable with given SQL type (e.g. CHAR(10)). |
| begin statement-list end | Marks beginning and end of the procedure body. |
| set variable = expression | Assigns a value to a variable. The value can be either a literal value (e.g. 10 or 'text') or another variable. Parameters are considered as normal variables. |
| variable := expression | Alternate syntax for assigning values to variables. |
| while
expr loop statement-list end loop | Loops while expression is true. |
| leave | Leaves the innermost while loop and continues from the next statement after end loop. |
| if
expr then statement-list1 else statement-list2 end if | Executes statements-list1 if expression expr is true, otherwise executes statement-list2. |
| if expr1 then statement-list1 elseif expr2 then statement-list2 end if | If expr1 is true, executes statement-list1. If expr2 is true, executes statement-list2. The statement can optionally contain multiple elseif statements and also an else statement. |
All SQL DML and DDL statements can be used in procedures. Thus, the procedure can e.g. create tables or commit a transaction. Each SQL statement in the procedure is atomic.
The SQL statements are first prepared with statement
EXEC SQL PREPARE cursor SQL-statement
The cursor specification is a cursor name that must be given. It can be any unique cursor name inside the transaction. Note that if the procedure is not a complete transaction, other open cursors outside the procedure may have conflicting cursor names.
The SQL statement is executed with statement
EXEC SQL EXECUTE cursor [opt-using ] [opt-into ]
The optional opt-using specification has a syntax
USING (variable-list)
where variable-list contains a list of procedure variables or parameters separated by a comma. These variables are input parameters for the SQL statement. The SQL input parameters are marked with the standard question mark syntax in prepare statement. If the SQL statement has no input parameters, the USING specification is ignored.
The optional opt-into specification has a syntax
INTO (variable-list)
where variable-list contains those variables where the column values of SQL SELECT statement are stored. The INTO specification is effective only for SQL SELECT statements.
Rows are fetched with statement
EXEC SQL FETCH cursor
If the fetch completed successfully, the column values are stored into user bound variable.
The result of each EXEC SQL statement executed inside a procedure body is stored into variable SQLSUCCESS. This variable is automatically generated for every procedure. If the previous SQL statement was successful, a value one is stored into SQLSUCCESS. After a failed SQL statement, a value zero is stored into SQLSUCCESS.
Example
create procedure "test2(tableid integer)
returns (cnt integer)
begin
exec sql prepare c1 select count(*) from
sys_tables where id > ?;
exec sql execute c1 using (tableid) into
(cnt);
exec sql fetch c1;
end";
CREATE ROLE role-name
Usage
Creates a new user role.
Example
CREATE ROLE GUEST_USERS;
CREATE [DENSE] SEQUENCE sequence-name
Usage
Sequence object is a an object that is used to get sequence numbers.
Using dense sequence guarantees that there are no holes in the sequence numbers. The sequence number allocation is bound to the current transaction. If the transaction rolls back, also the sequence number allocations are rolled back. The drawback of dense sequences is that the sequence is locked out from other transactions until the current transaction ends.
Using sparse sequence guarantees uniqueness of the returned values, but they are not bound to the current transaction. If a transaction allocates a sparse sequence number and later rolls back, the sequence number is simply lost.
The advantage of using sequence object instead of separate table is that the sequence object is specifically fine tuned for fast execution and requires less overhead than normal update statements.
Sequences are accessed from stored procedures. The current sequence value can be retrieved using the following stored procedure statement:
EXEC SEQUENCE sequence-name.CURRENT INTO variable
The new sequence value can be retrieved using the following stored procedure statement:
EXEC SEQUENCE sequence-name.NEXT INTO variable
Select access rights are required to retrieve the current sequence value. Update access rights are required to allocate new sequence values. These access rights are granted and revoked in the same way as table access rights.
Example
CREATE DENSE SEQUENCE SEQ1;
CREATE TABLE base-table-name
(column-element [, column-element] ...)
column-element ::= column-definition |
table-constraint-definition
column-definition ::= column-identifier
data-type
[column-constraint-definition
[column-constraint-definition] ...]
column-constraint-definition ::=
NOT NULL | NOT NULL UNIQUE |
NOT NULL PRIMARY KEY | CHECK (check-condition)
table-constraint-definition ::=
UNIQUE (column-identifier
[, column-identifier] ...) |
PRIMARY KEY (column-identifier
[, column-identifier] ...) |
CHECK (check-condition) |
FOREIGN KEY (column-identifier
[, column-identifier] ...)
REFERENCES table-name
(column-identifier [, column-identifier] ...)
Usage
Tables are created through the CREATE TABLE statement. The CREATE TABLE statement requires a list of the columns created, the data types, and, if applicable, sizes of values within each column, in addition to other related alternatives (such as whether or not null values are permitted).
Example
CREATE TABLE DEPT (DEPTNO INTEGER NOT NULL, DNAME VARCHAR, PRIMARY KEY(DEPTNO)); CREATE TABLE DEPT2 (DEPTNO INTEGER NOT NULL PRIMARY KEY, DNAME VARCHAR); CREATE TABLE DEPT3 (DEPTNO INTEGER NOT NULL UNIQUE, DNAME VARCHAR); CREATE TABLE DEPT4 (DEPTNO INTEGER NOT NULL, DNAME VARCHAR, UNIQUE(DEPTNO)); CREATE TABLE EMP (DEPTNO INTEGER, ENAME VARCHAR, FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)); CREATE TABLE EMP2 (DEPTNO INTEGER, ENAME VARCHAR, CHECK (ENAME IS NOT NULL), FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO));
CREATE USER user-name IDENTIFIED BY password
Usage
Creates a new user with a given password.
Example
CREATE USER HOBBES IDENTIFIED BY CALVIN;
CREATE VIEW viewed-table-name
[(column-identifier
[, column-identifier]... )]
AS query-specification
Usage
A view can be viewed as a virtual table; that is, a table that does not physically exist, but rather is formed by a query specification against one or more tables.
Example
CREATE VIEW TEST_VIEW (VIEW_I, VIEW_C, VIEW_ID) AS SELECT I, C, ID FROM TEST;
DELETE FROM table-name [WHERE search-condition]
Usage
Depending on your search condition the specified row(s) will be
deleted from a given table.
Example
DELETE FROM TEST WHERE ID = 5; DELETE FROM TEST;
DELETE FROM table-name WHERE CURRENT OF cursor-name
Usage
The positioned DELETE statement deletes the current row of the cursor.
Example
DELETE FROM TEST WHERE CURRENT OF MY_CURSOR;
DROP EVENT event-name
Usage
The DROP EVENT statement removes the specified event from the database.
Example
DROP EVENT EVENT-TEST;
DROP INDEX index-name
Usage
The DROP INDEX statement removes the specified index from the database.
Example
DROP INDEX UX_TEST;
DROP PROCEDURE procedure-name
Usage
The DROP PROCEDURE statement removes the specified procedure from the database.
Example
DROP PROCEDURE PROCTEST;
DROP ROLE role-name
Usage
The DROP ROLE statement removes the specified role from the database.
Example
DROP ROLE GUEST_USERS;
DROP SEQUENCE sequence-name
Usage
The DROP SEQUENCE statement removes the specified sequence from the database.
Example
DROP SEQUENCE SEQ1;
DROP TABLE base-table-name
Usage
The DROP TABLE statement removes the specified table from the database.
Example
DROP TABLE TEST;
DROP USER user-name
Usage
The DROP USER statement removes the specified user from the database.
Example
DROP USER HOBBES;
DROP VIEW viewed-table-name
Usage
The DROP VIEW statement removes the specified view from the database.
Example
DROP VIEW TEST_VIEW;
EXPLAIN PLAN FOR sql-statement
Usage
The EXPLAIN PLAN FOR statement shows the selected search plan for the specified SQL statement.
Example
EXPLAIN PLAN FOR select * from tables;
GRANT {ALL | grant-privilege
[, grant-privilege]...}
ON table-name
TO {PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
[WITH GRANT OPTION]
GRANT role-name TO user-name
grant-privilege ::= DELETE | INSERT | SELECT |
UPDATE [( column-identifier
[, column-identifier]... )] |
REFERENCES [( column-identifier
[, column-identifier]... )]
GRANT EXECUTE ON procedure-name
TO {PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
GRANT {SELECT | INSERT} ON event-name
TO {PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
GRANT {SELECT | UPDATE} ON sequence-name
TO {PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
Usage
The GRANT statement is
If you do use the optional WITH GRANT OPTION, you give permission for the user(s) to whom you are granting the privilege to pass it on to other users.
Example
GRANT GUEST_USERS TO CALVIN; GRANT INSERT, DELETE ON TEST TO GUEST_USERS;
INSERT INTO table-name [(column-identifier
[, column-identifier]...)]
VALUES (insert-value[, insert-value]... )
Usage
There are several variations of the INSERT statement. In the simplest instance, a value is provided for each column of the new row in the order specified at the time the table was defined (or altered). In the preferable form of the INSERT statement the columns are specified as part of the statement and they needn't to be in any specific order as long as the orders of the column and value lists match with one another.
Example
INSERT INTO TEST (C, ID) VALUES (0.22, 5); INSERT INTO TEST VALUES (0.35, 9);
INSERT INTO table-name [( column-identifier [, column-identifier]... )] query-specification
Usage
The query specification creates a virtual table. Using the INSERT statement the rows of created virtual table are inserted into the specified table (the degree and data types of the virtual table and inserted columns must match).
Example
INSERT INTO TEST (C, ID) SELECT A, B FROM INPUT_TO_TEST;
REVOKE {role-name [, role-name]... }
FROM {PUBLIC | user-name [, user-name]... }
Usage
The REVOKE statement is used to take a role away from users.
Example
REVOKE GUEST_USERS FROM HOBBES;
REVOKE
{revoke-privilege [, revoke-privilege]... }
ON table-name
FROM {PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
revoke-privilege ::= DELETE | INSERT |
SELECT | UPDATE | REFERENCES
REVOKE EXECUTE ON procedure-name
FROM {PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
REVOKE {SELECT | INSERT} ON event-name FROM
{PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
REVOKE {SELECT | INSERT} ON sequence-name
FROM {PUBLIC | user-name [, user-name]... |
role-name [, role-name]... }
Usage
The REVOKE statement is used to take privileges away from users and roles.
Example
REVOKE INSERT ON TEST FROM GUEST_USERS;
ROLLBACK WORK
Usage
The changes made in the database are discarded by ROLLBACK statement. It terminates the transaction.
Example
ROLLBACK WORK;
SELECT [ALL | DISTINCT] select-list
FROM table-reference-list
[WHERE search-condition]
[GROUP BY column-name [, column-name]... ]
[HAVING search-condition]
[UNION [ALL] select-statement]...
[ORDER BY {unsigned integer | column-name}
[ASC|DESC]]
Usage
The SELECT statement is used to retrieve information.
Example
SELECT ID FROM TEST; SELECT DISTINCT ID, C FROM TEST WHERE ID = 5; SELECT DISTINCT ID FROM TEST ORDER BY ID ASC; SELECT NAME, ADDRESS FROM CUSTOMERS UNION SELECT NAME, DEP FROM PERSONNEL;
SET SQL INFO {ON | OFF} [FILE {file-name |
"file-name" | 'file-name'}]
[LEVEL info-level]
SET SQL SORTARRAYSIZE {array-size | DEFAULT}
SET SQL JOINPATHSPAN {path-span | DEFAULT}
SET SQL CONVERTORSTOUNIONS
{YES [COUNT value] | NO | DEFAULT}
SET LOCK TIMEOUT timeout-in-seconds
SET TRANSACTION READ ONLY
SET TRANSACTION READ WRITE
SET TRANSACTION CHECK WRITESET
SET TRANSACTION CHECK READSET
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Usage
All the settings a re per user settings unlike the settings in the solid.ini file.
In SQL INFO the default file is a global soltrace.out shared by all users. If the file name is given, all future INFO ON settings will use that file unless a new file is set. It is recommended that the file name is given in single quotes, because otherwise the file name is converted to uppercase. The info output is appended to the file and the file is never truncated, so after the info file is not needed anymore, the user must manually delete the file. If the file open fails, the info output is silently discarded.
The default SQL INFO LEVEL is 4. A good way to generate useful info output is to set info on with a new file name and then execute the SQL statement using EXPLAIN PLAN FOR syntax. This method gives all necessary estimator information but does not generate output from the fetches which may generate a huge output file.
The sort array is used for in memory sorts in the SQL interpreter. The minimum value for SORTARRAYSIZE is 100. If a smaller value is given, minimum value 100 will be used. If large sorts are needed, it is recommended that the external sorter facility is used (in Sorter section in solid.ini) instead on using very large SORTARRAYSIZE.
The COUNT parameter in SQL CONVERTORSTOUNIONS tells how many ors are converted to unions. The default is 10 which should be enough in most cases.
The SET TRANSACTION settings are borrowed from ANSI SQL. It sets the transaction isolation level.
Example
SET SQL INFO ON FILE 'sqlinfo.txt' LEVEL 5
UPDATE table-name
SET column-identifier = {expression | NULL}
[, column-identifier = {expression |
NULL}]...
WHERE CURRENT OF cursor-name
Usage
The positioned UPDATE
statement updates the current row of the cursor. The name of the
cursor is defined using ODBC API function named SQLSetCursorName.
Example
UPDATE TEST SET C = 0.33 WHERE CURRENT OF MYCURSOR
UPDATE table-name
SET column-identifier = {expression | NULL}
[, column-identifier = {expression |
NULL}]...
[WHERE search-condition]
Usage
The UPDATE statement is used to modify the values of one or more columns in one or more rows, according the search conditions.
Example
UPDATE TEST SET C = 0.44 WHERE ID = 5
| Table-reference | |
| table-reference-list | ::= table-reference |
| table-reference | ::= table-name [ {, table-name} ... ] | outer-join |
| outer-join | ::= table-name LEFT [OUTER] JOIN
{table-name | outer-join} ON search-condition |
| Query-specification | |
| query-specification | ::= SELECT [DISTINCT | ALL] select-list table-expression |
| select-list | ::= * | select-sublist [ {, select-sublist} ... ] |
| select-sublist | ::= derived-column | [table-name | table-identifier ].* |
| derived-column | ::= expression [ [AS] column-alias] ] |
| table-expression | ::= FROM table-reference-list
[WHERE search-condition] [GROUP BY column-name [, column-name] ...] [HAVING search-condition] |
| Search-condition | |
| search-condition | ::= search-item | search-item { AND | OR } search-item |
| search-item | ::= [NOT] { search-test | (search-condition) } |
| search-test | ::= comparison-test | between-test | like-test | null-test | set-test | quantified-test | existence-test |
| comparison-test | ::= expression { = | <> | < | <= | > | >= } { expression | subquery } |
| between-test | ::= column-identifier [NOT] BETWEEN expression AND expression |
| like-test | ::= column-identifier [NOT] LIKE value [ESCAPE value] |
| null-test | ::= column-identifier IS [NOT] NULL |
| set-test | ::= expression [NOT] IN ( { value
[,value]... | subquery } ) |
| quantified-test | ::= expression { = | <> | < | <= | > | >= } [ALL | ANY | SOME] subquery |
| existence-test | ::= EXISTS subquery |
| subquery | ::= (query-specification) |
| Check-condition | |
| check-condition | ::= check-item | check-item { AND | OR } check-item |
| check-item | ::= [NOT] { check-test | (check-condition) } |
| check-test | ::= comparison-test | between-test | like-test | null-test | list-test |
| comparison-test | ::= expression { = | <> | < | <= | > | >= } { expression | subquery } |
| between-test | ::= column-identifier [NOT] BETWEEN expression AND expression |
| like-test | ::= column-identifier [NOT] LIKE value [ESCAPE value] |
| null-test | ::= column-identifier IS [NOT] NULL |
| list-test | ::= expression [NOT] IN ( { value
[,value]...} ) |
| Expression | |
| expression | ::= expression-item | expression-item { + | - | * | / } expression-item |
| expression-item | ::= [ + | - ] { value | column-identifier | function | ( expression ) } |
| value | ::= literal | USER | variable |
| function | ::= COUNT(*) | distinct-function | all-function |
null-function | scalar-function |
| distinct-function | ::= { AVG | MAX | MIN | SUM | COUNT } ( DISTINCT column-identifier ) |
| all-function | ::= { AVG | MAX | MIN | SUM | COUNT } ( [ ALL ] expression ) |
| null-function | ::= { NULLVAL_CHAR( ) | NULLVAL_INT( ) } |
| scalar-function | ::= string-function | numeric-function |
timedate-function | system-function | datatypeconversion-function |
| string-function | ::= ASCII(str-exp) | CHAR(ascii-code) | CONCAT(str-exp1, str-exp2) | {FN INSERT(str-exp1, start, length, str-exp2)} | LCASE(str-exp) | {FN LEFT(str-exp)} | LENGTH(str-exp) | LOCATE(str-exp1, strexp2 [, start]) | LTRIM(str-exp) | REPEAT(str-exp, count) | RIGHT(str-exp, count) | RTRIM(str-exp) | SPACE(count) | SUBSTRING(str-exp, start, length) | UCASE(str-exp) |
| numeric-function | ::= ABS(num-exp) | ACOS(float-exp) | ASIN(float-exp) | ATAN(float-exp) | ATAN2(float-exp1, float-exp2) | CEILING(num-exp) | COS(float-exp) | COT(float-exp) | DEGREES(num-exp) | EXP(float-exp) | FLOOR(num-exp) | LOG(float-exp) | LOG10(float-exp) | MOD(int-exp1, int-exp2) | PI() | POWER(num-exp, int-exp) | RADIANS(num-exp) | ROUND(num-exp, int-exp) | SIGN(num-exp) | SQRT(float-exp) | TAN(float-exp) | TRUNCATE(num-exp, int-exp) |
| datetime-function | ::= CURDATE() | CURTIME() | DAYNAME(date-exp) | DAYOFMONTH(date-exp) | DAYOFWEEK(date-exp) | DAYOFYEAR(date-exp) | HOUR(time-exp) | MINUTE(time-exp) | MONTH(date-exp) | MONTHNAME(date-exp) | NOW() | QUARTER(date-exp) | SECOND(time-exp) | TIMESTAMPADD(interval, int-exp, timestamp-exp) | TIMESTAMPDIFF(interval, timestamp-exp1, timestamp-exp2) | WEEK(date_exp) |YEAR(date_exp) |
| system-function | ::= IFNULL(exp, value) | {FN USER()} |
| datatypeconversion-function | ::= CONVERT_CHAR(value-exp) | CONVERT_DATE(value-exp) | CONVERT_DECIMAL(value-exp) | CONVERT_DOUBLE(value-exp) | CONVERT_FLOAT(value-exp) | CONVERT_INTEGER(value_exp) | CONVERT_LONGVARCHAR(value-exp) | CONVERT_NUMERIC(value-exp) | CONVERT_REAL(value-exp) | CONVERT_SMALLINT(valuie-exp) | CONVERT_TIME(value-exp) | CONVERT_TIMESTAMP(value-exp) | CONVERT_TINYINT(value-exp) | CONVERT_VARCHAR(value-exp) |
| Data-type | |
| data-type | ::= {BINARY | CHAR [ length ] | DATE | DECIMAL [ ( precision [ , scale ] ) ] | DOUBLE PRECISION | FLOAT [ ( precision ) ] | INTEGER | LONG VARBINARY | LONG VARCHAR | NUMERIC [ ( precision [ , scale ] ) ] | REAL | SMALLINT | TIME [ ( time precision ) ] | TIMESTAMP [ ( timestamp precision ) ] | TINYINT | VARBINARY | VARCHAR [ ( length ) ] } |
| Date/time literal | |
| date-literal | ´YYYY-MM-DD´ |
| time-literal | ´HH:MM:SS´ |
| timestamp-literal | ´YYYY-MM-DD HH:MM:SS´ |
SOLID Server supports views specified in the X/Open SQL Standard.
The COLUMNS system view identifies the columns which are accessible to the current user.
| Column name | Data type | Description |
| TABLE_CATALOG | VARCHAR | reserved for future use |
| TABLE_SCHEMA | VARCHAR | the name of the schema containing TABLE_NAME |
| TABLE_NAME | VARCHAR | the name of the table or view |
| COLUMN_NAME | VARCHAR | the name of the column of the specified table or view |
| DATA_TYPE | VARCHAR | the data type of the column |
| SQL_DATA_TYPE_NUM | SMALLINT | ODBC compliant data type number |
| CHAR_MAX_LENGTH | INTEGER | maximum length for a character data type column; for others NULL |
| NUMERIC_PRECISION | INTEGER | the number of digits of mantissa precision of the column, if DATA_TYPE is approximate numeric data type, NUMERIC_PREC_RADIX indicates the units of measurement; for other numeric types contains the total number of decimal digits allowed in the column; for character data types NULL |
| NUMERIC_PREC_RADIX | SMALLINT | the radix of numeric precision if DATA_TYPE is one of the approximate numeric data types; otherwise NULL |
| NUMERIC_SCALE | SMALLINT | total number of significant digits to the right of the decimal point; for INTEGER and SMALLINT 0; for others NULL |
| NULLABLE | CHAR | if column is known to be not nullable 'NO'; otherwise 'YES' |
| NULLABLE_ODBC | SMALLINT | ODBC, if column is known to be not nullable '0'; otherwise '1' |
| REMARKS | LONG VARCHAR | reserved for future use |
The SERVER_INFO system view provides attributes of the current database system or server.
| Column name | Data type | Description |
| SERVER_ATTRIBUTE | VARCHAR | identifies an attribute of the server |
| ATTRIBUTE_VALUE | VARCHAR | the value of the attribute |
The TABLES system view identifies the tables accessible to the current user.
| Column name | Data type | Description |
| TABLE_CATALOG | VARCHAR | reserved for future use |
| TABLE_SCHEMA | VARCHAR | the name of the schema containing TABLE_NAME |
| TABLE_NAME | VARCHAR | the name of the table or view |
| TABLE_TYPE | VARCHAR | the type of the table |
| REMARKS | LONG VARCHAR | reserved for future use |
The SQL_LANGUAGES system table lists the SQL standards and SQL dialects which supported.
| Column name | Data type | Description |
| SOURCE | VARCHAR | the organization that defined this specific SQL version |
| SOURCE_YEAR | VARCHAR | the year the relevant standard was approved |
| CONFORMANCE | VARCHAR | the conformance level at which conformance to the relevant standard |
| INTEGRITY | VARCHAR | indicates whether the Integrity Enhancement Feature is supported |
| IMPLEMENTATION | VARCHAR | identifies uniquely the vendor's SQL lanquage; NULL if SOURCE is 'ISO' |
| BINDING_STYLE | VARCHAR | the binding style 'DIRECT', *EMBED' or 'MODULE' |
| PROGRAMMING_LANG | VARCHAR | the host lanquage used |
| Column name | Data type | Description |
| REL_ID | INTEGER | table id |
| UR_ID | INTEGER | user or role id |
| ATTR_ID | INTEGER | column id |
| PRIV | INTEGER | privilege info |
| GRANT_ID | INTEGER | grantor id |
| GRANT_TIM | TIMESTAMP | grant time |
| Column name | Data type | Description |
| REL_ID | INTEGER | the relation id as in SYS_TABLES |
| CARDIN | INTEGER | the number of rows in the table |
| SIZE | INTEGER | the size of the data in the table |
| LAST_UPD | TIMESTAMP | the timestamp of the last update in the table |
| Column name | Data type | Description |
| ID | INTEGER | unique column identifier |
| REL_ID | INTEGER | the relation id as in SYS_TABLES |
| COLUMN_NAME | VARCHAR | the name of the column |
| COLUMN_NUMBER | INTEGER | the number of the column in the table (in creation order) |
| DATA_TYPE | VARCHAR | the data type of the column |
| SQL_DATA_TYPE_NUM | SMALLINT | ODBC compliant data type number |
| DATA_TYPE_NUMBER | INTEGER | internal data type number |
| CHAR_MAX_LENGTH | INTEGER | maximum length for a CHAR field |
| NUMERIC_PRECISION | INTEGER | numeric precision |
| NUMERIC_PREC_RADIX | SMALLINT | numeric precision radix |
| NUMERIC_SCALE | SMALLINT | numeric scale |
| NULLABLE | CHAR | are NULL values allowed (Yes, No) |
| NULLABLE_ODBC | SMALLINT | ODBC, are NULL values allowed (1,0) |
| FORMAT | VARCHAR | reserved for future use |
| DEFAULT_VAL | VARBINARY | reserved for future use |
| ATTR_TYPE | INTEGER | user defined (0) or internal (>0) |
| REMARKS | LONG VARCHAR | reserved for future use |
| Column name | Data type | Description |
| ID | INTEGER | unique event identifier |
| EVENT_NAME | VARCHAR | the name of the event |
| EVENT_PARAMCOUNT | INTEGER | number of parameters |
| EVENT_PARAMTYPES | LONG VARBINARY | types of parameters |
| EVENT_TEXT | VARCHAR | the body of the event |
| EVENT_SCHEMA | VARCHAR | the owner of the event |
| CREATIME | TIMESTAMP | creation time |
| TYPE | INTEGER | reserved for future use |
| Column name | Data type | Description |
| ID | INTEGER | foreign key identifier |
| KEYP_NO | INTEGER | keypart number |
| ATTR_NO | INTEGER | column number |
| ATTR_ID | INTEGER | column identifier |
| ATTR_TYPE | INTEGER | column type |
| CONST_VALUE | VARBINARY | possible internal constant value; otherwise NULL |
| Column name | Data type | Description |
| ID | INTEGER | foreign key identifier |
| REF_REL_ID | INTEGER | referenced table identifier |
| CREATE_REL_ID | INTEGER | creator table identifier |
| REF_KEY_ID | INTEGER | referenced key identifier |
| REF_TYPE | INTEGER | reference type |
| KEY_SCHEMA | VARCHAR | creator name |
| KEY_NREF | INTEGER | number of referenced key parts |
| Column name | Data type | Description |
| PROPERTY | VARCHAR | the name of the property |
| VALUE_STR | VARCHAR | value as a string |
| VALUE_INT | INTEGER | value as an integer |
| Column name | Data type | Description |
| ID | INTEGER | unique key identifier |
| REL_ID | INTEGER | the relation id as in SYS_TABLES |
| KEYP_NO | INTEGER | keypart identifier |
| ATTR_ID | INTEGER | column identifier |
| ATTR_NO | INTEGER | the number of the column in the table (in creation order) |
| ATTR_TYPE | INTEGER | the type of the column |
| CONST_VALUE | VARBINARY | constant value or NULL |
| ASCENDING | CHAR | is the key ascending (Yes) or descending (No) |
| Column name | Data type | Description |
| ID | INTEGER | unique key identifier |
| REL_ID | INTEGER | the relation id as in SYS_TABLES |
| KEY_NAME | VARCHAR | the name of the key |
| KEY_UNIQUE | CHAR | is the key unique (Yes, No) |
| KEY_NONUNIQUE_ODBC | SMALLINT | ODBC, is the key NOT unique (1, 0) |
| KEY_CLUSTERING | CHAR | is the key a clustering key (Yes, No) |
| KEY_PRIMARY | CHAR | is the key a primary key (Yes, No) |
| KEY_PREJOINED | CHAR | reserved for future use |
| KEY_SCHEMA | VARCHAR | the owner of the key |
| KEY_NREF | INTEGER | internal system specific information |
| Column name | Data type | Description |
| ID | INTEGER | unique procedure identifier |
| PROCEDURE_NAME | VARCHAR | procedure name |
| PROCEDURE_TEXT | LONG VARCHAR | procedure body |
| PROCEDURE_BIN | LONG VARBINARY | compiled form of the procedure |
| PROCEDURE_SCHEMA | VARCHAR | the owner |
| CREATIME | TIMESTAMP | creation time |
| TYPE | INTEGER | reserved for future use |
| Column name | Data type | Description |
| REL_ID | INTEGER | relation id |
| UR_ID | INTEGER | user or role id |
| PRIV | INTEGER | privilege info |
| GRANT_ID | INTEGER | grantor id |
| GRANT_TIM | TIMESTAMP | grant time |
| GRANT_OPT | CHAR | grant option info |
| Column name | Data type | Description |
| SEQUENCE_NAME | VARCHAR | sequence name |
| ID | INTEGER | unique id |
| DENSE | CHAR | is the sequence dense or sparse |
| SEQUENCE_SCHEMA | VARCHAR | the schema name |
| CREATIME | TIMESTAMP | creation time |
| Column name | Data type | Description |
| ID | INTEGER | relation id |
| MODE | VARCHAR | special mode info |
| MODIFY_TIME | TIMESTAMP | last modify time |
| MODIFY_USER | VARCHAR | last user that modified |
| Column name | Data type | Description |
| TARGET_ID | INTEGER | reserved for future use |
| SYNON | INTEGER | reserved for future use |
| Column name | Data type | Description |
| ID | INTEGER | unique table identifier |
| TABLE_NAME | VARCHAR | the name of the table |
| TABLE_TYPE | VARCHAR | the type of the table (BASE TABLE or VIEW) |
| TABLE_SCHEMA | VARCHAR | the owner of the table |
| TABLE_CATALOG | VARCHAR | reserved for future use |
| CREATIME | TIMESTAMP | the creation time of the table |
| CHECKSTRING | LONG VARCHAR | possible check option defined for the table |
| REMARKS | LONG VARCHAR | reserved for future use |
| Column name | Data type | Description |
| TYPE_NAME | VARCHAR | the name of the data type |
| DATA_TYPE | SMALLINT | ODBC, data type number |
| PRECISION | INTEGER | ODBC, the precision of the data type |
| LITERAL_PREFIX | VARCHAR | ODBC, possible prefix for literal values |
| LITERAL_SUFFIX | VARCHAR | ODBC, possible suffix for literal values |
| CREATE_PARAMS | VARCHAR | ODBC, the parameters needed to create a column of the data type |
| NULLABLE | SMALLINT | ODBC, can the data type contain NULL values |
| CASE_SENSITIVE | SMALLINT | ODBC, is the data type case sensitive |
| SEARCHABLE | SMALLINT | ODBC, the supported search operations |
| UNSIGNED_ATTRIBUTE | SMALLINT | ODBC, is the data type unsigned |
| MONEY | SMALLINT | ODBC, whether the data is a money data type |
| AUTO_INCREMENT | SMALLINT | ODBC, whether the data type is autoincrementing |
| LOCAL_TYPE_NAME | VARCHAR | ODBC, has the data type another implementation defined name |
| MINIMUM_SCALE | SMALLINT | ODBC, the minimum scale of the data type |
| MAXIMUM_SCALE | SMALLINT | ODBC, the maximum scale of the data type |
These tables are for system's internal use only.
| Column name | Data type | Description |
| V_ID | INTEGER | unique identifier for this view |
| TEXT | LONG VARCHAR | view definition |
| CHECKSTRING | LONG VARCHAR | possible CHECK OPTION defined for the view |
| REMARKS | LONG VARCHAR | reserved for future use |
The following words are reserved in several SQL standards: ODBC
2.1, X/Open and SQL Aceess Group SQL CAE specification, Database
Language - SQL: ANSI X3H2 (SQL-92). Some words are used by SOLID
SQL. Applications should avoid using any of these keywords for
other purposes. The following table contains also potential reserved
words; these markings are enclosed in parenthesis.
| Reserved word | ODBC | X/Open SQL | ANSI SQL2 | SOLID SQL |
| ABSOLUTE | X | X | ||
| ACTION | X | |||
| ADA | X | |||
| ADD | X | X | X | X |
| ADMIN | X | |||
| AFTER | (X) | X | ||
| ALIAS | (X) | |||
| ALL | X | X | X | X |
| ALLOCATE | X | X | X | |
| ALTER | X | X | X | X |
| AND | X | X | X | X |
| ANY | X | X | X | X |
| ARE | X | X | ||
| AS | X | X | X | X |
| ASC | X | X | X | X |
| ASSERTION | X | X | ||
| ASYNC | (X) | X | ||
| AT | X | X | ||
| AUTHORIZATION | X | X | X | |
| AVG | X | X | X | |
| BEFORE | (X) | X | ||
| BEGIN | X | X | X | X |
| BETWEEN | X | X | X | X |
| BINARY | X | |||
| BIT | X | X | ||
| BIT_LENGTH | X | X | ||
| BOOLEAN | (X) | |||
| BOTH | X | |||
| BREADTH | (X) | |||
| BY | X | X | X | X |
| CALL | (X) | X | ||
| CASCADE | X | X | X | X |
| CASCADED | X | X | ||
| CASE | X | X | ||
| CAST | X | X | ||
| CATALOG | X | X | ||
| CHAR | X | X | X | X |
| CHAR_LENGTH | X | X | ||
| CHARACTER | X | X | X | |
| CHARACTER_LENGTH | X | X | ||
| CHECK | X | X | X | X |
| CLOSE | X | X | X | X |
| COALESCE | X | X | ||
| COBOL | X | |||
| COLLATE | X | X | ||
| COLLATION | X | X | ||
| COLUMN | X | X | X | |
| COMMIT | X | X | X | X |
| COMMITTED | X | |||
| COMPLETION | (X) | |||
| CONNECT | X | X | X | |
| CONNECTION | X | X | X | |
| CONSTRAINT | X | X | ||
| CONSTRAINTS | X | X | ||
| CONTINUE | X | X | X | |
| CONVERT | X | X | ||
| CORRESPONDING | X | X | ||
| COUNT | X | X | X | |
| CREATE | X | X | X | X |
| CROSS | X | |||
| CURRENT | X | X | X | |
| CURRENT_DATE | X | X | ||
| CURRENT_TIME | X | X | ||
| CURRENT_TIMESTAMP | X | X | ||
| CURRENT_USER | X | |||
| CURSOR | X | X | X | X |
| CYCLE | (X) | |||
| DATA | (X) | |||
| DATE | X | X | X | |
| DAY | X | X | ||
| DEALLOCATE | X | X | X | |
| DEC | X | X | X | |
| DECIMAL | X | X | X | X |
| DECLARE | X | X | X | |
| DEFAULT | X | X | X | |
| DEFERRABLE | X | X | ||
| DEFERRED | X | X | ||
| DELETE | X | X | X | X |
| DEPTH | (X) | |||
| DESC | X | X | X | X |
| DESCRIBE | X | X | X | |
| DESCRIPTOR | X | X | X | |
| DIAGNOSTICS | X | X | X | |
| DICTIONARY | X | (X) | ||
| DISCONNECT | X | X | X | |
| DISPLACEMENT | X | |||
| DISTINCT | X | X | X | X |
| DOMAIN | X | X | ||
| DOUBLE | X | X | X | X |
| DROP | X | X | X | X |
| EACH | (X) | |||
| ELSE | X | X | X | |
| ELSEIF | (X) | X | ||
| END | X | X | X | X |
| END-EXEC | X | X | ||
| EQUALS | (X) | |||
| ESCAPE | X | X | X | |
| EVENT | X | |||
| EXCEPT | X | X | ||
| EXCEPTION | X | X | X | |
| EXEC | X | X | X | X |
| EXECUTE | X | X | X | X |
| EXISTS | X | X | X | X |
| EXPLAIN | X | |||
| EXTERNAL | X | X | ||
| EXTRACT | X | X | ||
| FALSE | X | X | ||
| FETCH | X | X | X | X |
| FIRST | X | X | ||
| FLOAT | X | X | X | X |
| FOR | X | X | X | X |
| FOREIGN | X | X | X | X |
| FORTRAN | X | |||
| FOUND | X | X | X | |
| FROM | X | X | X | X |
| FULL | X | X | ||
| GENERAL | (X) | |||
| GET | X | X | X | |
| GLOBAL | X | X | ||
| GO | X | X | ||
| GOTO | X | X | X | |
| GRANT | X | X | X | X |
| GROUP | X | X | X | X |
| HAVING | X | X | X | X |
| HOUR | X | X | ||
| IDENTIFIED | X | |||
| IDENTITY | X | X | ||
| IF | (X) | X | ||
| IGNORE | X | (X) | ||
| IMMEDIATE | X | X | X | |
| IN | X | X | X | X |
| INCLUDE | X | X | ||
| INDEX | X | X | X | |
| INDICATOR | X | X | ||
| INITIALLY | X | X | ||
| INNER | X | X | ||
| INPUT | X | X | ||
| INSENSITIVE | X | X | ||
| INSERT | X | X | X | X |
| INT | ||||
| INTEGER | ||||
| INTERSECT | X | X | X | |
| INTERVAL | X | X | ||
| INTO | X | X | X | X |
| IS | X | X | X | X |
| ISOLATION | X | X | X | |
| JOIN | X | X | X | |
| KEY | X | X | X | X |
| LANGUAGE | X | X | ||
| LAST | X | X | ||
| LEADING | X | |||
| LEAVE | (X) | X | ||
| LEFT | X | X | X | |
| LESS | (X) | |||
| LEVEL | X | X | X | |
| LIKE | X | X | X | X |
| LIMIT | (X) | |||
| LOCAL | X | X | ||
| LOCK | X | |||
| LONG | X | |||
| LOOP | (X) | X | ||
| LOWER | X | X | ||
| MAINMEMORY | X | |||
| MATCH | X | X | ||
| MAX | X | X | X | |
| MIN | X | X | X | |
| MINUTE | X | X | ||
| MODIFY | (X) | X | ||
| MODULE | X | X | ||
| MONTH | X | X | ||
| MUMPS | X | |||
| NAMES | X | X | ||
| NATIONAL | X | X | ||
| NATURAL | X | |||
| NCHAR | X | X | ||
| NEW | (X) | X | ||
| NEXT | X | X | X | |
| NO | X | |||
| NONE | X | (X) | ||
| NOT | X | X | X | X |
| NULL | X | X | X | X |
| NULLIF | X | X | ||
| NUMERIC | X | X | X | X |
| OBJECT | (X) | |||
| OCTET_LENGTH | X | X | ||
| OF | X | X | X | X |
| OFF | X | (X) | ||
| OID | (X) | |||
| OLD | (X) | |||
| ON | X | X | X | X |
| ONLY | X | X | X | |
| OPEN | X | X | X | |
| OPERATION | (X) | |||
| OPERATORS | (X) | |||
| OPTIMISTIC | X | |||
| OPTION | X | X | X | |
| OR | X | X | X | X |
| ORDER | X | X | X | X |
| OTHERS | (X) | |||
| OUTER | X | X | X | |
| OUTPUT | X | X | ||
| OVERLAPS | X | X | ||
| PARAMETERS | (X) | |||
| PARTIAL | X | X | ||
| PASCAL | X | |||
| PENDANT | (X) | |||
| PESSIMISTIC | X | |||
| PLAN | X | |||
| PLI | X | |||
| POSITION | X | X | ||
| POST | X | |||
| PRECISION | X | X | X | X |
| PREORDER | (X) | |||
| PREPARE | X | X | X | X |
| PRESERVE | X | X | ||
| PRIMARY | X | X | X | X |
| PRIOR | X | X | ||
| PRIVATE | (X) | |||
| PRIVILEGES | X | X | X | |
| PROCEDURE | X | X | X | |
| PROTECTED | (X) | |||
| PUBLIC | X | X | X | X |
| READ | X | X | ||
| REAL | X | X | X | |
| RECURSIVE | (X) | |||
| REF | (X) | |||
| REFERENCES | X | X | X | |
| REFERENCING | (X) | X | ||
| REGISTER | X | |||
| RELATIVE | X | |||
| RENAME | X | |||
| REPEATABLE | X | |||
| REPLACE | (X) | |||
| RESIGNAL | (X) | |||
| RESTRICT | X | X | X | X |
| RETURN | (X) | X | ||
| RETURNS | (X) | X | ||
| REVOKE | X | X | X | X |
| RIGHT | X | X | ||
| ROLE | (X) | X | ||
| ROLLBACK | X | X | X | X |
| ROUTINE | (X) | |||
| ROW | (X) | |||
| ROWS | X | X | ||
| SAVEPOINT | (X) | X | ||
| SCHEMA | X | X | X | |
| SCROLL | X | X | ||
| SEARCH | (X) | |||
| SECOND | X | X | ||
| SECTION | X | X | X | |
| SELECT | X | X | X | X |
| SENSITIVE | (X) | |||
| SEQUENCE | X | (X) | X | |
| SERIALIZABLE | X | |||
| SESSION | X | |||
| SESSION_USER | X | |||
| SET | X | X | X | X |
| SIGNAL | (X) | |||
| SIMILAR | (X) | |||
| SIZE | X | X | ||
| SMALLINT | X | X | X | X |
| SOME | X | X | X | |
| SPACE | ||||
| SQL | X | X | X | X |
| SQLCA | X | X | ||
| SQLCODE | X | X | ||
| SQLERROR | X | X | X | X |
| SQLEXCEPTION | (X) | |||
| SQLSTATE | X | X | ||
| SQLWARNING | X | X | (X) | |
| START | X | |||
| STRUCTURE | (X) | |||
| SUBSTRING | X | X | ||
| SUM | X | X | X | |
| SYSTEM | X | |||
| SYSTEM_USER | X | |||
| TABLE | X | X | X | X |
| TEMPORARY | X | X | ||
| TEST | (X) | |||
| THEN | X | X | X | |
| THERE | (X) | |||
| TIME | X | X | X | |
| TIMEOUT | X | |||
| TIMESTAMP | X | X | X | |
| TIMEZONE_HOUR | X | X | ||
| TIMEZONE_MINUTE | X | X | ||
| TINYINT | X | |||
| TO | X | X | X | X |
| TRAILING | X | |||
| TRANSACTION | XX | X | ||
| TRANSLATE | X | X | ||
| TRANSLATION | X | X | ||
| TRIGGER | (X) | X | ||
| TRIM | X | |||
| TRUE | X | X | ||
| TYPE | (X) | |||
| UNDER | (X) | |||
| UNION | X | X | X | X |
| UNIQUE | X | X | X | X |
| UNKNOWN | X | X | ||
| UNREGISTER | X | |||
| UPDATE | X | X | X | X |
| UPPER | X | X | ||
| USAGE | X | X | ||
| USER | X | X | X | X |
| USING | X | X | X | X |
| VALUE | X | X | X | |
| VALUES | X | X | X | X |
| VARBINARY | X | |||
| VARCHAR | X | X | X | X |
| VARIABLE | (X) | |||
| VARYING | X | X | X | |
| VIEW | X | X | X | X |
| VIRTUAL | (X) | |||
| VISIBLE | (X) | |||
| WAIT | (X) | X | ||
| WHEN | X | X | X | |
| WHENEVER | X | X | X | |
| WHERE | X | X | X | X |
| WHILE | (X) | X | ||
| WITH | X | X | X | X |
| WITHOUT | (X) | |||
| WORK | X | X | X | X |
| WRITE | X | X | ||
| YEAR | X | X | ||
| ZONE | X |
This directory holds the backup of your database files. You define it in the BackupDirectory parameter or give it as a parameter to the backup command.
A BLOb is a large block of information such as video, sound, or a document. Blobs can be saved to and retrieved from SOLID Server.
Checkpoints are used to store a consistent state of the database quickly onto the disk. After a system crash, the database will start recovering transactions from the last checkpoint.
This is the protocol that a server and its clients use to communicate with each other. The server and client have to use the same communication protocol in order to establish a connection.
This file includes the settings for SOLID programs. You may view this file using a normal text editor like notepad.exe.
The database administrator is responsible for tasks such as:
A database client program uses a database server to store data and to execute data management operations.
The database is located in this directory. It is by default your current working directory. The database directory and database file names can be modified by changing parameters FileSpec_[1...N]
The SOLID database file holds both data and related indexes. This file is also called index file.
A DBMS is a system that stores information in and retrieves information from a database file.
A database server is a program serving database clients by executing requests received from client applications.
This file holds a log of all committed operations executed by the database server. If a system crash occurs, the database server can use this log to recover lost operations.
A network name is a combination of a server name and a communication protocol. It identifies a database server in the computer network.
ODBC is a programming interface standard for SQL database programs published by the Microsoft Corporation. SOLID SQL API offer native high performance ODBC driver.
SOLID Server is a RDBMS, which stores and retrieves information according to relations defined by the user. In RDBMSs data is stored and retrieved in two-dimensional tables.
SAG CLI is a programming interface standard, whichODBC is also based on. SOLID Server supports also SAG CLI.
All tables are contained in a higher level construct called a schema. It is a place where tables and related objects can be gathered together under one qualifying name. For each schema there are zero or more tables, and for each table, there is exactly one schema to which it belongs. The relationship between a schema and its tables is similar to that of an operating system file directory and the files contained within that directory.
The default directory for storing SOLID DBMS database files. This is either the server program's working directory or alternatively the directory set by the SOLIDDIR environment variable.
SQL is a standardized query language, designed for handling database requests and administration. The SQL syntax used in SOLID Server is based on ANSI X3.135-1989 level 2 standard including important ANSI X3.135-1992 (SQL2) extensions. For a more formal definition of the syntax, refer to Appendix D SOLID SQL Syntax of this document. For a full description of the SOLID SQL API, refer to the SOLID SQL API Help file. The same information is included in the SOLID Server Programmer's Guide and the SOLID Server Programmer's Reference.
SQL script is a file consisting of multiple SQL statements. You can use a script to administer your databases.
System tables contain information about the state of the database such as table names, column names, data types, etc. System tables and their metadata together form the data dictionary.
User privileges define the operations a certain database user is allowed to perform with the data in a database.
A user role is a combination of user privileges that can be granted to a user.