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

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