Sybase ASE

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

2013-07-07T11:11:00.000-07:00

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?

2011-09-14T10:25:00.000-07:00

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

To insert dummy data into a table just to increase the size of the table in one shot can be done in the following way; however there is a high probability that u may end up filling up ur transaction log in this way if u choose very high input data in one shot. So, here is the example to show how we can do that -

First lets create a table from ASEISQL (a free Sybase editor available on the internet but you can use anything of your choice) like this -

create table TAB1
(
   EmpId int IDENTITY,
   EmpName varchar(100),
   EmpSalary float
)
go

Now open ISQL from cmd prompt as -

goto Run -> cmd.

In the command window type the following -

Isql -S<servername> -U<UserName> -P<password>

Now type the following in the isql prompt -

> use <database name>
> go
> insert into TAB1 (EmpName, EmpSalary) values('Tom', 10000.00)
> go 10000

As we can see the above statement will insert the above value 10000 times in the table TAB1 but this will be a logged event so take care as this will try to fill up your transaction log and you may end up yourself with a hung session. So, be careful!

How to quit from a Sybase ASE session?

2011-09-13T10:37:00.000-07:00

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?

2011-09-13T10:26:00.000-07:00

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?

2011-09-13T09:51:00.000-07:00

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.

To begin with the example, first we will create a table MyTab with the following create table command -

create table MyTab
(
   Id int,
   Name varchar(100),
   Dsc varchar(100),
StartDate datetime,
EndDate datetime
)
go

and then we will create a clustered index on this table using the create index command -

create clustered index MyTabC on MyTab(Id, StartDate)
go

As we can see in the above statement we have created a clustered index on MyTab table over the columns - Id and StartDate.
Just to counter check we will execute a query on sysindexes table to see if we have actually created a clustered index on the table -

select * from sysindexes where id = (select id from sysobjects where name = 'MyTab')
go

On executing the above statement we find that yes we have actually created a clustered index on our table with the index id (refer column indid in sysindexes table) of 1.
Now, just before creating the script to find index column names we will first have a look at the syntax of Sybase function index_col. The syntax for index_col function is -

index_col(Table_Name, Index_Id, key_index, user_id)

As we can see in the above statement -

Table_Name - is the table for whose index we are trying to find the column names,
Index_Id - is the id of the index as per sysindexes system table,
Key_Index - is the index of the field or column which consitituted the index and can vary upto keycnt column value in the sysindexes table,
User_Id - is the owner of the object. In case we do not specify the same then it will default to the current user.

Now, once everything is done and the syntax for index_col understood well, we will try to find out the names of columns in the index using a simple script shown below-

declare @keys integer
select @keys = keycnt from sysindexes where id = object_id("MyTab") and indid = 1
while @keys > 0
begin
select index_col("MyTab", 1, @keys)
select @keys = @keys - 1
end
go

As we can see in the above script we first tried to find out the total key count for the index whose index id is 1; what it actually means is that we will find the key count for the clustered index we have created already which will give us the value 2. We get the key count from the keycnt column in sysindexes table. Once we have the key count with us we simply looped through to find column names one at a time. The command -

select index_col("MyTab", 1, @keys)

on first iteration will give the column name - StartDate and on the next it will give the Id column name.

And finally that's all we required to find the column names in an index. Now as a next step we can refine the above script to form a string and return the result to us but thats an experimental program left for you all... Will be back with some more good stuff later. Till then have a great day ahead..

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

2011-09-10T09:35:00.000-07:00

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?

2011-09-08T12:46:00.000-07:00

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.

How to find tables or database objects created in a database on a particular day in Sybase?

2011-09-08T12:34:00.000-07:00

While working in database it is sometimes required to get a list of tables or database objects created on a particular day and it is not too hard to find the same. However we just need to keep in mind where to look for this particular set of information and we are done with the problem. As we all know that all the database objects when created in a database are maintained in the sysobjects system table on the Sybase SQL Server or Sybase Adaptive Server Enterprise RDBMS hence the best candidate to tell us the creation date/day of the database object should be the sysobjects system tables.

On looking at the sysobjects table we find that there is infact a crdate column which suggests the date on which date and time of the day a particular database object was created and using this information we can find the correct information that we require. A simple sql statement to find all the datasbase objects created on 1st Jan 2010 would be -

select * from sysobjects where crdate = convert(date, '01/01/2010', 0)

The above sql will list all the database objects created on 1st Jan 2010.

Note: As the above query involves coversion os string to date using convert function hence the syntax may vary based on your date settings.

What is the difference between Login and User in Sybase ASE?

2011-09-08T12:23:00.000-07:00

Login and User - two terminologies which are often confused by the new database developers. What is that credential called which is used to connect to a Sybase server? Is it a Login or a User?

To settledown this confusion and give a clear picture to the reader we must first clarify that logins and users - these terms are very vaguely used in computer science every now and then and as far as Sybase is concerned here also we find many people using different terms to specify something else. For example we will often find developers saying - what is the user id and password for logging into the server. As you can see that the above statement is completly incorrect as user is the person who wants log in to the sybase server however that person uses appropriate credentials to login to the system which is actually a pair of login name aka login and password.

To clarify it further we must know that login's basic purpose is to allow a security check on which ever person or application tries to enter into our Server. hence it tries to authenticate and validate the user which could be either a person or an application.

Once the correct user has logged in we must try to provide an identity to the login which has connected to our server and hence have a User attached to this Login. The purpose of User is to restrict or allows access to various parts of databases and also for attaching the ownership to the database objects. Therefore if we do not want to allow certain login to access certain database we can restrict this user by not associating it with an appropriate user id in that particular database.

Now enough of the theory and coming to practical aspect. A login as allows access to the server hence has an appropriate entry in the syslogins table on the master database of the Sybase server while a User attached to a particular login has entries in the sysalternates or sysusers tables in each database. In case, there is no entry for a login in sysusers or sysalternates tables of a particular database then the user is not allowed access to that particular database provided there is no guest user in the database otherwise the required login will be able to connect to the database using guest user.

That explained we can now understand that well there is a Login and there is a User and neither Login is a User nor User is a Login. Happy coding until next article.

Where is Query Plan stored in Sybase?

2011-09-08T12:08:00.000-07:00

Adaptive Server Enterprise or Sybase ASE before processing and executing a query prepares a query plan for each of the user submitted query and then actually executes the query based on the query plan. This is a pretty simple concept, however what is interesting to know here is - Where is the Query Plan stored in Sybase?

Some people might say that the query plan is saved in system tables however that is not completely true. The reason is that the query is the final blue print over which the Sybase server tries to execute the query based on a parse tree. Now, someone might ask out of curiosity that what is a parsed tree then? When a user submits a query in Sybase, the query is first checked syntactically and then a parse tree is prepared for the query execution and the object id's are mapped to the parse tree. While executing the query after preparing the parse tree Sybase server prepares many sets of query plans and estimates its costs. Once the query plan with minimum cost is created the Sybase server selects this query plan and uses it. This query plan exists in the procedure cache of the Sybase server which is recreated and flushed on each server restart. So, if our Sybase server shuts down and restarted again a new query plan will be created on the next execution of the stored procedure from the stored parse tree.

What should be the size of tempdb in Sybase?

2011-09-08T11:53:00.000-07:00

tempdb database or the temporary database in Sybase ASE is the place where Sybase Server while trying to execute the user queries creates all sort of worktables and temporary tables etc. The tempdb database hence is one of the most important databases on a Sybase server. But have you ever asked as what should be the tempdb size which I should keep to keep my Sybase server running happily ever after?

But before that one may ask as why is the size really our concern? To answer this we need to check the exact purpose of tempdb database. As already said the tempdb database is used to do all the intermediary things required by our Sybase server like creating worktables, sorting results and temporary table creation etc, so if all these operations are done in the tempdb database then we must be very sure that the tempdb database doesn't run out of space as there might be lots of users accessing the tempdb database.

So, with reason justified should we start calculating the tempdb size which will serve our purpose? Well, the major problem with this question is that there is no hard and fast rule to determine the tempdb size in Sybase to be very sure that it serves our purpose. Tempdb size is usually determined based on the size and the purpose of the applications using the Sybase databases. If we have very short queries for our applications then we need not require a very large tempdb space, however if we have very complex and lengthy queries that gets fired every now and than, then we need to have a large tempdb size to avoid running out of space. Usually it is recommended that the tempdb size should be approximately 20% - 25% of the largest database supported on a Sybase server. But as already said this figure will not hold true in every case. So, the next time you think of creating a tempdb you know that you need to experiment a lot to choose the optimistic approach.

How to force an Index in Sybase?

2011-09-08T11:25:00.000-07:00

Forcing an index in a query though might be a risky operation however is sometimes useful too as if there are very similar indexes created on a table and we specfically want to use one of them then we can specify the same in the query. How? Well, pretty simple. Simply use the following syntax if you need to force an index while framing the query and Sybase Adaptiver Server will try picking up the specified index. The sql query is as follows -

select * from table_name (index index_name) where col1 = @col1 and col2 = @col2

As we can see in the above statement simply suggesting the index name will help us force an index on a table. The only catch here is that we need to be very sure that what we are doing is what is required and in case of any doubts it is better to leave the rest to Sybase server and simple design the correct query.

Can we use DDL statements in a transaction in Sybase ASE?

2011-09-06T12:48:00.000-07:00

Sybase ASE allows the use of Data Definition Language statements within a transaction and so we can have create table, create view etc statements with the transaction block i.e. between begin and commit or rollback tran.

However, this feature is a configurable option named - "ddl in tran" and is available at database level, which means that the configurable parameter "ddl in tran" needs to be switched on whenever we think we need to use this option. But have you ever thought - "why is it so that ddl in tran is actually a configurable option?" or it would be much better to rephrase as - "Why does Sybase won't recommend using DDL statements within a transaction for normal case?".

Well, the answer to both these questions which infact are same and differ only in the way they express the thought is that whenever a DDL command or Data Definition language commands are executed by Sybase ASE then Adaptive Server needs to acquire locks on system tables and as we all know acquring locks on system tables for a longer transaction might actually degrade database performance. Hence this option is usually not recommended by Sybase, however if we are in dire need of using this option - for example - we need to execute queries using Oracle Hetrogeneous Services (Oracle - Sybase Gateway) which wraps each sql statement run from Oracle end into an implicit transaction before sending the query to Sybase end, we can enable ddl in tran database option but try to keep our transactions as short and specific as possible.

The only place where Sybase strictly disallow us in using ddl in tran option is in the temporary database or the tempdb which is supposed to behave unexpectedly on enabling this option. If we enable this option in temporary database tempdb then the databases may stop responding and behave unexpectedly. Therefore tempdb should never be enabled with ddl in tran option.

Now coming to the basic question as how to enable this option in a Sybase database. The answer to this is - "Use sp_configure system stored procedures". The syntax for enabling this option is -

sp_configure <<Database name>>, 'ddl in tran', true

and to switch off this option simply use -

sp_configure <<Database Name>>, 'ddl in tran', false

Please note - sp_configure is a system stored procedure and while trying to enable/disable this option the user needs to be in the master database.

How many types of control statements are present in Sybase ASE?

2011-09-06T12:20:00.000-07:00

Adaptiver Server Enterprise provides four different types of control statements for performing various tasks in database. These different types of control statements in Sybase are -

DDL or Data Definition Language statements - These are the statements that are being used to define various database objects or alter them. Some of such statements are - create table, create view, alter table etc.
DML or Data Manipulation Language statements - These are the control statements which are used to do various manipulations on the data stored in various database objects. Some such statements are - insert into, update, delete etc.T
TCL or Transaction Control Language statements - These are statements which are being used to control transactions in Sybase database. Example - begin tran, commit tran, savepoint etc.
DCL or Data Control Language statements - These are statements which are used to provide security access to various database objects. Example - grant and revoke.

Sybase Infocenter - One place to know it all.

2011-09-06T12:04:00.000-07:00

Newbies would ask - "Where is the best Sybase (any product including Sybase ASE or Sybase Adaptive Server Enterprise RDBMS) related material on the internet or which is the best Sybase learning material?". Well there isn't anywhere that you have to go to get the required information about relevant products and learning material in Sybase products, just go to -

http://infocenter.sybase.com/help/index.jsp

and you can browse through a collection of learning materials both online and in Adobe Acrobat Reader (pdf) format for learning and mastering the product.

For Example - If I want to know detailed syntax of create table or create database commands in Sybase ASE version 12.5.x or 15.x then simple go to the above link expand the appropriate product link and you can refer developer and administrator learning material in the site which will guide you through every step as you master and excel in the product.

How to create column names with blank space as a separator in Sybase ASE?

2011-09-03T13:17:00.000-07:00

Usually when we try to create a table with some given set of column names we are not able to use blank in between the column name so if we have to create a column say - Employee Name then we create it like EmpName or EmployeeName with no space. What if we require to have the same column name as - "Employee Name" or "Emp Name"? What to do in such a case?

Well, if we will try to create the column name using the simple method then we will hit on the column name error very early as soon as we try to create the table structure or add one using alter table command. So we will try to use similar approach with a slight tweak so as to tell the Adaptiver Server that we want to name the column specifically as what we are suggesting in the command so please let us do the same. A normal create table command to create an Employee table will be as follows -

create table Employee
(
EmpId int,
EmployeeName varchar(100),
EmpAge int,
EmpSalary float,
DeptId int
)
go

However, to get create the table with a blank space in between we will write the same syntax as -

create table Employee
(
   [Emp Id] int,
   [Employee Name] varchar(100),
   [Emp Age] int,
   [Emp Salary] float,
   [Dept Id] int
)
go

and we are done with it.

Lock Table command in Sybase ASE?

2011-09-03T12:53:00.000-07:00

There are basically two types of locks which we can have on a Sybase ASE table namely the -

Shared Lock and
Exclusive Lock

The shared lock is a lock which when taken on a table then the other processes can also aquire a share lock on the same table for reading the data. Basically this type of lock is taken when performing a select and is done automatically by the Sybase ASE server just before reading the data. Once a shared lock has been taken on a table no other process can have an exclusive lock on the table as the shared and exclusive locks are incompatible so the process wanting to take an exclusive lock will need to wait until the read operations have been performed.

Exclusive lock as the name suggests require a single access to the table with no other process able to read or modify the data present in the table at the same time. This is done because when a process requires an exclusive lock on a table then the process wants to update the data on the table which will take sometime and until then if some other process tries to read the data from the table then it might view inconsistent data which wont be preferrable.

Usually, exclusive lock is taken by a process on data modifications automatically and shared lock is taken automatically by the Sybase ASE server for a process just before reading the data from the table, however if we want we can specifically suggest to Sybase ASE server that we want to have an exlusive lock on the table in a transaction. The lock table command syntax to do so is as follows -

lock table <<Table Name>> in <<share|exclusive>> mode {optional} with <<nowait| wait time in sec>>

The above syntax will help us have a shared (share) or exclusive (exclusive) lock on the table.

The above syntax comes with an optional with clause to specify the time the process needs to wait in case the lock cannot be acquired immediately. In case we do not want to wait we can use the nowait option.

Remember, we can acquire locks on tables only within the transactions so we need to have lock statements between begin and commit/rollback transaction blocks.

Also, a lock gets removed once the transaction either gets rollbacked or committed and hence there is no seperate command to remove lock on a table.

How to find the locking scheme of a table in Sybase ASE?

2011-09-03T12:36:00.000-07:00

When a table is already existing in a system and we are asked to find out the locking scheme for a table to figure out the best possible tuning steps then we can use the sp_help stored procedure to find the appropriate locking scheme for the existing table.
The Sybase ASE stored procedure sp_help when run on a table will list the table structure and other relevant information, also it will show us the locking scheme for the table in the message window. So in case your current locking scheme is All Page Locks then the same will be displayed on the screen. To find the appropriate information use the following syntax -

sp_help <<Table Name>>

What is Dynamic SQL or what is the use of Dynamic SQL in Sybase ASE?

2011-09-01T11:08:00.000-07:00

Many times database developers are required to write queries based on data which is not present at the time of creation of scripts/stored procedures etc and Dynamic SQL is a way by which the Adaptiver Server Enterprise allows us to write such queries.

Well you might be thinking whether such situation may actually occur or is it just another R&D work we are trying to do here? So, now we will try to explain when you might be hit on with such a condition. Lets say, we are asked to find which database a particular table name or stored procedure exists and lets say we have around 10 databases to work on including default Sybase databases, so what will you do in such a simple case. Will you try to run a query on sysobjects table for one and for all databases one at a time, or you will try to specifically create 10 different queries to prefix database name in front of each database's sysobject table to find the required information?

The key to database programming is to work effeciently so that the required information is at hand there and then... Never too late, never too early but just in time... So to get this information we will use Dynamic SQL statments to hit on our system tables at runtime with appropriate amendments and we are done with this job.

In case we would have been required to extract this information from a single database then the required sql would have been -

select * from sysobjects where name like '%ObjectName%' and type in ('U', 'P')
go

Here ObjectName is the database object which we want to find using this sql. We have used like in the statement to just insure that we find all the database objects which also match the required name (just to make sure that our user is not forgetting the exact database object name).

Now as we have to use this query for all the different databases hence we will write the following dynamic sql within a stored procedure to get the required result -

create procedure rsp_ObjectFinder
@ObjectName varchar(35)
as
begin

declare
@DBName varchar(35),
@MySQL varchar(255),
@Counter int,
@CountLimit int

create table #MyTable
(
   Id numeric(10,0) Identity,
   DBName varchar(35)
)

insert into #MyTable (DBName)
select Name from master..sysdatabases

select
@CountLimit = @@rowcount,
@Counter = 1

while (@Counter <= @CountLimit)
begin

select @DBName = DBName from #MyTable where Id = @Counter

select @MySQL = 'select ''' + @DBName + ''' DatabaseName,* from ' + @DBName + '..'+'sysobjects where name like ''%' + @ObjectName + '%''and type in (''P'', ''U'')'

exec(@MySQL)

select @Counter = @Counter + 1

end

end
go

As we can see from the above example we have been able to query all the databases for certain database object. This example stored procedure can be further refined to incorporate many more features. Well, thats all for now will be back with more cool facts and topics sometime later...

How to get column names of a table using the bcp utility in Sybase?

2011-08-30T11:36:00.000-07:00

BCP program or the bulk copy program utility is used to extract data from a table into a flat file but can we have the column names of a table along with the table's data in the file?

The bcp utility does not offer a switch to extract column name so we need to have a workaround to achieve the same and what could that be? So, to tackle this situation here is Views to the rescue. To explain this concept we will be creating a table Employee with the following definition -

create table Employee
(
   EmpId int,
   EmpName varchar(100),
   Age int,
   Salary float
)
go

Now, we will create the following view -

create view V as
select
"EmpId",
"EmpName",
"Age",
"Salary"
from
Employee
where 1=1
union
select
convert(varchar, EmpId),
EmpName,
convert(varchar, Age),
convert(varchar, Salary)
from
Employee
go

As we can see here that we have created a view V which will do a union of column names and columns data. On executing this view we get the column name and the data both in a single resultset.

Now what remains is running the bcp command to extract the data to a flat file and the command to accomplish this is as follows -

bcp Database.Owner.ViewName out FilePath -SSERVERNAME -UUSERNAME -PPASSWORD -c -t,

and thats it.. We will get the column name and the data in a flat file simply by creating a view. Isn't it simple?

Types of Temporary tables in Sybase and what is the difference between them?

2011-08-30T10:38:00.000-07:00

How many types of temporary tables are there in Sybase? How often have you encountered this question? Pretty much... Right? Yup! And here is the answer to much liked question related to Sybase ASE.

Sybase Adaptive Server Enterprise allows developers to create two different types of temporary tables namely -

Session Temporary Table
Tempdb temporary table

In general both of these table are created in tempdb database however there are few differences between the two.
The major difference that appears is that session specific temp tables represented by a # (pound symbol) in front of the table name works for the lifetime of a session, which means that once the session is closed the temp table is flushed and all the entries are removed from the system tables. In contrary a tempdb temp table specified by the long name like -

tempdb.owner.temptable

persists even after the session is closed until the Sybase server itself is restarted which will then recreate the tempdb database completely hence removing all the entries from the system catalogue. As session specific table is created and destroyed within a session hence it cannot be used by different users to share the data wherease the tempdb database can be used to share the data between users.

One more difference that exists between these two tables is that session specific table will be suffixed with a 17 digit session number to keep it unique just in case two users in two different sessions create the same session specific temp table with the same name. As the maximum limit of a variable name is upto 30 characters hence we can name a session specific temp table only upto a length of 13 characters before the name will be automatically truncated by the Sybase ASE server. So if we try to name two session specific temp tables like -

#thisis17char and #thisis17characterlong

then the Sybase server will truncate the remaining characters of #thisis17characterlong preceding 13 characters and hence we will be encountered with the error -

Table name already exists in the system catalogue

because temp table #thisis17char already exists.

Will addition or removal of columns in a table effect the view created on the same table in Sybase ASE?

2011-08-29T10:53:00.000-07:00

Wonder what will happen when a view is created on a table but later on the table column list gets altered using the Alter Table command. Will the view automatically get updated with the new/deleted columns in the table?

Well the answer to this is "OfCourse No". Why? The answer to this is much simple than what might appear. We all know that a view is a simple database object which has its definition stored in the syscomments system table and does not store any of the information anywhere in the database. This is opposite to what a table does. Now when a table gets altered and a column which was earlier present in the table has been dropped then on envoking the view the Sybase ASE server will try to check the view definition which will still be holding the column which had been deleted from the table and hence will give us an error that the required column does not exists.

Lets demonstrate the above concept with a simple example -

First we will create a simple table Employee as defined -

create table Employee
(
    EmpId int,
    EmpName varchar(100),
    Age int
)
go

Now we will create a simple view V on this table as follows -

create view V as select * from Employee
go

After creating this view we will try querying the syscomments system table to find out views definition as stored by Sybase ASE server and the query is -

select * from syscomments where id = (select id from sysobjects where name = 'V')
go

On executing this query we find that the Sybase ASE server has expanded the '*' used in the view to the following -

create view V as select Employee.EmpId, Employee.EmpName, Employee.Age from Employee
go

As we can see the Sybase ASE server has replaced '*' and expanded it with column names. Now when we fire an Alter Table command to add or remove a column like -

alter table Employee
drop Age
go

OR

alter table employee
add Salary float NULL
go

then the alter table command does not rechecks that a view is already created on the table Employee and therefore the view is not corrected with the new or deleted column and hence when we try to execute the command -

select * from V
go

after dropping the Age column from the table Employee then as the Sybase ASE server tries to locate the Age column in the table Employee but is not able to find the same hence the Sybase ASE server tries to throw the error -

Invalid column name 'Age'.

which confirms that the view does not gets updated with the Alter Table command.

How to create a temporary table with variable name in Sybase ASE?

2011-08-27T14:09:00.000-07:00

If someone asks you if we can create a table with variable name or with a runtime generated name, then what would be your answer?

Ofcourse as you are reading this article so the answer should be - "Yes ofcourse". The rest of the article will tell you how to do the same. First let me tell you that ofcourse this approach has a short coming of its own but its fun to learn how we will achieve this thing and the shortcoming is that in case we run same script or stored procedure from two different sessions at nearly the same time then one of the session might get error that the table already exists in the system catalogue. Okay, so lets get back to the approach and you will yourself be able to understand the short coming that I have listed here.

So, coming back to work, the situation with us is as follows -

Situation - We want to create a table which will created and dropped on each session but we do not want to use the session temp table.

With the above problem statement we are sure about one thing and that is - We need to create table uniquely.
So what to do in this case as the table will be generated at runtime and we will have no control over the name. Well, we need to think about some unique value which will be unique atleast for a specific session. So what could that be? Well, as we are everytime talking about session session and only session here so what could be better than a session id... Yes, right on track.. So we will use the session id for our job and use the Sybase ASE global variable @@spid to serve our purpose.

Now as we have got the unique id to help us create unique table names so we will write the following sp to serve our purpose -

create procedure MyProc1
as
begin

declare
   @MyTabName varchar(100)

create table MyTab
(
    Id int,
    FirstName varchar(100),
    LastName varchar(100)
)

select @MyTabName = 'MyTab' + convert(varchar, @@spid)

sp_rename MyTab, @MyTabName

print 'Renamed the table MyTab to a unique table name'

select * from sysobjects where name like @MyTabName

end
go

And thats it... As we can see in the above stored procedure we have been able to create a unique table in Sybase ASE without using session temp table or without using dynamic sql. As you can see here that the only place we find a problem with the above stored procedure is when we create the table MyTab and rename it to the new table name. The problem here is that as the two statements are not in the transaction so there might be a case that in case of parallel execution of the same stored procedure we get the error that the table name already exists in the system catalogue. But this can be avoided if we use 'ddl in trans' db option for our Sybase ASE configuration.

Well thats all for now.... will be back with more fun sometime later.. Have fun till then....

Using waitfor time command in Sybase ASE or How to run a stored procedures from different sessions simultaneously at the same time in Sybase ASE?

2011-08-27T13:44:00.000-07:00

Sometimes while working with stored procedures it becomes important for us to test some scenario's where the same stored procedure needs to be run from two different sessions at the same time. But what to do in this case? We wont be able to open two different sessions and manually run the same stored procedure at the same time. So what to do in this case?

Well, nothing to fear in this scenario as Sybase already provides a command to enable a wait or make the session sleep for sometime. The waiting command is very simple and can be used in two versions -

1. waitfor time <<At what time should session run>>
2. waitfor delay <<Till what time should the session wait>>

As we can see the waitfor time command when encountered in the process flow the session is halted until the given time is reached. Example, Lets say we run a script like this at '10:20 am'.

use MyDb
go

select * from MyTab

waitfor time '10:25'

update MyTab set Id = 10 where Id = 5

select * from MyTab
go

Now when we ran the script at '10:20 am' then the script runs through all the parts of the script until it reaches the command -

waitfor time '10:25'

When the control reaches this statement the session is put on hold till system time is equal to '10:25 am' and the rest of the session executes exactly (with minor delay) at the given time. So, if we open two sessions at the same time and write waitfor time as the first line of the script then we will be able to run same stored procedure from two sessions at the same time.

Create table within if exists block in Sybase ASE?

2011-08-08T12:53:00.000-07:00

So, how is it that you try to check the existance of a sybase table. Well the answer is pretty simple. Fire the following query and you are right into what you want -

if exists(select 1 from sysobjects where name = <<Table Name>> and type = 'U')
begin
<<SQL statements>>
end
go

Okay, so we know how to check the existance of a table, but what if we say that we want to check if a table exists in system catalogue or not and if not then create the table. What to do in this case? Well the obvious syntax that comes o our mind as first thought is as follows -

if not exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
create table MyTab
(
     Id int,
     Name varchar(100),
     Age int
)
end
else
begin
print 'Table already exists'
end
go

Well, is it correct? I dont think so. Why? Because if in case MyTab table was already existing in the sysobjects table and we run this query then we will get the error that table MyTab already exists in the system catalogue. So what is wrong with the statement?

To understand this we need to look deeper into how Adaptive Server Enterprise tries to execute the above query. Well in above case when we fire the if exists clause and define the create table syntax after it, the server first tries to check the presence of the MyTab table in the system catalogue and once it is found the server further finds that the next statement tries to create a table with a name that is already present in the system catalogue and hence throws the error.

In case if we would have defined a drop table command within the if exists block then the command would have run successfully as in that case the server checks if the required table is already in the system catalogue and if so tries to drop it which is prefectly valid. The drop command is as follows -

if exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
drop table MyTab
end
else
begin
print 'MyTab does not exists.'
end
go

So, coming back to the question - What to do in case we require to create table within the if exists block in Sybase ASE? Is there any way by which we can achieve this?

Well the answer is - Yes, ofcourse. How? Lets figure it out...

As we can see in the above example we encountered the error only while creating the table because the server first checked the existance of the table in sysobjects table before creating the same and evaluating the condition, so we need to trick our server to deffer this check to runtime and so we will use the execute immediate or exec command to execute the create table statement as a dynamic sql. So, now with dynamic sql to our rescue we can place the create table syntax in a string and execute the statement within the if exists block which will trick the server to evaluate the exec statement only at the runtime and not at the compile time and hence avoiding the error. The syntax with dynamic sql is as follows -

if not exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
exec('create table MyTab (Id int, Name varchar(100), Age int)')
end
else
begin
print 'MyTab table already exists in the system catalogue'
end
go

Simple... Isn't it?