If you are still cranking out the traditional physical and logical file with DDS I think it’s time to get up to speed with SQL for both creating and querying files. For a long time I held onto using DDS, but with the capabilities in SQL if you are still clinging to DDS I think you are blowing it big time like I was. Let me explain.
First on the list of SQL cool features are views. Views are frankly becoming one of my favorite uses of SQL. With a view you can essentially create a permanent type of query in the form a logical file. A view is created as a non-keyed logical file. So they do not incur overhead like traditionally defined keyed logical’s with access paths the system has to maintain. Pretty cool, huh?
With views you can simply do so much. Anything that you can write in an SQL select statement can be created as a view that can be queried. Another problem this helps alleviate is by setting up specialized views with the appropriate columns of data and descriptive labels that your end users can hammer on with reporting programs like Crystal.
Not directly related to defining files, the next step when you make use of SQL is to start using it for queries and reports. For instance when it comes to report writing you may have converted over to using Query Manager instead of IBM Query for i.
But you are severely handicapping your reporting efforts if you are building “prompted” style Query Manager queries instead of writing them in SQL… you just have so many more features available. Sub-queries and the ability to use a whole slew of file join types come to mind.
Finally SQL is more universal and portable that DDS. Each of the major database systems you run into do implement features different and dialects of SQL that may deviate from ANSI standards. But once you have the basic skill-set down it is easily translated to other platforms and tools.
And with many IT environments running mixed databases from several vendors, adding SQL skills to your tool belt can only help.
John Andersen is an IT manager and RPG programmer for the midrange platform. Check out his AS400 Tutorials blog and sign up to claim your free system i tutorial and video courses.