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

Performance Tuning – Scenario Based

January 13th, 2011 No comments

Scenario 1
If an application makes multiple statement and each statement calls to a specific stored procedure. Thereby, being a multithreaded application it will call stored procedure multiple times at a time. It has been noticed that for some calls procedure takes longer time for different set of parameters. What could be the issue?

Solution 1
First we can’t blame sqarely on stored procedure for the slower performance. We have few options here
1. Reduce the number of concurrent calls to stored procedure.
2. Check whether the procedure is calling any child procedure with input parameter or not. Suppose child procedure is looking for location and if parent procedure is passing location as an input parameter and while the absence of this input parameter child procedure might be processing all available locations, which could be in huge number, and consuming time. So before calling the child procedure, apply proper check which would reduce the time consumptions for remaining calls.

Sybase ASE 12.5 to 15 Upgradation Steps -II

September 22nd, 2010 No comments
Upgrade Steps:

Before proceeding with any further steps , please make sure preupgrade step is clean  and you have backup of all databases, tables etc.

1. Make sure you are in ASe 15 enviorment, ( by executing .profile_15)
2. cd to sample resource files directory.
3. copy the sql server resource file with new name before modifying original file.
4. Edit the resource file for the old sybase home dir (ASE 12.5),for reserver word check yes and update the resource file as per your enviorment.
5. No need to shutdown your ASE 12.5 server, sqlupgradres will take care all the things.
6.  Only execute this command once you have done with all above steps and you have taken all backup. (MOST IMPORTANT)
sqlupgraderes again perform preupgrade, before actual upgrade.( you can modify its behaviour in resource file).
sqlupgraderes will copy automatically the interface file, cfg file , run server file in ase 15x.
Execute the following command with resource file: sqlupgraderes -r <resource_file_name>
Copying interfaces file entry to new directory…
The interfaces file entry has been copied to the new directory.
Running preupgrade program…
The preupgrade program has completed successfully.
Checking reserved words in each database…
No reserved word conflicts were found.
Checkpointing all databases…
All databases have been checkpointed.
Copying configuration file to new directory…
The configuration file has been copied to the new directory.
Updating RUN_SERVER file…
The RUN_SERVER file has been updated.
Doing pre-upgrade modifications…
Pre-upgrade modifications succeeded.
Restarting Adaptive Server….
Adaptive Server has been restarted.
Running upgrade program…
The upgrade program has completed successfully.
Restarting Adaptive Server….
Adaptive Server has been restarted.
Running installmaster script…
installmaster: 10% complete.
installmaster: 20% complete.
installmaster: 30% complete.
installmaster: 40% complete.
installmaster: 50% complete.
installmaster: 60% complete.
installmaster: 70% complete.
installmaster: 80% complete.
installmaster: 90% complete.
installmaster: 100% complete.
The installmaster script has been successfully installed.
Running installcommit script…
The installcommit script has been successfully installed.
Installing common character sets (Code Page 437, Code Page 850, ISO Latin-1,
Macintosh and HP Roman-8)…
Character sets installed.
Restarting Adaptive Server….
Adaptive Server has been restarted.

6. Log in the server, check the current version with @@version, @@boottime, database/device status.

Post-upgrade tasks:

1. Installing 15x stored procedures after upgrade depending which function is being used on the server.
installsecurity – Run this script if the Adaptive Server Auditing functionality was enabled in your earlier installation.
installhasvss – Run this script if the HA functionality is enabled and the HA feature is currently in use in the upgraded installation of        Adaptive Server.
Check the installation script version using sp_version by logging in ASE.
2. Compare sp_configure output to see if there is any difference. Verify procedure cache allocation after upgrading. Verify data cache has no change.
3. Make sure license file is used.
save the license files with a .lic file name extension to the SYSAM-2_0/licenses directory of the license server installation.
Modify the variable in .profile for any instances on the same box.
For example:
If you not having lic file, it will run under grace period of 1 months, you will get same warning in errorlog.
4. Reboot the instance
5. Reenabling auditing if needed.

Renaming the Adaptive Server – ASE

September 2nd, 2010 No comments

Before doing any step ;

i) Please take the backup of master database, including all other important critial databases.
ii) Please take the backup of all files which you are modifying to backout in case of any issue.
iii) Stop the client/app activity on the server.

1. First modify the sysservers table in master database and shutdown the dataserver.
Enable allow update on system table, modify sysservers for srvname and srvnetname with new server.

2. Copy the RUN Server file with new name.

3. Modify the new RUN server file for new server name, errorlog file name(optional), config file name (optional).

4. If you are modifying any cfg file name in new RUN server file, please copy the old cfg file with new name.

