Spotlight: Sybase-on-Linux how-tos.
Here are 10 quick shortcuts for using the Sybase database on Linux, compliments of Dr. Mich Talebzedah, SearchEnterpriseLinux.com's resident Sybase expert and principle consultant for London-based Peridale Ltd. You'll get advice on starting using Perl to access stored Sybase data, shutting down SQLServer using Sybase ASE and more.
- How to support user-defined functions in Sybase on Linux
In any version of Sybase you can use stored procedures easily to return a value back, like any user-defined function. In Sybase, the functionality of "user defined function" is incorporated in stored procedures.
- How to start and shut down Microsoft SQL Server from the command prompt when using Sybase Adaptive Server Enterprise (ASE) with SQL Server on a Linux server:
ASE does not automatically start when you log on as 'sybase.' In Linux, like any other flavors of Unix, you need to go under directory: $SYBASE/$SYBASE_ASE/install and start ASE as follows:
startserver -f RUN_
where <SERVER_NAME> is the name of your ASE you installed. For example, if your ASE instance is called then the runfile is called RUN_MY_SERVER etc.
Once you have started ASE, then you can logon to it using isql. If you have forgotten your "sa" password, then I have already written a thread in this forum on how to deal with lost password for 'sa'.
- How to use Perl to access the data stored in Sybase for Linux:
You can either use 'sybperl,' a suite of modules that implement a subset of the OpenClient API, or the DBI API via the DBD::Sybase module.
Click here for more useful information about Perl.
- How to create extract files:
CREATE a VIEW on the underlying tables and then BCP out the VIEW.
- How to create a database table in Sybase like backupset in Microsoft SQLServer for recording backup history:
Sybase ASE writes backup information to backup errorlog which is, by default, in the ASE's install directory. You should be able to write a small routine that dumps the database and writes the backup info like START TIME, END TIME, TIME TAKEN, NUMBER OF STRIPES USED and SIZE into a database table. You can design your own table and use ASE scheduler to start your backup job.
- How to fix an accidentally deleted stored procedure from the sysobjects table:
Sybase ASE writes backup information to backup errorlog which is, by default, in the ASE's install directory. You should be able to write a small routine that dumps the database and writes the backup info like START TIME, END TIME, TIME TAKEN, NUMBER OF STRIPES USED and SIZE into a database table. You can design your own table and use ASE scheduler to start your backup.
- How to query data through a view:
A VIEW is a compiled object. Data is always stored in the databases tables and through view you are looking at the underlying data:
SELECT a.col1, b.col2 from TABLE_A a, TABLE_B b WHERE a.col3 = b.col4
is equivalent to:
CREATE VIEW a_view
SELECT a.col1, b.col2 from TABLE_A a, TABLE_B b WHERE a.col3 = b.col4 SELECT * FROM a_view
- How to handle database refreshing:
Database refreshing is a process by which a Sybase database is overwritten or refreshed from an existing backup.
In general, it is more often that a backup of the current production database is taken and a copy of the same database in a development or testing environment is refreshed from this backup. The database to be refreshed needs to be the same size as the source database with data and log segments created preferably in the image of the source database (otherwise data and log segments could mix upon refresh).
The source database is backed up using Sybase's backup server into the operating system file(s). These files are binary. The most common method is to transfer these files to the host server containing the recipient Sybase server and database. Again the Sybase's backup server is used to load the
- How to discern the difference between the drop and truncate table commands:
Drop <table> drops the table, including the table definition and the associated objects (rules, indexes, constraints, triggers, primary key and so on). Obviously once a table is dropped; all the data rows contained in the table are also removed.
A truncate <table> command removes all rows from a table. The table structure and all the indexes continue to exist until you issue a drop table command (as discussed above). The rules, defaults and constraints that are bound to the columns remain bound, and triggers remain in effect.
Truncate table command also de-allocates the distribution pages for all indexes. The truncate table is equivalent to but faster than a delete command without a where clause. Delete removes rows one at a time and logs each deleted row as a transaction; truncate table de-allocates whole data pages and makes fewer log entries. Both delete and truncate table reclaim the space occupied by the data and its associated indexes. Only the table owner can truncate a table.
- How checkpoints are issued in Sybase:
A checkpoint process is an integral part of database logging and recovery in Sybase and indeed other database engines. The operation itself is known as "checkpoint." To elaborate on this we need to understand the logging and recovery process in Sybase.
In Sybase -- or to be more precise, in Adaptive Server Enterprise (ASE) -- the recovery process for every database is accomplished by means of the so-called transaction log. This transaction log is a database-specific system table called "syslogs." The syslogs table contains a sequential (serialized) list of all modifications to every object in that database, as well as any information required to maintain data integrity.
This was first published in August 2006