Sybase ASE
All versions
Last updated: 19 December 2010
ASE Quiz Questions: answers 2010
 
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 2010
This month's quiz question would make a fine after-Christmas-dinner discussion topic at the Secret Society of SQL Specialists - but use at your own risk in less nerdy settings!
Anyway, the topic of this question was brought up by a Sybase user who tried to understand what he was seeing; let's call him 'OP'.

OP ran into the following situation.
The two select queries below select identical results, but they return the results in a different order. Yet, in both cases, the order by clause is identical, namely: order by t.B. This felt strange to OP, and he suspected ASE might be at fault here. After all, the sort order is clearly defined to be according to column 'B' in table 't', right?

Check out the example below, and run it in your own ASE server to see how it behaves.
Then, before looking at the answer, decide on your verdict: is ASE correct here, or is this a bug?

This is a stripped-down version of OP's problem:
1> create table t (A int, B char(2))
2> insert into t (A, B) values(1, 'az')
3> insert into t (A, B) values(2, 'bb')
4> go

-- query #1
1> select A, reverse(B) as C from t order by t.B
2> go
 A           C
 ----------- ---
           1  za
           2  bb

(2 rows affected)


-- query #2
1> select A, reverse(B) as B from t order by t.B
2> go
 A           B
 ----------- ---
           2  bb
           1  za

(2 rows affected)
Made up your mind?


Answer:
Now that you've clicked through to the answer, this means you have an opinion on whether ASE is behaving correctly or incorrectly (you do, don't you?).
I'm guessing that most of you, my highly respected readers, decided whether ASE was behaving correctly or incorrectly based on what you *felt* would be the logical or desired behaviour. But I'm betting that most of you did not ask yourselves how 'correct' or 'incorrect' is actually *defined*. Did you?

Thing is, something can only be correct (or incorrect) if it has been defined somewhere what the correct behaviour actually is...

You can probably feel where this is going... in the case of this particular query, the best summary is probably to say that ASE is not behaving incorrectly -- for the simple reason that the semantics of this particular syntax happen to not be really defined anywhere.
I'll explain what that all means, but first, let's take a look at the ANSI SQL standard, the mother of all specifications in the RDBMS world.

As it happens, and I did not know this until I started looking into OP's issue, the ANSI SQL standard originally defined an order by clause only for use with a cursor declaration. It was not until the ANSI SQL/2008 standard that order by was defined with a regular select query.
The first thing to observe here is that, therefore, ASE's implementation of select...order by... has always been non-ANSI-standard. Or better: it is an extension to the ANSI SQL standard. BTW, exactly the same applies to all other RDBMS vendors, who all support select...order by... as well. In reality, every database supports a subset of the ANSI SQL standard, but there is always a large number of non-ANSI-standard features in every DBMS -- if you've ever ported some real-life application from one database brand to another, you probably found this out the hard way.

Now, if the ANSI standard does not define the semantics of an order by clause, then we're quickly getting in a bit of a gray area where 'correct' or 'incorrect' become relative concepts: it all depends how a particular database vendor -in this case, Sybase- chooses to define it.

So, let's turn to OP's actual queries.

I checked OP's queries in the oldest ASE version I could get running, which was 11.0 -- and the results were precisely the same as shown above (which came from ASE 15). In other words, ASE has forever been behaving as above (version 11.0 dates back to 1995, so for all practical purposes, that's pretty much 'forever' in this context). The good news is that ASE's behaviour has at least been unchanged on this point.

Now, as documented, an order by clause in ASE may refer to columns, expressions (and more), but also to aliases of select list expressions.
(it's here: see Reference Manual->Commands->order by clause->Usage, where it says "In Transact-SQL, you can use order by to sort items that do not appear in the select list. You can sort by a column heading, a column name, an expression, an alias name (if specified in the select list), or a number representing the position of the item in the select list (select_list_number)." )

The subtle -but crucial- difference between OP's query #1 and query #2, is that in query #1, order by t.B refers to the column 'B' in table 't'. However, in query #2, the select contains an alias column named 'B', and in this case, order by t.B happens to refer to the alias 't.B', rather than to the table column 't.B'. As a result, the rows are sorted in a different order despite the order by clause still being the same:
-- query #1
select A, reverse(B) as C from t order by t.B  -- sorts by the table column 'B'

-- query #2
select A, reverse(B) as B from t order by t.B  -- sorts by the alias 'B'
If you feel this is confusing, I'll be the first to agree (I was pretty confused myself for a while). But if you feel this is wrong, I have to disagree. It just doesn't say anywhere that ASE should do something other than what it is doing.
Formally, the only real issue here is that the precedence rule applied by ASE to resolve a qualified name in the order by clause is not documented: it doesn't actually *say* anywhere that if a table column and a select list alias both have the same name, ASE will pick the alias when interpreting the order by clause containing a qualified column name (that 'qualified' is vital BTW -- I'll get back to that shortly). I can tell you this omission will be corrected by the folks at Sybase techpubs.

That's indeed quite formal reasoning -- but cases like these require such an approach lest you'll be stuck forever in a battle of personal preferences.
From a more pragmatic viewpoint, it is interesting to see what other databases do with these queries. As it happens, IQ, SQL Anywhere, MS SQL Server, DB2 and Oracle all return the same result set order for query #1 and query #2 -- in other words, for them, order by t.B refers to the table column, even when there is an alias with the same name. If this is more intuitively correct for you, I'm with you. But again, this implementation is just as non-ANSI-standard as ASE's.

Now, the underlying issue is probably as follows. ASE seems to be unique in supporting the use of a select list alias which is qualified with a table name, i.e. order by t.B rather than the unqualified order by B.
For example, consider a table 't2' with only one column, named 'X'. ASE, Sybase IQ, SQL Anywhere, MS SQL Server, DB2 and Oracle all support the non-ANSI syntax select X as Y from t2 order by Y (i.e. with an unqualified column name 'Y'). But only ASE supports select X as Y from t2 order by t2.Y (with a qualified column name 't2.Y') -- the other databases raise a syntax error here.

The capability to use this qualified column name in the order by clause to refer to an alias is unique, and this leads to an ambiguity when that alias name can also be column name -- as OP discovered. Admittedly, the way this ambiguity has been resolved (by taking the alias rather than the column) could perhaps have been done differently, but apparently that was not how it was done.

If you made it this far through the explanation, you're probably wondering *why* things were ever implemented this way. I guess we'll never know the answer to that question -- this could very well be one of those things that was done for some reason in some distant past, for example because of some query in some particular customer's application which happened to be written this way and Sybase felt compelled to support that. Or maybe some different reason -- but this has been lost in the mist of time, so we can only speculate.

Anyway, generally for behaviour that has worked in a particular way for so long, Sybase engineers will be reluctant to change it -- it might lead to problems in existing customer systems which -inadvertently or not- appear to rely on this behaviour. So I wouldn't expect this particular order by behaviour to be 'corrected' any time soon...

Back to your own verdict (from before you clicked to see the answer to this quiz question) -- is ASE right or wrong?
Since the facts are that (i) ASE's behaviour does not violate the ANSI standard and (ii) it is also not inconsistent with ASE's own documentation and (iii) it has been consistently behaving like this for a long time, I hope you'll see that it is not justified to say that ASE is *wrong* -- but just poorly documented on this particular point. Your preference (and mine) would probably be to implement it differently if we could start from scratch, but that's not relevant now. OP suggested we could perhaps call this a "requirements bug". Works for me.
Is this all good or bad? I'm not going to have a judgement on that. In reality, if you dig deep enough in any of the other database brands, you'll probably find illogical gray-area issues there as well. It ain't nice, but it's a fact of life that things aren't always perfect.

Thanks to OP for bringing up this interesting issue. And thanks to Glenn Paulley for guidance on the ANSI SQL standard.

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


November 2010
Being lazy can be a virtue. I prefer to type as few keystrokes as I can when working with ASE -- not only because it's quicker, but also to avoid carpal tunnel syndrome from striking again.
So, what's the smallest number of keystrokes you have to type to enable the configuration parameter 'allow updates to system tables' ? -- something I need to type pretty often.

Answer:
To start with the answer right away: 'allow updates to system tables' can be done in 9 keystrokes.
This may strike you as a very low number. After all, the regular way of doing this would take the following 52 keystrokes (including hitting ENTER twice):
sp_configure 'allow updates to system tables', 1
go
How do we reduce the typing below that? Well, as it happens, you do not actually need to specify the full configuration parameter string: it is sufficient to specify a substring that uniquely identifies the config parameter in question. So 'allow updates' would already be enough, since there is no other config parameter with that substring in its name (though, of course, there could always be a new one in the next ASE version...).
This allows us to cut down significantly on typing. Since the three letters 'upd' happen to already uniquely identify the config parameter 'allow updates to system tables', we can reduce our keystrokes to 25:
sp_configure 'upd', 1
go
Note that we do not need any quotes around 'upd' since it is not a SQL keyword and does not contain any spaces. In other words, it is a string by default, so no explicit string delimiters (quotes) are needed. Also note that we, lazy as we are, can avoid that space between the comma and '1'. So we're down to 22:
sp_configure upd,1
go
How to reduce this further? Well, I never made a secret of my fondness for sqsh and its great feature for creating custom command aliases. As a result, my .sqshrc file is full with 2-letter shorthand codes for commonly used commands, or parts of commands.
For example, I have the following in .sqshrc:
\alias cf='\loop -e "sp_configure !*"'
In sqsh, this lets me type 'cf' instead of 'sp_configure'. So now we can just type this:
cf upd,1
As a bonus, when hitting ENTER at the end of the above line, the command sp_configure upd,1 is sent to the ASE server, without having to type go at all.
This brings my typing back to 9 keystrokes, including hitting ENTER at the end of the line. That's quite an improvement from the 52 keystrokes we started off with. And, not unimportant in my case, much less scope for making spelling mistakes.
Of course, it requires that you figure out and remember some of those unique substrings, but when you have to type them often, I bet you won't have a big problem with that.
Some hints for things I use often: 'le mo' can be used for 'enable monitoring'; 'nt c' is short for 'statement cache size'; 'x mem' is for 'max memory' (note that you need quotes around all of these since they contain a space).

Some readers might argue that you could reduce things even further by using a 1-letter sqsh alias. However, I have a bunch of these aliases ('cc' for sp_cacheconfig; 'db' for sp_dboption, etc), so 2 letters is the minimum to keep it workable (for me, at least) with some semi-logical aliases.
Also, it might be possible to reduce things further by figuring out a shorter unique substring for the configuration parameter, but for 'allow updates to system tables' I did not find one that also reminded me of the actual meaning of the config parameter ('upd' feels quite like 'update', after all).

If you cannot use sqsh for some reason, and you're stuck with isql you could also create a stored procedure 'sp_cf' in sybsystemprocs, which simply calls sp_configure (it would need to have the same parameters as sp_configure, obviously).
Also, you could of course create an alias for the entire command, e.g. 'cfupd1' to enable 'allow updates to system tables' and 'cfupd0' to disable it. I can't argue with that, but the question is how far you want to go with creating specific aliases. I prefer aliases for just the common parts like the stored proc name, but that's just a matter of taste.

BTW, what if you call sp_configure with a non-unique substring? In that case, sp_configure will print a message "Configuration option is not unique." and list all those options containing the substring you specified... so you can try again with a better choice.

Note that the principle of specifying a unique substring for an option also apply to sp_dboption and sp_serveroption. So to switch on 'truncate log on checkpoint' for a database named 'mydb', 16 keystrokes are enough (assuming 'db' is a sqsh alias for sp_dboption):
db mydb,ru,true
So.... you can save yourself a lot of typing by using unique substrings for config parameters and database options, and by using sqsh.



PS. I just realised that typing this quiz question took so many keystrokes that it probably nullified any savings from using the tricks above over the past few years... Ah well, I'll just put another coldpack on my wrist.

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


October 2010
This month's quiz question starts with the answer. The challenge for the reader (that's you) is to figure out the question.

Recently I found myself deleting rows from a multi-million row table in the following way:
1> delete MyTable where rand2()*100 < 1
2> go 225
So, why would I do such a thing? (Clearly, to remove rows -- no points for that. Why am I doing it this way ?)

Answer:
In short, what I'm trying to do is to drastically shrink the size of a table, but maintain the value distribution in the table.
The context is this: I was trying to reproduce a query plan issue that had been spotted by a customer. They were so kind to provide a few of their tables for me to reproduce the problem in-house at Sybase, but these tables were a little on the large side (which makes it harder to create a reproduction -- need a bigger database; loading the data takes longer, transaction log gets full quicker, etc).
Having stared at the issue for a while, I felt it was likely possible to still reproduce the problem with a table only a fraction of its original size. But I could not just blindly delete the first, say, 90% of the rows in the table -- for this query plan issue, the distribution of values seemed to play an important role (which is not surprising if you're familiar with histograms, densities, and some of the inner workings of ASE's cost-based query optimizer).

So how do I manage to delete millions of rows, but spread the deletions evenly over the entire table?
Let's look at that delete statement again:
1> delete MyTable where rand2()*100 < 1
2> go 225
The delete statement performs a table scan. For every row scanned, the built-in function rand2() is evaluated and produces a random number between 0 and 1; after multiplying by 100, the random number lies between 0 and 100. When this last value is less than 1, the row is deleted, and otherwise it is not touched. In other words, this delete statement deletes 1% of the rows in the table, but distributes the deletes randomly -- it is as if you're shaving off a thin slice, equally spread over the table.

The trick here is how rand2() works. This built-in function was added in ASE 15.0.1 and differs from its older cousin rand() in that rand() gets evaluated only once in a result set. However, rand2() is evalated for every row being accessed, which is exactly what we need here.
(if you're still on 12.x, you can also do it with rand() but you must apply a trick described in section 8.2 of my book "Tips, Tricks & Recipes for Sybase ASE" -- essentially you embed rand() in a subquery together with a column, e.g. where (select rand() + IntColumn*0)*100 < 1 ; see the book for more details).

Now, the other question: why am I doing 'go 225' ? If I want to delete 90% of the rows, why not simply use this:
1> delete MyTable where rand2()*100 > 10
2> go
Here, 90% of the scanned rows would qualify so we'd be done with just a single query.
Why am I not using this? Well, because I have a multi-million row table with a bunch of indexes on it -- and deleting 90% of the rows in one transaction was just too big for my transaction log (I did try it initially, of course, and found out the hard way). Enlarging the transaction was not convenient.
To avoid blowing up my transaction log, I am shaving off 1% of the rows of the table with the predicate where rand2()*100 < 1 , and I'm just repeating that delete operation 90 times (yes, the database option 'trunc log on chkpt' was enabled; otherwise I would have had to insert a line 'dump tran MyDB with truncate_only' after the delete statement, so that this would get repeated 90 times too).
NB: Note that when you repeat this delete command 90 times, you end up deleting much less than 90%, since every subsequent delete operates on a slightly smaller table than the previous deletion. So you'll need to run the delete more than 90 times -- I'll leave this mathematics exercise to the reader (but I got a 90% reduction with about 225 repeated deletions of 1%).

Update: Various readers helpfully pointed out (thanks!) that in order to delete 10% of the rows, you'd need 229 iterations since ln(.1)/ln(.99) =~ 229. Those readers get full marks for math, and in theory they're right. However, practice is different: keep in mind that the randomized deletion will not delete an exactly predictable number of rows. And because there are multiple iterations, variations in the number of deleted rows may be amplified in subsequent iterations. So, your mileage may vary...

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


September 2010
As you know, the procedure cache is an important shared resource in ASE: to run a query, we need to have a query plan, which in turn needs some space in the procedure cache in order to exist in the first place. If you don't have enough procedure cache space, your query runs into a 701 error:
Msg 701, Level 17, State 3
Server 'SYBASE', Line 1
There is not enough procedure cache to run this procedure, trigger, or SQL batch. 
Retry later, or ask your SA to reconfigure ASE with more procedure cache.
Some time ago, the question came up how to generate a 701 error at will. The context was the need to see what the actual impact of a 701 error on an existing application would be, and how to determine afterwards that this error condition had taken place.
So, the question: how can you reduce the amount of available stored procedure cache in a controlled manner so that a 701 error will occur?

Answer:
Filling up the procedure cache for testing purposes is not as easy as it sounds: if you simply configure the procedure cache to a very small size, you may not be able to run some system stored procedures at all anymore, making your ASE server hard to use. If you configure it with a normal size, you need to find a way to consume so much procedure cache space that a 701 error results. How do you do that?

The first thing you might have thought of is perhaps to create a bunch of stored procedures and execute those, thus making their plans consume procedure cache space.
In principle, that works, but the problem is that those plans will be swapped out of the procedure cache to make space for new queries -- this is how ASE avoids running out of procedure cache. Because plans can only be removed from the procedure cache when the procedure is not actually executing, you'd have to let those stored procedures execute a waitfor delay statement or something so as to keep those plans in an "executing" state, making it impossible for their plans to be removed. However, this isn't very practical: you'd probably need to create a whole bunch of such procedures and execute each of them in a separate session. This gets messy quickly.

A better approach is to create some large T-SQL variables in a session. When you declare a variable, the space for that variable is in fact allocated from the procedure cache. You can see the effect by running sp_monitorconfig 'procedure cache' which will report the current amount of available procedure cache:
Try the following yourself:
exec sp_monitorconfig 'procedure cache size'
go
declare @v1 char(16384)
exec sp_monitorconfig 'procedure cache size'
go
When you run these statements, you should see that in the second batch the available amount of procedure cache is less than in the first batch. This is because the second batch declares a big local variable while the first batch doesn't. The space allocated for the variable is what causes the difference in the available amount of procedure cache space (note: don't bother try making the numbers match exactly -- there's more happening under the covers).

While this is a good illustration of how to gobble up procedure cache space, it isn't very practical either if you try to generate a 701 error: the space allocated for variables is immediately released again at the end of the batch -- so you'd have to run another waitfor delay statement again to keep the batch from completing. Not so convenient.

(here comes the quiz answer...)
A similar, but much more managable approach is to allocate chunks of procedure cache by calling the built-in function set_appcontext(). This function creates a user-defined attribute (a string, actually) in the context of your current session. These attributes are maintained until you disconnect your session from the ASE server (or until you explicitly destroy them with rm_appcontext() -- but disconnecting is easier). Note that ASE doesn't do anything with these attributes you create, it's entirely up to your application to use them.
For example, running select set_appcontext('mytest', 'my_attribute_1', 'my test value #1') creates a 16-byte string containing 'my test value #1', the space for which is allocated from the procedure cache.
You can do the same with larger strings -- the maximum length is 255 bytes per call. As long as the combination of the first two arguments is kept unique, a new attribute will be created. The following loop will allocate ever more space from the procedure cache:
-- do NOT run this on your production server!
declare @n int
set @n = 100000  -- just pick a number...
while @n > 0
begin
   -- allocate space for a 255-byte string
   select set_appcontext('mytest', 'mytest_' + convert(varchar,@n), 
                         replicate('x', 255))
   set @n = @n - 1
end
go
Try running this loop (in your own personal test ASE server please!), while, from another session, keeping an eye on the available procedure cache space by repeatedly running this:
sp_monitorconfig 'procedure cache size'
You should see the amount of available procedure cache decrease while the loop is running.
If you keep running the loop above long enough, you will eventually eat up so much procedure cache that you'll run into a 701 error... and at that point you can see how your application would behave if a 701 occurred (one thing to test: does your client application handle that 701 error properly?)

Now, for part 2 of the question: how can you tell that a 701 error has happened? When a 701 happens, this is not written to the ASE errorlog. Also note that the column Reuse_cnt in the output from sp_monitorconfig does not indicate that a 701 occurred, but rather that something was removed from the procedure cache to make space for something else. This is normal.
So how can you tell a 701 occurred?
Readers of my book "Tips, Tricks & Recipes for Sybase ASE" will have found the answer in section 2.4.1: for errors that you would like to see logged into the ASE error log, set the 'with_log' attribute for the error message as follows:
sp_altermessage 701, with_log, true
After this, every time a 701 error occurs, it will end up in the ASE errorlog so that you have a record of it occurring. I recommend doing this for any errors that you might like to know about when they happened, like 1204 (indicating your ASE server has runs out of locks).

Some closing notes: the attributes you've set in your session with sp_appcontext(...), can be displayed with list_appcontext('*', '*'), and deleted again with rm_appcontext(...). But as noted before, just disconnecting your session cleans everything up instantly.

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


August 2010
Let's say you need to guarantee that a certain database table (let's call it 't') is always updated in a particular way, for example in conjunction with other tables or subject to certain business rules. The classic way to achieve this guarantee is to code a stored procedure (let's call it 'p'; no prizes for original naming today) that performs the update of t with all required processing and error checking. By granting execute permission on procedure p but not granting insert/update/delete permissions on table t itself, you can guarantee that non-table-owners cannot update table t other than through the stored procedure p.

This is all well-known stuff.
But it's only one half of the guarantee, if you really care about it.

So let's go one step further: how can you guarantee that table t is only updated through that same stored procedure p, even by the table's owner? (let's assume the table's object owner also has sa_role, so he's the all-mighty database owner too). Nothing stops the table owner from updating table directly t, ignoring stored procedure p.

How can you still guarantee that even the table owner doesn't update table t directly, but uses procedure p instead ?

Answer:
For the solution to this problem we need to use two additional ASE features: a good-old trigger and the MDA table monProcessProcedures.
As you may know (if not, you'll know at the end of this paragraph), when queried, monProcessProcedures contains a row for every stored procedure, trigger or SQL UDF currently being executed (by any user) in the ASE server. By filtering on your own session's SPID, you effectively get a call stack of any stored procedures or triggers executed.
So, if you want to ensure that table t only gets updated by procedure p, and not by another procedure or by any ad-hoc SQL statements, then you could create a trigger on the table that verifies that the direct caller is procedure p by examining monProcessProcedures:
create table t (a int)
insert t values (1)
go
create trigger my_trig on t 
   for update -- could also add insert & delete
as
begin
    declare @dbname  varchar(30)
    declare @owner   int
    declare @objname varchar(255)
    declare @contextlevel int

    -- figure out how deep the current call stack is
    select @contextlevel = max(ContextID) 
    from master.dbo.monProcessProcedures 
    where SPID = @@spid

    -- one level up should be our proc
    select @dbname=DBName, @owner=OwnerUID, @objname=ObjectName
    from master.dbo.monProcessProcedures 
    where SPID = @@spid
      and ContextID = @contextlevel - 1

    -- let's assume the proc to check for is 'dbo.p', located
    -- in the current database
    if    (@dbname = db_name()) 
      and (user_name(@owner) = 'dbo') 
      and (@objname = 'p')
    begin
       -- DML is performed by procedure 'p'. That's good.
       return
    end
    else
    begin
       -- update is done by something other than procedure 'p'. Not good.
       print "Table 't' may only be updated through stored proc 'p' !"
       rollback trigger -- could also use 'rollback' instead if preferred
    end
end
go


When updating the table directly, the trigger will notice this and roll back the update:
1> update t set a = a + 1
2> go
Table 't' may only be updated through stored proc 'p' !
(1 row affected)

-- table is not updated:
1> select * from t
2> go
 a
 -----------
           1

(1 row affected)
When performed through stored procedure p, the update is allowed:
1> create procedure p
2> as
3>    update t set a = a + 1
4> go

1> exec p
2> go
(return status = 0)

-- table is updated:
1> select * from t
2> go
 a
 -----------
           2

(1 row affected)

So, are there any circumstances when the logic in the trigger above would not work?
Yes, there are: since we're depending on the trigger to fire whenever someone modifies a row in the table, there are some cases we cannot intercept because a trigger does not fire. For example, when BCP'ing into a table or when truncating a table.
Of course, the table owner can also drop or disable the trigger -- there's little you can do against the table owner himself. But let's assume that doesn't happen.
Note that monProcessProcedures can be used even when the config parameter enable monitoring is set to 0 (i.e. switched off). Only by dropping the proxy table monProcessProcedures will the logic above be disabled.

Exercise for the reader: how do you modify the trigger above to check that stored proc p is not called by some other stored proc or trigger; in other words, that p is the top-level procedure being executed?
(not sure if this check would have any sensible meaning, but hey, these quiz questions are trying to be instructive...)

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


July 2010
As you know (I'm assuming), when a client connects to the ASE server, it sets a bunch of connection attributes. These include the username and password, the servername being connected to, the network packet size to be used, and possibly many more.
For some of these connection attributes, their values are visible once connected to ASE: for example, the hostname, application name and network packet size are available in master..sysprocesses, in columns hostname, program_name and network_pktsz, respectively.
It can be useful to specify a meaningful value for the application name in your application. Not only does this allow you (as well as code in a login trigger) to recognise your application, but also some ASE features use the application name. For example, resource limits and user-defined temporary databases can be bound to applications with a specific name.

Unfortunately, not all applications set meaningful values in these attributes. If you have a third-party application which you'd like to connect to ASE using a specific network packet size, or specify a particular application name, this isn't easy since you cannot modify the compiled code of that application.
Yet, there is a way to specify custom values for such connection properties, even when you're unable to modify the application itself.

How?

Answer:
The answer to this question lies in a little-known aspect of OpenClient: the OpenClient configuration file, known as 'ocs.cfg'. This configuration file allows you to specify values for connection attributes: when an application connects to the ASE server, it first checks the ocs.cfg file to see if any of its connection attributes are specified; if so, the value from ocs.cfg will be used, overriding any value that already may have been set by the application code.

Now, if you search on your system for a file named ocs.cfg, you will likely not find one. Indeed, the file does not exist by default, which is one of the reasons why this feature is not very well-known. If it exists, the ocs.cfg file is located in $SYBASE/$SYBASE_OCS/config/ocs.cfg (Unix/Linux) or %SYBASE%\%SYBASE_OCS%\ini\ocs.cfg (Windows). Note that this is on the system where the client application is located.
As I said, by default the ocs.cfg file does not exist. When you look in $SYBASE/$SYBASE_OCS/config or %SYBASE%\%SYBASE_OCS%\ini you'll find a file name sample.cfg which is the template for ocs.cfg. If you want to use ocs.cfg, copy sample.cfg and edit as needed.

The first thing you'll notice when you have copied sample.cfg to ocs.cfg, is that isql, probably as well as most other client applications, will suddenly fail to start with an error message like this:
CS-LIBRARY error:
        comn_get_cfg: user api layer: internal common library error: Configuration 
        section isql not found.
This error occurs because when an application sees that ocs.cfg exists, it looks for a section in the file specifically for this application. In the case of isql, it looks for a section marked [isql] in ocs.cfg.
In general, an application looks for a section [xyz] when its application name attribute has been set to xyz by the application. If no application name has been set by the application itself, it will use the section marked [DEFAULT].

So, for isql, try adding the following lines to your ocs.cfg (put these below the [DEFAULT] section):
[isql] 
	CS_APPNAME=testappname
	CS_HOSTNAME=testhost
Now try to connect again with isql, and then run the following query. You should see the hostname and application name that you specified in ocs.cfg:
1> select program_name, hostname from sysprocesses where spid=@@spid
2> go
 program_name                   hostname
 ------------------------------ ------------------------------
 testappname                    testhost

(1 row affected)
If you'd want to define a particular network packet size for your application (say, 4096 bytes), you'd add a line CS_PACKETSIZE=4096.

Go here for a list of connection properties. Note that most of the properties listed here do not apply to regular applications, or are "for retrieval only" which means they cannot be set in the ocs.cfg file.

Obviously, when using ocs.cfg, there are some downsides. First, all applications using the same $SYBASE/$SYBASE_OCS environment will need to have a section in ocs.cfg file or they will be unable to connect.
Second, the ocs.cfg effectively becomes part of your application environment, so you'd need put it under version control etc.

Despite the downsides, I wish I had known about ocs.cfg earlier. I recall a consulting job back in the 90's where we were battling with a 3rd-party telco application whose vendor was not very cooperative. Using ocs.cfg would have solved some of my problems, had I known about it.

Just as with the quiz question for May 2010 (see below), this makes me realise OpenClient is an essential part of our infrastructure, yet we're often unaware of its role or its capabilities.

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


May 2010
Here is an issue I stumbled over last week.
As you probably know, ASE 15 has increased the maximum length for many identifiers from 30 to 255 bytes. This applies to names of tables, column, stored procedures etc.
I was writing a small piece of SQL to run a particular test, and as it happened, the length of a name of one of the tables exceeded 30 bytes.
The reason I noticed the identifier was longer than 30 bytes was that this error message was raised:
1> create table this_is_a_kinda_long_table_name (a int)
2> go
Msg 103, Level 15, State 205
Server 'SYB155', Line 1
The identifier that starts with 'this_is_a_kinda_long_table_nam' is too long. 
Maximum length is 30.
Now that's strange!
I double-checked I wasn't accidentally connected to a 12.x server (and I wasn't, this was ASE 15.5). But if this was ASE 15.x, then a 31-character table name should be allowed....

What's going on here?

Answer:
First, I checked the documentation -- and indeed, ASE 15.0 and 15.5 do indeed allow 255 bytes for most types of identifiers, as it says here.
So at least I got that part right. But why then, was ASE unhappy with my 31-character table name?

The first thing to look at when seeing funny behaviour is your non-default configuration parameter settings (sp_configure nondefault is terribly useful here). I noticed that 'compatibility mode' was enabled in my server, and since that's a 12.5-compatibility feature, I reckoned that might have something to do with it. Unfortunately, disabling it made no difference at all, and the error still occurred.
I then simplified my SQL code to reduce it to the simplest reproduction, which turned out to be the create table statement shown above (the problem originally occurred in a select-into statement inside a stored procedure).

But now what? I scratched my head for a while, and I couldn't really find anything that looked wrong or that would explain why I was getting this error. So, frankly, I had no clue what it was that I was doing wrong -- since that was pretty much clear: somehow I had messed up, but if only I knew what....

Without a better option available, I decided to swallow my pride and ask my collegues what I was missing. Within 4 minutes, the answer landed in my mailbox (thanks to Sean Kiely and Bret Halford who responded at the same time). Their advice: check the version of the client you're running. If the client is 12.5, that explains why the identifier is too long.
Hmmm... that sounded like a plan -- especially because this possibility had not crossed my mind. Without understanding how the client version could make a difference, I followed their advice. And indeed: I was running the 12.5 version of isql. When running the same create table statement with isql 15.0, it worked fine, without error, as it should.

With my immediate problem solved, there was just one small but nagging question left in my mind: why does it make a difference if you use isql 12.5 or isql 15.0? This just didn't seem to make any sense to me. After all, isql only sends some text to the ASE server and fetches the results, right?
Wrong. As another collegue reminded me (thanks, Dave Putz), isql actually does a lot more, but normally that's all transparent and you'll never notice unless you hit some special combination of circumstances (like character set conversion -- see this quiz question for March 2010).

Now, when an OpenClient application connects to ASE, one of the things happening invisibly to the application is that the client-side OpenClient libraries and the ASE server exchange information on their capabilities.
One aspect of this is whether the client is able to handle 255-byte identifiers: OpenClient 15.x will indicate to an ASE 15.x server that it can handle such long identifiers, but obviously OpenClient 12.5 will not signal having this capability, since that did yet not exist in the days of 12.5. Consequently, when an OpenClient 12.5 application connects to an ASE 15.x server, the server knows that this client will be unable to handle identifier names longer than 30 bytes.
When ASE knows that the client cannot handle large identifiers, it will disallow the use of longer-than-30-bytes identifiers during the session for that client, so as to avoid messy failures during client-server communication.

Why does it matter whether the client is be able to handle such long names? For sending SQL code to the server, which is basically just a bunch of ASCII characters to the client, indeed this capability does not matter.
But for other things, it does. Two examples are bulk copy operations (e.g. BCP) and RPCs (remote procedure calls), which both involve the notion of an object name that is handled by the OpenClient layer.

So, to summarise... there are more things happening between client and server, Horatio, than are dreamt of in your philosophy...

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


April 2010
Here's one from the Crazy Tricks Department: We're always about making SQL queries run faster -- but what about the opposite? What about making a query run slower?
So, let's just say you want to slow down a query against a particular table; how would you achieve that?
Apart from the technical aspect, here's question #2: what *valid* reason (i.e. not for just messing up systems) could you have for wanting to slow down, rather than speed up, a query?

Answer:
When you have a system where all queries are encapsulated in stored procedures, you could simply add a waitfor delay statement into the stored procedure. But that's too trivial for what we're after here -- we want a way to make a query against a particular table run slower, irrespective where the query comes from.

This can indeed be achieved, provided you're running ASE 15.0.2 or later. The first part of the trick is to add a computed column (CPC) to a table; the second part is to put a SQL user-defined function (UDF) in that CPC; the third part is to put a waitfor delay statement inside that UDF.
Here's an example:
use my_db
go
create function f_slow
  returns int 
as 
   waitfor delay '00:00:00.1'
   return 0
go
create table t (a int, b as my_db.dbo.f_slow() materialized)
go
When inserting into this table, the value of CPC 'b' needs to be computed, since it is a materialized CPC (meaning the computed value is actually stored on disk). Computing the CPC's value requires executing the UDF f_slow(), which means a 0.1-second delay happens -- or whatever long or short interval was specified in the UDF.
Of course, this could also have been achieved by putting a delay in an insert trigger. But I'm using this example as the introduction to the real trick, which applies to selecting from the table, where no triggers apply.

So let's do the same, but now with a virtual CPC (whose value is calculated when queried, and not stored on disk) instead of a materialized CPC as above:
use my_db
go
create function f_slow  
  returns int 
as 
   waitfor delay '00:00:00.1'
   return 0
go
create table t (a int, b as my_db.dbo.f_slow())
go
When querying column 'b', the UDF will be evaluated for every row accessed with a select statement (or any other statement referring to that column). Which means that a 0.1-second delay will kick for every row accessed. Note that inserts will not be slowed down since the value of a virtual CPC is only calculated when it is queried (nothing stops you from putting both a virtual CPC and a materialized CPC in a table BTW).
Obviously, this trick only works when column 'b' is referenced in the query, so it must be in the select list or somewhere in a predicate. When column 'b' is in the select list, the UDF will be evaluated for every qualifying row; when the column is in a predicate, the UDF will be evaluated for every row being scanned, basically (in reality it can be a little less obvious when the UDF is evaluated, especially when subqueries are involved; and when the column is involved in a join predicate, the UDF may be evaluated more than once, depending on the join type and join order, among other things. But this summary is good enough to understand the big picture).

If your queries don't refer to column 'b' but only to column 'a' the delay will not kick in. However, you could use additional tricks like putting a view on top of the table and effectively swapping columns 'a' and 'b' by defining the value of CPC 'b' to be identical to 'a':
use my_db
go
create function f_slow (@i int)
  returns int 
as 
   waitfor delay '00:00:00.1'
   return @i
go
create table t (a int, b as my_db.dbo.f_slow(a))
go
create view v (a,b)
as
   select b, a from t
go
In addition, you could rename the view to the name of the table (after renaming the table itself). Any queries that would perform updates will likely fail on the non-updatable view, but that can be worked around with Instead-Of Triggers (a separate topic, indeed).

Anyway, when using this CPC-with-UDF-doing-waiting trick, you'll see the session be in alarm sleep status in sp_who during the waitfor delay. Also, you may need to increase the configuration parameter setting for number of alarms if this ends up being used by many concurrent users.



As for the second part of the question: why would you deliberately want to slow a query down?
Actually, doing something like this is not completely outlandish, though it is admittedly a bit exotic (or sick, depending on your viewpoint).
I can see this potentially being useful in what I would call 'expectation management'. Imagine you're building a system which is designed to run with a database of a large size, but initially the database size is still small. Consequently, initially the application may run very fast due to the database being small, but you know it will get slower when the database has reached its expected full size. This will inevitably annoy the end-users of the system, who don't like to experience a slowdown ("the system was nice and fast when it was new, but it's so much slower now"). Of course, the initial performance was unrealistically good and the users should not have complained when things reached their eventual 'normal' state -- but psychologically, humans just don't see things that way.
To protect yourself against such sentiments, it might be perfectly justifiable to build an artificial slowdown into the system, and gradually reduce the amount of slowdown as the database grows in size.
Obviously, there is a thin ethical line here -- this approach could just as well be used to make the initially released system to perform *so* slowly that additional contracts for more consulting/implementation/tuning work could be obtained. I'm clearly not suggesting that line of thought should be followed.

Another potential application could be in Quality of Service (QoS): perhaps there are some users for whom you want to provide a distinctly less-performing system than for others (and perhaps offer to upgrade those users to a better-performing offering for just a modest monthly fee). Since you can test for the user of the current session inside the UDF, there's no reason why you couldn't invoke a longer or shorter wait for particular users only.

So for those who really tune in to this approach, you could make the delay configurable. Just let the UDF read the delay from a table, which you can update if you want things to slow down a bit more or less. Here's a simple example:
create table time2wait (tm varchar(10))
insert time2wait values ('00:00:02')
go

create function f_slow (@i int) 
  returns int 
as 
	declare @w varchar(10) 
	select @w = tm from time2wait 
	waitfor delay @w 
	return @i
go


There may well be other potential applications... there's no trick too crazy, or there is an application for it somewhere...

A few weeks after publishing this quiz question, I realised it's worth mentioning some additional things. For example, the approach above will show column 'b' when selecting all columns of the table, which may be undesirable since it might confuse existing applications. To work around this, you can create a function index on the same expression as defined for the CPC. Creating a function index has the effect of adding a materialized computed column to the table, but the column will be hidden, and therefore not visible in the result set.
You can do this as follows:
use my_db
go
create function f_slow (@i int)
  returns int 
as 
   waitfor delay '00:00:00.1'
   return 0
go
create table t (a int)
go
create index ix_slow on t ( my_db.dbo.f_slow(a) )
go
As is hopefully clear from the examples above, the hidden materialized CPC that gets created for the function index will slow down inserts. However it will not have any effect on selects, since that requires a virtual CPC. Note that for updates, a materialized CPC will be re-evaluated only when it references a column that's being updated. Obviously, both for updates and deletes, referencing a virtual CPC somewhere in the query will have the effect of evaluating the CPC.
It is in fact also possible to slow down updates even when you're not updating a column referenced by the CPC. I'll leave that as a challenge for the reader, and a possible quiz question at a later time.

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


March 2010
I never know in advance what my next quiz question will be about, but this week the topic came to me easily. I wasted an evening trying to solve a wacky problem while getting some SQL to run on a system (a separate box) that was being prepared for demo purposes.
This turned out to be one of those cases where it's proven true that there's always something left to learn (as I mentioned last month).

The problem first surfaced when using sp_autoformat to format the contents of a table (if you don't know sp_autoformat, you should get acquainted -- try running 'sp_autoformat sysusers' as an example of what it does)
sp_autoformat is supposed to format each column as narrow as possible, given the actual data values it contains, trimming off trailing spaces from [var]char columns, among other things.
My problem was that sp_autoformat was formatting all [var]char columns with a bunch of spaces at the end, which is exactly what should NOT happen. Worse, it really messed up the formatting of some output that was supposed to fit on an 80-character window.

After checking my own SQL code was not at fault, I zoomed in on what sp_autoformat was doing (try adding the @trace=4 parameter). It seemed sp_autoformat was doing everything right, yet every column was still formatted longer than the longest value it contained. Weird.

Eventually I could simplify the problem and reproduce it without sp_autoformat, as follows:
1> select substring('abc', 1, 3)
2> go

 ------
 abc

(1 row affected)
Can you spot the problem? I'm doing a substring() whose result should be 3 characters long, but the resulting column is in fact 6 characters.
This seemed to defy all logic. What on earth is going on here?

Answer:
I was really stuck with this issue, and I didn't see a way out. I had already tried whether it was specific for a particular ASE version. The problem seemed to occur in any ASE 15 version, at least 15.0.3 and 15.5. However, when I ran the same SQL on an ASE 15.x server on my own system (i.e. not the demo box), everything worked fine, and the result was 3 characters long, as expected.
I tweaked both ASE 15 servers until they were fully identical in configuration settings, and still I was stuck: on the demo system, substring('abc',1,3) still generated an incorrect 6-character result, whereas on my own system it produced a 3-character result. At this point there was *no* difference between those two ASE 15 servers, other than that they were running on different hosts.

When I tried running my SQL on a 12.5 server on the demo box, the problem did not occur and the result was 3 characters long as it should be. So was this an ASE 15-specific problem?
I searched the ASE documentation for clues, but to no avail. Then, at the point when hard liquor looked like the only direction where solutions might be found, I realised that there had been some kind of message when I connected to the 12.5 server on the demo box for testing my reproduction:
% isql -Usa -P -S ASE1254 
Msg 2401, Level 11, State 2:
Server 'ASE1254':
Character set conversion is not available between client character set 'utf8'
and server character set 'iso_1'.
No conversions will be done.
1> 
I had ignored this message, but suddenly it seemed a good idea to pay attention. What was this saying?
Apparently the client on the demo box was using utf8 as its character set, and the server was using the default of iso_1. Hmmmm.... but so what? I did not get this message when connecting to the ASE 15 servers.
Curious, I tried running the same repro on a 12.5 server on my own system. This also worked correctly (3-character result), but there was no such message after connecting.

At this point it started to dawn on me that the issue might not be on the side of the server, but somehow involve the client. Something with character sets...
I recalled my little purple booklet has a section on character set stuff, although I don't think I've ever used that section for any practical purpose. The section ('Character set & sort order') didn't provide any clues, but the last line referred to some global variables towards the back of the quickref guide. There, I found the global variable @@client_csname, which supposedly indicates the character set used by the client for the current session.
When I selected this variable on the demo box (where the problem occurs), it appeared to be utf8. On my own system, where the problem did not occur, the client was using iso_1. So when the client uses utf8, somehow the [var]char result set columns are getting longer? That still sounded odd, but at least that's something we can experment with.

So, on the demo box, I ran the following. Using -Jiso_1 forces isql to use iso_1 as its client character set:
% isql -Usa -P -S ASE155     # uses utf8
1> select substring('abc', 1, 3)
2> go

 ------
 abc

(1 row affected)
1> exit
%
%
% isql -Usa -P -S ASE155 -Jiso_1    # uses iso_1
1> select substring('abc', 1, 3)
2> go

 ---
 abc

(1 row affected)
1> exit
%
We seemed to be getting somewhere now... At this point, I could draw at least two conclusions: (i) the issue was caused by the client using utf8 and (ii) the difference between my own system and the demo box was that my own system has environment settings such that the client uses iso_1 by default, whereas on the demo box, the client uses utf8 by default (as it turned out, this was because the environment variable LANG was unset on my own system but set to 'en_US.UTF-8' on the demo box).

Some questions still remained though, such as: why does the problem not occur on the ASE 12.5 servers? And why is there no warning about character set conversion in the ASE 15 servers? And most of all: why does a client character set of utf8 produce a 6-character string, and with iso_1, a 3-character string?

After reading through the documentation on ASE character set matters, things started to become more clear. Obviously, utf8 is a Unicode character set, which can represent many more symbols than, say, ASCII or iso_1. This means that Unicode characters can occupy 2 bytes, whereas iso_1 characters occupy only 1 byte. ASE -- or rather, OpenClient-- will automatically perform character set conversion where possible, and when converting from a single-byte character set (iso_1) to a multi-byte character set (utf8), a single character in the single-byte character set to may be represented by more than one byte in a multi-byte character set. This is actually indicated by the global variable @@client_csexpansion which indicates the factor of this expansion. Indeed, it turns out that this variable contains the value 2 when the client character set is utf8, and 1 when it is iso_1.
So there we are: with a client using utf8 and the server using iso_1, ASE will expand each iso_1 character to a 2-byte utf8 character. In isql, this means that the resulting column is shown as 6 positions, even though it is technically still 3 characters of 2 bytes each. When the client uses iso_1, the result set column consists of 3 1-byte characters, shown as 3 positions by isql.
To return to my original SQL formatting problem, by forcing the client to use iso_1 (with isql -Jiso_1 or by setting LANG to blanks), or suppressing character set conversion altogether (with isql -J), all my formatting worked fine again.

At this point, I thought a single-malt scotch was indeed well-deserved. By now I had spent hours on this problem.

While being really happy with myself, I realised there might have been an even simpler reproduction. And indeed: simply running "select 'a' " would have given a 2-character result column for utf8, but only a 1-character column for iso_1 (you can test this yourself by simply forcing utf8 or iso_1 with isql -Jutf8 or isql -Jiso_1, respectively -- this is assuming that your ASE server uses iso_1; but by now I'm sure you get the idea).

As for the two remaining questions: I'm assuming the OpenClient version that comes with ASE 15 has unicode support provided by default, whereas in 12.5 it does not. This must be the reason why the warning about character set conversion occurred only on 12.5. And this is also the reason why the 12.5 servers produce the correct 3-character result: character set conversion simply didn't happen for utf8, and this was what the warning message from isql was trying to alert me to.
(I guess there must be some kind of way to make character set conversion work for utf8 in 12.5 too -- but as you can tell from all of the above, my awareness of character set aspects in ASE is pretty limited, although recently I've been learning fast).

When you think about it, there's a lot of stuff happening behind the scenes when it comes to character sets. It's one of those things you usually don't notice -- until you work on a different system which may have different environment settings. Keep this in mind when writing SQL code where the formatting of results matters -- and test with different character sets.

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


February 2010
In 2010, it's 21 years ago I got involved in what turned out to be my first project involving ASE (then named Sybase SQL Server).
After so many years, I liked to think I knew what was worth knowing about that very first thing you encounter when entering into the world of ASE: the isql utility.

Turns out, I was wrong.

While doing some performance benchmarking recently, I ended up staring at some strange-looking results that just couldn't be right.
In essence, this was a comparison between a stored proc and isql both doing the same 10,000 simple selects (this was not what was I was testing, but where I ended up after digging into the results).
The stored proc looked like this:
create proc p
as
  declare @cnt int
  set @cnt = 0

  while @cnt < 10000
  begin
	  select * from my_tab
	  set @cnt = @cnt + 1
  end
go
The stored procedure was then kicked off with a straightforward isql script:
exec p
go 
The isql-based test just took the following simple script as input:
select * from my_tab
go 10000
(I've written about the 'go 10000' approach before; see the May 2005 quiz question for more).

Now, the strange thing was that, even though in both cases the exact same query was executed 10,000 times, the test with the stored procedure was about 4 times slower than than the test with the 'go 10000' script. That made no sense, since the stored proc test should be faster, or at least equal, since there's less processing overall.
Here's why: the 'go 10000' test sends the query to the server 10,000 times, while the stored proc test send the 'exec p' to the server only once. Otherwise, both tests perform the same select query 10,000 times, and produce the same 10,000 result sets.
So logically, the 'go 10000' test should be slower than the stored proc test since it has to do more client-server roundtrips. But what I was seeing is that it was actually 4 times faster than the stored proc test.

The statement cache was enabled (so there was no optimizer overhead anywhere), and I had verified that the query plans and I/O counts were identical. The table had 100 rows and 20 columns, and the data was static. There were no other users in the server.

What was going on?

Answer:
The concept of 'go N' in isql is an elegant way of executing a piece of SQL N times with minimal keyboard work. For example, it is useful to quickly generate a bunch of rows of test data (again, see here for an example).
With 'go N', isql sends the preceding query batch to the server, and receives the corresponding result sets. Repeat, until N is reached (obviously, N=1 by default).

When I couldn't figure out what was going on with these strange results, I decided to swallow my pride and ask some engineering collegues at Sybase, all of whom have been around for a long time. Interestingly enough, they all came up with different explanations (names withheld to avoid public embarrassment):
  • Collegue #1: "isql isn't sending the batch 10,000 times, it's sending it only once and it gets executed 10,000 times at the server."
  • Collegue #2: "When you use 'go 10000', only the result set of the last query is sent back to the client, so there is much less network traffic which explains why the stored proc is slower."
  • Collegue #3: "The stored procedure is slower because it is generating more DONE_IN_PROC TDS tokens, causing additional overhead."
Collegue #1 was outright wrong: I could easily tell from the MDA tables (monSysSQLText) that the query *was* sent 10,000 times. And even if he'd been right, it wouldn't explain why the stored proc was so much slower.
Collegue #2 was also wrong (though appeared to be thinking in the right direction): from the network I/O monitoring in sp_sysmon I could see roughly the same amount of data being sent back from the server to the client. The minimal difference might be explained by the theory of collegue #3, but there was no way this difference could explain the factor-4 performance gap.

Eventually it was Collegue #4 who got it right. When doing a 'go N', the query batch is indeed sent to the server N times. Also, the result set is sent back to the client N times. However, for all except the last one, isql just discards the result set, and doesn't actually process it. You'll notice that isql indeed shows only one result set and then prints "N xacts:", but I had always assumed all result sets would be fully read by isql, even though they wouldn't be printed except for the last one.
As it turned out, isql doesn't even bother to read out the individual rows and columns for all result sets except the last one. For 10,000 result sets of 100 rows and 20 columns each, the amount of client processing that would require adds up, and by skipping this processing, isql ended up faster than the stored proc.

With that wisdom, it is clear how the isql part of the test should be performed in order to make a fair comparison with the stored procedure: generate a SQL script with the query, followed by 'go', repeated 10,000 times. isql will then fully process all result sets, just as it does for the stored procedure.

Lesson Learned: using 'go 10000' is not a good way to do a realistic end-to-end performance test.
Second Lesson Learned: no matter how well you know ASE, there's always something new left to learn... as me and my collegues found out.


PS. For completeness, sqsh behaves exactly the same as isql when it comes to the handling of 'go N'.
How would you rate this ASE quiz question?
Great    OK    Average    Boring     


January 2010
You have a SQL script file containing tables, data, stored procedures etc., which are an essential part of the applications running on your ASE system. Periodically, as changes to your applications are implemented, the contents of this script changes too, and it needs to be re-run (with 'isql') against the ASE database for the application.
When running this script, you're assuming a database named 'PROD' to exist, since this is the place where the tables, stored procedures (etc) are located. However, you need to be 100% sure that this database indeed exists, and also that it can be accessed (it could inacessible due to having been marked 'suspect' for example, or it could have been put in single-user mode or in dbo-use-only mode).
So, if for some reason, there is no database named 'PROD', or it cannot be used, the script should not run, but raise an error message indeed.
This is less trivial to implement than it sounds.

The question: How can you conditionally abort execution of your SQL script somewhere in the middle of the script when database 'PROD' cannot be accessed?

Answer:
To abort a SQL script based on some condition that you detect in your SQL, use the built-in function syb_quit(). When running select syb_quit(), the ASE server will terminate the connection with the client immediately. This is the right technique to abort a SQL script somewhere half-way.

For the case described above, you could structure things as follows in your SQL script:
use master
go

-- this is needed to make sure the error message is displayed 
-- in case of a script abort
set flushmessage on
go

-- check database 'PROD' exists:
if not exists (select * from dbo.sysdatabases where name = 'PROD')
begin
    print "We seem to have a problem: database 'PROD' does not exist."
    print "Aborting..."

    -- now terminate the script:
    select syb_quit()
    -- this line is never reached!
end
go

-- OK, the database seems to exist...
use PROD
go
-- but we need to verify we can actually *use* this database
if db_name() != 'PROD'
begin
    print "We seem to have a problem: I cannot access database 'PROD'."
    print "Aborting..."

    -- now terminate the script:
    select syb_quit()
    -- this line is never reached!
end
go

...rest of SQL script...

When executing syb_quit(), the script is terminated and the rest of the SQL is not executed.
Since the client application doesn't know what's going on, it will detect that the connection is lost and print an error message, like this:
% isql -U my_login -P mysecr3t -S PROD_SERVER -i my_sql_script.sql
We seem to have a problem: database 'PROD' does not exist.
Aborting...
CT-LIBRARY error:
        ct_results(): network packet layer: internal net library error: 
Net-Library operation terminated due to disconnect
%

The sort of thing we're also protecting against here include: running the script as the wrong login/user and running it in the wrong server (you could of course add a check for the right server name).
Keep in mind that without such a check, the script would proceed and execute whatever SQL is in the script, in the database you happen to be in (probably 'master'). That may not be the right thing to do....

When you think about it, it's actually odd not to include some checks like these: it means that (i) you're absolutely sure such a situation could never occur and (ii) if it did, it would be harmless. Would those assumptions indeed be correct for your system? I bet they wouldn't.

Another example where such a check-and-abort construct can be useful is when you want to ensure that a script file gets executed against the proper version of ASE. For example, a script containing SQL UDFs should be executed against ASE 15.0.2 or later: when running it against ASE 12.x or 15.0.1, errors will result since SQL UDFs are supported only as of version 15.0.2. See the downloadable file behind this link for an example of such a check.

Lastly, back in ASE version 12.0, when syb_quit() was introduced, the function was left undocumented, leaving some ASE users wondering whether this was safe to use. However, these days syb_quit() is normally documented in the ASE manuals, and it can safely be used.

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