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.