5. Take the backup of the interfaces file, after that modify the new servername are just add the new server name with different port number.

6. Now restart your dataserver with new RUN server file.

7. Verify the connection from isql/app team.

Same step we can repeat for the Sybase backup Server.


Replication Server Interview questions updated!!!

August 17th, 2010 No comments

Hi Folks,

Just added some more ques for Sybase Replication Server in interviewquestion page  @

Happy Learning sybase …:)

Have Fun….


Dataserver Health Check Script

March 30th, 2010 No comments

Hi Folks,

This is going to be first post of month march, was busy due to relocation.  Now new topic health check…

In our production support environment we need do the health check on our data server on time to time basis, such as after the any long activity, in the production monitoring, after the server restart, and there are lot of many more cases.

I have tried to compile the Hc script v 1.0 as below , I am still trying to improve it , your suggestions and thoughts most welcome…


## Dataserver Health Check - Version 1.0
## Intial Drafted by - sybanva for SYBASETEAM.COM
## Date - 19th Feb 2010

print "#########################################################"
set nocount on
print "--------------------------------------------------------"
print "         DataServer Checks"
print "--------------------------------------------------------"
select @@version "DATASERVER VERSION"
select @@servername "Instance Name", @@maxpagesize " Page Size of Instance"
select getdate() "Current Date", @@boottime "Instance Startup date"
print "--------------------------------------------------------"
print "         Active Trace Flags"
print "--------------------------------------------------------"
dbcc traceon(3604)
dbcc traceflags
print "--------------------------------------------------------"
print "         Process Status"
print "--------------------------------------------------------"
select spid SPID,cmd COMMAND,cpu "CPU Usage",physical_io "PHYSICAL IO",ipaddr "IP ADDRESS",loggedindatetime "LOGGED IN DATE",hostprocess "Host PROCESS ID"  from sysprocesses where physical_io>0 or cpu >0
print "--------------------------------------------------------"
print "          Long Running Process Status"
print "--------------------------------------------------------"
If exists (select count(1) from syslogshold where spid <> 0)
print "<<<<<<<<  NO LONG RUNNING TRANS AT THIS MOMENT >>>>>>>>>"
select dbid "Database ID",spid SPID,starttime "START TIME",name NAME from syslogshold where spid <> 0
print "--------------------------------------------------------"
print "           Blocking Process Status"
print "--------------------------------------------------------"
If not exists (select count(1) from sysprocesses where blocked > 0)
select spid SPID,cmd COMMAND,cpu "CPU Usage",physical_io "PHYSICAL IO",ipaddr "IP ADDRESS",loggedindatetime "LOGGED IN DATE",hostprocess "Host PROCESS ID"  from sysprocesses where blocked > 0
print "<<<<<<<<  NO BLOCKING PROCCESS AT THIS MOMENT >>>>>>>>>"
print "--------------------------------------------------------"
print "           Checks for Zombie SPIDs"
print "--------------------------------------------------------"
if exists(select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses))
select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses)
print "<<<<<<<<  NO ZOMBIE SPIDS AT THIS MOMENT >>>>>>>>>"
print "--------------------------------------------------------"
print "           Check for Engines"
print "--------------------------------------------------------"
sp_configure 'number of engines'
select engine "Engine No",osprocid "OC Proc ID",status "STATUS",starttime "START TIME" from sysengines
print "--------------------------------------------------------"
print "           Checks for Database Status"
print "--------------------------------------------------------"
select  name "DB NAME",dbid "DBID",status "STATUS",crdate "CRATION DATE",dumptrdate "LAST DUMP TRAN DATE" from sysdatabases
print "--------------------------------------------------------"
print "           Checks for Suspect/OFFLINE  Database Status"
print "--------------------------------------------------------"
if exists(select name from sysdatabases where status in (-32768,64,256,32))
select name, status from sysdatabases where status in (-32768,64,256,32)
print "<<<<<<<<  ALL DBS STATUS IS NORMAL >>>>>>>>>"
print "--------------------------------------------------------"
print "           Checks for Servers"
print "--------------------------------------------------------"
select srvname "SERVER NAME",srvnetname "SERVER N/W NAME" from sysservers
print "--------------------------------------------------------"
print "           Checks for Backup Server"
print "--------------------------------------------------------"
print "--------------------------------------------------------"
print "           Checks for Monitor config"
print "--------------------------------------------------------"
sp_monitorconfig "max memory"
sp_monitorconfig "number of locks"
sp_monitorconfig "number of open indexes"
sp_monitorconfig "number of open objects"
sp_monitorconfig "number of user connection"
sp_monitorconfig "procedure cache size"
Same Thread @

