SybaseWiki - New pages [en]

Bitmap Failed internal checks

2018-04-09T07:37:05Z

Psap: Created page with "When you get this error I. 03/27 15:44:37. 0000021871 Exception Thrown from slib\s_bm.cxx:1855, Err# 1, tid 2047 origtid 2047 I. 03/27 15:44:37. 0000021871 O/S Err#: 0,..."

When you get this error

I. 03/27 15:44:37. 0000021871 Exception Thrown from slib\s_bm.cxx:1855, Err# 1, tid 2047 origtid 2047
I. 03/27 15:44:37. 0000021871 O/S Err#: 0, ErrID: 2049 (sbm_exception); SQLCode: -1009045, SQLState: 'QSA45', Severity: 14
I. 03/27 15:44:37. 0000021871 [20066]: Bitmap failed internal checks. You may have a damaged index. Please check your IQ message file for additional information.
-- (slib\s_bm.cxx 1855)

try setting the below option

SET option DML_Options14=8

[[Category:IQ]]

RepServer show changed configuration options

2015-09-09T13:46:57Z

Psap: Created page with "You can use the below SQL statement to see the changes to the Replication Server configuration as they have been made by either rs_configure (on the ASE side) or "configure re..."

You can use the below SQL statement to see the changes to the Replication Server configuration as they have been made by either rs_configure (on the ASE side) or "configure replication server" (on the Replication Server side).

It is assumed that you use ASE as the Replication Server System Database.

select optionname as "Option",
convert(varchar(16),dv) as "Default Value",
convert(varchar(16),charvalue) as "Config value"
from rs_config c
join rs_tvalues t
on c.optionname = t.name
and c.charvalue != t.dv
where t.type = "CF"
and t.value = 1
and not ((c.optionname = "cm_fadeout_time" and charvalue = "300")
or (c.optionname = "exec_nrm_request_limit" and c.charvalue = "8388608")
or (c.optionname = "id_security_mechanism" and c.charvalue = "")
or (c.optionname = "init_sqm_write_delay" and c.charvalue = "100")
or (c.optionname = "init_sqm_write_max_delay" and c.charvalue = "1000")
or (c.optionname = "mat_report_interval" and c.charvalue = "60")
or (c.optionname = "mem_thr_exec" and c.charvalue = "90")
or (c.optionname = "mem_thr_sqt" and c.charvalue = "85")
or (c.optionname = "memory_limit" and c.charvalue = "2047")
or (c.optionname = "sqm_cmd_cache_size" and c.charvalue = "20971520")
or (c.optionname = "sqt_max_cache_size" and c.charvalue = "20971520")
or (c.optionname = "sqt_max_prs_size" and c.charvalue = "2147483647"))
order by 1

Sample output

Option Default Value Config value
------------------------------ ---------------- ----------------
cm_max_connections 144 128
disallowed_prev_passwords 0 12
max_failed_logins 0 3
memory_limit 80 8192
min_password_len 6 8
num_client_connections 30 128
num_mutexes 1024 16777216
num_stable_queues 32 40
password_lowercase_required false true
password_numeric_required false true
password_uppercase_required false true
simple_passwords_allowed true false
sqm_recover_segs 1 4
sqm_write_flush on dio
sqt_max_cache_size 1048576 200000000
sts_cachesize 1000 30000
sts_full_cache_rs_columns off on
sts_full_cache_rs_config off on
sts_full_cache_rs_databases off on
sts_full_cache_rs_diskaffinity off on
sts_full_cache_rs_objects off on
sts_full_cache_rs_queues off on

(22 rows affected)

[[Category:RepServer]]

Docker - How to run Sybase ASE in a container

2015-06-22T11:43:05Z

Psap: /* For data */

This page will show you how SAP Sybase ASE (version 16.0 GA PL01) can be run in a docker container. You will also see how to work with docker images, containters and volumes.

All tests have been carried out on a Linux Centos 7 environment, I expect little to no change if you need to run it on Redhat. You need about 10 Gb of free space to install the Sybase software, database devices, etc. Beware that you need to split this free space into several separately mounted filesystems.

The Sybase ASE server will be called "ASE1".

No performance optimization has been done for docker nor for Sybase.

Unless otherwise noted, all commands need to be run under "root".

=Prepare the system=
Prepare your system to run docker and create the sybase user.
==Package update==
Under Centos / Redhat 7 firewalld can conflict with docker so we will remove it from the system.
yum remove firewalld

Make sure you have the latest patches installed. Reboot if a new kernel has been downloaded.
yum update
reboot

==Add sybase user and group==
Docker data volumes are separate filesystems on the Linux and are made available in a docker container. The sybase processes running in these containers must have the same uid/guid for the sybase user as on the host system (your Centos / Redhat server). Keep the uid/gid for the sybase user everywhere the same.

groupadd sybase -g <free-number>
useradd -u <free-number> -g sybase sybase

=Initial docker setup=

Install docker, start it, and have it started when the Linux server boots.

yum install docker
systemctl start docker
systemctl enable docker

If you run "docker images" you can see the available images. Download a basic docker image for centos.

docker images
docker pull centos
docker images

=Create 2 docker images customized for Sybase=
We will create 2 new docker images based on the centos image that has been downloaded previously. The first image will be used to store data (database devices, dump files) and the second image is used to run the Sybase processes. It is technically possible to use just 1 image.
==For data==
Run the centos image and start a bash shell. Make a note of the container id that is shown at the prompt.
docker run -t -i centos /bin/bash

Sample output:
docker run -t -i centos /bin/bash
[root@2d0c8aae3341 /]#
In this case the container id is 2d0c8aae3341.

yum update

Add the sybase group and user making sure that the uid and gid match with the host
groupadd sybase -g <number>
useradd -u <number> -g sybase sybase
exit

Create a new image based on the actions we have made
docker commit -m "sybase data image" -a "Peter Sap" <container-ID> sybase_data/centos:v1
docker images

==For the Sybase processes==
Run the centos image once more to install the additional packages that are required to run Sybase processes.
docker run -t -i centos /bin/bash
yum update
yum install glibc.i686 libaio
groupadd sybase -g <free-number>
useradd -u <free-number> -g sybase sybase
exit
docker commit -m "sybase server image" -a "Peter Sap" <2nd container-ID> sybase_server/centos:v1
docker images

=Setup storage=
In the test case that I have used a disk of 10 Gb was available at /dev/sdb. Modify all storage related instructions to your specific environment.

pvcreate /dev/sdb
vgcreate docker_vg /dev/sdb

=Create the docker container for the database files=
==Setup storage==
Create a filesystem of 2 Gb for the database files. Consider this as a bare minumum not sized for user databases nor for database dump files.
lvcreate -L 2G -n ASE1_data docker_vg
mkfs /dev/docker_vg/ASE1_data
mkdir /ASE1_data
mount /dev/docker_vg/ASE1_data /ASE1_data
chown sybase:sybase /ASE1_data

==Create a Dockerfile and the image==
mkdir -p /var/docker_projects/sybase_data
vi /var/docker_projects/sybase_data/Dockerfile

# Dockerfile for sybase data volume containers
FROM sybase_data/centos:v1
MAINTAINER peter@petersap.nl
USER sybase

Build the image
docker build -t="sybase/data:v1" /var/docker_projects/sybase_data

==Create the container==

docker create --privileged=true -v /ASE1_data:/ASE1_data --name=ASE1_data -t -i sybase/data:v1

=Create the container for the Sybase software=

==Setup storage==
Create a filesystem of 6 Gb for the Sybase software.
lvcreate -L 6G -n sybase-ase160.22544 docker_vg
mkfs /dev/docker_vg/sybase-ase160.22544
mkdir /sybase-ase160.22544
mount /dev/docker_vg/sybase-ase160.22544 /sybase-ase160.22544
chown sybase:sybase /sybase-ase160.22544

==Install the Sybase software==
su - sybase
cd /sybase-ase160.22544
Copy the Sybase software into the current directory
scp <user>@<server>:<directory>/EBF22544.tgz .
tar -xf EBF22544.tgz
rm EBF22544.tgz
cd ebf22544
Run the installer, install the software in /sybase-ase160.22544. Do not configure any of the products.
./setup.bin
cd ..
rm -Rf ebf22544
Type exit to go back to root
exit

==Create the container==
Note that we use the "sybase/data:v1" image again, as we did when the container for the database files was created.
docker create --privileged=true -v /sybase-ase160.22544:/sybase-ase160.22544 --name=ase160.22544 -t -i sybase/data:v1

=Create the container to run Sybase ASE=
==Setup storage==
Create a filesystem of 2 Gb to store the errorlog files, startup scripts, etc.
lvcreate -L 2G -n ASE1_server docker_vg
mkfs /dev/docker_vg/ASE1_server
mkdir /ASE1_server
mount /dev/docker_vg/ASE1_server /ASE1_server
chown sybase:sybase /ASE1_server

==Create a Dockerfile==
mkdir -p /var/docker_projects/sybase_server
vi /var/docker_projects/sybase_server/Dockerfile

# Dockerfile for sybase server containers
FROM sybase_server/centos:v1
MAINTAINER peter@petersap.nl
USER sybase

==Build the image==
docker build -t="sybase/server:v1" /var/docker_projects/sybase_server

==Create the container==
Change the ip-address 10.0.0.29 to match your own environment. The port numbers 5000 can also be changed. Remember to make the same changes later on.

As you can see below, the "docker create" statement references to the containers that have already been created, using the --volumes-from option.
docker create --hostname=ASE1_server --publish=10.0.0.29:5000:5000 --privileged=true --volumes-from ASE1_data --volumes-from ase160.22544 -v /ASE1_server:/ASE1_server --name=ASE1_server -t -i sybase/server:v1

=Build the Sybase ASE server=
At this point we have created the containers for the Sybase software (with the software installed), a container for the database devices and a container to run ASE. Now we will start the container for running ASE and build the Sybase server.
==Start the container==
docker start --attach=true --interactive=true ASE1_server

You should get a bash shell while running the container.

Source in the Sybase environment variables.
. /sybase-ase160.22544/SYBASE.sh
==Create a resource file==
Create a resource file for building ASE

cd /ASE1_server
vi ASE1.rs

sybinit.release_directory: USE_DEFAULT
sybinit.product: sqlsrv
sqlsrv.server_name: ASE1
sqlsrv.sa_password: secret123
sqlsrv.new_config: yes
sqlsrv.do_add_server: yes
sqlsrv.network_protocol_list: tcp
sqlsrv.network_hostname_list: ASE1_server
sqlsrv.network_port_list: 5000
sqlsrv.application_type: USE_DEFAULT
sqlsrv.server_page_size: USE_DEFAULT
sqlsrv.force_buildmaster: no
sqlsrv.master_device_physical_name: /ASE1_data/master.dat
sqlsrv.master_device_size: USE_DEFAULT
sqlsrv.master_database_size: USE_DEFAULT
sqlsrv.errorlog: /ASE1_server/ASE1.errorlog
sqlsrv.do_upgrade: no
sqlsrv.sybsystemprocs_device_physical_name: /ASE1_data/sybsystemprocs.dat
sqlsrv.sybsystemprocs_device_size: USE_DEFAULT
sqlsrv.sybsystemprocs_database_size: USE_DEFAULT
sqlsrv.sybsystemdb_device_physical_name: /ASE1_data/sybsystemdb.dat
sqlsrv.sybsystemdb_device_size: USE_DEFAULT
sqlsrv.sybsystemdb_database_size: USE_DEFAULT
sqlsrv.tempdb_device_physical_name: /ASE1_data/tempdb.dat
sqlsrv.tempdb_device_size: USE_DEFAULT
sqlsrv.tempdb_database_size: USE_DEFAULT
sqlsrv.default_backup_server: SYB_BACKUP
#sqlsrv.addl_cmdline_parameters: PUT_ANY_ADDITIONAL_COMMAND_LINE_PARAMETERS_HERE
sqlsrv.do_configure_pci: no
sqlsrv.sybpcidb_device_physical_name: PUT_THE_PATH_OF_YOUR_SYBPCIDB_DATA_DEVICE_HERE
sqlsrv.sybpcidb_device_size: USE_DEFAULT
sqlsrv.sybpcidb_database_size: USE_DEFAULT
# If sqlsrv.do_optimize_config is set to yes, both sqlsrv.avail_physical_memory and sqlsrv.avail_cpu_num need to be set.
sqlsrv.do_optimize_config: no
sqlsrv.avail_physical_memory: PUT_THE_AVAILABLE_PHYSICAL_MEMORY_FOR_ASE_IN_OPTIMIZATION
sqlsrv.avail_cpu_num: PUT_THE_AVAILABLE_NUMBER_CPU_FOR_ASE_IN_OPTIMIZATION
==Build ASE1==
srvbuildres -r ASE1.rs

Add an extra entry to the interfaces file to allow ASE to connect to the backupserver that will be running in another container. Remember to make changes to the ip-address and port number where needed.

vi $SYBASE/interfaces

Add these lines

SYB_BACKUP_docker
query tcp ether 10.0.0.29 5001

Modify the corresponding entry in ASE. Also, configure "console logging" to capture the ASE errorlog inside the log file of the running container.

isql -Usa -Psecret123 -SASE1
sp_addserver SYB_BACKUP,null,SYB_BACKUP_docker
go
sp_configure 'enable console logging',1
go
exit
==Create a start script==
Create a script that will start ASE1 when the docker container is kicked off.

cd /ASE1_server
vi start_ASE1.sh

#!/bin/sh

# Source in the Sybase environment variables

. /sybase-ase160.22544/SYBASE.sh

# Start ASE1
${SYBASE}/${SYBASE_ASE}/install/RUN_ASE1
RET=$?

exit ${RET}

chmod +x start_ASE1.sh
exit

You should now be back at the root prompt of your Linux centos/redhat host.

=Create a new container that will kick off ASE1 when the container starts=
As you have seen above, if you start the container you will get a bash prompt. Now we need to build the container once more with the ENTRYPOINT clause in the Dockerfile so start script is executed where container ASE1_server is started.
==Create a Dockerfile==
mkdir -p /var/docker_projects/ASE1_server
cp /var/docker_projects/sybase_server/Dockerfile /var/docker_projects/ASE1_server

Add this line to the new Dockerfile (/var/docker_projects/ASE1_server/Dockerfile)

ENTRYPOINT /ASE1_server/start_ASE1.sh

==Create image and container==

docker build -t="sybase/ase1:v1" /var/docker_projects/ASE1_server
docker rm ASE1_server
docker create --hostname=ASE1_server --publish=10.0.0.29:5000:5000 --privileged=true --volumes-from ASE1_data --volumes-from ase160.22544 -v /ASE1_server:/ASE1_server --name=ASE1_server -t -i sybase/ase1:v1
==Start the container to run ASE1==
docker start ASE1_server
Run "docker ps" to see the container running. If it is not running do "docker logs ASE1_server".
docker ps

=Build the Sybase backup server=
==Create the container==

docker create --hostname=ASE1_BCK_server --publish=10.0.0.29:5001:5001 --privileged=true --volumes-from ASE1_server --name=ASE1_BCK_server -t -i sybase/server:v1

==Build the Sybase backup server==

docker start --attach=true --interactive=true ASE1_BCK_server

. /sybase-ase160.22544/SYBASE.sh
cd /ASE1_server
vi SYB_BACKUP.rs

sybinit.release_directory: USE_DEFAULT
sybinit.product: bsrv
bsrv.server_name: SYB_BACKUP
bsrv.new_config: yes
bsrv.do_add_backup_server: yes
bsrv.do_upgrade: no
bsrv.network_protocol_list: tcp
bsrv.network_hostname_list: ASE1_BCK_server
bsrv.network_port_list: 5001
bsrv.allow_hosts_list: +
bsrv.language: USE_DEFAULT
bsrv.character_set: USE_DEFAULT
bsrv.tape_config_file: USE_DEFAULT
bsrv.errorlog: /ASE1_server/SYB_BACKUP.errorlog
sqlsrv.related_sqlsrvr: ASE1
sqlsrv.sa_login: sa
sqlsrv.sa_password: secret123
#bsrv.addl_cmdline_parameters: PUT_ANY_ADDITIONAL_COMMAND_LINE_PARAMETERS_HERE

srvbuildres -r SYB_BACKUP.rs

==Create a start script==
Create a script that will start the backup server when the docker container is started

vi start_ASE1_BCK.sh

#!/bin/sh

# Source in the Sybase environment variables

. /sybase-ase160.22544/SYBASE.sh

# Start ASE1_BCK
${SYBASE}/${SYBASE_ASE}/install/RUN_SYB_BACKUP
RET=$?

exit ${RET}

chmod +x start_ASE1_BCK.sh
exit

==Create a new container that will kick off ASE1_BCK when the container starts==
mkdir -p /var/docker_projects/ASE1_BCK_server
cp /var/docker_projects/sybase_server/Dockerfile /var/docker_projects/ASE1_BCK_server

Add this line to the new Dockerfile (/var/docker_projects/ASE1_BCK_server/Dockerfile)

ENTRYPOINT /ASE1_server/start_ASE1_BCK.sh

Create a new image and a new container, remember to change the ip-address and the port number.

docker build -t="sybase/ase1_bck:v1" /var/docker_projects/ASE1_BCK_server
docker rm ASE1_BCK_server
docker create --hostname=ASE1_BCK_server --publish=10.0.0.29:5001:5001 --privileged=true --volumes-from ASE1_server --name=ASE1_BCK_server -t -i sybase/ase1_bck:v1
docker start ASE1_BCK_server

=How to manage the Sybase instances=

Sybase ASE and backup server can be stopped and started with docker commands
docker start ASE1_server
docker start ASE1_BCK_server
docker ps
docker stop ASE1_server
docker stop ASE1_BCK_server

If you run the "shutdown" command in Sybase ASE the server will stop and the docker container will also end. Same for the backup server if you run "shutdown SYB_BACKUP" in ASE.

If for some reason a server will not start (or crashes) you can examine the console log file
docker logs ASE1_server

All the Sybase files are also accesable on the Linux (centos/Redhat) host so if a container will not start anymore you can still make changes to the configuration.

=Wrapping up=

==add to /etc/fstab==
Add the filesystems to /etc/fstab
/dev/docker_vg/sybase-ase160.22544 /sybase-ase160.22544 ext2 defaults 0 0
/dev/docker_vg/ASE1_data /ASE1_data ext2 defaults 0 0
/dev/docker_vg/ASE1_server /ASE1_server ext2 defaults 0 0

[[category:ASE]]

Installation guidelines IQ 16

2013-11-08T11:41:13Z

Psap: Created page with "This page describes how to install and configure a Sybase IQ server, version 16.0. If you are new to Sybase IQ, it will also give some information about the product and termi..."

This page describes how to install and configure a Sybase IQ server, version 16.0.

If you are new to Sybase IQ, it will also give some information about the product and terminology.

==About Sybase IQ==
===About the product===
Here are 2 videos that might be worth watching.

{|
|+
|-
| ||
|-
|{{#ev:youtube|Bbcl8uY3tj4|500}}
|{{#ev:youtube|9V8bmtWtY8M|500}}
|-
|To get a basic feeling of the product you can watch this sales video from SAP
|This video is more targeted towards DBA's who already have experience with Sybase IQ and it highlights the new features in version 16. (you might want to skip the first 5 minutes as this is mostly sales talk).
|}

===Some terminology===
Sybase IQ stores it's data in various places, here is a very brief overview of them:
* Catalog store or SYSTEM dbspace, holds the meta data of the system, the table definitions, the stored procedures, etc. This is the .db file.
* Transactionlog, holds transactional information of activity in the catalog store. This is the .log file.
* Mirror logfile. An optional (but recommended) file that mirrors the transactionlog. There is no default file extension so use .mirror.

Then we have various dbspaces where the user data is stored, sorts are taking place, etc.
* IQ_SYSTEM_MAIN, holds various internal structures for checkpointing, backups, etc. These are the .iq files.
* IQ_SYSTEM_TEMP, holds temporary tables and is used for sorting data. These are the .iqtmp files.
* IQ_SHARED_TEMP, holds temporary data if you use Distributed Query Processing.
* User main, this dbspace holds your data. No default file extension here so use .iq.

Each dbspace (execept for SYSTEM) consists of various files.

==Get the software==
First download IQ.

For a licensed version you need to go http://sybase.subscribenet.com (account required) or to the Sybase eShop http://eshop.sybase.com/eshop/buy?id=48151

A trial version can be downloaded here: http://scn.sap.com/community/developer-center/analytic-server

==Sample directory structure==
In this document the following directory structure is used:

* /opt/sybase/iq - holds the Sybase IQ software
* /var/sybase/IQ1 – holds logfiles for the IQ instance and the for the catalog store
* /var/sybase/IQ1/iq_files – holds the dbspaces or symbolic links to raw devices.

==Prepare the Operating System==
Before installing Sybase IQ 16 for a production environment, make sure you have read and followed the recommendations in the hardware size guide http://scn.sap.com/docs/DOC-41455

Check that the filesystem for the Sybase software is big enough. A full installation needs approx. 1.5 Gb so 4 Gb should give you enough room to handle upgrades too. In this case we install the software in /opt/sybase/iq16. Database files will be stored on raw devices and in a subdirectory in /var/sybase.

Become root and add a group "sybase" and a user "sybase" to the system. Also create the needed directories.

mkdir -p /opt/sybase
mkdir -p /var/sybase
groupadd sybase
useradd -g sybase -d /opt/sybase sybase
chown sybase:sybase /opt/sybase
chown sybase:sybase /var/sybase

Check that your operating system has the required patches and configuration settings. Look in the installation documentation at http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc10083.1600/doc/html/title.html for detailed instructions. When you run the IQ installation, it will also check for some required packages.

==Required packages==
For RedHat / Centos, you need to install these packages:
yum install csh
yum install file
yum install libaio
yum install libXext
yum install libXrender
yum install libXtst
yum install libXi

Based on Ubuntu Linux (unsupported but seems to work), you need to install these packages:

apt-get install libaio1
apt-get install csh
apt-get install libxrender1
apt-get install libxtst6
apt-get install ia32-libs

==Prepare the Installation==
Now, switch to the newly created user:
su - sybase
You should now be in the directory /opt/sybase. Make a work directory and put the downloaded Sybase software in it.
mkdir work
mkdir iq16
cd work
<put the software in this directory>
tar -xf <software-distro>

==Installation==
Go into the directory with the installed software. Run the installer (for instance ./setup.bin).
./setup.bin
Answer the questions and install into /opt/sybase/iq16, select Typical or Full as Install Set. You can select what license type you need, Licensed or Evaluation. For the licenced version you need an account at http://sybase.subscribenet.com to generate the license keys. The evaluation license allows you to run IQ with all its features for 30 days.
Accept the default port numbers for Sybase Control Center, and specify the password for the SCC administrator. Do not start Sybase Control Center.

Once the installation is complete you can remove the work directory and its content.
cd /opt/sybase
rm -Rf work
===Errors when running setup.bin===
====java not found====

When you get an error like this

exec: 2508: /tmp/install.dir.10616/Linux/resource/jre/bin/java: not found

you need to install software to run 32 bit binaries on a 64 bit machine. Become root again and install the required packages.

On ubuntu:

apt-get install ia32-libs

On Redhat

yum install glibc-devel.i386

====Fontconfig error====

Ignore the error "Fontconfig error: Cannot load default config file" if you get it.

==Setup your environment==
In /opt/sybase/iq16 you will find files like IQ.sh and IQ.csh. Depending on the type of shell you use, you need to source in one of these. For instance, when your default shell is bash you should do this:
. /opt/sybase/iq16/IQ.sh
Is is fairly common to activate such a script from your login script.

==Create raw devices==
Raw devices are recommended and you need them for the IQ Main Store, the Temporary Store and the User Stores.
For testing purposes use 2Gb for the Main store and 1 Gb for the Temporary store. You can always add more when needed, but plan well ahead in case you want to run any serious production.

The number of files in a dbspace are important, as well as the size and expected size in the future. In this example we will use 2 files per dbspace but use 8 to 12 files in production environments, each placed on a separate LUN.

Do not forget to chown the raw devices to the sybase user. Check if this setting is persistent after a reboot.

==Start the IQ server (utility database only)==
Create a directory where server and database specific files will be stored
mkdir /var/sybase/IQ1
cd /var/sybase/IQ1
mkdir iq_files

Set the variable IQLOGDIR16 to a directory where the logfiles will be stored. When you do not set the variable, they will go to $IQDIR16/logfiles (/opt/sybase/iq16/IQ-16_0/logfiles).

export IQLOGDIR16=/var/sybase/IQ1

Start the IQ server with only the (virtual) utility database active. Use the start_iq command and specify the name of the server using the -n flag.

start_iq -n IQ1

==Connect to the IQ server==
Use dbisql to connect to the IQ server. You may need to set the DISPLAY variable.
dbisql

When you get the error “Could not initialize class java.awt.Toolkit” make sure that the required packages are installed. See above section.

Specify “DBA” for the User ID, “sql” for the password, choose “Connect to a running database on this computer” and “utility_db” as the database name. Click Connect.

Alternatively, you can use dbisqlc with a basic gui or iqisql but both of these tools are deprecated.

==Create a database==

The create database command has several option that can have a big impact on performance. See notes below or study the Sybase documentation to get the best result.
Example:
create database 'IQ1.db'
transaction log on 'IQ1.log'
mirror 'IQ1.mirror'
message path 'IQ1.iqmsg'
iq path 'iq_files/IQ1_01.iq'
iq size 2000
iq reserve 2000
temporary path 'iq_files/IQ1_01.iqtmp'
temporary size 1000
temporary reserve 1000
dba user 'DBA'
dba password '4yourEyes'

When the database has been successfully created you can stop the IQ server
stop engine
and then exit from dbisql.
exit
===Notes for the "create database" command.===
* The default page size for IQ files (.iq and .iqtmp files) is 128 Kb. When a single table in the database is expected to grow above 8 Tb or will have more than 4 billion rows choose the next bigger IQ page size of 256Kb.
* When creating a database or a dbspace you can ommit the fully specified path to the filename. When you use filenames relative to the IQ server it is more easy to move a database to another server.
* When using raw devices do not use the clause "iq size" and/or "temporary size". IQ will determine the size of the raw device and use it completely.
* When the database is created and you know that it will grow after some time, add the “iq reserve” clause to the “create database” command. This will create a contiguous free list that is already large enough for the future size of the database and so prevents a fragmented free list. Do the same when the user dbspace is created (see below). Then add the “reserve” clause.

==Create a configuration file==
In the directory /var/sybase/IQ1 create a configuration file called params.cfg with at least the following content
-n IQ1
-iqmc 4000
-iqtc 6000

This configuration file sets the name of the server, the main cache size to 4000Mb, the temp cache size to 6000Mb. Specify as much memory as you can but leave a safe margin for other processes. Sybase recommends to keep the ration between main and temp cache 3:7 or 4:6 but it really depends on the type of activity on the IQ instance.
Then start the database
start_iq @./params.cfg ./IQ1.db

==Create a user dbspace==
Log on with dbisql and use the username/password combination as specified with the create database command. Then create a user space where all tables for the application should be stored. Example:

create dbspace user_main
using file user_main_01 'iq_files/user_main_01.iq'
size 5000 MB
reserve 5000 MB
iq store;

As we do not want to store any user data in IQ_SYSTEM_MAIN transfer the privileges to create objects to user_main.

set option public.default_dbspace = 'user_main';
grant create on user_main to public;
revoke create on iq_system_main from public;

==Extend the db spaces with a second file==
For performance reasons you should use multiple files per dbspace. Here are examples to extend the 3 different dbspaces: IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP and user_main.

alter dbspace IQ_SYSTEM_MAIN
add file main_02 'iq_files/IQ1_02.iq'
size 2000 MB reserve 2000 MB;

alter dbspace IQ_SYSTEM_TEMP
add file temp_02 'iq_files/IQ1_02.iqtmp'
size 1000 MB reserve 1000 MB;

alter dbspace user_main
add file user_main_02 'iq_files/user_main_02.iq'
size 5000 MB
reserve 5000 MB;

Try to keep all the files in any dbspace to the same size to achieve a situation that all files are more or less filled at the same capacity.

By default, Sybase IQ stripes the date over the various files within a dbspace. For performance reasons it is best to have IQ handle the striped writes rather than a SAN storage system. Doing striped writes from both sides (SAN and IQ) may be counter productive.

You can run these stored procedures to view the dbspaces and their files

exec sp_iqfile;
exec sp_iqdbspace;

==Getting started with users and tables==

create user develop identified by 'GoodLuck';
grant create any object to develop;

Log off from dbisql and reconnect with the credentials of the new user. Then create the first table.

create table company(
company_id int not null,
company_name varchar(30) not null);

[[category:IQ]]
[[category:Installation Guidelines]]

Could not find or load the physical store DLL

2013-10-02T19:31:39Z

Psap: /* Make sure that asynchronous I/O is enabled */

When you get this error

Unable to start specified database: Could not find or load the physical store DLL.

You might want to investigate 2 things

==Do not use shared memory==
Remove the iqwmem switch from the params.cfg file for the IQ server when you are *not* on Solaris or HP-UX. Example:

-iqwmem 500

See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00169.1600/doc/html/san1281545678021.html for more information.

==Make sure that asynchronous I/O is enabled==
Asynchronous I/O must be enabled on the server where the IQ instance is running.

On RedHat / Centos / Fedora

yum install libaio

On Ubuntu:

apt-get install libaio1

==Before you try again==
In both cases you need to reboot the IQ instance to activate the changed configuration

[[Category:IQ]]

Installation guidelines RepServer 15.7.1

2013-04-29T10:03:39Z

Psap:

This page describes how to install and configure a Sybase Replication Server version 15.7.1. You will also learn how to connect to the server, stop and restart it.

==Get the software==
First download the software for Replication Server.

For a licensed version you need to go here: http://sybase.subscribenet.com (account required) or to the eShop at sybase.com: http://eshop.sybase.com/eshop/buy?id=22574
Downloads are also available at the Sybase download center at http://downloads.sybase.com/swd/base.do?client=support. Once registered you can download the latest EBF (patch) for the desired platform. An EBF can sometimes also be used for a full installation.

==Storage requirements==
Make sure that the filesystem for the Sybase software is big enough. A full installation needs approx. 570 Mb so 2 Gb should give you enough room to handle upgrades too. In this case we install the software in /opt/sybase/rep1571.

Replication server partitions (stable devices) will be stored /var/sybase. The mininum size of the initial partition is 20 Mb.
Additional partitions that may be needed to queue up data must be taken into account as well. The requirements vary per environment and usage. When you run out of space in partitions you can always add more.

The partitions can be created on raw devices or on filesystem files (opened with Direct I/O).

==Memory requirements==
When running replication server on a virtualized environment, you need 300 Mb of RAM as a bare minimum. Increase memory when needed.
==Prepare the operating system==

Become root and add a group "sybase" and a user "sybase" to the system. Also create the needed directories.
Strictly spoken, the usage of the username and groupname called "sybase" is not needed, any username is fine.

mkdir -p /opt/sybase
mkdir -p /var/sybase
groupadd sybase
useradd -g sybase -d /opt/sybase sybase
passwd sybase
chown sybase:sybase /opt/sybase
chown sybase:sybase /var/sybase

==Installation of the software==
Now, switch to the newly created user:
su - sybase
You should now be in the directory /opt/sybase. Make a work directory and put the downloaded Sybase software in it.
mkdir install
cd install
<put the software in this directory>
tar -xf <software-distro>

Start the installation (when using an EBF download as installation file you may need to go into a subdirectory)

./setup.bin

Use /opt/sybase/rep1571 as the destination directory, do a full installation, and continue installation without a license key, no e-mail alerts. Do not start the sample replication server as we will do a manual configuration.

When the installation has finished successful you can safely remove the work directory and the installation package.

===Errors when running setup.bin===
====java not found====
When you get an error like this

exec: 2508: /tmp/install.dir.527/Linux/resource/jre/bin/java: not found

you need to install software to run 32 bit binaries on a 64 bit machine. Become root again and install the required packages.

On ubuntu:

apt-get install ia32-libs

On Redhat

yum install glibc-devel.i386

====Fontconfig error====
Ignore the error "Fontconfig error: Cannot load default config file" if you get it.

==Setup your environment==
In /opt/sybase/rep1571 you will find files like SYBASE.csh, SYBASE.sh and SYBASE.env. Depending on the type of shell you use you need to source in one of these. For instance, when your default shell is bash you should do this:

. /opt/sybase/rep1571/SYBASE.sh

By doing:

echo $SYBASE

it should return /opt/sybase/rep1571.
Is is fairly common to activate the SYBASE.sh script (or similar one) from your login script.

When you have generated a licence file through http://sybase.subscribenet.com install it in the directory $SYBASE/SYSAM-2_0/licenses

You can run without a license for 30 days (grace period).

==Create the Replication Server==
A Replication Server needs an system database (RSSD) to store its definitions. You can choose between a Sybase ASE (RSSD) or an embedded SQL Anywhere database (ERSSD).
The choice between them depends on licensing costs, availabity of hardware or personal preference. After configuring replication server with either RSSD or ERSSD you cannot switch between them.

When you select an ERSSD you do not need to manage it, it is all handled automatically. When choosing an RSSD (Sybase ASE) there is an extra layer of software that you need to install, manage and probably pay for as well.

In this document an ERSSD server will be used for the Replication Server System Database.

Create the directories for these servers:
cd /opt/sybase
mkdir admin
mkdir admin/REP1
mkdir admin/REP1_ERSSD
mkdir /var/sybase/REP1
mkdir -p /var/sybase/REP1_ERSSD/database
mkdir /var/sybase/REP1_ERSSD/translog
mkdir /var/sybase/REP1_ERSSD/backup

Now go to the "/opt/sybase/admin/REP1" directory and create a resource file. Use the following template as an example, call it REP1.rs. You can also use the Sybase supplied resource file, it's $SYBASE/$SYBASE_REP/init/rs/install.rs

sybinit.product: rs
rs.rs_operation: rs_install
rs.rs_idserver_name: REP1
rs.rs_id_server_is_rs_server: yes
# Password for rs_REP1_id_user (RS to RS connectivity)
rs.rs_idserver_pass: secret123
rs.rs_name: REP1
# Password for "sa" user
rs.rs_rs_sa_pass: secret123
rs.rs_needs_repagent: yes
rs.rs_rs_errorlog: /opt/sybase/admin/REP1/REP1.errorlog
rs.rs_rs_cfg_file: /opt/sybase/admin/REP1/REP1.cfg
rs.rs_rssd_embedded: yes
rs.rs_erssd_name: REP1_ERSSD
rs.rs_erssd_database_dir: /var/sybase/REP1_ERSSD/database
rs.rs_erssd_translog_dir: /var/sybase/REP1_ERSSD/translog
rs.rs_erssd_backup_dir: /var/sybase/REP1_ERSSD/backup
rs.rs_erssd_errorlog_dir: /opt/sybase/admin/REP1_ERSSD
# Password for REP1_RSSD_prim
rs.rs_rssd_prim_pass: secret123
# Password for REP1_RSSD_maint
rs.rs_rssd_maint_pass: secret123
rs.rs_diskp_name: /var/sybase/REP1/part1.dat
rs.rs_diskp_lname: part1
# Password for REP1_rsi
rs.rs_rs_pass: secret123
# Password for REP1_ltm
rs.rs_ltm_rs_pass: secret123
rs.rs_id_server_network_protocol_list: tcp
rs.rs_idserver_hostname: <put your hostname here>
rs.rs_idserver_port: 5000
rs.rs_rs_port: 5000

When the partition as defined by "rs.rs_diskp_name" is on filesystem, you need to create the file with the "touch" command.

touch /var/sybase/REP1/part1.dat

Add the entries for repserver and ERSSD to $SYBASE/interfaces

REP1
master tcp ether <put your hostname here> 5000
query tcp ether <put your hostname here> 5000

REP1_ERSSD
query tcp ether <put your hostname here> 5001

Now build the server with the command:
$SYBASE/$SYBASE_REP/install/rs_init -r REP1.rs

After a successful creation you can connect to the replication server using the command line tool isql. The login “sa" has been created, with the password that was specified in REP1.rs file. At the prompt of isql you can type a command or type exit to exit.
isql -Usa -Psecret123 -SREP1
admin who
go
exit

To connect to the ERSSD server cou can use the REP1_RSSD_prim login.
isql -UREP1_RSSD_prim -Psecret123 -SREP1_ERSSD
sa_conn_info
go
exit

==Basic Replication server tasks==

===To shutdown Replication Server===
isql -Usa -Psecret123 -SREP1
shutdown
go

===To start Replication Server===
cd /opt/sybase/admin/REP1
nohup ./RUN_REP1 &

===Check status===
When replication server has been started you can check if the status is ok by running the "admin health" command. It should return "HEALTHY". When the status is not healthy check the errorlog file (at /opt/sybase/admin/REP1/REP1.errorlog) for the cause.
Please note that immediately after a start of replication server it may take a couple of minutes before all processes are up and running.

isql -Usa -Psecret123 -SREP1
admin health
go
exit

==Recommended settings==
The following settings are recommended to improve stability and performance. To change a setting, use isql and log on with the sa account
isql -Usa -Psecret123 -SREP1

===Enable direct i/o===
Set sqm_write_flush to dio to enable direct I/O on the partitions. A reboot is needed to activate the setting.

configure replication server set sqm_write_flush to 'dio'
go
shutdown
go

===sqt_max_cache_size===
Increase sqt_max_cache_size when the following message is shown the errorlog:

WARNING #24068 SQT(105:1 DIST PRIM.tpcc) - t/sqtint.c(1333)
SQT cache size is too low to load more than one transaction into the cache.

As a rule of thumb you can double the value (default is 1048576 for 32 bit, 20971520 for 64 bit) but don't overdo it. Retrieve the current value with

admin config,sqt_max_cache_size
go

configure replication server set sqt_max_cache_size to '41943040'
go

Increasing the value may require that you increase the allocated amount of memory for replication server (setting memory_limit).
===sts_cachesize===
Increase the value for sts_cachesize when this message is printed in the errorlog:

I. 2013/01/12 15:39:53. A cached row for system table ‘rs_columns’ was swapped out of the cache in order to accomodate another row.

configure replication server set sts_cachesize to '2000'
go

You can retrieve the current value with

admin config,sts_cachesize
go

==All done==
Happy Sybase-ing with your new server!!

Further reading:

Installation guide for Unix http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32237.1571/doc/html/title.html

Getting started http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc10114.1571/doc/html/title.html

Reference manual http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc32410.1571/doc/html/title.html

[[Category:RepServer]]
[[category:Installation Guidelines]]

Linux configuration settings for Sybase ASE

2013-03-11T12:52:18Z

Psap: /* For connections to ASE */

To run Sybase ASE successfully on a Linux environment you need to configure a number of items. Below you will find a few of these, but please note that this is by far not an full overview. There could be more improvements to be made.

=Memory – Huge Pages=
The configuration of Huge Pages is needed to get an efficient translation from virtual memory pages to physical pages. When you configure for Huge Pages, the amount of memory assigned to Huge Pages is allocated and reserved for shared memory. Do not over configure to prevent swapping.

When ASE reports the below message in the errorlog, you need to configure for huge pages:

Could not allocate memory using Huge Pages. Allocated using regular pages. For better performance, reboot the server after configuring enough Huge Pages.

To get the values for huge pages first determine the maximum amount of memory you want to give to ASE. For instance, on a Linux server with 512Gb physical memory you can give 90% to ASE.
512 Gb x 90% = 460 Gb = 471040 Mb.

Then determine the size of a single huge page:

cat /proc/meminfo | grep Hugepagesize

Sample output:

Hugepagesize: 2048 kB

In this case 1 huge page is 2 Mb, so the number of huge pages we need will be 471040 Mb / 2 Mb = 235520

Then set the correct value in /etc/sysctl.conf:

vm.nr_hugepages = <value>

Next step is to calculate the number of huge pages in Kb and give the “sybase” user permission to grab the memory. Take the number of huge pages as calculated above. Then multiply by 2048. That wil give you the number of pages in Kb. Example: 235520 x 2048 = 482344960. Then have it stored in /etc/security/limits.conf like this

sybase soft memlock 482344960
sybase hard memlock 482344960

In this example, "sybase" is assumed to be the unix user running ASE.

Now reboot the Linux server to check if everything works as expected.

To see the configured number of huge pages at runtime:

cat /proc/sys/vm/nr_hugepages

Notes:
* As per Sybase recommendation (CR 627114) “lock shared memory” must be switched off.
* When ASE uses huge pages, it will allocate memory to the nearest multiple of 256Mb so don’t configure the ASE exactly to the maximum number of huge pages, but leave a small amount unused (e.g. 300 Mb). In case a small increase in size is needed due to the change of a Sybase config you will not get into trouble.

=Number of file descriptors=
You may need to increase the number of file descriptors per process if you get this error:

The number of file descriptors per ASE process required to support the current configuration exceeds the limit 4096 by 3955.

Make the following changes to /etc/security/limits.conf

sybase soft nofile 16384
sybase hard nofile 16384

In this example, "sybase" is assumed to be the unix user running ASE.

Log off and log on again to pick up the new settings.

=DISK I/O=
==fs.aio-max-nr ==
fs.aio-max-nr is the maximum number of asynchronous I/Os.

The recommended minimum value for fs.aio-max-nr is 1048576, but on a busy ASE with many engines you may need to have a higher number configured.

In ASE, set these values as a starting point.

* Set “disk i/o structures” to 1048576.
* Set “max async i/os per engine” to 8192.
* Keep "max async i/os per server" at it’s default value (2147483647).

When you have multiple engines configured within ASE then use this formula to calculate the value for fs.aio-max-nr

(disk i/o structures + max async i/os per engine) * max online engines

For example: (1048576 + 8192) * 32 engines = 33816576

Then set the value in /etc/sysctl.conf:

fs.aio-max-nr = 33816576

To get the value at runtime

cat /proc/sys/fs/aio-max-nr

===Troubleshooting fs.aio-max-nr===
When the below messages appear in the ASE errorlog, the configuration of fs.aio-max-nr is not correct:

kernel asynchronous I/O not initialized. The io_setup() system call returned -22.
or
kernel kistartup: asynchronous I/O not available: No such file or directory

Either recalculate the vale or reduce the Sybase related configuration options (“disk i/o structures” and/or “max async i/os per engine”)

===Troubleshooting dstartio===
When this message appears in the errorlog

kernel dstartio: I/O request repeatedly delayed; block number: <nr>, vdn: <nr>
then you need to increase "disk i/o structures" or "max async i/os per engine".

To determine if "disk i/o structures" or "max async i/os per engine" needs to be increased look at the output of sp_sysmon that was captured during the time that the message appeared. Snapshot of output:

I/Os Delayed by
Disk I/O Structures n/a n/a 0 n/a
Server Config Limit n/a n/a 0 n/a
Engine Config Limit n/a n/a 24582 n/a
Operating System Limit n/a n/a 0 n/a

When a non-zero value is reported, increase the corresponding Sybase parameter.
Alternatively, you can run sp_monitorconfig “all” to see if the maximum number of “disk i/o structures” has reached (see column Max_Used).

Be aware that the Linux setting for fs.aio-max-nr may need to be increased as well after changing the Sybase configuration settings.

==Queue depth==
Per Linux volume used by ASE the queue depth must be configured. The default value is 128 but it must be set to 1024.

You can only set the required value at boottime and, depending on the Linux distribution, you need to do it in /etc/init.d/boot.local (Suse) or /etc/init.d/rc.local (Ubuntu) or /etc/rc.d/rc.local (RedHat)

For example:

echo 1024 >/sys/block/sdb/queue/nr_requests

To retrieve the runtime value:

cat /sys/block/sd*/queue/nr_requests

=kernel.randomize_va_space=
This may be Suse and ASE 15.0.3 specific, but it may apply to other Linux / ASE version combinations as well.

Due to a high I/O load you may see stacktraces referring to the cm_writedes module, see below snippet.

00:00000:00006:2012/11/11 01:18:41.45 kernel pc: 0x00007ffff73a56a0 (null)+0x7ffff73a56a0()
00:00000:00006:2012/11/11 01:18:41.45 kernel pc: 0x00000000018fd036 cm_writedes+0x606()

In the later versions of ASE 15.7 this should be fixed and an error message is printed.
To prevent the issue, set the value for kernel.randomize_va_space to 0 in combination with a higher value of “cpu grace time” in ASE, like 1500.
To see the runtime value
cat /proc/sys/kernel/randomize_va_space
To see the boottime value, check /etc/sysctl.conf there must be line like kernel.randomize_va_space = 0

See also http://www.novell.com/support/kb/doc.php?id=7004855

After changing kernel.randomize_va_space do a reboot of the Linux server, as that seems to be necessary.

=Prevent a firewall from dropping idle connections=
==For connections to ASE==
When your Sybase server is behind a firewall there is always the risk that a connection from the client side (e.g. running isql) to ASE will be dropped when the firewall sees it as an idle session. This is regardless whether the session is really idle or that it is just a very long running query. To prevent it, put this in /etc/sysctl.conf

net.ipv4.tcp_keepalive_time = 300

and activate it

sysctl -p

==For ssh sessions==
===Putty===
Enable TCP keepalive. On the start screen of Putty click "Connection" and activate the check-box "Enable TCP keepalives".
===ssh===
Create a config file in your ssh directory (~/.ssh/config) with the following content
Host *
ServerAliveInterval 60
ServerAliveCountMax 1000
TCPKeepAlive=yes

Whenever you use ssh tell it to use the config file
ssh -F ~/.ssh/config <server>

=Soft / Hard limits for memory allocation (Suse only)=
By default, no more than 80 % of available memory (RAM and swap) can be claimed by a single process. This is the soft-virtual limit. To check the setting: cat /etc/sysconfig/ulimit and look for the value specified as SOFTVIRTUALLIMIT. Default is “80”.

Increase the setting as setting as needed, or set to “0” for unlimited.

Associated Sybase error message:

Attempt to attach shared memory (id 32768) at address (nil), with flags 0 failed

[[category:ASE]]

ASE Cache stalls

2013-02-10T16:50:03Z

Psap:

A cache stall in Sybase ASE may occur when a read from disk has to wait for a write to complete so that it can reuse the buffer. The page that needs to be written to disk has passed the wash marker in the cache and is scheduled to be written asynchronously to disk. The tasks in ASE dealing with that are the housekeeper and the checkpoint process.

When a stall occurs the process that requires the data to be read, just has to wait until the preceding writes are completed. Obviously, this is not good for performance.

Here is a simple query to see if your system is suffering from stalls:

select CacheName,
convert(char(3),IOBufferSize / 1024) + "Kb" as "IO Size",
Stalls
from master..monCachePool
where Stalls > 0
order by CacheName, IOBufferSize

Sample output

CacheName IO Size Stalls
------------------------------ ------- -----------
tempdb cache 8 Kb 92
tempdb cache 64 Kb 15

(2 rows affected)

==Potential solutions==
===Increase the wash size===
By increasing the wash size of a pool within a cache, the modified page will be triggered at an earlier stage to be written to disk. To see the definition of a cache run sp_cacheconfig followed by the name of the cache.

sp_cacheconfig "tempdb cache"
go

Sample output

Cache Name Status Type Config Value Run Value
------------ -------- ---------------- -------------- ------------
tempdb cache Active Mixed, HK Ignore 51200.00 Mb 51200.00 Mb
------------ ------------
Total 51200.0 Mb 51200.0 Mb
==========================================================================
Cache: tempdb cache, Status: Active, Type: Mixed, HK Ignore
Config Size: 51200.00 Mb, Run Size: 51200.00 Mb
Config Replacement: relaxed LRU, Run Replacement: relaxed LRU
Config Partition: 64, Run Partition: 64
IO Size Wash Size Config Size Run Size APF Percent
-------- ------------- ------------ ------------ -----------
8 Kb 3932160 Kb 0.00 Mb 25300.00 Mb 15
16 Kb 61440 Kb 300.00 Mb 300.00 Mb 15
64 Kb 3932160 Kb 25600.00 Mb 25600.00 Mb 15
(return status = 0)

The washsize can be increased by running sp_poolconfig as in this example:

sp_poolconfig "tempdb cache", "8K", "wash_size=5G"
go

The configuration change is immediate and no reboot is required. Do not make the wash area to big as it may be counterproductive.

===Tuning the housekeeper or checkpoint process===
Alternatively, you may need to tune the housekeeper or the checkpoint process. There is a good document for this here: http://www.sybase.com/files/White_Papers/Managing-DBMS-Workloads-v1.0-WP.pdf

But beware, a cache can be marked to be ignored by the housekeeper, as is the case in the example above. To configure such a state, add a line "cache status = HK ignore cache" to the ASE configuration file.

[[category:ASE]]

Installation guidelines ASE 15.7

2012-10-17T13:11:09Z

Psap: /* Linux */

This page describes how to install and configure a Sybase ASE server version 15.7. You will also learn how to connect to the server, stop and restart it.

==Get the software==
First download ASE.

For a licensed version you need to go here: http://sybase.subscribenet.com (account required) or to the eShop at sybase.com: http://eshop.sybase.com/eshop/buy?id=19517

To get a free download you can go to this page http://www.sybase.com/ase_1500devel. You can download ASE 15.7 for various operating systems and chipsets. When you install ASE you can choose to run without a license (limited features) or install a licenced version and buy a license from Sybase.

Downloads are also available at the Sybase download center at http://downloads.sybase.com/swd/base.do?client=support. Once registered you can download the latest EBF (patch) for the desired platform. An EBF can also be used for a full installation.

==Prepare the Operating System==
Make sure that the filesystem for the Sybase software is big enough. A full installation needs approx. 1.5 Gb so 4 Gb should give you enough room to handle upgrades too. In this case we install the software in /opt/sybase/ase157. Database files will be stored /var/sybase.

Become root and add a group "sybase" and a user "sybase" to the system. Also create the needed directories.
Strictly spoken, the usage of the username and groupname called "sybase" is not needed, any username is fine.

mkdir -p /opt/sybase
mkdir -p /var/sybase
groupadd sybase
useradd -g sybase -d /opt/sybase sybase
passwd sybase
chown sybase:sybase /opt/sybase
chown sybase:sybase /var/sybase

Enable the operating system to allow more than the default value for shared memory.

===Linux===
====Shared Memory====
To see the current value of shared memory:
sysctl kernel.shmmax

To configure a shared memory segment of 4 Gb
sysctl -w kernel.shmmax=4294967296

Then add kernel.shmmax=4294967296 to /etc/sysctl.conf

See also [[Linux configuration settings for Sybase ASE]], but these settings can be done when the installation and configuration has been completed.

====32 bit libraries====
When installing Sybase ASE 64 bit version on Linux, you need to install the 32 bit shared libraries as well. This will prevent problems when running isql or bcp.

=====Debian based systems=====
apt-get install ia32-libs
=====RedHat based systems=====
yum install glibc.i686

===Solaris (pre version 10)===
/etc/system
set shmsys:shminfo_shmmax = 4294967296

===Solaris (system 10)===
/etc/project
projadd -c "sybase" 'user.sybase'
projmod -s -K "project.max-shm-memory={privileged,4GB,deny}" 'user.sybase'

==Raw devices or filesystem files==
Sybase recommends to store all database files on raw devices except for temporary databases like tempdb. Some Unix adminstrators do not like raw devices and then you need to put everything on filesystems. Also, consult your storage administrator when in doubt.

===Raw devices===
When you use raw devices, create the following:
* master (100 Mb)
* sybsystemprocs (172M)
* sybsystemdb (6M)
* data01 (for instance 10G but for testing purposes 100Mb is also good. You can always add more)
* log01(for instance 2G but for testing purposes 20Mb is also good. You can always add more).
Do not forget to chown the raw devices to the sybase user. Check if this setting is persistent after a reboot.

===Temporory databases===
Files for temporary databases (like tempdb) should be stored on the filesystem. Allow at least 100Mb but, depending on your application, several Gb's or much more is not uncommon.

==Determine your license policy==
Since ASE 15.0 you cannot run a production server without a license file. License files can reside on the network or on the local system. Think about the license policy that suits you best. Most sites choose for license files on each Unix server.

When there is no valid license, ASE will run with a grace time of 30 days. After that it will shutdown and it cannot be rebooted anymore until you install a license, or change the license settings revert to the Express Edition.

==Installation of the software==
Now, switch to the newly created user:
su - sybase
You should now be in the directory /opt/sybase. Make a work directory and put the downloaded Sybase software in it.
mkdir install
cd install
<put the software in this directory>
tar -xf <software-distro>

Start the installation (when using an EBF download as installation file you may need to go into a subdirectory)

./setup.bin

When you get a message like "A suitable JVM could not be found" or “exec: 2470: /tmp/install.dir.1392/Linux/resource/jre/bin/java: not found” check the following document for a solution: [[Ubuntu 64 bit ASE installation]]

Use /opt/sybase/ase157 as the destination directory, do a full installation. At the list of possible servers to configure, deselect all options. We will do a manual configuration.

When the installation has finished successful you can safely remove the work directory and the installation package.

==Setup your environment==
In /opt/sybase/ase157 you will find files like SYBASE.csh, SYBASE.sh and SYBASE.env. Depending on the type of shell you use you need to source in one of these. For instance, when your default shell is bash you should do this:

. /opt/sybase/ase157/SYBASE.sh

By doing:

echo $SYBASE

it should return /opt/sybase/ase157.
Is is fairly common to activate the SYBASE.sh script (or similar one) from your login script.

When you have generated a licence file through http://sybase.subscribenet.com install it in the directory $SYBASE/SYSAM-2_0/licenses

==Create the ASE server==
Most installations, especially for development purposes, run multiple ASE's and backup servers from a single Sybase software installation. For a good overview it is quite handy to store files that are specific for a server in it's own directory. In this example we create two servers: an ASE called ASE1 and a backupserver called SYB_BACKUP. Configuration files and the errorlog are stored in the admin directory, database files are stored in /var/sybase.

Create the directories for these servers:
cd /opt/sybase
mkdir admin
mkdir admin/ASE1
mkdir admin/SYB_BACKUP
mkdir /var/sybase/ASE1

When you choose to use raw device it's quite handy when you create symbolic links from /var/sybase/ASE1 to those devices. For example:
ln -s /dev/hda4 /var/sybase/ASE1/master.dat
Do this also for the other raw devices that you have made.

Now go to the "/opt/sybase/admin/ASE1" directory and create a resource file. Use the following template as an example, call it ASE1.rs. You can also use the Sybase supplied resource file, it's $SYBASE/$SYBASE_ASE/init/sample_resource_files/srvbuild.adaptive_server.rs

sybinit.release_directory: USE_DEFAULT
sybinit.product: sqlsrv
sqlsrv.server_name: ASE1
sqlsrv.sa_password: secret123
sqlsrv.new_config: yes
sqlsrv.do_add_server: yes
sqlsrv.network_protocol_list: tcp
sqlsrv.network_hostname_list: <put your hostname here>
sqlsrv.network_port_list: 4000
sqlsrv.application_type: MIXED
sqlsrv.server_page_size: 4096
sqlsrv.force_buildmaster: no
sqlsrv.master_device_physical_name: /var/sybase/ASE1/master.dev
sqlsrv.master_device_size: 100
sqlsrv.master_database_size: 60
sqlsrv.errorlog: /opt/sybase/admin/ASE1/errorlog
sqlsrv.do_upgrade: no
sqlsrv.sybsystemprocs_device_physical_name: /var/sybase/ASE1/sybsystemprocs.dev
sqlsrv.sybsystemprocs_device_size: USE_DEFAULT
sqlsrv.sybsystemprocs_database_size: USE_DEFAULT
sqlsrv.sybsystemdb_device_physical_name: /var/sybase/ASE1/sybsystemdb.dev
sqlsrv.sybsystemdb_device_size: USE_DEFAULT
sqlsrv.sybsystemdb_database_size: USE_DEFAULT
sqlsrv.tempdb_device_physical_name: /var/sybase/ASE1/tempdb.dev
sqlsrv.tempdb_device_size: 100
sqlsrv.tempdb_database_size: 100
sqlsrv.default_backup_server: SYB_BACKUP
#sqlsrv.addl_cmdline_parameters: PUT_ANY_ADDITIONAL_COMMAND_LINE_PARAMETERS_HERE
sqlsrv.do_configure_pci: no
sqlsrv.sybpcidb_device_physical_name: PUT_THE_PATH_OF_YOUR_SYBPCIDB_DATA_DEVICE_HERE
sqlsrv.sybpcidb_device_size: USE_DEFAULT
sqlsrv.sybpcidb_database_size: USE_DEFAULT
# If sqlsrv.do_optimize_config is set to yes, both sqlsrv.avail_physical_memory and sqlsrv.avail_cpu_num need to be set.
sqlsrv.do_optimize_config: yes
sqlsrv.avail_physical_memory: 256
sqlsrv.avail_cpu_num: 1

This resource file will a create a Sybase server with the following characteristics:
* The name of the server will be ASE1, can be changed later with the sp_addserver stored procedure.
* The server will have a listener on the ip-address as indicated with <hostname> and port 4000. This can be changed later in the interfaces file located at $SYBASE/interfaces.
* Application type is mixed, so you expect both oltp and dss type of queries. This can be changed later by modification of the “optimization goal” setting with sp_configure).
* Server page size will be 4 Kb. The default is 2 Kb but with today’s hardware this will give sub-optimal performance. Once the server has been created it cannot be changed, a complete server rebuild is needed then.
* The master device will be 100 Mb, the size of the master database will be 60 Mb. The master devices will contain the master database, the master database contains important meta-data about the server. It’s very unusual to increase the size of the master device or the master database.
* The location of the errorlog is /opt/sybase/admin/ASE1/errorlog, can be changed later in the configuration file.
* A device of 172 Mb will be created for the sybsystemprocs database, also with a size of 172 Mb. The size of the device can be increased with the “disk resize” command, the database can be increased with the “alter database” command.
* A device of 6 Mb will be created for the sybsystemdb database. The size of the database will actually be 12Mb as a small portion will also reside on the master device. The size of the device can be increased with the “disk resize” command, the database can be increased with the “alter database” command.
* A temporary database 'tempdb' will be created on a device with a size of 100 Mb, the database will also be 100 Mb. In fact, it will be a bit larger since also a small portion will reside on the master device. Again, the device and the tempdb database can be increased in size. You can also create multiple temporary databases for improved concurrency.
* We will build a backup server called SYB_BACKUP. The name can be changed later with sp_addserver.
* We will not use a PCI (Pluggable Component Interface) database. This database is mainly used for Java in the server.
* Use the optimize configuration feature and tune for 256 Mb of memory and 1 cpu.

Now build the server with the command:
srvbuildres -r ASE1.rs

When the srvbuildres command fails with:
Task failed
Unable to boot server 'ASE1'.
Server 'ASE1' was not created.
then go to this page for tips how to solve this: [[srvbuildres task failed]].

After a successful creation you can connect to the server using the command line tool isql. The login “sa" has been created, with the password that was specified in ASE.rs file. At the prompt of isql you can type a command or execute a stored procedure. Type exit to exit.
isql -Usa -Psecret123 -SASE1
sp_helpdb
go
exit

==Basic ASE configuration==

===Move and modify configuration files===
In this step we will move the configuration files to the admin directory. First shutdown ASE

isql -Usa -Psecret123 -SASE1
shutdown
go

cd $SYBASE/$SYBASE_ASE
mv ASE1.cfg /opt/sybase/admin/ASE1
rm ASE1.*
cd install

Modify RUN_ASE1 and make the following changes:
* Make the -s flag the first option. This has the advantage that a listing of running processes on the system will show the name of the Sybase server.
* Change the location of the configuration files, as specified with the -c flag, to the admin directory
Each line must end with a backslash.
Example RUN_ASE1 file:

#!/bin/sh
#
# ASE page size (KB): 4096
# Master device path: /var/sybase/ASE1/master.dev
# Error log path: /opt/sybase/admin/ASE1/errorlog
# Configuration file path: /opt/sybase/ase157/ASE-15_0/ASE1.cfg
# Directory for shared memory files: /opt/sybase/ase157/ASE-15_0
# Adaptive Server name: ASE1
#
/opt/sybase/ase157/ASE-15_0/bin/dataserver \
-sASE1 \
-d/var/sybase/ASE1/master.dev \
-e/opt/sybase/admin/ASE1/errorlog \
-c/opt/sybase/admin/ASE1/ASE1.cfg \
-M/opt/sybase/ase157/ASE-15_0 \

Now restart the Sybase server
./startserver -f ./RUN_ASE1

===Load instmsg.ebf file===
The instmsgs.ebf file contains the latest update for Sybase error messages. You can load it with isql.
isql -Usa -Psecret123 -SASE1 < $SYBASE/$SYBASE_ASE/scripts/instmsgs.ebf

==Create the backup server==
Create the backup server using the following steps.

Go to the "/opt/sybase/admin/SYB_BACKUP" directory and create a resource file. Use the following template as an example, call it SYB_BACKUP.rs. You can also use the Sybase supplied resource file, it's $SYBASE/$SYBASE_ASE/init/sample_resource_files/srvbuild.backup_server.rs

sybinit.release_directory: USE_DEFAULT
sybinit.product: bsrv
bsrv.server_name: SYB_BACKUP
bsrv.new_config: yes
bsrv.do_add_backup_server: yes
bsrv.do_upgrade: no
bsrv.network_protocol_list: tcp
bsrv.network_hostname_list: <put your hostname here>
bsrv.network_port_list: 4001
bsrv.language: USE_DEFAULT
bsrv.character_set: USE_DEFAULT
bsrv.tape_config_file: USE_DEFAULT
bsrv.errorlog: /opt/sybase/admin/SYB_BACKUP/SYB_BACKUP.log
sqlsrv.related_sqlsrvr: ASE1
sqlsrv.sa_login: sa
sqlsrv.sa_password: secret123
#bsrv.addl_cmdline_parameters: PUT_ANY_ADDITIONAL_COMMAND_LINE_PARAMETERS_HERE

Build the server with the command:
srvbuildres -r SYB_BACKUP.rs

===Move and modify configuration files===
In this step we will move the configuration file to the admin directory. First shutdown the backup server:
isql -Usa -Psecret123 -SASE1
shutdown SYB_BACKUP
go
exit

Modify the file RUN_SYB_BACKUP, located at $SYBASE/$SYBASE_ASE/install. Move the line with the -s flag to the top. Sample RUN_SYB_BACKUP:
#!/bin/sh
#
# Error log path: /opt/sybase/ase157/ASE-15_0/install/SYB_BACKUP.log
# Maximum number of network connections: 25
# Maximum number of server connections: 20
# Multibuf executable path: /opt/sybase/ase157/ASE-15_0/bin/sybmultbuf
# Backup Server name: SYB_BACKUP
#
/opt/sybase/ase157/ASE-15_0/bin/backupserver \
-SSYB_BACKUP \
-e/opt/sybase/admin/SYB_BACKUP/SYB_BACKUP.log \
-N25 \
-C20 \
-M/opt/sybase/ase157/ASE-15_0/bin/sybmultbuf \

Restart the backupserver
startserver -f $SYBASE/$SYBASE_ASE/install/RUN_SYB_BACKUP

==Create a user database==
disk init name = data01,size="1G",physname="/var/sybase/ASE1/data01.dat"
go
disk init name = log01,size="100M",physname="/var/sybase/ASE1/log01.dat"
go
create database my_database on data01="1G" log on log01 = "100M"
go
==All done==
Happy Sybase-ing with your new server!!

Further reading:

Installation guide for Linux http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc30119.1570/doc/html/title.html

Sybase System administration http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31654.1570/html/sag1/title.htm

[[Category:ASE]]
[[category:Installation Guidelines]]

Bypasssing cross platform load issues

2012-08-31T09:14:09Z

Psap: /* Stacktrace in modules make_log_consistent and rec_build_recovery_info */

When loading an ASE database dump made on another platform you may run into problems. For instance, this can happen when the dump is made on an Intel x86 platform but you want to load it into an ASE running on Solaris Sparc. As documented by Sybase, you need to prepare a dump for this situation before attempting to load it.

See: http://www.sybase.com/detail?id=1033627 or go straight to this whitepaper http://www.sybase.com/content/1033627/18XPDL_1253_WP.pdf

When you do not follow the required steps before making the database dump you may get this error:

Started cross-platform conversion for log records.
Msg 3151, Level 16, State 2:
Server 'TEST', Line 1:
Adaptive Server cannot load this database because the database that was dumped
was not quiescent when the dump was performed. Run sp_flushstats before DUMP
DATABASE and ensure that the database is not updated during the dump.

Under some circumstances you may not want to follow the required steps and force ASE to bring the database online anyway. Below are the steps how you can do that. The name of database in the example is 'testdb'. You may run into some problems, see section below for solutions.

'''IMPORTANT''' This method may result in some loss of data or a an unusable database on the ASE where you load the database dump into. It's recommended to use it only for testing and query purposes.

==Bypassing Msg 3151==
After loading the database and Msg 3151 is reported follow these steps:

use master
go
sp_configure "allow updates",1
go
update sysdatabases set status = -32768,status3=131072 where name = "testdb"
go

Shutdown the server and start it up again.

A message in the errorlog indicates that recovery is not done for the database:

00:00000:00001:2011/04/05 16:07:54.38 server
*** Bypassing recovery of database id 4

Log on to ASE and rebuild the transaction log for the database
dbcc traceon(3604)
go
dbcc save_rebuild_log(testdb)
go
update sysdatabases set status = 0 where name = "testdb"
go

Shutdown the server and start it up again.

The database will now be brought online, watch the errorlog for any problems.
Now the database should be recovered succesfully.

Log on to the server and:
update sysdatabases set status3=524288 where name = "testdb"
go
use testdb
go
sp_post_xpload
go
update sysdatabases set status3=0 where name = "testdb"
go

All done now, you should be able to use the database for querying data.

==Problems and potential solutions==

===Transactionlog full during recovery===

00:00000:00013:2011/04/05 16:18:00.14 server Error: 1105, Severity: 17, State: 4
00:00000:00013:2011/04/05 16:18:00.14 server Can't allocate space for object 'syslogs' in database 'testdb' because 'logsegment' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.
00:00000:00013:2011/04/05 16:18:00.14 server This database requires upgrade, but its log is nearly full. Extend the log, or try 'DUMP TRAN testdb WITH NO_LOG', then retry ONLINE DATABASE.

Change the segment status for the database.

use master
go
update sysusages set segmap = 7 where dbid = db_id("testdb")
go

Now reload the database with the "load database" command and reboot ASE. Then:

use master
go
update sysdatabases
set status3 = status3 - 67108864,
status = -32768
where name = "testdb"
go

===Stacktrace in modules make_log_consistent and rec_build_recovery_info===
when an error in the errorlog appears like this:

00:00000:00001:2011/03/25 16:56:17.04 kernel Current process (0x30003) infected with signal 11 (SIGSEGV)
00:00000:00001:2011/03/25 16:56:17.04 kernel Address 0x00000000803b80fc (xls_open+0xdc), siginfo (code, address) = (1, 0x00000000000000ac)
00:00000:00001:2011/03/25 16:56:17.04 kernel Saved signal context address 0x0000010001b08280
00:00000:00001:2011/03/25 16:56:17.04 kernel pc=0x00000000803b80fc npc=0x00000000803b8100
00:00000:00001:2011/03/25 16:56:17.04 kernel g0-g2 0x0000000000000000 0x0000000000000008 0x00000000825fd000
00:00000:00001:2011/03/25 16:56:17.04 kernel g3-g5 0x0000000000000038 0x0000000081b3f9d0 0x00000000000001c0
00:00000:00001:2011/03/25 16:56:17.04 kernel g6-g7 0x0000000000000000 0xffffffff7eb00200
00:00000:00001:2011/03/25 16:56:17.04 kernel o0-o2 0x0000000000000000 0x000001001c99e6c0 0x000001001c83ab50
00:00000:00001:2011/03/25 16:56:17.04 kernel o3-o5 0x000001001d5dfc48 0x0000000000000000 0x0000000000000008
00:00000:00001:2011/03/25 16:56:17.04 kernel o6-o7 0x0000010001b07e61 0x00000000803b80f4
00:00000:00001:2011/03/25 16:56:17.04 kernel l0-l2 0x0000000080ddb5a0 0x00000000825c2c00 0x000001001e26a458
00:00000:00001:2011/03/25 16:56:17.04 kernel l3-l5 0x0000000000000000 0x0000000004000000 0x00000000825fd000
00:00000:00001:2011/03/25 16:56:17.04 kernel l6-l7 0x00000000822cc000 0x0000000004000000
00:00000:00001:2011/03/25 16:56:17.04 kernel i0-i2 0x0000000000000001 0x0000000000000000 0x0000000000000001
00:00000:00001:2011/03/25 16:56:17.04 kernel i3-i5 0x000001001e26a158 0x0000000000000104 0x0000000000000006
00:00000:00001:2011/03/25 16:56:17.04 kernel i6-i7 0x0000010001b07f11 0x0000000080ddb5f0
00:00000:00001:2011/03/25 16:56:17.04 kernel ************************************
00:00000:00001:2011/03/25 16:56:17.04 kernel SQL causing error : use master
00:00000:00001:2011/03/25 16:56:17.04 kernel ************************************
00:00000:00001:2011/03/25 16:56:17.04 server SQL Text: use master
00:00000:00001:2011/03/25 16:56:17.04 kernel curdb = 5 tempdb = 2 pstat = 0x1000
00:00000:00001:2011/03/25 16:56:17.04 kernel lasterror = 0 preverror = 0 transtate = 1
00:00000:00001:2011/03/25 16:56:17.04 kernel curcmd = 0 program =
00:00000:00001:2011/03/25 16:56:17.04 kernel extended error information: hostname: login:
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080f412e4 pcstkwalk+0x24(0x0000010001b06fa0, 0x0000010001b04e18, 0x000000000000270f, 0x0000000000000002, 0x0000000000000000)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080f4112c ucstkgentrace+0x1d0(0x000001001d5dfc48, 0x0000000000000002, 0x000000000000270f, 0x0000000000000000, 0x0000000000000000)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080eccb5c ucbacktrace+0xb4(0x0000000000000000, 0x0000000000000001, 0x0000000000007c00, 0x000001001d5dfc48, 0x000001001df6d540)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080594f74 terminate_process+0x131c(0x0000000000007400, 0xffffffffffffffff, 0x000001001d5dfc48, 0x0000000000008000, 0x0000000081b2e588)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080f05c2c kisignal+0x27c(0x0000000000000058, 0x0000010001b08560, 0x0000010001b08280, 0x0000000000030003, 0x0000000000000000)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x00000000803b80fc xls_open+0xdc(0x0000000000000001, 0x0000000000000000, 0x0000000000000001, 0x000001001e26a158, 0x0000000000000104)
00:00000:00001:2011/03/25 16:56:17.04 kernel [Handler pc: 0x0000000080de8c00 rec_handle installed by the following function:-]
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080ddb5f0 '''make_log_consistent'''+0xd8(0x0000000000000005, 0x00000000825c1c00, 0x0000000000000005, 0x000001001e26a158, 0x0000000000000005)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080ddc4e4 '''rec_build_recovery_info'''+0xc7c(0x00000000000000f0, 0x0000000000000000, 0x0000000000008000, 0x0000000000008000, 0x000000000000c000)
00:00000:00001:2011/03/25 16:56:17.04 kernel [Handler pc: 0x0000000080deccd0 rec__caller_hdlr installed by the following function:-]
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080de0d98 dorecover+0x118(0x000001001d5dfc48, 0x0000000000001000, 0x0000000000008090, 0x0000000081b2e400, 0x0000000000000000)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x00000000805988d4 ds__recoverdbs+0x660(0x000001001d5dfc48, 0x00000000000094d8, 0x000001001c97efc0, 0x0000000000000001, 0x0000000081d58514)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080591438 dsinit+0xa80(0x000001000002ec98, 0x0000000081b2e400, 0x0000000000000001, 0x0000000000000032, 0x0000000081d58514)
00:00000:00001:2011/03/25 16:56:17.04 kernel pc: 0x0000000080f57c0c _coldstart(0x0000000000000000, 0x00000000805909b8, 0x0000000000000000, 0x0000000000000000, 0x0000000000000000)
00:00000:00001:2011/03/25 16:56:17.04 kernel end of stack trace, spid 1, kpid 196611, suid 0

Then run these commands:

use master
go
update sysdatabases set status3 = 131072
where name = "testdb"
go

Reboot ASE, then

dbcc rebuild_log(testdb,1,1)
go
update sysdatabases set status = 0 where name = "testdb"
go

Reboot ASE once more.

===Transactionlog full===
When the database is accessible but the transactionlog becomes full, check the states:
use testdb
go
sp_helpsgement logsegment
go

When a negative value of free space is reported you may need to rebuild the transaction log.
dbcc save_rebuild_log(testdb)
go
use master
go
sp_dboption testdb,"single",true
go
use testdb
go
dbcc tablealloc(syslogs,full,fix)
go

[[category:ASE]]

Installation guidelines ASE 15.5

2010-10-24T18:40:38Z

Psap: /* All done */

This page describes how to install and configure a Sybase ASE server version 15.5.

==Get the software==
First download ASE.

For a licensed version you need to go here: http://sybase.subscribenet.com (account required) or to the eShop at sybase.com: http://eshop.sybase.com/eshop/buy?id=19517

To get a free download you can go to this page http://www.sybase.com/ase_1500devel. You can download ASE 15.5 for various operating systems and chipsets. When you install ASE you can choose to run without a license (limited features) or install a licenced version and buy a license from Sybase.

Downloads are also available at the Sybase download center at http://downloads.sybase.com/swd/base.do?client=support. Once registered you can download the latest EBF (patch) for the desired platform. An EBF can also be used for a full installation.

==Prepare the Operating System==
Make sure that the filesystem for the Sybase software is big enough. A full installation needs approx. 1.2 Gb so 4 Gb should give you enough room to handle upgrades too. In this case we install the software in /opt/sybase/ase155. Database files will be stored on raw devices and/or in /var/sybase.

Become root and add a group "sybase" and a user "sybase" to the system. Also create the needed directories.
Strictly spoken, the usage of the username and groupname called "sybase" is not needed, any username is fine.

mkdir -p /opt/sybase
mkdir -p /var/sybase
groupadd sybase
useradd -g sybase -d /opt/sybase sybase
passwd sybase
chown sybase:sybase /opt/sybase
chown sybase:sybase /var/sybase

Enable the operating system to allow more than the default value for shared memory.

*Solaris (pre version 10) /etc/system
set shmsys:shminfo_shmmax = 4294967295

*Solaris (system 10) /etc/project
projadd -c "sybase" 'user.sybase'
projmod -s -K "project.max-shm-memory={privileged,4GB,deny}" 'user.sybase'

*Linux:
Use "sysctl kernel.shmmax" to see the current value.
Configure a new value with "sysctl -w kernel.shmmax=78643200"
Then add kernel.shmmax=78643200 to /etc/sysctl.conf

==Raw devices or filesystem files==
Sybase recommends, and I fully 100% agree, to store all database files on raw devices except for temporary databases like tempdb. Some Unix adminstrators do not like raw devices and then you need to put everything on filesystems.

===Raw devices===
When you use raw devices, create the following:
* master (60Mb but 100Mb is better)
* sybsystemprocs (200M)
* sybsystemdb (20M)
* data01 (for instance 32G but for testing purposes 100Mb is also good. You can always add more)
* log01(for instance 5G but for testing purposes 20Mb is also good. You can always add more).
Do not forget to chown the raw devices to the sybase user. Check if this setting is persistent after a reboot.
===Temporory databases===
Files for temporary databases (like tempdb) should be stored on the filesystem. Allow at least for 200Mb but, depending on your application, several Gb's or much more is not uncommon.

==Determine your license policy==
Since ASE 15.0 you cannot run a production server without license file. License files can reside on the network or on the local system. Think about the license policy that suits you best. For small sites choose for license files on each Unix server.

When there is no valid license after the installation ASE will run with a grace time of 30 days. After that it will shutdown and it cannot be rebooted anymore until you install a license.

==Installation of the software==
Now, switch to the newly created user:
su - sybase
You should now be in the directory /opt/sybase. Make a work directory and put the downloaded Sybase software in it.
mkdir install
cd install
<put the software in this directory>
tar -xf <software-distro>

Start the installation (when using an EBF download as installation file you may need to go into a subdirectory)

./setup.bin

When you get a message like "A suitable JVM could not be found" or “exec: 2470: /tmp/install.dir.1392/Linux/resource/jre/bin/java: not found” check the following document for a solution: [[Ubuntu 64 bit ASE installation]]

Use /opt/sybase/ase155 as the destination directory, do a full installation. At the list of possible servers to configure, deselect all options. We will do a manual configuration.

When the installation has finished successful you can safely remove the work directory.

==Setup your environment==
In /opt/sybase/ase155 you will find files like SYBASE.csh, SYBASE.sh and SYBASE.env. Depending on the type of shell you use you need to source in one of these. For instance, when your default shell is bash you should do this:

. /opt/sybase/ase155/SYBASE.sh

By doing:

echo $SYBASE

it should return /opt/sybase/ase155.
Is is fairly common to activate the SYBASE.sh script (or similar one) from your login script.

When you have generated a licence file through http://sybase.subscribenet.com install it in the directory $SYBASE/SYSAM-2_0/licences
==Create the ASE server==
Most installations run multiple ASE's and backup servers from a single Sybase software installation. For a good overview it is quite handy to store files that are specific for a server in it's own directory. In this example we create two servers: an ASE called ASE1 and a backupserver called SYB_BACKUP. Configuration files and the errorlog are stored in the admin directory, database files are stored in /var/sybase.

Create the directories for these servers:
cd /opt/sybase
mkdir admin
mkdir admin/ASE1
mkdir admin/SYB_BACKUP
mkdir /var/sybase/ASE1

When you choose to use raw device it's quite handy when you create symbolic links from /var/sybase/ASE1 to those devices. For example:
ln -s /dev/hda4 /var/sybase/ASE1/master.dat
Do this also for the other raw devices that you have made.

Now go to the "/opt/sybase/admin/ASE1" directory and create a resource file. Use the following template as an example, call it ASE1.rs. You can also use the Sybase supplied resource file, it's $SYBASE/$SYBASE_ASE/init/sample_resource_files/srvbuild.adaptive_server.rs

sybinit.release_directory: USE_DEFAULT
sybinit.product: sqlsrv
sqlsrv.server_name: ASE1
sqlsrv.new_config: yes
sqlsrv.do_add_server: yes
sqlsrv.network_protocol_list: tcp
sqlsrv.network_hostname_list: <your hostname>
sqlsrv.network_port_list: 4000
sqlsrv.application_type: MIXED
sqlsrv.server_page_size: USE_DEFAULT
sqlsrv.force_buildmaster: no
sqlsrv.master_device_physical_name: /var/sybase/ASE1/master.dev
sqlsrv.master_device_size: 100
sqlsrv.master_database_size: 60
sqlsrv.errorlog: /opt/sybase/admin/ASE1/errorlog
sqlsrv.do_upgrade: no
sqlsrv.sybsystemprocs_device_physical_name: /var/sybase/ASE1/sybsystemprocs.dev
sqlsrv.sybsystemprocs_device_size: 200
sqlsrv.sybsystemprocs_database_size: 200
sqlsrv.sybsystemdb_device_physical_name: /var/sybase/ASE1/sybsystemdb.dev
sqlsrv.sybsystemdb_device_size: 20
sqlsrv.sybsystemdb_database_size: 20
sqlsrv.tempdb_device_physical_name: /var/sybase/ASE1/tempdb.dev
sqlsrv.tempdb_device_size: 100
sqlsrv.tempdb_database_size: 100M
sqlsrv.default_backup_server: SYB_BACKUP
#sqlsrv.addl_cmdline_parameters:
sqlsrv.do_configure_pci: no
sqlsrv.sybpcidb_device_physical_name: PUT_THE_PATH_OF_YOUR_SYBPCIDB_DATA_DEVICE_HERE
sqlsrv.sybpcidb_device_size: USE_DEFAULT
sqlsrv.sybpcidb_database_size: USE_DEFAULT
# If sqlsrv.do_optimize_config is set to yes, both sqlsrv.avail_physical_memory and sqlsrv.avail_cpu_num need to be set.
sqlsrv.do_optimize_config: no
sqlsrv.avail_physical_memory: PUT_THE_AVAILABLE_PHYSICAL_MEMORY_FOR_ASE_IN_OPTIMIZATION
sqlsrv.avail_cpu_num: PUT_THE_AVAILABLE_NUMBER_CPU_FOR_ASE_IN_OPTIMIZATION

This resource file will a create a Sybase server with the following characteristics:
* The name of the server will be ASE1, can be changed later with the sp_addserver stored procedure.
* The server will have a listener on the ip-address as indicated with <hostname> and port 4000. This can be changed later in the interfaces file.
* Application type is mixed, so you expect both oltp and dss type of queries. This can be changed later by modification of the “optimization goal” setting with sp_configure).
* Server page size will be 4 Kb, which is the default. This cannot be changed, a complete server rebuild is needed then.
* The master device will be 100 Mb, the size of the master database will be 60 Mb. The master devices will contain the master database, the master database contains important meta-data about the server. The master device can be increased with the “disk resize” command, the master database can be increased with the “alter database” command. There are no documented methods for shrinking devices or databases.
* The location of the errorlog is /opt/sybase/admin/ASE1/errorlog, can be changed later in the configuration file.
* A device of 200 Mb will be created for the sybsystemprocs database, also with a size of 200 Mb. The size of the device can be increased with the “disk resize” command, the database can be increased with the “alter database” command.
* A device of 20 Mb will be created for the sybsystemdb database, also with a size of 20 Mb. A small portion will also reside on the master device. The size of the device can be increased with the “disk resize” command, the database can be increased with the “alter database” command.
* A temporary database 'tempdb' will be created on a device with a size of 100 Mb, the database will also be 100 Mb. In fact, it will be a bit larger since also a small portion will reside on the master device. Again, the device and the tempdb database can be increased in size. You can also create multiple temporary databases for improved concurrency.
* We will build a backup server called SYB_BACKUP. The name can be changed later with sp_addserver.
* We will not use a PCI (Pluggable Component Interface) database. This database is mainly used for Java in the server.
* We will not use the optimize configuration feature now. When trying to use this feature through sqlsrv.do_optimize_config: yes can expect the error “Unable to find SQL file to run optimization task”. Configuration of the server will be done at a later stage.

Now build the server with the command:
srvbuildres -r ASE1.rs

When the srvbuildres command fails with:
Task failed
Unable to boot server 'ASE1'.
Server 'ASE1' was not created.
or
Optimizing server configuration...
Unable to find SQL file to run optimization task.
Task failed
then go to this page for tips how to solve this: [[srvbuildres task failed]].

After a successful creation you can connect to the server using the command line tool isql. At the prompt of isql you can type a command or execute a stored procedure. Type exit to exit.
isql -Usa -P -SASE1
sp_helpdb
go
exit

==Basic ASE configuration==

===Move and modify configuration files===
In this step we will move the configuration files to the admin directory. First shutdown ASE

isql -Usa -P -SASE1
shutdown
go

cd $SYBASE/$SYBASE_ASE
mv ASE1.cfg /opt/sybase/admin/ASE1
rm ASE1.*
cd install

Modify RUN_ASE1 and make the following changes:
* Make the -s flag the first option. This has the advantage that a listing of running processes on the system will show the name of the Sybase server.
* Change the location of the configuration files, as specified with the -c flag, to the admin directory
Each line must end with a backslash.
Example RUN_ASE1 file:

#!/bin/sh
#
# ASE page size (KB): 2K
# Master device path: /var/sybase/ASE1/master.dev
# Error log path: /opt/sybase/admin/ASE1/errorlog
# Configuration file path: /opt/sybase/ase155/ASE-15_0/ASE1.cfg
# Directory for shared memory files: /opt/sybase/ase155/ASE-15_0
# Adaptive Server name: ASE1
#
/opt/sybase/ase155/ASE-15_0/bin/dataserver \
-sASE1 \
-d/var/sybase/ASE1/master.dev \
-e/opt/sybase/admin/ASE1/errorlog \
-c/opt/sybase/admin/ASE1/ASE1.cfg \
-M/opt/sybase/ase155/ASE-15_0 \

Now restart the Sybase server
./startserver -f ./RUN_ASE1

===Load instmsg.ebf file===
The instmsgs.ebf file contains the latest update for Sybase error messages. You can load it with isql.
isql -Usa -P -SASE1 < $SYBASE/$SYBASE_ASE/scripts/instmsgs.ebf

===Configure the server using optase.sh===
With the tool optase.sh you can configure your server. You need to supply the following information:
* The amount of memory that the Sybase server can use. A rough guideline is approx 80 % of total physical memory but lower the value if your other servers or application software at the same time.
* The number of CPU's that the Sybase server can use. Again, leave a safe margin for other processes and the operating system.
* The pagesize of the ASE server, as specified in the ASE1.rs file. When you have used the default page size it will be 4Kb.
* Type of product edition. Some ASE features are not available in certain editions.
* Application type, as specified in the ASE1.rs file.
You can run optase.sh and have you prompted per item, or supply all the required information when you start the tool.
Example
$SYBASE/$SYBASE_ASE/bin/optase.sh 1024 2 2 EE MIXED /tmp/config.sql

When done, run the configuration script.

isql -Usa -P -SASE1 -i /tmp/config.sql

Then reboot the ASE server, stop it first
isql -Usa -P -SASE1
shutdown
go

Start again
startserver -f $SYBASE/$SYBASE_ASE/install/RUN_ASE1

==Create the backup server==
Create the backup server using the following steps.

Go to the "/opt/sybase/admin/SYB_BACKUP" directory and create a resource file. Use the following template as an example, call it SYB_BACKUP.rs. You can also use the Sybase supplied resource file, it's $SYBASE/$SYBASE_ASE/init/sample_resource_files/srvbuild.backup_server.rs

sybinit.release_directory: USE_DEFAULT
sybinit.product: bsrv
bsrv.server_name: SYB_BACKUP
bsrv.new_config: yes
bsrv.do_add_backup_server: yes
bsrv.do_upgrade: no
bsrv.network_protocol_list: tcp
bsrv.network_hostname_list: prd-syb-ase2
bsrv.network_port_list: 4001
bsrv.language: USE_DEFAULT
bsrv.character_set: USE_DEFAULT
bsrv.tape_config_file: USE_DEFAULT
bsrv.errorlog: USE_DEFAULT
sqlsrv.related_sqlsrvr: ASE1
sqlsrv.sa_login: sa
sqlsrv.sa_password: USE_DEFAULT
#bsrv.addl_cmdline_parameters: PUT_ANY_ADDITIONAL_COMMAND_LINE_PARAMETERS_HERE

Build the server with the command:
srvbuildres -r SYB_BACKUP.rs

===Move and modify configuration files===
In this step we will move the configuration files to the admin directory. First shutdown the backup server:
isql -Usa -P -SASE1
shutdown SYB_BACKUP
go
exit

Modify the RUN_SYB_BACKUP, located at $SYBASE/$SYBASE_ASE/install. Change the location of the log file,as specified with the -e flag. Change it to the admin directory. Also, move the line with the -s flag to the top. Sample RUN_SYB_BACKUP:
#!/bin/sh
#
# Error log path: /opt/sybase/ase155/ASE-15_0/install/SYB_BACKUP.log
# Maximum number of network connections: 25
# Maximum number of server connections: 20
# Multibuf executable path: /opt/sybase/ase155/ASE-15_0/bin/sybmultbuf
# Backup Server name: SYB_BACKUP
#
/opt/sybase/ase155/ASE-15_0/bin/backupserver \
-SSYB_BACKUP \
-e/opt/sybase/admin/SYB_BACKUP/SYB_BACKUP.log \
-N25 \
-C20 \
-M/opt/sybase/ase155/ASE-15_0/bin/sybmultbuf \

Remove the old log file
rm $SYBASE/$SYBASE_ASE/install/SYB_BACKUP.log

Restart the backupserver
startserver -f $SYBASE/$SYBASE_ASE/install/RUN_SYB_BACKUP

==Create a user database==
disk init name = data01,size="1G",physname="/var/sybase/ASE1/data01.dat"
go
disk init name = log01,size="100M",physname="/var/sybase/ASE1/log01.dat"
go
create database my_database on data01="1G" log on log01 = "100M"
go
==All done==
Happy Sybase-ing with your new server!!

Further reading:

Installatin guide for Linux http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc30119.1550/doc/html/title.html

Sybase System administration http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31654.1550/html/sag1/title.htm

[[Category:ASE]]
[[category:Installation Guidelines]]

Add a replicate table to an existing replication server

2010-10-06T12:57:29Z

Psap: Created page with 'With the replication server setup as described at this page Warm Standby setup instructions it is easy to add a replicate table to it. Follow these steps. ==Mark the table t…'

With the replication server setup as described at this page [[Warm Standby setup instructions]] it is easy to add a replicate table to it. Follow these steps.

==Mark the table that hold the primary data for replication==
On the primary ASE that manages the data for the warm standby setup you need to mark each table for replication. For instance, when you want to replicate data changes for a table called ‘myTable’ run this command:

use <database>
go
sp_setreptable myTable,"true"
go

==Prepare the replicate database==
If not already there, create a Sybase ASE server and a database that will be used to store the replicate table.

This page assumes that the tables on the replicate ASE are created identical as those on the primary ASE.
When done, you need to run a Sybase supplied script to create some extra tables needed by replication server. The script is called rs_install_replicate.sql and is located at $SYBASE/$SYBASE_REP/scripts. Example how to load the script:

isql -U<login> -P<password> -S<server> -D<database> < $SYBASE/$SYBASE_REP/scripts/rs_install_replicate.sql

==Create the maintenance user on the replicate ASE==
Create a login on the replicate ASE that will be used by replication server to maintain the data. It is the maintenance user.
sp_addlogin "<maintenance user>","<maintenance_user_password>"
go
use <database>
go
sp_addalias "<maintenance user>",”dbo”
go

==Check interfaces file==
Make sure that the interfaces file of the replication server has an entry to connect to the replicate ASE.

==Create a connection==
Create a connection from the replication server to the ASE server and database.
create connection to <replicate ASE>.<database>
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to <maintenance user>
set password to '<maintenance_user_password>'
go

==Create replication definition==
Create replication definitions for the table, see example below.
create replication definition RD_myTable
with primary at L1.peter
with all tables named 'myTable'
("sequ" identity,
"dt" datetime)
primary key("sequ")
replicate minimal columns
go

==Create subscription==
Create a subscription for the table
create subscription A5_myTable
for RD_myTable
with replicate at A5.peter
go

That’s all.
[[Category:RepServer]]