"Data is not information, Information is not knowledge, Knowledge is not understanding, Understanding is not wisdom."
--Cliff Stoll and Gary Schubert

In the past, I've often tried to explain the difference between data and information. It's especially important these days as we see more and more tools designed to show data, usually in a browser. The difference is in how much business logic you can put into place: A person's hire date is data, but how long he's worked for the company is information. Different tools allow different levels of business logic to be included.

I intend to tell you about the Data Perspective of the WDSc tool, while also introducing a number of other tools for displaying data (and in some cases, information). In this article, I'll touch on...

• DSPPFM
• DSPF
• UPDDTA
• Query/400
• Query Management
• STRSQL
• WRKDBF
• iSeries Navigator
• WDSc's Data Perspective
• Open-source alternatives

The Green-Screen Data Display Tools

If all you need is to dump the contents of a table, you already have a number of green-screen tools at your fingertips.

Raw Data Dumps

One of the oldest OS/400 commands and one of the newest can be used to dump raw data to the screen. DSPPFM is the older command, and as such it understands the concept of records. It will show all the data in a file in a nice subfile display, one line per record. You can see the data in character representation, hex, or both. DSPF is one of the newer OS/400 tools, and it sees everything as stream files, much as UNIX utilities do. Since DSPF's focus is on the IFS, it expects files to have EOR (end of record) information like CR/LF. Because DB2/400 files don't have such information, you can get some strange results if you use DSPF on a typical database file in the QSYS file system. And unlike DSPPFM, DSPF has an update-capable cousin known as EDTF; I suggest you never use EDTF on a QSYS file.

The Old Standby

UPDDTA is a long-standing utility that has gone through a number of name changes. You might recognize this command as DFU (Data File Utility). If you run UPDDTA on a file, it will build a default display that will then allow you to add, change, and delete records.

Query/400 and Query Manager

Query/400 is a perfect tool for making reports of simple to moderate complexity. As long as you can live within its processing and formatting constraints, you can do some pretty cool stuff. This is the first tool that lets you start turning data into information by providing a little business logic, selecting records based on their contents, summing, averaging, and all that sort of stuff. It lets you select level breaks and the associated text, and you can even create derived fields.

One level higher than Query/400 is Query Manager. It is entirely SQL-based, so its query and calculation capabilities are more powerful, and it also gives you more control over the report formatting.

I still get a little confused about the difference between Query Management and Query Manager. Query Management is included with the base operating system and is the runtime support for Query Management queries. With Query Management, you can place a single SQL statement in a source physical file and use CRTQMQRY to create a QMQRY object and then STRQMQRY to run the query, using the QMQRY object you created. You can also retrieve QMQRY objects from Query/400 queries using the RTVQMQRY and RTVQMFORM commands.

Query Manager is a user interface to Query Management that allows a user to develop Query Management queries in a manner similar to working with Query/400 queries. It provides a "wizard"-style interface to the QMQRY source members. Query Manager is not free, but it is part of the 5722ST1 product (Query Manager and SQL Development Toolkit). The SQL pre-processor also requires 5722ST1, so if you're compiling (not just running!) programs with embedded SQL (SQLRPG or SQLRPGLE), you also have Query Manager.

STRSQL

Query Manager leads us right into STRSQL. STRSQL is the OS/400 command that gives you direct access to the SQL command-line interface. When you execute the STRSQL command, you are presented with what is essentially a new command line. This command line, though, understands SQL statements. You can type in almost any SQL statement you want and see the results. There are limitations; you can only execute single-statement commands such as UPDATE and SELECT. No cursors allowed.

On the positive side, F13 (function key 13) brings up a display with a number of session attributes, many of which are iSeries-specific. For example, you can easily select the sort sequence to treat uppercase and lowercase letters the same (this can be a lifesaver). You can also direct the output of a SELECT statement to a printer or an output file, rather than the screen. I could devote entire columns to STRSQL, but that's not the purpose of today's column. I hope these couple of paragraphs will whet your appetite enough to do some research on your own.

WRKDBF

This free utility is one if the gems of the iSeries world. Those who know it love it and are continually impressed by the huge heart of the tool's author, Bill Reger. WRKDBF is a really powerful database display tool that combines the capabilities of UPDDTA and STRSQL with the ease of use of DSPPFM. Simply type in WRKDBF MYLIB/MYFILE, and you can edit, copy, and delete records or execute SQL statements on the fly. And it's free. If you don't already have this great utility, visit the WRKDBF Web page and get yourself a copy.

A little side note: I hear a lot of complaining in the mailing lists and so on about how IBM doesn't do this and iSeries programmers don't do that and therefore the iSeries is going to fail as a platform. If the people who spent all that time complaining did like Bill and turned that energy into something positive and then gave to the community something as creative and helpful as this utility, we'd be in a lot better shape. In a lot of ways, that's what the open source community is about, and it's something that's sorely lacking in the iSeries world. So, if you see someone griping, tell 'em to do something positive. And if you see someone doing something positive, please bring it to my attention either in the forums or in email, and I'll be sure to mention it here and on the IAAI Web site.

Out of the Green-Screen and Into the GUI

