Release 12.5 GA and above
INDEX:
I….. .ASE Overview
II…..System Databases and Tables
III….Transact SQL
IV….Transactions
V……Isolation Levels
VI…..Locking
VII…ASE Supported Platforms
VIII..ASE Utilities
IX…..Component Integration Services (CIS)
X……New Features – ASE 15
XI….ASE Resources
XII…SYBASE Data Server & Integration Products
For Details of the INDEX refer the next pages w.r .to each INDEX.
III. TRANSACT SQL .
Datatypes:–
Note:–Table with contents to be drawn……..
.
.
.
.
.
.
.
.
List System Objects & Attributes:–
select name
from sysobjects where type = ‘U’
go
OR
use system SP sp_tables
select name
from sysobjects where type = ‘T’
go
select name
from sysobjects where type = ‘P’
go
sp_help emp
go
sp_spaceused emp
go
sp_helptext emp_proc
go
Creating a table:–
Design of Table for creating a table :–
Examples of creating a table:–
1.)
create table emp (
emp_id numeric(8,0) identity,
emp_name varchar(50) not null,
salary money not null,
dept_cd char(3) not null,
fax_no integer null
)
go
2.)
create table invoice (
invoice_id numeric(8,0) identity,
sales_rep_id numeric(8,0) not null,
date smalldatetime not null,
comment varchar(255) null )
on data_seg2
go
3.)
create table err_cd (
err_id integer not null,
err_desc varchar(60) not null,
constraint pk_err_cd primary key clustered (err_id)
)
go
Altering a table:–
Alter table examples
- alter table employee add cell_no numeric(10) null
go
- alter table employee drop constraint ‘emp_dept_constr’
go
Default
alter table charge_item replace price_overridable_ind default 0
go
Change column name (quotes required)
sp_rename ‘emp.dept’,dept_name
go
Table Creation – Alternatives:—-
If you already have a table and would like to create another with the same column definitions, use ‘select into’
select * into my_copy from source_table
To capture a subset of the columns, replace * with names
select col1, col2 into my_copy from source_table
The above also copy the data to the new table
To skip the data copy, use an ‘empty where clause’
select * into my_copy
from source_table
where 1=0
Default rules are not copied to the new table
Indexes are not created on the new table
The database option
“select into/bulkcopy/pllsort” must be on in this database to use ‘select into’
Indexes:—
on emp (emp_id)
go
on emp (emp_name)
go
go /* Creates sorted index on a segment */
Typically,
Clustered index will be created on the primary key of a table
Non-clustered indexes are used where needed.
Clustered Vs Non-clustered indexes:—
Clustered indexes:
Non-clustered indexes:
Creating a constraint:—
Constraints are used to define primary keys, enforce uniqueness, and to describe foreign key relationships. By default, indexes are created upon creation of unique or primary key constraints.
Defaults:–
Table Partitioning:–
Insert performance on partitioned tables is improved, as multiple ‘entry points’ (last page entries) are created. Partitioned tables require slightly more disk space and small bit of additional memory.
alter table invoice — creates 3 partitions on invoice table
partition 3
go
Tables containing large amounts of data (> 2 GB) need to be spread across several devices, using sp_placeobject. Note that this procedure affects only future operations – if a table load of more than 2 GB is to be performed, it would have to be split into two or more stages.
sp_placeobject ‘data_seg2′,’invoice’
go
Object Permissions:–
Object security is fairly straightforward and is handled using the ‘grant’ Transact SQL statement.
go
go
go
go
Stored procedure security allows you to grant access on a business logic basis. For example, if you had a stored proc that updated the invoice table and selected data from the customer table, you could grant the execute privilege on the stored proc, and you’re done. The user would be able to run the procedure to update/select from the tables, but will not get to the tables directly.
on proc_upd_invoice to Srinath
go
Binding Rules to Columns:–
Default values for columns can be specified at the time a table is created, or afterwards via the modify command.
Examples
create table emp (
emp_id integer not null,
emp_name varchar(50) null,
salary money default 15000
)
go
alter table emp replace salary default 19000
go
Modifying Column:–
To modify/alter a column type you need to be “dbo” and have select into option enabled, in the database defaults.
create table emp (
emp_id integer not null,
emp_name varchar(50) null,
salary money default 0
)
go
/* make the name column longer */
alter table emp modify emp_name varchar(100) not null
go
/* rename the column */
sp_rename ‘emp.emp_name’, name
go
Move objects to segments:–
When databases contain more segments, it is often necessary to move tables between segments.
Examples
drop index ‘employee.idx_employee’
go
create clustered index on emp (emp_id) on new_seg
go
sp_placeobject new_seg , ‘emp’
go
sp_placeobject new_seg, ‘emp.emp_notes’
go
Views:–
Views offer an alternative way to look at data and can also be used to enforce security by limiting the data that is seen by users.
create view emp_view
as select emp_id, emp_name from emp
where emp_id > 1 and emp_id < 101
select * from emp_view
Stored Procedures:–
Stored procedures are compiled versions of SQL statements. Performance benefits are significant as network traffic is reduced, and the optimizer does not need to re-parse the code.
Stored Procedures are classified into:
Examples:
- sp_who –Provides information on users on ASE
- sp_help – Provides a information/listing of all objects in the current database.
All of the System Stored Procedures start with “sp_” and are located in the ‘sybsystemprocs’ system database
create procedure proc_rtv_invoice
(@inv_id numeric(8,0) ) as
select inv_id, inv_date, salesrep_emp_id
from invoice
where inv_id = @inv_id
return
go
exec proc_rtv_invoice 325
go
Triggers:–
Trigger is a special type of SP that gets executed automatically when any DML operation takes place on a table.
create trigger emp_trigger
on emp
for insert, update, delete
as
create trigger emp_trigger
on emp
for delete
as
delete payment
from payment, deleted
where payment.empid = deleted.empid
II. SYSTEM DATABASES AND TABLES.
When ASE is installed following databases are installed.
* master
* model
* sybsystemprocs
* tempdb
Optional databases that can be installed are
* sybsecurity
* sybsystemdb
* dbcc
* sybdiag
* pubs2 and pubs3
ASE databases:
master database:–
Controls the operation of ASE and stores the information of user databases and devices on the ASE server
No.… Information…………………………………………….System Table
1 User accounts :……………………………………………….syslogins
2 Remote user accounts :…………………………………… sysremotelogins
3 Remote servers that this servercan interact with:….sysservers
4 Ongoing processes :………………………………………….sysprocesses
5 Configurable environment variables :………………….sysconfigures
6 System error messages:…………………………………….sysmessages
7 Databases on Adaptive Server:…………………………..sysdatabases
8 Storage space allocated to each database:……………..sysusages
9 Tapes and disks mounted on the system:……………… sysdevices
10 Active locks:…………………………………………………..syslocks
11 Character sets:………………………………………………..syscharsets
12 Languages:…………………………………………………….syslanguages
13 Users who hold server-wide roles:……………………. sysloginroles
14 Server roles:………………………………………………….syssrvroles
15 Adaptive Server engines that are online:…………….sysengines
model database:–-
All new user databases use model database as a template database. ASE copies this database whenever a new database is created and extends the space of the new user database as specified in the ‘create database’ command Use sp_tables system SP to get the list of system tables in model database.
sybsystemprocs:-–
All ASE system stored procedures are stored in this database.
tempdb :–-
This is a temporary database used by ASE. “tempdb” database is used to store temporary tables and other working structures.
tempdb is installed by default during ASE installation.
Multiple tempdb(s) can be created based on need.
Optional databases::
sybsecurity :–-
This database contains the audit system to audit database users activity on ASE.
sysbsystemdb:-–
This is used by ASE to store information of distributed transactions and provides transaction coordination for remote servers using RPCs and CIS services.
dbcc :-–
This database is used to store configuration information of target database operation activity and results of the operation from ‘dbcc checkstorage’
sybdiag :–-
This database may be created and used by SYBASE Technical Support to troubleshoot the system. The database holds diagnostic information.
pubs2 and pub3 :-–
These are learning databases provided by SYBASE.
I. A S E OVERVIEW.
ASE Server:–
ASE server consists of
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.
Transaction Processing:–
Backup Procedures :–
Recovery Procedures:–
ASE Logins:–
Each Adaptive Server user must have a
login account. Characteristics of Login accounts
ASE Groups:–
Security and Account Setup:–
Database Creation:–
Storage Concepts:–
Transact-SQL:–
Transact SQL Extensions:–
Exception Handlers:
In SQL server the errors are always written to sysmessages table, so no exception can be handled here. The type of access to the SQL server from the front end determines the state of the error and how the error information can be retrieved.
Eg.
a). For ODBC type of access the error messages are stored in the SQLError object of the SQL server, the front end should query that object to get the error message.
b). For OLE-DB type of access the error messages are stored in the ISQLServerErrorInfo object. So the OLE-DB provider library
RAISERROR('Test Only', 1, 2) WITH SETERROR
SELECT @@ERROR
RAISERROR (101, 1, 2) WITH SETERROR
SELECT @@ERROR
Here is the result set:
Msg 50000, Level 1, State 50000
Test Only
-----------
50000
(1 row(s) affected)
Msg 101, Level 1, State 101
Line 0: SQL syntax error.
-----------
101
(1 row(s) affected)
The first RAISERROR returns an @@ERROR value of 50000. The second returns the syntax error message used by Microsoft® SQL Server™ with an @@ERROR value of 101.
Formatting T-SQL Blocks:
T-SQL blocks have the following sections:
PROCEDURE (Parameters)
RETURN DATATYPE
AS
Variables DATATYPE;
BEGIN
Executable_statements;
If err then goto Err_block
RETURN Value
Err_block:
Process error and return the error string
END
GO
Recommendations:
Formatting SQL statement:
|
SELECT
|
INSERT |
UPDATE
|
DELETE |
| SELECT FROM WHERE GROUP BY HAVING ORDER BY |
INSERT INTO VALUES OR INSERT INTO SELECT
|
UPDATE SET WHERE |
DELETE FROM WHERE
|
Control Structures
IF <expression> THEN
Executable_statements
ELSE
Executable_Statements
If executable statements are more than one statement then use BEGIN … END block for grouping the statements.
Recommendation:
Formatting Loops
Transact-SQL offers the WHILE loop. The GOTO statement can also be used for looping purposes.
Syntax:
WHILE Boolean_expression
{sql_statement | statement_block}
[BREAK] [CONTINUE]
Eg:
WHILE condition
Executable_Statements
END
BREAK / CONTINUE can be used within the loop to encounter the exceptions.
Each loop has a loop boundary and a loop body. The loop body should be indented from the boundary.
Rule
The naming convention for variables and constants is as follows:
1. Program Structure
The following should be the templates used in T-SQL source code files.
Any T-SQL source file should have a file header of the following format (Header) followed by the body of the procedure. Since SQL server doesn’t support the concept of Packages the only type of header is as follows:
/*
** (C) <Year>, Deutsche Bank Group
** Name : (name of the package/procedure/function)
** Description :
** Author :
** MODIFICATION HISTORY :
** The history of changes should be in chronological sequence
Date Name of the Proc/ Func Author Reasons for Change
[Comments by Version control system will be included in this block].
[The position of the same depends on the version control software and projects].
*/
Some general rules to be followed for functions / procedures.
Insert into table_one select * from table_two;
The select * statement should not be used because, the statement would be invalid in case the structure of the table changes. So the columns have to be explicitly mentioned.
1.1 Indenting
Tabbing is used for indenting. Statement blocks used with the following statements are indented one tab stop from the corresponding statements.
1.2 Spacing
A single space should be placed before and after all operators. A single space should also be placed after the comma of each argument in function parameter lists.
1.3 Comments
Rule for Stored Procedures
Rule for Triggers
Rule
