Sybase iSQL ASE 12.5 drive


This article will get you up and running with Sybase database commands. I personally refer it many times when I need  specific command. I kept it comprehensive reference material for anyone to jump start.

What is difference between SQL & T-SQL?

SQL- set of sqls are submitted individually to the database server.

T-SQL- the batch program is written where in all commands are submitted to the server in a single go. usually batches are run overnight and and all inserts and updates happen and these batches are scheduled. where as sqls’ are to run separately…..

all sqls’ are put in a file and schedule them called –t-sql .. besides it offers some other commands too.

SQL is the Structured Query Language the ANSI/ISO Standard database language. SQL Server’s implementation of the language is called Transact-SQL (T-SQL).

Sybase iSQL COMMAND TOUR

Connect sybase from Linux console
isql -Hpiidb199:9794 -SNYQ_MWDATA -Umware_rw -DGE2

use DN_Name
go

select DB_Name
go

sp_helpdb DB_Name
go

shows this info
name,db_size,owner,dbid,created,status,device_fragments,size,usage,created,free kbytessp_help
go

It shows all the availble objects of following type
view, user table, triggers, system table, stored procedure

sp_help Table_Name
go

shows description of table i.e.
Column_name,Type,Length,Prec,Scale Nulls,Default_name,Rule_name,Access_Rule_name,Identity

set rowcount 2
select * from ge_company
go

Below query will display exactly 2 records from table
select * from dbo.ge_comapany where company_id = 766
go

*Note table should be given with owner, here dbo is owner of ge_company

sp_helpindex ge_company
go

This command will show you list of indexes available into ge_company table
select name from sysobjects where name like “ge_%”

By default these tables will be created automatically when new database created
1> sp_help
2> go

Name Owner Object_type
—————————— —————————— ———————-
syb_auto_db_extend_control dbo user table
sysalternates dbo system table
sysattributes dbo system table
syscolumns dbo system table
syscomments dbo system table
sysconstraints dbo system table
sysdepends dbo system table
sysgams dbo system table
sysindexes dbo system table
sysjars dbo system table
syskeys dbo system table
syslogs dbo system table
sysobjects dbo system table
syspartitions dbo system table
sysprocedures dbo system table
sysprotects dbo system table
sysqueryplans dbo system table
sysreferences dbo system table
sysroles dbo system table
syssegments dbo system table
sysstatistics dbo system table
systabstats dbo system table
systhresholds dbo system table
systypes dbo system table
sysusermessages dbo system table
sysusers dbo system table
sysxtypes dbo system table
Some more tips of Sybase blocking handel

2> sp_block
3> go

no process should be in Absolute blocking spid

spid hostname
————————- —— ———-
Absolute blocking spid is 537 piias1359
Absolute blocking spid is 49 pi944c3n9
Absolute blocking spid is 616 paias1186
One login can play with these commands

Show login user information
> sp_displaylogin
> go

Suid: 3018
Loginame: irfansh

Fullname:
Default Database: env
Default Language:
Auto Login Script:
Configured Authorization:
Locked: NO
Date of Last Password Change: Jun 14 2007 2:01PM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: ANY
(return status = 0)

To change logged in users password
>sp_password old_passwd, new_passwd
>go

Selecting data from Table: querys and functions
> select sum(salary) from emp_tbl
> go

Determining which table columns to join
1> sp_helpjoins ge2_group, ge2_group_apps
2> go

first_pair
—————————— ——————————
group_id group_id

(1 row affected)
(return status = 0)

To find a table’s referenced tables.
1> sp_helpconstraint
2> go

Aggregate Function Result

sum([all|distinct] expression) Total of the (distinct) values in the expression

avg([all|distinct] expression) Average of the (distinct) values in the expression

count([all|distinct] expression) Number of (distinct) non-null values in the expression

count(*) Number of selected rows

max(expression) Highest value in the expression

min(expression) Lowest value in the expression

count vs. count(*)

While count finds the number of non-null values in the expression, count(*) finds the total number of rows in a table. This statement finds the total number of books:

Adaptive Server ignores any null values in the column on which the aggregate function is operating for the purposes of the function (except count(*), which includes them). If you have set ansi null to on, Adaptive Server returns an error message whenever a null value is ignored. For more information, see the set command in the Reference Manual.

Use the having clause to display or reject rows defined by the group by clause. The having clause sets conditions for the group by clause in the same way where sets conditions for the select clause, except where cannot include aggregates, while having often does.

This example is legal:

select title_id from titles where title_id like “PS%” having avg(price) > $2.0

But this example is not:

select title_id from titles where avg(price) > $20

How the having, group by, and where clauses interact

When you include the having, group by, and where clauses in a query, the sequence in which each clause affects the rows determines the final results:

  • The where clause excludes rows that do not meet its search conditions.
  • The group by clause collects the remaining rows into one group for each unique value in the group by expression.
  • Aggregate functions specified in the select list calculate summary values for each group.
  • The having clause excludes rows from the final results that do not meet its search conditions.

Sorting query results

The order by clause allows you to sort query results by one or more columns, up to 31.

How joins work

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 equijoins, 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

au_fname au_lname pub_name

——– ——– ——————–

Cheryl Carson Algodata Infosystems

Abraham Bennet Algodata Infosystems

(2 rows affected)

This earlier query is an example of an equijoin:

select * from authors, publishers

where authors.city = publishers.city

In the results of that statement, the city column appears twice. By definition, the results of an equijoin contain two identical columns. Because there is usually no point in repeating the same information, one of these columns can be eliminated by restating the query. The result is called a natural join.

example of a natural join is:

select au_fname, au_lname, pub_name

from authors, publishers

where authors.city = publishers.city

Self-joins and correlation names

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.

Example

select au1.au_fname, au1.au_lname,

au2.au_fname, au2.au_lname

from authors au1, authors au2

where au1.city = "Oakland" and au2.city = "Oakland"

and au1.state = "CA" and au2.state = "CA"

and au1.postalcode = au2.postalcode

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 title author

tables on their title_id column:

select *

from titles, titleauthor

where titles.title_id *= titleauthor.title_id

Inner joins

In which the joined table includes only the rows of the inner and outer tables that meet the conditions of the on clause.

Outer joins, in which the joined table includes all the rows from the outer table whether or not they meet the conditions of the on clause.

This query finds the names of all the publishers who publish business books:

select pub_name

from publishers

where exists

(select *

from titles

where pub_id = publishers.pub_id

and type = "business")

pub_name to find the names of publishers who do not publish business books, the query is:

select pub_name

from publishers

where not exists

(select *

from titles

where pub_id = publishers.pub_id

and type = "business")

Using and Creating Datatype

Datatype by category Synonyms Range Bytes of storage

Integer Type

