Sybase Most Used Commands

comment and continuation characters

comments
-- (Double hyphen) The database server ignores any remaining characters on the line. This is the SQL92 comment indicator.

/* comment */
/* xxx */ (Slash-asterisk) Any characters between the two comment markers are ignored. The two comment markers might be on the same or different lines. Comments indicated in this style can be nested. This style of commenting is also called C-style comments.

Line continuation character '\'

exec( ' print "This is a pretty long string, but fortunately, \
we can use two lines for it !" ' )

isql

cd $SYBASE/$SYBASE_ASE/scripts

-i: input_script_file
-o: output_file 
-e: echo the T-SQL commands to the same output file

isql -Usa -PSecr3t -SSYBASE -iinstallpubs2 -e -oerrors.out
isql -S SYBASERAYS_SERVER_1 -U my_user -P my_pass -i /opt/sybase/ASE-15_0/scripts/installdbccdb

isql -Usa -PSecr3t -SSYBASE
isql -Usybtest -PSomePass -SSYBASE
isql -Usa -P -SSERVER
1>exit

ddlgen

-- A Java-based tool that generates definitions for server- and database-level objects in Adaptive Server. either an sa_role or sso_role is needed to generate DDL
ddlgen 

$SYBROOT should be to your $SYBASE 
-D: a database;
-T: type of database object: 
       DB database 
       I index 
       U table
       V view 
       KC key-constraint
       P procedure 
       TR trigger 
       L login  
       USR user 
       RO role

-N: a wildcard for matchin for object Names. 

ddlgen -Usa -Ppswd -Smyserver:5001 -Dmydb
ddlgen -Usa -Ppswd -Smyserver:5000 -Dtempdb -TU -N#%

Most used global variables, functions, and views

select @@version
go

select @@servername, db_name()
go

select * from master..syslisteners;
go

select name, type, crdate from sysobjects;
go

select * sysservers;
go

select id, csid, name, description from master..syscharsets where type >= 2000;
go

Change Database Owner

use pubs2
go
exec sp_changedbowner sybtest
go
sp_changedbowner loginame [, true ]
sp_changedbowner my_user_1

Change sa password for the master database

exec sp_password NULL, "Secr3t"
go
sybase ase howto

start and shutdown db instance

$SYBASE/$SYBASE_ASE/install/showserver (Unix only) - Displays running servers on the current host; 

startserver:
which SYBASE.sh
. SYBASE.sh
echo $SYBABE
cd $SYBASE/SYBASE_ASE/install 
startserver -f RUN_servername
startserver -f RUN_SYB_BACKUP

shutdown :
isql -Usa -PSecr3t -SSYBASE
1> shutdown
2> go

sp_help

sp_version

-- sp_help: the command displays all objects in the current database
sp_help
go

sp_help table_name

sp_helptext objectname

sp_helpdb
go
sp_helpdb db1
go

sp_helpserver [server name]; 
go

sp_spaceused
go

Sybase session or connection

variable: SESSION_USER

# show spid and other session info
sp_who
go

kill <spid>
go

-- syb_quit () Causes ASE to terminate the current session.
select syb_quit().

tempdb_id ( )
tempdb_id ( [ spid ] )
Returns the database ID of the specified session's temporary database; 
without spid,for the current session.

sp_configure

-- sp_configure for all parameters
sp_configure
go
sp_configure 'disable disk mirroring', 0
go
sp_configure 'nondefault' for non-default parameters
go
sp_configure 'enable xml', { 0 | 1 } (dynamic)
go
sp_configure 'quoted identifier enhancements', { 0 | 1 }
go
sp_configure 'default character set id', charset_id (static)
go
sp_configure 'disable character set conversion', { 0 | 1 } (static)
go

Interface File

# Interface File
$SYBASE/interfaces     unix
%SYBASE%\INI\SQL.INI   windows

bcp: logical export

bcp

backup and restore, recover

dump database @dbname to @fname2
dump tran @dbname to @fname1

-- backup db:
dump database pubs2 to "/mnt/backup/pubs2.bkp"
go
dump database db1 to "/dev/SYBASERAYS_SERVER_1/dumpfiles/dump1"
go

-- backup transaction log:
dump transaction database to "/mnt/backup/dbtrandump2003_08_27_T23_32.bkp"
go

-- simply truncate transaction log 
1> dump transaction pubs2 with truncate_only
2> go

-- restore a user db
# load database
load database 
# load transaction log, incremental restore
load transaction
# turn the database online
online database

load database db1 from "/dev/SYBASERAYS_SERVER_1/dumpfiles/dump1"
go
online database db1
go

Add New User, Modify Password, and Add Permission

exec sp_addlogin "sybtestuser", "SomePass"
go
-- also make defdb as a default login database
sp_modifylogin sybtestuser, "defaultdb", "SomePass2"
go
create login
sp_addlogin
sp_adduser
sp_addalias

sp_displaylogin [ 'login_name' ]

sp_password 
alter login with passwd

sp_addlogin loginame, passwd [, defdb] [, deflanguage [, fullname]]]
sp_addlogin user1, password, @defdb = db1
go
sp_addlogin vyasab, Sunday01, NULL, NULL, "Abhisek Vyas"
go
sp_addlogin user2, password, db1, NULL, "user2"
go
exec sp_addlogin "sybtest", "SomePass"
go
sp_addlogin 'silveruser','silver','silvermaster'
go

sp_displaylogin [ 'login_name' ]
sp_modifylogin login_name, attribute, 'value'

drop login list_of_login_names [ with override ]
sp_droplogin login_name 

use silvermaster
go
sp_role 'grant','sa_role','silveruser'
go
sp_changedbowner silveruser
go
/* create a developer profile */
sp_addlogin 'jsmith','yankees','silvermaster'
go
use silvermaster
go
sp_addalias 'jsmith','dbo'
go
/* change jsmith password, note how SA/SSO pwd is required here */
sp_password 'sa_pwd','dodgers','jsmith'
go

# -------------------------
Every user is a member of the group "public" 
and can also be a member of one other group
Users remain in "public" even when they belong to another group

sp_addgroup grpname
sp_addgroup group1
go
# ------------------------
grant role role_granted [{, role_granted}...]to grantee [{, grantee}...]

grant role my_role_1 to my_user_1
go
grant role sso_role to my_role_role
go

revoke role role_name [{, role_name}...]from grantee [{, grantee}...]

# ------------------------

grant all on invoice to jsmith
go
grant select on invoice to wriker
go
grant update on invoice to wriker
go
revoke select on invoice from wriker
go
grant execute on proc_upd_invoice to jsmith
go

-- list privileges for each user

select u.name,v.name
from sysprotects p, master.dbo.spt_values v, sysusers u
where p.uid=u.uid
and p.action=v.number
and p.protecttype=1
and v.type = 'T'
and u.name!= 'dbo'
go


-- generate revoke commands for non-typical access

select 'revoke', name from sysobjects where id in
(
select p.id
from sysprotects p, master.dbo.spt_values v, sysusers u
where p.uid=u.uid
and p.action=v.number
and p.protecttype=1
and v.type = 'T'
and u.name!= 'dbo'
and v.name not in ('Delete','Execute','Insert','References','Select','Update')
)
go 

Sybase ASE DDL -- table

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

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

create table employee (
emp_id integer not null,
salary money default 0,
hire_dt datetime default getdate()
)
go

/* Future inserts will reside on data_seg2 */
sp_placeobject 'data_seg2','invoice'
go

/* leave table where it is, but future allocations go to the new segment */
sp_placeobject new_seg , 'employee'
go

/* leave table where it is, but future allocations for the
text column (employee_notes) go to the new segment */

sp_placeobject new_seg , 'employee.temployee'
go

/* leave table where it is, but future allocations for the
text column (resume) go to the new segment */

sp_placeobject new_seg , 'student.tstudent'
go

ALTER TABLE employee REPLACE salary DEFAULT 49000
go

create default def_highsal as 55000
go
sp_bindefault def_highsal,'employee.salary'
go 

alter table employee add constraint emp_constr primary key(emp_id)
go

alter table invoice add constraint inv_constr unique nonclustered(cust_id,inv_date)
go

alter table invoice add constraint inv_fk_emp foreign key (sales_rep_id) references employee(emp_id)
go

alter table invoice_detail modify inv_type varchar(50) not null
go

alter table invoice_detail modify inv_type varchar(50) not null,inv_priority varchar(50) null
go

alter table employee add cell_no numeric(10) null
go

alter table employee drop constraint 'emp_dept_constr'
go

alter table charge_item replace price_overridable_ind default 0
go

/* change column name -- quotes are required */
sp_rename 'employee.dept',dept_name
go

alter table invoice partition 4
go

-- reorg and rebuild a table
reorg rebuild account_sales
go

Sybase ASE DDL -- index

create unique clustered index emp_idx on employee (emp_id)
go
create index emp_name_idx on employee (lname)
go

create unique clustered index pk_invoice_data on invoice_data with sorted_data on segment1
go

drop index 'employee.idx_employee'
go

create clustered index on employee (emp_id) on new_seg
go

# force to use a specific index in q query
select t1.emp_id, t1.emp_name, t2.status 
from employee t1 (index idx_emp), 
audit_flags t2
where t2.emp_id = t2.emp_id
go

index:
# pre v15
select rowcnt( doampg ) from sysindexes where indid < 2 and id = object_id( 'my_table' );

# after v15
select row_count( doampg ) from sysindexes where indid < 2 and id = object_id( 'my_table' );

extract ddl

-- find source code for stored procedure, function,views, triggers
sp_showtext
sp_helptext
defncopy utility
ddlgen utility

sp_help table name
ddlgen utility

Data Type

-- ---------------
data type storage range/length comments
----------------- -------- ------------------- ----------------------------
integer 4 +/- 2.1 billion
smallint 2 +/- 32768
tinyint 1 0 .. 255
float 4 storage req is machine dependant
real 4
double precision 8
smallmoney 4 +/- 214,748 4 decimal places
money 8 +/- 922 trillion 4 decimal places
decimal/numeric varies
decimal(9,0) 4
decimal(12,0) 5

char(n) n length <= 255
varchar(n) varies length <= 255 ( over 4000 allowed in ASE 12.5 )

text varies length up to 2 GB 16 bytes stored in record (default)
image varies length up to 2 GB 16 bytes stored in record (default)

datetime 8 1/1/1753 .. 12/31/9999 precision to 1/300 second
smalldatetime 4 1/1/1900 .. 6/6/2079 precision to minutes
timestamp 8 same as varbinary(8)

bit 1 0/1 up to 8 bit fields stored within 1 byte

binary(n) length n
varbinary(n) length n

Only numeric data types with a precision of zero can be used for an identity column.
-- ---------------------------------

distributed system

create proxy_table invoice_items at 'SERVERXXX01.dbxxx001.dbo.invoice_items'
go

init device and create database

disk init name = 'device19',physname = '/dev/md/rdsk/d19',vdevno = 6,size = 1024000

sp_helpdevice
select * from master..sysdevices;

select vdevno from master..sysdevices where status & 2=2;

disk init name = "test_disk",
physname = "/opt/sybase/data/test_disk.dat", size = "1M"
go

disk init 
name = "db1_dbccdb_data",physname = "/opt/sybase/data/db1_dbccdb_data.dat",size = "18M" 
go

disk init
name = "db1_dbccdb_log",physname = "/opt/sybase/data/db1_dbccdb_log.dat", size = "2M"
go

-- disk resize name = "device_name", size = additional_space
disk resize name= "test_dev", size = "4M"
go
sp_helpdevice testdev
go

disk mirror name = "device_name" ,mirror = "physicalname
go

disk mirror
name = "log1", mirror = "/opt/sybase/data/log1_mirror.dat"
go

create database db1
on test_disk = "10M"
go

alter database db1 log on log1
go

-- turn transaction log off
alter database xxx log off

create database db1
on test_disk = "10M"
log on log1
go

create database dbccdb
on db1_dbccdb_data
log on db1_dbccdb_log
go

-- drop database db1
-- go

Moving the transaction log to another device
use master
go
sp_dboption "db1", "single user", true
go
sp_logdevice database_name, devname
sp_dboption "db1", "single user", false
go

Syntax: sp_changedbowner loginame [, true ]
sp_changedbowner my_db_user
go

Run sp_plan_dbccdb in the master database 
to obtain recommendations for database size, 
devices, workspace sizes, cache size, and 
the number of worker processes for the target database

use master
go
sp_plan_dbccdb db1
go

select * from master..sysdevices;

T-SQL

