|
|
|||||||||||||||||||||
|
This page contains answers to the ASE quiz questions from 2003. More ASE Quiz questions: 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001 December 2003 In an ASE 12.5 database, there is a normal user table (i.e. not a proxy table) called xmasquiz, with a single column a. This table contains a fixed set of rows, and is not modified in any way. Yet, when running the query select * from xmasquiz, a different set of rows seems to be returned each time. How do you explain this behaviour? Answer: The table contains 100 single-column rows, with values 1..100. By using an access rule containing a rand() function, a sequence of values is returned between randomly chosen lower and upper bounds. You can reproduce this as follows: create table xmasquiz (a int) go create access rule xmasrule as @a between floor(rand()*70) and 30 + ceiling(rand()*70) go sp_bindrule xmasrule, 'xmasquiz.a' go -- Now insert values 1..100 set rowcount 100 select n=identity(3) into #t from syscolumns set rowcount 0 insert xmasquiz select n from #t go -- Now each select statement will return a -- different set of values! select * from xmasquiz goAccess rules were introduced in ASE 12.5. When querying a table with an access rule, the access rule is invisibly appended to the query's where-clause, so it affects the query's result set. The idea behind access rules is to allow a specific user access to only specific rows in a table, for example only the rows 'owned' by that user. In this setup, each row would contain a column with an indication of the 'owner' of that row, and the access rule would match this column to the executing user's login ID or database user ID. The application of access rules shown here is probably not how the feature was originally intended... but it works! Using access rules requires enabling the configuration parameter enable row level access, which requires the ASE_ASM option to be licensed; this option is enabled in the free ASE Developer's Edition. In the T-SQL code above, note that the essential part is the use of rand(). Multiplying it by 70 and adding 30 is only a cosmetic tweak to create a nice variety in returned result sets. Note that it is equivalent to create two access rules, one as @a >= floor(rand()*70) and the other as @a <= 30 + ceiling(rand()*70), and bind both rules to column a. Prize winners: Congratulations to Alex Vickers, Manoher Gocher and Paul Barker who were the first to come up with a perfect reproduction; they'll receive a free copy of one of my books. Only a small number of replies were actually completely or partly correct, identifying at least 'access rules' at the key to this problem. Some people suggested using the millisecond part of getdate() in the where-clause; while this is an interesting idea, it would lead to different result sets than with rand(), since milliseconds are always a multiple of 3 in ASE. Also, it is difficult to use the millisecond part for determining both the upper and the lower boundary of the result set since the evaluation of the different built-in functions in the where-clause will almost always take place in the same millisecond, so they'll have identical values. Some others correctly aimed at access rules plus rand(), but felt a login trigger part had to be part of the solution as well. Close, though! The majority of the replies suggested things like the use of parallel query processing and/or missing out an order-by clause (wrong: this would not affect the number of rows in the result set), mapping the table to an OS file by means of ASE_XFS or to a different table (wrong: the question stated a proxy table was not involved), or a randomised 'set rowcount' (wrong: the first rows in the result set would still be the same every time). Other attempts to explain this problem involved the absence of any indexes or the use of resource limits. Lastly, an interesting (though also incorrect) solution was proposed by a few people, namely that column a is a Java datatype returning a random series of values. While many surprising things can indeed be done with a Java class as a column datatype, it still returns just one value for each row, so this would not explain the varying number of rows returned every time. Anyway, I hope you enjoyed this question. A happy and database-problem-free 2004 to everyone! November 2003 Suppose you're an ASE DBA, and you have been hired to replace another DBA who left abruptly. Once you've managed to log into the ASE server (see the question for September 2003 for more on that), one of the things you'll want to know about this server is which configuration parameters have been set to non-default values (i.e. which parameters have been changed by the DBA). How can this be determined most efficiently? Answer: Perhaps the most obvious way is to run the sp_configure command, print out all output and go over it with a yellow marker. However, this involves quite a few pages of paper, so a more elegant (and environmentally friendly) method would be nice. Also, keep in mind that there are a few hundred configuration parameters in ASE while only a relatively small number tend to have a non-default setting. A quicker way of answering this question is to run the following query:
select name = substring(name,1,35),
current_value = substring(cur.value2,1,15),
default_value = substring(defvalue,1,15)
from master..syscurconfigs cur,
master..sysconfigures con
where con.config = cur.config
and cur.value2 <> defvalue
-- the next line excludes read-only parameters,
-- but this works only for ASE 12.5+
and cur.type in ('static', 'dynamic')
order by name
This query extracts the non-default parameters directly from the system tables.
Note that this query looks only at the actual run-time value for each parameter. It won't display static parameters for which the 'config value' has already been changed, but ASE has not yet been restarted. Also note that this query always displays the configuration parameter configuration file, even though it is effectively read-only (its value is defined by the -e command-line flag when the ASE server is started). Should you want to exclude this parameter, add the predicate and (cur.config <> 114) to the where-clause. (Incidentally, there is yet another way of finding the non-default configuration parameters: on the command line, run grep -v DEFAULT YOURSERVER.cfg. However, this will not show the default values for these parameters, which is useful information). October 2003 Syntax errors normally cause the execution of SQL code to be aborted. However, syntax errors can also have unexpected side-effects. Can you see the difference between the following batches, which both contain incorrect syntax? -- batch 1 insert MyTable vlaues (1) -- should have been 'values' select * from MyTable go -- batch 2 inset MyTable -- should have been 'insert' select Col from YourTable select * from MyTable go(Hint: there are no tricks with the tables themselves: MyTable and YourTable both exist and contain a single int column named Col) Answer: When executing these batches, the first batch is not executed at all: due to the syntax error at vlaues, the statement is found to be invalid and the entire batch is aborted. This means that the select statement is not executed anymore. This is the normal, expected behaviour. However, the second batch is not aborted: both select statements appear to be executed! Isn't that surprising? The clue is that the second batch does not contain a syntax error, technically speaking. Although inset is spelled where insert was meant, ASE interprets inset as the name of a stored procedure: as documented, the first (non-keyword) identifier found in a batch is automatically supposed to be a stored procedure, even when there is no exec[ute] keyword preceding it. Since the stored procedure inset does not exist (well, it might exist, but let's just assume it doesn't), ASE issues a warning and continues with the next statement (MyTable is interpreted as a parameter to the would-be procedure inset). Technically, the syntax is correct here: it's just that the procedure inset does not exist, and this is considered less severe than a syntax error. In the first batch, ASE sees that vlaues is a misspelled SQL keyword, and abort the batch due to this syntax error. The difference with the second batch is that - in the first batch - ASE already knows we're in an insert statement, and vlaues cannot be part of that statement since a proper keyword is expected in this position. In the second batch, note how the original insert-select is not executed, while the select statement constituting the second part of the insert-select is. Also note that this error cannot be intercepted at run-time by checking @@error following the insert-select: since the select is still executed successfully, @@error will be 0, just as when the insert-select had been executed successfully. The misspelled keyword in the second batch may also lead to surprises (or problems) for the client application, since the server now returns two result sets instead of one. The problem in the second batch, where something completely different is executed than expected, can be avoided by encapsulating the statements in a stored procedure. In this case, create procedure will be the first statement in the batch, and when insert is misspelled as inset in the body of the procedure, this will result in a true syntax error, causing procedure creation to fail. The clue here is that a stored procedure execution inside a stored procedure must be preceded by the exec[ute] keyword, since the stored proc's name is not the first identifier in the batch anymore ('cos that's create procedure). Hence, there is no ambiguity about the meaning of a misspelled keyword as in a T-SQL batch. The problems resulting from the ambiguity described above are an argument in favor of putting production code, no matter how simple, in the form of stored procedures, rather than in a script file with individual T-SQL batches. Still, there is opportunity for error even with stored procedures when using execute-immediate, since the command string submitted to execute-immediate is treated as a T-SQL batch. Hence, the following procedure will behave exactly the same as the second batch above:
create procedure MyProc
as
exec('inset MyTable select Col from YourTable \
select * from MyTable')
go
More than anything else, these examples illustrate why proper testing of all SQL code is essential, and why a close inspection of the test result is required. In my opinion, the worst errors are those that go unnoticed -- the cases above may well qualify.
September 2003 Suppose you're an ASE DBA, and you have been hired to replace a DBA who was fired rather abruptly. Your first task is to change the production ASE server's sa password. However, you don't know the current sa password to start with -- and nobody else knows it either (let's assume the fired DBA isn't available for questions). You know that a new sa password can be generated by starting ASE with the command-line option -p, but this requires that you shut down the server first. What is the most safe and elegant way of stopping ASE without knowing the ASE password? Answer: Strange as it may sound, it is possible to shut down ASE without knowing any password at all. On Unix, this is done by sending Unix signal 15 to the ASE server, with the Unix command kill -15 pid (kill -TERM pid can be used instead; pid is the Unix process ID of the ASE server). When ASE receives this signal, it performs the equivalent of an immediate shutdown with nowait. As such, this isn't really a 'kill', but rather an urgent shutdown request -- the important thing is that the ASE server itself can still do its own shutdown. Note that not everybody can kill just any Unix process: special priviliges are needed for this. Signal 15 is a much gentler way of stopping an ASE server than the more commonly used kill -9 pid (equivalent to kill -KILL pid). With kill -9, Unix kills the ASE Unix process instantly, without giving ASE an opportunity to shut itself down as with signal 15. kill -9 is a very rude way to terminate ASE, and this should only be used as a very last resort method when kill -15 does not work. Also, database corruptions have occasionally been reported as a result of kill -9. Although these are probably rare, it illustrates that kill -9 is not recommended unless there is really no alternative. On NT, the Unix signals don't exist. However, when the ASE server is running as an NT service (as is usually the case), it can be shut down by stopping the NT service. There are various ways to do this, such as using the NT service manager applet in the Control Panel, Sybase Central, or running the net stop SYBSQL_servername command in a DOS box. In most cases, ASE will shut down fine with the equivalent of a regular shutdown (although in rare cases it may not -- in that case, either shutdown the NT host itself and ensure the ASE server doesn't auto-start; or see section 3.5 of my Tips & Tricks book [below] for a stronger way of killing the ASE NT process). These actions all require NT 'Administrator' privilige. When ASE is running in the foreground on NT (i.e. in a DOS box), the server can be stopped by closing the DOS box window; this is equivalent to a shutdown with nowait. Much more can be said about this topic. For example, there are a number of other reasons why kill -15 may sometimes be useful. Also, an equivalent of the Unix kill -9 for NT actually exists (useful since the regular NT kill command isn't capable of killing NT services). These issues are covered in section 3.5 of my book "Tips, Tricks & Recipes for Sybase ASE" -- see there for more details (check out this book -- apart from being useful for you, it'll allow me to continue writing ASE quiz questions and maintian this web site!). August 2003 When creating a table, a name and a datatype are usually specified for each column, i.e: create table MyTable (MyColumn int)However, in some cases, it is not necessary to specify both a name and a datatype, and the latter can be omitted. Do you know when this is possible? Answer: Although a datatype must always be specified for a column in the create table and alter table statements, the exception is the timestamp datatype. A column declared with the name 'timestamp' does not need to be accompanied by a datatype specification, as this defaults to 'timestamp'. Therefore, the following is valid T-SQL syntax: create table MyTable (MyColumn int, timestamp)as is this: create table MyTable (timestamp)The same applies when adding columns to an existing table with alter table: create table MyTable (MyColumn int) alter table MyTable add timestamp nullIn fact, since timestamp is not a T-SQL reserved word (it's the name of a datatype), the following is valid as well: create table timestamp (timestamp)Note, BTW, that a column named timestamp doesn't have to be of the timestamp datatype: create table MyTable (timestamp int) (NB: timestamp columns are special in some other ways as well. My latest book "Tips, Tricks & Recipes for Sybase ASE" discusses some of those aspects. For those who don't own this book, I'll probably do another quiz question about timestamp columns in the coming months) July 2003 As every ASE DBA knows, changing the value of a static configuration parameter requires an ASE restart for the change to become effective. There is a particular static configuration parameter that requires not one, but two ASE restarts before its value is effectively changed. Do you know which one? Answer: The configuration parameter 'enable cis' is static, but cannot immediately be changed by a single ASE restart. The reason is that, since version 12.0, this parameter depends on another configuration parameter, namely 'enable xact coordination'. By default, both configuration parameters are enabled (i.e. set to 1); both are static parameters as well. Before disabling 'enable cis', 'enable xact coordination' must be disabled first. sp_configure simply refuses to disable 'enable cis' until 'enable xact coordination' is disabled and ASE is restarted. Only then can 'enable cis' be disabled; another ASE restart is needed to make the latter change effective. Hence, two ASE restarts are needed to disable 'enable cis'. Interestingly, once both parameters are disabled, they can both be enabled and activated with the same, single ASE restart. Apparently sp_configure is less demanding with respect to the dependency on 'enable xact coordination' in this case. I have to say I cannot really see a good reason why these parameters shouldn't be allowed to be disabled together as well. After all, changing the value of a static configuration parameter does not have any effect until the next ASE restart, so there should not be any harm in disabling 'enable cis' and 'enable xact coordination' together. But, as mentioned earlier, sp_configure does not allow this. My guess is that this behaviour is an engineering oversight... Note that there is in fact a workaround that allows changing both parameters with a single ASE restart: shutdown ASE; manually change both parameters in the configuration file for the ASE server; and restart ASE. Go here for a full list of all quiz questions. |
|||||||||||||||||||||
|
