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

Nested Transaction

August 12th, 2012 No comments

NOTE: There must be at least one commit tran executed for each tran statement executed. But, regardless of how many begin tran/commit tran pairs appear to be nested in the code, there is only one transaction.



begin tran

change 1

begin  tran

change 2

commit tran

commit tran

In above pseudocode, there are two nested begin tran statement. Despite, the fact that it appears that there are two transactions (one inside another), there is actually only one open transaction. Thus, the transaction is active until the second commit tran statement is executed.



begin tran

change 1

begin tran

change 2

begin tran

change 3

commit tran

change 4

commit tran

change 5

rollback tran

In this example, none of the five changes would remain in the data after the rollback; again, although it looks like there are three levels of nesting, and it looks like changes 2,3,4, and 5 are fully bounded by begin/commit pairs, there is really only one transaction, and all work must complete in full or not at all. In fact, the transaction shown above is functionally identical to:

begin tran

change 1

change 2

change 3

change 4

change 5

rollback tran

dbcc checkdb vs dbcc checkstorage

June 25th, 2012 No comments

Checkstorage will detect allocation errors, it is a reasonable substitute for dbcc checkalloc (checkstorage will report a fair number of issues that checkalloc will not, many of them trivial things, and checkalloc may be able to detect a few odd conditions
checkstorage does not.

What checkstorage won’t catch are issues with index tree (keys out of order, index entries that point to missing rows, rows
that are not indexed).  So checkstorage is not a good substitute for checkdb.

Source :www & sybooks.

Performance Issue

June 24th, 2012 No comments

Few days back I have faced performance issue in one of our prod data server. I would like to share here.

User was running batch for pushing 90000 rows in a database and batch was not moved from last 1.5 hrs.

On login in the server I found response of the server was not good, it was taking more time to execute a simple query as usual. In first glance, it was looking like user job is hogging the resources, as user job spid was in syslogshold and not moved from long time.

We do some analysis and finally found the cpu usage for server was 100%. ( I used sp_monitor). I concluded that this high cpu usage is slowing down the server performance.

The next task was finding the query which was taking more cpu time. As server was on 15 version, I ran the below sql querry for mon tables for getting the high cpu usage.

select top 10  s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStatement s, master..monProcessSQLText t where s.SPID = t.SPID order by s.CpuTime DESC

We asked application team to check the reported spid and if possible, please abort the tran. There was select queries which were  taking maximum cpu . As they requested us to kill, we aborted/killed from data server.

After few seconds, data server cpu started fluctuating from 50 to 100% and finally it was below 50%.

Application batch of 10K inserts moved very quickly and finally issue resolved.

You can get full details on MDA queries @


Changing ASE Sort Order using sqllocres

June 6th, 2012 No comments
Task : Sort Order change of PROD_ASE_DS2 
Current Sort Order :50 Binary Sort Order (Server Default Sort Order)
New Sort Order : 51 Dictionary Based Sort Order
Before running any command , please import Sybase Enviorment in your current Shell, you can get the same by executing which resides in $SYBASE ( Sybase Installation Directory).
1. Started the Dataserver PROD_ASE_DS2 :
[sybase@localhost install]$ ./startserver -f RUN_PROD_ASE_DS2 ……………….. …………………… 00:00:00000:00001:2012/06/06 00:26:06.61 server ASE’s default unicode sort order is ‘binary’. 00:00:00000:00001:2012/06/06 00:26:06.61 server ASE’s default sort order is: 00:00:00000:00001:2012/06/06 00:26:06.61 server ‘bin_iso_1’ (ID = 50) 00:00:00000:00001:2012/06/06 00:26:06.61 server on top of default character set: 00:00:00000:00001:2012/06/06 00:26:06.61 server ‘iso_1’ (ID = 1). 00:00:00000:00001:2012/06/06 00:26:06.61 server Master device size: 30 megabytes, or 15360 virtual pages. (A virtual page is 2048 bytes.)

2. Checking the Current Sort Order of Server

[sybase@localhost install]$ isql -Usa -SPROD_ASE_DS2 -w999
1> select @@servername,getdate()
3> go
———————————————————— ————————–
PROD_ASE_DS2 Jun 6 2012 12:26AM
(1 row affected)
1> sp_helpsort
2> go
Sort Order Description
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) – Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
Characters, in Order
! ” # $ % & ‘ ( ) * + , – . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~ Â
¡ ¢ £ ¤ Â¥ ¦ § ¨ © ª « ¬ ­ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾ ¿ Ã
à à à Ã
à à à à à à à à à à à à à à à Ã
à à à à à à à à á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ÷ ø ù ú û ü ý þ ÿ
(return status = 0)
1> exit

3. Moving in Sample Resource file Directory Read more…

Sybase ASE Indexes -2

April 11th, 2012 No comments


Creation of Indexes:

You can create the index either of two ways:

  • With Create index command
  • By specifying Integrity Constraints like Primary Key and Unique Key in create table command.

Integrity constraints (Primary and Unique Keys) have following restrictions for the indexes:

  • You cannot create non unique indexes.
  • You cannot set various setting provided by create index command like ignore_dup_key,ignore_dup_row etc.
  • You cannot drop these indexes without alter table command.

When we specify the Primary Key in create table command, it creates Unique Cluster Index and unique Key creates unique non clustered index on the column mentioned in keys.

Summarize as –

  • Primary Key in create table command ==> Creates Unique Clustered Index
  • Unique Key in create table command  ==> Creates Unique non clustered Index
  • If neither the clustered nor the nonclustered keyword is used ==> ASE will create non clustered indexes.
  • If unique keyword is not used in create index command ==> ASE will create non unique indexes.


Create Index Command Syntax:

create [unique] [clustered | nonclustered] index index_name
on [[database.]owner.]table_name
(column_expression [asc | desc]
[, column_expression [asc | desc]]…)
[with {fillfactor = pct,
max_rows_per_page = num_rows,
reservepagegap = num_pages,
consumers = x, ignore_dup_key, sorted_data,
[ignore_dup_row | allow_dup_row],
statistics using num_steps values}]
[on segment_name]

Before executing create index, turn on select into: sp_dboption,’select into’, true

The simplest form of create index is: Create index index_name on table_name (column_name)


Viewing Indexes:

  • Using sp_helpindex we can view indexes of a table. E.g. sp_helpindex ‘tablename’
  • sp_statistics also  returns a list of indexes on a table. E.g. sp_statistics ‘tablename’
  • In addition, if you follow the table name with “1”, sp_spaceused reports the amount of space used by a table and its indexes. E.g. sp_spaceused ‘tablename’,1

Dropping indexes

  • The drop index command removes an index from the database.
  • Only the owner of an index can drop it. drop index permission cannot be transferred to other users. The drop index command cannot be used on any of the system tables in the master database or in the user database.
  •  You cannot drop indexes using drop index command which were created using Integrity constraint. To drop the same indexes you should use alter table command.

Index Option ( for Create Index Command):

 i)ignore_dup_key  :

  • This option is only for unique clustered and non clustered indexes.
  • If you try to insert a duplicate value into a column that has a unique index, the command is canceled. You can avoid this situation by including the ignore_dup_key option with a unique index. Your command would be successful and it will ignore that key value (It means, finally no insert on the table).
  • You cannot create a unique index on a column that already includes duplicate values, whether or not ignore_dup_key is set.

ii) ignore_dup_row and allow_dup_row:

  • These options are only for the non unique clustered index.
  • These options are not relevant when creating a nonclustered index. Since an Adaptive Server nonclustered index attaches a unique row identification number internally, duplicate rows are never an issue—even for identical data values.
  • A nonunique clustered index allows duplicate keys, but does not allow duplicate rows unless you specify allow_dup_row. If allow_dup_row is set, you can create a new nonunique, clustered index on a table that includes duplicate rows, and you can  insert or update duplicate rows.
  • The ignore_dup_row option eliminates duplicates from a batch of data. When you enter a duplicate row, Adaptive Server ignores that row and cancels that particular insert or update with an informational error message.
  • If a table has duplicate rows and you are creating non unique clustered index with ignore_dup_key, it will delete all the duplicate rows from the table.

iii)Sorted Data:

  • The sorted_data option of create index speeds index creation when the data in the table is already in sorted order. sorted_data speeds indexing only for clustered indexes or unique nonclustered indexes.
  • Creating a nonunique nonclustered index is, however, successful, unless there are rows with duplicate keys. If there are rows with duplicate keys, an error message appears and the command is aborted.


Source :                                                                                                                                  Continue…

Sybase ASE Indexes

April 9th, 2012 No comments


Indexes are the most important physical design element in improving database performance:

Indexes help to avoid table scans. A few index pages and data pages can satisfy many queries without requiring reads on hundreds of data pages.

Indexes in ASE:

We can divide ASE indexes in two categories by : i) Physical Order of Data with index key  ii) Uniqueness of the index column

Based on the physical order of data, Adaptive Server provides two general types of indexes that can be created at the table or at the partition level:

Clustered indexes, where the data is physically stored in the order of the keys on the index:

• For all pages-locked tables, rows are stored in key order on pages, and pages are linked in key order.

• For data-only-locked tables, indexes are used to direct the storage of data on rows and pages, but strict key ordering is not maintained.

Non clustered indexes, where the storage order of data in the table is not related to index keys

Based on index column uniqueness, indexes can be unique and non unique.

So following types of indexes present in ASE with permutation and combination with above two properties:

1. Unique Clustered Indexes

2. Non unique Clustered Indexes

3. Unique Non-clustered Indexes

4. Non unique Non-clustered indexes


Clustered Index Non Clustered Indexes
Unique Unique Clustered Index Unique Non Clustered Index
Non – Unique Non Unique Clustered Index Non Unique Non Clustered Index

So, all the indexes come under above 4 types:


In case of more than one index column, we can add prefix composite in above types.

Means Composite indexes are those indexes, which are created on more than on one column. Above four types of index can be composite as well.

