In the last issue, I showed you how to use command RunSQLStm (Run SQL Statements) to execute SQL statements from a CL program. In this issue, I show you how you can use query management queries (QMQRY) to execute SQL statements from a CL program. Keep in mind that I scratch only the surface with respect to query management queries. For example, I don't discuss query management forms that describe your output format. You can find additional information on query management queries at

http://publib.boulder.ibm.com/pubs/html/as400/v5r1/ic2924/info/qmp/rbaromst.pdf.

Query management queries are objects compiled from source, so the first thing you need to do is create a source physical file where you can store the SQL statements. The default source file name chosen by IBM is QQMQrySrc. For most cases, source files allow for 80 bytes of source data. However, QMQry expects source data to be 79 bytes or less in length. Therefore, instead of accepting the default record length of 92 (80 for source data, 6 for source date, and 6 for sequence number) when you create the source file, you must specify a record length of 91 as follows:

CrtSrcPF YourLib/QQMQrySrc RcdLen(91)

Now that you have a source file, let's work with a simple QMQry example.

For this tip and the one that follows, we'll use a file created from the following DDS:

      *    =================================================================
      *    = Physical file... Employees                                    =
      *    = Description..... Sample employee master file                  =
      *    =================================================================

     A                                      UNIQUE

     A          R EMPLOYEESR

     A            EMPLOYEEID     5
     A            LASTNAME      20
     A            FIRSTNAME     15
     A            LOCATION      10
     A            DEPARTMENT    10

     A          K EMPLOYEEID

Now, let's create a QMQry that displays those employees working in the IT department at the Dallas location. Enter the following source statements into member Employees1 in your QMQry source file (use TXT for source type):

--  *    ===================================================================
--  *    = QMQry......... Employees1                                       =
--  *    = Description... Sample QMQry                                     =
--  *    ===================================================================

Select * 
  From YourLib/Employees
  Where Location = 'DALLAS'
    and Department = "IT"

To compile this QMQry, issue the following command:

CrtQMQry  QMQry(YourLib/Employees1) SrcFile(YourLib/QQMQrySrc)

Finally, run the query and view your output with command:

 StrQMQry  QMQry(YourLib/Employees1)

To execute the query from within a CL program, simply issue the StrQMQry command in the program. (Of course, your SQL statement will likely be something other than Select, such as Delete or Update in the case where you're executing the SQL from a CL program.)