Sybase ADDICT

Recently, I successfully compiled Apache and Php to use a Sybase ASE v15.0.3 dataserver on CentOS v5.4 (32 bits). Apache was compiled with DSO support to use Php as a dynamically loadable module. This is how I did it:

1°) Linux Requirements:

You’ll need to download the Apache 2.0.63 and Php 5.3.1 sources.
To compile Php and Apache, the following packages need to be installed:

- make
- automake
- autoconf
- libtool
- makeinfo
- bison
- gcc
- cpp
- glibc-devel
- perl

Note:
I assume in this tutorial Sybase ASE v15 has been already installed (up and running) on your Linux box.

2°) Pre-Installation tasks:

Note:
I assume in this tutorial Sybase ASE v15 has been already installed (up and running) on your Linux box.

Now we gonna set up the environment variables. Go to the directory where Sybase ASE is installed, and source the script named “ASE150.sh”:

cd /usr/local/sybase/ase/1503
. ./ASE150.sh

Check your environment variables are set up correctly:

env|grep -i syb

Mines look like this:

SYBASE_JRE6_32=/usr/local/sybase/ase/1503/shared/JRE-6_0_6_32BIT
SYBASE_JRE6=/usr/local/sybase/ase/1503/shared/JRE-6_0_6_32BIT
SYBASE_UA=/usr/local/sybase/ase/1503/UAF-2_0
SYBASE_OCS=OCS-15_0
SYBROOT=/usr/local/sybase/ase/1503
SYBASE=/usr/local/sybase/ase/1503
SYBASE_WS=WS-15_0
SYBASE_ASE=ASE-15_0
LD_LIBRARY_PATH=/usr/local/sybase/ase/1503/DataAccess/ODBC/lib:/usr/local/sybase/ase/1503/OCS-15_0/lib:/usr/local/sybase/ase/1503/OCS-15_0/lib3p:/usr/local/sybase/ase/1503/ASE-15_0/lib:
PATH=/usr/local/sybase/ase/1503/ASE-15_0/jobscheduler/bin:/usr/local/sybase/ase/1503/RPL-15_0/bin:/usr/local/sybase/ase/1503/ASEP/bin:/usr/local/sybase/ase/1503/DBISQL/bin:/usr/local/sybase/ase/1503/UAF-2_0/bin:/usr/local/sybase/ase/1503/OCS-15_0/bin:/usr/local/sybase/ase/1503/ASE-15_0/bin:/usr/local/sybase/ase/1503/ASE-15_0/install:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

Modify LD_LIBRARY_PATH and PATH as follow:

export LD_LIBRARY_PATH=/usr/local/sybase/ase/1503/OCS-15_0:\
/usr/local/sybase/ase/1503/OCS-15_0/include:/usr/include:\
$LD_LIBRARY_PATH

export PATH=/usr/local/sybase/ase/1503/OCS-15_0:\
/usr/local/sybase/ase/1503/OCS-15_0/include:\
/usr/include:$PATH

Sybase changed open-client libraries names with Sybase ASE v15.
Because Php 5.3.1 will look for Sybase ASE open-client libraries from Sybase ASE v12.x, you need to trick Php and create symbolic links in $SYBASE/$SYBASE_OCS/lib for each following libraries:

lrwxrwxrwx 1 root root 13 Jan 12 01:32 libcobct.a -> libsybcobct.a
lrwxrwxrwx 1 root root 15 Jan 12 01:31 libcobct_r.a -> libsybcobct_r.a
lrwxrwxrwx 1 root root 16 Jan 12 01:33 libcobct_r.so -> libsybcobct_r.so
lrwxrwxrwx 1 root root 14 Jan 12 01:32 libcobct.so -> libsybcobct.so
lrwxrwxrwx 1 root root 12 Jan 12 01:29 libcomn.a -> libsybcomn.a
lrwxrwxrwx 1 root root 14 Jan 12 01:29 libcomn_r.a -> libsybcomn_r.a
lrwxrwxrwx 1 root root 15 Jan 12 01:29 libcomn_r.so -> libsybcomn_r.so
lrwxrwxrwx 1 root root 13 Jan 12 01:30 libcomn.so -> libsybcomn.so
lrwxrwxrwx 1 root root 10 Jan 12 01:34 libcs.a -> libsybcs.a
lrwxrwxrwx 1 root root 12 Jan 12 01:34 libcs_r.a -> libsybcs_r.a
lrwxrwxrwx 1 root root 13 Jan 12 01:34 libcs_r.so -> libsybcs_r.so
lrwxrwxrwx 1 root root 11 Jan 12 01:34 libcs.so -> libsybcs.so
lrwxrwxrwx 1 root root 10 Jan 12 01:32 libct.a -> libsybct.a
lrwxrwxrwx 1 root root 12 Jan 12 01:31 libct_r.a -> libsybct_r.a
lrwxrwxrwx 1 root root 13 Jan 12 01:32 libct_r.so -> libsybct_r.so
lrwxrwxrwx 1 root root 11 Jan 12 01:31 libct.so -> libsybct.so
lrwxrwxrwx 1 root root 12 Jan 12 01:14 libintl.a -> libsybintl.a
lrwxrwxrwx 1 root root 14 Jan 12 01:15 libintl_r.a -> libsybintl_r.a
lrwxrwxrwx 1 root root 15 Jan 12 01:15 libintl_r.so -> libsybintl_r.so
lrwxrwxrwx 1 root root 13 Jan 12 01:15 libintl.so -> libsybintl.so

Now you can start to compile Apache and Php with Sybase ASE v15.

3°) Apache compilation:

The Apache web server source code can be downloaded from http://httpd.apache.org.

./configure --prefix=/usr/local/httpd/apache/2063 \
--enable-so
make
make install

You must set the $SYBASE environment variable in the user’s profile who start Apache (in my case root) otherwise Php will not be able to connect to Sybase ASE. You can add $SYBASE in .bashrc or .profile or even Apache startup script:

export SYBASE=/usr/local/sybase/ase/1503

4°) Php compilation:

The PHP source code can be downloaded from http://php.net.

./configure --prefix=/usr/local/php/531 \
--with-apxs2=/usr/local/httpd/apache/2063/bin/apxs \
--with-sybase-ct=/usr/local/sybase/ase/1503/OCS-15_0 \
--with-config-file-path=/etc
make
make install

Enjoy

As of Sybase ASE v15.0.2, Sybase introduce the “Application Tracing” feature.

“Application Tracing” allows you to collect useful diagnostic information for a running client session (like execution plan, i/o cost etc…), and capture the trace output into a text file. Very helpful to debug some performance bottlenecks ! All you need to know to use this feature is the SPID (Server Process ID) of the session you want to trace. Applicative users with “set tracing” permission granted are allowed to use “Application Tracing” to trace their own sessions.

1°) User with administrative privileges (‘SA or SSO role’)

To enable tracing in Sybase ASE for a user connection, the syntax is the following:

set tracefile "<file-path>" for <spid>

To disable tracing in Sybase ASE for a user connection, the syntax is the following:

set tracefile off for <spid>

Note:
If you don’t specify a file path, trace will be created in $SYBASE.
You can’t trace more than one session at a time from a given session.
You can’t trace the same session from multiple sessions.
If the session being traced quits, or when tracing itself is disabled, the trace file will be closed.
In case ASE runs out of file space while writing the trace output, the trace file will be closed and tracing will be turned off.

To display informations about all open tracing sessions use:

sp_helpapptrace

2°) Applicative users with “set tracing” permission:

To enable tracing in Sybase ASE for your own user session, the syntax is the following:

set tracefile "<file-path>"

To disable tracing in Sybase ASE for your own user session, the syntax is the following:

set tracefile off

Note:
If you don’t specify a file path, trace will be created in $SYBASE.
If the session being traced quits, or when tracing itself is disabled, the trace file will be closed.
In case ASE runs out of file space while writing the trace output, the trace file will be closed and tracing will be turned off.
You can’t use sp_helpapptrace.

3°) What can be traced ?

The following “set option” commands can be traced with “Application Tracing”:

set show_sqltext <on/off>
set showplan <on/off>
set statistics io <on/off>
set statistics time <on/off>
set statistics plancost <on/off>

The following “set option” subcommands can also be traced with “Application Tracing”:

set option show_lop <normal/brief/long/on/off>
show logical operators used

set option show_managers <normal/brief/long/on/off>
show data structure managers used

set option show_log_props <normal/brief/long/on/off>
show logical properties used

set option show_parallel <normal/brief/long/on/off>
show parallel query optimization

set option show_histograms <normal/brief/long/on/off>
show histogram processing

set option show_abstract_plan <normal/brief/long/on/off>
show abstract plan details

set option show_search_engine <normal/brief/long/on/off>
show search_engine details

set option show_counters <normal/brief/long/on/off>
show optimization counters

set option show_best_plan <normal/brief/long/on/off>
show best plan details

set option show_code_gen <normal/brief/long/on/off>
show code generation details

set option show_pio_costing <normal/brief/long/on/off>
show physical io estimates

set option show_lio_costing <normal/brief/long/on/off>
show logical io estimates

set option show_elimination <normal/brief/long/on/off>
show partition elimination

4°) Example (User with SA or SSO role):

