AS/400 Programming Tips, Tricks, & Techniques

Nov 5 2012   9:13PM GMT

Getting Started With IBM i SQL

John Andersen Profile: John Andersen

In my previous article I talked about the virtues of moving from Query/400 to DB2 Query Manager, with the major advantage being you can use SQL and all of the features that come with it… But how do you get started?

Well if you are a salty sea dog that has been around the platform but are not up to date with SQL, first there is a new vernacular you will need:

A physical file in SQL is a table.

A logical file is an index.

A record is a row.

A field is a column.

A library is a schema.

Views are something new and really cool. SQL views are implemented on the IBM i as a non-keyed logical file. These are queries that are treated like a file but the data is retrieved when accessed.

These new terms are somewhat important because when you start working with SQL based tools, reading IBM Infocenter docs or hanging out in SQL circles those are the terms that will be used and they are more or less universal across database platforms.

Now where do you get started entering in SQL statements on the IBM i? I prefer using the “Interactive SQL” program accessed by simply entering STRSQL command on a command line.

There are a few reasons why I like and use STRSQL for day to day queries:

You are instantly connected to the database without a layer in between; Interactive SQL plain works without any problems and eliminate any of the hassles of using another program via ODBC.

Do you need to run a query to the screen, output those results to table or create an ad-hoc spool file and printable report? You can do all three in the “Change Session Attributes” option screen by pressing F13 and selecting the output type you would like. I use the printer output type frequently for whipping up reports on the fly.

STRSQL automatically saves your session and each SQL statement you enter is recorded in history where you can easily retrieve it for later use even if you exit out of the program, log off and come back to it later.

Now a drawback. Because interactive SQL is traditional green screen based it is character and not graphical… some people have problems with this, but it does not limit the capabilities of the program and is a matter of your personal preference. If you absolutely abhor green screen then check out the Run SQL feature in i Navigator.

As I mentioned in my previous article about Query/400… The IBM i is optimized for SQL and the SQL Query Engine versus traditional access methods that use the old Classic Query Engine. Let this optimizer do the work for you and optimize your statements, if you are staying current with OS releases you have bought and paid for these features!

If you are just getting started then you need not worry about messing up any data as long as you stick with basic SELECT statements. They will query data from a table and display it to the screen, where you can get into trouble are UPDATE and DELETE statements that can change or delete entire tables in a single keystroke.

Finally, here is a YouTube video that shows the interactive SQL program in action creating a table… enjoy:

-John Andersen

John Andersen is an irreverent IT manager and champion of the IBM i platform.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: