Adaptive Server architectures

User's Guide
PART 5. The Adaptive Server Family
CHAPTER 30. Transact-SQL Compatibility

Adaptive Server architectures

Adaptive Server Enterprise and Adaptive Server Anywhere are complementary products, with architectures designed to suit their distinct purposes. Adaptive Server Anywhere is designed as a workgroup or departmental server requiring little administration, and as a personal database. Adaptive Server Enterprise is designed as an enterprise-level server for the largest databases.

This section describes architectural differences between Adaptive Server Enterprise and Adaptive Server Anywhere. It also describes the Adaptive Server Enterprise-like tools that Adaptive Server Anywhere includes for compatible database management.

Servers and databases

The relationship between servers and databases is different in Adaptive Server Enterprise and in Adaptive Server Anywhere.

In Adaptive Server Enterprise, each database exists inside a server, and each server can contain several databases. Users are granted login rights to the server, and can connect to the server. They can then use each database on that server for which they have been granted permissions. System-wide system tables, held in a master database, contain information common to all databases on the server.

No master database in Adaptive Server Anywhere

In Adaptive Server Anywhere, there is no level corresponding to the Adaptive Server Enterprise master database. Instead, each database is an independent entity, containing all of its system tables. Users are granted connection rights to a database, not to the server. When a user connects, the connection is to an individual database. There is no system-wide set of system tables maintained at a master database level. Each Adaptive Server Anywhere database server can dynamically load and unload multiple databases, and users can maintain independent connections on each, but there is no way of addressing more than one database from within a single connection.

Adaptive Server Anywhere provides tools in its Transact-SQL support and in its Open Server support to allow some tasks to be carried out in a Adaptive Server Enterprise-like manner. For example, Adaptive Server Anywhere provides an implementation of the Adaptive Server Enterprise sp_addlogin system procedure that carries out the nearest equivalent action: adding a user to a database.

For information about Open Server support, see Adaptive Server Anywhere as an Open Server.

File manipulation statements

The Transact-SQL statements CREATE DATABASE, DROP DAT

ABASE, DUMP DATABASE, and LOAD DATABASE are not supported in Adaptive Server Anywhere, although Adaptive Server Anywhere does have its own CREATE DATABASE statement that is different in syntax.

Device management

Adaptive Server Anywhere and Adaptive Server Enterprise use different models for managing devices and disk space, reflecting the different uses for the two products. While Adaptive Server Enterprise sets out a comprehensive resource management scheme using a variety of Transact-SQL statements, Adaptive Server Anywhere is designed to be able to manage its own resources automatically, and its databases are regular operating system files.

Adaptive Server Anywhere does not support Transact-SQL DISK statements, such as DISK INIT, DISK MIRROR, DISK REFIT, DISK REINIT, DISK REMIRROR, and DISK UNMIRROR.

For information on disk management, see Working with Database Files

Defaults and rules

Adaptive Server Anywhere does not support the Transact-SQL CREATE DEFAULT statement or CREATE RULE statement. The CREATE DOMAIN statement allows a default and a rule (called a CHECK condition) to be incorporated into the definition of a user-defined data type, and so provides similar functionality to the Transact-SQL CREATE DEFAULT and CREATE RULE statements.

In Adaptive Server Enterprise, the CREATE DEFAULT statement creates a named default. This default can be used as a default value for columns by binding the default to a particular column or as a default value for all columns of a user-defined data type by binding the default to the data type. A default is bound to a data type or column using the sp_bindefault system procedure.

The CREATE RULE statement creates a named rule which can be used to define the domain for columns by binding the rule to a particular column or which can be used as a rule for all columns of a user-defined data type by binding the rule to the data type. A rule is bound to a data type or column using the sp_bindrule system procedure.

In Adaptive Server Anywhere, a user-defined data type can have a default value and a CHECK condition associated with it, which are applied to all columns defined on that data type. The user-defined data type is created using the CREATE DATATYPE statement.

Default values and rules, or CHECK conditions, can be defined for individual columns using the CREATE TABLE statement or the ALTER TABLE statement.

For Info For a description of the Adaptive Server Anywhere syntax for these statements, see SQL Statements.

System tables

In addition to its own system tables, Adaptive Server Anywhere provides a set of system views that mimic relevant parts of the Adaptive Server Enterprise system tables. These are listed and described individually in Views for Transact-SQL Compatibility, which describes the system catalogs of the two products. This section provides a brief overview of the differences.

The Adaptive Server Anywhere system tables are held entirely within each database, while the Adaptive Server Enterprise system tables are held partly inside each database and partly in the master database. The Adaptive Server Anywhere architecture does not include a master database.

In Adaptive Server Enterprise, the system tables are owned by the database owner, user ID dbo. In Adaptive Server Anywhere, the system tables are owned by the system owner, user ID SYS. The Adaptive Server Enterprise-compatible system views provided by Adaptive Server Anywhere are owned by a dbo user ID.

Administrative roles

Adaptive Server Enterprise has a more elaborate set of administrative roles than Adaptive Server Anywhere. In Adaptive Server Enterprise there is a set of distinct roles, although more than one login account on a Adaptive Server Enterprise can be granted any role, and one account can possess more than one role.

Adaptive Server Enterprise roles

In Adaptive Server Enterprise the following are distinct roles:

System Administrator Responsible for general administrative tasks unrelated to specific applications; can access any database object.
System Security Officer Responsible for security-sensitive tasks in Adaptive Server Enterprise, but has no special permissions on database objects.
Database Owner Has full permissions on objects inside the database that he or she owns, can add users to a database and can grant other users the permission to create objects and execute commands within the database.
Data definition statements Permissions can be granted to users for specific data definition statements, such as CREATE TABLE or CREATE VIEW, enabling the user to use those statements to create database objects.
Object owner Each database object has an owner, who may grant permissions to other users to access the object. The owner of an object automatically has all permissions on the object.

