Mimic Green-Screen Interactive SQL Without Development Kit

Hey, Ted:

When it comes to doing a quick query to verify data or troubleshooting a problem, the interactive SQL utility (STRSQL) is indispensable. Unfortunately, this requires purchasing the DB2 Query Manager and SQL Development Kit product, so not everyone has it. An alternative to STRSQL is the Operations Navigator SQL utility, which in my opinion is still fairly slow. Never fear, though, if the inability to do a quick SQL from a green screen has got you down. Here's an easy solution.

Whenever I end up on a system that doesn't have STRSQL available, I rely on two concepts to be able to churn out quick SQL queries: the ability to create a Query Management Query (*QMQRY) object from a source member containing an SQL statement (a *QMQRY is a special type of query object that contains a single SQL statement that can be run), and the ability to create shortcuts with PDM's "user options," which provide a shorthand way of issuing the commands needed to create and run the *QMQRY.

Here's how to put these two concepts in action. First, to create and store the SQL queries, create a source physical file, similar to the following:

CRTSRCPF FILE(SRCLIB/QSQLSRC)
         RCDLEN(91)
         TEXT('SQL Statements')

Note that the record length specified is only 91 characters (12 for date and sequence, leaving 79 bytes for the source itself). The reason for using this smaller source size is that the Create Query Management Query (CRTQMQRY) command, which is used to convert a source member containing an SQL statement to a Querr Manager query object, will ignore all source columns beyond 79 characters. You can use larger source file sizes as long as you keep this limitation in mind.

Use the WRKMBRPDM SRCLIB/QSQLSRC command to work with members in the source file. Use F6 to add a member to the source file. (The source type doesn't really matter, but I usually specify SQL.) Enter an SQL statement in the member, then save and exit.

At this point, having an SQL statement in a source member doesn't do much good, because it can't be executed. However, if you convert the source member to a QM query object, using the CRTQMQRY command, you can then run the query. Here's the syntax for the CRTQMQRY command:

CRTQMQRY QMQRY(OBJLIB/QRYNAME)
         SRCFILE(SRCLIB/QSQLSRC)

Typing in this lengthy command every time in order to create an executable query is cumbersome, so this is where the PDM shortcut comes in. On the WRKMBRPDM screen, press F16 to edit the user options. These user options allow you to create a shorthand way of executing the above CRTQMQRY statement. Press F6 to add a new option.

In the option parameter, type CQ (or some other two-character code), which you will use as your PDM shortcut. I chose CQ to remind me of "Create Query" or "Convert to Query." On the command line parameter, enter the following:

CRTQMQRY QMQRY(&L/&N) SRCFILE(&L/&F) 

Now press Enter. The &L, &N, and &F symbols are substitution parameters and will be replaced with the source file's library name, member name, and source file name respectively. I usually hardcode the QMQRY library name to QTEMP, so that the query will be gone when I sign off.

Here's how the shortcut works. When in the WRKMBRPDM screen (STRPDM option 3), enter CQ next to a member containing an SQL statement, and--presto!--the CRTQMQRY command executes with the proper parameters and you now have a QM query object in your library that can be executed.

Now that you have an executable *QMQRY object, the only step remaining is to run the query. The command to run a *QMQRY object is Start Query Management Query (STRQMQRY). In its simplest form, it has the following syntax:

STRQMQRY QMQRY(OBJLIB/QRYNAME)

Again, you don't want to type this every time so you can make another PDM shortcut. Create a shortcut called SQ with a command parameter of the following:

STRQMQRY QMQRY(&L/&N)  

Again, if you prefer QTEMP, use it instead of the &L library parameter. It is now possible to run the newly created *QMQRY without ever having to leave the WRKMBRPDM screen, by placing an SQ next to the member containing the SQL statement.

In summary, once the setup is completed, here are the steps to quickly running an SQL statement:

1. Create a source member with an SQL statement.
2. Save the source member and exit.
3. Next to the source member, enter the CQ (CRTQMQRY) shortcut to convert it to a *QMQRY object that you can run.
4. Once the *QMQRY is created, enter the SQ (STRQMQRY) shortcut next to the member name to run the query.

If the query fails, consult the job log. You'll find an error message that will explain the cause of the failure.

If you haven't done so already, explore the PDM user options, because they can make life easy. There is more that can be done with this tool, including things like optional prompting of command parameters, which can allow users more control when the shortcuts are invoked.

Further, if the above two-step process is too cumbersome, you can use a CL program instead, to issue the CRTQMQRY and STRQMQRY commands in one step. Additionally, the program can be used to replace an existing *QMQRY without having to respond to CRTQMQRY's obnoxious replacement confirmation prompt. Here's an example:

/*  Program: RUNSQLUTL   */
PGM (&SRCLIB &SRCF &SRCMBR)
 
/* PARAMETERS */ 
DCL &SRCMBR  *CHAR 10 
DCL &SRCF    *CHAR 10 
DCL &SRCLIB  *CHAR 10
 
MONMSG CPF0000 EXEC(GOTO ERROR)
 
/* Create existing QM – if it exists */
DLTQMQRY QTEMP/&SRCMBR 
MONMSG CPF0000

/* Convert source member to QM Query in QTEMP */
CRTQMQRY QTEMP/&SRCMBR SRCFILE(&SRCLIB/&SRCF) REPLACE(*YES)

/* Run QM Query in QTEMP */
STRQMQRY QTEMP/&SRCMBR
GOTO END

ERROR:
DSPJOBLOG
MONMSG CPF0000
 
END:

ENDPGM

This program can be setup as a PDM option called RS (Run SQL), for example. The PDM user option command required to pass the required source library, source file, and member parameters would look like this:

CALL RUNSQLUTL (&L &F &N)

Now the steps to run a query are as follows:

1. Create a source member with an SQL statement.
2. Save the source member and exit.
3. Next to the source member, enter the RS shortcut to convert the source member to a *QMQRY object and execute it.

--Mike

Thanks for the tip, Mike. Occasionally I work on an iSeries system that does not have any type of SQL, other than that which comes with the base support, and it is a major annoyance. RUNSQLSTM is not a lot of help, since it doesn't support the SELECT command.

Anyone who works on the same box on a regular basis would do well to get an SQL product of some sort. In addition to IBM's SQL/400, there are some third-party products. I have used some of them, and they are handy. Also, I'd like to encourage you readers not to forget that some of these products run on PC's. I am using Howard Arner's SQLThing on my day job, and I much prefer it to IBM's interactive SQL for ad hoc queries.

Midrange Guru is not about products, so I avoid talking about them, but SQL is so much a part of my work process, I felt I should mention the usefulness of SQL products.

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

Quoted from an experienced programmer new to the iSeries (AS/400): "The best thing about working on the AS/400 is using ASC's SEQUEL product." If you're tired of the limitations imposed by Query/400 or ODBC-based query and reporting tools, you need SEQUEL. Discover how thousands of sites around the world have improved access to iSeries data using SEQUEL's Windows- and Web-interfaces. It's the one tool you can rely on for virtually all your iSeries data access needs. FREE trial available. Read More about SEQUEL