How to check the Replication Latency manually?

January 23rd, 2010 No comments

Hi Folks,
For the checking latency between Primary and Replicated DBs , we have several methods.

Here, I am posting the manual method for checking the replication latency. If you have any more thoughts, please comment it out.

Rite Now I am travelling to Delhi, my train got late, so I am spending my time with my fav, with new posting in blog.

For calculating the latency, we can make two tables in PDB and RDB with column as defaut datetime, and setup replication between these two tables.

Before excuting the batch of your’s queries, insert the id (only for reference for the point, where we executed the insert statement) and it will update the primary_datetime by default current system date.

After the completing the batch, insert the the next id, automaticaly it will get the finish date in default column.

Now go to RDS and check the diff between primary_datetime and replicate_datetime.

1. IN PDS/PDB Create one table as
create table timer_table(id int, primary_datetime datetime default getdate())

2. In RDS/RDB create same table with one extra coloum
create table timer_table(id int,primary_datetime datetime,replicate_datetime default getdate())

3. Setup the replication between both.

4. Before executing the batch insert the value in table as
insert into timer_table (id) values(100)

5. Execute your batch for queries.

6. After completion, execute the insert statement as
insert into timer_table (id) values(101)

7. Now go in the replicated dataserver(RDS/RDB) and check the diff between primary_datetime and replicate_datetime.
select datediff(ss,min(primary_datetime),max(replicate_datetime)) from timer_table

With the help of id you can check the latency upto that point. Even,You can customize this method for checking the latency.

Source : www & sybooks.
Same Thread @ :

Basic Working of Sybase Replication Server

January 16th, 2010 No comments

Hey Guys,

Its my first post of New Year 2010. I am trying to put the basic understanding of Replication Server and whys it needs more monitoring. Hoping you will enjoy it!

Wishing you great new year 2010 ahead !

Basic Working of Replication Server

PDS : Primary Data Server
PDB : Primary Data Base

RDS : Replicated Data Server
RDB : Replicated Data Base

PRS : Primary Replication Server
RRS : Replicated Replication Server

RSSD : Replication System Database

1. RepAgent reads the record from transation log of the PDB for the tables which are marked for replication.

2. Logs into the PRS and write transactions in inbound queue of PDB in stable device.

3. Holds Data in inbound queue , untill it recieves commit.

4. Uses subscription information in its RSSD to decide what to do with the each transaction, after the commit:

i Discards the rans if there is no subscription.
ii Writes the transaction to the out bound queue if there are subscription.

5. Writes commited trans only in outbound queue according to subscription.

6. Sends transactions to their destination, it depends upone two things

i) if Replicated Database is managed by PRS
Apply changes to RDB using the DSI thread our the connection.

ii) If their are two server, RRS is managing RDB
Send commited trans to RRS over route.
RRS apply that changes in RDB

7. If apropriate, uses function string information in RSSD to compose command to submit to replicate database.

Source : sybooks and www.

As you people are seeing, there are lot of movement of trans/record , and for these trans movements Replication Server uses lot of threads(DSI,RSI,SQT,SQM etc).

If any one of thread stops , replication ceases , even it can hamper PDB performance.

Thats why it is little bit difficult to manage, not difficult, we can say, its need better monitoring.

If I am missing any thing , please add in comments.

Same Thread at :

ASE15 New Features – I: DBA Perspective

December 17th, 2009 1 comment

