Sybase ASE
All versions
Last updated: 06 June 2004
ASE Quiz Questions: answers 2004
 
This page contains answers to the ASE quiz questions from 2004.
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 2004
Suppose you're a RepServer DBA, and you want to maintain a historical overview of all changes made to any RepServer configuration parameters. Unlike ASE, RepServer config parameter changes are not logged into the RepServer errorlog file. How can you still keep track of these changes?

Answer:
The answer is actually pretty simple: triggers.
First, observe that all RepServer configuration parameters are stored in the RSSD table rs_config (actually, there's one exception, namely the disk_affinity settings -- but we'll deal with that later). A change to a configuration parameter (with RepServer commands configure replication server, create/alter connection or create/alter route) translates into either a change of a row in rs_config (if the changed parameter already existed in rs_config) or an insertion of a new row into rs_config (if it's a setting for a particular database connection or a route, which didn't exist yet).
With this in mind, it's pretty straightforward to log all RepServer config changes: just create triggers on rs_config, and log all changes into a separate table:
use your_RSSD
go

create table rs_config_log (dt datetime, 
                            action char(1), 
                            optionname varchar(30),
                            objid rs_id, 
                            charvalue_new varchar(255) null, 
                            charvalue_old varchar(255) null)
go

create trigger rs_config_trig_i on rs_config for insert
as
   insert rs_config_log 
   select getdate(), 'I', optionname, objid, charvalue, null
   from inserted
go

create trigger rs_config_trig_u on rs_config for update
as
   insert rs_config_log 
   select getdate(), 'U', i.optionname, i.objid, 
          i.charvalue, d.charvalue
   from inserted i, deleted d
   where i.optionname = d.optionname
     and i.objid = d.objid
go

create trigger rs_config_trig_d on rs_config for delete
as
   insert rs_config_log 
   select getdate(), 'D', optionname, objid, null, charvalue
   from deleted
go
Pretty simple, right? Just query rs_config_log and you have a timestamped log of all RepServer config changes. You'll notice that changing the value of an existing parameter is implemented by deleting and re-inserting the corresponding row into rs_config.
Also note that connection- or route-specific settings cannot be deleted from rs_config (so as to revert to the RepServer-wide defaults) with regular RepServer commands (not counting dropping the connection or route). If you want to achieve this, you'll have to manually delete the corresponding rows from rs_config.

As mentioned earlier, changes to the disk_affinity settings are not caught this way -- this is simply because disk_affinity is stored in the RSSD table rs_diskaffinity instead of rs_config. Obviously, nothing stops you from writing similar triggers on rs_diskaffinity, which is left as an exercise for the reader.

Although it's a good principle not to mess around in the RSSD, it's actually perfectly safe to create these triggers: the changes made to rs_config by RepServer are neither extremely performance-critical nor frequent. Also, the triggers do not change any functionality in the RSSD from the perspective of RepServer (which acts as the client application from an RSSD perspective). The only thing to keep in mind is that the triggers should never cause an SQL command to fail -- and I don't immediately see a reason why they would. The insert actions in the triggers will perhaps generate some addtional log records, but that's most likely negligable in comparison with the amount of changes to RSSD tables like rs_locater and rs_segments. Also, the amount of space occupied by the table rs_config_log is not likely to be significant.

NB: On a different but related topic: Note that rs_config.objid, and therefore also rs_config_log.objid, is either 0 (for RepServer-global config parameters) or non-zero (for settings for a specific database connection or route). Unfortunately, in the latter case, the ID value of the connection or route is byte-swapped in an undocumented, platform-specific way. To convert rs_config.objid to the corresponding value in rs_databases.dbid or rs_routes.dest_rsid, use the following expression in a 'classic' ASE RSSD: hextoint(substring(bintostr(rs_config.objid),1,8)). In an 'embedded' ASA RSSD in RS 12.6, use this expression instead: convert(int, substring(rs_config.objid,1,4)).
If you have a copy of my book "The Complete Sybase Replication Server Quick Reference Guide", you can download some handy stored procs to display all current non-default config settings for databases and routes. Those procedures can simply be adapted to work with rs_config_log instead.

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

November 2004
It's a well-known and documented fact that stored procedure calls in ASE cannot be nested endlessly -- the deepest possible nesting level is 16. The following recursive stored procedure illustrates this (try running it!):
create proc p as 
select level=@@nestlevel 
exec p
go

-- now run it; you'll quickly hit an error
exec p
go
Despite this limitation, do you think it would be possible for a stored procedure to call itself recursively more than 16 times? If so, how?

Answer:
If you've been following these quiz questions for some time, you probably know which way they tend to go... Therefore, it may not come as a surprise that procedure calls can indeed be nested more than 16 levels deep.

To achieve this, we have to use a little trick. It is true that you cannot nest procedure calls deeper than 16 levels, but that limit applies only within the same ASE server. However, procedures can also call each other in a different way, namely as a CIS RPC (remote procedure call). With a CIS RPC, the called procedure resides in a different ASE server. For example: exec YOUR_SERVER.your_db..your_proc executes procedure your_db..your_proc in the ASE server named YOUR_SERVER. Some configuration is required: the remote servername (YOUR_SERVER in the example) must be defined in master..sysservers as well as in the interfaces file; also, it must be possible to connect to that server from the ASE server where this statement is executed from. Also, the configuration parameter enable cis must be set to 1.

As has been demonstrated before (here, for example), the CIS functionality can be especially interesting when the remote ASE server being accessed is actually the same as the server executing the procedure call. With this mechanism, a procedure can be called recursively as many times as you have free user connections available: since each CIS procedure call occupies a new session in the ASE server, the maximum nesting level of 16 is not reached.

Here's a quick example to try yourself (please do NOT run this example in a production server since it may cause temporary problems for other users!).
This example uses the server name loopback; if you have configured the MDA tables in 12.5.0.3+, this definition already exists in your server. If not, create it as follows: sp_addserver loopback, null, @@servername.
-- here we go:
use tempdb
go

-- you may also enable the config
-- parameter 'cis rpc handling' instead
set cis_rpc_handling on
go

-- create the procedure
create proc p @n int as 
  select @n=@n+1   -- keep track of nesting level
  print "Level= %1! ; spid= %2!", @n, @@spid
  exec loopback.tempdb..p @n  -- call ourselves again via CIS
go

-- run it (not in your production server!)
exec p 0  
go
You'll see that nested procedure calls is not limited by 16 levels, but rather by the number of available free user connections (to see the effect, make sure you have a bunch of free user connections available, preferably more than 16).
Functionally, there's no difference with normal procedure calls within the same ASE server, but performance will obviously be less since the CIS server-to-server communication takes some time.

Note that running the above procedure will occupy all available user connections in your ASE server, meaning that nobody can connect to the server anymore (to release all occupied user connections, simply disconnect the session). For this reason, you should perhaps not run this in servers where this would cause problems, such as in production servers.

The practical relevance of this trick is probably limited. If you'd find yourself looking at a problem requiring 50-level deep procedure calls, there's either something wrong with the algoritm, or it shouldn't probably be solved in SQL at all. Nevertheless, this example illustrates what can happen when using CIS features improperly: when a query on a proxy table or execution of a CIS RPC ends up bouncing around between ASE servers, user connections may be consumed. I've actually seen something like this happen once, although not nearly as bad as demonstrated here. Nevertheless, keep in mind that perhaps you shouldn't go crazy with CIS connections between servers for this reason (performance is another).
Lastly, should you be concerned about this type of user-connection-eating problem, which can effectively amount to denial-of-service, you can create a login trigger to limit the number of concurrent sessions by a login. See www.sypron.nl/logtrig.html for details.

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

October 2004
Suppose you're an ASE DBA, and you have been hired to replace another DBA who left 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 previous DBA isn't available for questions).
You know that a new sa password can be generated by starting ASE with the command-line option -psa, but this requires that you shut down the server first. Fortunately, since you've been following these monthly quiz questions, you know how to do this -- see the question for September 2003.

Unfortunately, however, it then appears that the sa login does not have sso_role or sa_role anymore -- apparently the previous DBA revoked these roles from the sa login... As a result, the -psa command-line option fails (since it requires the name of a login with sso_role) and no new password is assigned.
At this point, you may be able to start and stop the ASE server as much as you like, but you cannot change the password of the DBA login, because you simply don't know which login has sso_role. Let's also assume you don't even know a single login name in this server (which is probably a fair assumption for a server you've never seen before).

The question: it seems as if you've been locked out of the ASE server... is it possible to somehow change the password of a login with sso_role, or do you have to recreate the server from scratch and reconstruct the user databases from dumps or device copies?

Answer:
Indeed, you have been locked out... Fortunately, there is a way around this problem, so that you won't have to rebuild your ASE server...

The basic idea is simple: just restart the ASE server with -pxxx, where xxx is the name of a login in the master..syslogins table -- that's just any login, trying them one by one if necesssary. At worst, you'll have to try this once for each existing login name (before sending me email to point out an omission -- there is indeed no point in trying the probe login).
However, how do you know which logins exist if you cannot log into the server in the first place? (remember, the question assumed you don't even know a single login name in this server, so you cannot log in to check the contents of master..syslogins).

Fortunately, there is a way out of this situation: the trick is to look into the master..syslogins table directly, without even logging into ASE at all.
How? First, stop ASE. Then, make a copy of the master device (if it's a raw device, use the Unix dd command to copy it to a file). Then, run the Unix strings command on this copy of the master device, and open the resulting output file in an editor. In that file, search for the string 'mastersa' -- this line marks the start of the master..syslogins table, and the login names are clearly visible, preceded by the name of their default database. Note that if the default database for the sa login has been changed, you obviously won't find the string 'mastersa'. Instead, search for 'sybsystemdbprobe' (or 'masterprobe' if you have an older version of ASE).
At this point, you can figure out the names of all logins in the server pretty easily from the subsequent lines. Once you know these login names, you can try them with the -p option to find out which one has sso_role, regenerate a password, and log in to ASE.

Altogether, the whole process may take a little while, but it's likely to be faster, and certainly more convenient, than rebuilding your ASE server from scratch...

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




September 2004
The following T-SQL code interchanges the values of two local variables:
declare @a int, @b int

-- First, assign some arbitrary values to @a and @b:
select @a = 123, @b = 456

-- Then run some magic lines of T-SQL code...
??????????????? --> that's the question! See below...

-- Finally, the values of @a and @b appear to be swapped:
select @a, @b
go

 ----------- -----------
         456         123

(1 row affected)
The essence of this code -the SQL in the position of the question marks- has been omitted.
The question: which are the most efficient (from a resource consumption point of view) T-SQL statement(s) to make this code work?

Answer:
This question has actually very little to do with databases, or with SQL, at all. It's rather a classic trick that works in any programming language supporting logical bit operations.

The solution is as follows:
declare @a int, @b int

-- First, assign some arbitrary values to @a and @b:
select @a = 123, @b = 456

-- Then run some magic lines of T-SQL code...
-- Let the variables do an exclusive-OR (that's the bit operator ^) 
-- on each other 3 times...
select @b = @a ^ @b
select @a = @a ^ @b
select @b = @a ^ @b

-- Finally, the values of @a and @b appear to be swapped:
select @a, @b
go
The exclusive-OR (EXOR) operator is a logical operation performed on the individual bits of both operands. If both input bits are identical, the result bit is 0; otherwise the result bit is 1: select 5 ^ 3 returns 6 (i.e. 0101 ^ 0011 = 0110).
You are invited to verify that the three EXOR operations above will indeed swap the values of the two variables without losing any information. This code works in any version of ASE.
Note that this approach works only for integer datatypes (and not for all datatypes, as erroneously mentioned when this quiz question was first published).

There are of course plenty of other ways to swap the values of two variables, but these will either involve an additional variable, or require some kind of I/O (for example to store values in a table). Hence, this solution is the most 'efficient' one from a resource-usage point of view.

In the old days, this kind of thing was commonly used in situations where every byte of memory counted and your programs had to use as little memory as possible (sigh... those long gone days of assembler programming, core memory and self-modifying code... it makes me feel nostalgic... but I digress).

As for the practical relevance of this trick in T-SQL -- well, there may not be one.
In practice, you'd rather declare an additional local variable than use hard-to-understand code like in this example (in other words, if you include programmer effort as a resource as well, the above solution may in fact not be the most resource-friendly one).
Unless of course, complexity is what you're aiming at, for example to deliberately obscure the logic of your SQL code -- or impress your collegues at work.

Thanks to Joop Bruggink for suggesting this topic!

Update 09-Sep-2004:
Soon after this question was published, emails started coming in describing different ways of achieving the same effect. These are too interesting not to mention here (each separated by a -- marker):
select
   @a = case when 1=1 then @b else 1 end,
   @b = case when 1=1 then @a else 1 end

-- 

select @a = @a + @b - @a, @b = @a + @b - @b

--

select @a = convert(int, @b), @b = convert(int, @a)

-- note that the following ones all use a 'dummy' operand value 
-- to force the proper order of evaluation:
select @a = @b + 0, @b = @a + 0

--

select @a = @b * 1, @b = @a * 1

--

select @a = @b | 0, @b = @a | 0

--

select @a = @b ^ 0, @b = @a ^ 0

--

select @a = @b & -1, @b = @a & -1

--
Thanks to: Igor Titov, Tom Oorebeek, Xiaoyu, Rajesh Kanchi, Ilya Zvyagin, Dimitrios Antonopoulos (as well as anyone else who responded but who I've missed here).

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



August 2004
Under normal circumstances, a timestamp column is always guaranteed to be unique for a row. For this reason, it seems timestamp columns are ideal as primary keys (or for making an index unique):
create table mytab (my_column int, timestamp)
create unique index pk on mytab(timestamp)
go
insert mytab (my_column) values (1)
insert mytab (my_column) values (2)
go
Right or wrong?

Answer:
Absolutely wrong.
It is true that a primary key should always uniquely identify a row, but there's another requirement that is usually not mentioned explicitly since it is so obvious: the value of a primary key should also be stable.
This is where timestamp columns fall short: they're indeed unique, but they may change. When any column in a row is updated, the value of the timestamp column for the updated row is automatically changed by the ASE server to a different, but still unique, value.
You can easily see this for yourself. First run the code above, and then run the following lines and see how the timestamp column changes:
select * from mytab
go
update mytab set my_column = 3 where my_column = 1
go
select * from mytab
go

-- the timestamp also changes when the column keeps the same value:
update mytab set my_column = 2 where my_column = 2
go
select * from mytab
go
Now imagine what this would mean in practice: once you've updated some rows and their timestamp columns have changed as a result, how are you going to find those rows back? Since you cannot predict what the new value of the updated timestamp column will be, it has become kinda useless for all regular retrieval purposes. I guess it's fair to say that timestamp columns are a bit too unique to be a unique key...

You'd perhaps think timestamp columns could be used in a read-only database. Theoretically, that might be true, but if you'd ever drop and recreate a table, all timestamp columns would still be changed as a result.
Also note that it is not a good idea to ever include a timestamp column in any index whatsoever: when a row is updated, the index which includes the timestamp column must also be updated since the timestamp column itself is changed as a side-effect of the row update. This means additional overhead, costing performance.

Summarising: if you require a unique column, don't use the timestamp datatype. Using an identity column may be a better choice.

Note that timestamp columns were actually designed for something called 'optimistic locking': when a client retrieves some rows and then decides to update one of those rows, it should check that no other application has updated the row in the mean time. By including a timestamp column in the table, and using the built-in function tsequal() in the where-clause of the update statement, the application can verify that the row to be updated has indeed not been changed since it was retrieved (if it has been, its timestamp will have changed). This way, the client can avoid locking the rows transactionally beforehand. With this mechanism, the value of a timestamp column is expected to be stable only over a short period of time.
See the ASE documentation for more information about tsequal().

NB: for completeness, note that there is one special situation when timestamp columns are not necessarily unique; this is described in the quiz question for February 2004.

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




July 2004
You have a table with many rows -- say, 10 million. Let's assume about 9.5 million rows, identified by certain criteria, must be deleted from this table.
What's the fastest way to do this?

Answer:
Perhaps surprisingly, the quickest way to solve delete those rows is not to delete them with a delete statement.
Instead, it is faster to first extract the ~0.5 million rows that should be kept and temporarily store 'm a in different table. Then, truncate the original table, and copy those 0.5 million rows back into it. Something like this:
select * 
into #t
from MyBigTable
where KeyColumn = values-for-rows-to-keep

truncate table MyBigTable

insert MyBigTable
select * from #t
The reason this works faster than simply deleting those 9.5 million rows is because inserting 0.5 million rows is less work than deleting 9.5 million rows: an insertion and deletion of a row is roughly the same amount of work since both are individually logged in the transaction log, and need to update all existing indexes.
The additional work lies in truncating the table, which is very fast: all allocated pages are simply deallocated; individual row deletions are not logged. Also, the 0.5 million rows must be written to #t, but since this is a select..into, which also does not log individual rows, it's very fast as well. Lastly, inserting the rows back into the main table is the most expensive step, performance-wise.
I ran a little test to show the sort of difference you can expect. For a table with a row of 300 bytes and one nonclustered index on an int column, deleting the 9.5 million rows took about 20 million logical I/Os. The other method took only 2.1 million I/Os -- basically, that's 10 times faster! (if the table has more indexes, the difference increases).

As with everything, there are some potential disadvantages for this approach. First, there is a small period of time when those 0.5 million rows are not present in the table, so ideally there should be no users accessing the table during this period.
Furthermore, when delete- or insert-triggers exist for the table, you'll have to take the processing by those triggers into account -- maybe you can simply disable the triggers, but you may have to perform any actions by those triggers yourself (which may or may not be feasible).
Lastly, if the table has foreign key constraints, these may be in the way when truncating the table or re-inserting the rows -- but you can easily drop those constraints and recreate them afterwards (that's perhaps a small price to pay when you can save so much time...).

Updated (09-Jul-2004):
There was quite a bit of response to this quiz question, or rather, to my answer to it.
Some readers responded (and rightly so) I should have mentioned the issue of updating the statistics afterwards: after re-inserting those 0.5 million rows, the statistics still reflect the original 10 million rows, which may not lead to identical distribution of values. To fix, this, run update [index] statistics.
There were also some comments about the point where this approach stops being more efficient. That depends (on the number of indexes, for example) -- but the cutoff point is likely to always be well below 50% of the total number of rows. It's quite easy to do the math yourself: just scale the practical problem down to, say, a 1000-row table, and count the number of logical I/Os required for each of the methods. That'll be a good estimate for the main table.
Lastly, it has been pointed out that it may be more efficient to drop the indexes before copying the 0.5 million rows back into the table, and rebuild the indexe afterwards. In fact, you may be able to gain even more by using fast BCP-in into the main table by copying those 0.5 million rows out and then back in. You may also try to copy them back into the main table with select...into existing table (define a proxy table pointing to the main table first). Note that these methods carry additional network I/O overhead, so experiment with different network packet sizes.

Indeed, these aspects were worth mentioning. On the other hand, I've also received complaints about some of the quiz answers being too long... I'll keep trying to find the right balance !

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



June 2004
As a DBA, you may need to determine which tables in a database contain an identity column. What is the quickest way to determine programmatically (i.e. not through sp_help) whether a table contains an identity column?

Answer:
Depending on the ASE version, there are different ways of doing this.

To start with ASE 12.5.0.3 or later, just use the next_identity(table_name) built-in function. When it returns NULL, the table does not have an identity column, or does not exist, or the specified name is not a table. When it returns any non-NULL value, it means the table exists and has an identity column:
-- ASE 12.5.0.3+

-- the basic idea for a single table:
if next_identity(@table_name) != NULL
   print "This table has an identity column"
else
   print "This table does not have an identity column"

-- find all tables with an identity col. in this database:
select name from sysobjects 
where next_identity(name) != NULL

In ASE pre-12.5.0.3, you'll have to query the syscolumns table directly as follows:
-- ASE pre-12.5.0.3

-- find all tables with an identity col. in this database:
select object_name(id)
from syscolumns 
where status & 128  = 128

Now, why would you need to do a thing like this? Well, as a DBA, tables with an identity column deserve your special attention: you may want to check whether the identity_gap property has been set, for example. Or you may want to run a stored procedure directly after ASE startup to check all tables with an identity column to see whether an identity gap would occur when a row is inserted -- and if so, fix the gap before it strikes your applications, with the procedure sp_idgap_repair (downloadable from here; for ASE 12.5.1+ only).

On a side note, did you know that you don't actually need to know the column name of an identity column in order to use it in queries? An identity column may always be referred to with the column name syb_identity; this works because a table cannot have more than one identity column at a time. For an example of how syb_identity can be used, see the code of sp_idgap_repair mentioned above.

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



May 2004
For a change, this month's quiz question is about Replication Server instead of ASE (mostly because I've been doing a lot of RepServer work lately while working on a yet-to-be-announced RepServer QuickRef guide (stay tuned!)).

RepServer configuration parameters are stored in the RSSD table rs_config. For a RepServer-wide config parameter (like memory_limit or num_threads), the normal way to change its value is through the RepServer command configure replication server. Alternatively, a setting can be changed through the RSSD procedure rs_configure, which is equivalent to manually updating the corresponding row in rs_config directly with a SQL query.

The question: which RepServer-wide configuration parameters in RS 12.6 should not be changed with rs_configure or by a direct manual update to rs_config, but only through configure replication server?

Answer:
First, a bit of background: RepServer 12.6 includes the new feature of the 'embedded RSSD' (ERSSD), where the RSSD is hosted by an ASA (SQL Anywhere) server instead of an ASE server.
The main advantage of using ASA for the RSSD is that it requires less DBA involvement than ASE. For example, RepServer (re)starts and stops the ASA server automatically when needed, and RSSD backups are scheduled and performed automatically. This should be especially useful when there isn't much ASE expertise around, for example in places where RepServer is used to replicate between non-ASE databases only.
Using ASA for the RSSD is an option when creating a new RepServer; the default still is an RSSD in ASE.

While the tables and stored procedures in the RSSD, and most RepServer functionality*, are identical for both the classic ASE RSSD and the new ASA ERSSD, there is a difference for the ERSSD-related configuration parameters. When you've chosen the ERSSD, RSSD backups are scheduled and performed automatically. These backups can be configured with the RepServer config parameters erssd_backup_dir, erssd_backup_start_date, erssd_backup_start_time and erssd_backup_interval. These parameters exist only in the ASA ERSSD, and not in an ASE RSSD.
Upon closer inspection, it appears that the ERSSD backup scheduling and execution is performed by ASA. To be precise, the ERSSD backup scheduling information in rs_config is also stored in the ASA system tables SYSEVENT and SYSSCHEDULE. When changing one of the ERSSD config parameters with configure replication server, not only the values in rs_config are updated, but also those in SYSEVENT and SYSSCHEDULE.
However, it appears that these ERSSD parameters cannot be changed rs_configure:
1> rs_configure erssd_backup_interval, '12 hours'
2> go

Parameter 'erssd_backup_interval' Cannot be found or accessed through
'rs_configure'. Check your spelling or try 'configure connection' or 
'configure database'
(Incidentally, note that the error message does not suggest the only correct action, namely using configure replication server)

Of course, nothing stops you from updating rs_config directly with a manual SQL query. However, sooner or later you'll find out that those changes have not taken effect, because they were never applied to the ASA system tables SYSEVENT and SYSSCHEDULE.
What appears to happen under the hood is that configure replication server not only updates rs_config, but -only for the ERRSD config parameters- also SYSEVENT and SYSSCHEDULE. When updating rs_config manually, these ASA tables are not affected, so you'd need to update them manually as well -- obviously, using configure replication server is more convenient.

This all means that the ERSSD config parameters are different from other RepServer-wide config parameters in a number of ways. Not only are they fully dynamic, and should they be changed only through configure replication server, but their existence in rs_config is merely a front-end to the ASA scheduling mechanism, and the rs_config are essentially read-only copies of the values in the ASA tables.

I suspect -but I'm guessing now- that this is the background of a puzzling statement in the release bulletin for RS 12.6, saying "rs_configure system procedure no longer supported" in RS 12.6. The release bulletin states that configure replication server must be used instead, but does not give any further explanation why rs_configure is suddenly not supported anymore. I have not found other reasons why rs_configure should be abandoned -- but as always, I'm open to your suggestions and experiences.


*In RS 12.6, you cannot create outgoing routes when the RepServer uses an ASA ERSSD. Since a route requires some RSSD tables to be replicated, a RepAgent of some kind would be needed for the ASA ERSSD database -- my guess is that this ASA RepAgent isn't yet available or something; hence this limitation. As far as I know, this is the only limitation in the RepServer functionality when using an ASA ERSSD instead of a classic ASE RSSD.

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



April 2004
Applications in your ASE server are suddenly suffering from heavy locking problems. As it turns out, the culprit is a new application, developed by an external contracting company, that has just been taken into production. This application appears to perform long-running updates on the most frequently used database tables. The exclusive locks held by these update statements block other users trying to access these tables.
Being the DBA, you're called into a meeting with your manager, the external contractor's manager and their developers to discuss a solution. The developers propose to use transaction isolation level 0 for the update statements performed by their application, so that blocking locks will not occur.
How should you respond to this suggestion?

Answer:
First, note that it's rare for developers to suggest a change to their own application in order to solve a concurrency problem involving that same application. In my experience, the typical reaction is to blame other applications or the database software itself, and suggest the DBA somehow sort it all out.
Nevertheless, the suggestion to use isolation level 0 for updates is useless. Transaction isolation level 0 only affects read operations (like select statements), by allowing to read through exclusive locks held by other sessions. Any data modifications will still use exclusive locks, irrespective of any isolation levels that may have been set. In other words, isolation level 0 will not reduce the exclusive locks caused by the updates at all.

Now that we're looking at isolation level 0, it would actually be logical to consider a related approach. What if isolation level 0 would be used for all sessions performing select statements? This would allow those selects to read through the exclusive locks, thus solving the locking problems, right?
Well, first note that this approach will not do anything to improve locking problems between two update statements, for the reason already mentioned. Although level 0 indeed has a concurrency-improving effect on select statements, this has some serious potential drawbacks as well. First, queries on isolation level 0 have more strict requirements for using a unique index, meaning that an index that would normally be the most efficient, may not be used with isolation level 0. Also, queries running at level 0 may be aborted and restarted half-way when data is changed under the query's feet. In addition, level 0 queries will access copies of pages being modified by other sessions rather than access those pages directly, thus consuming more cache memory. In other words, performance may actually get worse by using level 0. And we haven't even discussed the potential problems as a result of reading modified, uncommitted data...

As an ASE DBA, of course you know better than these flawed suggestions. Instead, the problem seems related to the way those updates are being performed: either they update too many rows, or use inefficient query plans, or both.
Your response should therefore be that the newly introduced application should be closely examined, and changed where necessary. Be careful before volunteering to be involved in this exercise, since this could amount to solving someone else's mess!
If the tables being updated use allpages locking, some concurrency improvement may be possible by changing to datapages or datarows locking, but this is unlikely to really fix the problem of inherently heavy updates. Better try getting the problem fixed at the root first!

The idea of using level 0 for DML is not hypothetical: I've actually seen this being proposed in situations similar to the one described in this question. However, transaction isolation levels appear to be a difficult topic, and more often than not, they are used incorrectly (transaction isolation level 3, a.k.a. 'holdlock' is another example). In my experience, non-standard isolation levels are hardly ever the solution to anything, but rather a source of new problems.

Lesson learned: transaction isolation levels are often misunderstood. Think twice before using this as a solution for a database problem.

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



March 2004
A feature requested by a number of ASE users is a way to grant permission for using the truncate table command to other users. Currently, truncate table can only be executed by the table owner, and there is no documented way to grant this command.
Nevertheless, a workaround exists to overcome this limitation. How?

Answer:
In fact, there are even two workarounds.

The first workaround is to grant replication_role to a login (i.e. grant role replication_role to jsmith). When a login has replication_role, it can execute truncate table on any table in the databases it is allowed to access, although it may not even be allowed to select from those tables.
replication_role is a special system-defined role, which -as the name indicates- is intended for use within the context of Replication Server. When an ASE login has replication_role, it is not only allowed to execute truncate table, but also set triggers on/off, set replication on/off and dbcc gettrunc/settrunc. Indeed, all these commands have a replication-specific purpose (note that these set commands are not even formally documented in the ASE documentation, although they are described in the Replication Server manuals).
This workaround is an all-or-nothing deal: by granting replication_role, truncate table is effectively granted on all tables in each database the login is allowed to access. It is not possible to restrict the permission to a specific table. Therefore, be careful when using this workaround - you may want to use auditing to monitor truncate table execution (run sp_audit 'truncate', 'all', 'your_db_name', 'on' for all databases you'd like to keep an eye on).

The second workaround is to use the grant-sa_role-to-non-sa-users trick as described at www.sypron.nl/grant_sa.html. With this trick (which I won't discuss in detail here -- please follow the above link for full details), it is actually possible to restrict truncate table permission to specific tables. This can be implemented with a user table to record the tables a user is allowed to truncate and writing a stored procedure (sp_truncate 'table_name' ?) that checks this table to verify whether a truncation attempt is allowed. This is left as an exercise for the reader...

Incidentally, a more fundamental solution seems to be near. According to http://www.sybase.com/detail?id=1027934, ASE 12.5.2 will include a new feature allowing truncate table to be granted (see Bugid 292330) ; this sounds as if the regular grant/revoke syntax will be supported, i.e. grant truncate table on MyTable to public -- we'll need to wait and see if it is allowed to grant this permission to a role as well.
According to the above web page, ASE 12.5.2 is expected in April/May 2004.

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


February 2004
A column of the timestamp datatype will always contain a unique value for each row in a database. However, there is a situation when a timestamp column has the same value for different rows. When?

Answer:
When a table has no timestamp column, it can be added to the table with alter table:
1> create table t (a int)
2> insert t values (1)
3> insert t values (2)
4> go
(1 row affected)
(1 row affected)

1> alter table t add timestamp
2> go
1> select * from t
2> go
 a           timestamp
 ----------- --------------------
           1                 NULL
           2                 NULL

(2 rows affected)
As the last query shows, the values of the timestamp column are identical for both rows. As soon as a row is updated, the corresponding timestamp column will be assigned a unique value.

The timestamp feature, combined with the tsequal() function is intended for 'optimistic locking' (i.e. instead of locking a row, its timestamp value is remembered by the application; later, it is checked that nobody else has modified it, since this would have altered the timestamp). This works because timestamp columns values are guaranteed to be unique in a database (except as above), so an update to a row can always be detected.
Note that a timestamp column, although its name may suggest differently, has no direct relation to the real-world clock time. The ASE server automatically assigns the current value of the 'database timestamp' to the column when the row is inserted or updated. The database timestamp is essentially a sequential counter without any other purpose than to provide a unique value for every modification in the database.

Incidentally, one could argue that, formally speaking, the timestamp columns don't have the 'same' value, since NULL doesn't equal NULL (due to the three-valued logic in SQL, which stipulates that the conditions NULL = NULL and NULL != NULL are both neither true not false).
While that argument sounds correct, it is interesting that the tsequal() function does consider NULL = NULL to be true:
1> declare @t timestamp
2> select @t = timestamp from t where a = 1
3> select * from t where tsequal(@t, timestamp)
4> go
(1 row affected)
 a           timestamp
 ----------- --------------------
           1                 NULL
           2                 NULL

(2 rows affected)
Why does tsequal() behave this way? Well, my guess is that tsequal() just wasn't ever expected to deal with such a situation (indeed, this isn't something you'll quickly see in a production system).

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

(if you're interested in more information about timestamp columns and the 'database timestamp' -for example, how the latter is used in the transaction mechanism, or how to relate a timestamp value (approximately) the to real-world clock time-, see chapter 13 of my book "Tips, Tricks & Recipes for Sybase ASE")
January 2004
This question is about a rather simple update. We have the following tables t1 and t2; both have two int columns and contain two rows:
1> select * from t1
2> go
 a           b1
 ----------- -----------
           0         100
           0         200

(2 rows affected)


1> select * from t2
2> go

 b2          c
 ----------- -----------
         100           3
         100           5

(2 rows affected)
What we want to do is assign the sum of column t2.c to t1.a for matching values in columns t1.b1 and t2.b2. In other words, we want the value 8 (=3+5) assigned to column t1.a for the row where t1.b1 = 100.
The following query is written to accomplish this:
update t1
set a = sum(c)
from t1, t2 
where b1 = b2 
  and b1 = 100
Couldn't be simpler, right? But does this query work correctly?

Answer:
Perhaps surprisingly, this update query updates both rows in t1:
1> update t1
2> set a = sum(c)
3> from t1, t2 
4> where b1 = b2 
5>   and b1 = 100
6> go
(2 rows affected)


1> select * from t1
2> go
 a           b1
 ----------- -----------
           8         100
           8         200

(2 rows affected)
In fact, if t1 had contained more rows, this query would have updated all those rows in the same way, irrespective of the value in their b1 column: the predicates where b1 = b2 and b1 = 100 are completely ignored by this update!

The problem lies in the sum() aggregate in the set clause. When replacing this aggregate by a constant or a variable, the update works as expected: it performs the specified join and update only 1 row (try this yourself!).
However, with an aggregate in the set clause, the update is effectively processed as follows (you can also see this with showplan on):
update t1
set a = (select sum(c)
         from t1, t2 
         where b1 = b2 
           and b1 = 100)
This is an interesting case because the ASE documentation does not seem to describe this particular situation at all (well, at least I couldn't find it...). However, the docs do say that aggregates are allowed only in the select list or in a having clause, so it feels a bit funny that an update-with-aggregate is accepted by ASE after all (if anyone knows more about the background of this issue, I'll be glad to post it here).

The correct way to write this update is to use a correlated subquery (this is also ANSI SQL-compliant):
update t1
set a = (select sum(c) 
         from t2 
         where t1.b1 = b2)
where b1 = 100
Alternatively (and more clumsy), you can split up the query and replace the aggregate by a variable:
declare @s int

select @s = sum(c)
from t1, t2 
where b1 = b2 
  and b1 = 100

update t1
set a = @s
from t1, t2 
where b1 = b2 
  and b1 = 100
Instead of a variable, a noncorrelated expression subquery can also be used (note how the line in bold is essential to avoid updating all rows again):
update t1
set a = (select sum(c)
         from t1, t2 
         where b1 = b2 
           and b1 = 100)
where b1 = 100  -- essential!
Note that the fipsflagger (i.e. set fipsflagger on) doesn't give any clues to this particular problem. It will generate a warning, indicating that a from clause in an update is non-standard SQL. However, the same warning is generated when replacing the aggregate by a variable, so the fipsflagger isn't particularly useful here.

Lesson learned: Don't be satisfied just because your update statement seems to run fine. Always check the exact rows and columns being updated!
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/quiz2004a.html