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

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

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!

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 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!

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!