RUNSQLSTM (Run Structured Query Language Statement) Command Description

RUNSQLSTM Command syntax diagram

 

Purpose

The Run Structured Query Language Statement (RUNSQLSTM) command processes a source file of SQL statements.

Parameters

SRCFILE
Specifies the qualified name of the source file that contains the SQL statements to be run.

The name of the source file can be qualified by one of the following library values:

*LIBL: All libraries in the job's library list 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.

library-name: Specify the name of the library to be searched.

source-file-name: Specify the name of the source file that contains the SQL statements to be run. The source file can be a database file or an inline data file.

SRCMBR
Specifies the name of the source file member that contains the SQL statements to be run.
COMMIT
Specifies whether SQL statements in the source file 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.

*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.

*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.

*NONE or *NC: Specifies that commitment control is not used. Uncommitted changes in other jobs can be seen. If the SQL DROP COLLECTION statement is included in the program, *NONE or *NC must be used. If a relational database is specified on the RDB parameter and the relational database is on a system that is not on an iSeries 400, *NONE or *NC cannot be specified.

*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).

NAMING
Specifies the naming convention used for naming objects in SQL statements.

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

*SQL: The SQL naming convention (collection-name.table-name) is used.

PROCESS
Specifies whether SQL statements in the source file member are executed or syntax-checked only.

*RUN: Statement are syntax-checked and run.

*SYN: Statements are syntax-checked only.

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 COMMIT is *CHG or *CS and ALWBLK is not *ALLREAD, or if COMMIT is *ALL or *RR, then a copy of the data is used only when it is necessary to run a query.

*YES: A copy of the data is used only when necessary.

*NO: A copy of the data is not used. If temporary copy of the data is required to perform the query, an error message is returned.

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 if *NONE or *CHG is specified on the COMMIT parameter. All cursors in a program that are not explicitly able to be updated are opened for read-only processing even though EXECUTE or EXECUTE IMMEDIATE statements may be in the program.

Specifying *ALLREAD:

  • Allows record blocking under commitment control level *CHG in addition to the blocking allowed for *READ.

  • 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), cannot 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 on the COMMIT parameter, which indicates that commitment control is not used.

  • The cursor is declared with a FOR FETCH ONLY clause or there are no dynamic statements that could run a positioned UPDATE or DELETE statement for the cursor.

Specifying *READ can improve the overall performance of queries that meet the above conditions and retrieve a large number of records.

ERRLVL
Specifies whether the processing is successful, based on the severity of the messages generated by the processing of the SQL statements. If errors that are greater than the value specified on this parameter occur during processing, no more statements are processed and the statements are rolled back if they are running under commitment control.

10: Statement processing is stopped when error messages with a severity level greater than 10 are received.

severity-level: Specify the severity level to be used.

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.

DATSEP
Specifies the separator used when accessing date result columns.

Note: This parameter applies only when *JOB, *MDY, *DMY, *YMD, or *JUL is specified on the DATFMT parameter.

*JOB: The date separator specified for the job is used. Use the Display Job (DSPJOB) command to determine the current value for the job.

'/': A slash (/) is used.

'.': A period (.) is used.

',': A comma (,) is used.

'-': A dash (-) is used.

' ': A blank ( ) is used.

*BLANK: A blank ( ) is used.

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 date 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:mm xx 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.

TIMSEP
Specifies the separator used when accessing time result columns.

Note: This parameter applies only when *HMS is specified on the TIMFMT parameter.

*JOB: The time separator specified for the job is used. Use the Display Job (DSPJOB) command to determine the current value for the job.

':': A colon (:) is used.

'.': A period (.) is used.

',': A comma (,) is used.

' ': A blank ( ) is used.

*BLANK: A blank ( ) is used.

DECMPT
Specifies the decimal point value used for numeric constants in SQL statements.

*JOB: The value used as the decimal point for numeric constants in SQL is the representation of decimal point specified 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.

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

*JOB: The LANGID value for the job is retrieved.

*LANGIDSHR: The sort sequence table uses the same weight for multiple characters, and is the shared-weight sort sequence table associated with the language specified on the LANGID parameter.

*LANGIDUNQ: The unique-weight sort table for the language specified on 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.

The name of the table name can be qualified by one of the following library values:

*LIBL: All libraries in the job's library list 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.

library-name: Specify the name of the library to be searched.

table-name: Specify the name of the sort sequence table to be used.

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 during the precompile.

language-identifier: Specify a language identifier.

DFTRDBCOL
Specifies the collection name used for the unqualified names of tables, views, indexes, and SQL packages. This parameter applies only to static SQL statements.

*NONE: The naming convention defined on the OPTION parameter is used.

collection-name: Specify the name of the collection identifier. This value is used instead of the naming convention specified on the OPTION parameter.

FLAGSTD
Specifies the American National Standards Institute (ANSI) flagging function. This parameter flags SQL statements to verify whether they conform to the following standards. 
ANSI X3.135-1992 entry
ISO 9075-1992 entry
FIPS 127.2 entry

*NONE: The SQL statements are not checked to determine whether they conform to ANSI standards.

*ANS: The SQL statements are checked to determine whether they conform to ANSI standards.

SAAFLAG
Specifies the IBM SQL flagging function. This parameter flags SQL statements to verify whether they conform to IBM SQL syntax More information about which IBM database products IBM SQL syntax is in the DRDA IBM SQL Reference, SC26-3255-00.

*NOFLAG: The SQL statements are not checked to determine whether they conform to IBM SQL syntax.

*FLAG: The SQL statements are checked to determine whether they conform to IBM SQL syntax.

PRTFILE
Specifies the qualified name of the printer device file to which the RUNSQLSTM printout is directed. The file must have a minimum length of 132 bytes. If a file with a record length of less than 132 bytes is specified, information is lost.

The name of the printer file can be qualified by one of hte following library values:

*LIBL: All libraries in the job's library list 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.

library-name: Specify the name of the library to be searched.

QSYSPRT: If a file name is not specified, the RUNSQLSTM printout is directed to the IBM-supplied printer file QSYSPRT.

printer-file-name: Specify the name of the printer device file to which the RUNSQLSTM printout is directed.

Parameters for SQL procedures:

The parameters listed below only apply to statements within the source file that create SQL procedures. The parameters are used during the creation of the program object associated with the SQL procedure.

TGTRLS
Specifies the release of the operating system on which the user intends to use the object being created.

In the examples given for the *CURRENT value, and when specifying the release-level value, the format VxRxMx is used to specify the release, where Vx is the version, Rx is the release, and Mx is the modification level. For example, V2R3M0 is version 2, release 3, modification level 0.

*CURRENT The object is to be used on the release of the operating system currently running on the user's system. For example, if V2R3M5 is running on the system, *CURRENT means the user intends to use the object on a system with V2R3M5 installed. The user can also use the object on a system with any subsequent release of the operating system installed.

Note: If V2R3M5 is running on the system, and the object is to be used on a system with V2R3M0 installed, specify TGTRLS(V2R3M0) not TGRRLS(*CURRENT).

release-level: Specify the release in the format VxRxMx. The object can be used on a system with the specified release or with any subsequent release of the operating system installed.

Valid values depend on the current version, release, and modification level, and they change with each new release. If you specify a release-level which is earlier than the earliest release level supported by this command, an error message is sent indicating the earliest supported release.

CLOSQLCSR
Specifies when SQL cursors are implicitly closed, SQL prepared statements are implicitly discarded, and LOCK TABLE locks are released. SQL cursors are explicitly closed when you issue the CLOSE, COMMIT, or ROLLBACK (without HOLD) SQL statements.

*ENDACTGRP: SQL cursors are closed and SQL prepared statements are implicitly discarded.

ENDMOD: SQL cursors are closed and SQL prepared statements are implicitly discarded when the module is exited. LOCK TABLE locks are released when the first SQL program on the call stack ends.

OUTPUT
Specifies whether the precompiler listing is generated.

*NONE: The precompiler listing is not generated.

*PRINT: The precompiler listing is generated.

DBGVIEW
Specifies the type of source debug information to be provided by the SQL precompiler.

*NONE: The source view will not be generated.

*STMT: Allows the compiled module to be debugged using program statement numbers and symbolic identifiers.

*LIST: Generates the listing view for debugging the compiled module object.

USRPRF
Specifies the user profile that is used when the compiled program object is run, including the authority that the program object has for each object in static SQL statements. The profile of either the program owner or the program user is used to control which objects can be used by the program object.

*NAMING: The user profile is determined by the naming convention. If the naming convention is *SQL, USRPRF(*OWNER) is used. If the naming convention is *SYS, USRPRF(*USER) is used.

*USER: The profile of the user running the program object is used.

*OWNER: The user profiles of both the program owner and the program user are used when the program is run.

DYNUSRPRF
Specifies the user profile to be used for dynamic SQL statements.

*USER: For local, dynamic SQL statements run under the user of the program's user. For distributed, dynamic SQL statements run under the profile of the SQL package's user.

*OWNER: For local, dynamic SQL statements run under the profile of the program's owner. For distributed, dynamic SQL statements run under the profile of the SQL package's owner.

DLYPRP
Specifies whether the dynamic statement validation for a PREPARE statement is delayed until an OPEN, EXECUTE, or DESCRIBE statement is run. Delaying validation improves performance by eliminating redundant validation.

*NO: Dynamic statement validation is not delayed. When the dynamic statement is prepared, the access plan is validated. When the dynamic statement is used in an OPEN or EXECUTE statement, the access plan is revalidated. Because the authority or the existence of objects referred to by the dynamic statement may change, still check the SQLCODE or SQLSTATE after issuing the OPEN or EXECUTE statement to ensure that the dynamic statement is still valid.

*YES: Dynamic statement validation is delayed until the dynamic statement is used in an OPEN, EXECUTE, or DESCRIBE SQL statement. When the dynamic statement is used, the validation is completed and an access plan is built. If you specify *YES on this parameter, check the SQLCODE and SQLSTATE after running an OPEN, EXECUTE, or DESCRIBE statement to ensure that the dynamic statement is valid.

Note: If you specify *YES, performance is not improved if the INTO clause is used on the PREPARE statement or if a DESCRIBE statement uses the dynamic statement before an OPEN is issued for the statement.

Example for RUNSQLSTM 

RUNSQLSTM  SRCFILE(MYLIB/MYFILE)  SRCMBR(MYMBR)

This command processes the SQL statements in member MYMBR found in file MYFILE in library MYLIB.

Error messages for RUNSQLSTM

*ESCAPE Messages

SQL9006
DB2 Query Manager and SQL Development Kit for AS/400 not at same install level as OS/400.
SQL9010
RUNSQLSTM command failed.
SQL9014
Remote Connection Active