Migration of stored procedure to distributed cloud database


Source:

K. N. Duan, "Migration of Stored Procedure to Distributed Cloud Database", Applied Mechanics and Materials, Vols. 513-517, pp. 2115-2120, 2014

 

Abstract:

Many legacy applications rely on a single instance relational database. In the age of big data, applications with growing popularity may continue to generate large amount of data. The data would be beyond the limit of a traditional relational database. These applications can be migrated to a distributed database system for acquiring high scalability and performance. But the migration process is complicated especially for user defined stored procedure. In order to obtain the correct behavior, a stored procedure usually has to be rewritten in the application code. This rewriting is usually a time consuming and unpleasant task. This study gives a mechanism for migrating stored procedure from single instance relational database to distributed sharded database without the need of application code rewriting. In the migration solution offered by this study, Oracle stored procedure code is parsed, analyzed, and translated so as to be stored and executed globally in a sharded database.

 

Summary:

This article lays out an approach for migrating stored procedures to big data platform called EngineOne.  The engine contains a distributed relational database DBOne which implements a sharding pattern.   Existing tables in a traditional RDBMS system such as oracle can be broken into shards, and existing SQL can executed against the engine.  The underlying engine worries about how to process the sql and merge the data back from the different shards (this is done at the engine level).  The crux of the approach is to take a PL/SQL stored procedure, and run it through a routine that splits up the sql, and converts the result into java byte code (note that the code generated are just JDBC wrapped functions that contain the original sql command, the sql itself is not transpiled into java.).  The author doesn't go into specifics about performance of the packaged code, and his samples are extremely simple.  There isn't much detail on how DBOne works, or if the underlying data merging it's doing between shards is performant (he cites the vendors website, which claims the engine is performant without mentioning any benchmarks).  I don’t think this DBOne product has much industry penetration, but his overall idea of breaking apart PL/SQL and executing it via JDBC has some interesting merits.

 

 

Building on the approach:

I would be concerned that this type of system would have major performance drawbacks, but the migration approach itself is simple and might work well in trivial cases, for very simple pl/sql statements.  The author makes no mention of translating cursors into bytecode, and the approach really only seems to address "read" statements, and some simple looping and branching constructs, and does not mention other CRUD operations, or transactions.  It's not clear if that choice was due to project scope, or limitations of the underlying engine.   His proposed tool is primitive, but intruiging, and could be built off of.  A more robust automated tool could be created that handles non-trivial cases, and that tool could potentially be used in a variety of different environments, not just with this DBOne product.  

Including the results of some real "before and after" experiments would be extremely useful.