Datatype Stores Bytes of storage
bigint Whole numbers between -263 and 263 – 1 (from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, inclusive. 8
int[eger] Whole numbers between-231 and 231 – 1 (-2,147,483,648 and 2,147,483,647), inclusive. 4
smallint Whole numbers between -215 and 215 -1 (-32,768 and 32,767), inclusive. 2
tinyint Whole numbers between 0 and 255, inclusive. (Negative numbers are not permitted.) 1
unsigned bigint Whole numbers between 0 and 18,446,744,073,709,551,615 8
unsigned int Whole numbers between 0 and 4,294,967,295 4
unsigned smallint Whole numbers between 0 and 65,535 2

Approximate numeric

Datatype Bytes of storage
float[(default precision)] 4 for default precision < 168 for default precision >= 16
double precision 8
real 4

Money

Datatype Range Bytes of storage
money Monetary values between +922,337,203,685,477.5807 and -922,337,203,685,477.5808 8
smallmoney Monetary values between +214,748.3647 and -214,748.3648 4

Date/time

Datatype Range Bytes of storage
datetime January 1, 1753 through December 31, 9999 8
smalldatetime January 1, 1900 through June 6, 2079 4
date January 1, 0001 to December 31, 9999 4
time 12:00:00 AM to 11:59:59:999 PM 4

Character

Datatype Stores Bytes of storage
char(n) Character n
unichar(n) Unicode character n*@@unicharsize (@@unicharsize equals 2)
nchar(n) National character n * @@ncharsize
varchar(n) Character varying Actual number of characters entered
univarchar(n) Unicode character varying Actual number of characters * @@unicharsize
nvarchar(n) National character varying Actual number of characters * @@ncharsize

Creating user-defined datatypes

Here is the syntax for sp_addtype:

sp_addtype datatypename,

phystype [ (length) | (precision [, scale] ) ][, “identity” |nulltype]

Here is how tid was defined:

sp_addtype tid, “char(6)”, “not null”

Dropping a user-defined datatype

To drop a user-defined datatype, execute sp_droptype:

sp_droptype typename

Note You cannot drop a datatype that is already in use in any table.

Getting information about datatypes

sp_help money

What are the databases and tables?

Adaptive Server database objects include:

  1. Tables
  2. Rules
  3. Defaults
  4. Stored procedures
  5. Triggers
  6. Views
  7. Referential integrity constraints
  8. Check integrity constraints

Single database can have 2 billion tables as per ASE 11.5

Indian Quotation Amount International Quotation
1 Lakh 100,000.00 100 Thousands
10 Lakhs 1,000,000.00 1 Million
1 Crore 10,000,000.00 10 Million
10 Crores 100,000,000.00 100 Million
100 Crores 1,000,000,000.00 1 Billion

Temporary table

You can 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 are created in the tempdb database.

There are two kinds of temporary tables:

Tables that can be shared among Adaptive Server sessions

create table tempdb..authors

(au_id char(11))

drop table tempdb..authors

Tables that are accessible only by the current Adaptive Server session or procedure

create table #authors

(au_id char (11))

exists until the current session or procedure ends, or owner drops it.

Important Rules on temporary tables

  1. cannot create views on these tables.
  2.  cannot associate triggers with these tables.
  3. cannot tell which session or procedure has created these tables.

*NOTE: These restrictions do not apply to shareable, temporary tables created in tempdb.

IDENTITY

You define an IDENTITY column by specifying the keyword identity,

Example


(sale_id numeric(5,0) identity,create table sales_daily

stor_id char(4) not null)

Column ‘sale_id’ will be maintained by sybase server. Similar to MySQL auto_increment feature. A row can be uniquely identified by this key.

*NOTE:- Avoid using IDENTITY in production since some time server enters a value which might not be in order.

By default, Adaptive Server begins numbering rows with the value 1, and continues numbering rows consecutively as they are added. Some activities, such as manual insertions, deletions, or transaction rollbacks, and server shutdowns or failures, can create gaps in IDENTITY column values.

Adaptive Server provides several methods of controlling identity gaps described in “Managing identity gaps in tables”

The IDENTITY column contains a unique ID number, generated by Adaptive Server, for each row in a table.

Uniq VS Primary key

Both constraints create unique indexes to enforce this data integrity. However, primary key constraints are more restrictive than unique constraints. Columns with primary key constraints cannot contain a NULL value.

Definition of unique constraints in the SQL standards specifies that the column definition shall not allow null values.

sp_primarykey, sp_foreignkey, and sp_commonkey define the logical relationship of keys (in the syskeys table) for table columns, which you enforce by creating indexes and triggers

There can be only one clustered index on a table,

The maximum number of references allowed for a table is 192.

Using sp_helpconstraint to find a table’s constraint information

A schema is a collection of objects owned by a particular user, and the permissions associated with those objects.

example:


create schema authorization dbo

create table list1

(col_a char(10) primary key,

col_b char(10) null

references list2(col_A))

create table list2

(col_A char(10) primary key,

col_B char(10) null

references list1(col_a))

You can declare a check constraint to limit the values users insert into a column in a table.

example:


create table my_new_publishers

(pub_id char(4)

check (pub_id in ("1389", "0736", "0877",

"1622", "1756")

or pub_id like "99[0-9][0-9]"),

pub_name varchar(40),

city varchar(20),

state char(2))

Create table from existing table using “select into”

example:


1> select pname, sname into friends_name from froends_etc

2> go

(0 rows affected)

Execution Time (ms.): 1000 Clock Time (ms.): 1000

View Limitation

There are a few restrictions on the select statements in a view definition:

• You cannot include order by or compute clauses.

• You cannot include the into keyword.

• You cannot reference a temporary table.

For example, to rename titleview to bookview, enter:

sp_rename titleview, bookview

Follow these conventions when renaming views:

To display the text of the create view statement, execute sp_helptext

example:

sp_helptext hiprice

———-

1

(1 row affected)

text

——————————————–


create view hiprice

as select *

from titles

where price > $15 and advance > $5000

(1 row affected, return status = 0)

sp_depends lists all the objects that the view or table references in the

current database, and all the objects that reference that view or table.

example:

sp_depends titles

Things inside the current database that reference the object.

object type

————- —————————

dbo.history_proc stored procedure

dbo.title_proc stored procedure

dbo.titleid_proc stored procedure

dbo.deltitle trigger

dbo.totalsales_trig trigger

dbo.accounts view

dbo.bookview view

dbo.categories view

dbo.hiprice view

dbo.multitable_view view

dbo.titleview view

(return status = 0)

sp_tables lists all views in a database when used in the following format:

sp_tables @table_type = “’VIEW’”

Transact SQL: numeric functions

Mathematic Functions

abs            absolute value             abs(-5) = 5
ceiling        next highest int           ceiling(5.3) = 6
floor          next lowest int            floor(5.7) = 5
power          exponential                power(2,8)=256
rand           random number              rand=0.315378 for example
round          round to n places          round(5.6,0)=6   round(5.66,1)=5.7
sign           -1,0,1                     sign(-5)=-1

Transact SQL: string functions

plus sign (+)      concatenation             'one'+'two'='onetwo'
ascii              char->ascii value         ascii('A')=65
char               ascii->char               char(65)='A'
charindex          similar to instring       charindex('two','onetwothree')=4
char_length        length of string          charlength('onetwo')=6
lower              lower case                lower('ONE')='one'
ltrim              trim left blanks          ltrim('   one')='one'
replicate          repeat chars              replicate('-',8)='--------'
reverse            flip string               reverse('salad')='dalas'
right              right chunk of string     right('Chicago',2)='go'
rtrim              trim right blanks         rtrim('test   ')='test'
space              spaces                    space(5)='     '
str                float->char               str(5.6,12,2)='        5.60'
stuff              insert chars within str   stuff('onetwothree',4,3,'-----')='one-----three'
substring          get piece of string       substring('sybase',1,2)='sy'
upper              upper case                upper('one')='ONE'

Transact SQL: misc functions

convert          convert between data types      convert(float,'5.50')=5.50
suser_name()     current login id
getdate()        current date

Transact SQL: date/time functions

datepart*        get part of a date         datepart(MM,'10/21/98')=10
dateadd*         manipulate a date          dateadd(DD,10,'10/21/98')= 10/31/98
getdate          todays date and time       getdate()=Nov 16 1998-2000  7:27PM

* date parts are MM,DD,YY,HH,MI,SS,MS

Its a QnA time

I will be discussing 36 most probable interview questioner one could expect for Sybase SQL developer or similar role

1)

