Posts Tagged ‘Sybase’

ASE Interview Questions!

March 11th, 2011 No comments
What is Identity Colum?
What is the advantage and disadvantage of Identity coloums?
From performnace point of view ,which is better  if exists or if not exists?
How can we avoid fragmentation in table?
There is update statement on  one APL and one DOL table. Which one would be fatser?Consider the cases:  where clause on index cluster index coloum , other case not using any index.
Why the reorg is faster on DOL table as compare cluster index rebuild on APL?
Wht cluster index with sorted_data on APL is faster than reorg rebuild in DOL?
What is Sybase recommendation for tempdb size, suppose we have 300GB , 150GB dbs are inserver, wht would be the sybase recommendation for sizing of tempdb?
Whats the difference between dsysnc and direct io?
Suppose we are not concerning about the recovery of the database, which would be better for performance dsync(on/off) or direct io and why?
Whats the asynchronus prefetch ? How is it helping in performance enhance?
We having a 4k page size server, what can be possible pool size in the server?
As Sybase recommends 4K size pool for log usage in 2k page size server , please let me know the pool recommendtaion for 4K pagesize server?
How can we reduce the spinlock without partioning the data cache?
Can we have the spinlock contention with single engine?
In sysmon report what are the five segment you will be looking for performance?
Whta is meta data cache?
Whta is the archive database?
How can we enable the acrhive database for compresssed backup?
Hows the object level recovery is possible in ASE?
How can we find the culprit spid which has filled up th etempdb database?
How can we find the culprit spid which is badly used the log segment of  tempdb?
Whats partioning? How partioning helping in increaeing the performance?
Suppose a table is partioned based on a coloum, how dataserver will be handle  the insert on the table?
Apart from the query plans, wht else resides in proc cache?
What is new config param “optimization goal”? Whats the parameter we need to provide it?
User is experiancing very slow performace, what can be the reason for this slowness?
What is engine affinity and how can set the engine affinity?
If there are 3 cpus in the box, how many engine we can configure ?
Suppose dataserver is running very slow and sp_monitor is showing 100% cpu usages, what can be possible issue? Where will you look at?
What is the error classes in replication server?
What is the diffrence between Warm standby and table level replication?
Can you please let me know five case when the thread goes down in replication?
What are triggers? What are type of triggers and how many triggers can we configure on a table?
What are diffrecnt locking scheme in ASE and what are the latches?
How can we dump a replication queue?

ASE Installation on Linux : Using Fedora Vmware Machine on Windows

March 5th, 2011 1 comment

1. Download the free vmware player from the vmware website:

2. Install the vmplayer which you have downloaded above.

3. Download the virtual appliance for Fedora 10 OS.

4. Unzip your vitual appliance.

5. After unzip, you need to start Fedora.10.vmx file. It will start your guest OS.

For Sybase Installation:

6. Crete the sybase user and adduser in sybase group.

7. For installation of ASE on Linux requires 64MB shared memory. So modify the /etc/sysctl.conf  with kernal.shmmax euqls to more than 64 MB.

8. Run  sysctl -p /etc/sysctl.conf  to take place the kernel level setting.

9. Now login with sybase account, down load or copy the ASE software from Sybase website and start as below.

10. Please see the below slide show for further installation:



October 7th, 2010 No comments


Came across this new technology trends “Cloud Computing” and trying to acquaintance with it .

Its very basic article but no doubts it will clear your doubts about it:

Source : wikipedia, sybase web site , www.

Q: What is Cloud Computing?
A: Wikipedia defines cloud computing as follows:
Cloud computing is a style of computing in which dynamically scalable and often virtualized resources are provided as a service over the Internet. Users need not have knowledge of, expertise in, or control over the technology infrastructure “in the cloud” that supports them.
The concept incorporates infrastructure as a service (IaaS), platform as a service (PaaS) and software as a service (SaaS) as well as other recent technology trends that have the common theme of reliance on the Internet for satisfying the computing needs of the users. Cloud computing services usually provide common business applications online that are accessed from a web browser, while the software and data are stored on the servers.
The term cloud is often used as a metaphor for the Internet, based on how the Internet is depicted in computer network diagrams, and is an abstraction for the complex infrastructure it conceals.

Q: What is a Public Cloud?
A: Wikipedia defines a public cloud as follows:
Public cloud or external cloud describes cloud computing in the traditional mainstream sense, whereby resources are dynamically provisioned on a fine-grained, self-service basis over the Internet, via web applications/web services, from an off-site third-party provider who shares resources and bills on a fine-grained utility computing basis.

