Archive for February, 2011

Load database: A Recurring Issue!

February 24th, 2011 5 comments

Hi Guys,

As we already aware for loading a database , there cann’t be any user online in the DB.

In our env, we are killing these spids before actual database load in script.

Sometime it works, sometime fails as clinet ids logged in so frequently in server so during the actual load statement database again comes in use status.

This is very frequent issue which I have seen in many enviorment where clined ids logged in very rapidally.

For that we using the some alternative as locking the ids manually and once the load starts we unlock.( We cannt wait untill load finish for unlock of ids) . This task required the manual intervention.

I would like to raise a question, why sybase cann’t provide the load database with kill option, beacuse any above such alternative would not be 100% effective as killing the spids ( by any way) and loading the database is two diffrent task. Between both task execution, there must be some time gap may be in millsecond, and that time gap is suffcient for any login id to logged ins.

One more option can be :

We can make database in offline state( something like that so no one can use it) before load database, aftre that we will go for load database.

Kindly let me know your thoughts and suggest if any thing I am missing above.

I have posted the same in various forums as well, will update you if I’ll get any response.

till then..


Categories: ASE Tags: , , , ,

Sybase Interview Ques Updated!!

February 21st, 2011 No comments


Updated the Sybase ASE/Rep Interview Question Page which I came across recently.

Please have a look @

Happy Learning !!


Default,Rules and User Defined Datatype

February 6th, 2011 No comments

Consider a table creation script
create table table1(id int not null,
cost smallmoney default $10
will it allow null value in column “cost”?


Source (Unleashed)
How to find all tables and columns which are bind with a rule/default?
select “table”, “column”,”user”=user_name(uid),”rule”=object_name(domain)
from sysobjects o,syscolumns c
and object_name(c.domain)=’rule/default name’

Sequence of events when using datatypes
sp_addtype ssn_type, ‘char(9)’, ‘not null’

create rule ssn_rule as
@ssn between ‘001111’ and ‘1111111’
or @ssn = ‘N/A’

create default ssn_default as

sp_bindrule ssn_rule,ssn_type
sp_bindefault ssn_default,ssn_type

create table ssn_table
(ssn ssn_type, name varchar(30))
A rule or default bound explicitly to a column override a rule or default bound to a datatype. A subsequent bind to the datatype replaces the bind to the column as long as the column and datatype have the same rule or default prior to modification.

Renaming Objects

February 6th, 2011 No comments

(Source – Unleashed)
If name of table is changed then stored procedure and views would work or will change to invalid state?

Even though the name of table has changed, objects like views and procedures that refer to that table by name are not affected by the change in the name. That is because SQL-based objects like Views and Procedures are stored both as text in the syscomments table and pre-parsed query tree identifying related objects by ID instead of Name. When a table name changes, a dependent view still works because the objectID of the table (stored in sysobjects) does not change.

Performance Tuning – Scenario Based

February 1st, 2011 No comments

Suppose we have a join order as below in our query
#tmp > Table1 > Table2>
but query plan would be making join order as below
Table1 > Table2 > #tmp
and it would be causing a whole index scan on both the tables Table1 and Table2 (which is as per the people expectation) before we use temp table to filter out results. However, still we can get performance improvement.
Solution –> We can use “set forceplan on” to force the join order. Though we will have table scan on Table1 and Table2 because of “forcing”, we still could get the performance improvement by avoiding index scan on the large tables Table1 and Table2 because table scan on less number of records (due to filteration by #tmp table) can be faster than index scan on large number of records.