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

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 ansinull 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

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

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.

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 titleauthor

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.

SubQueries

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 Datatypes

Datatypes by

category Synonyms Range Bytes of storage

Exact numeric: integers

tinyint 0 to 255 (Negative numbers are not

permitted.)

1

smallint 215 -1 (32,767) to -215 (-32,768) 2

int integer 231 -1 (2,147,483,647) to

-231 (-2,147,483,648

4

Exact numeric: decimals

numeric (p, s) 1038 -1 to -1038 2 to 17

decimal (p, s) dec 1038 -1 to -1038 2 to 17

Approximate numeric

float (precision) machine dependent 4 for default precision < 16,

8 for default precision >= 16

double precision machine dependent 8

real machine dependent 4

Money

smallmoney 214,748.3647 to -214,748.3648 4

money 922,337,203,685,477.5807 to

-922,337,203,685,477.5808

8

Date/time

smalldatetime January 1, 1900 to June 6, 2079 4

datetime January 1, 1753 to December 31,

9999

8

Character

char(n) character pagesize n

varchar(n) character varying,

char varying

pagesize actual entry length

unichar Unicode character pagesize n * @@unicharsize

(@@unicharsize equals 2)

univarchar Unicode character

varying, char varying

pagesize actual number of characters *

@@unicharsize

nchar(n) national character,

national char

pagesize n * @@ncharsize

nvarchar(n) nchar varying,

national char varying,

national character

varying

pagesize @@ncharsize * number of

characters

text 231 -1 (2,147,483,647) bytes or fewer 0 when uninitialized;

multiple of 2K after

initialization

Binary

binary(n) pagesize n

varbinary(n) pagesize actual entry length

image 231 -1 (2,147,483,647) bytes or fewer 0 when uninitialized;

multiple of 2K after

initialization

Bit

bit 0 or 1 1 (one byte holds up to 8 bit

columns))

1> select name, hierarchy from systypes order by hierarchy

2> go

name hierarchy

—————————— ———

floatn 1

float 2

datetimn 3

datetime 4

real 5

numericn 6

numeric 7

decimaln 8

decimal 9

moneyn 10

money 11

smallmoney 12

smalldatetime 13

intn 14

int 15

smallint 16

tinyint 17

bit 18

univarchar 19

unichar 20

sysname 22

varchar 22

nvarchar 22

update_id 22

char 23

nchar 23

timestamp 24

varbinary 24

binary 25

text 26

image 27

date 28

time 29

daten 30

timen 31

extended type 99

(37 rows affected)

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 databases and tables?

Adaptive Server database objects include:

• Tables

• Rules

• Defaults

• Stored procedures

• Triggers

• Views

• Referential integrity constraints

• 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

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.

rules on temporary tables

• You cannot create views on these tables.

• You cannot associate triggers with these tables.

• You 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

create table sales_daily

(sale_id numeric(5,0) identity,

stor_id char(4) not null)

Column ’sale_id’ will be maintained by sybase server. Similar to MySQL auto_increment feture. 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’”