Gene Y. C. Fuh

A framework for enforcing application policies in database systems

As database systems have grown in terms of scale and complexity, administration tasks have become increasingly difficult and time consuming. A scarcity of skilled database professionals has meant that human costs have begun to dominate the total cost of ownership (TCO) of a database system. Database vendors are under immense pressure to provide solutions that make their products easy to administer in areas such as problem diagnostics, monitoring, query tuning, access control and system configuration. To address this issue, we have built a framework that allows control over many administration operations via the use of policies. Users can uniformly define, manage and enforce policies to affect disparate aspects of the system. In our framework, policies are declarative constructs that are comprised of type, scope, condition and action. Policy groups cover query monitoring and tuning, query prioritization, system configuration, access control, report generation, etc. Policy scope defines the domain over which policies apply. Policy actions are performed if certain conditions are true. This framework has been fully integrated into DB2 for z/OS V9. Using detailed system performance evaluations, we report that enforcement of policies is largely a function of data-collection granularity. Under the setting for normal monitoring with minimal report, the overhead on system performance is very low (0.1%).

Integration of Server, Storage and Database Stack: Moving Processing Towards Data

Storage architecture includes more and more processing power for increasing requirement of reliability, managibility and scalability. For example, an IBM storage server is equipped with 4 or 8 state-of-the-art processors and gigabytes of memories. This trend enables analyzing data locally inside a storage server. Processing data locally is appealing under the following circumstances: (1) huge reduction of data flowing to the host, (2) reduction of CPU consumption on host. Accordingly, the benefits are (1) less data traffic through IO channel to the host, (2) better utilization of host bufferpool, and (3) enabling more workload on the host. One crucial task is to understand how DBMS can benefit from such hardware. That is to identify which database operations are beneficial to be offloaded given a query workload in a particular setting. For certain operations, we establish value proposition via various approaches and show the analytical and experimental results. In particular, starjoin queries are commonly used in business warehouses. We propose to offload a portion of a starjoin query from host to the POWER5 P processors on a storage server, which dramatically reduces the amount of channel IO and host CPU consumption. Moreover, the query elapsed time is improved via the exploitation of the state-of-the-art P processors on a storage server.

Aggregate predicate support in DBMS

In this paper we consider aggregate predicates and their support in database systems. Aggregate predicates are the predicate equivalent to aggregate functions in that they can be used to search for tuples that satisfy some aggregate property over a set of tuples (as opposed to simply computing an aggregate property over a set of tuples). The importance of aggregate predicates is exemplified by many modern applications that require ranked search, or top-k queries. Such queries are the norm in multimedia and spatial databases.In order to support the concept of aggregate predicates in DBMS, we introduce several extensions in the query language and the database engine. Specifically, we extend the SQL syntax to handle aggregate predicates and work out the semantics of such extensions so that they behave correctly in the existing database model. We also propose a a new rk_SORT operator into the database engine, and study relevant indexing and query optimization issues.Our approach provides several advantages, including enhanced usability and improved performance. By supporting aggregate predicates natively in the database engine, we are able to reuse existing indexing and query optimization techniques, without sacrificing generality or incurring the runtime overhead of database-external approaches. To the best of our knowledge, the proposed framework is the first to support user-defined indexing with aggregate predicates and search based upon user-defined ranking. We also provide empirical results from a simulation study that validates the effectiveness of our approach.

Inter-Data-Center Large-Scale Database Replication Optimization – A Workload Driven Partitioning Approach

Inter-data-center asynchronous middleware replication between active-active databases has become essential for achieving continuous business availability. Near real-time replication latency is expected despite intermittent peaks in transaction volumes. Database tables are divided for replication across multiple parallel replication consistency groups; each having a maximum throughput capacity, but doing so can break transaction integrity. It is often not known which tables can be updated by a common transaction. Independent replication also requires balancing resource utilization and latency objectives. Our work provides a method to optimize replication latencies, while minimizing transaction splits among a minimum of parallel replication consistency groups. We present a two-staged approach: a log-based workload discovery and analysis and a history-based database partitioning. The experimental results from a real banking batch workload and a benchmark OLTP workload demonstrate the effectiveness of our solution even for partitioning 1000s of database tables for very large workloads.

Debugging DB2/CS client/server applications

The technology of running external programs on the server side of a relational database management system (RDBMS) has been developed in the past few years. Database 2 TM / Common Server (DB2 TM /CS) for UNIX TM -based platforms supports external programs (i.e., userdefined functions and stored procedures) that are written by the application developer in a third-generation language such as C or C+ +. The main difficulty in debugging these external programs is that they are executed under the control of DB2/CS, which is itself a large software system for which no source code is provided. It is therefore impractical for a debugger to penetrate through the layers of software of DB2/CS to locate and debug the external programs. It is also very difficult for the debugger to determine when an external program will be invoked by the database engine and in which process it will be rbn. In addition, in an environment where the DB2/CS server is shared between a large number of users, it is necessary to ensure that the debugger does not violate the security of the DB2/CS system. In this paper, we describe a set of extensions to a distributed debugger and DB2/CS to support the debugging of external programs. A prototype was implemented to show the feasibility of the proposed approach.

Optimizing Inter-data-center Large-Scale Database Parallel Replication with Workload-Driven Partitioning

Geographically distributed data centers are deployed for non-stop business operations by many enterprises. In case of disastrous events, ongoing workloads must be failed over from the current data center to another active one within just a few seconds to achieve continuous service availability. Software-based parallel database replication techniques are designed to meet very high throughput with near-real-time latency. Understanding workload characteristics is one of the key factors for improving replication performance. In this paper, we propose a workload-driven method to optimize database replication latency and minimize transaction splits with a minimum of parallel replication consistency groups. Our two-phased approach includes 1 a log-based mechanism for workload pattern discovery; 2 a history-based algorithm on pattern analysis, database partitioning and partition adjustment. The experimental results from a real banking batch workload and a benchmark OLTP workload demonstrate the effectiveness of the solution even for partitioning 1000i¾?s of database tables in very large workloads. Finally, the algorithm to automate the cyclic flow of workload profile capturing and partitioning readjustment is developed and verified.

An Approach for In-Database Scoring of R Models on DB2 for z/OS

Business Analytics is comprehensively used in many enterprises with large scale of data from databases and analytics tools like R. However, isolation between database and data analysis tool increases the complexity of business analytics, for it will cause redundant steps such as data migration and engender latent security problem. In this paper, we propose an in-database scoring mechanism, enabling application developers to consume business analytics technology. We also validate the feasibility of the mechanism using R engine and IBM DB2 for z/OS. The result evinces that in-database scoring technique can be applicable to relational databases, largely simplify the process of business analytics, and more importantly, keep data governance privacy, performance and ownership.

On the linkage of dynamic tables in relational DBMSs

Tables and operations over tables are at the center of the relational model and have been at the core of the Structured Query Language (SQL) since its development in the 1970s. As database applications have grown rapidly, the concept of tables has been generalized in database languages. The new generalized table concept in the SQL standard and in some commercial databases includes explicitly defined derived tables, such as user-defined temporary tables, transition tables, user-defined table functions, and table locators, that can be manipulated by users. We call them dynamic tables, because their entities exist only at run time. The challenges that these dynamic tables pose to existing relational engines lie in the linkage between the creation of the derived table and its references. In this paper, we describe a uniform framework for compile-time and run-time processing of dynamic tables. We also give a thorough explanation of how such a generic framework can be realized in existing relational database management systems, such as IBM DATABASE 2TM Common Server. Our experience with our prototype has shown the simplicity, generality, and efficiency of our approach.