Sybase ASE



Sybase ASE

Induction to Sybase Adaptive Server Enterprise

White Paper

Contents

Introduction 4

ASE Architecture Basics 4

Adaptive Server Logon 5

Default System Databases 5

SQL 6

Transact - SQL (T-SQL) 6

Database Objects 7

Sybase Data Types 7

Basic SQLS and T SQL statement 8

Creating Table 8

Dropping Table 9

Altering Existing Tables 9

Adding Data to a Table 9

Selecting Data from a Table 10

Grouping of result set 12

Changing Existing Data in a Table 13

Deleting Data from Table 13

Equi joins , Natural Joins , Self Joins 14

Outer joins 16

Left Outer Join 16

Right Outer Join 16

Temporary Table 17

Views 17

Stored Procedure 18

Creating and executing stored procedures 18

Stored Procedures with Parameters 19

Returning information from stored procedures 19

Restrictions associated with stored procedures 20

Renaming stored procedures 20

Dropping stored procedures 21

Triggers in Sybase 21

Inserted and deleted table 22

Trigger restrictions 22

Disabling/Enabling triggers 22

Dropping Triggers 23

Global Variables used in Stored Procedures and Triggers 23

Transaction and Batches 23

Transactions 23

Batching 25

end Essential T-SQL commands 25

Some History of ASE 25

Other Sybase Database Products 25

Competitors & market share 25

Conclusion 25

Introduction

ASE is short for "Adaptive Server Enterprise", the relational database management software manufactured and sold by Sybase, Inc. It was Architect by Dr.Robert Epstein and Tom Haggin.Earlier Sybase called this RDBMS as “Sybase SQL Server. The product was renamed from SQL Server to Adaptive Server Enterprise (ASE) when version 11.5 was released .ASE is a versatile, enterprise-class RDBMS which is especially good at handling OLTP workloads. ASE is used intensively in the financial world (banks, stock exchanges, insurance companies), in E-commerce, as well as in virtually every other area.

The most recent ASE release is ASE version 15.0.3; the previous major release is version 12.5.x. The latest flavor of ASE is ASE Cluster Edition, a high-availability version of ASE similar to Oracle's RAC

ASE runs on the main flavors of UNIX, on Linux, on Windows NT/200x, and on MacOS X.

Sybase ASE is a proprietary, commercial software product. Yet, free versions of ASE are available as well

ASE Architecture Basics

A Sybase server consists of:

1. Two processes, data server and backup server.

2. Devices which house the database; one database (master) contains system and configuration data

3. A configuration file which contains the server attributes.

Memory Model:

The Sybase memory model consists of:

1. The program area, which is where the data server executable is stored.

2. The data cache, stores recently fetched pages from the database device.

3. The stored procedure cache, which contains optimized SQL calls.

The Sybase data server runs as a single process within the operating system; when multiple users are connected to the database, only one process is managed by the OS.

Adaptive Server Logon

Each ASE server can contains multiple databases apart from the default system databases. Each database contains multiple objects like tables, stored procedures, views etc. A user has one logon (login id/password) for the whole server. This is called the server user name. A user must be added to a database within the server before the user can access that database. The user's name within the database is just called a "user name”. A user has one server user name for the whole server and one database user name for each database he's been in. Each database has one user designated as the database owner or dbo. The dbo is privileged within the database.

A server user name may not be the same as the corresponding database username. It's generally a good idea to keep them the same. When a user logins to the database server, the user is put into his default database (different for different users).

By default an SQL query will look for a table name in the current database.

Default System Databases

Following are the system databases that are installed automatically when you install Adaptive server

Master – Controls user databases and operation of Adaptive Server as a whole. If the master database fails or gets repaired then server will become unavailable until you repair it.

Model – This is used by Adaptive Server as a template for creating new user databases. Each time the create database command is issued, SQL Server makes a copy of model.

Sybsystemprocs – It contains the Sybase System Stored Procedures

Sybsystemdb –All Sybase servers (version > 12.0) must have a sybsystemdb database. Adaptive Server uses this database for tracking transactions and during recovery. In addition, it is used for applications using two-phase commit and Distributed Transaction Management (DTM).

Note: The two-phase commit protocol allows client applications to coordinate transaction updates across two or more Adaptive Servers and treats those transactions as a single transaction. Two-phase commit guarantees that either all or none of the databases in the participating Adaptive Servers are updated.

Tempdb -Stores temporary objects, including temporary tables

Optionally, you can also install:

• The auditing database, sybsecurity

• The sample databases, pubs2 and pubs3

• The dbcc database, dbccdb

• The Job Scheduler database, sybmgmtdb

SQL

SQL (Structured Query Language) is a high-level language used in relational database systems. Originally developed by IBM's San Jose Research Laboratory in the late 1970s, SQL has been adopted by, and adapted for, many relational database management systems. It has been approved as the official relational query language standard by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO).

Almost all modern Relational Database Management Systems like Sybase ,MS SQL Server, Microsoft Access, MSDE, Oracle, DB2, Sybase, MySQL, Postgres and Informix use SQL as standard database language.

Although all those RDBMS use SQL, they use different SQL dialects. For example Sybase specific version of the SQL is called T-SQL, Oracle version of SQL is called PL/SQL, MS Access version of SQL is called JET SQL, etc.

SQL includes commands not only for querying (retrieving data from) a database, but also for creating new databases and database objects, adding new data, modifying existing data, and other functions.

Transact - SQL (T-SQL)

Sybase has extended SQL with procedural control statements. The extended SQL is called Transact-SQL.

Transact-SQL includes the following procedural extensions:

• Conditional execution (if..then)

• Looping (while)

• Variables

• Procedures calling procedures (much more on this later)

• Error handling

Database Objects

Database stores data in a set of database objects. Following are the Adaptive Server database objects

1. Table is a collection of rows; each row has set of associated columns

2. Rule allows to specify what user can or can’t enter into a particular column

3. Default is a value assigned to a column if the user doesn’t explicitly provide a value

4. Stored procedure is a named collection of SQL and control statements

5. Trigger is a stored procedure that fires when insert/update/delete happens in a table

6. Views is a named SELECT statement that is stored in a database as an object

7. Referential integrity constraints require that data being inserted in specific

Columns already have matching data in the specified table and columns

8. Check integrity constraints limit the values of data inserted into columns.

NOTE: Indexes are not considered as database object by Adaptive Server and hence not listed in sysobjects

Sybase Data Types

A data type is simply a kind of information: text, numbers, and so on. Using data types allows Sybase SQL Server to sort your information, perform calculations on your data, and to search through your data successfully. Here are the main Sybase data types

|Exact Numeric Data types |

|Data Type |Bytes |

|Tinyint |1 (0 to 255) |

|Smallint |2 (-32,768 to 32,767) |

|Integer |4 |

|Decimal(p,[s]) |2-17 |

|Numeric(p,[s]) |2-17 |

Note: p ( Precision. Specifies the total number of digits that can be stored

S ( Scale. Specifies the maximum number of digits that can be stored to the right of the decimal point

|Approximate Numeric Data Types |

|Data Type |Bytes |

|Float (P) |4 - 8 |

|Double P |8 |

|Real |4 |

|Date/Time |

|Data Type |Bytes |

|Smalldatetime |4 (Jan 1, 1900 to Jun 7 2079) |

|Datetime |(Jan 1, 1753 to Dec 31 9999) |

|Character Numeric |

|Data Type |Bytes |

|Char(n) |N |

|Varchar(n) |Actual Entry Length |

|Nchar (n) |n * @@ncharsize |

|Nvarchar(n) |@@ncharsize * no of characters |

|Monetary data types for representing currency values |

|Data Type |Bytes |

|Smallmoney |4 |

|Money |8 |

Basic SQLS and T SQL statement

Creating Table

The CREATE TABLE statement has an extremely broad range of options that are documented in the Sybase Reference manual , so only a small subset of options are described here. The basic syntax for creating a table is as follows:

Create table (column_name datatype , [, column-name datatype]...)

By default the column is NOT NULL. Table name can be max 30 chars long

Eg. : create table titles

(title_id tid not null Primary Key,

title varchar(80) not null,

type char(12),

pub_id char(4) null,

price money null,

advance money null,

royalty int null,

total_sales int null,

notes varchar(200) null,

pubdate datetime,

contract bit not null)

Note: It’s also possible to create a table from an existing table using the Select into clause

select pub_id, pub_name into newtable from publishers

A table called newtable is created, using two of the columns in the four-column table publishers. Since this statement includes no where clause, data from all the rows (but only the two specified columns) of publishers is copied into newtable.

If you just want the structure of the table with no data then

Select * into newtable from publishers where 1 = 2

Dropping Table

Basic SQL syntax for dropping a table is

Drop table

Eg: Drop table titles

Altering Existing Tables

Use the alter table command to change the structure of an existing table.

Following alterations can be done using the alter table command.

a) Add columns and constraints

b) Change column default values

