What is Deadlock and how to simulate deadlock in Sybase ASE?

In multitasking system and multiple processes running at the same time over number of resources sometimes we end up into a situation where each process starts waiting for a resource currently held by other resource and none of them in a position to let go off the resources held by them until the claimed resource is allocated to them. Pretty confusing? Yes, it is to some extent so we will first explain why a deadlock occurs. Lets say we have two processes P1 and P2 running parallely and there are two resources R1 and R2. Now both P1 and P2 start a transaction of their own and claim the two resouces in the following manner -

For Session 1 running P1 -

Step 1 - P1 claims R1 in shared mode
Step 2 - P1 wants to claim R2 in Exclusive mode

For Session 2 running P2 -

Step 1 - P2 claims R2 in shared mode
Step 2 - P2 wants to claim R1 in Exclusive mode

Now in the above situation if P1 or P2 runs both of their steps before the other process gets the CPU cycle then there wont be a deadlock however if in case when P1 completes step 1 and P2 starts executing Step 1 then both of the processes will each have a shared lock on separate resources. Now if again process P1 executes Step 2 then P1 will end up waiting for the resource R2 as exclusive mode is incompatible with the shared mode and same will happen with process P2. However, when process P2 will try to have exclusive mode right on the resource R1, system will enter into a deadlock situation and hence one of them will be detected by the system as deadlock victim and will be rolled back with the appropriate message.

How to Insert dummy data in a table to increase the size of the table in Sybase ASE?

Sometimes it is required to do performance testing of stored procedures and scripts over tables with large volume of data no matter of the exact data and the problem is we dont have data in the table. Then how to create dummy data in a table just to increase the size of the data. Well, one solution that we can use is to write insert statements in a while loop and insert same data again and again until we have sufficient data in the table or we can do something else? Yes, we can. How? Lets find it out..

How to quit from a Sybase ASE session?

When connected to a Sybase ASE server we can use the following sybase function to quit the existing session -

syb_quit()

The syntax to quit the session is -

select syb_quit()

On executing the statement we will receive the following information messages -

ct_results(): network packet layer: internal net libraryerror: Net-Library operation terminated due to disconnect

which suggests that the connection has been terminated.

How to find Index List in a Sybase database?

Index List - Yes you heard it right, we want to find the index list to find all the indexes in a database. But how to do that? Someone might say that we can query the sysindexes table and we will be able to get the complete list using a simple query -

select si.* from sysobjects so, sysindexes si where so.id = si.id
go

Well, yes that's right but in this case we will also get table names along with the index names in case we have non-clustered indexes on the table as Sybase makes table names entry in the sysindexes table. So, how about just getting the index names? Well to do that we need to modify the above query slightly and we will be able to get the index list in the database. The modified query is -

select
  si.*
from
  sysobjects so, sysindexes si
where
  so.id = si.id and
  si.indid > 0
go

Wondering why we modified the original query like this?

To answer this lets check the above query again. In the above query we are trying to select only those entries from sysindexes table whose indid i.e. the index id is greater than 0 and for information purpose - Sybase ASE makes a table entry with indid as 0 (Table Scan) in the sysindexes table with clustered index acquiring the position 1 and the rest are the non-clustered indexes. So with this information in mind we can now re-validate that the above query returns only the index list in a Sybase database and that was what was required by us.

How to find the names of columns in an Index on a table in Sybase ASE?

Ever wondered how sp_helpindex can get the column names in an index created on a Sybase table when the same information is not available handy via the system tables. Well, looking into the system stored procedure sp_helptext throws some light on how this can be achieved and what is the master key to find the column names of an index.

To look at the system stored procedure we will get the procedures text via another stored procedure sp_helptext. The syntax to get a stored procedures text is as follows -

sp_helptext <<procedure name>>

So, for our case it will be -

sp_helptext sp_helpindex

Now if we look at the system stored procedure sp_helpindex we will find that there is a special Sybase function index_col which is being used within a while loop to extract column names for each index existing on the table. Therefore to find the column names of an index in a script we can use index_col function. To demonstrate this we will create an example where we will try to find out column names in an index on a table in Sybase ASE and see if index_col function will really help us.

DefnCopy Utility! How to get Views, Stored Procedures, Trigger structure in a text file?

Views, Stored procedures and triggers - sometimes we require to extract the structure or definition of these database objects in a text file, so what to do if somone asks us to do the same?
Don't worry! Defncopy to the rescue!!

Now what is Defncopy? Never heard about it. No worries! Today we will explain you and give you a glimpse about this excellent utility.

Defncopy is a command line utility program provided by Sybase Inc (now SAP company) with the Sybase Adaptive Server Enterprise RDBMS to achieve this for us. For Example - If we have a stored procedure – MyProcedure1 which has the following definition –

create proc MyProcedure1
  @MyVar varchar(100)
as
begin
   select * from MyTable whereName = @MyVar
end
go

and if we want to get this definition of compiled stored procedure MyProcedure1 in a text file then we can use the Sybase Inc provided DefnCopy utility. This utility is also available when we have only the client program installed on our machine.

The syntax for DefnCopy utility via the command prompt is as follows –

defncopy -S <<SERVER NAME>> -U<<USER NAME>> -P <<PASSWORD>> out <<FILE WITHCOMPLETE PATH>> <<DATABASE>> <<OBJECT NAME>>

In the above syntax <<OBJECT NAME>> can be either a stored procedure, trigger, view etc whose definition exists in the syscomments table.

The only catch is that we cannot extract tables definition via this utility as for a table definition we need to query sysobject and syscolumns tables and this definition does not exist in the syscomments table.

Please keep in mind that there is a space between each of the options (switches)specified above and the option value which means –

There is a space between –S and<<SERVER NAME>> in above syntax.

Please note - This utility can be run through the command prompt only or via using the extended stored procedure for cmd.

How to force a Table Scan in Sybase?

Are indexes always useful and mandatory on a table or Is Table scan on a table always a bad news for us? To understand what is good for us we need to know the exact purpose of indexes and what is meant by a Table scan? When a user executes a query and the Sybase server has to iterate over each row of each page in a Sybase table then the scan that Sybase server is currently performing is called a Table scan.

In case we have created indexes and used proper SARGs then the Sybase server will pick up an appropriate index and fetch our results more quickly than any Table scan. However, this is just one part of the story and is applicable only when tables are of very large size. So, always create indexes on a table which is of very large size otherwise there are lots of overheads to maintain and keep the indexes up to date in Sybase. In case of smaller tables it is better to be without Indexes than using an Index. So, what to do in case we have some data in the table and have some indexes created on the table and still want to force Sybase to perform a table scan? This is usually required when the data in a table is serialized or sequential and we are sure that first few records is all that we need to iterate on.

So, now knowing that we need a Table scan in Sybase we need to make sure that Sybase server does not use any index while executing the query. This is called forcing a table scan. To force a table scan in Sybase we can use a simple statement like this -

select * from my_table (index 0) where col1 = @col1

As we can see here that we have suggested (index 0) to be used in the above mentioned query? Wondering why and what does (index 0) specify? Well, 0 here refers to the 0th entry in sysindexes table which is actually representing a table scan. Hence by suggesting the index we can force a table scan in Sybase.