At the core of every AS/400, iSeries and IBM i is the DB2 database tightly integrated into the operating system. If you work with the 400 for any length of time eventually you will end up diving into the data stored within the database tables, usually to update a field correcting inaccuracies or other erroneous data.
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 features that are part of the add-in tool. Now with minimal training you or your users can quickly build great looking reports in friendly spreadsheet formats everyone is used to seeing.
iSeries Navigator is an excellent tool for taking care of your AS/400 data and includes some really advanced features when it comes to query analysis. With Navigator you can issue SQL statements to whip up reports on the fly, mass update data, manipulate data tables, query data and use the data performance tools to collect performance metrics from the databases 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 look up is the ability to create SQL views and indexes over existing physical and logical files. With views you can create complex subsets and slices of data utilizing joins, sub-select statements, scalar functions, logical columns and more.
You can also get yourself into trouble pretty quickly doing this as well by bogging down the system with a bunch of access paths that it then has to maintain. Besides 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. Use caution when going the route of building out views unless they really are necessary.
Lastly the old trusty stalwart when it comes to data tools on the AS/400 is the Data File Utility or more commonly refereed to as DFU. Good old Data File Utility 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 use later on if the need strikes.
But DFU is a bit old and not really user friendly. I find the interface to be clunky but it does work and it comes with the system for free if you are in a pinch.
It's worth bringing up that there are some other commercial tools available for working with your physical and logical files that are a cut above the old DFU utility. These tools come with more advanced features and are certainly worth looking into if you need to constantly update data manually on your system.
You should also keep in mind that much like DFU these third party tools do not necessarily error check data that is input by the user. If this is important then you will want to build a program from RPG and a DDS screen or another language to enforce data quality and maintain the database integrity.
John Andersen is an IT manager with ten years of experience on the AS/400 and iSeries platform. Be sure to check out his website at Midrange Jump Start for more information on how to effetively manage your system in a few short hours.
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 features that are part of the add-in tool. Now with minimal training you or your users can quickly build great looking reports in friendly spreadsheet formats everyone is used to seeing.
iSeries Navigator is an excellent tool for taking care of your AS/400 data and includes some really advanced features when it comes to query analysis. With Navigator you can issue SQL statements to whip up reports on the fly, mass update data, manipulate data tables, query data and use the data performance tools to collect performance metrics from the databases 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 look up is the ability to create SQL views and indexes over existing physical and logical files. With views you can create complex subsets and slices of data utilizing joins, sub-select statements, scalar functions, logical columns and more.
You can also get yourself into trouble pretty quickly doing this as well by bogging down the system with a bunch of access paths that it then has to maintain. Besides 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. Use caution when going the route of building out views unless they really are necessary.
Lastly the old trusty stalwart when it comes to data tools on the AS/400 is the Data File Utility or more commonly refereed to as DFU. Good old Data File Utility 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 use later on if the need strikes.
But DFU is a bit old and not really user friendly. I find the interface to be clunky but it does work and it comes with the system for free if you are in a pinch.
It's worth bringing up that there are some other commercial tools available for working with your physical and logical files that are a cut above the old DFU utility. These tools come with more advanced features and are certainly worth looking into if you need to constantly update data manually on your system.
You should also keep in mind that much like DFU these third party tools do not necessarily error check data that is input by the user. If this is important then you will want to build a program from RPG and a DDS screen or another language to enforce data quality and maintain the database integrity.
John Andersen is an IT manager with ten years of experience on the AS/400 and iSeries platform. Be sure to check out his website at Midrange Jump Start for more information on how to effetively manage your system in a few short hours.
Comment