How to install "Interactive SQL"

We sometimes confuse two different pieces of software:

• Interactive SQL which supports Sybase
• SQL Central which supports SQL Anywhere

To connect to SAP ASE / Sybase  you need to have "Interactive SQL", not SQL Central.

There is no separate download of Interactive SQL, or at least I'm not aware of such a package. What you need to have is the full installation of the package, however you don't need to install the entire Sybase suit on your windows machine. During the installation only the client tools should be selected.

To download SAP Adaptive Server Enterprise in Evaluation or Developer edition you need to register on SAP website and ask for the download link. This can be done using:

https://www.sap.com/cmp/syb/crm-xu15-int-asewindm/index.html?url_id=text-us-sapcom-ase-trial-software

As internet is not static, with time this link may stop working and you will need to look for the downloads using a web search engine. This is ... politics of SAP.  See also:

https://archive.sap.com/discussions/thread/3565945

This is inconvenient, specially when you are playing with Linux/Unix installation. Usually one of the final steps is to configure client, so that it can work with the server. When you install server you usually download only the Linux/Unix Sybase install files. So in such case you should download the Windows install files and from it I installed the Windows Sybase client.

Those files should be available in the SDK FOR SAP ASE 16.0

Sybase 16/ SAP ASE Installation and Configuration online course

While building the blog something nice has been created. This is an online course dedicated to installation and initial configuration of Sybase 16 / SAP ASE.

Using this link you can have it in an 80% lower price:
https://www.udemy.com/sap-ase-16-sybase/?couponCode=SAPDB10

Below a couple of words about the course and some example movies. Have fun:

Sybase 16 / SAP ASE Installation and Configuration

• Preparing machine for Sybase installation
• Linux installation
• System requirements for Sybase installation
• Sybase 16 installation
• Starting and stopping Sybase
• Post installation configuration:configuring Sybase for automatic start, configuring network interface
• Configuration files, scriptes, binaries, errorlogs
• Configuring Sybase

This course is designed for  administrators beginning their adventure with Sybase database. We assume, you don’t know Sybase and you didn’t work too much with Linux. It is good if you have some experience with other database systems, however even without that knowledge you should still be able to understand the course.

 

Sybase database is known as well as  SAP Adaptive Server Enterprise. It is used in very specialized implementations, where stability and performance counts the most.

Course begins with some practical manuals allowing to build your own test environment. The next phase is Linux installation and it’s initial configuration, so you should be able to install Sybase in next steps. Installation finishes with the test procedure allowing to verify whether the server is correctly installed.

In the following lessons it will be presented what configuration files Sybase uses. You will also develop your own script allowing to start and stop the SAP ASE server.  You will learn how to configure the operating system to start and stop the database automatically when server boots.
In next lessons you will learn about configuration files like: interfaces file, network interface configuration. During this process you will see how to solve some problems that may appear during installation.  You will get familiar with the errorlog and sp_configure procedure.

This course is available only in English. Each lesson ends with a short quiz and a lab.

Sybase Course content:

•  How to prepare virtual machine for the course
• How to install Linux on it
• How to configure Linux for SAP ASE installation
• How to install SAP ASE
• How to validate the installation
• How the start-up procedure works
• How to start/stop ASE, how to configure it to automatic start/stop
• What environmental variables are used by ASE
• Which files are the most important, what kind of information they contain and how and when it is used
• How to configure ASE to listen on external network interfaces
• How to troubleshoot some common problems with installation or configuration
• How to install Interactive SQL on Windows
• How to work with sp_configure and how to work with configuration files

Example video

 

 

 

 

  

Courses can be run on iPhone, iPad and Android as well!

Activate your course in the browser. Next install a dedicated Udemy application on your mobile device. Go to „My courses” and learn whenever and whereever! If the Internet connection is a problem for you, the course can be downloaded for offline watching (only in mobile application)

Granting access to database (user or guest)

It is not enough to create a login to have access to the database. Each login requires additionally a user in the database where the login should have access to.  You can compare it to entering a building and an office in the building. Login allows only to enter the building and some public accessible places (in Sybase those "public places" are master and tempdb databases). To enter an office (user database) you need additionally to have user account.

So to create the user in the database MyDb for a login named myAppLogin you just issue command like this:

USE MyDb
sp_adduser myAppLogin

Starting from now the login myAppLogin can start using the database. The user can be removed using the following code:

USE MyDb
sp_dropuser myAppLogin

What is very important - you cannot remove a user when it is "in use", so there is an active session in the database using this user, however when the session was not found, the user will be removed.

Creating new login. Locking and unlocking the login

To gain access to the Sybase, you need to have a valid login. To create a login for your application type:

sp_addlogin myAppLogin, 'Passw0rd', MyDb, null, 'Application ABC', null, 8, 3

The parameters of the commands are:

• name of the login
• the password
• the name of a default database (warning: if you create a login with the default database, the user in the database is not created automatically. This step needs to be done manually)
• default language
• the description of the account
• time (in days) after that the password will expire. Leave null to have a never expiring password
• number of chars that the password should have. This is important for future password changes that will be done by the login
• number of failed logins, after which the account will be locked

After the login has been created you can test in syslogins whether the information about a new login should be available.

Sybase security fundamentals: logins vs users

Sybase and MS SQL are like an older and younger sisters, so if you know how the access in organized on one of the platforms, you know immediately how it in general works in another database system.
So, starting from beginning:

• To access the Sybase’s server databases you don’t need direct access to operating system where the server is installed, nor the files, where the database’s files are stored. The reason for this is, that Sybase server just listens on requests from clients and then gets data from the system, however this is done by the Sybase processes, not directly by the client.
• To connect to Sybase, you need to have a valid login. This login can be a locally created login, so just user name with password, but this login can be taken from Kerberos, LDAP or PAM. Usually dba’s decide to use the locally defined logins, but using external directory of users have huge advantages.

USE master
GO
SELECT * FROM syslogins

• The login alone is not enough. To access a specific database you need to have a user account as well. The user account is created per database and you can think about this like about an association of a login name with a database. If the login has such association, than is allowed to access the database. We say in such case that the login has a user in the database.

Making external backups: QUIESCE DATABASE

One of more advanced backup options in Sybase is, that Sybase supports making database dumps using external tools. Generally the problem should not be very complicated. Sybase's databases are at the end of it's implementation just files on disk! However....:

• When you copy database files all of them need to be from exactly the same time, and it will be very complicated/impossible to copy hundrets of Gigabytes in the same moment
• All the transactions should be hold in the time when the external backup is run

That is why in Sybase there is a command allowing to freeze all the transactions, so that the files can be easily copied using the external tool. Sybase has found a way this conditions can be met - just all the transactions should be frozen for the time of external copy process. In the same time data can be read, however if you decide to initiate the transaction, that this transaction will hang until the freeze will be finished. Unfortunately since that time also all read operations will hang waiting for the freeze end. 

Take a look at this example:

QUIESCE DATABASE Maintenance1 HOLD MyDb
GO
USE MyDb
GO
SELECT * FROM TEST
GO
INSERT TEST VALUES('A new value')

Here administrator plans to make a copy of MyDb files. So he initiates quiesceing of that database. Such operation needs to have a name, that is here Maintenance1. After this operation INSERT will stop working. The first SELECT will be executed with no additional delay, but the INSERT will hang.

In the same time in a second session similar activities can be initiated:

USE MyDb
GO
SELECT * FROM TEST
GO

In this example, the SELECT will hang as well!. That is caused by a still not executed insert from the first session:

However, when the maintenance will be finished with a command like:

QUIESCE DATABASE Maintenance1 RELEASE
GO

All the other frozen INSERTs or SELECTs will be executed in the correct order:

Such copied files can be later taken to another server, put into correct location, and when data_server will be started with the -q option, than all the quiesced databases will be treated as "in recovery" allowing to:

• restore them
• or
• leave as "in restore" and with time apply additional log dumps, making the second server up-to-date.

Sybase cumulative/incremental dump and load

There is one option named 'allow incremental dumps', by default not set, that would allow to perform incremental backups of a database.

Incremental backup contains only datapages that were modified since the last full database backup. This allows to build more compact recovery scenarios. For a huge database, where users usually don't perform huge updates, it may be easier to perform full backup once a week and an incremental backups everyday.. If required the transaction dumps can be done as usually.

Let's examine an example of incremental backup. In the code below: we create the devices and database, change option allowing incremental dumps for the database and perform one full backup, two incremental and finally one log dump:

USE master
GO

DISK INIT NAME ='myDevice_A', 
          PHYSNAME = '/opt/sap/databases/myDevice_A.dat', 
          SIZE=10240
GO

DISK INIT NAME ='myDevice_B', 
          PHYSNAME = '/opt/sap/databases/myDevice_B.dat', 
          SIZE=2560, 
          SKIP_ALLOC = true
GO

CREATE DATABASE myDB
ON myDevice_A = '20M'
LOG ON myDevice_B = '5M'
GO

sp_dboption 'myDB','allow incremental dumps', 'true'
GO

USE myDB
GO

CREATE TABLE T1(id INT)
GO

INSERT T1 VALUES (1)
GO

DUMP DATABASE myDB TO '/home/dumps/01_myDB_full.bak' 
GO

INSERT T1 VALUES(2)
GO

DUMP DATABASE myDB CUMULATIVE TO '/home/dumps/02_myDB_cumulative.bak'
GO

INSERT T1 VALUES(3)
GO

DUMP DATABASE myDB CUMULATIVE TO '/home/dumps/03_myDB_cumulative.bak'
GO

INSERT T1 VALUES(4)
GO

DUMP TRAN myDB TO '/home/dumps/04_myDB_tran.bak'
GO

USE master
GO

Now we will try different restore scenarios. The first just restores the full backup. No surprise, only one record will be visible in the restored table.

Log backup after catastrophic failure

The theory says, that a database admin should always have up to date backups... unfortunately this is not always true. It is possible that you have full database backup, you perform transaction backups every 3 hours, you even have the backup and recovery plan, but... Sybase due to some hardware failure stopped working and you cannot start it and the business waits and waits...

There is a utility called sybdumptran that allows to make a transaction log backup despite the non working sybase server.

Take a look at the following SQL code:

DISK INIT NAME ='MyDbDataDev', 
     PHYSNAME = '/opt/sap/databases/MyDbDataDev.dat', 
     SIZE=10240
GO

DISK INIT NAME ='MyDbLogDev', 
     PHYSNAME = '/opt/sap/databases/MyDbLogDev.dat', 
     SIZE=2560
GO

CREATE DATABASE MyDb
ON MyDbDataDev = '20M'
LOG ON MyDbLogDev = '5M'
GO

DUMP DATABASE MyDb TO '/home/dumps/MyDb_full.dmp' WITH INIT
GO

USE MyDb
GO

CREATE TABLE TEST(info char(30))
GO

INSERT INTO TEST VALUES('Before the 1st log dump')
GO

DUMP TRAN MyDb TO '/home/dumps/MyDb_log_1.dmp'
GO

INSERT INTO TEST VALUES('Never dumped')
GO

SHUTDOWN

As you see a new database on new devices has been created. Next we took a full backup, next user inserted one record, next the transaction low was dumped, next the user continued to insert the next record and then something went wrong and server stopped! Now you will see how to take a log dump despite stopped server:

More and more tempdb problems

Recently I have played with tempdb. I have practiced a lot of scenarios with different tempdb configuration. This caused of a big mess in the configuration as I needed to perform some activities that sometimes failed and required quick repair.

So finally I had a couple of devices used by tempdb (sometimes even non-existing) and I wanted to make my Sybase server be running again.

The first error was:

Device 'tempdb2data' (with physical name '/opt/sap/databases/tempdev2data.dat', and virtual device number 13) has not been correctly activated at startup time.  Please contact a user with System Administrator (SA) role.
server  Error: 1619, Severity: 21, State: 1
server  Could not open TEMPDB, unable to continue.

Cache: working with cache

In this post I will present the sp_cacheconfig procedure. Knowing it, in the next posts I will be able to show two common usages of the cache in the Sybase.

This command allows to define how the cache will be used. Yes, cache can be used also without knowing how this procedure works, as always blocks read from the disk will go into cache allowing to speed up future read operations. But having knowledge about this procedure you are able to design, how the memory in the cache will be used

The syntax of sp_cacheconfig is following:

sp_cacheconfig [cachename [, "cache_size[P | K | M | G]"] 
 [, logonly | mixed | inmemory_storage][, strict | relaxed]]
 [, "cache_partition=[1 | 2 | 4 | 8 | 16 | 32 | 64]"]
 [, instance instance_name]

As you can see, this command can be started without parameters. In such case only the definition of cache will be displayed. When you want to define the cache, then you should use at least two parameters:

• cache name - this is the name allowing you to reference this cache in future
• cache size - defining the size of the cache

The other parameters define how the cache will work, one of them is the most important in our case:

• logonly or mixed or inmemory_storage and the inmemory_storage is especially interesting. It will be extensively used in future examples. This type of cache allows to store the database in memory!

So for example to have a cache of size 100 MB of memory, that could be used by databases or to be more accurate to save database in memory following command should be used:

sp_cacheconfig inmem_db_cache, "100m", inmemory_storage

to display configuration use

sp_cacheconfig

If you would like to remove configuration - just set its size to );

sp_cacheconfig inmem_db_cache, "100m"

In next posts I will show the cache in 2 scenarios:

• additional temporal database in memory
• disk database preloaded into memory

Linux: How to create RAW device to be used with Sybase?

When using Sybase on Unix, than the recommended way of saving the databases is directly on disk devices and not on files (what is possible as well). There are two types of disk devices on Linux.

One is called a block device and applications use that device using block access. This means that data is read into cache and given to application in blocks. Example of a block device can be: /dev/sda2. If you issue command ll than in the result you can find, that the type of the device is "b" - block:

[root@SAP01 /]# ll /dev/sda2
brw-rw----. 1 root disk 8, 2 Jul  1 08:11 /dev/sda2

However block devices are not recommended for databases. Database should avoid the system cache. The system cache is good for normal files and in case of database, that is the database engine who should manage the caching of data. 

Unfortunately, on Linux (Fedora) the raw devices are not created automatically. So let's review a couple of commands that may help working with pure raw devices:

raw -qa

displays information regarding existing raw devices. If you did not create them, than initailly the result will be empty.

[root@SAP01 /]# raw /dev/raw/raw2 /dev/sda2
/dev/raw/raw2:  bound to major 8, minor 2

This command creates a raw device bound to the block disk device /dev/sda2. Note the arguments:

• the first one is the name of device that will be created and it must point to /dev/raw/ and must begin with raw and must end with a number
• the second is the existing block device

After issuing the command:

[root@SAP01 /]# raw -qa
/dev/raw/raw2: bound to major 8, minor 2

you will see information that now a new raw device is available. This device could be used by Sybase. Please note, that if a block device and raw device would be used simultaneously, than the data may be/ will be corrupted. Use only one type of a physical disk device a time.

More info regarding raw command:
http://www.tldp.org/HOWTO/SCSI-2.4-HOWTO/rawdev.html

Error when connecting from isql to Sybase

When you try to connect to Sybase / SAP ASE following error may be raised:

CT-LIBRARY error:
        ct_connect(): network packet layer: internal net library error: Protocol driver call to parse connection information failed"

All those issues are described in this online Sybase course, but if you are looking for the immediate help  continue reading,  what can be cause the error?

Environment may be not setup

(see lesson Sybase Environmental Variables - VIDEO)

For Sybase tools to work, the environment needs to be setup. This means that environmental variables needs to be present. To test if you have those variables may look like that:

echo $SYBASE

This should return a path to installation directory. If nothing is returned issue as sap user:

.  ./SYBASE.sh

(note the additional dot at the beginning). This will define all the settings and will cause that commands will know where and how to connect, so maybe your problem would be solved.

sqsh - smart replacement of rough isql

isql is a tool allowing to work with Sybase from command line, but it is not a very convenient tool. It is not possible to use history of commands and even command line editing doesn't work! Fortunately there are still hard-working programmers,who for free develop great tools!

This time I want to write about sqsh that can be downloaded from: https://en.wikipedia.org/wiki/Sqsh

The most important features that I like very much are:

