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

If you are tasked with installing and configuring the database, here is a list of steps to be taken:

  1. 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).
  2. Try to connect to the server with the isql command line tool.

    By default, the sa user is defined with a blank password. It is strongly recommended that you set the sa password after installation:

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

  3. Define server's default character set. With Sybase ASE, the db character set cannot be specified at the database level, it is defined at the server level.

    After starting the server, you must identify what server character set you want to use (for example, utf8) and re-configure the server. With Sybase ASE 15.5, this must be done with the charset command line 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.

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

    Commands:

     use master
     go
     disk init
          name = "devname",
          physname = "filename",
          size = devsize ...
     go
     create database dbname
        on devname
        with ...
     go 
  5. Leave the default transaction mode ( unchained mode), to force explicit transaction start and end commands. See the set chained command for more details.
  6. 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.

    Commands:

     master..sp_dboption dbname, 'allow nulls by default', true
     go 
  7. 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
  8. For development purpose, you may consider to set the next database option to truncate the transaction log when a checkpoint occurs, otherwise you will have to dump the transaction log when it is full: Commands:
     master..sp_dboption dbname, 'trunc log on chkpt', true
     go 
  9. Create a new login dedicated to your application: the application administrator . Assign the new created database as default database for this user.

    Commands:

     use dbname
     go
     sp_addlogin 'username', 'password', dbname, ... options ...
     go 
  10. 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"...

    Commands:

     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.

  11. 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.
  12. 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").