AS400 Tutorials

Answer Time

October 10th, 2012 by John Andersen

Alright here are answers to your questions from last weeks Question Time post.

1. How important is SQL in the 400 and why would I want to use it?

Increasingly important for a variety of reasons, but here are three areas:

a. Database creation, maintenance and queries. You can simply do much more for defining tables, views and indexes with SQL than is possible using traditional DDS defined files. It is not just for programmers and I cover many of those reasons on the Midrange IMHO article that you can read here:

Data maintenance tasks are a whole bunch easier and faster with SQL. If you need to update/change/delete data for 1000 or 1,000,000 records in a table, without SQL, this meant you painfully went through them one by one with a program like DFU or _______ file editor. Some people could whip together an RPG program. But in minutes, using one SQL statement you can accomplish the same exact task of updating/changing/deleting all those records at once. Pretty cool, huh?

b. RPG programmers have traditionally relied on using keyed logical files, and a lot of them. It used to be when you created a report or screen with subfiles you needed to have keyed logicals to access the data in the sequence you wanted it or you had to read through the entire file record by record. This resulted in creating a bunch of logical files for every possible sort order of the data. OPNQRYF solved some of these issues, but I found it a royal pain to work with and have not touched it in about 10 years.

You can use “embedded” SQL right in your RPG (and other) programs… basically you can slap SQL right in your code. You can also build SQL statements dynamically in your program to satisfy every possible sort of the data without needing a bunch of logical files.  This saves time and resources.

c. How much longer will DDS be around for file creation? I won’t make any predictions but at some point in a future release of the OS IBM may or may not support the creation of DDS based physical and logical files. Even today there are features that are a part of SQL that have no equivalent option in DDS. You are better off changing NOW than procrastinating and having your hand forced down the road.

Why would I want to use query manager?

Query Manager is good for creating reports and querying the database, although you can use it to run all types of SQL and not just SELECT statements. Many places are still using Query/400 or IBM Query for i… if so, you should be converting those reports over to Query Manager. If you like the idea of running the reports “natively” and taking advantage of features like spooled files, batch, security, resource control and job management that make the IBM i a great system then Query Manager is a great choice.

Now let’s talk about what drives Query Manager. Because QM is based on SQL it has many distinct advantages like using the SQL Query Engine. If you have a query, simply through advancements in SQE, it can run significantly faster without making any changes to the SQL. With SQL you can also address many reporting problems beyond the capabilities of Query/400 that used to require an RPG program.

Some businesses have made the decision to allow regular users (non system administrators) to create Query/400 query definitions… eventually they run into issues with those users grinding the system to a halt (particularly if your system is governed with limited interactive CPW) by excessive use of F5 to preview the query or even other bigger issues like those users overwriting important tables through the use of the output file option.

Query Manager has solved all of these problems and then some. You can configure ALL the security and job control aspects BY user profile. You don’t have to worry about grinding interactive sessions to a halt through excessive report previewing because Query Manager can run the query once and then save a snapshot of the data while you preview, clean up and format the report form. The amount of data returned by an interactive query can also be controlled, so only 100 records can be brought back while building and previewing the report, then when submitted to run the whole data set will be queried.

With Query Manager you have complete control over what types of SQL statements can be run, drilling even deeper you can control if the queries can be run interactively or in batch and even if they are allowed to create an outpfut file from the query results.

The real secrete sauce to Query Manager that you don’t see mentioned often is the ability to create Query Manager Procedures.

I’m new to the I series and I want to quickly learn some powers steps, like printer, out queue, configurations.  Which course do I select ?

If you are brand new to the AS/400, iSeries or IBM i platform then start with the Power System Jump Start. It will show you all of the ropes and time saving techniques to get you started operating a system quickly. I cover it in the post here:

How do I change a printer within a out-queue?

It is the other way around. You have an output queue associated (or not) with a printing device. You can also have a remote output queue that does not use a printer device description. But one way is by using the CHGWTR command you can change or associate an output queue to a print device (writer).

I liked question #1 the best. Leonard wins a free copy of any of my products… Leonard, let me know which one you want by leaving your selection as a comment to this post along with a valid email address. If your choice is Query In A Flash be sure to leave your mailing address (don’t worry I won’t approve comment the comment so nobody else but me will see it).

-John Andersen

Tags: 1 Comment

1 response so far ↓