Run SQL (RUNSQL)

Where allowed to run: All environments (*ALL)
Threadsafe: No

The Run SQL (RUNSQL) command will run the Structured Query Language (SQL) statement specified for the SQL (SQL) parameter.

The SQL statement will be parsed when the command is run. Syntax errors will not be identified until the command is run.

RUNSQL will run SQL statements in the caller's activation group. If RUNSQL is run in a compiled CL program or ILE CL procedure, the activation group of the program or procedure is used.

Restrictions:

In other SQL interfaces, an SQL statement is limited to 2 megabytes in length. The limit on this command is 5000 bytes, which is the maximum for a CL command *CHAR parameter.
Unlike the RUNSQLSTM (Run SQL Statements) command, the RUNSQL command does not produce a spooled file. When an error occurs during command execution, the corresponding SQL failure message is sent as an escape message to the caller. For a complex SQL statement that returns a syntax error, the easiest way to find the cause of the syntax error is to start a database monitor, run the RUNSQL command, and then analyze the database monitor using System i Navigator. This is especially true when the SQL statement is built as an expression in a control language (CL) program or procedure.
Unlike the RUNSQLSTM command which implicitly commits or rolls back the statements when it ends and commitment control is used, the RUNSQL command will not implicitly perform a commit or rollback; that is left up to the user's application. This is similar to how embedded SQL statements work in other programming languages, such as RPG or COBOL.
The RUNSQL command cannot be used to open a database file and leave it open. Any file opened by RUNSQL is closed before control is returned.

Top

Parameters

Keyword	Description	Choices	Notes
SQL	SQL	Character value	Required, Positional 1
COMMIT	Commitment control	CHG, UR, CS, ALL, RS, NONE, NC, RR	Optional, Positional 2
NAMING	Naming	SYS, SQL	Optional, Positional 3
DATFMT	Date format	JOB, USA, ISO, EUR, JIS, MDY, DMY, YMD, *JUL	Optional
DATSEP	Date separator character	JOB, '/', '.', ',', '-', ' ', BLANK	Optional
TIMFMT	Time format	HMS, USA, ISO, EUR, *JIS	Optional
TIMSEP	Time separator character	JOB, ':', '.', ',', ' ', BLANK	Optional
DFTRDBCOL	Default collection	Name, *NONE	Optional
DECMPT	Decimal point	JOB, SYSVAL, PERIOD, COMMA	Optional
SRTSEQ	Sort sequence	Single values: JOB, LANGIDUNQ, LANGIDSHR, HEX Other values: Qualified object name	Optional
	Qualifier 1: Sort sequence	Name
	Qualifier 2: Library	Name, LIBL, CURLIB
LANGID	Language id	Character value, *JOB	Optional
ALWCPYDTA	Allow copy of data	OPTIMIZE, YES, *NO	Optional
ALWBLK	Allow blocking	ALLREAD, NONE, *READ	Optional
SQLCURRULE	SQL rules	DB2, STD	Optional
DECRESULT	Decimal result options	Element list	Optional
	Element 1: Maximum precision	31, 63
	Element 2: Maximum scale	0-63, 31
	Element 3: Minimum divide scale	0-9, 0
CONACC	Concurrent access resolution	DFT, CURCMT, *WAIT	Optional

Top

SQL (SQL)

Specifies a single Structured Query Language (SQL) statement to be run.

This is a required parameter.

character-value: Specify the SQL statement to be run. The maximum statement length is 5000 bytes.

Top

Commitment control (COMMIT)

Specifies whether SQL statements are run under commitment control.

*CHG or *UR: Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows updated, deleted, and inserted are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs can be seen.
*CS: Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows updated, deleted, and inserted are locked until the end of the unit of work (transaction). A row that is selected, but not updated, is locked until the next row is selected. Uncommitted changes in other jobs cannot be seen.
*ALL or *RS: Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows selected, updated, deleted, and inserted are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs cannot be seen.
*NONE or *NC: Specifies that commitment control is not used. Uncommitted changes in other jobs can be seen. If the SQL DROP SCHEMA statement is included in the program, *NONE or *NC must be used.
*RR: Specifies the objects referred to in SQL ALTER, CALL, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, RENAME, and REVOKE statements and the rows selected, updated, deleted, and inserted are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs cannot be seen. All tables referred to in SELECT, UPDATE, DELETE, and INSERT statements are locked exclusively until the end of the unit of work (transaction).

Top

Naming convention (NAMING)

Specifies the naming convention used for objects in SQL statements.

*SYS: The system naming convention (library-name/file-name) is used.
*SQL: The SQL naming convention (schema-name.table-name) is used.

Top

Date format (DATFMT)

Specifies the format used when accessing date result columns. For input date strings, the specified value is used to determine whether the date is specified in a valid format.

Note: An input date string that uses the format *USA, *ISO, *EUR, or *JIS is always valid.

*JOB: The format specified for the job is used. Use the Display Job (DSPJOB) command to determine the current date format for the job.
*USA: The United States date format mm/dd/yyyy is used.
*ISO: The International Organization for Standardization (ISO) date format yyyy-mm-dd is used.
*EUR: The European date format dd.mm.yyyy is used.
*JIS: The Japanese Industrial Standard date format yyyy-mm-dd is used.
*MDY: The date format mm/dd/yy is used.
*DMY: The date format dd/mm/yy is used.
*YMD: The date format yy/mm/dd is used.
*JUL: The Julian date format yy/ddd is used.

Top

Date separator character (DATSEP)

Specifies the separator used when accessing date result columns.

Note: This parameter applies only when *JOB, *MDY, *DMY, *YMD, or *JUL is specified for the Date format (DATFMT) parameter.

*JOB: The date separator specified for the job at precompile time, when a new interactive SQL session is created, or when RUNSQLSTM is run is used.
Use the Display Job (DSPJOB) command to determine the current date separator value for the job.
'/': A slash is used as the date separator.
'.': A period is used as the date separator.
'-': A dash is used as the date separator.
',': A comma is used as the date separator.
' ' or *BLANK: A blank is used as the date separator.

Top

Time format (TIMFMT)

Specifies the format used when accessing time result columns. For input time strings, the specified value is used to determine whether the time is specified in a valid format.

Note: An input time string that uses the format *USA, *ISO, *EUR, or *JIS is always valid.

*HMS: The hh:mm:ss format is used.
*USA: The United States time format hh:mmxx is used, where xx is AM or PM.
*ISO: The International Organization for Standardization (ISO) time format hh.mm.ss is used.
*EUR: The European time format hh.mm.ss is used.
*JIS: The Japanese Industrial Standard time format hh:mm:ss is used.

Top

Time separator character (TIMSEP)

Specifies the separator used when accessing time result columns.

Note: This parameter applies only when *HMS is specified for the Time format (TIMFMT) parameter.

*JOB: The time separator specified for the job at precompile time, when a new interactive SQL session is created, or when RUNSQLSTM is run is used.
Use the Display Job (DSPJOB) command to determine the current time separator value for the job.
':': A colon is used as the time separator.
'.': A period is used as the time separator.
',': A comma is used as the time separator.
' ' or *BLANK: A blank is used as the time separator.

Top

Default collection (DFTRDBCOL)

Specifies the name of the schema identifier used for the unqualified names of the tables, views, indexes, SQL packages, aliases, constraints, external programs, node groups, and triggers. This parameter applies only to static SQL statements.

*NONE: The naming convention specified for the Naming convention (NAMING) parameter is used.
name: Specify the name of the schema identifier to be used instead of the naming convention specified for the NAMING parameter.

Top

Decimal point (DECMPT)

Specifies the decimal point value used for numeric constants in SQL statements. This value is also used as the decimal point character when casting between character and numeric values.

*JOB: The representation for the decimal point is the value used by the job running the statement.
*SYSVAL: The QDECFMT system value is used as the decimal point.
*PERIOD: A period represents the decimal point.
*COMMA: A comma represents the decimal point.

Top

Sort sequence (SRTSEQ)

Specifies the sort sequence table to be used for string comparisons in SQL statements.

Single values

*JOB: The SRTSEQ value for the job is used.
*LANGIDUNQ: The unique-weight sort table for the language specified for the Language id (LANGID) parameter is used.
*LANGIDSHR: The shared-weight sort table for the language specified for the LANGID parameter is used.
*HEX: A sort sequence table is not used. The hexadecimal values of the characters are used to determine the sort sequence.

Qualifier 1: Sort sequence

name: Specify the name of the sort sequence table to be used with this program.

Qualifier 2: Library

*LIBL: All libraries in the library list for the current thread are searched until the first match is found.

*CURLIB: The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.
name: Specify the name of the library to be searched.

Top

Language id (LANGID)

Specifies the language identifier to be used when SRTSEQ(*LANGIDUNQ) or SRTSEQ(*LANGIDSHR) is specified.

*JOB: The LANGID value for the job is retrieved.
language-identifier: Specify a language identifier.

Top

Allow copy of data (ALWCPYDTA)

Specifies whether a copy of the data can be used in a SELECT statement.

*OPTIMIZE: The system determines whether to use the data retrieved directly from the database or to use a copy of the data. The decision is based on which method provides the best performance. If the Commitment control (COMMIT) parameter is not *NONE, the Allow blocking (ALWBLK) parameter should be set to *ALLREAD, when possible, for best performance.
*YES: A copy of the data is used only when necessary.
*NO: A copy of the data is not used. If a temporary copy of the data is required to perform the query, an error message is returned.

Top

Allow blocking (ALWBLK)

Specifies whether the database manager can use record blocking and the extent to which blocking can be used for read-only cursors.

*ALLREAD

Rows are blocked for read-only cursors. All cursors in a program that are not explicitly able to be changed are opened for read-only processing even though there may be EXECUTE or EXECUTE IMMEDIATE statements in the program.

Specifying *ALLREAD:

Allows record blocking for all read-only cursors.
Can improve the performance of almost all read-only cursors in programs, but limits queries in the following ways:
- The Rollback (ROLLBACK) command, a ROLLBACK statement in host languages, or the ROLLBACK HOLD SQL statement does not reposition a read-only cursor when *ALLREAD is specified.
- Dynamic running of a positioned UPDATE or DELETE statement (for example, using EXECUTE IMMEDIATE), can not be used to update a row in a cursor unless the DECLARE statement for the cursor includes the FOR UPDATE clause.

*NONE

Rows are not blocked for retrieval of data for cursors.

Specifying *NONE:

Guarantees that the data retrieved is current.
May reduce the amount of time required to retrieve the first row of data for a query.
Stops the database manager from retrieving a block of data rows that is not used by the program when only the first few rows of a query are retrieved before the query is closed.
Can degrade the overall performance of a query that retrieves a large number of rows.

*READ

Records are blocked for read-only retrieval of data for cursors when:

*NONE is specified for the Commitment control (COMMIT) parameter, which indicates that commitment control is not used.
The cursor is declared with a FOR READ ONLY clause or there are no dynamic statements that could run a positioned UPDATE or DELETE statement for the cursor.

Top

SQL rules (SQLCURRULE)

Specifies the semantics used for SQL statements.

*DB2: The semantics of all SQL statements will default to the rules established for DB2. The following semantics are controlled by this option:
Hexadecimal constants are treated as character data.
*STD: The semantics of all SQL statements will default to the rules established by the ISO and ANSI SQL standards. The following semantics are controlled by this option:
Hexadecimal constants are treated as binary data.

Top

Decimal result options (DECRESULT)

Specifies the maximum precision, maximum scale, and minimum divide scale that should be used during decimal operations, such as decimal arithmetic. The specified limits only apply to NUMERIC and DECIMAL data types.

Element 1: Maximum precision

31: The maximum precision (length) that should be returned from decimal operations is 31 digits.
63: The maximum precision (length) that should be returned from decimal operations is 63 digits.

Element 2: Maximum scale

31: The maximum scale (number of decimal positions to the right of the decimal point) that should be returned from decimal operations is 31 digits.
0-63: Specify the maximum scale (number of decimal positions to the right of the decimal point) that should be returned from decimal operations. The value can range from 0 to the maximum precision.

Element 3: Minimum divide scale

0: The minimum divide scale is not used.
0-9: Specify the minimum divide scale (number of decimal positions to the right of the decimal point) that should be returned from decimal operations. The value cannot exceed the maximum scale. If 0 is specified for the maximum scale, minimum divide scale is not used.

Top

Concurrent access resolution (CONACC)

Specifies how the database manager should handle record lock conflicts for data in the process of being updated.

*DFT: Specifies that the concurrent access option will not be explicitly set for this program. The value that is in effect when the program is invoked will be used. The value can be set using the SQL_CONCURRENT_ACCESS_RESOLUTION option in the query options file QAQQINI.
*CURCMT: Directs the database manager to use the currently committed data when possible in the case of record lock conflicts for read-only queries. This only applies when the commit level is *CS.
*WAIT: Directs the database manager to wait for the outcome in the case of record lock conflicts.

Top

Examples

Example 1: Insert a Row into a Table

RUNSQL   SQL('insert into testlib/t1 values (1)')

This command will insert one row into file T1 in library TESTLIB.

Example 2: Run a Query and Store the Results in a Temporary Table

RUNSQL   SQL('CREATE TABLE qtemp.t1 AS
              (SELECT * FROM qsys2.systables
               WHERE table_schema = ''TESTLIB'') WITH DATA')
         COMMIT(*NONE) NAMING(*SQL)

This command runs a query and stores the results in a temporary table. The table would be file T1 in library QTEMP. If the command is run in a CL program or procedure, you can use the Receive File (RCVF) command to read the results of the query.

Example 3: Using an Expression for the SQL Statement

RUNSQL1: PGM  PARM(&LIB)
          DCL  &LIB TYPE(*CHAR) LEN(10)
          DCL  &SQLSTMT TYPE(*CHAR) LEN(1000)
          CHGVAR  VAR(&SQLSTMT) +
                  VALUE('DECLARE GLOBAL TEMPORARY TABLE result +
                       AS (SELECT * FROM qsys2.systables WHERE +
                       table_schema = ''' || &LIB || ''') with +
                       data WITH REPLACE NOT LOGGED')
          RUNSQL  SQL(&SQLSTMT) COMMIT(*NONE) NAMING(*SQL)
ENDSQL1: ENDPGM

This example shows how the RUNSQL command can be used in a CL program or procedure. The SQL parameter is built as a CL character expression concatenating literal text with the value of CL character variable &LIB.

Top

Error messages

*ESCAPE Messages

SQLxxxx: Any SQL failure with four digits, such as SQL0204.
SQ2xxxx: Any SQL failure from 20000 to 29999, such as SQ20180.
SQ3xxxx: Any SQL failure from 30000 to 39999, such as SQ30106.

Top