Lately I have been spending a lot of time working with SQL on the AS/400 and IBM i power system. With my background in cranking out traditional DDS physical and logical file background I didn’t see the need for changing until I really dug in and understood how SQL is implemented on the i.

And the writing is on the wall for DDS… plus there are features available using SQL DDL to define and create your database that trumps the capabilities of DDS. It’s time to cover a few of the SQL created objects you will end up using on the platform.

Often people who come to the IBM I from the outside get tripped up with files that have multiple members. Essentially the database allows you to create an additional “member” as part of a table that uses the same column structure but the data between members is treated as separate.

It is almost like having two tables that are defined in exactly the same way that are treated as one object on the system. When using the native programs you can access a secondary member quite easily. But with SQL there is not a direct way of doing so. To get around this you use the CREATE ALIAS statement pointing to the specific file member. This creates a logical file that queries data from the member it is built over.

Next up are views. Quite frankly views are one of the coolest features of using SQL. You build a view to see the data you want, and how you want which is a major advantage. You can build a view to summarize data, cast or format columns… basically anything that can be run as a select type statement can be permanently built using a view.

Views can be especially useful with users running third party reporting tools to mine data out of the system. You can setup specific views and schemas for reporting mining that has just the data they want and use the label on statement that will make column descriptions more readable.

On the IBM i a view is created as a non-keyed logical file. This means that the system does not have to maintain any access paths or incur overhead like with traditional keyed logical files built using DDS specs. The drawback to a view is that it is evaluated and queried at runtime, so it may not be the fastest way to get at records of data.

Lastly you will end up needing to build indexes. Indexes create keyed logical files on the system. If you are writing any RPG type programs you will end up requiring indexes for things like chain and reade operations in your code. Indexes help speed up the retrieval of data and records because they create an access path that the system then maintains. This can be a double edged sword.

Of course you should use indexes judiciously and when evaluating the possibly of creating an index look at the whole picture. Building an index to shave a minute or two off of a month end reporting task probably is not near as important as shaving seconds from an input screen that is used all day every day.

John Andersen is a long time AS/400 and IBM i manager. For more information and techniques on leveraging the power of SQL on your IBM check out his IBM i SQL site.

John Andersen is a long time AS/400 and IBM i manager. For more information and techniques on leveraging the power of SQL on your IBM check out his IBM i SQL site at http://www.ibmisql.com

Author Bio: John Andersen is a long time AS/400 and IBM i manager. For more information and techniques on leveraging the power of SQL on your IBM check out his IBM i SQL site.

Category: Computers and Technology
Keywords: as400,iseries,ibm i,sql,db2