Getting Started with Sybase

We have set up the Sybase database management system for your use in CS 145. The Sybase system uses a client-server model. An SQL server is maintained that is the only program allowed to read and write from the database. We will use a client program called sqsh (pronounced ``squish''). sqsh is an SQL interpreter that allows the user to type in SQL commands and have them sent off to the SQL server. Sybase also provides the user with all of the fundamental procedures necessary to write his or her own client programs. We'll use these procedures later on in the course.

Logging on

You can access the Sybase database system from any of the Sun SPARCstations (elaine's, adelbert's, and epic's) located on the second floor of Sweet Hall. Note that it is not required for you to be physically at the machines; you can log in remotely as you would for any other campus machine such as Leland or Xenon. Your Leland account name and password are also your account name and password for the SPARCstations. Sybase is currently not installed on the DECstations (amy's). You may use the DECstations to, e.g., edit files, but anything related to using Sybase will not work on the DECstations.

Setting up

You should add the Sybase directory to your path and set up the SYBASE and DSQUERY environment variables. We suggest you do so by adding the following lines to your .login file in your home directory.

For csh:

  set path = ($path ~sybase/bin)
  setenv SYBASE ~sybase
  setenv DSQUERY ATHENA

For sh:

  PATH = ${PATH}: ${SYBASE}/bin
  export PATH
  SYBASE = /usr/pubsw/package/Licensed/sybase10.0.3/@sys/apps/sybase
  export SYBASE
  DSQUERY = ATHENA
  export DSQUERY

These lines will add to your path the directory containing all of the Sybase executable files that you need (such as sqsh, sybooks, etc.) as well as setting you up to access our course Sybase server which is called ``athena''.

Starting `sqsh`

To start the sqsh program, type the following at the Unix prompt:

  sqsh [-U <username>] [-P <password>]

Your username should be the same as your Leland account name and your initial password will be cs145pw. Please use the sp_password procedure (explained below) to change your password as soon as you log in for the first time. Once in sqsh, you should receive a prompt that looks like ``1>''. You are automatically put inside a database we have set up for this class. The database you have been assigned to will be called either cs145a, cs145b, cs145c, or cs145d. You may create new tables in this database, but you are not allowed to create another database. Most people's accounts should be set up by the time you receive this handout. If you cannot log in by Wednesday evening October 9, please contact us by sending a message to cs145ta@cs.stanford.edu.

Commands in `sqsh`

sqsh accepts fairly standard SQL commands. A command can stretch across multiple lines. When you are done typing in the command, type ``go'' on a new line (at the sqsh prompt) to execute it. To quit sqsh, type ``exit'' or ``quit'' at the sqsh prompt.

Information on all Sybase commands and procedures can be found by typing sybooks at the Unix prompt. This command brings up the on-line documentation. You must be running X-Windows to view the sybooks documentation. If you cannot use sybooks, you can instead use the Sybase ``man'' command, called syman, from the Unix prompt. Like Unix ``man'', syman must be followed by the name of the command you are interested in. (As of press time we are having some problems with syman, but we expect the problems to be resolved by the time you first log into Sybase.) Information on sqsh is found by typing man sqsh at the Unix prompt. Hard-copy Sybase documentation is available at Sweet Hall.

System-defined procedures

The Sybase system comes with some predefined stored procedures that you will find useful when working with Sybase. The system procedures all follow the naming convention that they are prefixed with ``sp''. The most useful ones are listed below:

sp_help [<object>]: Provides information about a database object. For a table or view, it lists the schema. If <object> is a stored procedure, the parameters of the stored procedure are listed, with their datatypes. If no object is given, all database objects are listed.
sp_helpdb [<database>]: Provides information about a database.
sp_helprotect [<name>]: Lists protections on a database object or user.
sp_helptext <object>: Prints the creation text for a stored procedure or view.
sp_password <old>,<new>: Changes your sqsh password from <old> to <new>.
sp_rename <old>,<new>: Renames a database object from <old> to <new>.
sp_who: Reports information on all users and applications that have connected to your SQL server process.

Listing your objects

You can list the objects (tables, views, etc.) that you have created with the following SQL query:

  select name from sysobjects
  where uid =
  (select uid from sysusers
   where name = '<your user name>')

More on `sqsh`

There are two basic ways to interact with sqsh:

One shot: You have a file <foo> that contains commands (separated by go's, don't forget) and you want sqsh to execute <foo> and exit. At the Unix prompt, type:
sqsh -P <password> < <foo>
or
sqsh < <foo>
In the second case you will be asked to supply your password in a command line. Note that we have omitted the ``-U <username>'' option here and in the example below--it may be omitted when your Sybase username is the same as your Unix login name, as should be the case in your setup.
The results of execution will be printed out and can be redirected to a file.
Interactive: To get into sqsh, type (at the Unix prompt):
sqsh -P <password>
or
sqsh
In the second case you will be asked to provide your password. Once you are inside sqsh, you type in your commands followed by go for execution. You might want to read in and execute a command file <foo>. You do this by typing (at the sqsh prompt):
```
  \buf-load <foo>
  \go
```
Note that in this case, <foo> can contain at most one command and no go's.

Example

In this example, we will show you how to use sqsh to create two relations, look at their schemas, and delete them. The user is known to Sybase as user mary with password MYPW. The two relations with schemas:

  PATIENT(SSN:char(11), Name:char(20), birthdate:char(20))
  NURSE(EmpID:int, SSN:char(11), Name:char(20))

are defined in the file dbcreate. The file dbdrop contains a single command that destroys the NURSE relation. The file listobj contains a single command that lists the objects created by the user.

elaine14:~> cat dbcreate
create table PATIENT
   (SSN char(11),
    Name char(20),
    birthdate char(20))
\go
create table NURSE
   (EmpID int,
    SSN char(11),
    Name char(20))
\go
elaine14:~> cat dbdrop
drop table NURSE
elaine14:~> cat listobj
select name from sysobjects where uid =
(select uid from sysusers where name = 'mary')
elaine14:~> sqsh < dbcreate
Password:
elaine14:~> sqsh -P MYPW
sqsh-1.2 Copyright (C) 1995, 1996 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> select name from sysobjects where uid =
2> (select uid from sysusers where name = 'mary')
3> \go
 name                           
 ------------------------------ 
 NURSE                          
 PATIENT                        

(2 rows affected)
1> sp_help PATIENT
2> \go
 Name                           Owner                          Type                   
 ------------------------------ ------------------------------ ---------------------- 
 PATIENT                        mary                           user table             

 Data_located_on_segment        When_created               
 ------------------------------ -------------------------- 
 default                               Oct  4 1996  5:08AM 

 Column_name     Type            Length Prec Scale Nulls Default_name    Rule_name       Identity 
 --------------- --------------- ------ ---- ----- ----- --------------- --------------- -------- 
 SSN             char                11 NULL  NULL     0 NULL            NULL                   0 
 Name            char                20 NULL  NULL     0 NULL            NULL                   0 
 birthdate       char                20 NULL  NULL     0 NULL            NULL                   0 
Object does not have any indexes.
No defined keys for this object.
Object is not partitioned.

(return status = 0)
1> sp_help
2> NURSE
3> \go
 Name                           Owner                          Type                   
 ------------------------------ ------------------------------ ---------------------- 
 NURSE                          mary                           user table             

 Data_located_on_segment        When_created               
 ------------------------------ -------------------------- 
 default                               Oct  4 1996  5:08AM 

 Column_name     Type            Length Prec Scale Nulls Default_name    Rule_name       Identity 
 --------------- --------------- ------ ---- ----- ----- --------------- --------------- -------- 
 EmpID           int                  4 NULL  NULL     0 NULL            NULL                   0 
 SSN             char                11 NULL  NULL     0 NULL            NULL                   0 
 Name            char                20 NULL  NULL     0 NULL            NULL                   0 
Object does not have any indexes.
No defined keys for this object.
Object is not partitioned.

(return status = 0)
1> drop table PATIENT
2> \go
1> \buf-load listobj
1> select name from sysobjects where uid =
2> (select uid from sysusers where name = 'mary')
3> \go
 name                           
 ------------------------------ 
 NURSE                          

(1 row affected)
1> \buf-load dbdrop
1> drop table NURSE
2> \go
1> \buf-load listobj
1> select name from sysobjects where uid =
2> (select uid from sysusers where name = 'mary')
3> \go
 name                           
 ------------------------------ 

(0 rows affected)
1> \quit
elaine14:~>

Creating a typescript to turn in

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. (Use man to find out about the script command.) sqsh gives you a third way to record interactions with the Sybase command interpreter. Suppose you have a command file foo (containing SQL commands separated by go's). You can use the standard Unix output redirection, such as:

  sqsh -U mary -P MYPW < foo > foo.typescript

However, the file foo.typescript produced contains only what sqsh prints out--whatever sqsh received as input is not recorded. For your programming assignments, showing the actual input that caused some output to be printed is important. For that, sqsh provides the option -e that will force it to echo the input:

  sqsh -U mary -P MYPW -e < foo > foo.typescript

The TAs of CS145, cs145ta@cs.stanford.edu, last modified: 10/08/96