SQL and Conversion Strategies

Published: December 3, 2008

Hey, Ted:

I just read your article Common Table Expressions Ease System Conversion. Instead of changing each SQL statement to add a common table expression, I'd suggest you create permanent views that join the new files. Give the views the same names and the same column names as the original file. Because a view doesn't have a key, you can have as many views as you want without any performance decrease. A permanent view can also be used for Query/400. In this way queries do not have to be changed.

--Birgitta Hauser

I am always delighted to find mail from Birgitta Hauser in my inbox. I know before I read the first word that I am probably about to learn something helpful.

I did not mean to imply that the use of common table expressions was our only conversion strategy. The crux of the tip was that a common table expression overrides a database file of the same name. That can be a handy bit of information to know.

To Birgitta's point, we are using other techniques. We have created some join logical files, which RPG programs can continue to use for random access operations (CHAIN, SETLL, READE, etc.) We have created SQL views and indexes as well.

We recently replaced an SQL reference to a join logical we had created, and performance got very bad very quickly. After we had written an SQL view over the same data, the program ran in seconds, as it had before.

Thanks to Birgitta for that sage advice. She had other comments, which I may explore in future editions of Four Hundred Guru.

--Ted

RELATED STORIES

Common Table Expressions Ease System Conversion

SQL Doesn't Like Logical Files

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot