4 Tips For Unlocking The 400 Database

[John Andersen]

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.

[tomholden]

Re: 4 Tips For Unlocking The 400 Database

Unlock the database??? ah, crap those wasted days and hours i just spent locking it down!

[kitvb1]

Re: 4 Tips For Unlocking The 400 Database

... 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. ...

John... This is not quite clear what you're trying to say here.

SQL views should never be created over logical files. SQL indexes (AFAIK) can only be created over physical files.
Views have no virtually no system overhead, whilst indexes have ca. the same (or slightly less) overhead than logical files. Depending on the view, the overhead is more likely to be seen in the program, but not the system.

Views are a very useful tool - but not just for Excel Add-in or reporting tool. An example would be to create a view over the customer, order header, & order override file (wherein records may or may not exist and fields may or may not be filled) to get the document language. Another view - same files - for the transport method, one for the incoterms, one for carrier and so on. A tiny service program over the view and you have

LangCode = GetOrdLang(l#cono: Ord);

which is then portable.

(hopefully I've explained what I mean clearly )

[B.Hauser]

Re: 4 Tips For Unlocking The 400 Database

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.

John, you are confusing DDS described logical files, SQL Views and Indexes and Access Paths.

1. An SQL view can only be built over a DDS described physical file, a SQL table or an SQL view, but never over an DDS described logical file.

2. An SQL view never has an access path (not even under release 7.1). In this way access paths maintenance for SQL views is always rebuild. That means you can have thousands of views built over an DDS described physical file or an SQL Table without any performance decrease when inserting, updating or deleting a row (or rows) in the base tables.

3. An SQL index can be compared with a keyed logical file. Because an SQL index is always keyed it is always created with access path maintenance *IMMED. Having thousands of indexes built over a physical file or SQL table will decrease performance, because the access paths (or better the bitmaps that are integrated in the indexes or keyed logical files) must be actualtized as soon as a row is inserted, updated or deleted in the base table (or DDS described physical file). Having identical access paths (in different logical files or indexes), i.e. the same key fields in the same sequence will result in a single access path, that must be maintained. A DDS described logical file has a default page size of 8 K while an SQL index has a default page size of 64K.

4. DDS described logical file can be created with access paths maintenance *IMMED or *REBLD, but must be created with access paths maintenance *IMMED as soon as there is a logical file with a unique key build over the based physical file.

5. As Kit already pointed out Views should not be avoided, but used whenever possible, i.e create a view for each problem or task you have to solve. Instead of accessing the physical files or SQL indexes directly give this view to your RPG-Programmer, Cobol-Programmer, Java-Programmer, PHP-Programmer, Query-User and even Exel User and all will get the same result. If you have to change the view (for example until now you select status 1 and 2 but now you also need status 3) you only have to change your view and all programs and queries and excel downloads will represent the enhanced data.

Also using views may allow you sometime to redesign your database and remove redundancies. Assumed you have a view that is today nothing else than SELECT * FROM TABLE and you use this view for every insert update or delete and even as base for all other views built over the based physical file or SQL Table. Until now you didn't win anything. Now add instead of triggers for Insert, Update and Delete. An instead of trigger is a trigger that is linked with a view but NOT with a physical file and activated as soon as the view will be changed. It's the trigger program that will update the underlying physical files or SQL tables. Now imagine you want to redesign your database and remove redundancies. The view will be recreated based on several joined files/tables, but still contains the same fields/colums. None of your programs or queries has to be changed. ("Only" the database, i.e the table was splitted into serverals, the view and your instead of trigger program.)

6. Indexes cannot be directly used within SQL statements, but can be used with native I/O like any other keyed logical file.
Eventhough you cannot specify an index in an SQL statement it's the query optimizer who needs indexes (or access path stored in logical files) to get fast access to the required data. Without existing access paths (in either SQL indexes or keyed logical files) tables scans must be performed (or a temporary index may be created). Both will slow down query execution.

7. Specifying a DDS described logical file in a SQL Statement is possible, but should be avoided. The query optimizer needs to analyze the DDS described logical file, take the field selection, join information and select/omit clauses (but not the key information) and has to rewrite the SQL statement based on the physical files/tables and this information. After rewriting the SQL statement, optimization starts. At this time the optimizer does not know anymore that there was specified a logical file that contained a key. ... and even worse SQL statements where a DDS described logical file is specified must be executed by the old (classical) Query Engine CQE and cannot profit from the advantages of the SQL Query Engine.

8. Specifiying SQL views in an SQL statement will not cause any rerouting.

Birgitta

[Terry Wincheste]

Re: 4 Tips For Unlocking The 400 Database

5. As Kit already pointed out Views should not be avoided, but used whenever possible, i.e create a view for each problem or task you have to solve. Instead of accessing the physical files or SQL indexes directly give this view to your RPG-Programmer, Cobol-Programmer, Java-Programmer, PHP-Programmer, Query-User and even Exel User and all will get the same result.

Birgitta,

I was under the impression that the use of a view in a high level language requires embedded SQL, true?
(this is one of the reasons I have avoided using views....)

Terry

[kitvb1]

Re: 4 Tips For Unlocking The 400 Database

Hey Biggie

.... and that's your 16 000 word allocation for today

Toi, Toi, Toi, for your Common presentations next week - have fun!

[B.Hauser]

Re: 4 Tips For Unlocking The 400 Database

... as long as you do not need a key, a view can be used like any other logical file in the F-Specs.
If you need a specified sequence you indeed need to use embedded SQL or have to create an SQL index (without specifying fields) with the required key fields and use this one in the F-Specs.
May be I was not clear, when I said give it to your RPG-Programmers.

Even though IBM made a lot of efforts for native I/O, such as local F-Specs, defining a file lika an other file, using long field names (7.1) and RPG Open Access (ROA in Release 7.1), I'd expect that native I/O will be replaced more and more with embedded SQL.

@Kit - I only trained for my presentations at COMMON

[tomholden]

Re: 4 Tips For Unlocking The 400 Database

just another tidbit of info on views and native I/O. if you do a DSPFD on a view you'll see that the view is specified as a LF (since IBM hasn't really differentiated between LF, INDEX, or VIEW as object types) and is unkeyed. you'll also see the SQL statement used to create the view (actually it's what is executed at run-time since a view is not really a "static" index/LF) hope that confuses you more...lol