c) Add either null or non-null columns

d) Drop columns and constraints

e) Change locking scheme

f) Convert column data types

g) Convert the null default value of existing columns

h) Increase or decrease column length

The syntax of the alter table command is

alter table table_name

[add column_name datatype [identity | null |not null] [, column_name datatype [identity |null | not null]]]

[drop column_name [, column_name]

[modify column_name {[data_type][[null] | [not null]]}

[, column_name datatype [null | not null]]]

Eg: alter table titles add description varchar(100)

alter table titles modify title varchar(90)

alter table titles drop royalty

Adding Data to a Table

The INSERT statement adds new rows to a table

You can use the insert command to add rows to the database in two ways; with the values keyword or with a select statement:

• The values keyword specifies values for some or all of the columns in a new row. A simplified version of the syntax for the insert command using the values keyword is:

insert table_name

values (constant1, constant2, ...)

• You can use a select statement in an insert statement to pull values from one or more tables (up to a limit of 16 tables, including the table into which you are inserting). A simplified version of the syntax for the insert command using a select statement is:

insert table_name

select column_list

from table_list

where search_conditions

Here is the full syntax for the insert command:

insert [into] [database.[owner.]] {table_name |

view_name} [(column_list)]

{values (constant_expression

[, constant_expression]...) | select_statement}

Few Sample INSERT statements are given below

a) insert into dept values (5, ‘Sales’, 500, ‘10/26/1998’)

b) insert into employee (ename, edoj, emgr_id, edeptid, esal, rec_createddt)

values (‘ADAM’, ‘12/01/2007’,10, 5,5000,‘12/01/2007’)

c) insert into temp_dept select * from dept

d) insert into temp_employee (ename, edoj, emgr_id, edeptid, esal, rec_createddt)

select ename, edoj, emgr_id, edeptid, esal, rec_createddt

from employee where edeptid = 10

Selecting Data from a Table

The select command retrieves data stored in the rows and columns of database tables using a procedure called a query. A query has three main parts: the select clause, the from clause, and the where clause

A simple syntax of a select statement is:

select select_list

from table_list

where search_conditions

The select clause specifies the columns you want to retrieve. The from clause specifies the tables to search. The where clause specifies which rows in the tables you want to see.

Few Simple Select Statements are given below

Select * from publishers

Select pub_id, pub_name, city, state from publishers

select Publisher = pub_name, pub_id from publishers

Select pub_name "Publisher's Name" from publishers

Select title_id,(total_sales * price) - (advance /2) from titles

The where clause in a select statement specifies the search conditions that determine which rows are retrieved. Search conditions, or qualifications, in the where clause includes:

• Comparison operators (=, , and so on)

• Eg: where advance * 2 > total_sales * price

• Ranges (between and not between)

Eg: where total_sales between 4095 and 12000

• Lists (in, not in)

Eg: where state in ("CA", "IN", "MD")

• Character matches (like and not like)

Eg: where phone not like "415%"

• Unknown values (is null and is not null)

Eg: where advance is null

• Combinations of search conditions (and, or)

Eg : where advance < 5000 or total_sales between 2000 and 2500

Following are few sample Select statements using where clause

1. select title_id, newprice = price * $1.15

from pubs2..titles

where advance > 5000

2. select title_id, total_sales

from titles

where total_sales between 4095 and 12000

3. select au_lname, state

from authors

where state in ("CA", "IN", "MD")

Select emp_name from employee where Created_by like ‘USER%’

The where keyword can also introduce:

• Join conditions

• Subquery

When you join two or more tables, the columns being compared must have similar values—that is, values using the same or similar datatypes.

There are several types of joins, such as equijoins, natural joins, and outer joins. The most common join, the equijoin, is based on equality. The following join finds the names of authors and publishers located in the same city:

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city = publishers.city

Because the query draws on information contained in two separate tables, publishers and authors, you need a join to retrieve the requested information. This statement joins the publishers and authors tables using the city column as the link:

where authors.city = publishers.city

You can embed a join in a select, update, insert, delete, or subquery

A subquery is a select statement that is nested inside another select, insert, update, or delete statement, inside a conditional statement, or inside another subquery. They are also called inner queries and they appear within a where or having clause of another SQL statement, or in the select list of a statement.

