What is the RUNSQLSTM and how can it be used?

[ Back ]

The RUNSQLSTM command is a CL command that reads and processes SQL statements stored in a source member. The statements in the source member can be run without compiling. This allows static SQL statements or dynamically generated SQL statements to be run without the need for embedding them in a high-level language such as RPG.

The RUNSQLSTM can run a series of SQL statements, but it is limited to a subset of standard SQL statements. That is you can embedded as many SQL statements in a single source member as necessary to get the job done. The only real shortcoming in RUNSQLSTM is the lack of support for the SELECT statement.

Although we would prefer IBM enhance RUNSQLSTM to support the SELECT statement, The RPG Developer Network does offer the SQL Lite™ software package ($295+ $5 shipping, including source code) this package supports most SQL statements, including the interactive SELECT statement.

As mentioned, the RUNSQLSTM command supports a subset of SQL statements, the SQL statements that can be run with this command include the following:

  • ALTER TABLE

  • CALL

  • COMMENT ON

  • COMMIT

  • CREATE ALIAS

  • CREATE COLLECTION

  • CREATE INDEX

  • CREATE PROCEDURE

  • CREATE SCHEMA

  • CREATE TABLE

  • CREATE VIEW

  • DELETE

  • DROP

  • GRANT (Package Privileges)

  • GRANT (Procedure Privileges)

  • GRANT (Table Privileges)

  • INSERT

  • LABEL ON

  • LOCK TABLE

  • RENAME

  • REVOKE (Package Privileges)

  • REVOKE (Procedure Privileges)

  • REVOKE (Table Privileges)

  • ROLLBACK

  • SET TRANSACTION

  • UPDATE

    •

     

    A typical source member, containing SQL statements for use by RUNSQLSTM would be as follows: 

     Source File: Mylib/mySrcFile(mySQLstuff)

    0001   -- First do the update
0002  UPDATE custmast SET credit = 100.00 where credit = 0.00;
0003  CREATE VIEW custcredit AS select custno, credit, slsreg
0004         where credit > 100.00; /* Create Logical View */
    RUNSQLSTM SRCFILE(mylib/mysrcfile) srcmbr(mySQLstuff) COMMIT(*NONE)

    Line 1 is a comment. The – indicates that everything after those two characters is a comment.

    Line 2 is an SQL UPDATE statement. Note that SQL statements must end with a semicolon.

    Line 3 is a CREATE VIEW statement. This creates an SQL view, or "Logical File" on the AS/400. It is continued on to the 4th line.

    Line 4 is a continuation of line 3. Note that there is also a second style of comment on line 4. This is the CL style comment. Line 4 also includes the ending semicolon after the SQL statement.

    Since RUNSQLSTM doesn't use CL style continuation, the semicolon is required to end all SQL statements.

    There is an output listing when RUNSQLSTM runs your SQL statements. It is sent to QSYSPRT unless you specify another print output file the PRTFILE (Print File) parameter.

    [ Back ]