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...
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.
Monday, August 8, 2011
Create table within if exists block in Sybase ASE?
So, how is it that you try to check the existance of a sybase table. Well the answer is pretty simple. Fire the following query and you are right into what you want -
if exists(select 1 from sysobjects where name = <<Table Name>> and type = 'U')
begin
<<SQL statements>>
end
go
Okay, so we know how to check the existance of a table, but what if we say that we want to check if a table exists in system catalogue or not and if not then create the table. What to do in this case? Well the obvious syntax that comes o our mind as first thought is as follows -
if not exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
create table MyTab
(
Id int,
Name varchar(100),
Age int
)
end
else
begin
print 'Table already exists'
end
go
Well, is it correct? I dont think so. Why? Because if in case MyTab table was already existing in the sysobjects table and we run this query then we will get the error that table MyTab already exists in the system catalogue. So what is wrong with the statement?
To understand this we need to look deeper into how Adaptive Server Enterprise tries to execute the above query. Well in above case when we fire the if exists clause and define the create table syntax after it, the server first tries to check the presence of the MyTab table in the system catalogue and once it is found the server further finds that the next statement tries to create a table with a name that is already present in the system catalogue and hence throws the error.
In case if we would have defined a drop table command within the if exists block then the command would have run successfully as in that case the server checks if the required table is already in the system catalogue and if so tries to drop it which is prefectly valid. The drop command is as follows -
if exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
drop table MyTab
end
else
begin
print 'MyTab does not exists.'
end
go
So, coming back to the question - What to do in case we require to create table within the if exists block in Sybase ASE? Is there any way by which we can achieve this?
Well the answer is - Yes, ofcourse. How? Lets figure it out...
if exists(select 1 from sysobjects where name = <<Table Name>> and type = 'U')
begin
<<SQL statements>>
end
go
Okay, so we know how to check the existance of a table, but what if we say that we want to check if a table exists in system catalogue or not and if not then create the table. What to do in this case? Well the obvious syntax that comes o our mind as first thought is as follows -
if not exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
create table MyTab
(
Id int,
Name varchar(100),
Age int
)
end
else
begin
print 'Table already exists'
end
go
Well, is it correct? I dont think so. Why? Because if in case MyTab table was already existing in the sysobjects table and we run this query then we will get the error that table MyTab already exists in the system catalogue. So what is wrong with the statement?
To understand this we need to look deeper into how Adaptive Server Enterprise tries to execute the above query. Well in above case when we fire the if exists clause and define the create table syntax after it, the server first tries to check the presence of the MyTab table in the system catalogue and once it is found the server further finds that the next statement tries to create a table with a name that is already present in the system catalogue and hence throws the error.
In case if we would have defined a drop table command within the if exists block then the command would have run successfully as in that case the server checks if the required table is already in the system catalogue and if so tries to drop it which is prefectly valid. The drop command is as follows -
if exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
drop table MyTab
end
else
begin
print 'MyTab does not exists.'
end
go
So, coming back to the question - What to do in case we require to create table within the if exists block in Sybase ASE? Is there any way by which we can achieve this?
Well the answer is - Yes, ofcourse. How? Lets figure it out...
How to change a User Defined Data Type?
User defined data types or UDT's allow a database developer to define their own user defined data types over the base data types available in Sybase ASE. Adaptive Server Enterprise the enterprise version of RDBMS offered from Sybase Inc took care of developers requirement to define new data types via the user defined data types. Though a developer cannot completely define a new data type in Sybase but the developer can still define a customized version of data types to meet their customer requirements.
Lets say we use 250 character length too frequently to meet our customer requirements then we can define a user defined data type 'TEXT' to meet this requirement and use 'TEXT' instead of varchar(250) data type. A new data type can be defined via the following syntax -
Lets say we use 250 character length too frequently to meet our customer requirements then we can define a user defined data type 'TEXT' to meet this requirement and use 'TEXT' instead of varchar(250) data type. A new data type can be defined via the following syntax -
sp_addtype TEXT, varchar(250), null
Apart from customizing the data type UDT's basic requirement is to provide a wrapper to the base data types so that in case the below mentioned data type gets changed the UDT remains the same. However, this statement has a major catch -
The catch is that once we define a column specific to a UDT Sybase does not provide any option to make amendments to this UDT and hence there is nothing like - sp_modifytype which will help us change the UDT - What if we need to amend our TEXT (varchar(250)) to TEXT (i.e. varchar(255))?
So, what to do in this scenario?
Saturday, August 6, 2011
How to get table data in a file in Sybase ASE?
BCP or the Bulk Copy Program is a utility that is provided in Sybase ASE to help the developer get a table's data as output to a file. With the use of bcp utility we can get the data from a table into a file and restore this data back into the table at any later stage.
The bcp command can be used to input and output data from a file to a Sybase table or vice-versa. This is suggested via the use of two keywords - in and out for input and output respectively. The syntax for bcp utility is as follows -
For native mode -
bcp DATABASENAME.OWNERNAME.TABLENAME out “File Path with File Name within quotes” –SSERVERNAME –ULOGINNAME –PPASSWORD –t, -n
For character mode –
bcp DATABASENAME.OWNERNAME.TABLENAME out “File Path with File Name within quotes” –SSERVERNAME –ULOGINNAME –PPASSWORD –t, -c
As mentioned above in case we need to output the result in character mode then we need to use -c option in the bcp command however if we need to output the data in native mode then we use the -n option. Apart from this basic syntax we can also define format file for the data transfer using the -f option.
How to get a SQL query output in a file, Sybase ASE?
While working in support projects for sometime I often realized that it is good to have results of a SQL Query as output in a file for later reference. So, if you do a data fix today and someone later asks you to confirm that the issue was infact fixed by you then you can show them the output file with SQL query output and tell them - "Here it is..."
So now as we have defined one of the purpose of getting SQL query output into a file lets show you how to do the same.
Lets say there is an Employee table with the following structure -
create table Employee
(
EmpId int,
FirstName varchar(100),
LastName varchar(100),
Age int,
Salary float,
ManagerId int,
DeptId int
)
go
Now lets say this table is having around 10,000 records and recently our Department 'Accounts' with DeptId as 1 has a new manager 'TOM' with ManagerId as 2. So we try to update the table Employee using the following query -
update table Employee
set ManagerId = 2
where
DeptId = 1
go
Now after running this script we check our data fix and write the following query -
select * from Employee where DeptId = 1
go
After running this SQL query we see that the output is as expected and the new manager 'TOM' has been set correctly for Department 'Accounts' but we do want to keep a proof of this and so we open the isql in the command prompt (for Windows) and write the following to get the output of this result directly to the file, so we write the following command -
isql -S<<SERVER NAME>> -D<<DATABASE NAME>> -U<<LOGIN NAME>> -P<<PASSWORD>> -i<<INPUT SQL FILE PATH>> -o<<OUTPUT RESULT FILE PATH>> -w1000 -n
and we are done with it. The above statement must be written in the command prompt or UNIX prompt and not within an isql session. Also, in the above statement remove the <<>> markers to execute the statement with actual parameters.
Saturday, July 30, 2011
Add a NOT NULL column to table using Alter Table command in Sybase ASE
While working with database development using Sybase ASE every now and then we tend to hit on a problem due to change in requirements to add new columns to the table definition.
Alter Table command is one such command that helps us add/remove/modify columns from the table efinition. Now you must be thinking that this sounds pretty easy so where is the catch? Well, the biggest catch while using alter table command is that the alter table command does not allow addition of a NOT NULL column to a Sybase table directly.
Wondering why I imphasized on the use of word directly? Well, because just try to use this simple command to add a NOT NULL column to the table -
alter table <<table name>>
add <<column name>> <<data type>> NOT NULL
Alter Table command is one such command that helps us add/remove/modify columns from the table efinition. Now you must be thinking that this sounds pretty easy so where is the catch? Well, the biggest catch while using alter table command is that the alter table command does not allow addition of a NOT NULL column to a Sybase table directly.
Wondering why I imphasized on the use of word directly? Well, because just try to use this simple command to add a NOT NULL column to the table -
alter table <<table name>>
add <<column name>> <<data type>> NOT NULL
and you will be greeted with a nice error message suggesting that this is not allowed in Sybase ASE without specifying a default value for this column. Now, the reason behind this error message is that once the table has been created and a number of rows exist in this table then the addition of a new column should provide some default values for the rows already existing in the table as we want this column to be a NOT NULL column.
But, sometimes we may require that we add a new NOT NULL column to our Sybase ASE table without the use of default. Why? Because we dont want that once this new field has been added to our table then any developer or a user application can insert the data in the table without explicitly specifying some value for this column either purposely our by mistake.
So, now that our requirement has been justified and the error message hitting us what should we do to add a NOT NULL column to our existing table without specifying the default for this column?
Well, enough of the problem definition its time to figure out a solution. So here it is-
Thursday, July 28, 2011
What is DSEdit utility in Sybase ASE and how to use it?
DSEdit or dsedit.exe - It is a program provided by Sybase Inc (now a SAP company) by which we can add new server entries. The dsedit program with the help of a configuration file also called the interface file makes entries in a 'sql.ini' file located in the "Sybase ASE" install directory.
Here is the screenshot of the dsedit utility on Windows machine -
 |
Please click on the image to enlarge. |
Sybase install directory can be found by $SYBASE in unix enviornment else it is usually present in C drive of your computer in case of Windows enviornment. The path for sql.ini in case of Windows is -
C:\Sybase\ini
A sample sql.ini will look like this -
Find all the triggers on a table?
We all know that there are three types of triggers that can be created on a table, namely the insert, update and delete trigger. What if someone asks us to find out trigger associated to a table. Well the first answer would be very much easy - Simply type -
sp_depends <<Table Name>>
and we will get all the objects dependent on the table. The only catch here is that sysdepends table is the prime table that is being used by sp_depends stored procedure to extract the required information and as per the history related to this table - This isn't very reliable because Sybase SQL Server and Microsoft SQL Server both are very bad in maintaining entries in this table. So whats the answer to our problems.
Well, the answer to this is pretty much easy. Simply query the sysobjects table for the table under consideration and check for the following column entries -
1. instrig - For insert trigger object id.
2. updtrig - For update trigger object id and
3. deltrig - For delete trigger object id
Well, apart from these three trigger entries there is one more reserved trigger type which is select trigger represented by seltrig column in sysobjects, however this isn't used in Sybase ASE.
sp_depends <<Table Name>>
and we will get all the objects dependent on the table. The only catch here is that sysdepends table is the prime table that is being used by sp_depends stored procedure to extract the required information and as per the history related to this table - This isn't very reliable because Sybase SQL Server and Microsoft SQL Server both are very bad in maintaining entries in this table. So whats the answer to our problems.
Well, the answer to this is pretty much easy. Simply query the sysobjects table for the table under consideration and check for the following column entries -
1. instrig - For insert trigger object id.
2. updtrig - For update trigger object id and
3. deltrig - For delete trigger object id
Well, apart from these three trigger entries there is one more reserved trigger type which is select trigger represented by seltrig column in sysobjects, however this isn't used in Sybase ASE.
Wednesday, July 27, 2011
What is the maximum nesting level of a stored procedure in Sybase ASE?
Its a very obvious question for a Sybase developer to be faced with this kind of a question - What is the maximum level to which a stored procedures can be nested. Well the answer to somebody could be as short and specific as just run the following config system sp - sp_configure and search for "nested level" and you should see the right answer. This option is usually reflected as 16 however could be changed to some other value < 16.
But what if someone simply says - Well, I dont just believe figures, so can you just tell me a quick implementation of how exactly this works, to which simply say - Yes, Ofcourse!
So, in this article we will try creating two short and specific examples to demonstrate the maximum nesting levels of a stored procedure.
Sunday, July 24, 2011
How to get stored procedure resultset into a table? - Method 2 - Component Integration Service
In my last article I tolld you how we can get the resultset from a stored procedure back into the calling stored procedure or a batch, however the previous method wanted us to do lots of modifications to the existing stored procedure. What if, we are asked to get the resultset of the stored procedure without doing any modifications to the existing called stored procedure? For example - what if someone asks us to use the output from sp_spaceused system stored procedure to get the total number of rows in the table under question?
Well in that case CIS or Component Integration Service in Sybase ASE comes to the rescue. Component Integration Service via the use of proxy tables will help us achieve our desired result. How? Well I will list all the steps required while using CIS and finally we will be able to get what we want. The steps which we must follow to use the CIS are as follows -
How to get stored procedure result into a table? - Method 1
One of the basic requirements while developing in Sybase ASE or any other database is to get the resultset from a stored procedure into another stored procedure or a batch file. While it is pretty simple in Microsoft SQL Server 2005 and later but its much difficult and ofcourse a grey area in Sybase ASE. In this first method we will try to show you the indirect way of getting the result set from one stored procedure back into some other stored procedure. Now, coming back to work..
To get the resultset from one stored procedure say - proc1 into proc2 we will create a temporary table in proc1 with the same structure as the resultset that we expect from proc2 and then we will insert resultset in this temporary table already created in proc1. This will help us to get the resultset back from the proc2 procedure.
To demonstrate this we will first create a stored procedure proc2 which will give us the results from one of our table Employee. The procedure is as follows -
create procedure proc2
as
begin
select
EmployeeId,
EmployeeName,
EmployeeSalary
from
Employee
where
EmployeeName = 'TOM'
end
go
The Employee table is is defined as follows -
create table Employee
(
EmployeeId int,
EmployeeName varchar(100),
EmployeeAge int,
EmployeeSalary float
)
go
Now as we can see that the proc2 stored procedure currently selects a certain subset of the table Employee where the EmployeeName = 'TOM' but the problem here is that we wont be able to use this resultset directly within another stored procedure or a batch script. So what should we do now?
Saturday, July 23, 2011
What does set nocount on does in Sybase ASE or What is the use of nocount set option in Sybase ASE?
Sometimes when we check the system stored procedures we usually find something like this written -
set nocount on
among the first few lines of the stored procedure and you might be wondering what this set option is trying to achieve because when we try to write stored procedures we don't seem to use this set option.
To give the answer to this simple question we will first take your attention towards a very simple query -
select * from MyTAB
set nocount on
among the first few lines of the stored procedure and you might be wondering what this set option is trying to achieve because when we try to write stored procedures we don't seem to use this set option.
To give the answer to this simple question we will first take your attention towards a very simple query -
select * from MyTAB
where MyTAB is a dummy table with only two fields EmpId and EmpAge, both being integers. This table has currently 100 rows. Now
Saturday, July 16, 2011
Find total number of rows in a table without using count(*) or which system stored procedure can be used to find total number of records in a table?
Preferrably when you tend to ask someone - "How to find total number of records in a table?"; then you will receive a very generic answer -
"Simply try select count(*) from TableName and you will get the total number of records.. Simple isn't it?".
However, sometimes when the table sizes we are dealing with at the moment are very large - like in the databases of large financial firms who tend to have large transactional tables to store the data related to each transaction then in such cases try not to use the count(*) method as you might end up getting your session in hung state with no output for long.
The reason why you might end up in such a state is that count(*) tries to check the whole table to find the exact number of records in the table which makes it undergo a table scan and hence takes lot of time, while if what you really care about is the approx number of records in the table then try the following system stored procedure -
sp_spaceused <<table name>>
The system stored procedure sp_spaceused checks the OAM (Object Allocation Map) entry in the tables data page and computes the total number of records stored in the data table.
Simple isn't it?
"Simply try select count(*) from TableName and you will get the total number of records.. Simple isn't it?".
However, sometimes when the table sizes we are dealing with at the moment are very large - like in the databases of large financial firms who tend to have large transactional tables to store the data related to each transaction then in such cases try not to use the count(*) method as you might end up getting your session in hung state with no output for long.
The reason why you might end up in such a state is that count(*) tries to check the whole table to find the exact number of records in the table which makes it undergo a table scan and hence takes lot of time, while if what you really care about is the approx number of records in the table then try the following system stored procedure -
sp_spaceused <<table name>>
The system stored procedure sp_spaceused checks the OAM (Object Allocation Map) entry in the tables data page and computes the total number of records stored in the data table.
Simple isn't it?
Sunday, July 10, 2011
What is the difference between Delete and Truncate command?
Everytime I go for an Interview it is one of the questions that has the highest probability of hitting u - "What is the difference between the Delete and the truncate command?"
Well there are lots of differences between the two commands after all if they would have been similar then the essence of the other would have not been there? So, simply just before giving the answer here are the two commands -
Delete Command -
delete from <table name> <optional where clause>
Truncate Command -
truncate table <table name>
So if we have a look at these two basic commands we find the following differences -
Well there are lots of differences between the two commands after all if they would have been similar then the essence of the other would have not been there? So, simply just before giving the answer here are the two commands -
Delete Command -
delete from <table name> <optional where clause>
Truncate Command -
truncate table <table name>
So if we have a look at these two basic commands we find the following differences -
- Delete command can have an optional where clause which can help delete only a subset of the rows present in the table. So, we can have only 5 rows deleted meeting our condition from a table of say around 1000 rows.
- Truncate command does not have a where clause associated with it and hence can be used to remove all the entries in a table. Delete command in absence of a where clause behaves just like a truncate command with some exception in the internal working. What this difference is - we will come to know a little later in the article.
- Delete command when used within transaction can be rollbacked wherease the same is not applicable with the truncate command. When a truncate command was initially designed it was not able to rollback the truncation of the table however with newer versions of ASE we can rollback the truncate command.
- Delete command is a DML operation however the truncate command is a DDL operation.
- As delete command makes entries in the log for each row deleted from the table hence it also fires the delete trigger. However, as truncate table operation makes entries for only particular data pages deallocation hence the triggers dont fire at the time of truncate command.
Now coming to the internal working of delete and truncate command -
Subscribe to:
Posts (Atom)