Sybase ADDICT

Sybase is announcing the end of engineering support for ASE Database Expert Option and ASE SQL Expert Option for all platform as of 02/01/2011. These products will be discontinued, means Sybase will not provide these products anymore.

But Sybase customers are eligible to upgrade to “Quest SQL Optimizer Suite for Sybase ASE” at no additional charge.

Details of the upgrade program can be found here:
http://www.quest.com/sql-optimizer-suite-for-sybase/upgrade

See more here

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

The following transact-sql code will help you to find all defined rules in a Sybase ASE database.

select sysobjects.name as "rule name",
object_name(syscolumns.id) as "table name",
syscolumns.name as "column name"
from sysobjects, syscolumns
where object_name(syscolumns.domain)=sysobjects.name
and sysobjects.type = 'R'
go

The output displays the table and column to which the rules are bound.

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.