set option isql_print_result_set=ALL
go
set option isql_show_multiple_result_sets=On
go

set flushmessage on
go
set nocount on
go

# quit a script
select syb_quit()

# find temp db name
select db_name(tempdb_id())
go

set quoted_identifier on

sp_configure 'quoted identifier enhancements', { 0 | 1 }

create table "This is my table!" ("My Column!" int, YourColumn int)
select [name] from [sysobjects] where [type] = 'U'

# Causes Adaptive Server to stop processing a query after a specified number of rows are affected 
Activated by: set rowcount N
Turned off by: set rowcount 0

set rowcount 10
go
select emp_name, salary from employee order by salary desc
go
set rowcount 0
go

dbcc

dbcc checkstorage

Performance Monitoring

# monitoring
sp_monitorconfig
MDA tables
sp_sysmon

Excution Plan

# sql execution plan
set showplan
set statistics plancost
sp_showplan
show_plan()

Gather Information For Tech Support

# gather information for tech support
sybdiag utility

most popular site

sybase iq

# SySAM: Sybase Software Asset Management -- License Management
sysam status
sysam status -a

sysadm reread

set SYBASE_LICENSE_FILE=29722@bos;29733@nyc;@sfo
lmutil lmborrow -status

# verify that SySAM is running
$SYBASE/$SYBASE_SYSAM/bin/lmutil lmstat -c

/bin/lmutil lmdiag -c ./licenses/SybaseIQ.lic

Migration tool: iqunload

local store: iqlsunload
iqunload is a command line utility for SAP Sybase IQ 12.6 and 12.7 database migration. 
iqunload re-creates the legacy catalog on the new database catalog in the current installation.
iqunload does not change SAP Sybase IQ data and temp dbspaces. 
The utility preserves all legacy database options and applies them to the new database. 
SAP Sybase IQ ignores any legacy options that no longer apply to the current version of the software. 

sp_iqcheckdb ('verify database') 

sybase ase

What_Is_Sybase_ASE 

notes

sybase iq:
http://wenku.baidu.com/search?word=Sybase&lm=0&od=0&fr=top_home


http://wenku.baidu.com/view/6583c3ea856a561252d36fa2.html?re=view

http://wenku.baidu.com/view/8931ae68a98271fe910ef939.html

http://wenku.baidu.com/view/58dd9e795acfa1c7aa00cc9f.html

# 4 dbs generated in sybase is installed: master model tempdb sybsystemprocs
master: dictionary views for this db instance
model: standard db template
tempdb: group by, order by, and #temp_tablename, tempdb..mytablename
sybsystemprocs: for sp_help, sp_configure, sp_helpdevice

sp_help
sp_configure
sp_helpdevice

-- ----------------------


isql -Usa -Pxxx
sp_helpdb
go
sp_helpdb my_dbname
go


$SYBASE/interfaces
$SYBASE/SYBASE.cfg

-- ----------------------

-- check server status
showserver

-- ----------------------

startserver -f RUN_SYBASE

startserver -f RUN_SYB_BACKUP

-- ---------------------

isql -Usa -P
shutdown SYB_BACKUP
go
shutdown
go

-- --------------------

$SYBASE/install/.log

-- --------------------

dump database master to "/usr/sybase/master.dup"
go

isql
sp_configure "allow updates", 1
go
begin tran
go
use master
go

sp_configure "allow updates", 0
go

sp_helpdb
go

sp_helpdb MBFEWKDB
go

sp_helpdevice
go

sp_helpdevice device_name
go

sp_dropdevice device_name


sp_addlogin login_name, passed [,defaultdb[,deflanguage[,fullname]]]
eg: sp_addlogin 'test', '123456'

sp_droplogin login_name
eg: sp_droplogin 'test'

sp_password old_passwd,new_passwd[,login_name]

sp_adduser login_name[,name_in_db[,grpname]]
# use sp_adduser to add a user to a specific database. 
# The user must already have an Adaptive Server login.
syntax: sp_adduser loginame [, name_in_db [, grpname]]

use db1
go
sp_adduser myuser1
go
sp_adduser user2, user2_db, group1
go

select * from sysusers;


use MBFEWKDB
go
sp_helpuser
go

sp_configure "number of user connections", 50
go

misc

Sybase Tools

isql
sybase central
toad 

rocket99