Sybase Interview Preparation Guide

Download PDF

Sybase exclusively focuses on mobilizing and managing information. It is known to be the third largest database
management company after Oracle and IBM. Sybase products on Investment banking are well known to the world over and it offers many customized solutions for business intelligence.

Sybase has worlds leading products some of them are
► ASE
► Avantgo
► Power builder and power designer
► SQL ianywhere
► Sybase IQ
► Sybase replication server
► Sybase RTE
► Sybase data federation
► Main frame connect

Sybase storage systems are optimized for Dataware housing applications where data cannot be modified can be modified by searching and accessing the data.
Its column based search criteria gives high performance for search but it negates the writing capability to the data. Performance for row based relational based databases is worse because it is very difficult for updating and modifying them.

This product from Sybase is the world leader in messaging services through mobile. It distributes mobile content via MMS, SMS, UMTS and WAP. It process more than 8 billion messages monthly and with this product you have access to enterprise mobility anywhere.

The update statistics command helps the server
make the best decisions about which indexes to use when it processes a query, by providing information about the distribution of the key values in the indexes. The update statistics commands create statistics, if there are no statistics for a particular column, or replaces existing statistics if they already exist. The statistics are stored in the system tables systabstats and sysstatistics.

Structure of ASE installation hosts several databases and it comprises of one data server. Also system files occupy only one meta data. User databases are stored in the form of tables. Security and information privacy can be protected by setting permissions.

Sybase IQ stores data base in the form of columns of data rather in rows. This storage model improves the accessibility and search criteria for the data. Performance of the search criteria improves greatly because it searches only columns rather than the rows and columns of the database. This is also environment friendly.

These are the following benefits you can get from mainframe connect: -
1) Client applications can be enabled. External software’s can be connected to the mainframe connect for higher performance.
2) Mainframe applications connect to external data through LAN.
3) New applications can be created which enables you to connect to external data or source of information.

This connectivity tool gives access to high performance tools which connects you very fast to the main frame data sources. It gives you read and write access to databases. It also has a facility by which you can connect the main frame data to secondary data storage facilities.

Power designer has two unique tools known as Link and Sync technologies. This technology offers Data base designers to design efficient data models through which one can offer efficient design. Also Data base designers can share resources among them which will improve the productivity and performance.

Data integration suite offers advanced development and management tools. Integration of data is very easy and efficient as you can navigate your data to a specified address without much difficulty. The five important features are ETL, Data federation, Replications, Real time events and search events.

The numeric and decimal types are identical in all respects but one:
Only numeric types with a scale of 0 can be used for the IDENTITY column

Absolutely there are numerous features but most important are as follows

1. Manages User Connection
2. Enforces Business Rules
3. Provide Recovery and BackUps
4. Understand T-SQL
5. Optimize Query Execution
6. Maintain Databases with NO Redundancy
7. Supports Data and Referential Integrity
many more....

Enterprise scalability provides increased capacity without any new addition of hardware. It increases the capacity by increasing the availability in the enterprise data layer.
ROI: - Streamlining of application projects and reducing data management costs, data federation increases ROI. Complexity and superior decision making choices make ROI best in its class.

Some of the benefits which you can get from a Data integration suite are
1) Flexibility: -This suite gives a greater flexibility to incorporate data from various sources such as web, file systems, etc and it can transfer this data to third party applications such as SQL, production systems, etc.

Procedure cache is the memory area where compiled query tree run e.g, procedure, batch query. Data Cache is the memory area where data that is required for the current querys running is bought from Disk on to memory for building result sets.

This software works with storage replication systems and replicates the database to an available ASE or any database back up devices such as oracle. It increases the security of applications and also reduces the fail over time for data applications.

Open switch business software helps you to work on the back up system without any hassles making your primary database server secured. It gives you connectivity to the primary database from the secondary server even in the middle of the application.

Replication server software makes you to manage multiple data platforms very easily. Some of the features which it provides are heterogeneous replication and synchronization across various applications bi directionally. It provides services from the client to server and server to client.

Coalesce is also available in Sybase
This functions evaluates the listed expressions and returns the first non-null value. If all the expressions are null, coalesce returns null

USAGE: coalesce(expression, expression [, expression]...)

EXAMPLE: coalesce(initialqty,finalqty,middleqty)

Returns the first occurrence of a non-NULL value in either the initialqty or finalqty or middleqty

Here are four approaches. Before attempting any of the following: Backup, Backup, Backup.
Dump and Restore
1. Backup the databases on the device, drop the databases, drop the devices.
2. Rebuild the new devices.
3. Rebuild the databases (Make sure you recreate the fragments correctly - See Ed Barlow's scripts (http://www.tiac.net/users/sqltech/) for an sp that helps you do this if you've lost your notes. Failure to do this will possibly lead to data on log segments and log on data segments).
4. Reload the database dumps!

Twiddle the Data Dictionary - for brave experts only.

1. Shut down the server.
2. Do a physical dump (using dd(1), or such utility) of the device to be moved.
3. Load the dump to the new device
4. Edit the data dictionary (sysdevices.physname) to point to the new device.

JDBC is a Application programming Interface which helps Java Developers to interact with Database like Sybase via DML Statements (Insert, Update, Delete and so on).

RAID
RAID means several things at once. It provides increased performance through disk striping, and/or resistance to hardware failure through either mirroring (fast) or parity (slower but cheaper).

RAID 0
RAID 0 is just striping. It allows you to read and write quickly, but provides no protection against failure.

RAID 1
RAID 1 is just mirroring. It protects you against failure, and generally reads and writes as fast as a normal disk. It uses twice as many disks as normal (and sends twice as much data across your SCSI bus, but most machines have plenty of extra capacity on their SCSI busses.)
Sybase mirroring always reads from the primary copy, so it does not increase read performance.

RAID 0+1
RAID 0+1 (also called RAID 10) is striping and mirroring together. This gives you the highest read and write performance of any of the raid options, but uses twice as many disks as normal.

Why Increase It?
Basically, it will allow a task to be scheduled onto the CPU for a longer time. Each task on the system is scheduled onto the CPU for a fixed period of time, called the timeslice, during which it does some work, which is resumed when its next turn comes around.

The process has up until the value of ctimemax (a config block variable) to finish its task. As the task is working away, the scheduler counts down ctimemax units. When it gets to the value of ctimemax - 1, if it gets stuck and for some reason cannot be taken off the CPU, then a timeslice error gets generated and the process gets infected.

On the other hand, ASE will allow a server process to run as long as it needs to. It will not swap the process out for another process to run. The process will decide when it is "done" with the server CPU. If, however, a process goes on and on and never relinquishes the server CPU, then Server will timeslice the process. Potential Fix
1. Shutdown the ASE
2. %buildmaster -dyour_device -yctimemax=2000
3. Restart your ASE. If the problem persists contact Sybase Technical Support notifying them what you have done already.

There used to be a section in the FAQ describing how to drop all of tempdb's devices physically from the master device. This can make recovery of the server impossible in case of a serious error and so it strongly recommended that you do not do this but simply drop the segments as outlined below.

Sybase TS Preferred Method of Moving tempdb off the Master Device.

This is the Sybase TS method of removing most activity from the master device:
1. Alter tempdb on another device:

1. alter database tempdb on ...
2. go

2. Use the tempdb:

1. use tempdb
2. go

3. Drop the segments:

1.sp_dropsegment "default", tempdb, master
2. go
1. sp_dropsegment "logsegment", tempdb, master
2. go
1. sp_dropsegment "system", tempdb, master
2. go

Note that there is still some activity on the master device. On a three connection test that I ran:

while ( 1 = 1 )
begin
create table #x (col_a int)
drop table #x
end