Sybase ASE
All versions
Last updated: 06 December 2004
ASE Quiz Questions 2004
 
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 2004
Suppose you're a RepServer DBA, and you want to maintain a historical overview of all changes made to any RepServer configuration parameters. Unlike ASE, RepServer config parameter changes are not logged into the RepServer errorlog file. How can you still keep track of these changes?

Go here for the answer.

November 2004
It's a well-known and documented fact that stored procedure calls in ASE cannot be nested endlessly -- the deepest possible nesting level is 16. The following recursive stored procedure illustrates this (try running it!):
create proc p as 
select level=@@nestlevel 
exec p
go

-- now run it; you'll quickly hit an error
exec p
go
Despite this limitation, do you think it would be possible for a stored procedure to call itself recursively more than 16 times? If so, how?

Go here for the answer.

October 2004
Suppose you're an ASE DBA, and you have been hired to replace another DBA who left abruptly. Your first task is to change the production ASE server's sa password. However, you don't know the current sa password to start with -- and nobody else knows it either (let's assume the previous DBA isn't available for questions).
You know that a new sa password can be generated by starting ASE with the command-line option -psa, but this requires that you shut down the server first. Fortunately, since you've been following these monthly quiz questions, you know how to do this -- see the question for September 2003.

Unfortunately, however, it then appears that the sa login does not have sso_role or sa_role anymore -- apparently the previous DBA revoked these roles from the sa login... As a result, the -psa command-line option fails (since it requires the name of a login with sso_role) and no new password is assigned.
At this point, you may be able to start and stop the ASE server as much as you like, but you cannot change the password of the DBA login, because you simply don't know which login has sso_role. Let's also assume you don't even know a single login name in this server (which is probably a fair assumption for a server you've never seen before).

The question: it seems as if you've been locked out of the ASE server... is it possible to somehow change the password of a login with sso_role, or do you have to recreate the server from scratch and reconstruct the user databases from dumps or device copies?

Go here for the answer.

September 2004
The following T-SQL code interchanges the values of two local variables:
declare @a int, @b int

-- First, assign some arbitrary values to @a and @b:
select @a = 123, @b = 456

-- Then run some magic lines of T-SQL code...
??????????????? --> that's the question! See below...

-- Finally, the values of @a and @b appear to be swapped:
select @a, @b
go

 ----------- -----------
         456         123

(1 row affected)
The essence of this code -the SQL in the position of the question marks- has been omitted.
The question: which are the most efficient (from a resource consumption point of view) T-SQL statement(s) to make this code work?

Go here for the answer.

August 2004
Under normal circumstances, a timestamp column is always guaranteed to be unique for a row. For this reason, it seems timestamp columns are ideal as primary keys (or for making an index unique):
create table mytab (my_column int, timestamp)
create unique index pk on mytab(timestamp)
go
insert mytab (my_column) values (1)
insert mytab (my_column) values (2)
go
Right or wrong?

Go here for the answer.

July 2004
You have a table with many rows -- say, 10 million. Let's assume about 9.5 million rows, identified by certain criteria, must be deleted from this table.
What's the fastest way to do this?

Go here for the answer.

June 2004
As a DBA, you may need to determine which tables in a database contain an identity column. What is the quickest way to determine programmatically (i.e. not through sp_help) whether a table contains an identity column?

Go here for the answer.

May 2004
For a change, this month's quiz question is about Replication Server instead of ASE (mostly because I've been doing a lot of RepServer work lately while working on a yet-to-be-announced RepServer QuickRef guide (stay tuned!)).

RepServer configuration parameters are stored in the RSSD table rs_config. For a RepServer-wide config parameter (like memory_limit or num_threads), the normal way to change its value is through the RepServer command configure replication server. Alternatively, a setting can be changed through the RSSD procedure rs_configure, which is equivalent to manually updating the corresponding row in rs_config directly with a SQL query.

The question: which RepServer-wide configuration parameters in RS 12.6 should not be changed with rs_configure or by a direct manual update to rs_config, but only through configure replication server?

Go here for the answer.

April 2004
Applications in your ASE server are suddenly suffering from heavy locking problems. As it turns out, the culprit is a new application, developed by an external contracting company, that has just been taken into production. This application appears to perform long-running updates on the most frequently used database tables. The exclusive locks held by these update statements block other users trying to access these tables.
Being the DBA, you're called into a meeting with your manager, the external contractor's manager and their developers to discuss a solution. The developers propose to use transaction isolation level 0 for the update statements performed by their application, so that blocking locks will not occur.
How should you respond to this suggestion?

Go here for the answer.

March 2004
A feature requested by a number of ASE users is a way to grant permission for using the truncate table command to other users. Currently, truncate table can only be executed by the table owner, and there is no documented way to grant this command.
Nevertheless, a workaround exists to overcome this limitation. How?

Go here for the answer.

February 2004
A column of the timestamp datatype will always contain a unique value for each row in a database. However, there is a situation when a timestamp column has the same value for different rows. When?

Go here for the answer.

January 2004
This question is about a rather simple update. We have the following tables t1 and t2; both have two int columns and contain two rows:
1> select * from t1
2> go
 a           b1
 ----------- -----------
           0         100
           0         200

(2 rows affected)


1> select * from t2
2> go

 b2          c
 ----------- -----------
         100           3
         100           5

(2 rows affected)
What we want to do is assign the sum of column t2.c to t1.a for matching values in columns t1.b1 and t2.b2. In other words, we want the value 8 (=3+5) assigned to column t1.a for the row where t1.b1 = 100.
The following query is written to accomplish this:
update t1
set a = sum(c)
from t1, t2 
where b1 = b2 
  and b1 = 100
Couldn't be simpler, right? But does this query work correctly?

Go here for the answer.


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.


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