Install Sybase ASE and create a database - database configuration/design tasks

Install Sybase ASE software on your computer, with the Sybase client software. Make sure that the server is started and environment variables are properly set (On UNIX™, you will find SYBASE.* shell scripts to source in the installation directory).

Try to connect to the server with the isql command line tool.

If needed, change the password of the "sa" database administrator:

$ isql -S server_name -U sa
1> sp_password old_password, new_password
2> go
Password correctly set. 
(return status = 0)

Starting with Sybase ASE 15.7, the password of the sa user is defined at installation time.

Define server's default character set: You must identify what server character set you want to use (typically, utf8) and re-configure the server if needed. With Sybase ASE, the db character set cannot be specified at the database level, it is defined at the server level, typically during the installation. It is also possible to change the srever character set with the charset utility and with the sp_configure stored procedure. You have to shutdown the server, start a first time to have the server take the new character set into account and then restart a second time for use. See Sybase documentation for more details or more recent versions of Sybase ASE.

Make sure that you select a case-sensitive character set / sort order.

Note: Check the $SYBROOT/locales/locales.dat file, to make sure that your current locale (LANG/LC_ALL) is listed in the file. You may want to add the following lines for UTF-8 support, under the section of your operating system:

  locale = POSIX, us_english, utf8
  locale = en_US.utf8, us_english, utf8
; Windows only:
  locale = .fglutf8, us_english, utf8

Example:

$ export DSQUERY=servername
$ charset -Usa -P binary.srt utf8
Please enter sa's Password: 
Loading file 'binary.srt'.

Found a [sortorder] section.

This is Class-1 sort order.

Finished loading the Character Set Definition.

Finished loading file 'binary.srt'.

1 sort order loaded successfully

$ isql -Usa -P
1> sp_configure 'default sortorder id', 50, 'utf8'
2> go
3> shutdown
4> go
Server SHUTDOWN by request.
ASE is terminating this process.
.......

$ $SYBROOT/ASE_*/install/RUN_servername
... (server makes some initialization / setup and stops) ...

$ $SYBROOT/ASE_*/install/RUN_servername
...

Create a new Sybase database entity, with sufficient storage devices for data and transaction log.

Use either the Sybase Central, the Sybase Control Center GUI tool or use isql with SQL commands.

Connect to the server with the sa user.

First create database devices for data and transaction log. Define a transaction that can hold the biggest transaction your application can do to avoid administrative tasks to dump the log when the server hangs. When creating the database, use the new created database devices as database segments:

 use master
 go
 disk init
      name = "devname",
      physname = "filename",
      size = devsize ...
 go
 create database dbname
    on devname
    with ...
 go

Leave the default transaction mode ("unchained" mode), to force explicit transaction start and end commands. See the set chained command for more details.

The database allows NULLs by default when creating columns. This is controlled by the 'allow nulls by default' option. If this option is set to OFF, columns created without NULL or NOT NULL keywords are NOT NULL by default:

 master..sp_dboption dbname, 'allow nulls by default', true
 go

The database must allow Data Definition Language (DDL) statements in transaction blocks. To turn this on, use following commands:

 master..sp_dboption dbname, 'ddl in tran', true
 go
 checkpoint
 go

For development purpose, consider to set the database option to truncate the transaction log when a checkpoint occurs, otherwise you will have to dump the transaction log when it is full. Command to automatically truncate the transaction log on checkpoint:

 master..sp_dboption dbname, 'trunc log on chkpt', true
 go

Create a new login dedicated to your application: the application administrator. Assign the new created database as default database for this user:

 use dbname
 go
 sp_addlogin 'username', 'password', dbname, ... options ...
 go

Create a new database user linked to the new application administrator login: In Sybase Central, open to the "Databases" node, select "Users" and right-click "New" ...

 use dbname
 go
 sp_adduser 'username', 'group', ... options ...
 go

See documentation for more details about database users and privileges. You must create groups to make tables visible to all users.

If you plan to use SERIAL emulation based on triggers using a registration table, create the SERIALREG table. Create the triggers for each table using a SERIAL. See issue SERIAL data types for more details.

Create the application tables. Do not forget to convert Informix^® data types to Sybase ASE data types. See topic data type Conversion Table for more details. In order to make application tables visible to all users, make sure that all users are members of the group of the owner of the application tables. For more details, see ASE documentation ("Database object names and prefixes").