Seo Directory Site

At the core of every AS/400 is the built in DB2 database that is tightly integrated into the operating system. So if you work with the 400 for any length of time eventually you will end up diving into the data stored within the database, usually to update a field correcting inaccuracies or other foul-ups. In this article we will review a few tools that you can use today to start working with your systems data for reporting, extracting, manipulating, collecting performance data and more.

The first data tool is the Excel Add-in that enabled you to download data right from you’re AS/400 or iSeries directly into an Excel spreadsheet with just a couple mouse clicks. This is a great tool for building on the fly reports with the ability to add in data constraints, sort and sequence as part of the add-in. Now with minimal training you or your users can quickly build great looking reports in friendly spreadsheet formats everyone is used to seeing.

Operations navigator is another excellent tool for taking care of your AS/400 data and includes some really advanced features. With operations navigator you can issue SQL queries to generate ad-hoc reports or update data en mass, manipulate data tables, query data and use the data performance tools to collect performance metrics of the query engine. With these metrics in hand you can thoroughly analyze query requests and response times to diagnose which queries are taking up the most amount of resource.

A huge boon for data manipulation and lookup is the ability to create standard SQL views and indexes over existing data tables. Using views you can create complex subsets of data utilizing joins, sub select statements, scalar functions, logical columns and more. You can also get yourself into trouble doing this as well, especially take precautions when building views, indexes or logical files over files from a third party vendor as this can cause problems when it comes time to upgrade or patch the software. An SQL view or index by itself doesn’t really add much until you use the Excel Add-in or another reporting tool to get the data into a user friendly format.

Finally the old trusty stalwart when it comes to data tools on the AS/400 is the Data File Utility or more commonly reffered to DFU. It has been around for so long that the word DFU is actually used as a verb. Data File Utility allows you to manipulate data files on the fly and then save the program for later use. DFU is a bit old, not exactly user friendly and sort of clunky but it does work and it comes with the system for free.

I should mention that there are some other commercial tools available that are a cut above the old data file utility program. These tools come with more advanced features and are certainly worth looking into if you need to constantly update data manually on your system. It’s almost important to keep in mind that like DFU these third party tools also won’t error check data that is input by the user. If this is important then you will want to build a program from RPG or another language to enforce data quality and referential integrity.