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

QweryBuilder, is a unique database development tool with a very simply purpose, to make accessing data from a database simple, quick, accurate and efficient. QweryBuilder implements innovative ideas to make this possible. An end user can extract and update data without writing a line of SQL. Database developers can create procedures, tables, views and triggers with fewer keystrokes. It currently works with Sybase ASE, iAnywhere SQL Anywhere, Microsoft SQL Server and Oracle. An evaluation version of QweryBuilder can be downloaded from www.Werysoft.com.

Criteria Query Window

The Criteria Query window is one of the cornerstone features of QweryBuilder. It allows you to select a table from your database, enter criteria for that table then retrieve the results. Selecting an item from the table list will display all the columns in a freeform view for that table. You are then able to enter the data you want to retrieve (i.e. criteria). See Figure2.

Data will be retrieved in a new tab. These results are fully updateable. You can change the data, delete rows, insert rows or import data from a file. Once you’re done manipulating the data you simply click the update button to commit your changes.

Graphic Query Window

The Graphic Query window allows you to query your database graphically. This means that you can generate a SELECT statement with just your mouse, no key strokes necessary, see Figure3. When the window first opens you will be asked to select the tables that you want to be part of the query. The selected tables will display in the Graphic Query window, displaying the columns contained within. Click on the columns that you want to be included in the SELECT statement. On the toolbar within the window you will see an option that will let you tether tables together, allowing you to create relationships graphically.

Autocomplete and MyAutocomplete

One of the great features of QweryBuilder is the intellisense found in the SQL Editor window. It assists you as you type your ad-hoc queries. You can display a list of tables by typing in the database owner (e.g. dbo) followed by a period (.). Or you can simply enter Ctrl-Space. To bring up a list of columns for a table type the name of the table followed by a period, or type the table alias you entered in the FROM clause then enter a period. To bring up a list of database owners\schema’s, type the word “this” followed by a period.

E.g.

SELECT A. <– Autocomplete window will display here
FROM MyTable A

SELECT *
FROM dbo. <– Autocomplete window will display here

Another feature in QweryBuilder, known as My Autocomplete, allows you to get quick access to data within your database via the intellisense window. The My Autocomplete feature (Tool/My Autocomplete) can display a list of data retrieved from the database or data that has been manually typed in. So why would a feature like this be handy? I know in a support role you are constantly querying the database looking for information based on “key” data. Let me provide an example. Let’s say a popular query is to find all employee’s by department. Your database will contain an Employee table and a Department table. The Employee Table will contain a foreign key back to the Department table called DeptId. If you need to find all employees in the HR department you will have to first look up the key for the HR department. Once you have this key you will then use it to query the Employee table. So really what you’ve done is create an SELECT statement, executed it, copied the results from the results window or memorized it, created another query for the Employee’s table then entered your department. I know this is not too many steps but it should be easier.

To do this example the QweryBuilder way, open the My Autocomplete window. Enter a new item by clicking the “new” button. Provide a name (e.g. dept); this name will be used in the query window so keep it short. From the type drop down select Command. Enter the following syntax:

SELECT DeptId, DeptName
FROM Department

The column order in the above SELECT statement is important. You want the key column first (the column value that will be inserted into the editor) then the descriptor column second. In your SQL Editor window you can then type the following:

SELECT * FROM EMPLOYEE A
WHERE A.DeptId = dept. <– dept is the name we specified in the My Autocomplete window.

After entering the period you will see that the word dept is removed (it’s not valid for our query) and a list of data is retrieved and displayed (the results of the query we entered in the My Autocomplete window). Find your department and select it. Once you have your department selected you can click enter or tab to insert the key value into your editor window.

Other Features

Format SQL

Check Syntax

Generate CRUD Report

Form View Results.
You can view your query results in a form view next to your regular result set. The idea behind this is that you can scroll vertically through your results while easily seeing all the data in the row without having to scroll horizontally.

Create a Table from Results.
After you select results from the database you can turn those results into a new table. The results can be from a single table or it can be data from multiple tables. The generated script will also provide you with all the INSERT statements for the data you retrieved.

Create Insert Statements from Results.
The idea of creating insert statements is the exact same as what was described in the previous paragraph. This is a good feature because it allows you to copy small amounts of data from one database to a similar table in another database.

Sort Columns.
I can’t count the number of times I’ve retrieved information from a really long table (i.e. a table with many columns) then get lost while scrolling through the results looking for 1 particular column. With QweryBuilder you can sort the columns in your result set so you can easily find the column you’re looking for. It’s a bit of a time saver sometimes.

Filter Results.
After you retrieve your results it may be necessary to further limit those results. Clicking the button seen in Figure 6 will open a filter window. Enter your filter criteria and close the window.

Database Search
QweryBuilder comes with a database search window. You can enter a search string and select the type(s) of database objects you want to search. The application will extract the DDL for each type of database object you selected and search for your strings. If the string is found it will display it in a treeview. Clicking on the name of the found item will display the object in an editor window so you can easily see where the string was found. The search results can then be printed or saved. This allows you to access your results in another program like Excel.

Conclusion

Our mission at Werysoft is to be the leader in the database development tools market. Our goal is to provide our end users with the software they need to be productive while working with the largest databases in use today.

We pride our self in our ability to develop creative solutions to tough problems. This allows us to realize our goal of making you more productive. Our hope is that you see this in QweryBuilder.

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