I. A S E  OVERVIEW.

  • ASE Server
  • Memory Model
  • Transaction Processing
  • Backup Procedures
  • Recovery Procedures
  • ASE Logins
  • ASE Groups
  • Security and Account Setup
  • Database Creation
  • Storage Concepts
  • Transact SQL
  • Transact SQL Extensions

ASE Server:–

ASE server consists of

  • Two processes, data server and backup server.
  • Devices house the databases. Master database contains system and configuration data.
  • Configuration file contains the server attributes.
  • The logical page size of the server can vary between 2K to 16K (2K, 4K, 8K and 16K). 2K page size is the default.

Memory Model:–

The ASE executable runs as a single process within the OS. Multiple users connect to the database. Only one process is managed by the OS. Each Sybase database connection requires memory.

memory-model-ase

  • ASE executable or the dataserver executable resides in the program area.
  • Cache stores both Data Cache and Procedure Cache. Both have independent memory allocation.
  • The data cache stores recently fetched pages from the database device.
  • The procedure cache contains information of optimized sql calls.

Transaction Processing:–

  • Transactions are written to the data cache first and then they advance to the transaction log and database device.
  • Pages are discarded from the data cache on rollback.
  • Transaction logs are used to restore data in event of a hardware failure.
  • Checkpoint operation flushes all updated/committed memory pages to their respective tables.
  • Transaction logging is required for all databases. Image (blob) fields may be exempt.
  • During update transaction, the data page(s) containing the row(s) are locked.
  • Row level record locking is available and can be enabled. To facilitate this, the table structures may need to be tuned.

Backup Procedures :–

  • Backup procedures are facilitated by the backup server.
  • Backup procedure is carried out using “dump database” operation.
  • Backup operation can be performed when the database is on-line or offline.
  • Transactions can be dumped using “dump transaction” commands.

Recovery Procedures:–

  • Load procedures are facilitated by the backup server.
  • Load procedure is carried out using “load database” operation.
  • Load operation loads the designated database with the named dump file.
  • Transactions can be loaded using “load transaction” commands.
  • Transaction commands can then be issued to load multiple transaction dump files.

ASE Logins:–

Each Adaptive Server user must have a
login account. Characteristics of Login accounts

  • A login name is unique on the server
  • Password is required to login
  • A default database (optional) is assigned.
  • A default language (optional).
  • A full name (optional).

ASE Groups:–

  • A group is created within a database.
  • The Database Owner is responsible for creating groups and assigning users to them.
  • A login/user is always a member of the “public” group, which includes all users on Adaptive Server.
  • The login/user can also belong to one other group.
  • Use sp_helpuser system stored procedure to find out what group the user belongs to.

Security and Account Setup:–

  • The initial login shipped with ASE is “sa” (system administrator).
  • The “sa” login has the role “sa_role” which is the super-user.
  • User logins are added at the server level.
  • The login is granted access to one or more databases by adding the login as a user of the respective database.
  • Access is granted to one or more tables and objects within a database.
  • A user in a database can be aliased as “dbo”.
  • “dbo” has all rights within a database.

Database Creation:–

  • Databases are initialized with the “create database” command.
  • Many databases can exist in one ASE server.
  • Tables are created within each database.
  • ASE databases can reside on one or more segments/devices.

Storage Concepts:–

  • Tables are stored in segments.
  • Segment is an area within a device with a name and a size. A segment/device is allocated for a database.
  • The transaction log is stored in its own segment, usually on a separate device.

Transact-SQL:–

  • Transact-SQL is a robust programming language created by SYBASE to access the database and the underlying data in the tables.
  • ANSI 89 and 92 compliant.
  • Used for managing ASE database and server(s).

Transact SQL Extensions:–

  • Any number of result sets can be returned to calling applications via SELECT statements.
  • Triggers and Stored procedures (System and User) are supported for writing SQL that are stored in a compiled format, which allows for automatic execution and faster execution of DML SQL code.
  • Cursors are supported for row by row processing.
  • Functions (System/Arithmetic/Date/String), Rules and Defaults are supported.
  • Temporary tables are supported, which allows customized private work tables to be created for complex processes.
  • Global and Local variable support.
  • Flow control statements (IF-ELSE, WHILE …).
  • Federal Information Processing Standards (fipsflagger).