What is stored procedure? How many maximum parameter can pass through it?

It is a db objects that exists independly of the table ,Which contain a set of query. Maximum paraperters pass through it is 255

2)
what is Advantage of stored procedure?
faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency

3)
what is Trigger? how many trigger can a table have and what are?
It is internal part of a statement that fired when insert,update,delete operation taken place.Three trigger,Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
4)
What is magic table?
The table that exists within a trigger named as Inserted and deleted are Magic table
5)
what is view? how many table can join for creating view?
It is logical way of looking at physical data located in a table, Upto 16

6)
how will you add a column and drop a column?
Alter table table name [add][drop] column name
7)
explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a cursor

8)
compare WHERE clause and HAVING clause?
Where clause cannot have Aggeregate function ,and it select the row before the group by function .having select the row after the group by
9)
What is dead lock?
A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources.

10)
What is sp_dboption?
It is used to enable or disable db option for your database

11)
What is CURSOR?
It is a pointer , use for row by row operation
12)
Why we should go for Deallocating the cursors?
When we Deallocate the cursor so that server will clear the memory space occupied by the cursor we can use that space for some other action

13)
How many index can have for a table?
250

14)
What is Clustered and Nonclustered index?
When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater
15)
How do I get the Duplicate rows from a table?
select * from table group by column having count(*)>1
16)
While creating index I forgot to tell what type what will happen?
nonclustered index

17)
Compare Primary key and Unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

18)
What is BCP?and Types?
It is used for import and export the values from table to os file and vice-ver. Fast bcp and Slow bcp

19)
Compare left and Right Outer Join?
For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL

21)
What is Hotspot?
Whan a multipe processes attempt to modify a same page in table

22)
How do I force the lock?
Using Holdlock

23)
What is Isolation levels and explain them?
The categories of locking behavior within transaction that are defined by ANSI,Level are 0,1,2,3

24)
how to get Yesterday’s date?
dataadd(dd,-1,getdate())

25)
What is –I option in Sybase tools?
It is the name of the interface file to use when trying to find a server to connect to

26)
How do u copy the output results into a file?
isql -Usa -P -S -oos_file_name

27)
If I want current database name?
db_name()

28)
What are the different types of lock in Sybase?
Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.
Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.

29)
List out all System procedure in your server?
select name from sysobjects where type=’P’
30)
There’s a performance issue What will be your very first step towards the solution?
First you need to identify which query is creating the problem and than to identify where the problem lies in that query. Query that is taking maximum time is the first you want to consider for the optimization. To check which query is taking maximum time to execute place getdate() both before and after the query. Identity the type of the query whether it is a data look-up query, or data modification query.
31)
What is @@rowcount?
returns the no of rows affected by the preceding query

32)
How do you check whether the rollback was correct?
By checking the @@trancount value before rollback statement execute

33)
What is Roll Forward and Roll Back?
Roll forward : commited transaction not written to data area are rolled forward into the data.
Roll back:uncommited transaction rollback all data modification done with in transaction are reversed

34)
Compare Delete and Truncate?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

35)
How many columns can table have?
250

36)
How will you find second maximum value in a table?
select max(column1) from table where column1<(select max(column1) from table)

I strongly recommend to all my users that for further reading and reference of Sybase ASE 12.5 please refer to http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_12.5/title.htm