Getting Started with SAP/Sybase

Installation

I've successfully installed and used SAP/Sybase Adaptive Server Enterprise versions 11.0.3.3 - 16 on Linux.

You can download SAP/Sybase ASE from the downloads section of the Sybase site.

Modern versions of ASE (12.5.1 and newer) for Linux are distributed as tarballs. Installation and server initialization are done in a single step. For instructions, see Creating a Database below.

Versions 12.5 and older were distributed as RPM's. They must be installed before the server can be initialized.

To install them on an RPM-based linux system, simply use rpm -i.

For example, version 12.5:

rpm -i sybase-openclient-12.5.0.1ESD-1.i386.rpm \
sybase-common-12.5.0.1DE-1.i386.rpm \
sybase-ase-12.5.0.1ESD-1.i386.rpm

To install them on Debian Linux, install alien by running apt-get install alien then convert the RPM's to DEB's uisng alien --to-deb and install them them with dpkg.

For example, version 12.5:

alien --to-deb sybase-openclient-12.5.0.1ESD-1.i386.rpm
alien --to-deb sybase-common-12.5.0.1DE-1.i386.rpm
alien --to-deb sybase-ase-12.5.0.1ESD-1.i386.rpm
dpkg -i sybase-openclient-12.5.0.1ESD-1.i386.deb
dpkg -i sybase-common-12.5.0.1DE-1.i386.deb
dpkg -i sybase-ase-12.5.0.1ESD-1.i386.deb

Version 11.0.3.3 does not create a sybase user when the RPMS are installed. After installation, you must run the following commands as root.

export SYBASE=/opt/sybase
/opt/sybase/install/sybinstall.sh

Answer Yes or y to all questions. The script will create a sybase user and prompt you for a password.

Creating a Database

To create a database, the server must first be initialized.

If you are installing ASE 15.0.2 or older, check the LANG environment variable first and set it to something simple like en_US as opposed to en_US.iso885915.

The instructions for initializing each version are very different.

See Initializing a 15.7 server, Initializing a 15.5 server, Initializing a 15.0.2 server, Initializing a 12.5.2 server, Initializing a 12.5.1 server, Initializing a 12.5 server, Initializing an 11.9.2 server and Initializing an 11.0.3.3 server.

Once you have initialized the server, you should start the server. From this point on, the database creation process is the same for all versions.

At this point, you should set up your environment. The SAP/Sybase client is called isql. Note that unixODBC and Interbase/Firebird have a client called isql as well which could be installed in /usr/bin, /usr/local/bin or some other path. If you have unixODBC or Interbase/Firebird installed, you'll have to decide which client you want the isql command to run. If you want it to run the SAP/Sybase client, follow the instructions below. Otherwise, you'll have to run the SAP/Sybase isql client by its full pathname which could be /opt/sybase/OCS-15_0/bin/isql, /opt/sybase/OCS/bin/isql, /opt/sybase-12.5/OCS/bin/isql, /opt/sybase-11.9.2/bin/isql or some other path.

In the configuration script for your shell, add the path to the SAP/Sybase isql client to your PATH environment variable as follows. In these examples, /opt/sybase/OCS/bin is used, but your SAP/Sybase isql client may be installed elsewhere.

For Bourne shells:

PATH=$PATH:/opt/sybase/OCS/bin
export PATH

For C-shells:

setenv PATH ${PATH}:/opt/sybase/OCS/bin

Now log out and log back in and you can use the isql client to create a database.

The initialization process should have created a server named LOCALHOST (or localhost for version 11.9.2 or older) with an administrative user named sa. Versions 15.7 and newer allow you to set the password for the sa user during installation. In versions 15.5 and older, the user is created with no password.

If you need to set the administrative user's password, log into the database with the following command. When it prompts you for a password, just press return.

For version 12.5 or newer:

isql -U sa -S LOCALHOST

For version 11.9.2 or older:

isql -U sa -S localhost

At the prompt, run the following stored procedure to give sa the password sapassword.

sp_password null,"sapassword","sa"
go

Now logout.

quit

Log back in using the following command. When it promts you for a password, type sapassword.

For version 12.5 or newer:

isql -U sa -S LOCALHOST

For version 11.9.2 or older:

isql -U sa -S localhost

Though you could create your own tables using the sa user and the system database, it's not a good idea. You should create a user database. The following commands create a database named testdb.

create database testdb
go
quit

Now log into the database you just created with the following command.

For version 12.5 or newer:

isql -U sa -S LOCALHOST -D testdb

For version 11.9.2 or older:

isql -U sa -S localhost -D testdb

Creating a user is a 2 step process. First a universal login must be created, then it must be added as a user to a particular database. Execute the following stored procedures and queries to create a login named testuser with password testpassword, add it as a user of testdb and give it all permissions.

sp_addlogin "testuser","testpassword"
go
sp_adduser "testuser"
go
grant all to testuser
go
quit

Now you can log in as testuser using the following command. When promted for a password, enter testpassword.

For version 12.5 or newer:

isql -U testuser -S LOCALHOST -D testdb

For version 11.9.2 or older:

isql -U testuser -S localhost -D testdb

A common problem when using ASE in this minimal configuration is running out of log space. If you run out of log space, queries will hang until space is freed up. You might get an error like this:

The transaction log in database testdb is almost full.  Your transaction is
being suspended until space is made available in the log.

To dump the log, log into the testdb database as the sa user as follows. When it promts you for a password, type sapassword.

For version 12.5 or newer:

isql -U sa -S LOCALHOST -D testdb

For version 11.9.2 or older:

isql -U sa -S localhost -D testdb

Once logged in, execute the following series of commands.

dump tran testdb with no_log
go

A longer term solution is to configure the database to truncate its log at each checkpoint. To configure this, log into the master database as the sa user as follows. When it promts you for a password, type sapassword.

For version 12.5 or newer:

isql -U sa -S LOCALHOST -D master

For version 11.9.2 or older:

isql -U sa -S localhost -D master

Once logged in, execute the following series of commands.

sp_dboption testdb, 'trunc log on chkpt', true
go

This is not something you should do in production. Without the transaction log, there is no way to recover from certain failure conditions. It is only safe to do in development or test environments where data could be reconstructed or recovered some other way.

This should be enough to get you started. To set up more complex configurations, consult the ASE documentation.

Accessing a Database

If you are installing ASE 15.0.2 or older, check the LANG environment variable first and set it to something simple like en_US as opposed to en_US.iso885915.

The first step in accessing a ASE database is setting up your environment. The SYBASE environment variable must be set to the directory that sybase was intalled in.

In the configuration script for whatever shell you use, create an environment variable called SYBASE containing the directory you intalled ASE in. In these examples, /opt/sybase is used, but your instance of ASE may be installed elsewhere.

For Bourne shells:

SYBASE=/opt/sybase
export SYBASE

For C-shells:

setenv SYBASE /opt/sybase

Now log out and log back in and you can use the isql client to access the database.

isql requires a username, server and database name to access a database. The server parameter does not refer to the hostname of the machine you want to connect to, but rather to an entry in the $SYBASE/interfaces file. When a database is created, the process puts an entry in the $SYBASE/interfaces file on the machine the database was created on. This entry can then be copied into the $SYBASE/interfaces file on other machines to provide them remote access to the database. Below is a sample entry for a server named REMOTEHOST which refers to an instance of ASE running on testhost.testdomain.com, on port 4100, accessible over tcp on ethernet and a similar entry for a server named LOCALHOST which is running on localhost.

REMOTEHOST
 master tcp ether testhost.testdomain.com 4100
 query tcp ether testhost.testdomain.com 4100

LOCALHOST
 master tcp ether localhost 4100
 query tcp ether localhost 4100

Similar entries can be made to access servers running on other hosts.

To access the database testdb on the server LOCALHOST as the user testuser, run the following command.

isql -U testuser -S LOCALHOST -D testdb

Once you're connected to the database, the isql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, type go on a line by itself. To exit, type quit on a line by itself.

A sample isqlsession follows.

[user@localhost user]$ /opt/sybase/OCS/bin/isql -U testuser -S LOCALHOST -D testdb
Password: 
1> create table testtable (
2> col1 char(40),
3> col2 integer
4> )
5> go
1> select name from sysobjects where type='U'
2> go
 name                           
 ------------------------------ 
 testtable                      

(1 row affected)
1> sp_help testtable
2> go
 Name                           Owner                         
         Type
 ------------------------------ ------------------------------ ---------------------- 
 testtable                      testuser                      user table             

(1 row affected)
 Data_located_on_segment        When_created               
 ------------------------------ -------------------------- 
 default                               Jan 14 2002 12:24PM 
 Column_name     Type            Length Prec Scale Nulls Default_name   
         Rule_name       Identity 
 --------------- --------------- ------ ---- ----- ----- --------------- --------------- -------- 
 col1            char                40 NULL  NULL     0 NULL           NULL                   0 
 col2            int                  4 NULL  NULL     0 NULL           NULL                   0 
Object does not have any indexes.
No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
 
 exp_row_size reservepagegap fillfactor max_rows_per_page 
 ------------ -------------- ---------- ----------------- 
            1              0          0                 0 
(return status = 0)
1> insert into testtable values ('hello',50)
2> go
(1 row affected)
1> insert into testtable values ('hi',60)
2> go
(1 row affected)
1> insert into testtable values ('bye',70)
2> go
(1 row affected)
1> select * from testtable
2> go
 col1                                     col2        
 ---------------------------------------- ----------- 
 hello                                             50 
 hi                                                60 
 bye                                               70 

(3 rows affected)
1> update testtable set col2=0 where col1='hi'
2> go
(1 row affected)
1> select * from testtable
2> go
 col1                                     col2        
 ---------------------------------------- ----------- 
 hello                                             50 
 hi                                                 0 
 bye                                               70 

(3 rows affected)
1> delete from testtable where col2=50
2> go
(1 row affected)
1> select * from testtable
2> go
 col1                                     col2        
 ---------------------------------------- ----------- 
 hi                                                 0 
 bye                                               70 

(2 rows affected)
1> drop table testtable
2> go
1> quit

1 comment:

  1. This technical post helps me to improve my skills set, thanks for this wonder article I expect your upcoming blog, so keep sharing.
    Regards,
    Best SAP training in chennai|SAP training center in chennai|SAP Training in Chennai

    ReplyDelete