Why you should check out ASE 15.7 ESD2

[former_member182090]

Recently, ASE engineers again managed to deliver a bunch of pretty useful new ASE features and enhancements. This fits a trend: when looking back a year or so, as part of SAP, ASE has definitely become a better general-purpose RDBMS.  You'll probably remember that ASE 15.7 was released in September 2011 with features like data compression, a new kernel for threaded CPUs, new LOB (text/image) features, query performance enhancements, and much more. That was a great step forward, and at the time we said  much more was coming.  So, fast forward: a few weeks ago (early August 2012), ASE 15.7 ESD2 was released. Despite being an ESD, this ASE release actually contains a truckload of great new features as well. You should really check these out since some of these are just so useful... Let me just highlight a selected few that I like particularly (I hope to come back to some of these topics in the time ahead).

• larger database size - First, the maximum size of an ASE database has been doubled as of 15.7 ESD2. This means that with a 2KB or 16KB page size, the maximum size of a single ASE database is now 8TB or 64TB, respectively (and you can still have thousands of such databases in a single ASE server).
• async database creation - When creating such a large ASE database, waiting for the creation process to complete could take a while. To make that a bit easier, ASE 15.7 ESD2 lets you create a database in asynchronous mode with  create database MyDB (...) with async_initThis means that the creation process returns control more quickly, allowing you to start using the new database. The new aspect here is that, at that point, the database has not been completely initialized yet, but it is online and accessible. While you start using the database to create tables and load data, the initialization process continues transparently under the covers. How can you not like that?
• split/merge partition - For big data sets, ASE already provided semantic table partitioning in version 15.0. In 15.7 ESD2, this has been extended with split-partition and merge-partition functionality (syntax is alter table MyTable merge partitions (...) and alter table MyTable split partition (...)). These have been on many people's wish lists for a while.
• non-blocking table rebuild - An exciting feature is the new 'online' mode for the reorg rebuild command. reorg rebuild MyTable rebuilds the entire table (and all its indexes), but access to the table is blocked while the rebuild operation is in progress. With the new syntax reorg rebuild MyTable with online , ASE will rebuild the table too, but users can still select, insert, update and delete on the table while the rebuild operation is in progress. This is a great step forward for system availability and I'm sure many ASE users will like this (NB: create index in online mode is not yet available, but it's in the works).
• faster update statistics - As every ASE DBA knows, running 'update statistics' frequently is a must-do; in ASE 15 this especially applies to 'update index statistics' on multi-column indexes.  Yet, on large tables running this on multi-column indexes may take too long for the available maintenance window. This is where the new hash-based update statistics comes in: by using hashing techniques to sort the column data, update statistics can run a lot faster for those cases (typically, faster than when using update statistics with 'sampling'). All it takes is the new syntax update index statistics MyTable with hashing (some syntax variations exist).
• faster index creation - When creating an index with the with consumers clause, the index can now be created in parallel. Technically, this was already the case before 15.7 ESD2, but the parallelism has now been greatly improved, leading to shorter index creation times. (note that some configuration steps are needed to make this work best)
• query performance - Another area where much work was done is in performance optimizations around query processing. Much of this is internal to ASE and affects things like reduced spinlock contention (and therefore, CPU usage) around various internal resources, as for example the ASE procedure cache. This will benefit every ASE user and I warmly recommend upgrading to 15.7 ESD2. I've said it before: in ASE 15.7, always enable the configuration parameter streamlined dynamic SQL, even if you don't use any dynamic SQL. This parameter is the master switch for a wide range of internal query processing optimizations that everyone will benefit from.
• MV's - A much-requested feature has now been delivered to ASE: Materialized Views. You can now run  create materialized view mv1 (...) as select * from MyTable, YourTable where (etc.)and then use the MV in a query just as any other table. There are various options, for example for the refresh mode of the MV (not discussed here). Note that the ASE documentation refers to MVs as  "Pre-computed Result Sets" (the syntax create precomputed result set is identical to create materialized view).
• security & permission - some long-standing requests for enhancements to ASE's permission mechanism have been made. A common comment was that the powers to the sa_role were too broad -- so you can now grant permission for each type of DBA operation individually. For example, you can create a user with permission to run the shutdown command, but no other things that sa_role allows you to do. This feature is known as "granular permissions". Another new feature is "predicated privileges" which lets you control access on data row level. This is similar in concept to the "access rules"  or "fine-grain access control" that were introduced in ASE 12.5, but the new predicated privileges are more powerful, flexible and easier to use.

Let me end here for now, even though the list of new stuff in 15.7 ESD2 is not complete.  As you can see, there's something for everyone in 15.7 ESD2, and we're delivering functionality that's been on many people's wish-lists (including mine) for quite some time.  No doubt, you have a particular favorite feature that's not yet there, but we're working on that, too (and that's hardly meant as a joke).  Stay tuned!