• command line editing (you can use left & right arrows)
• history, that is saved even between sessions
• executing the commands after ";" (you don't have to type "go" - just end the query with ";" and it will run)
• compatibility in the scope of options with isql

The only part that makes some problems is, that to have it working you need to install it and the installation must be done starting with source code (BTW - this is also an advantage)

Installation step by step with all the problems that I have met:

Copy the sources and un-tar them:

tar xvf sqsh-2.5.16.1.tgz

This command will create a folder with source code. Change directory to it and execute a configure command:

./configure

In my case this resulted with error message:

configure: error: no acceptable C compiler found in $PATH

See `config.log' for more details

So I needed to read the config.log file. I have found entry like:

## ----------- ##

## Core tests. ##

## ----------- ##

configure:2244: checking build system type

configure:2258: result: x86_64-unknown-linux-gnu

configure:2278: checking host system type

configure:2291: result: x86_64-unknown-linux-gnu

configure:2467: checking for gcc

configure:2497: result: no

configure:2560: checking for cc

configure:2607: result: no

configure:2663: checking for cl.exe

configure:2693: result: no

configure:2717: error: in `/opt/sap/sqsh-2.5':

configure:2719: error: no acceptable C compiler found in $PATH

See `config.log' for more details

Ok, so I needed to install compiler. I have found firt available compilers:

dnf search gcc

selected one of them and installed:

dnf install gcc.x86_64

Now I could to proceed with configure:

./configure

Unfortunately this ended with error again:

checking Open Client installation... no

configure: error: Unable to locate Sybase installation. Check your SYBASE environment variable setting.

Sybase system tables

To develop scripts that automate the administration, we need to use system tables. They contain information like list of devices, databases, logins, objects, messages and much more. Some of them are specific to the server like sysdatabases the other are specific to databases like sysobjects or sysprocedures.

There is a nice poster presenting the diagram of all of those system metadata tables. Click here to download it  here http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc70204.1550/pdf/a155pst.pdf

Thank you Kasia for sharing!

Error: ninit: bind, Cannot assign requested address

When Sybase starts than it binds to network interfaces as defined in master entires of interfaces file under the server name. For example for the server SAP01, the colored entries determine network interfaces where sybase will bind:


SAP01
        master tcp ether localhost 5000
        query tcp ether localhost 5000
        master tcp ether 192.168.137.100 5000

SAP01_BS
        master tcp ether localhost 5001
        query tcp ether localhost 5001


SAP01_XP
        master tcp ether localhost 5002
        query tcp ether localhost 5002


SAP01_JSAGENT
        master tcp ether localhost 4900
        query tcp ether localhost 4900

However sometimes network was not ready when server was started and than following entries can be started in the errorlog:

network name localhost, interface IPv4, address 127.0.0.1, type tcp, port 5000, filter NONE
network name 192.168.137.100, interface IPv4, address 192.168.137.100, type tcp, port 5000, filter NONE
ninit: bind, Cannot assign requested address

Probably server restart will solve the issue, however you may decide to use an online operation:

Start following commands:

sp_listener status
GO

sp_listener start, '192.168.137.100:5000'
GO

sp_listener status
GO

The 'status' procedures just display the interfaces where sybase is listening. The 'start' command performs the binding operation. Values of IP and port should be the same as in the interfaces file.

In the same time in the errorlog following successful message should be recorded:

network name 192.168.137.100, interface IPv4, address 192.168.137.100, type tcp, port 5000, filter NONE
Listener with protocol tcp, host 192.168.137.100, port 5000 started.

Funny Sybase Fact - Boot Count

In the Sybase error log you can find information indicating how many times the server has been restarted since the installation. I have never seen this before. I also remember that many errors are generated, because the number a programmer wanted to save in the variable was too big in comparison  to the variable type. So I started if this can bring some bad consequences. Will my servers ever breach the maximal value that can be stored in Sybase? What will happen then? Will server stop working or only the counter will start from 0 again? Should we be afraid like about Millennium Bug in 2000 or not?


The line looks like:

The displayed here Boot Count value has type unsigned int, so the maximal value is
4 294 967 296

This causes some limitations to number of restarts:

• if you perform restart every day than you are allowed to do this only 11 767 033 years
• if you perform restart every hour than you are allowed to do this only 490 293 years
• if you perform restart every minute, than you are allowed to do this only 8 171 years

Do you see any problems with this? Can you advise?

Startup script to run Sybase ASE automatically after unix booting

Unfortunately after installation when the server is rebooted, Sybase is not starting automatically. The  problem is, that Sybase installation is done using a non privileged account, so there is no way to modify system files starting Sybase automatically.

Below you can find a script, that can be used to automatically manage Sybase on Linux machine and it can be probably also used on other Unix machines. You can call it with start or stop or restart parameter, so this is a good candidate to use it with every systemd compatible operating system.

If you would like to have Sybase starting after each OS boot perform following configuration. Create a service file and save it as /etc/systemd/sysctl/sybase.service. If required change the path to point to correct location of the scrip sybase_start

[Unit]
Description=SYBASE ASE
After=network.target

[Service]
ExecStart=/opt/sap/sybase_start start
Type=forking
PIDFile=/opt/sap/sybase.pid
ExecStop=/opt/sap/sybase_start stop

[Install]
WantedBy=multi-user.target

Recovering failed master database

As always, to recover a server, you need to have a backup!

USE master
GO
DUMP DATABASE master to '/home/dumps/master.dmp'
GO
SHUTDOWN

Here we additionaly have stopped the server to make it a little destroyed... Here you can check where the masterdevice exists and how to change its name:

[sap@SAPSybase install]$ ls
core.jsa_20161016_210528.1147  core.jsa_20170219_172003.1157  core.jsa_20170309_224222.1180  RUN_SAP01_BS       SAP01.log   veritas
core.jsa_20170219_121910.1154  core.jsa_20170219_173024.1170  core.jsa_20170327_022202.1180  SAP01_BS.log       showserver
core.jsa_20170219_170522.1147  core.jsa_20170219_180321.1181  RUN_SAP01                      SAP01_JSAGENT.log  sybhauser
[sap@SAPSybase install]$ more RUN_SAP01
#!/bin/sh
#
# SAP ASE page size (KB):       4k
# Master device path:   /opt/sap/data/master.dat
# Error log path:       /opt/sap/ASE-16_0/install/SAP01.log
# Configuration file path:      /opt/sap/ASE-16_0/SAP01.cfg
# Directory for shared memory files:    /opt/sap/ASE-16_0
# License properties file:      /opt/sap/ASE-16_0/sysam/SAP01.properties
# Interfaces file directory:    /opt/sap
# SAP Adaptive Server Enterprise name:  SAP01
#
/opt/sap/ASE-16_0/bin/dataserver \
-d/opt/sap/data/master.dat \
-e/opt/sap/ASE-16_0/install/SAP01.log \
-c/opt/sap/ASE-16_0/SAP01.cfg \
-M/opt/sap/ASE-16_0 \
-N/opt/sap/ASE-16_0/sysam/SAP01.properties \
-i/opt/sap \
-sSAP01 \

[sap@SAPSybase install]$ ls /opt/sap/data
empty  master.dat  sybmgmtdb.dat  sybsysdb.dat  sysprocs.dat  tempdbdev.dat
[sap@SAPSybase install]$ mv /opt/sap/data/master.dat /opt/sap/data/master.dat.org
[sap@SAPSybase install]$ ls /opt/sap/data
empty  master.dat.org  sybmgmtdb.dat  sybsysdb.dat  sysprocs.dat  tempdbdev.dat

Of course now Sybase cannot start:

Restore database form a damaged media

In the following example you will see how to recover from a damaged media failure. Let's assume, we have a database named A1 with one database file and one log file. The files are located on different devices (in our case on two separate disk files).

USE A1
GO

SELECT * FROM TestTran
GO

DUMP DATABASE A1 TO '/home/dumps/A1_Full.dmp'
GO

INSERT INTO TestTran (SomeText) VALUES ('The transaction that was not backed up')
GO

SHUTDOWN

The table TestTran contained already 3 rows. Here additional new row has been added, but it was never dumped neither to a full database dump, nor to log dump. After that server was stopped. This will allow us to perform some destructive activities on the database file, however in normal situation this could be a disk failure.

Now in operationg system let's rename the data file for the database. Finally the files of the database devices are like here:

[root@SAPSybase]# ll /opt/sap/databases/a1
total 67584
-rw-r-----. 1 sap sap 62914560 Apr 23 16:49 a1.old.mdf
-rw-r-----. 1 sap sap  6291456 Apr 23 16:48 a1.trn

After this damage Sybase can be started again:

cd /opt/sap/ASE-16_0/install
../bin/startserver -f ./RUN_SAP01

Already during the startup, and also in the error log following error will be displayed:

Sybase ASE dump history

This is good to have backup, but it is also good to know which backups you have! By default the backup history is not enabled in Sybase, so immediately execute:

sp_configure 'enable dump history', 1

This is an  online operation, so no restart is required. Having the option "on", after some backups check the following query:

sp_dump_history @operation = 'list', @database_name = 'ALFA'

The output can  be similar to:

After enabling the history, consider having also a job that purges the old backup history records. For this @operation should be equal 'purge'. The history is saved in a file. The path to the file is returned by @operation listfiles. This is just a text file that can be dispalyed also using the operating system commands:

[root@SAPSybase ASE-16_0]# cd /opt/sap/ASE-16_0
[root@SAPSybase ASE-16_0]# more dumphist

Result is like:

Loading transaction dumps. Point in time restore

The load operation of log files is not so boring as the dump operation, because:

• during the load operation, the correct sequence of dump files needs to be loaded
• the databases to be loaded, needs to be created first, and it's size must be big enough to accommodate the backups that should be restored
• the restore can stop at some, chosen by you date and time
• the last command during the restore is to ONLINE the database

Let's take look at the following example:

USE ALFA
GO

CREATE TABLE TestTran
(
   id INT IDENTITY PRIMARY KEY,
   SomeText NVARCHAR(300),
   Date DATETIME DEFAULT GETDATE()
)
GO

DUMP DATABASE ALFA TO '/home/dumps/alfa_full.dmp' WITH INIT
GO

INSERT INTO TestTran (SomeText) VALUES ('This is the first transaction')
GO
DUMP TRAN ALFA TO '/home/dumps/alfa_log1.dmp'
GO
SELECT * FROM TestTran
GO

INSERT INTO TestTran (SomeText) VALUES ('This is the second transaction')
GO
--wait a moment, remember the current time (in between the inserts)
SELECT GETDATE()
GO
INSERT INTO TestTran (SomeText) VALUES ('This is the third transaction')
GO
DUMP TRAN ALFA to '/home/dumps/alfa_log2.dmp'
GO

SELECT * FROM TestTran
GO

DUMP TRANSACTION - command and options

All the transactions before being saved in the database, first need to be put in the transaction log. Of course with time the transaction log would fill up, so the old, closed transactions need to be removed from the log. To do this you can choose one of the solutions:

• set database option 'trunc log on chkpt', what is good on test systems, but not in production. Why? Because on test systems we don't care about the data itself, while on production we would like to have possibility to restore the database to any point in time, so we need to have all the transactions somewhere. In case the option 'trunk log on chkpt' has been set, when Sybase saves data in the database (checkpoint finishes) all the transactions already saved and closed by users can be and are removed from the log. So the log can fill up only if someone started a really long or huge transaction.
• from time to time perform log dump, and monitor the space in the logs. 

The syntax for log backup is:

dump tran <db_name> to <device_name>
[, stripe on <device_name>...]
[with <options>]

This command usually takes all the transactions committed already by the user and saves them on the device. As the data is already saved somewhere it can be removed from the transaction log, making space for other transactions. In case the transaction log would not be executed on time, the log can become full and all the transactions may be suspended or rolled back depending on database option named  'ABORT TRAN ON LOG FULL', what was already described in Sybase log space exhausted, transactions frozen -what to do?

Let's take closer look to the options that can be added to the dump transaction command

dump tran db_name with truncate_only

This command causes the log being truncated, without being actually backed up! Use it only if you don't care about the data. You can also try this command when you have problem with the full log in the database. After truncating the log, checkpoint will be written to the log, so if you have problem with full log, this command may fail. You don't need to specify the device name, as the backup will not be done. Remember to perform new full backup of the database, after this operation, as the backup chain will be broken at this moment.

Sybase ASE database option "abort tran on log full" example

There is database option named "abort tran on log full". When the option is set to true, when the transaction log segment fills up, the transaction that cannot write to transaction log will be aborted. However when this option is not set, the transaction will be suspended and will wait until the space in the log will be available again.

This setting can be displayed using following command:

sp_helpdb 'Beta'

The result may be similar to:

to change it issue:

master..sp_dboption 'Beta', "abort tran on log full", false

Now the options should change to:

The error message recorded in the errorlog in case of filling up the log would look like this:

00:0006:00000:00033:2017/03/26 09:51:53.20 server  Space available in segment 'logsegment' has fallen critically low in database 'Beta'. All future modifications to this database will be aborted until the transaction log is successfully dumped and space becomes available.

That is difference to previous messages recorded in response to the same event in case, when the "abort tran on log full" would be set to false:

00:0006:00000:00007:2017/03/19 12:49:44.92 server  1 task(s) are sleeping waiting for space to become available in the log segment for database Beta.

Last-chance threshold

As discussed in previous article, a long database may cause the log to fill up. Then depending on database settings, the transaction causing problem can be aborted (terminated or rolled back) or it can cause that the entire database will be frozen. All transactions will wait until again some free space will be available in the log. Having this in mind, Sybase has mechanism called Last-Chance Threshold. Shortly before filling up the entire log, in the moment when the transactions are aborted or suspended, Sybase starts procedure sp_thresholdaction.

This procedure by default is not present on the system. It needs to be created manually by the administrator. Usually the procedure should create additional TRANSACTION backup, so some space in the log will be made free again and the transactions should be able to continue.  But generally the body of the procedure can contain any actions.

DBA may decide to create more thresholds that will be called depending on conditions met in the log, but there is only one threshold known as "Last Chance".  An example of such procedure below:

USE sybsystemprocs
GO

CREATE PROCEDURE sp_thresholdaction
        @dbname VARCHAR(30),
        @segmentname VARCHAR(30),
        @space_left INT,
        @status INT
AS
    IF @status=1 --last chance thershold
    BEGIN  
        PRINT 'Last threshold condition met...'
        DUMP TRANSACTION @dbname TO dumpsdev
    END
GO

Sybase log space exhausted, transactions frozen -what to do?

The main reason for the log file existence is logging all the user transactions incoming to the database. Every transaction needs to go through the transaction log. The main reason of troubles related to log is a full log condition. Indeed, if the log is full, we cannot execute any transaction and all the activity needs to be halted. The log should be big enough to accommodate all the transactions. Transaction can be stored longer or shorter depending on the settings of database

• if the database has option 'truncate on checkpoint', whenever transaction has been finished, after a CHECKPOINT, when all the modified by this transaction pages have been saved in the database, the log entries are not longer required
• if the option is not set, then first a backup of the log needs to be taken. After transaction has finished, and dbo has performed the backup of the log, the space in the log can be released.

Let's consider following example:

CREATE DATABASE Beta ON
BetaDataDev='10M'
LOG ON BetaLogDev='5M'
GO

USE Beta
GO

CREATE TABLE TestTran (ID INT IDENTITY PRIMARY KEY, SomeText NVARCHAR(1000))
GO

BEGIN TRAN
    WHILE(1=1)
    BEGIN
        DECLARE @info VARCHAR(100)
        INSERT TestTran VALUES(@info)
        SET @info = Convert(VARCHAR(100),GETDATE())
        PRINT  @info
    END

DISK INIT error: open device failed

On modern and secure operating systems, everything is secured with permissions and never production services are started using a high privileged accounts. That is good, as the systems are better secured and even one compromised service will not cause failing the entire server or application. On the other hand administering such a good secured system can become nightmare, because you will ever and ever receive error messages, when the permissions are not set correctly.

Let's look at following example. You want to create a new raw device for SAP Sybase databases:

disk init name='BetaDataDev', physname ='/dev/sdb1', size=5120
disk init name='BetaLogDev', physname ='/dev/sdb2', size=2560

When starting the command following error is shown:

DISK INIT encountered an error while attempting to open/create the physical file for device '/deb/sdb1'. Consult the Adaptive Server error log for more details. For the cluster edition, check the error log of each instance.

So let's look into the error log:

tail /opt/sap/SAP-16_0/install/SAP01.log

What can be found there?

00:0006:00000:00026:2017/03/19 10:47:47.73 kernel  basis_dsizecheck: attempt to open device '/dev/sdb1' failed, system error is: Permission denied

What does it mean? Sybase is running on the system on some specific account. This account does not have access to the device file /dev/sdb1. Let's change the permission, for example changing the owner of the device:

chown sap /dev/sdb1
chown sap /dev/sdb2

Well, it is time to retry the creation of devices:

disk init name='BetaDataDev', physname ='/dev/sdb1', size=5120
disk init name='BetaLogDev', physname ='/dev/sdb2', size=2560

Damaged database - how to restore the database

Damaged database could be damaged in many different ways, so it is hard to have one solution for this:

try to online the database

ONLINE db_name

if not working try to force online:

sp_forceonline_db db_name, sa_on

this procedure will try to bring the database online without checking for consistency errors or suspected pages. Those operations should allow the administrator to see the content of database, to perform the last backup of the database, may be do some export and so on

The next point is usually dropping the database and recreating it as an empty database and then  loading the dump. To drop database use:

DROP DATABASE db_name

If there are problems with this command following command could be started:

dbcc dbrepair (db_name, dropdb)

So, the next point is recreating the database. Command displaying information regarding backup can be very helpful as it shows which devices have been used by the database:

Load database - restoring a database

Command used to restore the database i LOAD DATABASE. Not every user can issue that command, actually only a very narrow group of them: sa (system administrator) and dbo (database owner). The load database will overwrite the content of an existing database and is considered as very destructive command. If the incorrect dump would be loaded than a real problem will appear...

Generally options used in the LOAD command are the same as in the DUMP described here, however just after the load, the database will be in offline state. To have it again on online state the ONLINE DATABASE command will need to be issued:

LOAD DATABASE ALFA  from '/home/dumps/01_normal.dmp'
GO
ONLINE DATABASE ALFA
GO

To verify the status of database issue command:

sp_helpdb

If the database would be in offline state you will see such a description in status column.

Only database that is not in use by any user may be restored. If any user's session is still active in the database following error will be recorded:

Dump database

Command

DUMP DATABASE myDB TO myDumpDevice

is the simplest form of creating the backup, however a lot of options can be added to that simple command. Lets take look at some examples:

DUMP DATABASE ALFA TO '/home/dumps/01_normal.dmp'

here a database named ALFA is being backed up into the file name 01_normal.dmp. What will happen if there is already such a file on disk? Well - it will be simple overwritten. So one backup - one file forever.

DUMP DATABASE ALFA TO '/home/dumps/01_stripped_01.dmp'
STRIPE ON '/home/dumps/01_stripped_02.dmp'

Here the dba expected the backup will be a really big file, so decided to dump the databases into 2 files. Please note, that it will be only one backup here. Part of database's pages is saved in the first file and the rest into the another. This is not a 'mirroring' of backup file. No way to restore the database when you will lost one of the files. The purpose of using that syntax is just to have smaller files and if those files will be created on different physical disks. also the performance may be better. Smaller files are easier to manage, additionally the file system where the files are being backed up can be smaller in size. Each stripe can be created on separate backup server (so also on separate machine).

Backups to devices

As described in Dump Devices article, dump devices can make administration much easier, however they can be used only for local dumps...

Let's assume you have some devices defined. To display the list of devices you can use following command:

sp_helpdevice

Knowing that dumpsdev is a valid name of dump device you try to execute following dump command on the Backup Server. Note, that you try to use the name of the device:

DUMP DATABASE ALFA TO dumpsdev AT SAP01_BS WITH COMPRESSION=9

Unfortunatelly the dump command ends with error message:

Could not execute statement.
Error encountered by Backup Server.  Please refer to Backup Server
messages for details.
SQLCODE=8009
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=16
Line 31

Dump devices

To create the backup you work with a dump database command. When such a command is executed manually by administrator it is quite common just to use int the backup command just a file, where the backup should be stored:

DUMP DATABASE master TO '/home/dumps/master_2017_02_19.dmp'

However, when the backups should be created always in the same location, always with the same script and metodology, than it is quite convenient to use backup devices. Backup device is a definition that points to some location on the system. This can be a file but this can also be a tape device. The easiest way to create a device is using following sp_addumpdevice command:

EXECUTE  sp_addumpdevice 'disk', 'dumpsdev','/home/dumps/sap01.dmp'

The first parameter designates the device as a file on disk or a tape device. The second parameter is  called a logical name, and it will be used in commands performing the dump. The last one is the physical name of a device, in case of file this is just the name of a file.

Having such a device following command can be executed:

DUMP DATABASE master TO dumpsdev

This dump of master database will be saved in the dumpsdev this is in the file /home/dumps/sap01.dmp. Should you forget the connections between logical names and physical names, always a procedure sp_helpdevice can be used to display, how the device has been defined:

Errors starting Backup Server

Following errors can be seen when starting Backup Server:

Error 1

[sap@SAPSybase ~]$ /opt/sap/ASE-16_0/bin/startserver -f /opt/sap/ASE-16_0/install/RUN_SAP01_BS 
[sap@SAPSybase ~]$ Backup Server/16.0 SP02 PL01/EBF 25184/P/Linux AMD Opteron/Enterprise Linux/ase160sp02pl00/4902/64-bit/OPT/Wed Sep 9 09:04:15 2015 Confidential property of SAP AG or an SAP affiliate company. Copyright 2015 SAP AG or an SAP affiliate company. All rights reserved. Unpublished rights reserved under U.S. copyright laws. This software contains confidential and trade secret information of SAP AG or an SAP affiliate company. Use, duplication or disclosure of the software and documentation by the U.S. Government is subject to restrictions set forth in a license agreement between the Government and SAP AG or an SAP affiliate company, or other written agreement specifying the Government's rights to use the software and any applicable FAR provisions, for example, FAR 52.227-19. SAP AG or an SAP affiliate company 
Logging Backup Server messages in file '/opt/sap/ASE-16_0/install/SAP01_BS.log' 
Open Server Error: 16253.10.0: srv_thread_props(): property SRV_T_LISTENADDR not valid for service thread .

This error was in my case - fake! Backup server has been started correctly! This can be easy verified:

Method 1 - showserver command in bash

In the bash session start:

showserver

An example output can be similar to:

[sap@SAPSybase ~]$ showserver
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S sap       1272  1271  0  80   0 -  6541 poll_s 16:30 pts/0    00:00:00 

  /opt/sap/ASE-16_0/bin/backupserver -e/opt/sap/ASE-16_0/install/SAP01_BS.log -N25 -C20 

  -I/opt/sap/interfaces -M/opt/sap/ASE-16_0/bin/sybmultbuf -SSAP01_BS
0 S sap       1111  1106 16  80   0 - 488674 futex_ 16:27 ?       00:00:31 

  /opt/sap/ASE-16_0/bin/dataserver -d/opt/sap/data/master.dat -e/opt/sap/ASE-16_0/install/SAP01.log 

  -c/opt/sap/ASE-16_0/SAP01.cfg -M/opt/sap/ASE-16_0 -N/opt/sap/ASE-16_0/sysam/SAP01.properties 

  -i/opt/sap -sSAP01

The second line tells that backup server is up and running

Backup: Backup Server

There is a special service associated with Sybase installation: Backup Server. It can be used to perform the backup or load:

• remotely
• directly from disk, bypassing the main Sybase process

The steps done during a backup or restore process performed this way are:

• Sybase process (can be from a different machine) sends using Remote Procedure Call (RPC) a request to perform a backup/load operation
• Backup can be divided into many stripes. It is Backup Server responsibility to decide how big the stripes will  be. Additionally those stripes (files) can be located on local drives, but also on remote shares making the backup less depended on the same hardware as the database itself!
• It is possible that a local Backup Server will perform the backup on locally installed databases and send the data to remote Backup Server, which in turn will save the received backup on local tapes available on that remote machine.

Of course backup server needs to be installed and configured on the same machine as the Sybase. That is a requirement, because backup server process needs to have access to the same devices as Sybase server has. Backup server listens on dedicated port that is different than the Sybase port. The information regarding ports can be found in the interfaces file. Below, an example content of interfaces file can be found:

Backup: Some facts

Well, backup is one of the most mandatory duty of database administrator. Let's first answer a couple of backups features:

• If the database contains a small amount of data, but it takes a lot of disk space on the disk, than the backup will be small, as only used pages are backed up, unused pages are skipped
• Every administrator should have a backup and recovery plan and from time to time an exercise should be done. The goal of the exercise is to test if all required factors are fulfilled to successfully restore the database
• To perform backup of the database you need to have permissions. The database owner (dbo). Of course it is enough to be aliased to the dbo. That way sa role is also capable of performing backup of any database. Additionally every member of the server role oper_role also has right to dump any database. Assuming there is login named backup_login, you can assign it to oper_role using following command:
  
  

  sp_role "grant" , oper_role, backup_login

  
  or using the newer syntax:
  
  

  GRANT ROLE oper_role TO backup_login
  
  

• Backup can be done while the database is in use, so generally backups should be performed quite often depending on the load on the server, the number of transactions, the importance of the data stored in the database. Backup increases the load on the server (CPU and disk) ~5%

SQL: Transaction log maintnance, thresholds

As presented in: SQL: Sybase transaction log, the transaction log is used to store the transactions coming from users. Having this in mind, if the transaction log would become full, no more transactions in the database will be accepted. Database with full transaction log is not usable.

To avoid situation where the transaction log would be full, there is possibility to define thresholds, that define actions that should be started when only transaction log is filled up to some specific level. By default there is Last-Chance Threshold defined. When this threshold is met, automatic process of backup of the log is started.

In production environment, when the option "trunc log on chkpt" is set to OFF, transaction log will be emptied (truncated) only after an incremental backup. Backup of the transaction log is necessary, as the space in the transaction log may be re-used only if the server knows, that the log has been copied already somewhere.

How does it work?

SQL: Recovery and Recovery Interval

Usually under recovery many people understand the process of restoring a database after a disaster, so you take an available backup, restore it and the time when the database is being restored is called recovery. This is not the correct definition of recovery in the pure database naming convention...

Recovery is quite a normal process in a database live and and is performed quite often, not only after database failure. Actually after each server restart, when databases are attached to the server, it needs to verify if the content of a database file is consistent with the content of the transaction log. This process is named recovery.

Yes. Don't be surprised, that after server restart some databases may be found in recovery status! Just sit and wait, looking from time to time into the errorlog to see the progress. After the recovery is done, the database will be available in normal state again!

What kind of situation can be met during recovery?

• in the transaction log, a transaction is found that was committed, but still not saved in the database
• in the transaction log, a transaction is found that was not committed.