Using the SQL statement processor
The SQL statement processor allows SQL statements to be run from a source member or a source stream file. The statements in the source can be run repeatedly or changed without compiling the source. This makes the setup of a database environment easier.
The SQL statement processor is available through the Run SQL Statements (RUNSQLSTM) command.
The following statements can be used with the SQL statement processor:
- 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
- LOCK TABLE
- 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
- TRANSFER OWNERSHIP
- TRUNCATE
- UPDATE
In the source, SQL statements do not begin with EXEC SQL. Each statement ends with a semicolon. For a source member, the default right margin is 80. If the record length of the source member is longer than 80, only the first 80 characters are read. You can change the right margin to some other value by using the MARGINS parameter on the RUNSQLSTM command. For a source stream file, the entire file is read; no margin is used.
Comments in the source can be either line comments or block comments. Line comments begin with a double hyphen (--) and end at the end of the line. Block comments start with /* and can continue across many lines until the corresponding */ is reached. Block comments can be nested.
SQL statements, CL commands, and comments are allowed in the source file. A CL command must be prefixed by 'CL:'. For example:
CL: ADDLIBLE MYLIB;
INSERT INTO T1 VALUES('A', 17);The output listing containing the resulting messages for the SQL statements and CL commands is sent to a print file. The default print file is QSYSPRT.
The OPTION parameter lets you choose to get an output listing or to have errors written to the joblog. There is also an option to generate a listing only when errors are encountered during processing.
To perform syntax checking only on all statements in the source, specify the PROCESS(*SYN) parameter on the RUNSQLSTM command. To see more details for error messages in the listing, specify the SECLVLTXT(*YES) parameter.