Archive for the ‘Start Sybase’ Category

Database load error

November 24th, 2011 No comments

Sometime “database is in use” error encounters while loading the database, this could be due to in-memory db tables might still active for the named database. So use the dbcc commands to clear those and reload the database.

How to know what are the spids using given database

November 24th, 2011 No comments

Use below command


dbcc dbreboot(‘report’,’dbname’)


It will give you all spids which are using given database.

How to remove duplicate rows from a file in unix

November 24th, 2011 No comments


cat <filename> | sort | uniq -c


Note: “sort” command is required before “uniq” because uniq command matches the next line with previous one. If file is not sorted then duplicate rows unlikely be filtered.

How to avoid locking on system tables in tempdb

November 24th, 2011 No comments

1. tempdb database is shared among all databases in a server, so it is heavily used. It causes lock contention on sysobjects, syscolumns and sysindexes tables. To reduce the contention try to use “select…insert” instead of “select into””, because former is fully logged operation which slow down the process and provide a solution to locking problem.

2. Try to use data only locking scheme in place of allpages.

“Pushd, popd and cd -” unix commands

November 21st, 2011 No comments

pushd:- This command acts similar to “cd” command.

popd:- This command is used to return to previous directory.

cd -:- This command is also used to return to previous directory.

NOTE Both “popd” and “cd -” works similar but they differ primarily that “cd -” acts like a loop i.e.

                                                     cd -                  cd -       cd -      cd -

Directory X ——> Directory A ——–> Directory B ——-> Directory A ——–> B ——->A —–> B


                                                popd       popd

Directory X ——> Directory A —> Directory B ——-> A———> X

Categories: ASE, Developement, Start Sybase, UNIX Tags: ,

getopts unix command

November 21st, 2011 No comments

getopts is an unix command to fetch argument and argument values. OPTARG and OPTIND are two variables to hold argument values and index.


suppose we have define command usage as below

test.ksh [-s <server name>] [-d <database name>] [-u <user name>] [-p <password>]

so usage is

while getopts s:S:d:D:u:U:p:P i

do case “$i” in

        s|S) SERVER="$OPTARG";;
        d|D) DATABASE="$OPTARG";;
        u|U) USER="$OPTARG";;
        p|P) PASS="$OPTARG";;
        ?) usage;;


Categories: ASE, Developement, Start Sybase, UNIX Tags: ,

Basic awk concepts

November 21st, 2011 No comments

Awk is basically a tool for text processing and report generation which is having features of programming language like C. Let see below how awk happens to be useful tool while writing shell scripts.

$ awk ‘{ print }’ /etc/passwd

When we executed awk, it evaluated the print command for each line in /etc/passwd, in order. All output is sent to stdout, and we get a result identical to catting /etc/passwd. Now, for an explanation of the { print } code block. In awk, curly braces are used to group blocks of code together, similar to C. Inside our block of code, we have a single print command. In awk, when a print command appears by itself, the full contents of the current line are printed.

$ awk ‘{ print $0 }’ /etc/passwd

In awk, the $0 variable represents the entire current line, so print and print $0 do exactly the same thing.

$ awk ‘{ print “” }’ /etc/passwd

Whenever you pass the “” string to the print command, it prints a blank line. If you test this script, you’ll find that awk outputs one blank line for every line in your /etc/passwd file. Again, this is because awk executes your script for every line in the input file. Here’s another example:

$ awk ‘{ print “hiya” }’ /etc/passwd

Print different fields – Fields are separated by colon “:”

  • ======================*

$ awk -F”:” ‘{ print $1 }’ /etc/passwd

$ awk -F”:” ‘{ print $1 ” ” $3 }’ /etc/passwd –> It will print first and third fields with a space between them.




Categories: ASE, Developement, Start Sybase, UNIX Tags:

Survey among Sybase and Oracle customers – Bloor’s Research .

November 17th, 2011 No comments
Survey among Sybase and Oracle customers.


1. License fees: 75% of respondents thought that Sybase ASE was less expensive, by an average of 28%.


2. Support costs: 78% of respondents thought that Sybase ASE was less expensive, by an average of 32%.


3. Number of database administrators: nobody thought that Oracle required fewer DBAs and 61% thought that Sybase required fewer DBAs. On average the saving was 32%.


4. Frequency of security patches: again, nobody thought that this was less frequent in the case of Oracle while 68% thought that this was the case with Sybase, with an average reduction of 22%.


5. Issue resolution: 73% thought that Sybase was faster at resolving issues, typically being 21% faster.



Categories: ASE, News, Start Sybase Tags: , ,

dataserver -X : ASE Diagnostic Interface

November 10th, 2011 No comments


$dataserver -X  <——- For running the dataserver in sybmon mode.(starts this server as sybmon, not dataserver)
Enter password:                                       <——- Password quine
Adaptive Server Enterprise/12.5.3/EBF 12331 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/64-bit/FBO/Tue Jan 25 08:52:58 2005
Sybase Adaptive Server Enterprise Diagnostic Interface
Confidential property of Sybase, Inc.
Copyright 1987, 2005
Sybase, Inc.  All rights reserved.
Unpublished rights reserved under U.S. copyright laws.

This software contains confidential and trade secret information of Sybase,
Inc.   Use,  duplication or disclosure of the software and documentation by
the  U.S.  Government  is  subject  to  restrictions set forth in a license
agreement  between  the  Government  and  Sybase,  Inc.  or  other  written
agreement  specifying  the  Government’s rights to use the software and any
applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA

WARNING: For use by authorized personnel only.
If you are not an employee of Sybase, Inc., or
have not been authorized by a qualified employee
of Sybase, Inc., please terminate this program now.

No servers found using directory: /data/sybase/sqlserver/12.5.3

> cat /data/sybase/sqlserver/12.5.3/ASE-12_5/                 <————— We need to provide the Krg  file location with cat
Shared memory regions currently cataloged:

Name            Key             Id      Status
PROD_ASE1 0x64d28ab5      5603    Available
PROD_ASE2 0x64d28adf      5604    Available
PROD_ASE3 0x64d28add      205     Available
PROD_ASE4 0x64d28ae5      206     Available

> attach PROD_ASE3                                  <—————-Attaching a shared memory segment for analysis
Attaching to server PROD_ASE3, using shared memory id: 205

PROD_ASE3:active> help ?
Help text for Sybmon commands
Usage:  <help | ?> [<command group name> | all]

PROD_ASE3:active> detach        <————-Detaching the shared memory segment

> quit <——–Exiting from sybmon mode

you have mail in /var/mail//sybase
PROD_ASE3:active> who ?
List all active server processes, process for specified spid,
or only busy, idle  or blocked processes
Usage: who [ <spid> | busy | blocked | idle ]

PROD_ASE3:active> locks ?
Display all the locks held or waited for
Usage: locks

PROD_ASE3:active> traceflags ?
List all active traceflags
Usage: traceflags [( 1 | 2 )]

PROD_ASE3:active> opentables ?
Display open tables for one or all active database processes
Usage: opentables [<spid> | <kpid> | <SYB_PROC *>]

PROD_ASE3:active> memdump ?
Dump server’s shared memory region(s) to a disk file
Usage: memdump [<file name> [[nocache | cache] [halt| nohalt] [proc | noproc] [nounused | unused]] | [full]]
The first of each argument pair is the default.

PROD_ASE3:active> stacktrace ?
Display stack trace for a server process
Usage: stack <kpid> | <spid> | <syb_proc addr in hex> | all | run

PROD_ASE3:active> status ?
Show status of shared memory and sybmon program
Usage: status

PROD_ASE3:active> status
Attached to server: PROD_ASE3
Logging: off
Display: on
Timestamplog: on
Sybmon Diagnostics:
        General Diagnostics: off
        Print Module Diagnostics: off
        Virtual Memory Manager Diagnostics: off
        Virtual Machine Diagnostics: off
Dump file mapping mode: normal

PROD_ASE3:active> version ?
Display the version of this program

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