Sybase ASE
All versions
Last updated: 28 December 2008
ASE Quiz Questions 2008
 
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 2008
I have long been advocating the use of identity columns to generate sequential numbers without sacrificing concurrency. Basically, the trick is to use a table with only an identity column and insert a dummy row for the sole purpose of obtaining the generated identity value. The basic method boils down to this (I first described it here in 1998 as part of an approach to avoid identity gaps, but the number-generator approach can very well be used on its own as well):
create table keytable (dummy_key numeric(10,0) identity)

-- insert a 'dummy' row to generate new number
insert keytable values () 

-- pick up the identity value
declare @new_key numeric(10)
select @new_key = @@identity  

-- now use this value in subsequent processing...
There can be various reasons why you'd want to use an identity column this way, despite the fact that avoiding identity gaps isn't really a concern anymore these days (since those gaps can be limited in size, and easily repaired if they occur after all). One possible reason is that you may want to perform some additional work before using the identity value as a primary key (for example, by computing and appending a checksum digit). It may also make coding easier if you can decouple generating numbers from using them.

Anyway, here's the question: a significant improvement to this method above has recently become possible. Do you know how?

Go here for the answer.

September 2008
This question is about finding the size of a raw device.
Recently I was creating some raw devices with the Unix/Linux tool 'fdisk' (when working with ASE Cluster Edition you need raw devices for the quorum device and the database devices). I thought I had created a 200MB raw device to hold my 'master' and 'sybsystemprocs' databases, but when I created the ASE CE server, creation of the 200 MB master device failed due to insufficient space being available.
So it seemed I had not created my raw devices with the exact sizes I thought I had... but I didn't want to recreate the raw devices though. What I needed to know is exactly how big my raw devices really were.
The question: what's the easiest, no-brainer way to determine the actual size of a raw device?

Go here for the answer.

August 2008
There have been recent (mid-August) news reports about a new SQL injection atttack making the rounds, obviously aimed at MS SQL Server. Some reports, like the one below, mention that systems with Sybase databases could also be at risk: http://www.trustedsource.org/blog/142/New-SQL-Injection-Attack-Infecting-Machines (mirror).
Reportedly, thousands of web servers have already been infected.
The question: Is it correct that Sybase databases could be vulnerable to this attack?

Go here for the answer.

June 2008
The following quiz question was suggested by Helmut Ruholl, and combines various aspects of ASE's query processing in an interesting way.

Let's say you need to determine the Nth value in a range (e.g. the 3rd highest). ASE does not currently support a 'rank'-type built-in function, but there are other ways to solve this problem. One is by using an identity column, but that involves creating a temporary table (I'm not saying that's a bad thing, but for the sake of this quiz, let's try to do without it).
So, how can you select the Nth value from a range without creating an additional table?

Go here for the answer.

March 2008
Recently, the following problem made me seriously doubt my sanity.
I had a perfectly ordinary stored procedure which had always been working fine. Then I made a change to an application with the result that various stored procedures were now executed in a remote ASE server a CIS RPCs. Whether you execute a stored proc locally or remotely as an RPC should not make any difference for its functionality, and indeed the procedures kept working fine. That is, with one exception: this particular stored proc consistently failed when executed as an RPC, and with an error message that didn't make sense to me.
Below is the (greatly simplified) reproduction of this problem. When executing the procedure locally, it works fine, but when running it as a CIS RPC, I get error 207 (using 'loopback' as a server name pointing back to the server itself is a quick way of reproducing this problem):
(NB: make sure the config parameter "cis rpc handling" is set to 1)

1> create proc p 
2> as
3>   exec('select objid=object_id("MyTab")')
4> go

1> exec p
2> go
 objid
 -----------
  2058183110

(1 row affected)


1> exec loopback.my_db..p
2> go
Msg 207, Level 16, State 4
Server 'SYB1502', Line 1
Invalid column name 'MyTab'.
(return status = 0)

Obviously, I was doing something wrong. But what? This stored procedure seemed too simple and innocent to contain any errors.
How would you solve this?

Go here for the answer.

January 2008
As of version 15.0.2, ASE supports user-defined functions (UDFs) in SQL (as opposed to
Java UDFs which exist since 12.0).

Various folks who've been using SQL UDFs in ASE have tried to create a recursive UDF (i.e. a UDF that calls itself), like the example below (admittedly this is a very simple function -- you can try to spice it up a bit by thinking of this as the recursive definition of subtraction). However, it seems to be impossible to create a recursive UDF:
1> create function my_udf (@p int)
2> returns int
3> as
4>    if @p > 0
5>       return dbo.my_udf(@p - 1)
6>
7>    return @p
8> go
Msg 14217, Level 16, State 1
Server 'SYB1502', Procedure 'my_udf', Line 5
SQL function not found. If you are attempting to call a java UDF, 
Java services are not enabled.
The problem here is that the UDF is referring to an object, which does not yet exist (namely, itself) at the moment the UDF is created. Since UDFs are not statements, but become part of a statement, they can tolerate less than stored procedures which are created anyway if a called stored proc does not currently exist.

Nevertheless, there is a way to make this recursive UDF work. How?

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/quiz2008.html