Archive for the ‘Sybase ASE/REP Interview Questions’ Category

Sybase Interview Questions

January 4th, 2012 No comments

Same has been updated in @
How can we configure the dbcc database?

How can you configure sybsecurity?

Have you ever worked on terabyte size of  database? How are you taking backup for the same?

Whats the diff between MSA and WS?  Can we consider MSA as a Ws?

You are not able to execute any command in ASE as tempdb is full and you cant create user defined tempdb on the fly , how will you investigate ?

What are the new features fo Sybase ASE 15?

What are the different options avilable with reorg ?

Why we require reorg ?

Suppose if every thing is fine in REplication enviorment  and data is not replicating , how will you troubleshoot the same?

What is gen id in rep server?

How can you check the latency in the replication enviorment?

Whats is HA in Sybase? How can we monitor the HA status?


Sybase IQ : Architecture & Benefits

January 3rd, 2012 No comments

Sybase IQ ??

Sybase® IQ is a high-performance decision-support server designed specifically for data warehousing.

Sybase IQ is part of the Sybase product family that includes Adaptive Server Enterprise and SQL Anywhere. Component Integration Services within Sybase IQ provide direct access to relational and nonrelational databases on mainframe, UNIX, or Windows servers.


Architecture ??

Sybase IQ architecture differs from most relational databases. Sybase IQ focuses on readers, not writers, which provides a fast query response for many users.

Data is stored in columns, not rows

Placing indexes on all columns provides a performance advantage

A large page size provides a performance advantage

A large temporary cache provides a performance advantage for most operations

Access to data occurs at the table level

Most query results focus on data at the table level

Most insertions and deletions write data for an entire table, not for a single row.


Benefits ??
Sybase IQ is a decision support system optimized to deliver superior performance for mission-critical business solutions.

Intelligent query processing that use index-only access plans to process any type of query.

Ad hoc query performance on uniprocessor and parallel systems.

Multiplex capability for managing large query loads in a multi-server configuration.

Fully-flexible schema support.

Efficient query execution without query-specific tuning under most circumstances.

Fast initial and incremental loading.

Fast aggregations, counts, comparisons of data.

Parallel processing optimized for multi-user environments.

Stored procedures.

Increased productivity due to reduced query time.

Entire database and indexing stored in less space than raw data.

Reduced input/output (I/O).

Guidelines for improving I/O performance

December 8th, 2011 No comments

The major guidelines for improving I/O performance in Adaptive Server are as follows:

• Spreading data across disks to avoid I/O contention.

• Isolating server-wide I/O from database I/O.

• Separating data storage and log storage for frequently updated databases.

• Keeping random disk I/O away from sequential disk I/O.

• Mirroring devices on separate physical disks.

• Partitioning tables to match the number of physical devices in a segment.

Logical or Database Devices and Physical Devices

December 7th, 2011 No comments

Distinctions between logical or database devices and physical devices:
• The physical disk or physical device is the actual hardware that stores the data.

• A database device or logical device is a piece of a physical disk that has been initialized (with the disk init command) for use by Adaptive Server. A database device can be an operating system file, an entire disk, or a disk partition.

• A segment is a named collection of database devices used by a database. The database devices that make up a segment can be located on separate physical devices.

• A partition is block of storage for a table. Partitioning a table splits it so that multiple tasks can access it simultaneously. When partitioned tables are placed on segments with a matching number of devices, each partition starts on a separate database device.

sp_helpdevice –> To get information about devices
sp_helpsegment –> To get information about segments
sp_helpartition –> To get information about partitions.

How to run sybase query in different databases in one run

November 24th, 2011 No comments

I would suggest to run SQL via shell script as below.









select col1, col2 from table


write shell script


while read LINE



isql -S “” -D “${database}” -i “query.txt”

done < database.txt


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.

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