set tracefile "c:/tmp/ase-trace-spid-16.txt" for 16
set show_sqltext on
set showplan on
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
----------- ----------- ----------------------------
16          19          c:/tmp/ase-trace-spid-16.txt

(1 row affected)
(return status = 0)

set tracefile off for 16
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
 ----------- ----------- ----------

(0 rows affected)
(return status = 0)

You can find the tracing output from code above while running system procedure sp_helpdb for spid 16: ase-trace-spid-16.zip

On Unix/Linux machines hosting some Sybase dataservers running multiple engines, it is always difficult to find out quickly what dataservers engines belong to. When you have dataservers running multiple engines, “showserver” or “ps” command line always show something like this:

sybase 9510 8892 246 Jul 11 ? 1858:08 ./ase/1503/ASE-15_0/bin/dataserver -ONLINE:1,0,0xffffff


Of course, with some Unix commands line you can find what dataservers engines are associated to, looking for PID, or connect to each dataservers and look into system table “sysengines” for PID. But I was always frustrated about that… When I connect to a machine, I want something that show up easily… There is a workaround for this: you just have to bind your dataservers names with your dataservers processes, using Unix links !

sybase@vbox:$ cd $SYBASE/$SYBASE_ASE/bin
sybase@vbox:$ ln -s dataserver SYBPARFRLIV01_DS
sybase@vbox:$ ln -s backupserver SYBPARFRLIV01_BS
sybase@vbox:$ ln -s monserver SYBPARFRLIV01_MS


So it should look like this:

lrwxrwxrwx 1 sybase dba 10 02:46 SYBPARFRLIV01_DS -> dataserver
lrwxrwxrwx 1 sybase dba 12 02:46 SYBPARFRLIV01_BS -> backupserver
lrwxrwxrwx 1 sybase dba 9 02:46 SYBPARFRLIV01_MS -> monserver


Sun Solaris operating system will not show the link name when you will issue a “ps”, so you may want to copy the binaries with a “cp”.

sybase@vbox:$ cp -p dataserver SYBPARFRLIV01_DS


Then you will have to modify your RUNFILE according your links addition. Mine looks like this:

#!/bin/sh
#
# Master device path:/srv/sybase/SYBPARFRLIV01_DS/dbf/master
# Error log path:/srv/sybase/SYBPARFRLIV01_DS/log/SYBPARFRLIV01_DS.log
# Directory for shared memory files: /products/sybase/ase/1503
# Adaptive Server name: SYBPARFRLIV01_DS
#
cd /products/sybase
./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS \
-sSYBPARFRLIV01_DS \
-d/srv/sybase/SYBPARFRLIV01_DS/dbf/master.dat \
-e/srv/sybase/SYBPARFRLIV01_DS/log/SYBPARFRLIV01_DS.log \
-c/srv/sybase/SYBPARFRLIV01_DS/cfg/SYBPARFRLIV01_DS.cfg \
-M/products/sybase/ase/1503 \

Now when you will issue a “ps” command, you will explicitly see dataserver’s name for each engine ! The result is even better with a “top” command, helping you to find out quickly dataservers CPU activity.

sybase@vbox:$ ps -ef|grep SYB
sybase 9514 8892 140 Jul 11 ? 1874:18 ./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS -ONLINE:5,0,0xffffff
sybase 9515 8892 137 Jul 11 ? 1715:21 ./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS -ONLINE:6,0,0xffffff
sybase 9513 8892 146 Jul 11 ? 1851:21 ./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS -ONLINE:4,0,0xffffff
sybase 8892 8891 242 Jul 11 ? 1991:39 ./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS -sSYBPARFRLIV01_DS -
sybase 27200 1 0 Jun 27 ? 0:00 /bin/sh RUN_SYBPARFRLIV01_BS
sybase 8891 1 0 Jul 11 ? 0:00 /bin/sh RUN_SYBPARFRLIV01_DS
sybase 9512 8892 73 Jul 11 ? 1533:53 ./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS -ONLINE:3,0,0xffffff
sybase 9511 8892 249 Jul 11 ? 2048:07 ./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS -ONLINE:2,0,0xffffff
sybase 9510 8892 246 Jul 11 ? 1858:08 ./ase/1503/ASE-15_0/bin/SYBPARFRLIV01_DS -ONLINE:1,0,0xffffff


Hope this help.

Job scheduler allows DBA to create and schedule administration tasks. Since ASE v15.0.3, lot of bugs are solved, making job scheduler to be workable. By the way, I will suggest to heavily test it before to go in production environment !

So how to install it ?

Job scheduler is a separate external process running as an agent (JS Agent), and an internal ASE task (JS Task) that need a specific database to run. We will need to setup a database and create a directory services entry in interface file.

Create the database sybmgmtdb:

Let’s assume the dataserver you created is named SYBPARFRUAT01_DS and you created 2 separate devices named sybmgmtdat for data and sybmgmtlog for log segment.
Connect to the dataserver you want to install the job scheduler and create the database sybmgmtdb:

create database sybmgmtdb on sybmgmtdat = 100 log on sybmgmtlog = 25
go
sp_dboption sybmgmtdb,"trunc log",true
go
sp_dboption sybmgmtdb, "select into", true
go
use sybmgmtdb
go
checkpoint
go

Then, back to Unix command line, you must run the installjsdb script located in $SYBASE/$SYBASE_ASE/scripts, it will install the job scheduler componants into database sybmgmtdb:

isql -Usa -Ppassword -SSYBPARFRUAT01_DS \
-i $SYBASE/$SYBASE_ASE/scripts/installjsdb

I previously mentionned job scheduler is a separate server process running as an agent. So we need to create a directory services entry in interface file.
Let’s call the job scheduler server process SYBPARFRUAT01_JS.

SYBPARFRUAT01_JS
master tcp ether vm.sybnux.com 5999
query tcp ether vm.sybnux.com 5999

Now, let’s connect to the dataserver and add the job scheduler agent using sp_addserver to create an entry in the dataserver’s sysservers table:

sp_addserver SYB_JSAGENT, null, SYBPARFRUAT01_JS
go

While executing a job, job scheduler consider all dataservers as remote servers, including the server where Job Scheduler is installed.
Job scheduler use CIS mechanism. So to execute jobs locally, we need to create an entry in sysservers (SYBPARFRUAT01_RPC) to alias the local dataserver (SYBPARFRUAT01_DS):

sp_addserver SYBPARFRUAT01_RPC, ASEnterprise, SYBPARFRUAT01_DS
go

Setup job scheduler users:

To create, manage, or execute jobs and schedules, we need to create at least one job scheduler admin account one job scheduler user account.

exec sp_addlogin 'jobsadm',
'password',
@defdb='sybmgmtdb',
@deflanguage='us_english'
go
exec sp_addlogin 'jobsusr',
'password',
@defdb='pubs2',
@deflanguage='us_english'
go
use sybmgmtdb
go
sp_adduser jobsadm
go
use pubs2
go
sp_adduser jobsusr
go
sp_addexternlogin 'SYBPARFRUAT01_RPC','jobsadm','jobsadm','password'
go
sp_addexternlogin 'SYBPARFRUAT01_RPC','jobsusr','jobsusr','password'
go

Grant the appropriate roles to logins:

sp_role 'grant', js_admin_role, jobsadm
go
sp_role 'grant', js_user_role, jobsadm
go
sp_role 'grant', js_user_role, jobsusr
go
sp_modifylogin jobsadm, 'add default role', js_user_role
go
sp_modifylogin jobsadm, 'add default role', js_admin_role
go
sp_modifylogin jobsusr, 'add default role', js_user_role
go

Activate and start the jobscheduler agent:

sp_configure "enable job scheduler", 1
go
use sybmgmtdb
go
sp_js_wakeup "start_js", 1
go

You are now ready to use the jobsheduler.
To stop the jobscheduler proceed as follow:

use sybmgmtdb
go
sp_js_wakeup "stop_js", 1
go

Did you ever think about an integration between Sybase ASE and “Tivoli Storage Manager” (TSM) provided by IBM ?

As far as I knew, IBM did not offer such kind of tool to interface Sybase ASE and TSM and, as far as I knew, no such kind of interface exist from any other vendor.  Sure, you can still buy “SQL Backtrack” that provides direct backup to TSM through their OBSI… But from my point of view SQL Backtrack becomes obsolete regarding the new backup capabilities of Sybase ASE. So it is an expensive solution to buy SQL Backtrack just to link directly ASE with TSM to perform backups… What a pity, it could sometimes make the DBA’s life easier to backup databases directly to TSM…

But the good news is such kind of product finally exists and is named “Data Protector” !!

Data Protector will allow you to backup your Sybase ASE databases using TSM. Data Protector is a command line driven backup and restoration tool.

Unfortunately I can’t test it in the environment I’m currently working in (TSM administrators do not allow me to test it). But I think it deserves a look. Repostor is the company providing this product. You can visit their company website here: http://www.repostor.com/joomla.

Features available in this product are:

• Full backup of all databases
• Full backup of selected database(s)
• Full restore of all databases
• Full restore of selected database(s)
• Restore of database to a new machine
• Restore of database to a new database / instance
• Query to TSM. What backup versions do I have?
• Pick older version of backup for restoration

They even provide a video to install an use the product, you can see it below: