Sybase Session 1 Document

                                                                                                                                        

SYBASE SESSION – 1 DOCUMENT

___________________________________________________________________________________________________ Copyrights Reserved Page 1 of 27 

                                                                                                                                        

Introduction 

DATABASE



DBMS Vs RDBMS



TYPES OF RDBMS



COMPARISION WITH OTHER DATABASE



TOOLS IN SYBASE



GETTING STARTED WITH SYBASE



FEATURES OF SYBASE



Basic Commands in Sybase



Data Types



Constraints



Indexes



Queries

___________________________________________________________________________________________________ Copyrights Reserved Page 2 of 27 

                                                                                                                                        

Database

 Collection of data  It a structure for storing information.  They are typically organized into tables, having collections of records  You may think of a table as a collection of columns and rows.  For example, a table named Employee might contain the EmpID, Empname, Empsalary, Emp desigination and other information about that employee.  Each row is called a data record and each column is called a record field.

Relational Database  A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged). DBMS stands for Database Management System. 

It is a collection of interrelated data and a set of program to access the data.



The main objective is to provide a convenient environment to retrieve and store the data from the database.

RDBMS stands for Relational Database Management System.  It is a software program used to create the database and it allows you to enter, manipulate, and retrieve data  RDBMS also structured in database tables, fields and records.  Most RDBMS use SQL as database query language. Difference between DBMS and RDBMS  Both are used to organize data and provide retrieval of data. 

DBMS permits only one person to access the database at a given time, on the other hand RDMS allows many users simultaneous access to the database.



RDBMS leads DBMS



Security wise



Used in large organizations to store large amount of data.



It has quick retrieval mechanisms.

___________________________________________________________________________________________________ Copyrights Reserved Page 3 of 27 

                                                                                                                                         What is SYBASE?  It is database server developed by Sybase Corp. Which was Architect by Dr.Robert Epstein and Tom Haggin. 

Sybase support RDBMS (it is database management system that is based on the relational model gives data model based on predicate logic and set theory) and T-SQL

What is T-SQL ? 

To communicate with the sql server and to manipulate objects stored in sql server, client programs and SP use a variety of Structured Query language is called T-SQL



T-sql extends sql by adding program flow-control constructs, local variables and other capability to allow the DBA to build code-based objects in SP and Trigger.

What are the Versions we have till now ? 

Basically created for Unix platform. Microsoft and Sybase made an agreement and released sql server 1.0 but their agreement soon came to an end on 1993 .1995 Sybase released Sybase sql server 11.0 (has a single process,multi-thread database engine and includes a query optimizer). Latest releases server 15.0

The following is a list of the versions of ASE released over the years:        

  

Sybase SQL Server 3.0 – the first publicly released version of the product released in 1988. Sybase SQL Server 4.0 – released in 1990. Sybase SQL Server 4.2 – released in 1991.  Sybase SQL Server 4.8 – released in 1992. Sybase SQL Server 4.9 – also released in 1992, along with version 4.9.1; version 4.9.2 was released in 1993. Sybase System X, aka Sybase SQL Server 10.0 – released in 1993. Sybase SQL Server 11.0 – released in 1995. Adaptive Server Enterprise 11.5 – released in 1997. --> (The version which we are using right now) Adaptive Server Enterprise 11.9 – released in 1998. It does not appear that version 11.9.0 was ever publicly released; instead 11.9.2 was the released version, and 11.9.3 was the first 64 bit version made available. Adaptive Server Enterprise 12.0 – released in 1999. Adaptive Server Enterprise 12.5 – released in 2001. Adaptive Server Enterprise 12.5.0.1 – released in 2002, along with version

___________________________________________________________________________________________________ Copyrights Reserved Page 4 of 27 

                                                                                                                                         12.5.0.2.  Adaptive Server Enterprise 12.5.0.3 – released in 2003, along with version 12.5.1.  Adaptive Server Enterprise 12.5.2 – released in 2004, along with version 12.5.3.  Adaptive Server Enterprise 12.5.3a – released in 2004/2005.  Adaptive Server Enterprise 12.5.4 – released in 2006.  Adaptive Server Enterprise 15.0 – released in 2005, along with version 12.5.3.  Adaptive Server Enterprise 15.0.1 – released in 2006.  Adaptive Server Enterprise 15.0.2 – due for release in 2007.

Types of RDBMS: 

SYBASE.



MS SQL SERVER.



MYSQL.



Microsoft ACCESS.



DB2.



TERADATA

Sybase Vs Oracle

A Sybase server consists of: 

Two processes, data server and backup server.



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



A configuration file which contains the server attributes.

___________________________________________________________________________________________________ Copyrights Reserved Page 5 of 27 

                                                                                                                                        

An Oracle instance consists of: 

Six processes: PMON Process Monitor, SMON System Monitor, DBWn Database Writer, LGWR Log Writer, ARCn Archiver, and CKPT Checkpoint.



Data files which contain the tables and other data objects, control files which contain configuration information, redo log files for transaction processing, and archive files for recovery purposes.



Configuration files which contains the instance attributes, and external security information.

Memory Model Sybase: 

The program area, which is where the dataserver executable is stored.



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



The stored procedure cache, which contains optimized sql calls.

Oracle: 

The SGA, which contains executable program code.



The shared pool, which is split between the library cache and data dictionary cache, optimized queries are stored here.



The redo log buffer, which is where transactions are stored before they are written to the redo logs.

Transaction Processing Sybase: 

Transactions are written to the data cache, where they advance to the transaction log, and database device. When a rollback occurs, pages are discarded from the datacache. The transaction logs are used to restore data in event of a hardware failure. A checkpoint operation flushes all updated (committed) memory pages to their respective tables.



Transaction logging is required for all databases; only image (blob) fields may be exempt.



During an update transaction, the data page(s) containing the row(s) are locked. This will cause contention if the transaction is not efficiently written. Record locking can be turned on in certain cases, but this requires sizing the

___________________________________________________________________________________________________ Copyrights Reserved Page 6 of 27 

                                                                                                                                         table structure with respect to the page size. The locking mechanism can be bypassed by setting the isolation level, to allow data to be read from uncommitted transactions.

Oracle 

Transactions are written to the redo log buffer, where they advance to the redo logs, data file buffer, and data files. When a rollback occurs, block images are discarded from the redo log buffer; as the previous block data is held in the rollback segment blocks. Committed transactions are promoted to the archive logs. Archive logs are used to restore the data in the event of a hardware failure. A checkpoint operation flushes all updated (committed) memory blocks from the log buffer and database buffer pool.



During an update transaction, record locking provides prevents data block collisions from occurring. Access to the "before" image of the record(s) is made available during this time, which reduces contention. This is a patented record locking mechanism.

Backup Procedures Sybase: 

A "dump database" operation can be performed when the database is online or offline.Subsequent "dump transaction" commands need to be issued during the day, to ensure acceptable recovery windows. A new ASE 12.5 feature allows database dump files to be "mined" for a single table. Thus an entire database loaded is not required when only a single table restore is needed.

Oracle: 

Previous to Oracle 8, the only way to back up the database was through a "cold" backup. This involved shutting down the Oracle instance, backing up the data files, and restarting upon completion.

Recovery Procedures Sybase: 

A "load database" command loads the designated database with the named dump file.Subsequent "load transaction" commands can then be issued to load multiple transaction dump files.

___________________________________________________________________________________________________ Copyrights Reserved Page 7 of 27 

                                                                                                                                         Oracle: 

Recovery is achieved by restoring the data files, and verifying the control files are synchronized properly. When RMAN is used (it is now the standard in most production databases) recovery is accomplished via an automated set of processes.

Security and Account Setup

Sybase: 

The initial login shipped with Sybase is "sa" (system administrator). This login has the role "sa_role" which is the super-user, in Sybase terms. User logins are added at the server level, and then granted access to each database, as needed. Within each database, access to

tables

can

be

granted per application requirements. A user can also be aliased as "dbo", which automatically grants them all rights within a database.

Oracle: 

Oracle is shipped with several built-in accounts: system, internal, and sys. Operating system authentication is required in order for a login to be created with similar privileges. After a login is created, access is then granted to the tables within schemas as needed.

Database Creation Sybase: 

Databases are initialized with the "create database" command. It is not unusual for a Sybase server to contain many different databases. Tables are created

within

each

database;

users

refer

to

tables

by

using

ownername.tablename nomenclature. “Aliasing” users with the database eliminate the need for the prefix. Typically, a user will be aliased as "dbo" (database owner), which also gives the same result. Oracle: 

Databases are initialized with the "create database" command. In most (99.9%) cases the database name is the same as the instance name, and

___________________________________________________________________________________________________ Copyrights Reserved Page 8 of 27 

                                                                                                                                         there is only one database per instance. Within an Oracle instance, schemas are created which contain the tables for an application. Tables are referenced by schema_name.tablename. Each user is assigned a default schema upon creation; this schema name is the same as the user name.

Storage Concepts Sybase: 

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

Oracle: 

