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..
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.
How many types of control statements are present in Sybase ASE?
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.
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
How to create column names with blank space as a separator in Sybase ASE?
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.
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?
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>>
Thursday, September 1, 2011
What is Dynamic SQL or what is the use of Dynamic SQL in Sybase ASE?
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...
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...
Subscribe to:
Posts (Atom)