Sybase ASE
All versions
Last updated: 31 December 2005
ASE Quiz Questions 2005
 
This page contains some quiz-type ASE questions to test your ASE knowledge.
More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.


December 2005
A stored procedure p1 is reverse-engineered, for example with ddlgen, Sybase Central or sp_helptext. The stored proc is then dropped and the reverse-eningeered SQL text is executed. What is the name of the object that has now been created?
(we're assuming the stored proc's SQL text was not encrypted with sp_hidetext)

Go here for the answer.

November 2005
ASE currently does not support timezone attributes in date/time datatypes. How can you still determine the timezone in which your ASE server's host is located?

Go here for the answer.

October 2005
How many rows may be retrieved by the select statement in each of these two SQL code samples?
-- sample 1
create table t1  (k int unique, ....)
insert t1 values (rand()*1000, ....)
insert t1 values (rand()*1000, ....)
insert t1 values (rand()*1000, ....)
[...1000 additional inserts...]
select * from t1 where k = rand()*1000

-- sample 2
create table t2  (k varchar(36) unique, ....)
insert t2 values (newid(), ....)
insert t2 values (newid(), ....)
insert t2 values (newid(), ....)
[...1000 additional inserts...]
select * from t2 where k = newid()

Go here for the answer.

September 2005
Recently I had a discussion about the merits of holdlock (a.k.a. transaction isolation level 3 for a single select statement). Some legacy code had surfaced, basically looking like this:
select * 
from MyTable holdlock
where ...some-condition...
go
The question: does holdlock make sense here?

Go here for the answer.

July 2005
I always have trouble remembering the parameters for stored procedures like sp_addsegment, sp_addthreshold, sp_addalias etc. For example, sp_addsegment requires the segment name and database name as first and second parameters, respectively, while sp_addthreshold wants these parameters the other way around. What's the quickest way of finding out which parameters these procedures expect?

Go here for the answer.

June 2005
This is a problem that took me quite some time to figure out a customer site a while ago.

The customer had a trading system with two central tables in a Parent-Child relationship (let's call the tables 'Parent' and 'Child'; they're both row-locked). When a row was inserted into the Child, the values of some of the inserted columns had to be added to the Parent; this was done through an insert trigger on the Child.
Since multiple inserts of Child rows for the same Parent could happen at the same time, deadlocks occurred a bit too frequently. Changing the application was not an option, so a classic trick to force serialization was applied: the insert trigger first locks the Parent row with a dummy update and then performs the actual trigger work. The idea is that a second Child insert for that Parent row will hit the Exclusive-Row lock on the Parent row and be forced to wait until the first transaction completed.
Basically, the trigger code looked like this:
create trigger Child_insert_trigger on Child for insert 
as
begin
-- first, force serialization in case of multiple
-- Child inserts for the same parent
update Parent
set SomeColumn = P.SomeColumn
from Parent P, inserted i
where P.ParentKey = i.ParentKey

-- now do the real trigger work...
...
...
end
Admittedly, this won't win prizes for subtlety, but this method tends to work fine as far as forcing serialization is concerned. Sometimes this kind of (indeed somewhat heavy-handed) approach may be your only option to alleviate terrible deadlock situations.

Now, the problem: amazingly, even with this trigger-serialization code, deadlocks still kept occurring! These deadlocks always involved two sessions executing the Child_insert_trigger trying to lock the same Parent row to force serialization.
This did not seem logical: when looking at the locks taken out by the trigger (by building a waitfor in the last part of the trigger for example), we could clearly see the Parent row being locked exclusively before anything else happened.

So.... the question is: how is it possible that deadlocks still kept occuring despite these serialization statements?

Here are two additional pieces of information.
First, assume no other activity takes place on these Parent and Child tables other than the inserts into Child.
Second, this is an example of the deadlock details:
Deadlock Id 128: Process (Familyid 0, Spid 59, Suid 3) was executing 
a UPDATE command in the trigger 'Child_insert_trigger'.
SQL Text: insert into Child (...)
Deadlock Id 128: Process (Familyid 0, Spid 48, Suid 3) was executing 
a UPDATE command in the trigger 'Child_insert_trigger'.
SQL Text: insert into Child (...)
Deadlock Id 128: Process (Familyid 0, Spid 48) was waiting for a 
'exclusive row' lock on row 8 page 1334285 of the 'Parent' table 
in database 4 but process (Familyid 0, Spid 59) already held a 
'shared row' lock on it.
Deadlock Id 128: Process (Familyid 0, Spid 59) was waiting for a 
'update row' lock on row 8 page 1334285 of the 'Parent' table in
database 4 but process (Familyid 0, Spid 48) already held a 
'update row' lock on it.

Deadlock Id 128: Process (Familyid 0, Spid 59) was chosen as the 
victim. End of deadlock information.
Go here for the answer.

May 2005
Suppose you need to generate 100000 rows of unique test data values for the following simple table:
create table test_tb (a int, b varchar(32))
The generated column values should all be unique (although the actual values themselves may be meaningless).

What is the easiest way to achive this?

Go here for the answer.

April 2005
Suppose you need to retrieve a randomly selected row from a table (for example, for a workload generator). What is the simplest way to achieve this?

Go here for the answer.

March 2005
Due to circumstances, I haven't been able to produce add a quiz question this month. Sorry!
February 2005
When setting up a warm standby with Replication Server, or when creating a database subscription in RepServer 12.6, the replicate database must be initialised (or, more formally, 'materialised'). This is typically achieved by creating the standby connection, or the database subscription, with the use dump marker clause, and then making a database dump of the primary database. This database dump is then loaded into the replicate database.
When the primary database is large (say, a few 100's of Gbytes), it may take a while to transfer the database dump to the replicate side and to load it, simply because of its size. As a consequence, there can be a long time without an up-to-date standby.
The question: is it possible to perform the initialisation in such a way that the time without an up-to-date standby is reduced?

Go here for the answer.

January 2005
Does ASE give any clues about the exact OS (operating system) version that's running on your ASE host?

Go here for the answer.

 
 This document is located at www.sypron.nl/quiz2005.html