Following is an example of Select statement using a Subquery

select au_lname, au_fname

from authors

where au_id in (select au_id from titleauthor where royaltyper 5000 group by type

3. Select type, avg(advance) from titles where advance > 1000 and advance < 10000 group by type

You can group by maximum of 16 columns

Changing Existing Data in a Table

Use the update command to change single rows, groups of rows, or all rows in a table. If an update statement violates an integrity constraint, the update does not take place and an error message is generated. The update is canceled, for example, if it affects the table's IDENTITY column, or if one of the values being added is the wrong datatype, or if it violates a rule that has been defined for one of the columns or datatypes involved.

A simplified version of the update syntax for updating specified rows with an expression is:

update table_name

set column_name = expression

where search_conditions

Following are few sample update statements

1. update authors

set au_lname = "Health", au_fname = "Goodbody"

where au_lname = "Blotchet-Halls"

2. update titleauthor

set title_id = titles.title_id

from titleauthor, titles where titleauthor.title_id=titles.titles_id

Deleting Data from Table

The DELETE command deletes one or more rows in a table

A Simplified version of Delete Syntax is

delete [from]

     [[database.]owner.]{view_name | table_name}

     [where search_conditions]

The where clause specifies which rows are to be removed. When no where clause is given in the delete statement, all rows in the table are removed

Following are few sample delete statements

1. Delete titles

2. Delete from titles

3. delete titles from authors, titles, titleauthor where titles.title_id = titleauthor.title_id and authors.au_id = titleauthor.au_id and city = "Big Bad Bay City"

Deleting all rows from table using delete command requires log space to perform this and its very slow. Use truncate table to delete all rows in a table. It is almost always faster than a delete statement with no conditions, because the delete logs each change, while truncate table just logs the deallocation of entire data pages. As with delete, a table emptied with truncate table remains in the database, along with its indexes and other associated objects, unless you enter a drop table command.

You cannot use Delete/truncate table if another table has rows that reference it through a referential integrity constraint. Delete the rows from the foreign table, or truncate the foreign table and then Truncate /Delete the primary table.

The syntax of truncate table is:

truncate table [[database.]owner.]table_name

For example, to remove all the data in sales table use the below SQL:

truncate table sales

Equi joins , Natural Joins , Self Joins

Equi Joins: Joins that match columns on the basis of equality (=) are called equijoins. Equijoins compare the values in the columns being joined for equality and then include all the columns in the tables being joined in the results.

Eg : select * from authors, publishers where authors. city = publishers. city

In the results of this statement, the city column appears twice. By definition, the results of an equijoin contain two identical columns

Natural Join: The result of equijoins can be altered to eliminate repeating column. The new result is natural join.

The query that results in the natural join of publishers and authors on the city column is:

select publishers.pub_id, publishers.pub_name,

  publishers.state, authors.*

from publishers, authors

where publishers.city = authors.city

The column publishers.city does not appear in the results

You can use more than one join operator to join more than two tables or to join more than two pairs of columns. These “join expressions” are usually connected with and, although or is also legal

Eg:

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city = publishers.city

and authors.state = publishers.state

Self Join: Joins that compare values within the same column of one table are called self-joins. To distinguish the two roles in which the table appears, use aliases, or correlation names.

select emp.emp_name,mgr.emp_name

from employees emp, employees mgr

where emp.employee_id= mgr.employee_id

NOTE : The condition for joining the values in two columns does not need to be equality. You can use any of the other comparison operators: not equal (!=), greater than (>), less than (=), and less than or equal to ( and ! publishers.state

and pub_name = "New Age Books"

Outer joins

Joins that include all rows, regardless of whether there is a matching row, are called outer joins. Adaptive Server supports both left and right outer joins. For example, the following query joins the titles and the titleauthor tables on their title_id column. It is an example of left outer join.

Left Outer Join

select *

from titles, titleauthor

where titles.title_id *= titleauthor.title_id

It selects all rows from titles even though there is no matching title_id in titleauthor table. For rows with no corresponding title_id in titleauthor table, it will show null values for titleauthor data

Right Outer Join

select *

from titles, titleauthor

where titles.title_id =* titleauthor.title_id

It selects all rows from titleauthor even though there is no matching title_id in title table. For rows with no corresponding title_id in title table, it will show null values for title data

Sybase supports both Transact-SQL and ANSI outer joins. Transact-SQL outer joins (shown in the above example) use the *= command to indicate a left outer join and the =* command to indicate a right outer join. ANSI outer joins use the keywords left join and right join to indicate a left and right join, respectively. Sybase implemented the ANSI outer join syntax to fully comply with the ANSI standard.

The above left outer join example rewritten as an ANSI outer join will look as below:

select *

from titles left join titleauthor

on titles.title_id = titleauthor.title_id

The two methods of writing joins, ANSI or Transact-SQL, are equivalent. However Sybase recommends that your applications use ANSI outer joins because they unambiguously specify whether the on or where clause contains the predicate.

Temporary Table

Create temporary tables either by preceding the table name in a create table statement with a pound sign (#) or by specifying the name prefix "tempdb..".

Temporary tables created with a pound sign are accessible only by the current Adaptive Server session: users on other sessions cannot access them. These nonsharable, temporary tables are destroyed at the end of each session. The first 13 bytes of the table's name, including the pound sign (#), must be unique. Adaptive Server assigns the names of such tables a 17-byte number suffix. (You can see the suffix when you query tempdb..sysobjects.)

Temporary tables created with the "tempdb.." prefix are stored in tempdb and can be shared among Adaptive Server sessions. Adaptive Server does not change the names of temporary tables created this way. The table exists either until you restart Adaptive Server or until its owner drops it using drop table.

Views

View is a named SELECT statement that is stored in a database as an object. A view is derived from one or more real tables (called base tables) whose data is physically stored in the database.

Only the definition of a view is stored in the database. Not the separate copies of data.

Example:

create view emp_v as

select eid, ename, edeptid

from employee

where edeptid not in (5, 10)

Advantages of views:

1. Views allow to focus, simplify, and customize each user’s perception of the tables.

2. Views also provide a security mechanism by allowing users to access a subset of rows or columns in one or more tables.

3. Define frequently used joins, projections, and selections as views

Note: Views can also be based on more than one table

Stored Procedure

A stored procedure is a named collection of SQL statements or controlof-flow language.

Stored procedures can

• Take parameters

• Call other procedures

• Return a status value to a calling procedure or batch to indicate success or failure and the reason for failure

• Return values of parameters to a calling procedure or batch

• Be executed on remote Adaptive Servers

The procedures stored in the sybsystemprocs database whose names begin with “sp_”

are known as system procedures, because they insert, update, delete and report on data

in the system tables.

Stored procedures can serve as security mechanisms, since a user can be granted

permission to execute a stored procedure, even if she or he does not have permissions on

the tables or views

Creating and executing stored procedures

The complete syntax for create procedure is:

Create procedure [owner.]procedure_name

@parameter_name datatype [= default] [output],

@parameter_name datatype [= default] [output]]

...

…

[with recompile]

as {SQL_statements | external name dll_name}

Here is the complete syntax statement for execute:

[exec[ute]] [@return_status = ]

[[[server .]database.]owner.]procedure_name[ ;number]

[[@parameter_name =] value |

[@parameter_name =] @variable [output]

[, [@parameter_name =] value |

[@parameter_name =] @variable [output]...]]

[with recompile]

Stored Procedures with Parameters

A parameter is an argument to a stored procedure.

Eg: create proc au_info @lastname varchar(40), @firstname varchar(20)

As

Executing the proc with parameter

execute au_info Ringer, Anne

If you supply the parameters in the form “@parameter = value” you can supply them in any order.

Eg: execute au_info @firstname= Anne ,@lastname=Ringer

Default parameters

You can assign a default value for the parameter in the create procedure statement. This value, which can be any constant, is used as the argument to the procedure if the user does not supply one.

create proc pub_info @pubname varchar(40) = "Algodata Infosystems" ,@pubid int=null

as

Returning information from stored procedures

Stored procedures can return the following types of information:

1. Return status – indicates whether or not the stored procedure completed successfully.

2. Return parameters – report the parameter values back to the caller, who can then use

conditional statements to check the returned value.

Return Status can take any of the following values

|Value |Meaning |

|0 |Procedure Executed Without Error |

|-1 |Missing Object |

|-2 |Data Type Error |

|-3 |Process Was Chosen as Dead Lock Victim |

|-4 |Permission Error |

|-5 |Syntax Error |

|-6 |Miscellaneous User Error |

|-7 |Resource Error Such as out of space |

|-8 |Non –Fatal Internal Problem |

|-9 |System Limit was Reached |

|-10 |Fatal Internal Inconsistency |

|-11 |Fatal Internal Inconsistency |

|-12 |Table or Index is Corrupt |

|-13 |Database is corrupt |

|-14 |Hardware Error |

Restrictions associated with stored procedures

The create procedure definition itself can include any number and kind of SQL statements,

except use of these create statements:

• create view

• create default

• create rule

• create trigger

• create procedure

The maximum number of parameters in a stored procedure is 255.

When both a create procedure statement and an execute statement include the

output option with a parameter name, the procedure returns a value to the caller.

create procedure mathtutor

@mult1 int, @mult2 int, @result int output

as

select @result = @mult1 * @mult2

Output parameter while executing the proc

exec mathtutor 5, 6, @result output

Renaming stored procedures

Synatx for renaming stored procedure is

sp_rename objname, newname

Eg: sp_rename mathtutor mathtutor1

Dropping stored procedures

Syntax for dropping stored procedure is

drop proc[edure] [owner.]procedure_name [, [owner.]procedure_name] ...

Eg: drop proc mathtutor

Triggers in Sybase

Trigger is a special type of SP that gets executed automatically when any DML operation takes place on a table.

Triggers are used to

1. Enforce referential integrity.

2. Cascade changes to related tables.

3. Apply complex restrictions than that enforced using rules.

4. Perform analysis before and after changes to the table.

Triggers cannot have the following:

1. create and drop commands.

2. alter table, alter database, truncate table.

3. Load database and transactions.

4. Grant and revoke statements.

5. update statistics

6. reconfigure

7. disk init, disk mirror, disk refit, disk reinit, disk remirror, disk unmirror

8. select into

Syntax for creating Trigger in Sybase is

create trigger [owner.]trigger_name

on [owner.]table_name

{for {insert, update, delete}

as SQL_statements

Eg: create trigger emp_trigger

on emp

for delete

as

delete payment from payment, deleted where payment.empid = deleted.empid

Since triggers execute as part of a transaction, the following statements are not allowed in a trigger:

• All create commands, including create database, create table, create index, create

• procedure, create default, create rule, create trigger, and create view

• All drop commands

• alter table and alter database

• truncate table

• grant and revoke

• update statistics

• reconfigure

• load database and load transaction

• select into

Inserted and deleted table

Trigger maintains two internal tables which can be accessed only by trigger. It has no

existence outside the trigger. All the new rows inserted in the trigger table are also inserted into inserted table. All the deleted rows from trigger table are inserted into deleted table.

Trigger restrictions

• A table can have a maximum of three triggers: one update trigger, one insert trigger, and one delete trigger.

• Each trigger can apply to only one table. However, a single trigger can apply to all three user actions: update, insert, and delete.

• You cannot create a trigger on a view or on a temporary table, though triggers can

reference views or temporary tables

• The writetext statement will not activate insert or update triggers.

• You cannot create triggers on system tables

• You cannot use triggers that select from a text column or an image column of the inserted or deleted table

• Truncate table cannot fire a trigger

Disabling/Enabling triggers

Syntax: alter table [database_name.[owner_name].]table_name

{enable | disable } trigger [trigger_name]

Eg: alter table pubs2 disable del_pubs

alter table pubs2 enable del_pubs

Dropping Triggers

drop trigger [owner.]trigger_name [, [owner.]trigger_name]...

Eg: Drop Trigger del_pubs

Global Variables used in Stored Procedures and Triggers

Sybase has special global variables which, which should really be called system variables.

These are predefined variables which hold system related information. All global variables start with a "@@"

The most interesting global (system) variables are:

|Global Variable |Purpose |

|@@error |@error contains 0 if the last transaction is successful. otherwise @@error contains the last error number |

| |generated by the system |

|@@identity |@@identity contains the last value inserted into an IDENTITY column in |

| |the current user session. |

|@@sqlstatus |@@sqlstatus contains status information resulting from the last fetch |

| |statement for the current user session. (0-fetch successful, 1- Error, 2- No data) |

|@@rowcount |Contains the number of rows affected by the last query. |

|@@transtate |Keeps track of the current state of a transaction |

| |(0- Transaction in Progress, 1- Success, 2-Statement aborted, 3- Transaction Aborted) |

|@@servername |he name of the Sybase dataserver. |

|@@version |What version of the Sybase server you are using. |

Transaction and Batches

Transactions

A transaction represents a set of database operations which either complete successfully or rollback so that no modifications at all are made. Sybase transactions start with an explicit "begin transaction" statement and end with the standard "commit transaction". These statements have to occur in pairs. In Sybase, if a single "select", "insert", "update", or "delete" is executed, the single statement is treated as if there is a "begin transaction" before the statement and a "commit transaction" after the statement. Thus by default, each insert, update, and delete statement is considered a single transaction. You can use the following commands to create transactions:

begin transaction – marks the beginning of the transaction block.

Syntax : begin {transaction | tran} [transaction_name]

transaction_name is the name assigned to the transaction. It must conform to the rules for

identifiers. Use transaction names only on the outermost pair of nested begin/commit or

begin/rollback statements.

save transaction – marks a savepoint within a transaction:

Syntax : save {transaction | tran} savepoint_name

savepoint_name is the name assigned to the savepoint. It must conform to the rules for

identifiers.

commit – commits the entire transaction:

Syntax : commit [transaction | tran] [transaction_name]

rollback – rolls a transaction back to a savepoint or to the beginning of a transaction:

Syntax : rollback [transaction | tran] [transaction_name | savepoint_name]

Transactions allow Adaptive Server to guarantee:

• Consistency – simultaneous queries and change requests cannot collide with each other,

and users never see or operate on data that is partially through a change.

• Recovery – in case of system failure, database recovery is complete and automatic.

Example:

begin transaction royalty_change

update titleauthor set royaltyper = 65 from titleauthor, titles where royaltyper = 75

If(@@ERROR != 0)

rollback transaction royalty_change

update titleauthor set royaltyper = 35 from titleauthor, titles where royaltyper = 25

save transaction percentchanged

If(@@ERROR != 0)

rollback transaction percentchanged

commit transaction royalty_change

Checking the state of transactions

The global variable @@transtate keeps track of the current state of a transaction. Adaptive

Server determines what state to return by keeping track of any transaction changes after a

statement executes.

@@transtate may contain the following values:

|Value |Meaning |

|1 |A transaction is in progress.A transaction is in effect.Previous statement executed successfully |

|2 |Transaction Succeeded. Transaction completed and committed its changes |

|3 |Statement aborted .The previous statement was aborted.No effect on the transaction |

|4 |Process Was Chosen as Transaction aborted .The transaction aborted and rolled back any changes |

Batching

There is limited space provided to the log segment. While running big transaction, it can lead to the log suspend due to not having enough space available to complete the transaction. When you have long transaction and it crosses the log segment threshold, it is called open transaction.

To prevent the log suspend problem you need to run the transaction in small batches which is enough for the log size. For running multiple transactions in a database you need to put proper batches in your transaction so that log is not blown up.

For example if you are deleting large volume of data, use batching while deleting.

set rowcount 10000

while (@rowcount != 0)

begin

delete from TABLE where data between ‘20080101’ and ‘20080130’

select @rowcount = @@ROWCOUNT

end

Essential T-SQL commands

Important commands to be on fingertips: -

select db_name() – returns the name for current database.

select @@servername – returns the current database server.

sp_who – reports current processes with users and database info.

sp_depends – displays information about objects dependencies.

sp_help – summary information about database object.

Some History of ASE

ASE started its life in the mid-eighties as "Sybase SQL Server". For a number of years Microsoft was a Sybase distributor, reselling the Sybase product for OS/2 and (later) NT under the name "Microsoft SQL Server".

Around 1994, Microsoft basically bought a copy of the source code of Sybase SQL Server and then went its own way. As competitors, Sybase and Microsoft have been developing their products independently ever since. Microsoft has mostly emphasised ease-of-use and "Window-ising" the product, while Sybase has focused on maximising performance and reliability, and catering to the high end of the OLTP market.

When releasing version 11.5 in 1997, Sybase renamed its product to "ASE" to better distinguish itself from "MS SQL Server". Because of the common background, there are still many similarities in today's versions of ASE and MS SQL Server: it is relatively simple to learn one if you already know the other

In the early days, Sybase SQL Server was the first true client-server RDBMS which was also capable of handling real-world workloads. In contrast, other DBMSs have long been monolithic programs; for example, Oracle only "bolted on" client-server functionality in the mid-nineties. Also, Sybase SQL Server was the first commercially successful RDBMS supporting stored procedures and triggers, and a cost-based query optimizer.

The technical quality of ASE has always been, and still is, recognized throughout the IT industry. As a technology-driven company, Sybase has always been better at writing good software and building great products than at marketing those products. In my opinion, this is the main reason why today's market share of ASE is smaller than that of Oracle or Microsoft SQL Server.

Incidentally, the name 'Sybase' is said to have been derived from the words 'system' and 'database'.

Other Sybase Database Products

Apart from ASE, Sybase also sells two other database products, called ASIQ -- "Adaptive Server IQ" -- and SQL Anywhere or ASA -- "Adaptive Server Anywhere" (note that "Adaptive Server" by itself doesn't identify one specific Sybase product):

ASIQ (or just "IQ" for short) is a database for DSS-type applications (Decision Support Systems); it is not suitable for OLTP. ASIQ is very good in answering ad-hoc queries that would be very difficult to optimize in an RDBMS. For example: which female customers have shoe size 8 and bought a blue dress this month ? ASIQ performance is incredible. Also, ASIQ is unique in being the only such product where the total data volume actually shrinks (for other vendor's products, the data volume typically expands signficantly, often by an order of magnitude).

SQL Anywhere (also referred to as ASA) is a fully functional RDBMS, but typically used in smaller systems (as opposed to the large-scale ASE installations). ASA was originally known as Watcom SQL, which was acquired by Sybase in the 90's. This was renamed to SQL Anywhere/ASA; it's often referred to just as "Anywhere".

ASA runs on just about any piece of computing hardware including DOS, PalmOS, Windows and Unix. A light-weight variety is used in equipment such as mobile phones; Sybase claims to be market leader in this mobile market segment.

Sybase sells many other products, including two other databases discussed above like Replication server etc .But it is still best known for ASE. For this reason, you may hear people talking about "Sybase" when they're referring to ASE.

Competitors & market share

The main competitors of Sybase ASE are Microsoft SQL Server, IBM's DB2 and Oracle (Informix has been swallowed by IBM). Competition in the database market is fierce, especially Oracle likes to picture Sybase as an almost-dead, irrelevant company, whose customers would do themselves a big favour if they'd switch to Oracle. I think the real reason for those attacks is the fact that Sybase has a very profitable and loyal customer base (especially financials, but also government, telco, healthcare), which Oracle would love to snatch away. Apparently though, those customers have their reasons to stay with Sybase... (while we're at it, go here for some puns on Oracle).

On a technical note, a major advantage of Sybase ASE over Oracle is its more efficient use of the underlying hardware: an Oracle server consists of a handful of OS processes, continuously competing for CPU. In contrast, ASE has only one OS process for doing the same work, thus strongly reducing overhead. In addition, ASE's transaction logging mechanism is more efficient than Oracle's in terms of disk I/O usage. In other words, ASE gets significantly more end-user performance from the same hardware than Oracle would, due to a fundamentally different, and more efficient architecture.

It is widely believed by DBAs with both Sybase and Oracle experience that a Sybase ASE environment is significantly cheaper to operate and maintain than a comparable Oracle environment. Some DBAs claim that, in their experience, you need about five times more DBAs for Oracle than for Sybase ASE. That number may be on the upper end of the scale, but it seems safe to say that you need only half the DBAs for Sybase than you do for Oracle.

It is a fact that the market share of ASE is smaller than that of the other three products mentioned above. People sometimes wonder if this means that ASE is no good -- doubts that will certainly be fueled by the competition. The plain fact that Wall Street (among others) runs for a large part on ASE and other Sybase products should be an indication of the quality and reliability of the Sybase products.

It's another fact that Oracle is bigger than Sybase, but that's hardly an argument against Sybase. After all, the size of a company is not necessarily an indication of the quality or reliability of its products: if that were true, the world wouldn't be complaining about Microsoft so much.

Open-source products like MySQL and PostGreSQL cannot really be compared with ASE, as they're aimed at an entirely different class of applications. Typically these are used in environments with not many concurrent users, no high availability/recoverability requirements, not-so-large database sizes, or with a predominantly read-only workload. In other words, these products are not suited for the enterprise-level computing requirements addressed by ASE.

Conclusion

Over the past year, Forrester interviewed many Sybase customers who use Sybase Adaptive Server Enterprise (ASE). A majority of these customers rated Sybase ASE as a highly reliable database management system (DBMS) with strong database features, very good technical support, and the ability to meet or exceed their requirements to support mission-critical applications. Sybase remains committed to ASE, focusing on advanced features and innovation, especially in the areas of performance, continuous availability, scalability, security, manageability, and XML

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download