Archive for the ‘Troubleshooting’ Category

ASE 15 Migration Study: Why you should handle prepared statements with great care.

July 30th, 2012 No comments

I have been involved for the past two months in analyzing migration problems of two large local ASE sites.  I decided to share with you the things discovered during the failed ASE 15 migration analysis so that if you happen to be in a similar situation you may discover the way out with less pains.

For these customers, migration to ASE 15.0- ASE 15.5 has been a painful fiasco for two consecutive years.  Cases have been opened.   Professional Services have been sent on site.  A lot of work have been done on rethinking and rewriting code for the new optimizer whims.  Tears, money, and what not shed all through the process.

The truth is, Sybase TS has been telling us  for years that we have bad code, and we – as customers or support teams – were each time infuriated by the insolence of telling us this.  I cannot say that TS has been completely wrong. I can say, however, that were we thinking more WHAT is so peculiar about our code rather than WHY are we told that our code sucks we might have spared ourselves a lot of pain.

I will not write you a detailed report on what we have found here in the blog pages – it will require a lot of pyrotechnics to make things legible here.  Rather, I attach you the report of the study.  You may download it and read at your leisure.   I think it is worth the pains.  Who knows, may be it will solve migration problems for more customers out there.  Local customers were not SO peculiar after all.

Here is the link:  Migration to ASE 15 – 2 Case Studies Involving Prepared Statements.

For those who have little time reading this, let me just warn:  if you use prepared statements in your application code – awares or unawares – beware.  You may be paying very high penalty for this.  Especially in ASE 15 that has been made to work fast – sometimes very fast.  The penalty may be so high that you will consistently fail migrating your old ASE 12.5.x servers to ASE 15 without knowing that the solution is so close.

Here a preview of some data:

Have fun reading this.  I have had a lot of fun digging up the roots of the failed migrations (using my own tools, to be sure, and writing new ones along the way).

If you have any questions – be my guest.



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

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.

Sybase Community Forum…

August 10th, 2010 No comments

Hi All,

After long time, new posting…:(

Just sharing..may be some folks aware with it…Recently came across the Sybase’s own  community forum, supported by Team Sybase….

Its having very good technical discussion, troubleshooting…tips fo ASE, IQ, Replication and many more…

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 @

Cleaning Shared Memory Segment and semaphore, for the ASE startup..

January 30th, 2010 2 comments

Panic dataserver, stopping it manually, and on restart showing issues with memory , meanwhile business people wants updates in every 5 mins…The situation is really worst for a DBA.

This posting is related to shared memory problem, for cleaning the shared memory:

When we shutdown the Sybase dataserver abruptly, by using the kill -9 or kill -12(or due to any reason automatically).

During the restart, sometime it gives the problem unable to create shared memory region, as below:

os_create_region: shmget (0x%x): %s

os_create_region: Shared memory segment %d is in the way

os_create_region: uninitialized shared memory descriptor

os_create_region: shmat (%d): %

Killing Process may left behind the System V semaphore or shared memory left behind instead of being cleaned up automatically. To eliminate unneeded semaphores or shared memory segments, we need to release semaphore or shared memory segment manually.

For the Semaphores:

Run the command ipcs -sa, it will give all semaphores active in the UNIX box, look for the value “0” in the column NSEMS. 0 values indicate that unused semaphore, find all semaphore for the Sybase user and remove them using command ipcrm -s

testinghostname:(/sybase)=>ipcs -sa
IPC status from as of Thu Sep 17 08:50:11 EST 2007
s 201326671 0xc103d804 –ra-ra-ra- patrol dba patrol dba 2 8:45:15 0:00:08
s 201326668 0xc103d80e –ra-ra-ra- sybase dba sybase dba 0 8:50:08 0:00:00

testinghostname:(/sybase)=>ipcrm -s 201326668

For the Shared Memory:

To cleanup the Shared memory segment, which are assigned at OS level but not in use, run ipcs -ma command.

testinghostname:(/sybase)=>ipcs -ma
IPC status from as of Thu Sep 17 08:50:24 EST 2007
Shared Memory:
m 654311446 0x4103d80e –rw-rw-rw- patrol dba patrol dba 0 2304 1766 1766 8:50:18 8:50:18 0:00:08
m 671088657 0x4103d80d –rw-rw-rw- sybase dba sybase dba 0 1792 1766 1762 8:45:15 8:45:15 0:00:08
m 671088650 0x4103d80b –rw-rw-rw- root root root root 0 768 1766 1766 8:50:18 8:50:18 0:00:08
m 671088648 0x4103d80a –rw-rw-rw- sybase dba sybase dba 1 1280 1766 1766 8:50:18 8:50:18 0:00:08
m 654311548 0x4103d809 –rw-rw-rw- sybase dba sybase dba 0 256 1766 1766 8:50:08 8:50:08 0:00:08

Now look in the NATTCH field for 0, if it 0 zero for Sybase user , it is unneeded, but still assigned at OS level. Remove it ipcrm -m by providing the id.

testinghostname:(/sybase)=>ipcrm -m 671088657 -m 654311548

Now Start the dataserver….

Same case with backup server, if u get any error with shared memory..!


Source : Sybooks and UNIX Manuals on wwww.
Same Thread @

Cross Database intgerity constraints and dbid mismatch

December 16th, 2009 1 comment

Hi Folks,

I came across the interesting issue on ASE Server.(Still not sure, need to check with sybase tech suport)

Suppose in our prod env, we have cross database referential integrity constraints, these referential integrity constraints stores in sysreferences table by relating the dbids of the two dbs(primary db and referenced db).

When we perform the refresh from one server to another (Suppose PROD – > UAT).These constraints come as it is in refreshed database. In refreshed dataserver, all dbs may not be created in same order as source dataserver, which results in dbid mismatch from source server.

Still our constraints are same as source db with source dbids.

When we run the dbcc checks on refreshed db, it starts flaging for the wrong dbid in sysreferences table.

Interesting ! Isn’t it?

In sybase manual and sybooks, I didn’t get any article related to db id mismatch, for cross db integrity constraints.

Will update u soon, if any.


Categories: ASE, Troubleshooting Tags: , ,

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….