The SQL Anywhere programs

Chapter 3. Overview of SQL Anywhere
The SQL Anywhere programs

SQL Anywhere includes a set of database administration tools, as well as the SQL Anywhere database engine itself.

Each of the database tools is a SQL Anywhere client application and communicates with the database engine using the embedded SQL interface.

The Windows 3.x version of many of the programs has a slightly different name (ending with a W) so that Windows 3.x applications can coexist in the system path with Windows 95, DOS, OS/2, or Windows NT applications.

For reference information about each of the utilities in your SQL Anywhere package see the chapter "SQL Anywhere Components".

The SQL Anywhere database engine and server

At the core of SQL Anywhere is the database engine or database server, which handles all requests from client applications and carries out all manipulations of the database.

The name of the database engine or server executable depends on the operating system, as follows:

Operating system Database Engine or Server Executable

SQL Anywhere, for Windows 3.x DBENG50W.EXE is a 32-bit database engine, and DBENG50S.EXE is a 16-bit database engine provided for computers that can run Windows 3.x only in standard mode

SQL Anywhere, for operating systems other than Windows 3.x DBENG50.EXE

SQL Anywhere Desktop Runtime System for Windows 3.x RTDSK50W.EXE is a 32-bit database engine, and RTDSK50S.EXE is a 16-bit database engine provided for computers that can run Windows 3.x only in standard mode. The runtime database engines are distributable royalty-free on purchase of the SQL Anywhere Desktop Runtime System

SQL Anywhere Desktop Runtime System, for operating systems other than Windows 3.x RTDSK50.EXE. The runtime database engine is distributable royalty-free on purchase of the SQL Anywhere Desktop Runtime System

SQL Anywhere database server for Windows 3.x DBSRV50W.EXE is a 32-bit database engine

SQL Anywhere database server, for operating systems other than Windows 3.x DBSRV50.EXE, DBSRV50.NLM for the NetWare Loadable Module and DBSERVER for QNX

Operating system	Database Engine or Server Executable
SQL Anywhere, for Windows 3.x	DBENG50W.EXE is a 32-bit database engine, and DBENG50S.EXE is a 16-bit database engine provided for computers that can run Windows 3.x only in standard mode
SQL Anywhere, for operating systems other than Windows 3.x	DBENG50.EXE
SQL Anywhere Desktop Runtime System for Windows 3.x	RTDSK50W.EXE is a 32-bit database engine, and RTDSK50S.EXE is a 16-bit database engine provided for computers that can run Windows 3.x only in standard mode. The runtime database engines are distributable royalty-free on purchase of the SQL Anywhere Desktop Runtime System
SQL Anywhere Desktop Runtime System, for operating systems other than Windows 3.x	RTDSK50.EXE. The runtime database engine is distributable royalty-free on purchase of the SQL Anywhere Desktop Runtime System
SQL Anywhere database server for Windows 3.x	DBSRV50W.EXE is a 32-bit database engine
SQL Anywhere database server, for operating systems other than Windows 3.x	DBSRV50.EXE, DBSRV50.NLM for the NetWare Loadable Module and DBSERVER for QNX

The SQL Anywhere Client, DBCLIENT.EXE, enables client applications to communicate with the network database server. For Windows 3.x, the SQL Anywhere Client executable is named DBCLIENW.EXE.

For QNX, the SQL Anywhere Client is a library named DBCLIENT, which is loaded at run time.

The SQL Anywhere Desktop Runtime database engine

The SQL Anywhere Desktop Runtime System contains a runtime SQL Anywhere database engine that supports database manipulation language (DML) SQL commands.

What is it?

The runtime version of the database engine:

Is a transaction-processing database engine
Fully supports the DML subset of SQL commands, such as SELECT, INSERT, UPDATE, and DELETE
Provides full referential integrity, including cascading updates and deletes
Supports declared temporary tables
Allows users to be added to a database
Works with existing databases

Runtime and standalone database engine differences

The functions supported by the SQL Anywhere standalone database engine that are not fully supported in the runtime database engine are as follows:

Standalone database engine function How it operates in the runtime database engine

Replication You cannot replicate data

ALTER statements You cannot use:

ALTER DBSPACE to modify the characteristics of the main database file or extra dbspace

ALTER TABLE to change table definitions

ALTER PROCEDURE to change procedure definitions

CREATE statements You can use CREATE VARIABLE to create SQL variables. SQL variables do not form part of the database definitionYou cannot use the following CREATE statements:

