AS400 Tutorials

Inside the Mailbag: Query Manager

March 13th, 2013 by John
Respond

Dear Friend,

Recently I received an email from a reader… they didn’t quite see the value of using Query Manager vs. Query/400. I answered him via email but I also wanted to expand a little on my reply.

In some ways I agree, Query/400 is quick and easy. Believe it or not I still use it from time to time today.

But the real bonus to Query Manager is SQL. In fact any tool on the system that uses SQL is more efficient.

SQL is fast, it is the standard way to access, retrieve and modify data on the platform now.

SQL makes your life so much easier. Database admins from other platforms probably look at that in a weird kind of way since SQL is sort of synonymous with databases.

But if you have been around the platform like I have – SQL wasn’t always so and we used other tools to get things done. Remember using PFE?

Yeah PFE was sort of like the dark days before we had SQL on the 400.

Anyhow… Onward.

SQL is just so much more efficient and it is faster. If you take a Query definition that joins multiple files and it takes some time to run, I can assure you by porting it over Query Manager, it will run faster… probably without tweaking the SQL statement at all.

You can also finesse the underlying SQL statement used by Query Manager. You can tune it. You can work with it. This cannot be accomplished with Query/400.

With SQL also comes a whole new bag of goodies that have no equivalent in Query… things like CASE, casts, and sub-queries.

Query Manager is not a glorious report building package with slick formatting and a nice GUI, but it is an excellent alternative to Query/400.

You probably already have Query Manager installed and licensed… along with the tools available on the system you can port your query definitions right over.

 

Warm Regards,
-John Andersen

Tags:   · · · · · Comments Off

Single Level Storage

November 12th, 2012 by John
Respond

One of the things any administrator on the IBM i takes for granted is the DB2 database just works and is more or less self optimizing. Have you ever wondered how or why that is?

Go check out this article:

http://db2fori.blogspot.com/2012/11/one-of-crown-jewels-single-level-storage.html

The DB2 for i blog is on my shortlist for good reason… articles like this one that dive into the guts of the system writen by the guys at IBM. Go check it out.

-John Andersen

Tags: Comments Off

Making Use Of SQL Views

November 9th, 2012 by John
Respond

Views are a really fantastic feature of SQL. On the IBM i a view is implemented as a logical file that does not have a key… this means the system does not have to maintain an access path or incur overhead with a view.

If you can put together a simple select statement then you can create a view.

But how are views handy? Here are a few places:

Storing “queries” permanently as a file. A view is really just an SQL select statement stored as an object and then run when accessed.

For example at one business I was at I made up some views that where used for month end reporting. They joined together (you can use joins in views) inventory transactions to the general ledger. The views where then had some reports or Query/400 queries over on to make sure everything lined up. Speed and efficiency where not a concern and the views worked perfect.

Take existing DB2 tables and files, laying a view over the top while adding descriptive column labels or expressions. Now you can create a set of views for users to query against with exactly the right columns of data with descriptions they can understand and even calculate some columns of data.

When views are used in this way it actually has a couple of benefits.

Now people wanting to query data can get exactly what they want and from an administrative standpoint you can secure things and provide access to exactly the views they will use. Simply whip up a few views using the “create view” statement and setup a library of views for reports and database mining. Your users will love you for it.

Do you see how views can be really handy? If you are not using them today then you should be!

If you agree then check out my IBM i SQL course and find out how to start making your very own SQL views.

-John Andersen

Tags:   · 1 Comment