PostgreSQL – Sybase Concepts Correspondence

sybase

The following diagram depicts the main PostgreSQL concepts pretty nicely:

enter image description here

I find myself having to work with Sybase ASE 15 and I was wondering what the similar concepts are in Sybase. E.g. from what I've understood so far Sybase lacks the concept of schemas. So some questions are:

  • does Sybase ASE 15 have the concept of schemas?
  • what is the boundary for referential integrity constraints? E.g. in PostgreSQL you can have foreign keys across schemas (but not across databases). What is the situation in Sybase ASE 15?

Best Answer

SAP/Sybase ASE uses a slightly different conceptual model than PostgreSQL, Oracle & SQLServer, which all have the concept of a schema.

PostgreSQL  |   SAP ASE
----------------------- 
Cluster     |   Database Server or Database Server Cluster
Catalog     |   Database
Schema      |   User
-----------------------

In ASE, a cluster is multiple physical Database Servers working as a single logical Database Server. There is no separate designation if a Database Server (single or cluster) is running multiple databases, since that is the expected operation.

Database is the collection of related objects (Tables, views, triggers, users, etc).

Schemas: In ASE a user can own objects, and set permissions on those objects independently, which satisfies some of the same use cases for schemas BUT a user can not be dropped from the database if they own any objects. So for practical purposes, most sites don't use user level objects.

The lowest granularity storage object in ASE is a page (other databases call this a block) the size of which is set when the server is installed, but can be 2k, 4k, 8k or 16k. This means that the database allocates pages in chunks equivalent to the page size setting.

Referential integrity can cross database (schema) boundaries.

I highly recommend checking out sypron.nl. Rob Verschoor is an SAP VP & Engineer and his site is packed with good information, tips, tricks and scripts. His quick reference books are also VERY good, and I would recommend them to anyone needing to work with ASE, Replication Server or IQ.

Related Question