Sybase ASE Introduction

Sybase

At the time, Sybase called the database server "Sybase SQL Server"and made a deal with Microsoft to share the source code for Microsoft to re-market on the OS/2 platform as "SQL Server".

Due to disagreements between the two companies over revenue sharing (or lack thereof), Sybase and Microsoft decided to split the code-lines and went their own way, although the shared heritage is very evident in the Transact-SQL (T-SQL) procedural language as well as the basic process architecture. The big difference is that Sybase has a Unix heritage, while Microsoft SQL Server was adapted and optimized only for the Microsoft Windows NT operating system (vendor lock-in). Sybase continues to offer versions for Windows, several varieties of Unix, and for Linux.

Sybase's main products include:

Data Management Products

• Adaptive Server Enterprise (ASE) - Enterprise Class RDBMS and Column-oriented DBMS storage platform.
• Advantage Database Server (ADS) - Relational Database Management System
• Sybase Replication Server - a platform for data replication and movement between database systems, including Sybase and Oracle

Analytics Products

• Sybase IQ - a data warehousing and business intelligence RDBMS
• RAP the Trading Edition- a capital markets analytics platform
• Sybase Complex Event Processing - Optimized real-time risk and trade analytics software

Mobility Products

• SQL Anywhere - RDBMS with a small footprint designed for mobility
• Afaria - Mobile Device management and security software
• Sybase Unwired Platform (SUP) - a framework for developing mobile applications
• SMS Ad Exchange - an SMS mobile advertising service.
• GRX 365 - network performance and security
• mBanking 365 - a mobile banking product
• MMS 365 - a content delivery gateway
• MMX 365 - a messaging exchange
• Sybase 365 mCommerce Solution - an end-to-end solution for mBanking, mPayments and mRemittance

Tools

• PowerBuilder - a rapid application development (RAD) tool.
• PowerDesigner - a modeling and design tool .

Versions

• 11.0 & 11.5 are called as SQL server (Called as SQL Server as this is in tie up with Microsoft )

• 12.0,12.5,15.0(Currently in market ) &15.7(New Version released)

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

Transact-SQL augments SQL with certain additional features:

• Control-of-flow language
• Local variables
• Various support functions for string processing, date processing, mathematics, etc.
• Changes to DELETE and UPDATE statements

These additional features make Transact-SQL Turing complete.

Sybase IQ is a relational database software system used for business intelligence and data warehousing, produced by Sybase, Inc..

As a column-oriented DBMS, Sybase IQ stores data tables as sections of columns of data rather than as rows of data. This has a number of advantages; if a search is being done for items matching a particular value in a column of data, only the storage objects corresponding to that data column within the table need to be accessed. A traditional row-based database would have to read the whole table top to bottom.

Sybase IQ is optimized for data warehouse type applications, where data is added to databases but typically not modified much if at all, and the typical access is to search through the data. The column based storage which enables good performance for reading through data somewhat slows down writing data (instead of just the last data object requiring updating with a row object, one data object per table column must be updated). Large volume data imports can still be reasonably efficient.

Prior to version 15, Sybase IQ didn’t have automatic queues for requests of changing its objects, like other RDBMSes (SQL Server, Oracle, ASE, ASA, MYSQL, DB2, etc) do. Therefore, a DDL/DML statement may return an error if the target object is in use. Sybase IQ provides explicit lock table statement support that will allow behavior similar to traditional RDBMS, although the locking scheme is less granular.[5] Since version 15, this behavior is configurable. A DDL/DML operation can wait until other active operations on the same object are complete.

Development Tools

For a DBA ,SQL ADV,dbartsian,dsEDIT are most useful tools

• Powerbuilder
• Rapid SQL
• SQL ADV
• db artisan
• ds EDIT

Sybase Architecture

• Sybase is a single process and multi threaded architecture
• Oracle is a multi process multi threaded architecture

Kernal Structure : /Sub-routine/ System calls has been written in the Sybase Server

Sequencer Framing of SQL statements to be executed in the form of tokens

Parser Checks for Semantic errors

Compiler checks for syntax,it will be executing the code

Managers

• Transaction Manager takes care of Transaction manager
• Resource Manager takes care of Resource management
• Lock Manager takes care of Locking of data

System & Optional Databases (default databases with sizes)

When Adaptive Server is installed by default 5 databases will be created

Default Databases

• Master (6 MB)
• Model (2 MB)
• SybSystem procs (Sybsystem procedures 120 MB)
• Temp db (3 MB)
• SysSytem db (2 MB) (distributed transactions)

Optional Databases

• Syb Security (Auditing database)
• Pubs2 and Pubs3 (Training Database)
• dbccdb

* MASTER Database can be expanded only on MASTER Device

* MODEL Database cannot be bigger than tempdb

Two devices are created after installation

• Master device holds Master ,Model, Tempdb, SysSysdb
• Syb Systemprocs device all stored procedures and SybSystemprocs
• Master Database that is created on Master Device cannot be expanded onto any other device