SQL: Sybase transaction log

Each user's connection, has 2KB of memory assigned, that should be used as a buffer. It is called User Log Cache (ULC). It can be said, that transaction is played in the ULC first. If transaction is short, than it fits fully into the ULC, but if it is too long, than the ULC would fill up...

So whenever COMMIT is issued by the transaction or if the ULC becomes full, Sybase sends the modified pages into the database's transaction log.

At that time, we can say, that transaction log is more important as the database itself. Every committed transaction is saved in the log first. The data file can be not modified at all! Let's analyze, what happens when user modifies some data:

• data is read form the disk into the buffer
• user transaction begins
• transaction data modifications are done and stored in ULC
• finally, the transaction issues the COMMIT
• data from ULC is sent to transaction log
• when the 'save on disk' operation finishes the user application can continue, as at that moment we are sure, that the transaction is fully stored on the disk, data modification is also done in memory buffer, where the "up to date" pages are stored
• ...
• at some moment a CHECKPOINT is issued
• CHECKPOINT synchronizes the data from buffer cache to disk

SQL: Transactions in Sybase

Transaction is one or more operations on data that need to be saved in the database all or none of them. Transactions can be introduced in Sybase in two ways:

• chained - non default, compliant with ANSI standard. The specific of that mode is, that transaction is started automatically when the first statement modifying the data is started. To finish the transaction COMMIT or ROLLBACK must be issued. This model is default in Oracle database.
• unchained - default, non ANSI standard. Each command modifying the data is a complete transaction. That transaction is started automatically and also automatically committed when the data modification finishes. To have more commands in one transaction an explicit BEGIN TRANSACTION must be issued. To finish the transaction COMMIT or ROLLBACK must be executed. This model is default in TSQL languages (Sybase or Microsoft SQL).

Databases metadata

Quite often when the database already exists some time on the server administrator needs to read somehow information regarding that database. The most important and the most basic information can be found in sysdatabases table:

SELECT * FROM sysdatabases

There is a lot of columns here, the most important are described below, the others save some more complicated stuff that is presented in quite descriptive way by for example sp_helpdb stored procedure.

Database options

Do you remember that some options of a server can be changed using sp_configure command? If the option affects database, it should be applied using a similar sp_dboption. Logic of the function is quite alike the server sp_configure. It's general syntax is:

sp_dboption 'database_name','option name', <'true'|'false'>

That procedure must be executed from the master database.

Some of database options are:

• abort tran on log full - this option causes a transaction to be rolled back, if there is a risk of filling up the transaction log. When managing database logs dba may define actions that should be taken by Sybase engine on some levels of filling up transaction log. If the current transaction is breaching the last-chance treshold it will be rolled back when this option is used.
• allow nulls by default - when you create a new table and nothing was mentioned about null acceptance for the NULL value, than by default NULLs are not accepted (so database in background adds NOT NULL expression). When this option is enabled, than the behavior of database engine changes and columns in the table will by default accept nulls

Database: How database dies: DROP DATABASE

Every dba troubles about his databases and cares about their health. But finally each database needs to die. To remove a database ALFA use  following statement:

USE master
GO

DROP DATABASE ALFA
GO

For DROP DATABASE to succeed following conditions need to be met:

• User executing the statement must be a SA (system admin) or DBO (database owner)
• No one can use the database when it is dropped. If still some sessions exist, they can be terminated using KILL command
• Database cannot be refereced by other databases
• DROP DATABASE must be started from master database

Database: expanding and shrinking database

Even if administrator decided to create the database assigning it a lot of spare disk space, it comes to a moment, when a database needs to be expanded. Assuming database has currently size of 55 MB, additional 5 MB of disk space can be assigned to it using following command:

USE master 
GO 

ALTER DATABASE ALFA 
ON AlfaDataDev='5M'
GO

The result, could be like:

Execution time: 0 seconds
Extending database by 1280 pages (5.0 megabytes) on disk AlfaDataDev
Warning: The database 'ALFA' is using an unsafe virtual device 'AlfaDataDev'. The recovery of this database can not be guaranteed.
Execution time: 0.031 seconds

Table design: Estimating size of a table

This is amazing! A wonderful tool for administrator!

One of the tasks of administrator is to estimate size of the database. But how to do this? As input for the task dba can receive: structure of the database and estimated number of records in each database. Generally this should be enough...

Having structure of objects in the database, objects like procedures, functions, views can be ommitted in estimation. Those objects don't contain data, so they are small. No worries.

What is important are tables and indexes. But no worries again, as Sybase dba has very useful procedure sp_estspace!

Let's suppose a database contains table of following structure:

CREATE TABLE Document
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
 NAME VARCHAR(50) NOT NULL,
 DESCRIPTION VARCHAR(200) NULL,
 DATE DATETIME DEFAULT GETDATE())

Additionally following index has been created:

Database owner and permissions of the owner

Everything in database world must to have an owner. This is specially true regarding database itself. The owner of database has all available permissions in the database. Granting new permissions for database owner in the database does not make sense.

Changing the owner on database results in permissions to be changed, so usually if dba decides to change the ownership from LoginA to LoginB and LoginA should still have some a
ccess to the database, those permissions would need to be granted separately.

Let's suppose we have a database ALFA. We also have a server login AlfaApp. To create the login following command could be executed:

CREATE LOGIN AlfaApp WITH PASSWORD 's3cr3t5!'

To verify permissions of that user on the database try to start on behalf of the user USE statement:

[sap@localhost ~]$ isql -S192.168.2.11:5000 -UAlfaApp -Ps3cr3t5!
1> USE ALFA
2> GO
Msg 10351, Level 14, State 1:
Server 'SAP01', Line 1:
Server user id 6 is not a valid user in database 'ALFA'
1>

The error message is clear - user AlfaApp cannot access ALFA database. Who is the owner of database? This information can be displayed using sp_helpdb:

Database creation - the 3 worst practicies

Isn't it stupid, that default settings are usually not the best!? Couldn't every software be designed so, that default options are optimal and
recommended values? No. Default settings should allow the currently running process to finish successfully...

So let's take look at default options when a new database is created in Sybase. Are defaults optimal?

The shortest form

CREATE DATABASE Alfa

What will happen here? Where will the database be created? Answer is easy - on default device! If the administrator followed one of the best practices during devices administration, that the default device is on a dedicated file or disk. But if the administrator was too lazy, than the default device is still master!

Database: creating a new database

Before creating a database display the list of devices available. Take a look at device names, sizes and options. When database is being created nothing should happen incidentally.

To create a database use command like this one:

CREATE DATABASE ALFA
ON AlfaDataDev = '50M'
LOG ON AlfaLogDev

This command will create a database named ALFA and it will reside on two devices: AlfaDataDev and AlfaLogDev. Not all device space needs to be consumed by the database. As it is shown from the first device only 50 MB was taken.  In the case of the second device all space will be used by the database.
Why have we used two devices? What the syntax "ON" and "LOG ON" mean?

System databases

System databases contain basic configuration information or some default values allowing Sybase to work.

master

The most important database is master. When Sybase starts option -d points to location of master device. The entire server configuration is next loaded from master database. Having this in mind - yes, master is the most important database. Next points are just consequences of this remark:

• administrator should always have up to date backup of master database
• no user databases should be put on the master device

Database: What is a database in SAP Sybase? System tables

A database is the basic component, where user data is stored, but apart from user data, databases store also some other elements:

• indexes that speedup data retrieval
• views, functions and procedures that simplify working with data
• transaction log that is required to guarantee transactions to be consistent even if database server would be unexpectedly stopped
• system tables describing objects that bildup the database

Let's take closer look to system tables. Their names usually begin with 'sys' prefix and generally should never be modified directly (however after changing the 'allow updates' parameter it is possible):

sysdevices system table

