ASE Quiz Questions

June 2002
Let's use T-SQL to do some math!
How do you calculate a number's factorial, also known as n!, in a single select statement, without using any loops ?
(quick reminder: n! is defined as 1*2* ... *(n-2)*(n-1)*n, for n >= 0. Hence, 5! is 120)

Answer:
ASE contains a set of mathematical and trigonometric built-in functions, which can be used to perform various mathematical operations, including many that are more complex than those built-in functions themselves. Calculating a number's factorial is just one such example. You can do this as follows:

-- calculating the factorial of n (change n to some number)
select exp(sum(log(id))) from sysobjects where id <= n 

1> select exp(sum(log(id))) from sysobjects where id <= 5
2> go

--------------------
          120.000000

(1 row affected)

You're probably scratching your head about this calculation now (university is probably a lwhile ago for most of us...). First, we're just using sysobjects as an easy way to generate a bunch of sequential numbers, which we need for this trick; we happen to know that the system table object IDs form a series of sequential numbers (actually, there's more to say about this, see below).
Next, the mathematical basis for the expression exp(sum(log(n))) is formed by the following two formulae:

x^a * x^b = x^{a + b}
and
e^{ln x} = x (where e¹ = 2.7182818284..., and ln is the natural logarithm)

From these formulae, and the fact that e^x and ln x are implemented by the T-SQL functions exp() and log(), respectively, it follows that the factorial is calculated by the above query. Note that the sum() aggregate implements the addition of the exponents as in the first formula.

For completeness, note that the above formula could also be written (in a slightly more complicated way) as follows:

select power(exp(1), sum(log(id))) 
from sysobjects where id <= n

As mentioned above, this trick relies on having a table with a list of numbers. It is also required that this list of numbers does not contain any gaps, and this is the limitation of using the sysobjects, because no system tables with object IDs 20, 28 or 29 exist. So as long as you're not calculating factorials of 20 or higher, you're fine (though you'll find that you'll quickly get floating-point results for increasing values of n).
For a better method, you could use something like this (admitted: this is more than one select statement now...):

create procedure sp_factorial
   @n int
as
begin
   select n=identity(3) into #factorial
   from syscolumns

   select 
   factorial = str(exp(sum(log(n))),255,0)
   from #factorial where n <= @n
end

In this code, the syscolumns table is used to generate a unique set of consecutive numbers into a #temp table; this works because syscolumns always contains at least a few hundred rows.
Note that this code converts the result to an integer string using the str() function. This will allow factorials up to 146!, as str() doesn't seem to support result strings longer than 255 characters. When leaving off str(), you can go up to 171! before arithmatic overflow occurs, but the result will be displayed in floating-point notation.

(thanks to Rutger van Veenendaal for suggesting this topic)

May 2002
You are happily working in an ASE server, but your collegue just can't seem to connect to the server. How do you quickly, and unambiguously, determine the network address (= host + port number) on which the ASE server is listening ?

Answer:
Determining the network address from within the ASE server is relevant when troubleshooting connectivity problems, for example when interfaces files are not correct or not found.
You can quickly find out the server's network address with the following simple query:

select * from master..syslisteners

(On ASE 12.5.1+, you can also run sp_listener 'status')

This will give you the desired information as long as you're not using TLI (on Solaris for example), as TLI uses a long hexadecimal string in which the IP address and port number are encoded. In that cases, run the following query:

select 
substring(address_info, charindex("\x", address_info), 99) a
into #t
from master..syslisteners
where address_info like "%\x%"

select 
convert(varchar(3),hextoint(substring(a,11,2))) + "." +
convert(varchar(3),hextoint(substring(a,13,2))) + "." +
convert(varchar(3),hextoint(substring(a,15,2))) + "." +
convert(varchar(3),hextoint(substring(a,17,2))) "IP address",
convert(varchar,hextoint(substring(a,7,4))) "Port"
from #t

Lastly, the network address is also listed in the ASE errorlog (but that's usually not as quick to determine as when using the above queries). Also, it involves the risk of looking in the wrong errorlog -- which is why using the above queries is the preferred method when any doubt should be avoided.

I haven't used ASE with LDAP yet, but I guess that the above queries would also use in combination with LDAP (experiences, anyone ?).

April 2002
In January 2002, Sybase Inc. has moved its corporate headquarters from Emeryville, CA to a new building in Dublin, CA.
Could this real-world move have any *technical* impact on the work of an ASE DBA ?

Answer:
Everyone's first reaction will be: this is nonsense. When Sybase physically moves its office to some new location, why should an ASE DBA have to care about this?

Sure. Of course. There shouldn't be any impact at all !

Well, here's what happened to me recently:
Whenever I'm troubleshooting a customer's ASE environment, I always check out the last part of the ASE errorlog. More specifically, I want to look at some things in the errorlog at the moment of the last ASE reboot (such as whether the devices are using asynch I/O or not). For many years, I've done this check as follows: open the errorlog in an editor, go to the bottom of the file, and search back upwards for the place where the last reboot occurred. To find this place, I always search for the string "meryv", which is part of "Emeryville", which happens to be in the copyright-blabla message that is written to the ASE errorlog for every ASE start (before you ask: the reason I'm not including the first letter "E" in the search string is that I normally use 'vi', which is case-sensitive -- and I'm lazy). This method always worked perfectly; I don't remember any occasion where this string also ocurred in another place.

Somewhere last month (March 2002), I did this same thing again, and my conclusion was that the server was last rebooted a few weeks earlier, somewhere in February 2002. However, the local DBA insisted that his server had been rebooted the day before! I was about to tell him that he was really wrong, because I could tell from the errorlog that the last reboot had occurred a few weeks before.
Well, on closer inspection the DBA appeared to be right -- somewhere I had gone wrong, but I didn't immediately see how. It turned out that a recent ASE EBF had been installed a few weeks before, and the copyright message in this EBF did not contain the word "Emeryville" anymore, because it had been replaced with the new coporate address in Dublin, CA. This will be the case for all new releases and EBFs from now on.
In this case, I had almost made a fool of myself by insisting that the DBA didn't know what he was doing; but of course, he was right and I was wrong...

Lesson learned: Making assumptions about the real world can be tricky in software! And from now on, I'll be searching the errorlog for "opyright" or "onfidential" (not for "ublin", because then we'll have the same trouble when dealing with an older ASE EBF...)

March 2002
The following query is really very simple: there's not even a table involved. What is the result of this expression ?

1> select +2*-1*-3+-2--1*+3 
2> go

Answer:
The result of this expression is 4 (did you think it was 7 ?)
This is a bit of trick question: the clue is that the characters -- are actually a comment according to the ANSI SQL syntax. Therefore, the expression being evaluated only consists of +2*-1*-3+-2, and --1*+3 is ignored as a comment.
As I said, this is a trick question, and all those + and - signs are only there to trick you into the wrong answer!
It's not completely hypothetical however: I once made this mistake myself...

February 2002
This month's question is about a select statement which looks very, very simple...
Suppose we have the following, very simple table:

1> select a from my_tab
2> go

 a
 -----------
          10
          20
          30

(3 rows affected)

These two queries on this table seem absolutely trivial (and they are!):

1> select a from my_tab where a > 10
2> go

 a
 -----------
          20
          30

(2 rows affected)


1> select sum(a) from my_tab where a > 10
2> go

 -----------
          50

(1 row affected)

Now, here comes the question: how many rows will the following query, which is a combination of the above two queries, return ? Will there be 1, 2 or 3 rows in the result set ?

1> select a, sum(a) from my_tab where a > 10
2> go

Answer:
Contrary to what you may have expected, the correct answer is that there will be 3 rows in the result set:

1> select a, sum(a) from my_tab where a > 10
2> go

 a
 ----------- -----------
          10          50
          20          50
          30          50

(3 rows affected)

This result may seem strange and counterintuitive, given that this query really looks similar to the two previous queries. Also, when looking at the result set, the where-clause seems to apply to the sum() aggregate, but somehow not to the row for a = 10, which is also listed! What's going on ?

This result is not a bug: it is actually correct in terms of ASE's T-SQL implementation of SQL. However, and this is the clue to understanding the problem, this query is invalid according to the ANSI SQL standard, which recognises aggregates only in the context of group by or having clauses.
Specifically, the ANSI standard specifies that when a select list contains an aggregate function (here: sum()), and that select statement does not have a group by clause, then that same select list may not contain additional columns which are not aggregates. The column a in the above query violates this rule.
Still, ASE allows this non-standard ANSI SQL construct as an ASE-specific SQL extension; however, the semantics of such proprietary SQL extensions may not always be what you expect. In this case, the ASE Transact-SQL User's Guide specifies the following:
"If a select statement includes a where clause, but not a group by clause [...] an aggregate function produces a single value for the subset of rows, called a scalar aggregate. However, a select statement can also include a column in its select list (a Transact-SQL extension), that repeats the single value for each row in the result table."
This means that such a query will return as many rows as exist in the original table, effectively ignoring the where clause on this point. For generating the aggregate, the where clause is applied, however.

Lesson learned: In cases like this, where a query returns an apparently strange result set, it's a good idea to check whether the SQL statement is using some ASE-specific, non-ANSI construct, before calling Sybase Technical Support to report that bug you think you've found in ASE.
You can perform this check by enabling the fipsflagger, which prints a warning for every SQL statement violating the ANSI standard:

1> set fipsflagger on
2> go
1> select a, sum(a) from z where a > 10
2> go
FIPS WARNING: Select list contains aggregate function(s) but 
GROUP BY clause not specified.
 a
 ----------- -----------
          10          50
          20          50
          30          50

(3 rows affected)

January 2002
In ASE 12.5, the server page size can be 2Kb, 4Kb, 8Kb or 16Kb. How can you find out which size your server has been set up with ?

Answer:
There are various ways of finding out the server page size. For example, running sp_cacheconfig will show the existing buffer pools, and the smallest pool size always corresponds to the server page size.
The simplest way of getting this information is to examine the new global variable @@maxpagesize (introduced in 12.5). This variable contains the server page size, expressed in bytes: for a 2KB page size, @@maxpagesize will be 2048; for 4Kb pages it will be 4096, etc.

Interestingly though, there is yet another global variable, promising the same information: this variable is called @@pagesize, and always contains a constant value of 2048 -- so despite the name, this variable has nothing to do with the server page size as we know it in ASE 12.5.
Though @@pagesize is formally undocumented, I believe that this variable is a remnant of the days when ASE (then SQLServer) also ran on Stratus, which was different in that the ASE disk I/O size was 4Kb, instead of 2Kb as on all other platforms (for those who remember: when doing disk init, you had to calculate the device size using 4Kb pages instead of 2Kb). @@pagesize actually indicated this platform-specific disk page size of those days. In fact, @@pagesize can still be found in various places in the standard system stored procedures, where it is used for various space-related calculations.

Go here for a full list of all quiz questions.

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

Site Map Shop Quiz Mailing List Feedback About this site Copyright/Legal