CREATE DATATYPE to create user-defined data types

CREATE DBSPACE to create new database files

CREATE INDEX to create indexes

CREATE PROCEDURE to create procedures

CREATE FUNCTION to create user-defined functions

CREATE TABLE to create tables or temporary tables

CREATE TRIGGER to create triggers

CREATE VIEW to create views

COMMENT statement You cannot add comments to the system table

DROP statement You cannot use the DROP statement to drop a DATATYPE, DBSPACE, FUNCTION, INDEX, TABLE, VIEW, PROCEDURE, or TRIGGER from the system tables

CALL statement You cannot invoke procedures

GRANT statement You can grant the special user permissions (CONNECT, DBA, RESOURCE, GROUP, MEMBERSHIP IN GROUP) using GRANT, but you cannot change permissions on tables and views

REVOKE statement You can revoke the special user permissions using REVOKE, but you cannot change permissions on tables and views

Triggers Triggers will not be fired by the runtime database engine

No transaction log To simplify database administration, the runtime database engine does not employ a transaction log, Users of the runtime database engine should backup their databases by making backup copies of their database file itself

The database engine options that refer to transaction logs (-a,-f) Not applicable to the runtime database engine

The DBBACKUP options described in the User's Guide that refer to transaction logs ( -r, -t, -x) Not applicable to the runtime database engine

Standalone database engine function	How it operates in the runtime database engine
Replication	You cannot replicate data
ALTER statements	You cannot use: ALTER DBSPACE to modify the characteristics of the main database file or extra dbspace ALTER TABLE to change table definitions ALTER PROCEDURE to change procedure definitions
CREATE statements	You can use CREATE VARIABLE to create SQL variables. SQL variables do not form part of the database definitionYou cannot use the following CREATE statements: CREATE DATATYPE to create user-defined data types CREATE DBSPACE to create new database files CREATE INDEX to create indexes CREATE PROCEDURE to create procedures CREATE FUNCTION to create user-defined functions CREATE TABLE to create tables or temporary tables CREATE TRIGGER to create triggers CREATE VIEW to create views
COMMENT statement	You cannot add comments to the system table
DROP statement	You cannot use the DROP statement to drop a DATATYPE, DBSPACE, FUNCTION, INDEX, TABLE, VIEW, PROCEDURE, or TRIGGER from the system tables
CALL statement	You cannot invoke procedures
GRANT statement	You can grant the special user permissions (CONNECT, DBA, RESOURCE, GROUP, MEMBERSHIP IN GROUP) using GRANT, but you cannot change permissions on tables and views
REVOKE statement	You can revoke the special user permissions using REVOKE, but you cannot change permissions on tables and views
Triggers	Triggers will not be fired by the runtime database engine
No transaction log	To simplify database administration, the runtime database engine does not employ a transaction log, Users of the runtime database engine should backup their databases by making backup copies of their database file itself
The database engine options that refer to transaction logs (-a,-f)	Not applicable to the runtime database engine
The DBBACKUP options described in the User's Guide that refer to transaction logs ( -r, -t, -x)	Not applicable to the runtime database engine

The Sybase Central database management program

Sybase Central is a graphical database management tool for Windows 95 or for Windows NT 3.51 or later. Sybase Central is the recommended tool for carrying out the tasks described in the following sections, including the following:

Creating and maintaining databases
Backing up databases
Managing transaction log files
Working with compressed databases
Working with read-only databases
Compacting databases by unloading and reloading

SQL Anywhere administration utilities

For a complete description of each of the utilities that come with your SQL Anywhere package, see the chapter "SQL Anywhere Components".

You can access the administrative utilities from Sybase Central, which is the recommended tool for managing SQL Anywhere databases, from ISQL, or as command-line utilities.

Action Utility Description

Stopping a database server stop utility Stops a running database server or SQL Anywhere Client

Creating and maintaining databases initialization utility Creates a new database. If you wish to use a customized sorting and comparison order (collation) for a new database, you need to use the collation utility.

Modifying erase utility Erases a database file, log file, or write file

Inspecting database files information utility Provides information about a database file or write file

Modifying the transaction log transaction log utility Displays or changes the name of the transaction log.Transaction logs are a record of all changes made to a database, and are an important part of database backups. Transaction logs can be translated into ASCII SQL command file form by the log translation utility as part of recovery from some forms of database failure, and applied to a copy of the database made at the time the transaction log was started to restore the database

Backing up databases backup utility Performs full or incremental online backup of databases

Validating the database validation utility Checks the validity of all indexes on a database table, and should be used in conjunction with backups to maintain the integrity of information in a database

Working with compressed databases database compression utility Compresses a database file (compressed database files are usually 40 to 60 percent of their original size)

uncompression utility Expands a compressed fileCompressed databases are useful in situations where file space is limited. The SQL Anywhere database engine is not able to update compressed database files directly, however, and so compressed databases need to be used in conjunction with a write file created with the write file utility.

Working with read-only databases write file utility Manages write files and provides a method of working with read-only databases.There are some situations where it is necessary to work with databases that SQL Anywhere cannot modify directly: read-only databases. Database provided on CD-ROM are a common class of read-only database.Compressed databases are read-only databases. If you wish to test new applications without modifying a production database, a write file is a useful alternative.All changes made to a read-only database are made instead to a write file, leaving the master database unchanged. The SQL Anywhere standalone engine and network server also produce a transaction log, which maintains records of all changes made to the write file. If, at a later point, you wish to apply the changes in the write file to the master database, you can do this by applying the transaction log to the original database using the log translation utility.

Unloading and reloading data unload utility Unloads the database. This program creates a text command file named RELOAD.SQL. This command file may be run from ISQL to rebuild the database from scratch.The size of a database file does not decrease when rows are deleted (although future insertions will use the space freed by the deleted rows). You can rebuild a database in order to reclaim the disk space associated with deleted rows.The REBUILD batch or command file (not available in the Windows 3.x version of SQL Anywhere) automates the process of rebuilding a database.

Upgrading databases upgrade utility Upgrades databases made using earlier versions of SQL Anywhere to SQL Anywhere version 5.0 format.For information about upgrading databases, see "The Upgrade utility".

Action	Utility	Description
Stopping a database server	stop utility	Stops a running database server or SQL Anywhere Client
Creating and maintaining databases	initialization utility	Creates a new database. If you wish to use a customized sorting and comparison order (collation) for a new database, you need to use the collation utility.
Modifying	erase utility	Erases a database file, log file, or write file
Inspecting database files	information utility	Provides information about a database file or write file
Modifying the transaction log	transaction log utility	Displays or changes the name of the transaction log.Transaction logs are a record of all changes made to a database, and are an important part of database backups. Transaction logs can be translated into ASCII SQL command file form by the log translation utility as part of recovery from some forms of database failure, and applied to a copy of the database made at the time the transaction log was started to restore the database
Backing up databases	backup utility	Performs full or incremental online backup of databases
Validating the database	validation utility	Checks the validity of all indexes on a database table, and should be used in conjunction with backups to maintain the integrity of information in a database
Working with compressed databases	database compression utility	Compresses a database file (compressed database files are usually 40 to 60 percent of their original size)
	uncompression utility	Expands a compressed fileCompressed databases are useful in situations where file space is limited. The SQL Anywhere database engine is not able to update compressed database files directly, however, and so compressed databases need to be used in conjunction with a write file created with the write file utility.
Working with read-only databases	write file utility	Manages write files and provides a method of working with read-only databases.There are some situations where it is necessary to work with databases that SQL Anywhere cannot modify directly: read-only databases. Database provided on CD-ROM are a common class of read-only database.Compressed databases are read-only databases. If you wish to test new applications without modifying a production database, a write file is a useful alternative.All changes made to a read-only database are made instead to a write file, leaving the master database unchanged. The SQL Anywhere standalone engine and network server also produce a transaction log, which maintains records of all changes made to the write file. If, at a later point, you wish to apply the changes in the write file to the master database, you can do this by applying the transaction log to the original database using the log translation utility.
Unloading and reloading data	unload utility	Unloads the database. This program creates a text command file named RELOAD.SQL. This command file may be run from ISQL to rebuild the database from scratch.The size of a database file does not decrease when rows are deleted (although future insertions will use the space freed by the deleted rows). You can rebuild a database in order to reclaim the disk space associated with deleted rows.The REBUILD batch or command file (not available in the Windows 3.x version of SQL Anywhere) automates the process of rebuilding a database.
Upgrading databases	upgrade utility	Upgrades databases made using earlier versions of SQL Anywhere to SQL Anywhere version 5.0 format.For information about upgrading databases, see "The Upgrade utility".