All metadata describing server, database or objects in the database are saved in system tables. Data in those tables can be queried, but should not be directly modified.

One of such table is sysdevices, listing all defined on server devices. To change/add/remove records in that table use:

• disk init - to create new devices, 
• disk resize - to extend already created devices, 
• disk mirror - to enable mirroring of device, 
• disk unmirror - to stop mirroring,
• sp_dropdevice - to remove device, 
• sp_deviceattr - to change options related to the device
• sp_diskdefault - to mark a device as default or unmark this property

Moving database device using mirroring

In IT at all one apect doesn't change. This is the need for changes! Even if the location of database devices will be very well designed in the moment, with time it can be discovered that there is too less disk space on the device or file system and the database will beed to be migrated to new device.

This can be done in different ways. One of them is using mirroring. It seems to be quite good as no down time would be required. The idea is:

• enable mirroring of the old device to the new device located on new file system
• when devices are mirrored stop mirroring excluding the old device 
• in that way only the new device will be used!

Mirroring devices - strategies

Mirroring in Sybase ASE allows to avoid failures of databases. So the answer to question "what to mirror" is the same as "what is the most important"?

• master database and device is surely one of the most important componentes of server. Failure in master device will cause the entire server to stop. In case of master database additional step should be done apart from just mirroring devices.
  When Sybase ASE starts it needs to load master database. If the device would be destroyed when server was offline, it will not be able to start. Remember that information about mirroring is stored in sysdevices table in... master. Server without master is not aware of exiting mirror. To solve this issue parameter -r can be added to RUN file pointing to a mirrored master device.

Mirroring devices

SAP ASE allows to mirror device making instant copy of a device in another file/device. In case the primary device would contain a bad block, correct data could be read from a spare location. Mark that mirrored are devices - not databases, so in case when a database is stretched between multiple devices, mirroring only some devices doesn't give full protection for the database.

To mirror a device an option 'disable disk mirroring' must be set to off. Following command allows to verify this setting:

execute sp_configure 'disable disk mirroring'

In case the option would be set to true, you can change it's value starting:

sp_configure 'disable disk mirroring', 0

That option is static - this means server restart is required to make mirroring possible.

Managing devices

Dropping a device

After a database has been dropped a device not used by an
 database can usually also be dropped(of course dba may decide not to release disk space). To remove device following command could be used:

exec sp_dropdevice 'BetaDevice'

where BetaDevice is logical name of device that should be removed. After issuing that command only row in sysdevices is removed. File that the device was build on, need to removed manually. The same concerns also a raw device.

If the device is in use - so you have a database using it - don't worry. Such command will end with an error "Device is being used by a database. You can't drop it.".

Default device and why you should change it?

Every one likes to work with intelligent programs: just put some specific information and the remaining part of parameters should be taken from a set of default parameters. Sybase ASE also works that way. When a new database will be created, one or more devices should be specified, but if this part of command will be omitted, than... a default device will be used. Initially master device is the default one. This is not the best situation, as if by a mistake no device will be specified during database creation it will be placed on master device. According to information in Administration guide it is not recommended situation:

Caution!
Do not store user databases on the master device; doing so makes it difficult to recover both the system databases and any user databases stored on the master device.

Possible errors during device creation

When raw device is being created and something goes wrong, usually an error message displayed to the administrator working in his tools is not very explanatory. This may be error message like this:

Could not execute statement.

DISK INIT encountered an error while attempting to open/create the
physical file for device '/dev/sdb1'. Consult the Adaptive Server error
log for more details. For the Cluster Edition, check the error log of
each instance.
SQLCODE=5123
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=50
File: "devices.sql", line 1

To verify what happened behind the scene, the best is to log at ASE error log on the server. The default error log location on server SAP01 is:

/opt/sap/ASE-16_0/install/SAP01.log

Raw device creation

As raw devices guarantee that write will be completed within transaction (unlike file device even with with DIRECTIO option) and additionally have very good write performance (unlike file with DSYNC option), this is common in production to see raw devices.

To create a raw device you need first to create a device in OS. On Linux station where drive has been attached using IDE controller the disk can be visible via a symbolic file /dev/sdb. First of all partitions should be created on the disk.

To work with partitions execute following steps:

• start command 

fdisk /dev/sdb

File based device creation

To create a new database you need first to have devices created. As already described inthe post
Raw devices or files? What is better? we can create raw devices and regular files functioning as devices.

Let's start with an example showing how to create a device basing on a file. All what is required to start with the device creation is a folder on file system having enough free space. First decide where to locate the devices. It is not a bad idea to have a dedicated folder for file devices. Because data files and log files are used differently you should probably think about locating files on separate filesystem anyway. Here let's put  all the files in the same folder.

[sap@localhost ~]$ pwd
/opt/sap
[sap@localhost ~]$ mkdir databases
[sap@localhost ~]$ cd databases
[sap@localhost databases]$ mkdir alfa

Having folder, enough space and read and write permissions there, we can switch to SQL:

disk init name ='AlfaDataDev', 
          physname = '/opt/sap/databases/alfa/alfadatadev.dat', 
          size=10240
GO
disk init name ='AlfaLogDev', 
          physname = '/opt/sap/databases/alfa/alfalogdev.dat', 
          size=2560, 
          skip_alloc = true
GO

Raw devices or files? What is better?

One of the questions when a new device is to be created is if the device should be a raw device or a file. There are a couple of factors that should be taken into consideration:

• Normal files are easier to manage. When database grows and you need to add a new disk space to the database, it is enough to create a new file and add it as a device to the database. In case of a raw device you have much more administrative work to do...
• Normal files have better performance during writes to the disk. Files are usually buffered by OS, so modifying a file in memory is faster than sending the modification to the disk, however...

System database's devices

Information regarding devices in Sybase/ SAP ASE can be found in many system tables, however this will be a very bad idea to modify them directly. When you need a new device some dedicated commands should be used.

System databases are created during installation and normally you don't need to create them manually. Probably the only moment when a manual creation of databases will be required is when installation has been somehow damaged and you need to recover from the disaster.

During installation following devices have been created:

SELECT name,phyname FROM sysdevices

name                           phyname                                                       
------------------------------ -------------------------------------------------
master                         /opt/sap/data/master.dat       
tapedump1                      /dev/nst0                         
tapedump2                      /dev/nst1                         
sysprocsdev                    /opt/sap/data/sysprocs.dat
systemdbdev                    /opt/sap/data/sybsysdb.dat
tempdbdev                      /opt/sap/data/tempdbdev.dat
sybmgmtdev                     /opt/sap/data/sybmgmtdb.dat 

and those devices are used by following databases (we are skipping tape devices here as you see in WHERE clause):

Cannot execute file RUN_SYBASE

If Sybase ASE has not started automatically you may wish to start it manually. Command used to start server is:

startserver

located by default in /opt/sap/ASE-16_0/bin. This script by default searches for a file /opt/sap/ASE-16_0/install/RUN_SYBASE. This file contains startup parameters used by server during start. However if during the installation you chosed to name the server differently, such a file is not present. Instead of this another file should be placed in that directory. On my server it is RUN_SAP01. To direct startserver script to use that file you can pass the path as parameter using -f:

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

Devices - introduction

To separate database objects from physical server components all databases are build on logical devices. Logical devices seat between a database and physical disk structures. Having database build on devices it is quite easy to migrate database to another server. All you need are just devices with the same names on the another system!

A device can be a file (common solution on Windows implementation) or a raw device (usually used in Unix implementation).

Pages in Sybase / SAP ASE: Virtual and Logical pages

In many places, where command do something with disk or database, you can specify page size. There is also mentioned that default size of the page is 2KB, but this can be set to 4KB, 8KB and so on...
Generally we have two types of pages that can be described by parameters of some commands. There are:

• Virtual pages
• Logical pages