Q: What is a Private Cloud?
A: Wikipedia defines a private cloud as follows:
Private cloud and internal cloud are neologisms that some vendors have recently used to describe offerings that emulate cloud computing on private networks. These (typically virtualization automation) products claim to “deliver some benefits of cloud computing without the pitfalls”, capitalizing on data security, corporate governance, and reliability concerns.

Q: How are enterprises using cloud computing?
A: Enterprises are typically using cloud environments for the purposes of testing or to accommodate systems with variable workloads. For example, they want to do simulations or implement a POC but don’t want to make the capital investment in infrastructure to construct these environments.

Q: What are the benefits from using cloud-based solutions?
A: Cloud computing is a potentially cost-efficient model for provisioning processes, applications and services while making IT management easier and more responsive to the needs of the business. These services – computation services, storage services, networking services, whatever is needed – are delivered and made available in a simplified way – “on demand” regardless of where the user is or the type of device they’re using.
The cloud improves resource utilization through the power and flexibility of virtualization. It helps to quickly deliver standardized services through IT service automation, delivering and accessing resources as a service and reducing system and application management costs.
In summary, the benefit of cloud computing is often summarized as follows:
Reduced Cost – Public Cloud technology is paid incrementally through usage or time-based models, potentially saving organizations money. Complete abstraction of hardware and software resources also saves costs by eliminating server and storage management costs.
Flexibility – Cloud computing offers much more flexibility than past computing methods because of the ability to easily add extra capacity.

Q: What is an Amazon Machine Image (AMI)?
A: An Amazon Machine Image (AMI) is a pre-built package of software which can be used to create a virtual machine within the Amazon EC2. An AMI forms the basic unit of deployment for services delivered using EC2 and typically includes the operating system and additional software necessary to deliver a service.

Q: What are Production AMIs and how do they differ from Development AMIs?
A: Production AMIs can be used for all development and production purposes. Unlike Development AMIs they can use used for revenue generating activities and can be used by all developers and customers. Development AMIs are for development and testing only and are provided at no charge to customers and developers.

Q: What is Sybase’s Cloud Computing initiative?
A: This initiative is targeting developers that will enable enterprises to exploit the cloud computing model for various classes of applications including transactional, analytics and mobile applications, enabling organizations to take advantage of cost- and resource-efficiencies associated with Cloud Computing.
Sybase data management, analytics and mobile technologies will enable robust and secure private cloud computing environments in partnership with infrastructure, storage and virtualization technology vendors.
Sybase will enable developers to extend their existing software development environments onto public cloud computing environments such as the Amazon Elastic Compute Cloud (Amazon EC2) environment.

Q: Which other technology vendors are you currently working with?
A: As part of our overall strategy, we are working with storage virtualization vendors such as Symantec and its Veritas Storage Foundation to optimize Sybase’s Virtualized Resource Managementtm (VRM) technology framework found in ASE Cluster Edition for this storage environment to build robust and reliable cloud computing environments.

Sybase is working with industry leaders in infrastructure, storage and virtualization to optimize technologies for delivery into public and private cloud environments that provide greater technology availability and flexibility to Sybase customers looking to Unwire their Enterprise.

Sybase is also working with Amazon Web Services to allow developers to quickly extend their development environments to Amazon Elastic Computer Cloud (Amazon EC2). Sybase is providing customers and partners with freely available development editions of its technologies as Amazon Machine Images (AMI).
Categories: ASE, News Tags: ,

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


Usage of #! /usr/bin/ksh..

August 11th, 2010 No comments

I came across with below article for the usgae of #!/usr/bin/ksh.

Just Sharing….

Source : www

Originally, we only had one shell on unix. When you asked to run a command, the shell would attempt to invoke one of the exec() system calls on it. It the command was an executable, the exec would succeed and the command would run. If the exec() failed, the shell would not give up, instead it would try to interpet the command file as if it were a shell script.
Then unix got more shells and the situation became confused. Most folks would write scripts in one shell and type commands in another. And each shell had differing rules for feeding scripts to an interpreter.
This is when the “#! /” trick was invented. The idea was to let the kernel’s exec() system calls succeed with shell scripts. When the kernel tries to exec() a file, it looks at the first 4 bytes which represent an integer called a magic number. This tells the kernel if it should try to run the file or not. So “#! /” was added to magic numbers that the kernel knows and it was extended to actually be able to run shell scripts by itself. But some people could not type “#! /”, they kept leaving the space out. So the kernel was exended a bit again to allow “#!/” to work as a special 3 byte magic number.
#! /usr/bin/ksh
now mean the same thing. I always use the former since at least some kernels might still exist that don’t understand the latter.
And note that the first line is a signal to the kernel, and not to the shell. What happens now is that when shells try to run scripts via exec() they just succeed. And we never stumble on their various fallback schemes.

– Senior Unix SA

Categories: UNIX 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…

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 @

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: ,