| Where allowed to run: All environments (*ALL) Threadsafe: No |
Parameters Examples Error messages |
Cozzi's Run SQL using QM Query (RUNQMQRY) command runs most SQL statements using the IBM i QM Query engine, including the SELECT statement. Have you considered using the more powerful SQL iQuery? Learn more at: http://www.coztools.com/iQuery
RUNQMQRY creates a temporary *QMQRY object and then uses the STRQMQRY command to run the SQL statement. Most SQL statements may be specified, including but not limited to:
Restrictions:
| Top |
| Keyword | Description | Choices | Notes |
|---|---|---|---|
| SQL | SQL statement | Character value | Required, Positional 1 |
| OUTPUT | Output | *, *PRINT, *FILE, *OUTFILE | Optional, Positional 2 |
| NAMING | Naming | *SYS, *SQL | Optional, Positional 3 |
| RDB | Remote Relational database | Character value, *LOCAL, *NONE | Optional, Positional 4 |
| USER | Remote Database User ID | Character value, *USER, *USRPRF, *CURRENT | Optional, Positional 5 |
| PWD | Remote Database Password | Character value, *NONE | Optional, Positional 6 |
| OUTFILE | File to receive output | Qualified object name | Optional, Positional 7 |
| Qualifier 1: File to receive output | Name | ||
| Qualifier 2: Library | Name, *CURLIB, *LIBL | ||
| OUTMBR | Output member and option | Element list | Optional, Positional 8 |
| Element 1: Member | Name, *FIRST | ||
| Element 2: Replace or add records | *REPLACE, *ADD |
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Specify any SQL statement that you wish to run. The statement is copied to a source file member in QTEMP and then converted into a *QMQRY object for processing. The maximum SQL statment length supported is 5000 characters.
This is a required parameter.
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Specifies for SELECT statements only, the output device for the result set. By default output is sent to the display for interactive jobs, and to a SPOOL file (print) for a batch job.
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Specifies SQL naming style being used in your SQL statement.
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Specifies the remote system where the tables/files being queried exist. This entry may be *LOCAL or one of the values in the system's remote database directory. Use the WRKRDBDIRE command to view the available remote database choices.
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
When the RDB parameter contains a remote database name, then this parameter must contain the user profile that is used to connect to that remote system. When the RDB parameter is RDB(*LOCAL or *NONE) than this parameter is ignored.
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Specifies password for the user profile being used to connect to the remote database system. This parameter is ignored when the RDB parameter is *LOCAL or *NONE.
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Specifies the name of a database physical file where the output from an SQL SELECT statement is written. For non-SELECT statements, this parameter is ignored.
Qualifier 1: File to receive output
Qualifier 2: Library
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Specifies name of the database member that recieves the result set from the SQL SELECT statement. The ADD/REPLACE option is also specified on this parameter.
Element 1: Member
Element 2: Replace or add records
| Top |
Consider using SQL iQuery - LICPGM(2COZ-IQ3) The better choice for Command Line SQL and Reports. http://www.SQLiQuery.com
Example 1: Simple Command Example
RUNQMQRY SQL('SELECT * FROM QIWS/QCUSTDT') OUTPUT(*PRINT)
Learn more at: http://www.SQLiQuery.com
This example prints a listing of the records in the sample IBM-supplied database table QCUSTDT in the QIWS library.
Example 2: More Complex Command Example
RUNQMQRY SQL('SELECT * FROM QIWS/QCUSTDT') OUTPUT(*FILE)
OUTFILE(MYLIB/CUSTOMERS) OUTMBR(*FIRST *REPLACE)
This command copies the records in the result set to the file named CUSTOMERS in library MYLIB. The first member in the file receives the data, and if any records exist in the file, they are replaced with the data from this SQL statement.
Example 3: More Complex Command Example
RUNQMQRY SQL('UPDATE CUSTOMERS SET LASTORD = CURRENT DATE
WHERE ORDSTATUS = 1')
This command update the CUSTOMERS file by setting the LASTORD (last order date) field to today's date. It does this for all recods that have an ORDSTATUS of 1. NOTE: The RUNQMQRY command does not provide support to control the COMMIT parameter. This is a limitation in the STRQMQRY command itself. As an alternative, consider using SQL iQuery's RUNIQRY command (which does offer the COMMIT parameter) or the IBM-supplied RUNSQL command.
| Top |
*ESCAPE Messages
| Top |