Install SAP 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 SAP® ASE software on your computer, with the SAP 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.

    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 SAP ASE 15.7, the password of the sa user is defined at installation time.

  3. 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 SAP 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 server 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 SAP documentation for more details or more recent versions of SAP 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
    ...
  4. Create a new SAP ASE database entity, with sufficient storage devices for data and transaction log.

    Use either the SAP Central, the SAP 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 
  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:
     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, consider setting 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 
  9. 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 
  10. Create a new database user linked to the new application administrator login:
    In SAP ASE 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.

  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.
    Convert Informix® data types to SAP 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 SAP ASE documentation ("Database object names and prefixes").