ABC Article Directory banner displaying blue butterfly logo. Click to go directly to the main Homepage


Discover Key IBM I SQL Objects to Make Life Easier






     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






Article Source: http://www.abcarticledirectory.com

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.


Posted on 2012-12-16, By: *

* Click on the author's name to view their profile and articles!!!


Note: The content of this article solely conveys the opinion of its author.


Animated RSS Symbol for the ABC Article Directory rss category feed ... Get Every New Computers Article Delivered Instantly!



Facebook Comments


" data-width="470" data-num-posts="10">




Cartoon image of a figure holding a magnifying glass looking for articles in the ABC Article Directory

Still Searching? Last Chance to find what you're looking for with a Google Custom Search!



Custom Search

Or.... You can search this site using our Bing Custom Search!

Bing




Did You Like/Dislike This Article? Give It YOUR Rating!

Please Rate this Article

No Ratings Yet. Be The First To Rate This Article





/EDF Publishing. All rights reserved. Script Services by: Sustainable Website Design
Use of our free service is protected by our Privacy Policy and Terms of Service Contact Us

User published content is licensed under a Creative Commons Attribution-No Derivative Works 3.0 Unported License
.
Increase your website traffic with Attracta.com

Valid CSS!


ABOUT SSL CERTIFICATES
Date:


Powered by ABC Article Directory