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: