(originally published at www.sybase.com in October 2011; moved to SCN in November 2012)
Last week I did my part in a 5-episode webcast series about Sybase ASE 15.7 - the latest and greatest incarnation of your favorite OLTP DBMS (and not just yours: ASE is also SAP's preferred OLTP DBMS; see here for more).
In case you missed it: ASE 15.7 was released on September 28th, and can now be downloaded by anyone with a Sybase support contract. For everyone else, the Developer Edition and the Express Edition for ASE 15.7 are also available now.
Specifically for a guy named Peter, I can now finally shed some light on a tech question he brought up -- so Peter, since I did not have your email address, keep reading...
ASE 15.7 is packed with new functionality. I cannot actually remember an ASE release with so many new features as ASE 15.7. I guess that's the reason why the webcast comes in 5 installments.
My part of the webcast was about application development features. This means: enhancements to SQL functionality or syntax whose focus is to deliver better (or more) functionality - as opposed to better performance or shorter maintenance downtime, for example (which ASE 15.7 also has, but that's the topic of subsequent webcasts). You can listen to the webcast recording here.
Let me just briefly recap the highlights of these application-developer-oriented features in ASE 15.7.
First, there have been enhancements to handling of text/image data (collectively known as LOB data). For as long as I can remember, customers have asked for the ability to declare SQL variables and parameters of the text or image datatype. In ASE 15.7, that is now possible. This can be particularly handy when doing XML processing on mature XML documents, which can easily exceed the maximum length of 16KB for a varchar variable.
Another LOB enhancement is known as "LOB locators". This is best described as a "pointer" to a LOB variable. When a client application wants to operate on a LOB value (like truncating/overwriting/concatenating it, etc.), ASE 15.7 can send the LOB locator to the client rather than the full LOB value, which may be big. The client app then operates on the locator with new (and existing) T-SQL statements which are sent back to the ASE server where the operation on the LOB value is effectuated. This improves efficiency since no big LOB values need to be exchanged between client and server, but only the small (24 bytes) LOB locator values instead.
If this all sounds a bit cryptic, check out the webcast slides for more detail and examples.
Another enhancement in ASE 15.7 is the MERGE statement. I have blogged about this before, so see there for more details. Suffice to say here that MERGE is a more efficient way of inserting-or-updating a bunch of rows into another table than you could ever code yourself with separate insert an update steps.
This is the point where Peter comes in. At the end of the webcast one listener with that name asked a question which I was unable to answer at the time. His question was whether you can force an index in the MERGE statement in the same way as for other types of query. After consulting with the responsible engineering team, the answer has to be that, unfortunately, this is currently not supported; the same applies to other query plan forcings (I/O size etc) as well as to specifying an abstract query plan. Should you try this, you may find that it actually works fine. However, be warned: it may also just as well not work, since there is currently a number of known issues when using these clauses for MERGE. For that reason, we recommend you do not try to use any query plan forcings around the MERGE statement for now.
There is also a new feature that is perhaps less about app development and more about performance after all. In a nutshell, by enabling the config parameter 'streamlined dynamic SQL' (disabled by default), a series of internal optimizations around query processing becomes active (not just for dynamic SQL, but for many other types of queries too). This should translate into better performance although it is -as always- hard to predict how much gain you may see in your system. So I'm not going to speculate. Instead, please let me know what you find. There is no reason not to use this optimization, so please give it a try...
Lastly, there are some features that qualify as 'small' in comparison to the other topics discussed. These include syntax enhancements; for example, certain constructs are now allowed in subqueries that previously caused an error.
As it happens, my favorite new ASE feature falls actually in this category. If you want to convert the number 123 to a 6-character string, and pad the result with leading '0' characters (i.e. '000123') in pre-15.7 you had to use the following expression (figuring out how this works is left as an exercise to you, reader):
select right(replicate('0',6) + convert(varchar,123),6)
In ASE 15.7, you can now simply specify the padding character as a third argument to the str() function, so the following is now sufficient:
select str(123, 6, '0')
Any padding character can be specified (a '*' would result in '***123').
Admittedly, it did probably not require a major effort by Sybase's most talented engineers to implement this enhancement. But I always really like such little features that make my life easier when coding SQL. Small can certainly be beautiful.