As you can see, you have a plethora of green-screen tools to access data on your iSeries. Now, I'll introduce a couple of GUI tools that help you do the same.

iSeries Navigator

iSeries Navigator is the tool people love to hate. It's a GUI for iSeries administration that continues to evolve. Some people complain that the interface is a thick client and is somewhat slow and clunky. I've seen the agility of the interface increase with each release, and as of V5R3, IBM has even added a Web interface to the tool. To find out more about the V5R3 version of iSeries Navigator, you can start with this excellent article by Greg Hintermeister of IBM.

But even now iSeries Navigator has a lot of features. I'll focus on the one most important to this particular column, which is the database access. To access data on the iSeries, you select the Database entry under the appropriate iSeries connection. Once you've done that, you can access libraries and, in turn, the files within those libraries. A simple double-click on a file gives the results in Figure 1.

Figure 1: This is the default view of the file QCUSTCDT in QIWS. (Click images to enlarge.)

You can also execute SQL scripts, as shown in Figure 2.

Figure 2: iSeries Navigator also allows you to run an SQL statement and see the results.

WDSc's Data Perspective

This being the "Weaving WebSphere" column, this should be the centerpiece of the article. Unfortunately, the Data Perspective of WDSc just doesn't have that much functionality. In its default state, the Data Perspective is largely unfinished. You can access a table and dump the contents, but that's about it, and even that takes a little bit of doing. This is because, unlike iSeries Navigator, which is focused on the iSeries, the WDSc Data Perspective allows you to connect to just about any type of database. Thus, before you even start, you need to fill in a wizard with a bunch of information about the location and type of the data you wish to access and the method (the JDBC driver) you want to use to access it. Figure 3 shows the setup screen.

Figure 3: WDSc's Data Perspective requires some information before you get started.

More than that, though, is the fact that the Data Perspective really doesn't have much in the way of features. Even after you access a DB2/400 database, all you'll see is the tables, which represent the physical files, as shown in Figure 4.

Figure 4: Most of the folders in the DB Servers view are empty and non-functional.

Even though there are a bunch of folders named "Aliases" and "Triggers" and so on, none of them actually work. They don't pull anything down from the host. You can import a schema from the host into a project, but even then you are severely limited. Just for the fun of it, I tried to create a new stored procedure, and I received the message, "This option is disabled." After a little more futzing around, I got the message, "To use this feature, you must install the DB2 Application Development Client." Well, I've never heard of the DB2 Application Development Client; evidently, this is something that non-iSeries DB2 developers need. Apparently, in order to use WDSc with my iSeries database, I have to purchase additional non-iSeries tools. This seriously reduces the usefulness of the tool, in my opinion.

It's fairly late in the article cycle to get input from IBM on this. Frankly, I didn't dream that I'd run into something like this, so I left this part of the article (what I thought would be the fun part!) until the end. I'll request some clarification from the powers that be and then post a follow-up in the forum. Keep an eye out for updates.

One Last Bullet in the Gun: Open-Source SQL Clients

Interestingly, the very weakness that causes a thumbs down for WDSc for the time being turns out to be a gateway to another concept that might have some very cool ramifications. I hope that WDSc will someday allow us to do all the database design we need, including triggers and UDFs and all the advanced relational database features. In the meantime, there is some hope, at least from the query side, which is what this column is about.

WDSc provides a JDBC driver that will access DB2/400 data (this driver is also available as part of the JTOpen Java Toolkit). Since a JDBC driver exists, that means Java programs can access DB2/400 like they would any other database. That being the case, there are quite a few open-source database query programs (they're typically called "SQL clients"). Any one of these should, in conjunction with the appropriate JDBC driver, allow you to access your iSeries data.

I've had some problems with a couple of these in the past, so I chose other routes to access my data (primarily STRSQL, iSeries Navigator, and occasionally exporting to XML). But even though I can't personally recommend any of them, I can at least point you to a whole slew of open-source SQL clients. Use them in conjunction with the JDBC drivers above, and you should have graphical access to your DB2 data.

Wrapping It Up

Aren't you glad I managed to resist the impulse to call this section "putting it in perspective"? Anyway, it's pretty clear that there are a lot of ways to access your data. And truthfully, as SQL becomes more common, it's also easier to turn that data into information. SQL allows you to do things like date arithmetic (which solves the hire date vs. years employed issue) as well as complex joins to bring in data from other files. This is a great help when doing ad hoc queries.

And it's a good thing to learn SQL. SQL isn't just for ad hoc reporting. For a sizable segment of the inquiry and reporting requirements for a normal business, SQL can reduce your programming load. There are caveats: For example, SQL requests tend to use table and column names (file and field names) across application layers, making the application less flexible. But there are ways around that as well; maybe we'll address that in another column.

For now, enjoy the many tools at your disposal. The MC Press store has a number of books on the various subjects mentioned here. And if this article convinces you to go out and get Bill Reger's remarkable WRKDBF utility, then with that alone my work is done!

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been working in the field since the late 1970s and has made a career of extending the IBM midrange, starting back in the days of the IBM System/3. Joe has used WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. Joe is also the author of E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSc: Step by Step. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..