• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL and Conversion Strategies

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ARCAD Software:  FREE Webinar, Managing IBM i and .NET Development, December 9
    RJS Software Systems:  Make your office paperless with WebDocs
    COMMON:  Join us at the 2009 annual meeting and expo, April 26-30, Reno, Nevada

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    ACOM Adds Md2 Systems to Sales Channel Soltis Exiting IBM, But He’s Not Leaving the ‘400

    Leave a Reply Cancel reply

Volume 8, Number 41 -- December 3, 2008
THIS ISSUE SPONSORED BY:

Help/Systems
WorksRight Software
Computer Measurement Group

Table of Contents

  • There’s Power in Edit Words
  • SQL and Conversion Strategies
  • Admin Alert: Tuning i5/OS Storage Pools for Performance

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle