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 PROCEDURE
ALTER SEQUENCE
ALTER TABLE
CALL
COMMENT
COMMIT
CREATE ALIAS
CREATE FUNCTION
CREATE INDEX
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 ENCRYPTION PASSWORD
SET PATH
SET SCHEMA
SET TRANSACTION
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. 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.

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)
RUNSQL1: ENDPGM