Dynamic SQL transformation automatization


Download presentation

In case of database migration from MS-SQL Server 2005 on Oraclе 11gR2 dynamic SQL queries translation problem has occured. Original system contains nearly 3000 dynamic queries in approximately 850 stored procedures. All dynamic SQL queries should be translated in target language and transformed concordantly with code, e.g. tables should be renamed.

There is abstract parsing for dynamically generated strings processing, but it can not be used for dynamically generated strings transformation “as is”, because necessary information about source code position for string’s parts is lost. Moreover, syntax analysis does not include semantics, which is very important for translation. Nevertheless, even vanilla abstract syntax analysis can be useful for migration because it can help to improve translation result by detecting of crucial code blocks, i.e. temporary tables creation, variable assignment for reaching definitions improvements, existence of returned recordset and etc.

The main problem of the transformation is calculation of new values of all variables which are used for construction of the query string of interest, such that construction and execution for all possible resulting dynamic queries also would work correct in target system.

Semen Grigorev

Semen GrigorevSemen Grigorev

Semen Viacheslavovich Grigorev finished a mastery at the faculty of the Mathematics and Mechanics of the Saint-Petersburg state university in 2012. Master of Information Technology. Developer in “Lanit-Tercom”.

 

Co-author: Iakov Kirilenko

 


Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© CEE-SECR 2012 • Email:
Powered by WordPress. • Hosted by Hosting Community • Developed by i-Help