Archive for the ‘ASE’ Category

Sybase ASE 12.5 to 15 Upgradation Steps -I

September 21st, 2010 1 comment


Just trying to make out the Sybase ASE 12.5 to ASE 15.0 upgrade check list.
If any point is missing,any recommendation/suggestions. please comment it out below.

Thanks n Happy Learning Sybase.


PS: Please take the backup of  tables, files, dirs before making any changes. Below steps can be changed for working environment/platform. Before doing on Prod, first proceed with dev env.

ASE 15.x Intsallation :

1. Please first read all the plateform requirement, os patch level before ASE15x installation.
If any patch is recommended, please get it apply with Unix team.

2. Install Adaptive Server 15x into its installation directory other than ASE 12.5.

3. run ./setup to install ASE15x – Just install Binary – do not create any Instance.
run ./setup –console in command mode, do not create any instance here.

Pre Upgrade Tasks:

1. Please run dbcc checkdb, dbcc checkalloc and dbcc checkcatalog in single user mode with fix option,
and make sure that there is no Errors from the check, if there is any Error, Please Fix the problem. It is critical for successful migration.

2. Backup database and Dump Transaction Logs.

3. bcp system tables out
syslogins, sysloginroles, sysusages, sysservers, sysdatabases, sysdevices

4. Ensure that Instance has sybsystemdb if it does not exist, create it.

5. Disable auditing using Command
Sp_Configure ‘auditing’, 0

6. Save the current audit settings for the pre-15.0.2 Adaptive Server using the command:


7. There are recommendation to set allow password downgrade’ to 1 – in case we want to downgrade from 15.0.2 to 12.5.4 again – but not required in my case.
Because encryption algo is changed in ASE 15.

8. Extract databse devices and databases creation scripts using ddlgen,Sybase Central.

9. Save sp_configure output.

10.Save data cache information.

11. Make sure, ASE is configured with enough lock structures for the upgrade process to complete successfully,
it is strongly recommended that you perform the following steps:

a) In every user database, execute the following query:
1> select 2 * (count(*)) + 100 from systabstats
2> go
b) Record the highest value returned

c) If the value from Step 2 above is less than the current number of configured lock structures,
then increase the configured value to the value from Step 2, as shown below:

1> sp_configure ‘number of locks”,
2> go

12. Take the backup of cronjobs and comment out/suspend all the dba maint jobs and application jobs in cron/autosys.

13. Please make sure there is no user activity on the server.

14. Lock all the User and Application ID, functinal ids, system ids: repserver dbo.

Must take the bcp out of syslogins before changing it.

USE master
EXEC sp_configure ‘allow updates to system tables’,1
select ‘update syslogins set status = 2 where name = “‘ + name+'”‘ from syslogins
where name not in (“hauser”,”probe”,”probe_sybase”,”sa”)
order by name

15. Setup black out/define green zone to avoid alerts. (optional).

16. Create a new profile named as .profile_15 in sybase home directory by copying ASE 15x .
Verify that your SYBASE environment variable points to the location of the new Adaptive Server software files you just installed.
set the OLDSYBASE, OLDSYBASE_ASE, and OLDSYBASE_OCS environment variables to the location of the server you are upgrading,
to avoid any possibe error during sqlupgraderes.

17. Make a backup of existing .profile and move it with .profile_12.5

18. preugrade test : preupgrade is need to run for upgrade elibibilty test for fixing error/warning before actual upgrade;

preupgrade is exe which is loacted under /ase15_0/upgrade/.
i)If you are in 12.5 enviorment ( by executing .profile_12.5), go in the below mentioned dir and issue the below command.

/ASE-15_0/upgrade> preupgrade -S -Usa

ii) If you are in 15x env, (by executing .profile_15), please copy the interfaces filefrom 12.5 dir to 15x dir.
Then run the above command.

19. Run Preupgrde test: It may recommends lot of warning, errors for your databases and config params.

Please fix all these errors until all is not resolved and get it confirm with preupgrade test.

20. Fix all reported errors and repeat preupgrade process until no error. And see the sentence as below.

Preupgrade of Adaptive Server to 15.0 is complete.
Upgrade eligibility test succeeded.

Categories: ASE Tags: , , ,

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.


HVAR Technology for Replication..Server..

August 10th, 2010 No comments

New Release of Replication Server Includes Patent Pending Technology for High Volume, Immediate Data Transfer Requirements

Sybase, Inc., an SAP company (NYSE: SAP), and industry leader in delivering enterprise and mobile software, today announced transformative new features for the latest version of Sybase® Replication Server®, Sybase’s database replication product supporting change data capture, data distribution and synchronization of data across heterogeneous database environments for real-time analytics, reporting, distributed operations and disaster recovery. Sybase Replication Server innovation dramatically reduces latency with moving data between enterprise information stores, addressing challenges with the ever accelerating pace of business.

This latest release includes the following capabilities:

Real Time Loading for Sybase IQ – empowering organizations to deliver real-time analytics from ASE to Sybase IQ and through continuous change data capture (CDC) technology.

Advanced Performance Services – providing high-volume transaction replication using HVAR technology, significantly reducing transaction latency.

New Heterogeneous Capabilities – improving performance in heterogeneous database environments operating Sybase ASE, Oracle®, IBM® DB2® and Microsoft® SQL Server database servers, with parallel Data Server Interface (DSI). Enterprises can also now maintain warm standby applications for Oracle databases.

In-Memory Database Replication – enabling ASE on-disk databases to be replicated to ASE in-memory databases.



Categories: ASE Tags: , , , ,

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…

directio,dsync & sync , async IO

April 20th, 2010 3 comments


I came across very conceptual article about the sybase device io.

If you have any questions and suggestions please let me know. Thanks.

Source: Sybase Blogs, www, sybooks, sybase white paper for direct io.


  • Asynchronous I/O allows the process issuing the I/O to continue executing while OS completes the request.
  • Dataserver process does not block on this request till its completion.


  • Synchronous I/O blocks the I/O issuing process from continuing executing while OS completes the request.
  • The process is blocked till the request is completed. This generally results in poor throughput.


  • In 12.0 sybase introduced the dsync flag – shorthand for “Data Synchronous.”
  • When the dsync setting is on, Adaptive Server opens a database device file using the UNIX dsync flag.
  • The dsync flag in ASE directly translates to the O_DSYNC open(2) flag. That is to say, when ASE opens a device that has the dsync flag set, ASE will pass O_DSYNC to open(2).
  • This flag tells the file system that a write to that file must pass though the cache and be written to disk before the write is considered complete.
  • In other words, for writes we throw away the cache efficiency and make sure the data goes to disk.
  • This way if the system crashes, everything that we thought had been written to disk has in fact been written to disk.

Is DSYNC “synchronous”??

  • This is not true.
  • This synchronous / asynchronous conflict is at a different level. With async i/o we are talking about the context in which the i/o is executed, i.e. whether the i/o blocks the caller or if it is done in a different context.
  • With dsync we are talking about when the write() is considered complete.
  • These are not mutually exclusive, and you can asynchronously do a data synchronous i/o.
  • The async portion is as always: the application issues an i/o and later polls (or is notified) for completion. The dsync portion means that the application won’t be told that the I/O has completed until the data has made it to disk.


  • Direct I/O is another kind of file system i/o, introduced in ASE15.
  • In the direct i/o model the file system cache is completely bypassed.
  • Using direct i/o, writes are naturally safe because they bypass the cache and go straight to disk.
  • Direct i/o is very, very similar to raw i/o.
  • The main difference is that in direct i/o the structure of a file system still exists, easing manageability.
  • With raw i/o, no file system exists. The performance of raw and direct i/o should be very similar.

Also like to add :

  • The dsync and directio are mutually exclusive. Both cannt be turn on same time on the device. Both dsync and directio provide the full recoveribility.
  • If you gone through the full article, the next question is, which would be best in raw device and filesystem device with direct io, both are bypassing the file system cache.
Categories: ASE Tags: ,

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 @

ASE interview Ques updated

February 28th, 2010 No comments

Updated ASE interview questions for User Mgmt n Permissions

Happy Holi!!

Categories: ASE, News Tags: , , , ,

New domain name for your fav Blog :

February 17th, 2010 No comments


Today, I have registered new domain name for my fav blog as its title name
Earlier wordpress domain will still work parallel.
So keep rocking sybase and happy learning n fun!!!!!


Categories: ASE, News Tags: , , , ,

Sybase Acquires Aleri!!!

February 5th, 2010 No comments

After the ASE 15.5 release and Sybase Q4 result, one more good news for all Sybase Guys…

4th Feb 2010, Sybase, Inc. (NYSE: SY), an industry leader in enterprise and mobile software, today announced that it has finalized an Asset Purchase Agreement with Aleri Inc. Aleri is a leading provider of enterprise-class complex event processing (CEP) technology and CEP-based solutions.

“With this transaction Sybase positions itself as a clear market leader in CEP. We have strengthened our real-time analytics platform, by adding Liquidity Risk Management and the industry leading Liquidity Management Suite,” said Dr. Raj Nathan, Senior Vice President and CMO of Sybase. “Our customers increasingly expect Sybase to provide a broad analytics portfolio to meet their growing needs and we are committed to continue to deliver on this vision.”

Keep Rocking sybase!!!

Source : &…leri-staff

About Aleri

According to Forrester Research, “The Aleri platform is a strong product backed by a strong strategy.” Aleri is recognized as a standout leader in CEP in the Forrester Research, Inc., independent report published August 4, 2009 –“The Forrester Wave™: Complex Event Processing (CEP) Platforms,” Q3 2009. The full report can be found at

Aleri is focused on providing tools and solutions that deliver Continuous Intelligence ™ – allowing businesses to make better decisions through more timely insight and respond quickly to changing conditions. The centerpiece of this effort is Aleri’s Complex Event Processing (CEP) technologies, designed for high speed real-time analysis of data streams, with tools for rapid application development and deployment. Aleri offers a range of solutions to common business problems, with CEP at the core of all solutions. In March of 2008, Aleri merged with Coral8, another leading provider of CEP technology enabling the company to offer a complete suite of tools and solutions focused on continuous intelligence and business agility in a fast paced business environment. Founded in 1999 and headquartered in Chicago, Aleri was privately held at the time of the transaction.

Categories: ASE, News Tags: ,

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 @