In the case of partitions, we can categories above types as local and global indexes.Local indexes get created at partition level and table level index called as Global indexes.

Global indexes with one index tree cover the whole table, or local indexes with multiple index trees, each of which covers one partition of the table.

Function-based indexes are a type of non clustered index which use one or more expressions as the index key.


Sybase IQ Installation

March 24th, 2012 No comments

Hi All,

Please find attached Sybase IQ Installation on Unix/Linux platform.


Implementation of Function String in Sybase Replication Server(SRS)

January 30th, 2012 No comments

These experience shared by  Senior DBAs as name mentioned, Hope this  will help you to understand more about function string from implementation point of view in a Replication environment: 
Craig Oakley , Senior DBA.

We used function strings when we wanted to replicate all columns to some servers, and only selected columns to other (web-facing) servers. This was particularly useful before Rep Server allowed multiple RepDefs on the same table. One concern was text columns which were not being replicated to the web-facing server: we had to create a function string to get a text pointer (we used a one-row table and just update all the text columns on top of each other, as the value was not needed on that server): failure to get a text pointer cause the DSI to go down, and we could not specify that as a condition to ignore.

Beyond this, I would imagine function strings could help specify how you want the update to be done, which could be a performance improvement. It would also allow for a different implementation at the replicate than there is at the primary (such as a table at the primary being two joined tables at the replicate).


Sukhesh Nair, Senior Sybase DBA

We used to have a setup where data was replicated from sybase to oracle as also to a warm standby sybase server. Rep Server function strings helped in filtering data that would need to be passed to Oracle. It helped immensely in streamlining the data flow to targets by manipulating the incoming data through function string. I feel it is one of the most advanced and useful yet very less used capabilities of Sybase Rep Server.
The deterrent could be because of the complexity it would introduce to the replication system. The setup we had worked wonderfully and never gave us any major problems. Without proper monitoring (which needs to be scripted by DBAs) it used to be hard to maintain. Many of the current Rep Server administrators I see do not have adequate knowledge or experience of handling function strings.

Rey Wang , Senior Sybase DBA

You can map the delete to no op with functional string.

Partha Gogoi Senior DBA

We use function strings to transform data at the replicate..We have databases being replicated from Toronto and New York to London, Sydney and Singapore and the client ids are transformed at the replicate because, as per business requirements, the client ids are different at each site.. Of course , having a Universal client id would simplify things , but the systems and databases at each site grew independently until replication was set up and it would be a lot of rework to change all the client ids at the replicate sites

Øystein Grinaker Senior DBA

A Function String could be used to change default behaviour.
Say you delete a row in a table on PDB, but you do not want to delete the row on the RDB. Then make a change in rs_delete. You may make the rs delete just to make a logical delete by updateing a deletemarker for that spesific row.


Source :



Inserting data into a data-only-locked heap table

January 5th, 2012 No comments

When users insert data into a data-only-locked heap table, Adaptive Server tracks page numbers where the inserts have recently occurred, and keeps the page number as a hint for future tasks that need space. Subsequent inserts to the table are directed to one of these pages. If the page is full, Adaptive Server allocates a new page and replaces the old hint with the new page number.

Blocking while many users are simultaneously inserting data is much less likely to occur during inserts to data-only-locked heap tables. When blocking occurs, Adaptive Server allocates a small number of empty pages
and directs new inserts to those pages using these newly allocated pages as hints.

For datarows-locked tables, blocking occurs only while the actual changes to the data page are being written; although row locks are held for the duration of the transaction, other rows can be inserted on the page. The row-level locks allow multiple transaction to hold locks on the page.

If conflicts occur during heap inserts
Conflicts during inserts to heap tables are greatly reduced for data-onlylocked tables, but can still take place. If these conflicts slow inserts, some workarounds can be used, including:

• Switching to datarows locking, if the table uses datapages locking
• Using a clustered index to spread data inserts
• Partitioning the table, which provides additional hints and allows new pages to be allocated on each partition when blocking takes place

Inserting data into an allpages-locked heap table

January 5th, 2012 No comments

When you insert data into an allpages-locked heap table, the data row is always added to the last page of the table. If there is no clustered index on a table, and the table is not partitioned, the sysindexes.root entry for the heap table stores a pointer to the last page of the heap to locate the page
where the data needs to be inserted.

If the last page is full, a new page is allocated in the current extent and linked onto the chain. If the extent is full, Adaptive Server looks for empty pages on other extents being used by the table. If no pages are available, a new extent is allocated to the table.

Conflicts during heap inserts
If many users are trying to insert into an allpages-locked heap table at the same time, each insert must
wait for the preceding transaction to complete.

This problem of last-page conflicts on heaps is true for:
• Single row inserts using insert
• Multiple row inserts using select into or insert…select, or several insert statements in a batch
• Bulk copy into the table

Some workarounds for last-page conflicts on heaps include:
• Switching to datapages or datarows locking
• Creating a clustered index that directs the inserts to different pages
• Partitioning the table, which creates multiple insert points for the table, giving you multiple “last pages” in an allpages-locked table