Using the RUNSQL CL command

The RUNSQL CL command allows an SQL statement to be run from within a CL program without needing a source file.

The following statements can be used by RUNSQL:

  • ALTER FUNCTION
  • ALTER MASK
  • ALTER PERMISSION
  • ALTER PROCEDURE
  • ALTER SEQUENCE
  • ALTER TABLE
  • ALTER TRIGGER
  • CALL
  • COMMENT
  • COMMIT
  • compound (dynamic)
  • CREATE ALIAS
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE MASK
  • CREATE PERMISSION
  • CREATE PROCEDURE
  • CREATE SCHEMA
  • CREATE SEQUENCE
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE TYPE
  • CREATE VARIABLE
  • CREATE VIEW
  • DECLARE GLOBAL TEMPORARY TABLE
  • DELETE
  • DROP
  • GRANT
  • INSERT
  • LABEL
  • MERGE
  • REFRESH TABLE
  • RELEASE SAVEPOINT
  • RENAME
  • REVOKE
  • ROLLBACK
  • SAVEPOINT
  • SET CURRENT DECFLOAT ROUNDING MODE
  • SET CURRENT DEGREE
  • SET CURRENT IMPLICIT XMLPARSE OPTION
  • SET CURRENT TEMPORAL SYSTEM_TIME
  • SET ENCRYPTION PASSWORD
  • SET PATH
  • SET SCHEMA
  • SET TRANSACTION
  • TRANSFER OWNERSHIP
  • TRUNCATE
  • UPDATE

The statement string can be up to 5000 characters long. It must not end with a semicolon.

Comments are allowed in the statement string. A line comment begins with a double hyphen (--) and ends at the end of the line (a carriage return and/or line feed) or at the end of the string. Block comments start with /* and continue until the corresponding */ is reached. Block comments can be nested.

If any file is opened by RUNSQL, it is closed before control is returned to the caller. If commitment control is active, it is up to the user’s application to perform the commit or rollback.

The command runs in the invoker’s activation group. If RUNSQL is included in a compiled CL program, the activation group of the program is used.

No output listing is generated by default. If a failure occurs, the SQL message is sent as an escape message to the caller. For a complex SQL statement that returns a syntax error, you can use the database monitor to help find the cause of the error. Start a database monitor, run the RUNSQL command, and analyze the database monitor using System i Navigator. You can use the OPTION, PRTFILE, and SECLVLTXT parameters on the command to generate a listing.

Run an INSERT statement from CL:
RUNSQL SQL('INSERT INTO prodLib/work_table VALUES(1, CURRENT TIMESTAMP)')
In a CL program, you could use the Receive File (RCVF) command to read the results of the table generated for this query:
RUNSQL SQL('CREATE TABLE qtemp.worktable AS
  (SELECT * FROM qsys2.systables WHERE table_schema = ''MYSCHEMA'') WITH DATA')
       COMMIT(*NONE) NAMING(*SQL)
Create a CL program that constructs and runs an SQL statement using an input parameter as part of the statement:
RUNSQL1: PGM PARM(&LIB)
    DCL &LIB TYPE(*CHAR) LEN(10)
    DCL &SQLSTMT TYPE(*CHAR) LEN(1000)
    CHGVAR VAR(&SQLSTMT) +
           VALUE('DELETE FROM qtemp.worktable1 +
                  WHERE table_schema = '' || &LIB || '' ')
    RUNSQL SQL(&SQLSTMT) COMMIT(*NONE) NAMING(*SQL)
  ENDPGM