Sybase ASE
All versions
Last updated: 28 December 2008
ASE Quiz Questions: answers 2008
 
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?

Answer:
The slight drawback of the method described above is that you need to insert a row in order to obtain a new identity value. The inserted rows themselves are useless and can be deleted later, but they will need to be taken care of sooner or later. Also, inserting rows takes I/O, and also there is potential for the inserted rows to cause concurrency problems (which can be addressed with properly chosen partitioning and/or lock schemes). It may be a drawback, but all in all, it's a small price to pay for a high-concurrency number generator which is guaranteed not to return duplicate values.
The good news is that as of ASE 15.0.2, there no longer is a need to insert a row in the first place (you still *can*, but you don't *have* to, if it is just for the purpose of just getting a new identity value). 15.0.2 has a new built-in function reserve_identity() which lets you grab a number from an identity column without having to insert a row at all:
create table keytable (dummy_key numeric(10,0) identity)

-- pick up an identity value:
select @new_key = reserve_identity('keytable', 1) 

-- now use this value in subsequent processing...
This clearly is an improvement over the old approach, since it simplifies using an identity column as a sequential number generator.

Note that reserve_identity() is different from next_identity(), which was introduced in ASE 12.5.0.3: next_identity() returns the next identity value that will be generated to the first session inserting a row -- and that may not be the session calling next_identity(). Also, next_identity() is a read-only function which does not cause any change to the table's identity counter itself.
In contrast, reserve_identity() increments the identity counter and returns the counter value. This happens under the protection of a spinlock, which is of a very short duration, and unrelated to transactional locks. Therefore, many sessions can request identity numbers concurrently by calling reserve_identity(), and they will be get unique identity values without the risk of lock contention (because the subsequent processing --i.e. the resulting transactional locks-- is likely to have a significantly longer duration than the assignment of the identity value itself -- the spinlock).

Note that reserve_identity() returns the identity value as a string, so you will need to convert it back to your desired numeric datatype, e.g.:
select @new_key = convert(int, reserve_identity('keytable', 1) )

select @new_key = convert(numeric(10), reserve_identity('keytable', 1) )

reserve_identity() also fixes another long-standing request: if you know you need primary keys for 3 rows, you'd like to get a block of 3 identity values. But if you need to insert 5 rows, or just 1 row, you'd like to get a block of 5, or just a single value, respectively. There was never a good solution for this problem (setting the 'identity grab size' was too crude and inflexible an approach to be usable). However, reserve_identity() addresses this by letting you specify how many units the identity counter should be bumped up by.
Below is an example of three users all grabbing blocks of values of different sizes from the identity counter:
-- user A:
declare @new_key int
select @new_key = convert(int, reserve_identity('keytable', 3) )
select @new_key
go

 -----------
         123


-- user B:
declare @new_key int
select @new_key = convert(int, reserve_identity('keytable', 2) )
select @new_key
go

 -----------
         126


-- user C:
declare @new_key int
select @new_key = convert(int, reserve_identity('keytable', 1) )
select @new_key
go

 -----------
         128
What happens here is that user A (who came first) requests a block of three values, and gets returned the first value, namely 123 (meaning 124 and 125 will not be given out to anyone else). Next, user B requests two values, and gets returned 126 (so 127 is also for him to use). User C requests just one value and gets 128.

Lastly, I should mention that by default, you can get only one value at a time. To get larger blocks of values you'll need to set the configuration parameter 'identity reservation size' to the largest block size you want to allow.

How would you rate this ASE quiz question?
Great    OK    Average    Boring     

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?

Answer:
It is actually quite simple to determine the size of a raw device: just copy the contents of the device with 'dd' and you'll know exactly how large it is -- but it helps if you don't blindly trust the numbers...
With 'dd', it's quickest to copy it to /dev/null (that trusty bitbucket). Here is what I did:
# dd if=/dev/sda7 of=/dev/null bs=1024
200781+0 records in
200781+0 records out
205599744 bytes (206 MB) copied, 3.47277 seconds, 59.2 MB/s
According to the 'dd' output, clearly my raw device is bigger than 200MB, since it reports '206MB'. Yet I could not create a 200MB master device on it.... something must be wrong here....

Upon closer inspection it appears that those 206 Megabytes are calculated the way disk vendors like it best: one Megabyte is taken to be 1000 Kilobytes, and one Kilobyte is 1000 bytes. When applying the more real-world notion of 1024*1024, 205599744 bytes actually correspond to 196 Megabytes. This is why I couldn't create my 200MB master device!

The only remaining question is why I thought my partition would be 200MB when I created it in 'fdisk'. When specifying the partition's size, I entered +200M... but 'fdisk' rounded that slightly down to an integer number of disk cylinders.
Lesson learned: if you want to be sure of the size of partitions in 'fdisk', calculate the number of required cylinders manually.

How would you rate this ASE quiz question?
Great    OK    Average    Boring     

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.
Reportedly, thousands of web servers have already been infected.
The question: Is it correct that Sybase databases could be vulnerable to this attack?

Answer:
I find it amazing how often you read a news article about a topic that you actually know something about, is off the mark. The blog article above is another example: for some very clear reasons, the SQL attack described could never harm a Sybase database, neither ASE nor ASA/SQLAnywhere (a vital distinction not recognised in the article, incidentally). My guess is that the blogger isn't a SQL specialist.
Anyway, the answer is: this attack can NOT harm Sybase databases -- and for more than one reason. Let's take a look why.

First, what does the attack do?
The blogger at www.trustedsource.org has already conveniently converted the hex codes to ASCII, resulting in basically the following SQL code:
DECLARE @T varchar(255), @C varchar(4000) 
DECLARE Table_Cursor CURSOR FOR select a.name, b.name 
from sysobjects a, syscolumns b 
where a.id=b.id and a.xtype='u' and 
(b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) 
OPEN Table_Cursor 
FETCH NEXT FROM Table_Cursor INTO @T,@C 
WHILE(@@FETCH_STATUS=0) 
BEGIN 
exec('update ['+@T+'] set ['+@C +']=['+@C+']+"--script link to malicious URL--"')
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor
What this SQL code seems to do is to look in the system tables for columns of varchar and text datatypes (in MS SQL Server, that is), and then generates -and runs- update statements that attach a malicious URL to these column values -- that is, I haven't pointed my browser there, but it seems safe to assume that the Javascript at those URLs, located at a '.cn' website, are malicious (decode the hex yourself if you want the full URL).
Now, presumably, the idea is that, after being injected, the SQL is executed in the database. This would put malicious URLs in columns, and I guess those columns might at some point be displayed in a web browser, which now contains URLs to the malware scripts. Someone may then click on such a URL, and trigger the malware script.
Of course, for this sequence to start off at all, the SQL code needs to be executed in the first place, but let's assume it is injected in such a way that the client application actually executes it.
If we believe the reports, quite a few web servers have already been infected though it's unclear if the full scenario has already played out.

Now then, what about Sybase?
At first sight, it does look like familiar SQL code when you're a Sybase user, joining syscolumns and sysobjects on their respective id columns. However, there are various reasons why the attack in this form cannot affect a Sybase ASE or SQL Anywhere database.

First, the SQL syntax shown here is simply invalid on ASE (as well as on SQL Anywhere, but I'll mostly stick to ASE here). In ASE/SQL Anywhere, neither syscolumns nor sysobjects have a column named xtype as they do in MS SQL Server. Therefore, the SQL code will run into a syntax error in Sybase.

Second, in ASE, this SQL code can simply never run successfully due to syntactic requirements: the declare cursor statement must always be in a separate batch. This means, you'd need to have a go first, and then the rest of the batch. That would require more coordination at the level of the client application to submit two consecutive batches instead of one; or, alternatively, create a stored procedure around the code, and then execute the procedure -- but either of these are significantly more difficult to implement in the context of a virus. In MS SQL Server, there is no such restriction so the code runs fine there.

Third, there are other syntax elements that cause this SQL code to produce various syntax errors in ASE and SQL Anywhere -- it is left to the reader to determine which ones (hint: ASE 12.x produces more syntax errors than 15.x).

And lastly, even if the virus writer would manage to jump over all the hurdles above, and the SQL were to execute successfully in a Sybase database, then there would still not be any damage (other than wasting some CPU cycles), because the cursor would always have a result set of 0 rows. Can you see why? (if you're reading this, I'm not giving any further clues here, Mr. Virus Writer...).

All in all, it is completely impossible for this particular attack to ever be harmful to a Sybase database. Don't let the blogger at www.trustedsource.org scare you!
We can take at least some comfort from the fact that zero exploits aimed at Sybase databases have surfaced over the past few years! That's better than most other database vendors can claim...

Anyway, writing a successful virus may be hard -- but writing a successful cross-database virus is surely harder.
Finally, it is interesting to note that there actually appears to be an unexpected upside to NOT being fully syntax-compatible with MS SQL Server... not for vendor lock-in, but for virus lock-out.


How would you rate this ASE quiz question?
Great    OK    Average    Boring     

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?

Answer:
Assuming we have a table named 't' with a column 'c':
create table t (c int)
insert t values (3)
insert t values (4)
insert t values (7)
insert t values (1)
insert t values (9)
... then we can find the Nth largest value with the following query (here, N = 2):
declare @n int
 
select @n = c from 
 (select top 2 c from t) dt
order by c desc
 
select @n
As the reader is invited to verify, this will produce '7', which is the second-largest value in the table above.

This solution combines different aspects of ASE's query processing:
  • select...top N... (available since ASE 12.5.3)
  • Using a derived table (available since ASE 12.5.1)
  • Using a local variable to select multiple column values into (all ASE versions)
The reason why this works is that we're relying on an aspect of ASE that appears surprisingly useful here: when selecting a multi-row result set into a local variable, only the last value in the result set will end up in that variable; the previous values are overwritten. For the purpose of finding the Nth value, this comes in handy.
To find those values 1..N , we're using select top here. However, because the syntax select @n = top 2 c is invalid, the select top 2 c is wrapped into a derived table, and the select @n is then applied to this derived table.
To make things work reliably, it is required that values 1..N are ordered, so an order by is always needed (use ascending order when you want to find the Nth lowest value; use descending for the Nth highest value).

If you wanted to create a stored proc where N is a parameter, you should dynamically create the SQL command with the string value of N, and execute it inside execute-immediate:
create procedure rank_n 
  @nth int,
  @large_or_small varchar(5)
as
  declare @s varchar(500)
  declare @n int

  select @s = 
    'select @n = c from ' + 
    ' (select top ' + convert(varchar, @nth) +
    ' c from t) dt ' + 
    ' order by c ' + 
    case when lower(@large_or_small) = 'large' then 'desc' else 'asc' end

  exec(@s)
 
  select @n
go

1> rank_n 2 , 'large'
2> go
 -----------
           7


1> rank_n 2 , 'small'
2> go
 -----------
           3


Of course, different solutions are possible, but what I like about the solution above is the elegance of using 'select top', which is more intuitive than using 'set rowcount' (though that doesn't require a derived table, and works in all ASE versions):
declare @n int
 
set rowcount 2
select @n = c from t
order by c desc
set rowcount 0
 
select @n

Update 30-Jun-2008: Mark Parsons pointed out that the following syntax is allowed, and therefore all of the above can also be done without a derived table:
declare @n int
 
select top 2 @n = c from t
order by c desc
 
select @n
It appears that select top 2 @n = c is correct syntax, while select @n = top 2 c is invalid. I didn't actually know that, so I learned something again. Thanks Mark!


How would you rate this ASE quiz question?
Great    OK    Average    Boring     

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?

Answer:
I went round in circles for a while with this problem, trying various changes to the stored procedure -without luck-, before I realised I should do the simple things first: *read* the actual error message.
The error message complains about 'MyTab' being an invalid *column* name. Now that's interesting... why would the ASE server say that?
Some quick experimentation shows that you get the same error when running this:
1> select object_id(MyTab)    -- this is 'example A'
2> go
Msg 207, Level 16, State 4
Server 'SYB1502', Line 1
Invalid column name 'MyTab'.
In other words, leaving out the double quotes around MyTab causes error 207. Interesting, but I did not leave out the quotes, did I? After all, that stored proc *does* contain those double quotes -- and indeed it runs fine when executed locally.
So what on earth is going on? I surely cannot just loose those double quotes somewhere down the road?

Things didn't get much clearer when I interchanged the double and single quotes: suddenly the RPC ran fine (see below). Note that I had to use two styles of quotes because I'm using a string ("MyTab") within a string here (the argument of the exec-imm statement).
1> create proc p
2> as
3>   exec("select objid=object_id('MyTab')")  -- this is 'example B'
4> go

1> exec loopback.my_db..p
2> go
 objid
 -----------
  2058183110

(1 row affected)
I just refused to believe I was doing something wrong. But that 207 error was rather hard to deny... (know that feeling?)
After staring at the screen for some time longer, I realised I better figure out the exact SQL code being executed by the proc in RPC mode. That was easily done with the MDA tables: just query monSysSQLText.
However, this only added to the mystery since the executed SQL code was exectly what I expected, with the double quotes in the right place: (note that statements executed in exec-immediate are shown separately in monSysSQLText, without 'exec()' around it):
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)

1> select * from master..monSysSQLText
2> go
 SPID   KPID        ServerUserID BatchID     SequenceInBatch
        SQLText
 ------ ----------- ------------ ----------- ---------------
        ----------------------------------------------------
[...]
    14     4915275            1          15               1
       select objid=object_id("MyTab")
[...]
I finally gave up, frustrated because I couldn't find what was wrong. I wasn't proud to surrender to such a simple 1-line stored proc, but I just couldn't find it.
It was only a few days later when it suddenly dawned upon me: I had been looking in the right place, but at the wrong moment! This realisation came when working on another problem, again involving inspection of monSysSQLText. I noticed that when ASE makes a CIS connection, it executes a series of session-level 'set' commands (I should have known that, since, with hindsight, I'd seen it before -- though I never paid attention to it).
When ASE executes an RPC for the first time, such a CIS connection to the remote server is made and those 'set' commands are executed. However, for subsequent executions, the existing CIS connection is used without running those 'set' commands again. When looking at monSysSQLText earlier, I'd been watching the execution of such a subsequent execution, so those 'set' commands were nowhere to be seen since they were executed only once for this ASE session.
Had I seen the 'set' commands, things would have been clear immediately. Here are a few of those commands:
1> select * from master..monSysSQLText
2> go
 SPID   KPID        ServerUserID BatchID     SequenceInBatch
        SQLText
 ------ ----------- ------------ ----------- ---------------
        ----------------------------------------------------
[...]
    18     5046275            1           1               1
       set quoted_identifier on

    18     5046275            1           2               1
       set textsize 32768

    18     5046275            1           3               1
       set transaction isolation level 1

    18     5046275            1           4               1
       set raw_object_serialization on
[...]
ASE always executes these commands when it makes a CIS connection to another ASE server (including itself). The explanation for my trouble was in the first command: 'set quoted_identifier on'. Setting this option allows SQL code using quoted identifiers (see here for an example) to run correctly when executed in a CIS context. Because of this setting, ASE was interpreting "MyTab" as a 7-character identifier, with both double quotes being part of the identifier. This is quite different from what I intended, namely a 5-character identifier delimited by two double quotes. As a result, we're having essentially the same problem as shown above in 'example A': an identifier without delimiters, which leads to error 207.

A little test confirmed this was indeed the issue:
1> select object_id("MyTab")
2> go
 objid
 -----------
  2058183110

(1 row affected)

1> set quoted_identifier on
2> go
1> select object_id("MyTab")
2> go
Msg 207, Level 16, State 4
Server 'SYB1502', Line 1
Invalid column name 'MyTab'.

-- adding single quotes around "MyTab" :
1> select object_id('"MyTab"')
2> go
 objid
 -----------
  2058183110

(1 row affected)
The cause of the confusion was now clear: quoted identifiers only use double quotes. That's why the problem didn't occur in 'example B' above.
What is not making it easier to understand this problem is that error 207 prints the identifier as 'MyTab', i.e. it doesn't show the double quotes at all, even though the error occcurs because ASE sees the double quotes as part of the identifier.
The solution for the stored procedure is easy now: simply swap the single and double quotes as shown in 'example B' above.

The last remaining question: why does CIS execute these 'set' commands in the first place? This is because CIS determines the 'capabilities' of the remote dataserver to support specific features, one of these being the ability to use quoted identifiers. What matters here is that you cannot stop ASE from running 'set quoted_identifier on' when it creates a CIS connection.
See the CIS User's Guide for details about capabilities.
How would you rate this ASE quiz question?
Great    OK    Average    Boring     

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?

Answer:
The trick to crate a recursive UDF is to use a second UDF to close to loop: UDF#1 calls UDF#2, which in turn calls UDF#1. However, UDF#2 needs to be created as a dummy first (i.e. without calling UDF#1), since it cannot call UDF#1 until that function has been created itself. Once UDF#1 exists, UDF#2 can be dropped and recreated, this time calling UDF#1:
-- first create 'my_udf2' with a dummy body
create function my_udf2 (@q int)
returns int
as
   return @q
go

-- now create 'my_udf', which calls 'my_udf2'
create function my_udf (@p int)
returns int
as
   if @p > 0
      return dbo.my_udf2(@p - 1)
   
   return @p
go

-- now drop 'my_udf2' and recreate it, calling 'my_udf'
drop function my_udf2
go
create function my_udf2 (@q int)
returns int
as
   return dbo.my_udf(@q)
go
You can now call my_udf, which will call itself until the parameter value has been decremented to 0 (again, this is not the most spectacular UDF, but this is all about the concept of recursive functions):
1> select dbo.my_udf(3)
2> go

 -----------
           0

(1 row affected)
Note that there is a downside to this approach, apart from a possible performance impact due to the additional UDF call: since every UDF call adds a nesting level (i.e. @@nestlevel is incremented), you will run out of nesting levels sooner. Also, you may run out of stack space sooner since each UDF call needs to push some information on the session's internal stack.
How would you rate this ASE quiz question?
Great    OK    Average    Boring     


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