|
|
|||||||||||||||||||||
|
More ASE Quiz questions: 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
December 2005 A stored procedure p1 is reverse-engineered, for example with ddlgen, Sybase Central or sp_helptext. The stored proc is then dropped and the reverse-eningeered SQL text is executed. What is the name of the object that has now been created? (we're assuming the stored proc's SQL text was not encrypted with sp_hidetext) Answer: You would probably expect a procedure named p1 to be created, since that was the name of the object being reverse-engineered. However, surprising as it may sound, the created procedure may well be named differently. Consider the following sequence: create proc p2 as print 'Hello!' go sp_rename p2, p1 goWhen procedure p1 is now reverse-engineered, we will get the SQL text with which p2 was originally created. When executing this SQL, we get a procedure named p2 -- and not p1 as may have been expected. The technical background of this problem is that the SQL text with which a compiled object is created, is stored in syscomments at object creation time. When the object is later renamed, this is not reflected in the saved SQL text. Note that this problem may also occur for views, rules, defaults and triggers since these object types all have there SQL text stored in syscomments. Also note that tools like Sybase Central and ddlgen may add additional SQL statements to the reverse-engineered SQL text featuring the original object's name. You may wonder whether this could ever be relevant? It certainly can be -- I stumbled across this one during an ASE migration/upgrade years ago where the customer did not trust the accuracy of their stored-proc creation scripts, and therefore reverse-engineering of compiled objects was chosen instead. During tests, it turned out that this problem was occurring for one particular procedure. Fortunately, this was discovered in time. Is there any way to tell that you may be hitting this problem? Unfortunately, there is no reliable way to detect this situation beforehand, since you cannot tell from sysobjects alone that an object has been renamed. I can see two ways of approaching this problem. First, the difficult and clumsy one: try to apply some programmatic intelligence to the SQL text extracted from syscomments to see if the original object's name matches the name stored in syscomments -- but you'd need to take things like line breaks and comments into account... The second option might be more practical: just go ahead and create the object from the reverse-engineered SQL. Then try to figure out the name of what you've just created by querying sysobjects for a very recently created object of a certain type, and check whether its name is what you expect. You may want to rename the original object first, to some name that is unlikely to ever be used, and only drop the original object when the recreated object appears to have the expected name (for interested readers, check out how this approach is used in my proc sp_recreate). November 2005 ASE currently does not support timezone attributes in date/time datatypes. How can you still determine the timezone in which your ASE server's host is located? Answer: There are two ways to determine the timezone of the ASE host's system clock. Option 1: Use xp_cmdshell to run OS commands (on Unix, run the 'date' command for example), and parse the results to pick up the time zone. This can be used in ASE 11.5 or later. Go here for examples of using xp_cmdshell with its results imported back into ASE. Option 2: ASE 12.5.3 and later supports the built-in function getutcdate(). This function returns the date and time of the system clock expressed as UTC time (UTC = 'Universal Time Coordinated' = GMT = Greenwich Time = the international 'default' timezoneless time). In contrast, getdate() returns the system clock's time corrected for the local time zone. Armed with this knowledge, you can use these functions to calculate the difference between the local time and GMT. Your first attempt might be a query like this: select datediff (hh, getutcdate(), getdate())While the principle behind this query is correct, there are actually two problems with it. First, this query returns the time difference only as an integer multiple of hours. However, not all timezones have full-hour offsets to GMT. For example, the local time zone in India is defined as GMT + 05:30 hours, while folks in Kathmandu, Nepal, live on GMT + 05:45. In other words, the query above cannot be applied universally since it won't return the accurate offset for all time zones. (for more information about timezones, see -for example- www.worldtimezone.com) The second problem is that the above query implicitly assumes that getutcdate() and getdate() will always return the same number of milliseconds when they are called at the same time. Although that does seem to be the case most of the time, there is no guarantee that it always will be -- it is not impossible that the values returned have a minimal difference in their millisecond fields. For example, in the GMT+1 timezone, imagine there's a 3 millisecond difference between both functions, with getutcdate() returning '10:22:46.016' and getdate() returning '11:22:46.013'. It is left as an exercise for the reader to confirm that the number of hours returned by the datediff() query above will be 0 instead of 1. To work around these problems, the time difference should not be calculated in hours but in a smaller units like minutes: select datediff (mi, getutcdate(), getdate())NB: don't use 'mm' for the datepart since that'll get you the difference in months (which will always be 0 anyway). This query may still return 59 instead of 60 minutes due to the possible millisecond difference, but it will be clear what the time zone offset is. (NB2: Time is a funny concept. When writing this question, I wondered what timezone the South Pole would be in. And how should one handle the international date line once you're down there? I'll let you know when I've figured it out...) October 2005 How many rows may be retrieved by the select statement in each of these two SQL code samples? -- sample 1 create table t1 (k int unique, ....) insert t1 values (rand()*1000, ....) insert t1 values (rand()*1000, ....) insert t1 values (rand()*1000, ....) [...1000 additional inserts...] select * from t1 where k = rand()*1000 -- sample 2 create table t2 (k varchar(36) unique, ....) insert t2 values (newid(), ....) insert t2 values (newid(), ....) insert t2 values (newid(), ....) [...1000 additional inserts...] select * from t2 where k = newid() Answer: First, let's look at what the two samples have in common. Both samples insert rows while attempting to supply unique key values for each row; finally one of the inserted rows is selected at random by suppling a random value as a search argument. Sounds good, right? Well... let's look a bit closer. In sample 1, the value of the inserted key value is an integer between 0 and 1000, based on rand() (which produces a random float value between 0 and 1). Since we've inserted 1000 rows, there's a reasonable chance that the search argument in the select statement matches the key value of one of the inserted rows (note that at most one row can be retrieved since a unique constraint exist). Therefore, 0 or 1 rows may be retrieved. Sample 2 looks essentially identical to sample 1, except that newid() is used instead of rand() to generate unique key values. However, the select statement in sample 2 will never retrieve a row. Why? The reason is that newid() generates UUIDs (Universally Unique Identifiers) or GUIDs (Globally Unique Identifiers). As defined by the IETF, such values are guaranteed to never produce a value that has been generated before. In other words, values generated by newid() are eternally unique by definition. As a result, the invocation of newid() in the select statement will produce a value that has never been generated before -- therefore, none of the rows in the table will match and 0 rows will always be returned. So, while both rand() and newid() involve randomness, the essential difference is that rand() can produce duplicate values while newid() cannot. NB: newid() was introduced in ASE 12.5.0.3. August/September 2005 Recently I had a discussion about the merits of holdlock (a.k.a. transaction isolation level 3 for a single select statement). Some legacy code had surfaced, basically looking like this: select * from MyTable holdlock where ...some-condition... goThe question: does holdlock make sense here? Answer: The function of holdlock is to maintain all shared locks until the end of the transaction. This enforces ANSI transaction isolation level 3, which guarantees that when you run the same select statement twice, you'll get the same result set (i.e. another user won't be able to change or add any rows in the mean time) So, for holdlock to make any sense, you'd need to use it inside a transaction, typically something like this: begin tran select * from MyTable holdlock where ...some-condition... ...other processing, modifying rows from the earlier result set... commitIn the example that started off this quiz question, no transaction was present, so one might be tempted to draw the conclusion (as I did initially) that in this case, using holdlock is simply pointless. Why? Because the whole point of isolation level 3 is to ensure that rows you have read in a statement will not be changed until further notice (i.e. the end of the transaction). OK, let's hold here for a second... There's actually more to it than you might think. Who says there is no transaction involved? It seems we've assumed that the code snippet was to be run in unchained transaction mode (the ASE default). If, on the other hand, this code would run in chained mode (the ANSI default), the picture suddenly changes: in that case, the select statement would start a new transaction if none would be active, and then a holdlock might make more sense. Also, the code could be run by isql -Y, which would also switch on chained mode for that session. So things may not be as simple as we thought... I suppose my main point here is there can be more to a simple piece of code than you'd expect... that piece of legacy code was in fact supposed to run in unchained mode, but I'd just like to show it may be worth taking a step back and considering other aspects. On a side note, I would recommend always being suspicious of holdlock clauses or use of non-default transaction isolation levels: this is complex stuff that tends to be misunderstood and used either incorrectly or for the wrong reasons (holdlock often indicates a desparate attempt to solve problems resulting from bad database design -- albeit at the expense of concurrency). Also see the quiz question for April 2004. July 2005 I always have trouble remembering the parameters for stored procedures like sp_addsegment, sp_addthreshold, sp_addalias etc. For example, sp_addsegment requires the segment name and database name as first and second parameters, respectively, while sp_addthreshold wants these parameters the other way around. What's the quickest way of finding out which parameters these procedures expect? Answer: Just run the stored proc without parameters, and ASE will print an error message telling you which mandatory parameters are expected. The parameter names usually indicate quite clearly what the parameter is for: 1> sp_addsegment 2> go Msg 201, Level 16, State 2 , Procedure 'sp_addsegment' Procedure sp_addsegment expects parameter @segname,Note that only mandatory parameters are listed. Typically, these are the first parameters in the argument list; optional parameters typically come last. Note that a different, more user-friendly, approach has been used in a number of new sp_* procedures that were introduced in recent years. Procedures like sp_tempdb (12.5.0.3) and sp_dbextend (12.5.1), which provide quite extensive functionality, will print a section with 'usage' information when called without parameters or with 'help' as the first parameter. The same approach has been followed for sp_encryption (12.5.3a), and sp_metrics (15.0). As of 12.5.1, also sp_monitor will print usage information when called with 'help': for reasons of backward compatibility (sp_monitor already existed in version 3 of ASE!), the default behaviour without parameters -displaying some not-so-useful 'cpu busy-type' statistics- has not changed. BTW, the new version of sp_monitor -with parameters- is actually very useful, since it reports things like the top-10 I/O-intensive queries; it gets its information from the MDA tables. As various readers pointed out, you may also run sp_help to find a procedure's parameters. That will work, but has a disadvantage: you must change to the sybsystemprocs database first -- either with use sybsystemprocs or by running sybsystemprocs..sp_help sp_addsegment. For some reason, I always seem to make a typing error in 'sybystemprocs' -- oops 'sybssytemprocs' (well, you know what I mean...), so I try to avoid this. Even without spelling errors, these are more keystrokes than just running the stored proc without parameters. In case you have the sybsyntax database installed, you could run sp_syntax sp_xxx to get the parameters of sp_xxx. Unfortunately, sybsyntax is not maintained at the same level as the ASE documentation, so you may find that some stored procedures are not included. Lastly -and as a shameless bit of self-promotion- make sure you have a copy of my handy ASE Quick Ref Guide, which describes the parameters of all system procedures -- and a lot more. More ASE Quiz questions: 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001 |
|||||||||||||||||||||
|
