IBM Support

Run SQL (RUNSQL) - new command

News


Abstract

The RUNSQL command provides a new approach to executing an SQL statement.

Content

Command details:

  • Execute a single SQL statement without having to construct a source physical file member or write a program.
  • No spool file is generated. Upon failure, the specific SQL failure message is returned to the command caller.
  • In other SQL interfaces, an SQL statement is limited to 2MB in length. The limit on this command is 5000 bytes.
  • Many parameters are similar to the RUNSQLSTM command.
  • RUNSQL executes SQL statements in the invoker's activation group. If RUNSQL is included in a compiled CL program the activation group of the program is used.
  • While RUNSQLSTM command execution includes an implied COMMIT or ROLLBACK, the RUNSQL command does not. RUNSQL can be used within a application transaction.
  • The SQL Programming Guide in the Knowledge Center contains more information under the "Using the RUNSQL CL command" section.
Example:
RUNSQL SQL(‘INSERT INTO prodLib/work_table VALUES(1, CURRENT TIMESTAMP)')
/* In a CL program, use the Receive File (RCVF) command to read the results of the query */
RUNSQL SQL('CREATE TABLE QTEMP.WorkTable1 AS
(SELECT * FROM qsys2.systables WHERE table_schema = ''QSYS2'') WITH DATA') COMMIT(*NONE) NAMING(*SQL)
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 = ''' CONCAT &LIB CONCAT ''‘ ')
RUNSQL SQL(&SQLSTMT) COMMIT(*NONE) NAMING(*SQL)
ENDSQL1: ENDPGM

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
14 January 2020

UID

ibm11167478

Page Feedback