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..
Showing posts with label Sybase ASE. Show all posts
Showing posts with label Sybase ASE. Show all posts
Wednesday, September 14, 2011
Tuesday, September 13, 2011
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.
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.
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.
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.
Saturday, September 10, 2011
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.
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.
Thursday, September 8, 2011
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.
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?
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.
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?
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.
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?
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.
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?
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.
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?
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.
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.
Tuesday, September 6, 2011
Can we use DDL statements in a transaction in Sybase ASE?
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.
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.
Sybase Infocenter - One place to know it all.
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.
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.
Saturday, September 3, 2011
Lock Table command in Sybase ASE?
There are basically two types of locks which we can have on a Sybase ASE table namely the -
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.
- Shared Lock and
- Exclusive Lock
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?
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>>
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>>
Tuesday, August 30, 2011
How to get column names of a table using the bcp utility in Sybase?
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?
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?
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 -
Sybase Adaptive Server Enterprise allows developers to create two different types of temporary tables namely -
- Session Temporary Table
- Tempdb temporary table
Monday, August 29, 2011
Will addition or removal of columns in a table effect the view created on the same table in Sybase ASE?
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 -
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 -
Saturday, August 27, 2011
How to create a temporary table with variable name in Sybase ASE?
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.
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.
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?
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.
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.
Subscribe to:
Posts (Atom)