Fresh Batch for SAP(Sybase) ASE and SAP Rep Admin Training starting from 4th Nov 2017. Please reach us @

Procedure Cache and Query Plan

April 26th, 2012 1 comment

ASE doesn’t contain any table which stores the Query Plans of stored procedure. Instead, Query Plans gets stored in procedure cache that is the part of max memory.

ASE maintains MRU/LRU (most recently used/least recently used) algorithm. Stored procedures generally preferred over separate SQL statements because when users execute stored procedure, Adaptive server search procedure cache for existing query plan. If it is available then execution begins.

If Query plan is not available or all copies are in use, if multiple users are executing same stored procedure at a time then multiple copies of query plan will be available in procedure cache until size of cache is supporting, then query tree for the procedure is read from the sysprocedures table  .  Then query tree is then optimized, based on the parameters passed to the procedures and converted into query plan and then execution begins.


Categories: ASE Tags:

DBA Sidekick(Sybase) Andriod App on Google Play

April 25th, 2012 No comments

Source : Link

Key Features:

1. Search Sybase Error Code and Adaptive Server Anywhere SQLCODEs to find error description
2. Partial search available
3. Negative error code need not be supplied for Adaptive Server Anywhere SQLCODEs

Categories: ASE, News Tags: , ,

Sybase ASE Brainstorming Question Series

April 24th, 2012 No comments

Hi All,

We are preparing the Sybase ASE Brainstorming Question Series.

In this series we will put the questions topic wise as well as hands on (day-to-day troubleshooting) questions.

For all questions, We will also include the answers as well 🙂

Please forward your advices/suggestions on Link .

We are waiting to hear from your side.



Categories: ASE Tags:

Database device and segment

April 21st, 2012 No comments

For Adaptive Server, devices provide a logical map of a database to physical storage, while segments provide a logical map of database objects to devices.

Adaptive Server keeps track of the various pieces of each database in master.dbo.sysusages. Each entry in sysusages describes one fragment of a database. Fragments are a contiguous group of logical pages, all on the same
device, that permit storage for the same group of segments. Fragments are also known as “disk pieces.”


Categories: ASE Tags:

we are also changed..

April 20th, 2012 No comments

Hi Folks,

As SAP disclosed its database plans on Apr 10 2012 press conf and If you go through the Sybase point of view for Sybase Technologies with SAP Real Time Data Platform, Its certainly a great move in technology front for ASE,IQ,Rep Server & ASA.

SAP HANA is superb innovation from SAP and it’s integration with Sybase Data Management Products will put these product miles ahead with others.

Definitely It is very good news for Sybase folks!

Now we are also changed our tag-line for showing our support  with this vision of SAP Sybase..& keep your blog in sync with same.

Happy Learning Sybase!
Categories: ASE Tags:

SAP Real Time Data Platform

April 20th, 2012 No comments



Please see the SAP Real Time Data Platform from Sybase Point of View ::


Link ::

Slides :


Categories: ASE, News Tags:

Stock Market Opening Timings With Reference To India Time

April 18th, 2012 1 comment

North and South America

S&P – US : 19:00 PM

Mexican Bolsa -Mexico : 18:00 PM

Brazil Bovespa : 19:30 PM

NASDAQ : 7:00 P.M.

NYSE: 8:00 P.M.
FTSE100 -UK : 21:10 PM
DAX – Germany : 21:00 PM
CAC 40 – France : 21:00 PM
Shanghai Composite – China : 07 :00 AM
Hang Seng – Hong Kong : 07 :00 AM
Nikkei – Japan : 07 :00 AM

Categories: ASE Tags:

Relation between sysdevices, sysdatabases and sysusages

April 15th, 2012 1 comment

Pre 15.x Version


1. Virtual Page Number: Virtual Page number is unique across the server, always in 2k size. Does not depend upon server page size.

2. Logial Page number : Unique across a database. Depends upon the page size of server ( 2k, 4k, 8k and 16k)

3. vdevno from sysdevice as vedno(low/2power24)&255

4. The high Order four bits of sysusage.vstart stores the virtual device number and can be calculated as vdevno=(vstart/2pow24)&255

sysdevices.low : Starting vitual page for a devices and generally calculated as vedvno*2power24

sysdevices.high : Last vitual page of the device allocation

Device Size: (sysdevices.high – sysdevices.low+1)

sysdatabase.dbid : Database Id for a database.

sysusages.vstart : is the vitual page number from where the database fragment starts

sysusages.lstart : Is the start of the logical page number

sysusages.size : Number of contiguous database (logical) pages

sysusages.dbid : Database id for  fragment.

 vstart of a database fragment is lying between the low and high of device used.


Version 15.x


1. Virtual Page Number: Virtual Page number is unique across the device, always in 2k size. It does not depend upon server page size.

2. Logial Page number : Unique across a database.

3. Now, device identification number is stored in the vdevno column and no longer as part of the high or low column.

and also no longer as part of the sysusage.vstart column.

sysdevices.low : Generally Zero , block offset of virtual page in 2K page if there is offset with vstart parameter of disk init.

sysdevices.high : Last virtual page of the device allocation

sysdevices.vdevno : Virtual device number for a device (for master device =0 and maximum value is defined by number of devices config parameter)

Device Size : (sysdevices.high – sysdevices.low+1)

sysdatabase.dbid : Database Id for a database.

sysusages.vstart : is the virtual page number from where the database fragment starts in a device specified by vdevno

sysusages.lstart : Is the start of the logical page number of the database.

sysusages.size : Number of contiguous database (logical) pages

sysusages.dbid : Database id for  fragment.

sysuages.vdevno : Vdevno of device on which database fragment is created.

These global variable always represents as

@@maxpagesize – logical page size

@@pagesize – virtual page size


Source : sybooks

Categories: ASE Tags:

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…

SAP Press Conference – April 10, 2012

April 11th, 2012 No comments


Categories: ASE Tags: