I’d rather play golf

In a previous article, I presented some examples of how one can SELECT rows from a dml-derived-table, a new SQL language feature of the SQL Anywhere 12 server, now in beta. In this post, I want to briefly describe some other ways in which one can exploit dml-derived-tables to simplify applications.

The first thing to mention is that even though the title of this post is “SELECT over an UPDATE statement”, dml-derived-tables are derived tables and hence can be used in any context one might use a derived table – including, of course, update statements (INSERT, MERGE, DELETE, UPDATE). Consequently one can effectively “nest” one UPDATE statement within another; here is an example using MERGE in combination with UPDATE:

1
2

CREATE TABLE modified_employees
( EmployeeID INTEGER PRIMARY KEY, Surname VARCHAR(40), GivenName VARCHAR(40) )

3
4
5
6
7
8
9
10
11
12
13
14
15

MERGE INTO modified_employees AS me
USING (SELECT modified_employees.EmployeeID, 
              modified_employees.Surname, 
              modified_employees.GivenName 
       FROM (
          UPDATE Employees
          SET Salary = Salary * 1.03
          WHERE ManagerID = 501) 
            REFERENCING (FINAL AS modified_employees) ) AS dt_e 
       ON dt_e.EmployeeID = me.EmployeeID 
WHEN MATCHED THEN SKIP
WHEN NOT MATCHED THEN INSERT
OPTION(optimization_level=1, isolation_level=2)

In the above example, the table “modified_employees” models a collection of Employees whose state has been altered; the MERGE statement above merges employee identifiers and names for those employees whose salary has been increased by 3% with those employees already extant in the “modified_employees” table.

[Read more →]

Third International Workshop on Testing Database Systems
(DBTest 2010)
June 7, 2010, Indianapolis, Indiana, USA
In conjunction with the 2010 ACM SIGMOD/PODS Conference

Workshop Overview

New usage patterns, evolving hardware trends, and increased competition drive continuous innovation and expansion of data-processing systems. Commercial database vendors are adding new features related to ease of management, semistructured data, data compression, parallelism, reliability, and security. New data-processing systems are being developed over MapReduce backends, key-value stores, low-power devices, and widely-distributed systems. It is increasingly expensive to test and tune these systems. DBTest 2010 aims to bring together researchers and practitioners to discuss important open problems and new techniques in testing systems for data management.

Topics of Interest

Topics of interest include, but are not limited to:

• Testing techniques for data-processing systems, storage services, and applications
• Data and workload generation for testing
• Generation of stochastic models for large test matrices
• Algorithms for automatic program verification
• Techniques to maximize code coverage during testing
• Testing the reliability and availability of data-processing systems
• Testing and designing systems that are robust to estimation inaccuracies
• Testing the effectiveness of adaptive policies and components
• Interactions between testing and tuning of data-processing systems
• Testing data consistency Vs. availability tradeoffs
• Metrics for predictability of query and workload performance
• Metrics for query plan robustness
• Security and vulnerability testing
• War stories and vision papers

Important Dates

• Paper submissions due: March 29, 2010 (Monday, 5PM PST)
• Notification of acceptance: April 22, 2010 (Thursday)
• Camera-ready papers due: May 6, 2010 (Thursday)
• Workshop: June 7, 2010 (Monday before SIGMOD)

Paper Submission

Papers should be formatted according to the ACM guidelines and SIGMOD proceedings template. Papers should not be longer than six pages, and should be submitted on-line through DBTest 2010’s CMT paper submission site, which will be setup shortly.

Workshop Organizers

• Shivnath Babu (Duke University)
• Glenn Paulley (Sybase iAnywhere)

DBTEST Workshops Steering Committee

• Leo Giakoumakis (Microsoft)
• Donald Kossmann (ETH Zurich)

Program Committee

• Shivnath Babu (Duke University)
• Carsten Binnig (SAP)
• Jose Blakeley (Microsoft)
• Mitch Cherniack (Brandeis University)
• Leonidas Galanis (Oracle)
• Dave Godwin (IBM Toronto Laboratory)
• Haryadi Gunawi (University of California, Berkeley)
• Donald Kossmann (ETH Zurich)
• Vivek Narasayya (Microsoft Research)
• Glenn Paulley (Sybase iAnywhere)
• Ken Salem (University of Waterloo)
• Jiri Schindler (Network Appliance)

In a previous post in May 2009 I expressed admiration for a SQL language feature in IBM’s DB2 product that permits one to use an update DML statement as a table expression in a query’s FROM clause. Here is a simple example to illustrate DB2’s syntax:

1
2
3

SELECT T_updated.*
FROM NEW TABLE ( UPDATE T SET x=7 WHERE y=6 ) AS T_updated
WHERE T_updated.pk IN ( SELECT S.fk FROM S WHERE S.z = 8 )

With this construction, it is straightforward to join the modified rows to other tables, return the modified rows to the application via a cursor, output the modified rows to a file, and so on. Without this extension, one would have to define a AFTER or BEFORE TRIGGER to copy the modified rows to another (different) table, manage the contents of that other table (and handle concurrent updaters), and execute a separate SELECT statement over the trigger-inserted table (only) after the UPDATE statement had been executed. That’s a fair amount of work just to output what changes an update statement made.

[Read more →]

On Friday I attended a Distributed Systems seminar at the University of Waterloo given by Christopher Ming-Yee Iu, now a PhD student at EPFL in Lausanne studying under Willy Zwaenepoel. Here’s Ming’s abstract:

Instead of using a separate query language like SQL to query databases, many programmers prefer using higher-level abstraction layers like Ruby on Rails or Hibernate when interacting with databases. Unfortunately, these abstractions do not offer the same functionality as SQL, so programmers often have to resort to writing SQL code when they need to express complicated queries. We have developed a system called JReq that lets programmers write these complicated queries using regular Java loops and iterators. Our system can then automatically identify these queries in Java code and translate them into efficient SQL code. In order to perform this translation of Java to SQL, our system uses a translation algorithm that relies heaviliy on symbolic execution. This translation algorithm provides us with the power to do some interesting program transformations, and we have recently been able to reuse it to build a query optimizer that optimizes Map-Reduce programs to run on SQL databases.

Ming’s presentation was an up-to-date talk on the progress of the project to date; an earlier publication [1] presents the basic ideas and illustrates the ability of the rewriting system to generate queries for the (now obsolete) TPC-W benchmark. In a nutshell, the JReq bytecode-rewriting system, formerly named “Queryll”, is able to take Java “queries” embodied as iterative loops, for example:

1
2
3
4
5
6

for (Office of: em.allOffice()) {
   if (of.getName().equals("Seattle"))
      westcoast.add(of);
   else if (of.getName().equals("LA"))
      westcoast.add(of);
}

and generate SQL queries directly from the code:

7
8
9
10

SELECT ...
FROM Office AS A
WHERE (((A).Name != "Seattle") AND ((A).Name = "LA"))
      OR ((A).Name = "Seattle")

[Read more →]

You can find an NHibernate dialect customized for the Sybase SQL Anywhere 12 server (currently in beta) here.

This new SQLAnywhere12Dialect offers support for:

• The TIMESTAMP WITH TIME ZONE data type (also known as the DATETIMEOFFSET data type); and
• SEQUENCE values using the new SEQUENCE support in the SQL Anywhere 12 server.

SQL Anywhere 12 now supports ISO-ANSI SEQUENCE values in addition to AUTOINCREMENT (or IDENTITY) values. Sequences in a given database are stored in a new catalog table, SYS.SYSSEQUENCE, and SQL Anywhere 12 supports the .CURRVAL and .NEXTVAL syntax to permit applications to reference SEQUENCE values. Moreover, SQL Anywhere supports sequence_name.NEXTVAL in any context where an arbitrary expression could be used, making it straightforward to use a SEQUENCE as a default primary key value (see the example at line 4 below).

To illustrate SEQUENCE support in SQL Anywhere 12, I modified the “HelloNHibernate” application from the book [1] by Kuate, Harris, Bauer, and King. First, we had to create the SQL Anywhere 12 database:

1

dbinit helloseq.db

and then create a SEQUENCE object that would supply the primary key values for the Employee table:

2

CREATE SEQUENCE Employee_sequence INCREMENT BY 10 START WITH 1

3
4
5
6
7

CREATE TABLE Employee (
   ID INTEGER NOT NULL PRIMARY KEY DEFAULT (Employee_sequence.NEXTVAL),
   NAME VARCHAR(50) NULL,
   MANAGER INTEGER NULL
)

Note that the ID column of the Employee table no longer specifies AUTOINCREMENT; rather, its default value refers to the SEQUENCE named “Employee_sequence”. Using .NEXTVAL automatically provides the next sequence value to use when inserting a new Employee.

[Read more →]

As mentioned previously, Mohammed Abouzour and Peter Bumbulis of Sybase iAnywhere, along with Ken Salem of the University of Waterloo, had their paper entitled “Automatic Tuning of the Multiprogramming Level in Sybase SQL Anywhere” accepted for publication at the forthcoming 5th International Workshop on Self-Managing Database Systems which will be co-located with the IEEE Data Engineering Conference in March 2010 in Long Beach, California.

The paper outlines the theory behind the MPL self-tuning algorithms that are implemented in the beta release of SQL Anywhere 12 and the ability for the server to mitigate performance issues in the face of workload fluctuations. Moreover, because the SQL Anywhere 12 server can now automatically grow and shrink the thread pool on-the-fly, another advantage to this self-managing technique is to reduce instances of thread deadlock when the server is temporarily overwhelmed by outstanding requests.

Adam Leventhal of Sun Microsystems first wrote about the integration of triple-parity RAID with Sun’s ZFS filesystem in July 2009. His motivation for looking at RAID-7 was to overcome the disparity between disk capacity, transient disk errors (ie bit rot), and disk array recovery times. That is: even with RAID-5 or RAID-6, computer systems are still vulnerable to catastrophic disk system failure because the increase in transient disk errors make rebuilding large RAID arrays prone to failure.

[Read more →]

The 5th International Workshop on Self-Managing Database Systems, co-located with the 2010 IEEE International Conference on Data Engineering, takes place on 1 March 2010 in Long Beach, California.

Over the weekend the SMDB 2010 Program Committee made final selections of the papers to be included in the Workshop. Out of 15 submissions the program committee selected 9 papers, 6 long presentations and 3 short ones, to make up what I think is an excellent full-day program, headed by a keynote talk by Oliver Ratzesberger, the Senior Director of Architecture & Operations at eBay.

In particular, I note that a paper entitled “Automatic Tuning of the Multiprogramming Level in Sybase SQL Anywhere” was accepted for the Workshop. This paper was authored by Mohammed Abouzour and Peter Bumbulis of Sybase iAnywhere, along with Ken Salem of the University of Waterloo. The paper describes the self-tuning capabilities of the server kernel in SQL Anywhere 12 that automatically adjusts the server’s multiprogramming level in response to changes in workload.

The selected papers for the Workshop are:

Long papers

• Wendy Powley, Pat Martin, Mingyi Zhang, Paul Bird, and Keith McDonald: Autonomic Workload Execution Control Using Throttling.
• Goetz Graefe and Harumi Kuno: Adaptive indexing for relational keys.
• Karsten Schmidt and Theo Haerder: On the Use of Query-driven XML Auto-Indexing.
• Rasmus Amossen: Vertical partitioning of relational OLTP databases using integer programming.
• Yanpei Chen, Archana Ganapathi, Armando Fox, Randy Katz, and David Patterson: Statistics-Driven Workload Modeling for the Cloud.
• Jennie Rogers, Olga Papaemmanouil, Ugur Cetintemel: A Generic Auto-Provisioning Framework for Cloud Databases.

Short papers

• Debabrata Dash, Ioannis Alagiannis, Cristina Maier, and Anastasia Ailamaki. Caching All Plans with Just One Optimizer Call.
• Marc Holze, Ali Haschimi, and Norbert Ritter: Towards Workload-Aware Self-Management: Predicting Significant Workload Shifts.
• Mohammed Abouzour, Ken Salem, and Peter Bumbulis. Automatic Tuning of the Multiprogramming Level in Sybase SQL Anywhere.

I will post a preprint of Mohammed’s paper on this blog once the camera-ready copy has been submitted to IEEE.

On 15 December I presented a webinar entitled “Object-relational Mappers: Friend or foe?”. A flash recording of this webcast is available here. After entering your contact information, you’ll be automatically redirected to another page and the flash recording will begin immediately.

This afternoon we made available the beta release of Innsbruck, the codename for SQL Anywhere 12. Windows and Linux platforms are supported with this beta.

If you are interested in trying the new beta release, register here; you’ll be subsequently notified by email on how to download the software.

In the coming weeks, look for a number of articles on new features included in this release of SQL Anywhere. A short list of these features include:

• comprehensive support for spatial data types;
• automatic tuning of the database multiprogramming level (-gn command switch);
• support for Oracle-style SEQUENCEs;
• support for SELECT statements over update DML statements;
• support for immediate maintenance of materialized views that contain outer joins;
• support for IS DISTINCT FROM and IS NOT DISTINCT FROM search conditions;
• locking improvements;
• support for the TIMESTAMP WITH TIME ZONE data type;
• self-healing database statistics technology;
• and a plethora of additional features, option settings, and performance improvements.