Sybase ASE
All versions
Last updated: 15 March 2002
ASE Quiz Questions: answers 2001
 
This page contains answers to the ASE quiz questions from 2001.
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 2001
In ASE 12.5, which server page size (2Kb, 4Kb, 8Kb or 16Kb) should you use for the following statements to work successfully ?
Statement 1: declare @my_variable varchar(16000)
Statement 2: create table t (a varchar(1500))
Answer:
Short answer: any server page size will do!

Slightly more useful answer: In 12.5, the maximum length for string expressions has been increased to 16384 bytes (from 255 bytes in 12.0 and before). This new limit applies to local variables, stored procedure parameters and string expressions (in 12.5, all built-in string functions can handle such long strings). The best thing about this change is that it independent of the server page size: this means that you can always use these long strings, also when you're running a 12.5 server using the classic, default 2Kb page size.

However, note that while you can happily create such a long, 16000-byte string, this does not mean that you can also store it in a column of a database table. When it comes to the maximum possible length of a column, the server page size suddenly does matter: the maximum length of a row, and therefore the maximum length of a column, depends on the server page size.
A very useful result of these changes in 12.5 is that, in a default 2Kb-page size server, the 255-byte limit on var(char) and var(binary) columns has been increased to 1960 bytes -- this is why the second statement in the above question will work in any 12.5 server.
In servers with larger page sizes, even longer strings can be stored. The details depend also on the table's lock scheme and whether it's a fixed-length or variable-length column:

page sizelock schememax.length (fixed)max. length (variable)
2Kballpages
1960
1948
datarows/datapages
1958
1954
4Kballpages
4008
3988
datarows/datapages
4006
4002
8Kballpages
8104
8068
datarows/datapages
8102
8098
16Kballpages
16296
16228
datarows/datapages
16294
16290

Note that these maximum lengths apply to var(char) and var(binary) columns only; also, the maximum length is specified in bytes (which is identical to characters unless you're using a multi-byte character set).
Also, please note that it is unlikely that you'll be using columns with these exact maximum lengths: usually there will be at some columns in the table as well, and these occupy some space as well. This means that that space available for such a long var(char) column will have to be a little bit less.

Lastly, these limits, and many others, are conveniently displayed by the new command dbcc serverlimits (don't forget to enable trace flag 3604 first!).

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
November 2001
Which T-SQL command performs no function at all ?

Answer:
This is the command reconfigure [with override], which does absolutely nothing at all. This command is a remnant from version 10 and before, where it was required for changing configuration parameters; the with override option was required when the config parameter "allow updates" was changed. With the overhaul of configuration parameters in ASE 11.0 (changing from the config block to the config file), this command became redundant. The command is still there in 12.5 (probably to stop old scripts still containing this command from failing), but it performs no function anymore.
Sybase recommends not to use reconfigure [with override] anymore as it may be removed in future ASE versions. Interestingly, Sybase's own scripts, like installmaster, still contain this command, even in ASE 12.5 (so my guess is that it will still be there in ASE version 15...).

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

October 2001
What is the easiest way to find all tables with a DOL lockscheme ?

Answer:
It depends on your ASE version. The following will always work in 11.9 and later:
select name,
       case sysstat2 & 57344 
            when 32768 then "datarows" 
            when 16384 then "datapages" 
            else "allpages"
       end 
from sysobjects 
where sysstat2 & 57344 in (32768, 16384) 
      -- finds only 'datarows' and 'datapages'
  and type = "U"

However, starting with ASE 12.5, this query can be replaced with a much simpler query using the built-in function lockscheme() which was introduced in 12.5. With this function, all DOL tables can be found as follows:
select name, lockscheme(name)
from sysobjects
where lockscheme(name) in ("datarows", "datapages")
  and type = "U"

Finding all tables having a particular lockscheme can be useful for different reasons. When doing performance tuning, it might be useful to know which tables have which lockscheme. Another example could be a DBA job which routinely runs reorg commands on all DOL tables, but skips APL tables.

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
September 2001
Suppose it's one of those days when everything is going wrong: you realise that, by accident, you've issued a drop table or a delete or truncate table statement on some large, important table in a database that you thought was your test environment but really appears to be your production database. You're frantically hitting CTRL-C to abort the statement, but you're not sure if that has worked. Which immediate action would be your best chance of stopping the command before it completes ? (thus saving your data, your company and your job…)

Answer:
In an emergency like this, your best bet is to immediately issue a shutdown with nowait [predictable disclaimer: be aware that this is a drastic action; I assume no responsability for any consequences]. As long as the server is shut down before the DML statement has completed, you've actually managed to abort the statement because it hasn't been committed. When the ASE server restarts, the non-committed transaction for the DML statement will be rolled back as part of normal recovery and your table and data will still be there.
You could of course also try to simply kill the session in which that DML statement is running, but this probably takes a little more time (because you first need to figure out the spid to be killed), and that might just be enough for the command to complete, and your data to be gone.

Needless to say, a shutdown isn't nice. However, this is an emergency situation, and a shutdown is not half as ugly as having to restore that table from a previous dump (which would lead to a much longer downtime anyway).

Obviously, you should better avoid accidentally dropping your production tables, but the above scenario is certainly not hypothetical: databases for test and development purposes often have the exactly the same schema as the production database, and you wouldn't be the first DBA who thought he was working in his test environment while really he was working in a window connected to the production server…

Lesson learned: always double-check which environment you're accessing before doing destructive actions (which is just one good reason for always defining your @@servername).

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
August 2001
To find out an ASE server's name, you normally run select @@servername. Can you also get the same information using fewer keystrokes ? (less typing minimises RSI risks ! ;-)

Answer:
Indeed you can: by executing a short command that causes a syntax error, because the error message always includes the server name (which is "PROD1" in the example below). In isql this takes only five keystrokes (including hitting 'enter' twice):
1> -
2> go
Msg 102, Level 15, State 1
Server 'PROD1', Line 1
Incorrect syntax near '-'.
If you'd use the -c option of isql without specifying a terminator, an empty line will execute the batch, so three keystrokes would be sufficient (hit 'enter' twice):
1> -
2>
Msg 102, Level 15, State 1
Server 'PROD1', Line 1
Incorrect syntax near '-'.
If you'd be using use sqsh, three keystrokes would also be sufficient:
PROD1.master.1> -;
Msg 102, Level 15, State 1
Server 'PROD1', Line 1
Incorrect syntax near '-'.
Typing a single letter or digit will also generate an error message (provided there is no stored procedure of that name in your database). You get the idea…

In case you don't see a servername displayed, but something like below, this means that your servername is NULL (=undefined) because it hasn't been defined yet (to define your server's name, run sp_addserver YOURSERVERNAME, local):
1> -;
Msg 102, Level 15, State 1
, Line 1
Incorrect syntax near '-'.
(thanks to Kermit Lowry, III for the tip about 'isql -c')
(thanks to Carl Newman for pointing out that the NULL servername case wasn't covered)

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
July 2001
In ASE 12.5, which was released in June 2001, two major improvements have been made to the disk init command. Do you know which ones ? (note: one of these is not documented…)

Answer:
There are three improvements to disk init worth mentioning:

1. specifying the device size in Kb, Mb or Gb (new in 12.5, documented)
2. automatic assignment of the vdevno (new in 12.5, not documented)
3. direct re-use of a vdevno (existed since 11.5, but is still not documented)

Ad 1. The best-known improvement to disk init in ASE 12.5 is that you can now specify the size of the device directly in Kbytes, Mbytes or Gbytes, instead of specifying the size as a number of 2Kb pages (a significant improvement indeed!). Example:
disk init name = "data_dev1", 
physname = "/opt/data/dev1", size="750M"
Ad 2. Another improvement seems to be undocumented. It is shown in the above example: you don't need to specify the dreaded vdevno parameter anymore in ASE 12.5. When omitting vdevno, ASE will simply take the first unused vdevno available.
Note: if you would ever have to repair a server using the disk reinit command, you will have to specify the vdevno parameter, and it must be the same as the vdevno used when the device was created, irrespective of whether this vdevno was specified explicitly or assigned automatically as above -- so make sure you keep a copy of the sysdevices table (or the sp_helpdevice output) in some safe place.

Ad 3. There is, in fact, another undocumented feature which already existed in ASE 11.5, but which is still not described in the ASE documentation: vdevno's are immediately re-useable when the device has been dropped (previously in 11.0, the vdevno remained "in use" until the server was cycled). This means the following will work fine:
1> disk init name = "data_dev1", 
2> physname = "/opt/data/dev1", 
3> vdevno=5, size="1M"
4> go
1> sp_dropdevice data_dev1
2> go

(note: for file system devices, we must now use a 
different file name (or delete the old file first)
1> disk init name = "data_dev1", 
2> physname = "/opt/data/dev1a", 
3> vdevno=5, size="1M"
4> go
For the technically interested (and that's most of you who are reading this page): this works because since 11.5 an undocumented command disk release exists which is used in sp_dropdevice to free up the internal structures occupied by a device (see the source code of sp_dropdevice). Somehow this very useful improvement never seems to have made it into the ASE documentation...

Summarising, these features make a DBA's life a little easier. No more calculating multiples of 512; no need to figure out which vdevno's are in use; and less server reboots to re-use a vdevno after you've dropped a device (and some nostalgia, of course: in a few years time, who will remember what the number "16777216" was ever used for ?)

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
June 2001
raiserror is a trivial command, right ?

Question 1: which of the following four raiserror statements are correct, and which are wrong ?
-- ensure message 20000 exists
sp_addmessage 20000, "test message: %1!"
go
-- ensure message 20001 does not exist
sp_dropmessage 20001
go

1) raiserror 20000, "this is a test"
2) raiserror 20000 "this is another test"
3) raiserror 20001 "this is yet another test"
4) raiserror 20001 "and this is %1!", "yet another one…"
Answer: All four raiserror statements are correct, though their behaviour is different. In all cases, the specified error number is placed in @@error. The difference is in the message that is printed, and this depends on the absence or presence of the comma following the error number: when this comma is present, as in example 1, the error message must actually exist (i.e. created using sp_addmessage). When the comma is not there (as in the other examples), it doesn't matter whether the error message exists or not: the specified error number is placed in @@error and the specified text string is printed literally.
Note that whenever a formatting specifier ("%1!", etc.) is present somewhere (as in examples 1 and 4), a corresponding argument must be present, otherwise a syntax error will result. For example, raiserror 20000 would be wrong because a formatting placeholder was specified in message 20000.
BTW, note that raiserror 20000, "this is a test" does not just display message 20000: it displays message 20000 in the language which is in effect for the current session. If the error is not available in this language, it will display the message in the default language (US English), provided the message exists.

Question 2: raiserror is allowed only for error numbers >= 17000 (try doing a raiserror 123). Why then, does sp_addmessage only allow you to create error messages for error numbers >= 20000 ? What's so special about the 17000-19999 range ?

Answer: The range of error messages 17000-19999 is reserved for the system stored procedures in the sybsystemprocs database: the messages 17000-20000 are used by the system stored procs for displaying messages to the user. For example, running sp_help with a non-existing object name will display message 17461. Messages 17000-19999 are stored in master..sysmessages and are created by the installmaster script.
Messages 20000 and higher are reserved for use in your own applications, and stored in the sysusermessages table in the current database. sp_addmessage contains hard-coded checks that stop you from adding error numbers lower than 20000 (of course, nothing would stop you, the DBA, from changing sp_addmessage, but that's probably not a good idea).
For completeness, messages 1-16999 are also stored in master..sysmessages but cannot be used by raiserro; these messages are used by the server to communicate error messages to you. By default, all these messages are in US English. When installing additional Sybase-supported languages in ASE, translated versions of these messages are created as well.

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
May 2001
Suppose you do the following:
  1. BCP-out a table to a file
  2. delete all rows from the table (or truncate it, or drop it)
  3. BCP-in the data back into the table (using the same BCP mode and terminators as in step 1)

...then the rows in the table will always be the same as before we started; right or wrong ?

Answer: Wrong! That is, in most cases the data will indeed not have changed -- this is of course the normal behaviour as every DBA knows it. However, it is often overlooked that there is one situation where the data actually will change in these steps. Try the following:
create table t (a char(8), b int default 99 NULL)
go
insert t values ("row one", 1)
insert t values ("row two", NULL)
go
select * from t
go
a        b
-------- -----------
row one            1
row two         NULL

So far so good; now do the three steps described above:
% bcp yourdb..t out yourfile.bcp -Usa -Ppswd -SYOURSERVER -n

truncate table t
go

% bcp yourdb..t in yourfile.bcp -Usa -Ppswd -SYOURSERVER -n


And the data now looks like this:

select * from t
go
a        b
-------- -----------
row one            1
row two           99 

 

Is this what you expected ? The second row does not have the value NULL in column 'b' anymore, but 99, which is the value of the default for that column. Without doing any update, and just copying data to/from a file, data has actually been changed ! How is this possible ?
This behaviour is caused by the combination of the default on column 'b', the NULL value in the second row, and BCP-in. When BCP-ing the data into this table, inserting the NULL value works as an "implicit" insert, meaning that no value was specified and therefore the default value (99) will be used (the same would happen for the following statement: insert t (a) values ("three") -- this would insert a row with value 99 for column 'b'). In contrast, the original insert statement explicitly specified the NULL value and this supersedes the default. You cannot change the way BCP treats NULL values -- it just inserts them as if no value was specified, resulting in the above behaviour.
This can be a real problem in practice, when rebuilding a table with BCP; for example to defragment the table, when having corruption problems or when fixing an identity gap. In all these cases you run the risk that data is modified in the process while you don't expect it.
Note, BTW, that the various ingredients leading to this problem are all described in the ASE documentation (though the net result of their combination isn't).

The remedy for avoiding this problem is simple: remove the default before doing the BCP-in, and restore it afterwards.
If a declared default was used (as in the above example), this can be done by setting the default temporarily to NULL before the BCP-in:
alter table t replace b default null

After the BCP-in is completed, restore the original default as follows:
alter table t replace b default 99

In case the default was created as an object and bound to the column (or datatype) with sp_bindefault , you must unbind it from the column with sp_unbindefault , do the BCP-in and then rebind it with sp_bindefault .

To find all columns where this problems might occur (i.e. those columns having both a default and allow NULL values), you can use the following query:
select object_name(id) + "." + name "Table.Column", 
       object_name(cdefault) "Default"
from syscolumns 
where cdefault > 0 and status & 8 = 8

You may want to inspect the data in these columns to see if it actually contains any NULL values. Here's what you can use to automatically generate the queries to do this inspection:
select "select '" +  object_name(id) + "." + name + "', 
       count(*) '#null values' from " + object_name(id) +
	   " where " + name + " = NULL"
from syscolumns 
where cdefault > 0 and status & 8 = 8
Lesson learned: don't do a BCP-in unless you've verified that the above problem won't occur !

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
April 2001
What's wrong about this T-SQL program, if anything ?
declare @i int
while 1 = 1 -- always true
begin
   if @i = 100 
      break -- exit the loop
   select @i = @i + 1
   exec my_proc @i
end
Answer: There's nothing wrong with the loop control method as such: using the C-style control logic with while 1 = 1 to create a closed loop, and jumping out of this loop with a break statement, is perfectly valid. The trouble is that this program is in fact an endless loop, because the loop counter has not been initialised explicitly. Because of this, @i always remains at the same value which it contained after it was declared, namely NULL: when adding 1 to @i in the body of the loop, the result is still NULL, because arithmatic expressions where one of the terms is NULL (such as NULL + 1) are defined to have NULL as the resulting value. Therefore, @i will never reach the value of 100 at which it will exit the loop, and the server will forever remain very busy executing the procedure my_proc ...
To do it right, there should have been a line select @i = 0 preceding the while statement.

Let's look at another example of the same mistake, using a different style of loop control:
declare @i int
while @i < 100
begin
   select @i = @i + 1
   exec my_proc @i
end

This program is not an endless loop. In fact, it does nothing at all, because the body of the loop isn't entered even once: when the condition in the while statement is evaluated, the comparison of @i (which still contains NULL) and 100 is defined to evaluate to "false". Therefore, the loop body is skipped immediately, and the program finishes.

Lesson learned: always initialise your local variables before using them ! (which is good practice in any programming language)

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