Sybase ASE
All versions
Last updated: 01 December 2002
ASE Quiz Questions: answers July-December 2002
 
This page contains answers to the ASE quiz questions from the second half of 2002.
More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001


December 2002
Is it possible to create a table without a name ?

Answer:
The answer is both 'yes' and 'no', depending on your interpretation of the word 'without'...

First, the obvious 'no': of course, each object must have a name: sysobjects.name does not allow NULL values.

The 'yes' answer is far more interesting: although, technically, an object always has a name, this does not mean that this name is always easily recognised as such. Try the following T-SQL statement:
1> set quoted_identifier on
2> go
1> create table " " ( " " int)
2> go
This creates a table with a name consisting of a single space. This is possible because we've enabled the quoted_identifier option, which allows object names with spaces. In fact, the column name is also a single space.
When doing an sp_help " ", the table name and column name are dutifully displayed, but to the human eye, it will seem that no name is displayed at all (you may have to experience this yourself to understand what happens -- so I'll leave running sp_help " " as an exercise for the reader...)

When accessing this table, the quoted name should always be specified:
1> set quoted_identifier on
2> go
1> insert " " values (123)
2> go
(1 row affected)
1> select * from " " 
2> go

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

(1 row affected)

Now, try the following:
1> set quoted_identifier on
2> go
1> create table "  " ( " " int, "  " int)
2> go
1> create table "   " ( " " int, "  " int)
2> go
These statements create two more tables with names consisting of two and three spaces, respectively. Note that each table has two columns, whose names consist of spaces as well.

Returning to the original question: the object name is normally expected to be an identifying and distinguishing attribute of a database object. Technically, that is the case even when these names consist of spaces. However, from a human viewpoint, it could be argued that these aren't really object names because they cannot be recognised as such.

If you don't know that such object names exist in your database, you will probably have a hard time figuring out what's going on. The usual query on sysobjects is not very useful:
1> select name, type, id
2> from sysobjects where id > 99
3> go
 name                           type id          
 ------------------------------ ---- -----------
                                U      228500368
                                U      260500482
                                U      308500653
(3 rows affected)
Instead, try this:
1> select '['+name+']' name, type, id 
2> from sysobjects where id > 99
3> go
name                             type id          
-------------------------------- ---- -----------
[ ]                              U      228500368
[  ]                             U      260500482
[   ]                            U      308500653

(3 rows affected)
By delimiting the object name with brackets, you should be getting a clue...

Lastly, if you want to scare your DBA (or one of your colleagues), try creating a table like this:
1> set quoted_identifier on
2> go
1> create table "***dbcc corruption error!***" 
2> ( "Database corrupt!" int, "Infected with 11!" int)
3> go
Now, just wait until the victim finds out about this table...
(maybe you shouldn't do this in a production database...)

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
November 2002
Suppose you're writing some T-SQL code to produce a report. You want the first lines to be formatted like this:
***************
  R E P O R T
***************
What is the least amount of T-SQL code needed to print these three lines ?

Answer:
Just one print statement is enough:
1> print "
2> ***************
3>   R E P O R T
4> ***************
5> "
6> go

***************
  R E P O R T
***************
What we're using here is the little-known fact that string expressions may extend over multiple lines (I'm not sure whether this is actually documented). All characters between the opening quote on line 1 and the closing quote on line 5 form a string, including the newline characters.
I'm sure you'll appreciate the simplicity of this code, especially when compared with the other, more common, methods below.

Most people would probably have used a separate print statement for each line, or coded the newlines explicitly by using their ASCII value:
1> declare @nl char(1) 
2> select @nl = char(10)
3> print "***************%1!  R E P O R T%2!***************",
4> @nl, @nl
5> go
Note that a single select statement can also be used, but you'll need to add an extra space in column 1 on lines 2 and 3 (because select output by default starts with a space) for each row in the result set):
1> select "***************" + char(10) + "   R E P O R T" + 
2> char(10) + " ***************"
3> go

 -----------------------------------------------------------
 ***************
   R E P O R T
 ***************
(note the dashed line: this is not printed by the select statement, but by isql. To suppress this line, start isql with the -b option)

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
October 2002
Each ASE release is identified by an "EBF number" (also called a "SWR number") in its version string. This EBF number is unique, meaning that Sybase TechSupport can tell exactly which functionality (like bug fixes) is included in the specific ASE version you're running.

The question: is it possible that two different ASE executables have the same EBF number ?

Answer:
The short answer: yes, strictly speaking, two ASE executables can indeed have the same EBF number -- but only in one particular situation.

The long answer:
As the EBF number forms a unique identification of an specific ASE release, it would seem illogical that two ASE executables could have the same EBF number.
Still, this happens!
In fact, it happens all the time: each EBF contains both a dataserver (NT: sqlsrvr.exe) and a diagserver (NT: diagsrvr.exe), and by definition, these executables have the same EBF number:
% dataserver -v 
Adaptive Server Enterprise/12.5.0.1/EBF 10292 IR/P/Linux 
Intel/Linux 2.4.7-10smp i686/rel12501/1781/32-bit/OPT/Thu 
Jun  6 14:14:09 2002
(...)

% diagserver -v
Adaptive Server Enterprise/12.5.0.1/EBF 10292 IR/P/Linux 
Intel/Linux 2.4.7-10smp i686/rel12501/1781/32-bit/DEBUG/Thu 
Jun  6 14:31:26 2002
(...)

As you can see, these executable have the same EBF numbers, but different version strings: the diagserver has DEBUG where the dataserver has OPT (OPT means "optimized"; on other platforms (e.g. Solaris), you may see FBO ("Feedback Optimized") instead of OPT).
Instead of "EBF", you may also see "SWR" preceding the EBF number. "EBF" and "SWR" mean the same thing, and were both used in the past. Recently, "SWR" has been abandoned in favor of "EBF".
(for completeness: diagserver is a diagnostic version of dataserver; you're not supposed to run diagserver unless instructed by Sybase TechSupport).
Note BTW, that the executables were compiled at different times (apparently it takes about 17 minutes to build a dataserver ?)

I admit this is a bit of a trick question - but I'm pretty sure many DBAs would have answered "no" at first sight...

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
September 2002
It is a well-known fact that all objects in an ASE database (tables, procedures, triggers, etc.) have a unique object ID. These object IDs are stored in sysobjects, among other tables.

The question: is it possible that two different objects in the same ASE database actually have the same object ID ?

Answer:
Strange as it may sound, this is indeed possible -- but only in one very specific situation.
Consider the following code:
1> create procedure p;1 as print "This is p1 !"
2> go

1> create procedure p;2 as print "This is p2 !"
2> go

1> exec p;1
2> go
This is p1 !
(return status = 0)

1> exec p;2
2> go
This is p2 !
(return status = 0)
What we're using here is the "procedure grouping" feature of ASE. The stored procedures p;1 and p;2 have the same name (i.e. 'p'), but a different version number -- the semicolon is mandatory syntax. Each procedure can be executed separately by explicitly specifying the version number. When executing, these procedures are fully independent.
(note that you may have difficulties to run the above commands when using sqsh, because sqsh uses the semicolon as a batch terminator. To fix this, edit the .sqshrc file and remove the line saying \set semicolon_hack=on, or use isql or another client instead)

BTW, when you don't specify the procedure version number, the default is 1:
1> exec sp_who;1
2> go
-- this will work !
Below the covers, these two stored procedures are actually treated as one single object: when you look in sysobjects and sysprocesses, you'll see that there is only one procedure p, with 'number' values 1 and 2:
1> select object_id("p;1")
2> go

 -----------
        NULL  <== this means that procedure "p;1" 
                  does not exist as such!


1> select object_id("p")
2> go

 -----------
  1904804621  <== ...but procedure "p" does exist!



1> select name, type, id 
2> from sysobjects where name like "p%"
3> go
 name                           type id
 ------------------------------ ---- -----------
 p                              P     1904804621


1> select distinct id, number 
2> from sysprocedures where id = 1904804621
3> go
 id          number
 ----------- ------
  1904804621      1
  1904804621      2

So this is the only situation where two different stored procedures have the same object ID. Actually, these procedures can only be dropped together, not individually:
1> drop proc p;1
2> go
Msg 102, Level 15, State 1:
Server 'S125', Line 1:
Incorrect syntax near ';'.

1> drop proc p
2> go

-- now both p;1 and p;2 have been dropped...

I think this 'procedure grouping' feature is one of the more bizarre areas of ASE functionality. It has been in ASE as long as I can remember (but I think I only discovered this feature in version 4.9 or 10). In any case, 'procedure grouping' is not ANSI-compliant, but a Sybase-specific SQL extension.
The idea behind 'procedure grouping' is to guarantee consistency between a number of stored procedures, because these cannot be dropped and recreated individually. Still, I have never had a requirement for something like this, and I have never seen this feature actually being used either. So if anyone has ever used this, I'd be interested to know more about the reasons why!.

Lastly, note that there is a configuration option "allow procedure grouping": when this is set to 1 (=default), procedure grouping is allowed; when set to 0, it is not allowed.

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
August 2002
Suppose you want to know when your ASE server was started. How many different ways would you know to determine this ?

Answer:
There are quite a few ways to find the last start date/time of an ASE server. I get as far as nine (though readers of this page have contributed additional ways -- see below):
  1. Probably the most obvious way is to look in the ASE errorlog, which will clearly show when ASE was last started. This will work for all versions and platforms.
    (When searching through the errorlog for messages related to the ASE startup, there's a gotcha -- see the question from April 2002)


  2. You may not have access to the host on which ASE is running, which is where the errorlog is located. In that case, you can also determine the last reboot by looking at the creation time of tempdb:
    select crdate 
    from master..sysdatabases where dbid=2
    
    (to display the time with full precision, use a date formatting style, for example convert(varchar,crdate,109)).

    This is probably the easiest method. It will work for at least version 11.0 and later (in fact, also for ASE version 10 -- thanks to Kazuo Otani who kindly verified this).
    I have a vague recollection of things being different in version 4.x, where the tempdb creation time was always Jan 1st, 1900 (however, I cannot verify this since I don't have access to a working ASE 4.x installation).


  3. In ASE 11.0 or later, you can query the sysengines table to find the starttime of the oldest engine (thanks to David Ferrington for suggesting this method):
    select starttime
    from master..sysengines
    where engine = 0
    
    Or simply as follows (less typing):
    select min(starttime)
    from master..sysengines
    

  4. In ASE 12.5, the server boot time has been captured in loggedindatetime of sysprocesses for the internal system processes:
    select min(loggedindatetime) 
    from master..sysprocesses where suid=0
    
  5. In ASE 12.5.0.1, it's even simpler:
    select @@boottime
    
    The static global variable @@boottime is undocumented, but happens to be identical to sysdatabases.crdate for tempdb.


  6. On Unix, the command ps -ef (or equivalent, depending on your Unix flavour) will show all processes running on that box, along with the time they were started. By looking (or grepping) for "dataserver", you can find when the server was started.
    There's one potential problem with this method: when the server has been running for more than 24 hours, you'll only see the date when the server was started, not the precise time.
    AFAIK, this method cannot be used on NT.


  7. When you're running ASE on NT, and event logging is enabled, the NT event log contains the contents of the ASE errorlog. This means you can also determine the server start time from the eventlog.


  8. When you've installed auditing, you may use the "security" option to configure it to log all ASE startup and shutdown events.


  9. Lastly, for all ASE versions: when the server has been running with a single engine since it was started, you can also find the server starttime as follows:
    select dateadd(ss, 
             floor((@@cpu_busy+@@io_busy+@@idle) *
    		       (@@timeticks/-1000000.0)
    			  ), getdate())
    
    ASE diehards will recognise the logic behind the good-old sp_monitor here. How to adjust this formula for multiple engines is left as an exercise for the reader.



  10. Actually, Brian Fitzgerald pointed out two more methods:

  11. The creation date/time of the file $SYBASE/$SYBASE_ASE/<servername>.krg also corresponds to the server starttime (all platforms).


  12. On some Unix platforms (incl. Solaris, and --I believe-- also HP-UX), you can determine the creation time (but apparently not the date) of the ASE server's shared memory segment using the command ipcs -a .



  13. In 12.5.0.3+, there's yet another way:

  14. The MDA table monState contains the server start date/time in the column StartDate (more information about the MDA tables is here).

How would you rate this ASE quiz question?
Great    OK    Average    Boring      
July 2002
For most dbcc commands, the 3604 traceflag must be enabled to display any output at the client. However, some dbcc commands don't require the 3604 traceflag; do you know which ones ?

Answer:
The only two dbcc commands which will always display their output at the client, regardless of the 3604 traceflag, are dbcc gettrunc and dbcc settrunc (for the non-RepServer users: these dbcc commands operate on the secondary truncation point in the ASE transaction log).

Actually, I have no idea why these commands are exceptions with respect to 3604 -- if anyone knows why, please let me know!

NB: Nobody's perfect. Shortly after this question was published, Tom Oorebeek pointed out that also dbcc checkalloc/ indexalloc/ tablealloc/ checkdb/ checktable/ checkcatalog/ checkstorage/ checkverify do not require 3604. However, some of these, like checkalloc/ indexalloc/ tablealloc do print some extra info with 3604 enabled (thanks Tom !)

NB2: ...and there's more: Darrell Anderson and Mike Chachich pointed out (thanks!) that also dbcc cis and dbcc rebuild_text do not require 3604.

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

Go here for a full list of all quiz questions.


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