In Adaptive Server Anywhere, the following database-wide permissions have administrative roles:

The Database Administrator (DBA permissions) has, like the Adaptive Server Enterprise database owner, full permissions on objects inside the database that he or she owns and can grant other users the permission to create objects and execute commands within the database. The default database administrator is user ID DBA.
The RESOURCE permission allows a user to create any kind of object within a database. This is instead of the Adaptive Server Enterprise scheme of granting permissions on individual CREATE statements.
Adaptive Server Anywhere has object owners in the same way that Adaptive Server Enterprise does. The owner of an object automatically has all permissions on the object, including the right to grant permissions.

For seamless access to data held in both Adaptive Server Enterprise and Adaptive Server Anywhere, you should create user IDs with appropriate permissions in the database (RESOURCE in Adaptive Server Anywhere, or permission on individual CREATE statements in Adaptive Server Enterprise) and create objects from that user ID. If the same user ID is used in each environment, object names and qualifiers can be identical in the two databases, helping to ensure compatible access.

Users and groups

There are some differences between the Adaptive Server Enterprise and Adaptive Server Anywhere models of users and groups.

In Adaptive Server Enterprise, connections are made to a server, and each user requires a login ID and password to the server as well as a user ID for each database they will access on that server. Each user of a database can be a member of at most one group.

In Adaptive Server Anywhere, where connections are made to a database, there is nothing corresponding to the login ID. Instead, each user is granted a user ID and password on a database in order to use that database. Users can be members of many groups, and group hierarchies are allowed.

Both servers support user groups, so that you can grant permissions to many users at one time. However, there are differences in the specifics of groups in the two servers. For example, Adaptive Server Enterprise allows each user to be a member of only one group, while Adaptive Server Anywhere has no such restriction. You should compare the documentation on users and groups in the two products for specific information.

Both Adaptive Server Enterprise and Adaptive Server Anywhere have a public group, for defining default permissions. Every user is automatically a member of the public group.

Adaptive Server Anywhere supports the following Adaptive Server Enterprise system procedures for managing users and groups.

For Info For the arguments to each procedure, see Adaptive Server Enterprise system and catalog procedures.

System procedure

Description

sp_addlogin

In Adaptive Server Enterprise, this adds a user to the server. In Adaptive Server Anywhere, this adds a user to a database.

sp_adduser

In Adaptive Server Enterprise and Adaptive Server Anywhere, this adds a user to a database. While this is a distinct task from sp_addlogin in Adaptive Server Enterprise, in Adaptive Server Anywhere, they are the same.

sp_addgroup

Adds a group to a database.

sp_changegroup

Adds a user to a group, or moves a user from one group to another.

sp_droplogin

In Adaptive Server Enterprise, removes a user from the server. In Adaptive Server Anywhere, removes a user from the database.

sp_dropuser

Removes a user from the database.

sp_dropgroup

Removes a group from the database.

System procedure	Description
sp_addlogin	In Adaptive Server Enterprise, this adds a user to the server. In Adaptive Server Anywhere, this adds a user to a database.
sp_adduser	In Adaptive Server Enterprise and Adaptive Server Anywhere, this adds a user to a database. While this is a distinct task from sp_addlogin in Adaptive Server Enterprise, in Adaptive Server Anywhere, they are the same.
sp_addgroup	Adds a group to a database.
sp_changegroup	Adds a user to a group, or moves a user from one group to another.
sp_droplogin	In Adaptive Server Enterprise, removes a user from the server. In Adaptive Server Anywhere, removes a user from the database.
sp_dropuser	Removes a user from the database.
sp_dropgroup	Removes a group from the database.

In Adaptive Server Enterprise, login IDs are created on a server-wide basis. In Adaptive Server Anywhere, users are created for individual databases. ]

Database object permissions

The Adaptive Server Enterprise and Adaptive Server Anywhere GRANT and REVOKE statements for granting permissions on individual database objects are very similar. Both allow SELECT, INSERT, DELETE, UPDATE, and REFERENCES permissions on database tables and views, and UPDATE permissions on selected columns of database tables. Both allow EXECUTE permissions to be granted on stored procedures.

For example, the following statement is valid in both Adaptive Server Enterprise and Adaptive Server Anywhere:

GRANT INSERT, DELETE
ON TITLES
TO MARY, SALES

This statement grants permission to use the INSERT and DELETE statements on the titles table to user Mary and to the sales group.

The WITH GRANT OPTION clause, allowing the recipient of permission to grant them in turn, is supported in both Adaptive Server Anywhere and Adaptive Server Enterprise, although Adaptive Server Anywhere does not permit WITH GRANT OPTION to be used on a GRANT EXECUTE statement.

Database-wide permissions

Adaptive Server Enterprise and Adaptive Server Anywhere use different models for database-wide user permissions. These are discussed in Users and groups. Adaptive Server Anywhere employs DBA permissions to allow a user full authority within a database. This permission is enjoyed by the System Administrator in Adaptive Server Enterprise, for all databases on a server. However, DBA authority on an Adaptive Server Anywhere database is different from the permissions of a Adaptive Server Enterprise Database Owner, who must use the Adaptive Server Enterprise setuser statement to gain permissions on objects owned by other users.

Adaptive Server Anywhere employs RESOURCE permissions to allow a user the right to create objects in a database. A closely corresponding Adaptive Server Enterprise permission is GRANT ALL used by a Database Owner.