*Source : Sybase Resources on world wide web. Sybooks.

  • MDA Installation Automaticaly.
  • ASE 15.x isql client can now do large network packets.
  • IPv6 platform support : IPv6 is now supported on IBM AIX. 15.0.2 ESD#1.
  • Encrypted columns : meets US Government encryption standards.
  • IN ASE 15.x, there are a number of partition level operations you can do. Reorgs can be done on a partition level.
  • ASE 15.0 and later versions no longer use vdevno. i.e. the disk init syntax doesn’t need to mention the vdevno parameter(Even earlier, it was optional).
  • Before 15.0, after changing a database option we need to use that database and do checkpoint on it. But ASE15.0 doesn’t need this.
  • ASE 15 Cluster Edition, a high-availability version of ASE similar to Oracle’s RAC.
  • Dumping and loading databases with password protection:You can protect your database dump from unauthorized loads using the password parameter of the dump database command. If you include the password parameter when you make a database dump, you must also include this password when you load the database.

  • Fast bcp:  In 15.0.2, fast BCP is also allowed for indexed tables. Rest all things about bcp remain same, like select into/bulkcopy/pllsort’ is enabled; logging only the page allocations in log etc.
  • Disk init syntax:Disk init syntax in 12.5 expects size parameter in K, M, and G only. From 15.0 and onwards, T (Terabyte) can be specified.Also, pre 15.0; the maximum size of a device was 32GB
  • In ASE 15.0.2 IR, you can run sp_configure nondefault, which will list out, the configuration parameters set to non-default values. It is veru usefull to check which config not using the dafult values.
  • Automatic update statistics : Instead of manually running update statistics at a certain time, you can set update statistics to run automatically at the time that best suits your site and avoid running it at times that hamper your system. The best time for you to run update statistics is based on the feedback from the datachange function. datachange also helps to ensure that you do not unnecessarily run update statistics.In ASE 15.0, Update statistics is not necessary after index rebuild. Also sp_recompile is not necessary after index rebuild.
  • Application tracing:Version 15.0.2 of ASE comes with the new feature of ‘application tracing’. Understanding this feature is a must for every DBA, because it provides a simple mechanism to figure out what your client applications are actually doing: apptracing lets you capture the SQL submitted to the ASE server by a specific client connection, and writes it into a file.     set tracefile ‘/tmp/spid54.trace.out’ for 54 # where 54 is spid            set show_sqltext on
  • VLDB Support: ASE 15 allows you to assign two billion logical devices to a single server, with each device up to 4 Tb in size. ASE 15 supports over 32,767 databases, and the maximum size limit for an individual database is 32 terabytes, extending the maximum storage per ASE server to over 1 million terabytes!
  • Functional indexes: When applications need to search tables based on the result of a function, performance can suffer. Functional indexes allow the server to build indexes on a table based on the result of a function. When repeated searches use that function, the results do not need to be computed from scratch.
  • Row-locked system catalogs :Adaptive Server version 15.0 converts most system catalogs to a datarows locking scheme. These system catalogs continue to use allpages locking scheme: Materialized tables such as syslocks and sysprocesses. These tables are generated during run-time and their locking schemes are irrelavent for concurrency.

sysmessages and sysusermessages, which are read-only tables.

Auditing tables in sybsecurity, which are write-once and read many times.

  • Semantic partitions/smart partitioning:  ASE 15 makes large databases easy to manage. And more efficient by allowing you to divide tables into smaller partitions which can be individually managed. You can run maintenance tasks on selected partitions to avoid slowing overall performance, and queries run faster because ASE 15’s smart query optimizer bypasses partitions that don’t contain relevant data.
  • Query Processor: The Adaptive Server version 15.0 query processor is self-tuning, requiring fewer interventions than earlier versions. This version of Adaptive Server has less reliance on worktables for materialization between steps since the engine supports data flow between steps. However, more worktables could be used in cases where Adaptive Server determines that hash and merge operations are effective.
  • Scrollable cursors:With large data sets, filing through a mountain of results data can be difficult. ASE 15’s bi-directional scrollable cursors make it convenient to work with large result sets because your application can easily move backward and forward through a result set, one row at a time. This especially helps with Web applications that need to process large result sets but present the user with subsets of those results.
  • Computed columns: Often applications repeat the same calculation over and over for the same report or query. ASE 15 supports both virtual and materialized columns based on server calculations. Columns can be the computed result of other data in the table, saving that result for future repeated queries.
  • Query processing metrics (qp metrics):Query processing (QP) metrics identify and compare empirical metric values in query execution. When a query is executed, it is associated with a set of defined metrics that are the basis for comparison in QP metrics.
  • Large identifiers:There are new limits for the length of object names or identifiers: 255 bytes for regular identifiers, and 253 bytes for delimited identifiers. The new limit applies to most user-defined identifiers including table name, column name, index name and so on. Due to the expanded limits, some system tables (catalogs) and built-in functions have been expanded.
  • User-defined web services:In addition to the Web methods provided by the Adaptive Server Web Services Engine, Web Services enables you to create Web services and execute SQL commands in Adaptive Server Enterprise using either a Web browser or a SOAP client. These user-defined Web services use existing security and auditing control inherent in Adaptive Server Enterprise.

Will post New Features part 2, once get completed.
If you find any new feature, which is very useful, please comment out in this section.


Syssrvroles dropped

December 12th, 2009 3 comments

Hey Guys!
In one case, I dropped out the syssrvroles accidently. Now, no one having any roles, even sa is not having its roles..I hav backup of all system tables and dump of master db. But We dont hav sso_role for any login we can’t do the bcp in , we can’t insert any row in any system table. Even we can’t change the allow update on system table, requires sso_role. Anyway. We have backup of we can recover loading the dump of master , but still need suggestions….