[Terry Wincheste]

Re: 4 Tips For Unlocking The 400 Database

@Birgitta: Thanks for the additional information. I'm a COBOL'er. My thought was to create a view that was narrowly focused and then use it like a select/omit logical My SQL background is limited to a couple of one-line select and update statements...so I guess I need to do some reading on how to create views and try reading them sequentially with COBOL.

@Tom: I'm always confused with SQL ;-) My original perception of a View was that it was the same as a dynamic select/omit in DDS, which in my experience, can be devastating to open deepending on the file size...

[B.Hauser]

Re: 4 Tips For Unlocking The 400 Database

@Terry
Views are quite easy, you only have to learn what's possible with an SQL Select-Statement. Everything that is possible with an SELECT statement can be used within an view except ORDER BY. That's why a view never has an access path.
If you finally have built the SELECT statement you need, just add CREATE VIEW Bibl./ViewName as (Select .... ) and that's it.

As of COBOL or RPG. If you are familiar with the basic embedded SQL commands (Declare Cursor, Open, Fetch, Close, Select ... into, Update, Delete, Insert) and how to use host variables. You can handle every view. You may also reduce source code compared with classic I/O, because a lot of business logic is now moved to the database.

Birgitta

[kitvb1]

Re: 4 Tips For Unlocking The 400 Database

@Terry: If you do not use "select * ...", but rather select individual fields, in your views - no level check is performed*. The advantage here is that you can change business logic - without having to recompile many programs. The danger here is that you can change business logic - on the fly - make sure sure your controls in place to implement such a change are secure.

*Admittedly, I had to check with Biggie before posting this that my memory isn't failing me. Thanks Biggie.

[B.Hauser]

Re: 4 Tips For Unlocking The 400 Database

@Kit,

I love listeners who not only pay attention what I explain (even in a foreign language) but also remember sometime later what I told

Birgitta