Archive for September, 2011

Exception handling in Sybase

September 29th, 2011 No comments


I would like to touch upon few topics, which are acting big subject areas in oracle, in Sybase. These below mentioned topic needs thorough discussion.

1. Exception Handling in Sybase –> In Sybase I have come across with only one command called “raiserror” for throwing an error, but what about throwing and catching exceptions, like we have in orcale.

2. User functions in Sybase –> Can we create custom functions like getdate(), db_name() etc?

3. Query Plan of functions –> Does query plan gets generated every time whenever we call functions by passing different arguments?

Please share your thoughts and knowledge on these topics.

Enjoy learning


Categories: ASE Tags:

Sybase dataserver binary output and dbcc command.

September 28th, 2011 No comments

When we execute dbcc sqltext without putting on traceflag 3604 and 3605
where the out put of sqltext goes? In errorlog?

No,For errorlog we have traceflag 3605.

Lets explore the RUN server file again:

-sPROD_ASE_DS1 > /dev/null

-e : denoting the errorlog file where all error message and informational messages resides.

AS we know, When we are running any binary file, the output of that binary displays on the screen.

What about the output of $SYBASE/$YSBASE_ASE/bin/dataserver binary,
generally we redirect it to null device (/dev/null) as above.

Now,I am redirecting the output to file like below as in /tmp/sybaselog.out file.

-sPROD_ASE_DS1 > /tmp/sybaselog.out

Run the dbcc sqltext command , the result would be display in dataserver output file, without any traceflag.

It means when we require any output on user screen and errorlog, need to enable the traceflag 3604 and 3605 respectively,
otherwise it will be display in sybase dataserver binary , out put file ,if we are redirecting it to file.

sybase@localhost ~]$ isql -Usa -SPROD_ASE_DS1
1> select @@spid
2> go


(1 row affected)
1> select name from sysdatabases
2> go

(6 rows affected)

1> dbcc sqltext(14)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc sqltext(14)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

[sybase@localhost ~]$ tail -f /tmp/sybaselog.out
00:00:00000:00001:2011/09/28 08:51:15.11 server  ASE's default unicode sort order is 'binary'.
00:00:00000:00001:2011/09/28 08:51:15.11 server  ASE's default sort order is:
00:00:00000:00001:2011/09/28 08:51:15.11 server         'bin_iso_1' (ID = 50)
00:00:00000:00001:2011/09/28 08:51:15.11 server  on top of default character set:
00:00:00000:00001:2011/09/28 08:51:15.11 server         'iso_1' (ID = 1).
00:00:00000:00001:2011/09/28 08:51:15.11 server  Master device size: 500 megabytes, or 256000 virtual pages. (A virtual page is 2048 bytes.)
00:00:00000:00001:2011/09/28 08:51:15.11 kernel  Warning: Cannot set console to nonblocking mode, switching to blocking mode.
SQL Text: SELECT fid=right(space(80)+isnull(convert(varchar(80),fid),'NULL'),3), spid=right(space(80)+isnull(convert(varchar(80),spid),'NULL'),4), status=SUBSTRING(convert(varchar(80),status),1,10), loginame=SUBSTRING(convert(varchar(80),loginame),1,8), origname=SUBSTRING(convert(varchar(80),origname),1,8), hostname=SUBSTRING(convert(varchar(80),hostname),1,21), blk_spid=right(space(80)+isnull(convert(varchar(80),blk_spid),'NULL'),8), dbname=SUBSTRING(convert(varchar(80),dbname),1,6), tempdbname=SUBSTRIN
SQL Text: select @@spid

SQL Text: select name from sysdatabases

Please let me knwo if you have any more thoughts!!

Compiled Objects in ASE

September 22nd, 2011 No comments

Adaptive Server uses compiled objects to contain vital information about each database and to help you access and manipulate data.

  • A compiled object is any object that requires entries in the sysprocedures table, including:
  1. Check constraints
  2. Defaults
  3. Rules
  4. Stored procedures
  5. Extended stored procedures
  6. Triggers
  8. Functions
  9. Computed columns
  10. Partition conditions

Compiled objects are created from source text, which are SQL statements that describe and define the compiled object.

When a compiled object is created, Adaptive Server:

  • Parses the source text, catching any syntactic errors, to generate a parsed tree.
  • Normalizes the parsed tree to create a normalized tree, which represents the user statements in a binary tree format. This is the compiled object.
  • Stores the compiled object in the sysprocedures table.
  • Stores the source text in the syscomments table.

Why Software is Soft?

September 22nd, 2011 No comments

why software is soft ,it’s very basis question, worth to know it..

Our computer is designed in layered in architecture, In the core (inner layer) we have all physical component(h/w).

In starting , we were directly interacting with hardware and mainly in 1 & 0 , even you can say in on/off state of a transistor. It was really very difficult process for the user’s interaction.

To reduce the hardness of physical component for interaction, we started layering h/w with piece of code.

Simply saying to remove the hardness of hardware , for making it soft for users, we used layers, as we calling now as Software.

Thanks Prof Raman for clearing my thoughts!

object id 98 and 99 in ASE:sysencryptkeys & ALLOCATION

September 21st, 2011 No comments

Basic tools for performance tuning

September 18th, 2011 No comments

I have mentioned few basic performance tuning tools used to check the the health of query and stored procedure. These tools are basically sybase commands only.


Sybase optimizer is basically a cost based optimizer which calculates the cost for each query ,in terms of how much I/O performs, and then decide the appropriate plan.

For calculating the cost, optimizer uses the statistic of table columns and index data which happens to be stored in the form of histogram. It then chooses the access method that the optimizer determines has the least cost.

Sybase has a command called “update statistics” to store the statistics in “systatics” table.



update statistic table_name {(column list) | (index name)]



1. Set statistics io

2. set statistic time

3. set showplan

4. set noexec

5. set fmtonly


set statistics io



set statistic io on


Description:It will show the number of logical reads and physical reads  and total I/O reads

Table: Author scan count 1,

logical reads: (regular=465 apf=0 total=465),

physical reads: (regular=324 apf=234 total=558), apf IOs used=234

Total actual I/O cost for this command: 1023.

Total writes for this command: 0


set statistic time



set statistic time on


Description: It will show the time taken to parse and compile the query, execution time etc.

Parse and Compile Time 35.   (CPU ticks)

SQL Server cpu time: 35ms.

Execution Time 140.

SQL Server cpu time:14000 ms. SQL Server elapsed time: 60123ms.


set showplan



set showplan on


Description: It will show the query paln of query.


set noexec on



set noexec on


Description: If you want to see the query plan without executing the query then use both commands together as below

set showplan on


set noexec on


select * from Author


set fmtonly on



set fmtonly on


Describe: It is used to show the query plan of a stored procedure without executing the stored procedure. Use both commands together as below.

set showplan on


set fmtonly on


exec getAuthorsData


20K+ Visitors in!

September 15th, 2011 No comments


One more good news you fav blog has reached the figure of 20K yday!!

Thank all for your support and response!!


Categories: ASE Tags:

ASE 15.7 for SAP® Business Suite Released in Techwave!!!

September 14th, 2011 No comments

Hi Folks,

Most Awaited Database ASE 15.7 has been released yday in Vegas Techwave:

Extremely good news for Sybase Users!!!!!!!!

ASE 15.7 key features include:

Management of Large Datasets

  • Compression— allows large databases to be stored more compactly and reduces I/O times to ensure high performance on even the largest databases.
  • Reduced Query Latency— helps better handle large data sets, especially those which use dynamic SQL for interactive data retrieval.
  • Replication Performance—increases the performance of Sybase’s industry-leading transaction replication and syncing technology.
  • Enhancements for Parallel Hardware —improves optimization of multi-core/multi-threaded CPU architectures to get the maximum performance out of today’s latest processors

Simplified Administration

  • Online Operations—increases data availability while allowing data to be optimized for application performance.
  • Extended Diagnostics—allows DBAs to quickly pin-point performance bottlenecks and speed customer support requests.
  • Strong Password Encryption—protects the database from external intrusion and hacking
  • Single Sign-on & Login Profiles—makes it easier to manage large numbers of users and simplifies end-user access to the system

Ease of Application Development

  • Efficient Management of Large Objects—inline management of large objects as well as enhanced application development features such as large objects as parameters to stored procedures.
  • Enhanced Application Language Capabilities—many improved TransactSQL&™ language features to increase productivity of application developers as well as support for a variety of popular languages such as Python, PHP and Perl.
  • An Enterprise-Class DB for ISV applications —enhanced business-critical performance for ISV applications out of the box, enabling ISVs to easily write and port their applications to ASE 15.7.
Categories: ASE, News Tags: , , , , ,

Essential DBA Unix

September 12th, 2011 No comments

Hi All,

I am listing the command which we are frequently used in our DBA unix enviorment. I would lile to thnx Abhay for sharing this.

Essential DBA Unix
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!System Administration!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

su : Acquiring super user status
su – henery : Crearting Other users (henery) environment
passwd : Changing Passowrd
date : Setting the system date
wall : Write message all users desktop , terminate
calender : display calender

Read more…