Tables are stored in tablespaces; a tablespace is made up of one or more data files.Although it is possible to use raw devices within Oracle, it is not recommended. Control files, rollback segments, and redo logs are all stored in separate files within the operating system. Note that Oracle 10g RAC features a special OFS (Oracle File System), which allows the database to get better i/o performance over conventional file systems.

Partitioning Sybase: 

Semantic "smart" partitioning is new in ASE 15. Tables can be partitioned by range, value, or round-robin. By default, all tables in ASE 15 are partitioned, round-robin.

Oracle: 

Oracle 9i,10g does support semantic partitioning, by list, range, hash, hash range, and composite.

Transact-SQL Sybase: 

Transact-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Temporary tables are supported, which allows customized, private work tables to be created for complex processes.

___________________________________________________________________________________________________ Copyrights Reserved Page 9 of 27 

                                                                                                                                        

Oracle: 

PL-SQL is a robust programming language in which stored procedures can be written. The procedures are stored in a compiled format, which allows for faster execution of code. Cursors are supported for row by row processing. Since PL-SQL procedures cannot return a result set, returning rows to a calling application requires implementing arrays as "out" variables. One powerful feature of PL-SQL is the ability to create custom functions which can be used within SQL statements.

Triggers

Sybase: 

Sybase ASE supports insert, update, and delete triggers. Triggers are fired after the transaction starts, and have the capability to roll back if required. Images of the 'before' and 'after' records are visible within the trigger.

Oracle: 

Oracle supports select, insert, update, and delete triggers. Triggers can be set to fire 'before' or 'after' a transaction. NEW and OLD aliases are used to refer to the before and after images of the data. Oracle also supports DDL Event Triggers.

Analytic Functions, OLAP Sybase: 

Sybase ASE does not support analytic functions (median, n-tile, lead/lag, over partitions) at the moment; Sybase IQ, the data warehouse product, does support most analytic functions.

Oracle: 

Oracle 10 does support analytic functions like median, n-tile, and lead/lag, over partitions.

___________________________________________________________________________________________________ Copyrights Reserved Page 10 of 27 

                                                                                                                                         Flat File Processing Sybase: 

Sybase ASE includes the BCP utility (Bulk-Copy) which can import or export data via flat files. "Fast" mode imports are achieved by simply removing the indexes (and triggers) from the target table.

Oracle: 

Oracle includes the SQLLDR utility (SQL Loader) which can import via flat files.” Direct" mode imports are achieved by simply removing indexes (and triggers)from the target table, and specifying direct=yes at command line. There is no Oracle utility for exporting data to a flat file; sqlplus must be used to spool the data to a file.

Performance and scalability Sybase: 

On a regular basis, Sybase Adaptive Server Enterprise on Sun captures the #1 TPC benchmark for SMP performance, check sybase.com for the latest results.



Scalability: Sybase 15 scales from handheld devices to enterprise level servers.

Oracle: 

Oracle has always been known for its speed and performance. Oracle 10 supports over 15,000 active user connections. The patented record locking scheme made it an attractive candidate for scanned applications marketed by PeopleSoft and the like. The ability to turn transaction logging on and off at will allows Oracle squeak by competitors during benchmark analysis runs.

Price Sybase: 

Price per seat is average, compared to other vendors; Sybase ASE is the leader in lowest transaction cost, with the best performance, among the leading enterprise database vendors.

Oracle: 

Price per seat is relatively high, compared to other vendors.



Support is achieved by opening "TAR" cases with the support team.

___________________________________________________________________________________________________ Copyrights Reserved Page 11 of 27 

                                                                                                                                         Management and Development Tools (for Windows) Sybase: 

ISQL is the interactive query tool used with Sybase.t is useful for entering queries and stored procedures.



Sybase Central is shipped with Sybase ASE. It offers a good interface for performing basic database tasks. The "best of breed"product in this category is DB-Artisan by Embarcadero Technologies. Sybase recently released Workspace, an Eclipse based SQL development environment, which features a stored procedure debugger and graphical SQL creator. For development, Sybase Inc. offers Workspace Powerbuilder,Powerdesigner, Power J and its "Studio" line products. Powerbuilder remains the most robust, straightforward, and practical choice for windows development, supporting many other RDBMs in addition to Sybase ASE.

Oracle: 

SQL-Plus is the interactive query tool used with Oracle; it is useful for entering queries and stored procedures. Complex reporting capabilities are available for those willing to learn SQL-Plus extensions.



Oracle Enterprise ships with Enterprise Manager, a full featured front end to the intricacies of Oracle. The main areas of the system (security, storage, schemas) are managed by totally separate applications, which is cumbersome at times.

SYSTEM DATABASES: There are 4 types of system databases 

Master Database



Model Database



Sybsystemprocs



Temporary Database

___________________________________________________________________________________________________ Copyrights Reserved Page 12 of 27 

                                                                                                                                         Master Database 

Controls the user databases and the operation of SQL Server as a whole. Known as master, it keeps track of such things as user accounts, ongoing processes, and system error messages.



If the master database fails or repaired than server will became unavailable until you repair

Model database 

A template for new user databases. The build master program and the install model script create model when SQL Server is installed. Each time the create database command is issued; SQL Server makes a copy of model and extends it to the size requested, if necessary.



It is house for those items you want available across all databases (rules, defaults, user defined data types)

SYBSYSTEMPROCS In server 4.21 version we had 3 sys databases



It is a modular program keep it all SP in it database, If Sybsystemprocs exists it will check to see whether you execute system SP.

There are two types of Sybsystemprocs



Stored Procedure

___________________________________________________________________________________________________ Copyrights Reserved Page 13 of 27 

                                                                                                                                          System procedure Stored procedure: 

A collection of SQL statements and optional control-of-flow statements stored under a name. SQL Server-supplied stored procedures are called system procedures.

System procedures: 

Stored procedures that SQL Server supplies for use in system administration. These procedures are provided as shortcuts for retrieving information from the system tables, or mechanisms for accomplishing database administration and other tasks that involve updating system tables.



Sp_help, Sp_helptext, Sp_dboption, Sp_lock etc.

Temporary database 

The temporary database in SQL Server, tempdb, that provides a storage area for temporary tables and other temporary working storage needs (for example, intermediate results of group by and order by).

P-T-T: This table exists in the tempdb until server gets restart or shutdown T-T-T: This table exists in the tempdb only for the session or Sp in which they created, all sever go for T-T-T creation System tables 

One of the data dictionary tables. The system tables keep track of

___________________________________________________________________________________________________ Copyrights Reserved Page 14 of 27 

                                                                                                                                         information about the SQL Server as a whole and about each user database. The Master Database contains some system tables that are not in user databases.

Database –level sy tb level sy tb Syscomments Sysindexes Sysobjects

System

syslogins sysdatabases sysservers

SYBASE TOOLS: There are three major tools provided by sybase for administering maintaining, and accessing the sql server.

They are



Bcp



Isql



defncopy

SQL 

It is std interface provided by Sybase, which used for batch program execution (like a editor)

___________________________________________________________________________________________________ Copyrights Reserved Page 15 of 27 

                                                                                                                                        

BCP 

It is used to import or export data from database to os files and os files to database

DEFNCOPY 

It is used to import or export creation text of the objects (SP, Trigger, rules, view, defaults) from database to os files and os files to databases.



The other sybase tools we have is DBA Tools

Sybase DBA Tools: 

The Sybase Database Administration (DBA) Tools provide users with the tools to administer, manage and monitor all aspects of a Sybase database. The tools may be accessed from the application DBA Tools menu or from the schema browsers context sensitive popup menu.

Instance Manager: Provides manageability of sybase instance allowing the user to modify and view the server parameters and

viewing

server

version information.

Storage manager: Provides manageability of sybase databases, database devices, dump devices and caches, allowing a user to visualize and maintain storage.

Security Manager: Provides manageability of logins, server roles, users and roles allowing the user to manage permissions, roles and security of the sybase database. ___________________________________________________________________________________________________ Copyrights Reserved Page 16 of 27 

                                                                                                                                         Session Manager :Provides manageability of database sessions including user lock which allow the user to monitor and kill sessions. What to study in Sybase   

Performance tunings Versions sample interview qstns



Exercises on query



session split+

Features of Sybase Version 11.5 : 

Among all the versions the version 11.5 supports all the concepts like



It has a single process, multi-thread database engine & includes a Query optimizer.



It has a New roll back records, optimizer changes & trace flag 326.



It has changes to locking, changes to error locking

How to get in to Sybase ? 1.Applications->system tools->terminal

___________________________________________________________________________________________________ Copyrights Reserved Page 17 of 27 

                                                                                                                                        

___________________________________________________________________________________________________ Copyrights Reserved Page 18 of 27 

                                                                                                                                        

2. Getting started with the sybase server.

Type the IP (ssh -X [email protected])followed by password

___________________________________________________________________________________________________ Copyrights Reserved Page 19 of 27 

                                                                                                                                        

3.After that we have to type the path followed by user name and password

Path:cd /opt/sybase source SYBASE.sh isql -Usa -SLOCALHOST

This is how we have to get in to the sybase database.

___________________________________________________________________________________________________ Copyrights Reserved Page 20 of 27 

                                                                                                                                        

Basic Commands:

We have few very basic commands. The commands are follows

a) Create : It is used to create a table for inserting the records. The syntax is follows CREATE TABLE skill (skill_id INTEGER NOT NULL,skill_name CHAR( 20 ) NOT NULL, skill_type CHAR( 20 ) NOT NULL)

b)Insert : It is used to insert a row in the table. The syntax is follows INSERT [ INTO ][ owner.] table_name[ (column_name, ...) ]..VALUES (expression...)

c) Update: It is used to modify (add or modify) rows in the table. The syntax is follows Update tablename set column name = value where some condition.

d)Alter : It is used to add or delete a column in the table. The syntax is follows Alter table tablename add(new column) int null.

e)Delete : It is used to delete the table. The syntax is follows Delete tablename where some condition.

f) Views : It is a logical way of looking at the physical data located in the tables. The syntax is follows. Create view view_name as select column name from tablename.

___________________________________________________________________________________________________ Copyrights Reserved Page 21 of 27 

                                                                                                                                        

g) Rules:

It is used to assign a rule for the column. Create rule rulename as some condition (like @mark >50) Next we have to bind the rule to the table: sp_bindrule rulename,'tablename.columnname'

h) Stored Procedures: It is a database object that exists independently of the tables. The command to declare a stored procedure is create proc {procedure name} as {statement or block of statements}

For eg: create proc myproc as begin select au_id, au_lname from authors where state="CA" select title_id, price from titles where price > $20.00 return 0 end ___________________________________________________________________________________________________ Copyrights Reserved Page 22 of 27 

                                                                                                                                        

i)Triggers : It is an integral part of statement that fires when you do insert ,update and delete operation. The syntax is follows Create trigger triggername on tablename for {insert | update | delete} [,..] as sql statements [return]

j)Cursors : It is a pointer that identifies a specific working row within a set. The syntax of the declare cursor statement is : declare cursor_name cursor for select_statement [for {read only | update [of column_name_list]}] For eg: declare authors_crsr cursor for select au_id, au_lname, au_fname from authors where state != "CA" for update

___________________________________________________________________________________________________ Copyrights Reserved Page 23 of 27 

                                                                                                                                        

1.Create table win_ratio with 4 columns team,matches_win,match_played a)insert values to the above table as mentioned teams(axn, ibn, ifm, abv), matches_played(5, 10,7, 5), matches_won(2, 4, 2, 6) b)Obtain the teams who won either 2 or 6times. 2. Give the difference between the following. 

Order by and group by



Count and count (*)



Truncate and Delete.

3. Update the above table as given: 

Modify the table 2 with 4 in matches_won column.



Delete the teams who won more than 4times.

4. While ascending and descending orders in the same query for different columns, in what order does the result set appears?

5. Explain the following with syntax(if any) 

view as security



temporary and permanent temporary tables



check constraints

6. What are the limitations of 

Default



View

___________________________________________________________________________________________________ Copyrights Reserved Page 24 of 27 

                                                                                                                                          Rule

7. What are the types of index? Explain the leaf structure of the index. 8. Give the difference between the following 

Binary and var binary



Char and var char



Rule and Default.

9. Explain views with check option with some examples?

DATABASE SCHEMA The database scheme consists of four relations: Product(maker,model,type) PC(code,model,speed,ram,hd,cd,price) Laptop(code,model,speed,ram,hd,screen,price) Printer(code,model,color,type,price) The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

___________________________________________________________________________________________________ Copyrights Reserved Page 25 of 27 

                                                                                                                                        

Answer the following queries depending on the above database scheme.

1. Find the model number, speed and hard drive capacity for all the PCs with prices below $500. Result set: model, speed, hd. 2. Point out the maker and speed of the laptops having hard drive capacity more or equal to 10 Gb. 3. Find the model number, speed and hard drive capacity of the PCs having 12x CD and prices less than $600 or having 24x CD and prices less than $600. 4. Find the model number, RAM and screen size of the laptops with prices over $1000. 5. Find out the makers that sale PCs but not laptops. 6.Find out the models and prices for all the products (of any type) produced by maker B.

Book For Reference:

___________________________________________________________________________________________________ Copyrights Reserved Page 26 of 27 

                                                                                                                                         Book: Sybase SQL Server 11 by Ray Rankins Jeffrey R. Garbus David Solomon Bennett Wm.McEwan

___________________________________________________________________________________________________ Copyrights Reserved Page 27 of 27