About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
News
Abstract
The RUNSQL command provides a new approach to executing an SQL statement.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > Run SQL (RUNSQL) - new command
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":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
All Versions
Operating system(s):
IBM i
Document number:
1167478
Modified date:
27 March 2025
UID
ibm11167478
Manage My Notification Subscriptions
