Where allowed to run: All environments (*ALL) Threadsafe: No |
Parameters Examples Error messages |
The COZZI - Run iQuery (RUNIQRYF) command runs an ad hoc SQL SELECT statement over the specified file. The default "SELECT * FROM dbfile" is run when only a file name is specified with no other parameter options. Additional parameters allow field selection, select/omit and sequencing of the output.
The output from RUNIQRYF can be directed to a growing number of output devices including Display and Print but others are available.
The RUNIQRYF command is a basic query tool that utilizes the SQL CLI API layer found in most operating systems, including IBM i.
Top |
Keyword | Description | Choices | Notes |
---|---|---|---|
FILE | Database file to query | Single values: *ACTJOB, *ALLOBJAUT, *ALLUSR, *DB2PTF, *DSK, *GRPPRF, *IBMBLOG, *JOBLOG, *JOBSCD, *JOBSCDE, *LIBL, *OBJ, *PTFBKPLVL, *PTFGRP, *RCDLCK, *SPCVAL, *SYS, *SYSVAL, *TCPIP, *TOPHOGS, *USRGRP, *USRPRF, *COZXREF, *COZFUNC Other values: Element list |
Required, Positional 1 |
Element 1: Table | Qualified object name | ||
Qualifier 1: Table | Name | ||
Qualifier 2: Library | Name, *CURLIB, *LIBL, *USER, *USRPRF | ||
Element 2: Remote database 3-level name | Name, *LOCAL | ||
MBR | Member name | Name, *FIRST, *LAST, *FILE | Optional, Positional 2 |
OUTPUT | Output | *, *PRINT, *FILE, *PDF, *TEXT, *CSV, *STMF, *XML, *HTML, *EXCEL, *XLSX, *JSON, *SYLK, *SLK, *RPG, *LIST, *OUTFILE, *RPGIO, *RPGI, *RPGO, *RPGDS, *RPGDSO, *SQL, *SQLDCL, *SQLEXEC, *SQLSRC, *SQLSCRIPT, *SQLIMMED, *DISPLAY | Optional, Positional 3 |
FLD | Select fields to include | Single values: *COUNT, *COUNTER Other values (up to 300 repetitions): Name, *ALL, *ROWID, *RRN, *DATE, *USER, *USRPRF |
Optional, Positional 5 |
OMITFLD | Select fields to omit | Single values: *NONE Other values (up to 300 repetitions): Generic name, name |
Optional, Positional 6 |
WHERE | WHERE clause (SQL syntax) | Character value, *NONE | Optional, Positional 7 |
ORDERBY | ORDER BY clause (SQL syntax) | Character value, *NONE | Optional, Positional 8 |
RDB | Remote database name | Character value, *LOCAL | Optional, Positional 10 |
USER | Remote user ID | Character value, *NONE, *USER, *USRPRF, *CURRENT | Optional, Positional 11 |
PWD | Remote password | Character value, *NONE | Optional, Positional 12 |
SPLFNAME | SPOOL file name | Name, *FILE, *DFT | Optional, Positional 13 |
OUTFILE | Database output file | Qualified object name | Optional, Positional 17 |
Qualifier 1: Database output file | Name, QSQLSELECT | ||
Qualifier 2: Library | Name, *CURLIB | ||
OUTMBR | Database output member | Name, *FIRST, *FILE | Optional, Positional 18 |
MBROPT | Output file data option | *ADD | Optional, Positional 19 |
STMF | IFS Folder or full stmf name | Path name, *HOME | Optional, Positional 20 |
STMFNAME | IFS stmf name or *STMF | Path name, *STMF, *FILE, *SPLFNAME | Optional, Positional 21 |
STMFOPT | Output stmf data option | *REPLACE, *ADD | Optional, Positional 22 |
STMFDIROPT | Output stmf folder option | *CREATE, *NONE | Optional, Positional 23 |
STMFCCSID | Output stmf CCSID | 1-65535, *PCASCII, *WINDOWS, *APPLE, *BINARY, *JOB, *NONE, *UTF8, *UTF16 | Optional, Positional 24 |
STMFHDR | Insert Content-Type Header | *YES, *NO | Optional, Positional 25 |
CSVOPT | CSV Options | Element list | Optional, Positional 26 |
Element 1: Headings | *NONE, *FIELDS, *COLHDG, *FLDNAME, *NOHDG, 0, 1, 2 | ||
Element 2: Trim blanks | *NONE, *RIGHT, *LEFT, *BOTH, 0, 1, 2, 3 | ||
Element 3: Quote symbol | Character value, *QUOTE, *APOS, *BAR, *NONE | ||
Element 4: Escape symbol | Character value, *STRDLM, *DBL, *ESC, *TRIM | ||
Element 5: Separator symbol | Character value, *COMMA, *TAB, *BAR | ||
Element 6: End of line symbol(s) | Character value, *LF, *CR, *CRLF, *LFCR | ||
Element 7: Null value symbol | Character value, *NONE, *DASH, *PLUS, *PERIOD | ||
Element 8: CSV data prefix | Character value, *NONE, *EQUALS | ||
AS | Correlation name (file alias) | Name, *NONE, *QRYF | Optional, Positional 4 |
LOG | Log options | *SQL, *LVL1, *LVL2, *LVL3, *LVL4, *LVL5, *LVL0, *NONE | Optional, Positional 9 |
COMMIT | Commitment control option | *CHG, *UR, *CS, *ALL, *AUTO, *RS, *NONE, *NC, *RR | Optional, Positional 14 |
NAMING | Naming (*SYS or *SQL) | *SYS, *SQL | Optional, Positional 15 |
OPTIMIZE | Optimize for... rows | 1-9999999, 100, *ALL, *NONE | Optional, Positional 16 |
Top |
Specifies the database file to be queried. If the file is not qualified, the library list is searched for the database file. The maximum length of a file name is 128 characters and is converted to a 10-character system object name automatically when a long name is specified.
This is a required parameter.
Qualifier 1: Database file to query
Qualifier 2: Library
Top |
Specifies the member in the database file of the FILE parameter that is used for the query. Since SQL does not directly support database members, RUNIQRYF uses an OVRDBF command to redirect the RUNIQRYF SQL processor so that it accesses the member name specified.
Top |
Specifies where the queried output is sent. If the OUTPUT is to the display or print, the file, library and member name along with the system name on which the output is generated are included in the heading of the output.
Top |
Specify a list of field names to include in the output. Field names may be up to 18 characters in length. The special values *ALL, *ROWID and *RRN may be specified to include all fields and/or the row sequence number or relative record number.
You can specify 300 values for this parameter.
Top |
Specifies a list of fields to omit from the queried output. Use this parameter when you want to "include all but a few" fields.
Single values
Other values (up to 300 repetitions)
Top |
Specifies the select and omit conditions for records returned by RUNIQRYF. This parameter accepts a standard SQL "WHERE" clause.
Top |
Specifies the sort sequence for the queried output. Specify a standard SQL ORDER BY clause, but do not include the words "ORDER BY" in the parameter as they are automatically inserted. The classic "ORDER BY" clause is a list of field names, separated by commas. For example: ORDERBY('country,zipcode desc,company') In this example, the output is sorted by country, then in descending order by zip code, and then by company name.
Top |
Specifies the remote or local database to connect to for the query. Files stored on the system or partition identified on this parameter are queried by the SQL iQuery RUNIQRYF command. When RDB is not RDB(*LOCAL) the USER and PWD parameters must be speciied. The data source must already be defined on the system for the connect function to work. On the IBM i platform, you can use the Work with Relational Database Directory Entries to determine which data sources have been defined, and to optionally define additional data sources. If the application does not supply a target database the local database is used.
When you are using DB2 for i CLI on a newer release, SQLConnect() can encounter an SQL0144 message. This indicates that the data source (the server) has obsolete SQL packages that must be deleted. To delete these packages, run the following command on the data source: DLTSQLPKG SQLPKG(QGPL/QSQCLI*) In typical use, RDB(*LOCAL) (the default) is what should be specified. However, SQL iQuery can connect to and query database files on remote systems or in other partitions. Use the RDB parameter to identify those remote systems/partitions. Any other entry besides *LOCAL, must exists in the Remote Database Directory. Use the WRKRDBDIRE command to view and manage your remote database entries.
Top |
Specifies the user ID of the remote system specified on the RDB parameter. This parameter is ignored when RDB(*LOCAL) is specified and is required when RDB is something other than *LOCAL.
Top |
Specifies the password for the User ID specified on the USER parameter. This parameter is ignored when RDB(*LOCAL) is specified and is required when RDB is something other than *LOCAL.
Top |
Specifies name of the SPOOL file when OUTPUT(*PRINT *PDF or *TEXT) is specified.
Top |
Specifies name of a new or existing DB2 for i database file that receives the rowset data from an SQL SELECT statement. This parameter is only valid with an SQL SELECT statement or a CTE.
Qualifier 1: Output file
Qualifier 2: Library
Specify the library name where the output file is located or will be located (if it is being created).
Top |
Specifies an output member for the OUTFILE. SQL largely ignores members in database tables. This parameter is reserved for future use.
Top |
Specifies whether to replace or add records in the output file member.
Top |
This parameter is used when OUTPUT(*PDF | *TEXT | *CSV) is specified. Otherwise it is ignored. Specify either a path name of the folder where the output is to be located or the fully qualified path and file name to be created. If no file name is specified, specify the file name on the STMFNAME parameter.
Top |
Specify the stream file or PDF file name to be created within the folder identified on the STMF parameter. If the STMF parameter includes the file name, this parameter must be STMFNAME(*STMF).
Top |
Specifies whether to clear or add to an existing stream file.
Top |
Specifies whether to create the folders that contain the stream file. SQL iQuery creates the entire folder directory tree for you when STMFDIROPT(*CREATE) is specified.
Top |
Specifies CCSID used when creating and translating the data being written to the IFS file specified on the STMF and STMFNAME parameters.
Top |
Top |
Specifies options to control how comma separated files are created.
Element 1: Headings
Note that column headings are not enclosed in quotes.
Element 2: Trim blanks
Specifies whether or not trailing and leading blanks are removed.
Element 3: Quote symbol
Specifies the symbol (character) used to enclose non-numeric fields.
Element 4: Escape symbol
Specifies the symbol inserted before the Quote symbol when that quote symbol is detected within the data.
Element 5: Separator symbol
Element 6: Null value symbol
Element 7: End of line symbol(s)
Top |
Specifies an alias used to reference the primary file in the SQL statement. The primary file on RUNIQRYF is the file name specified on the FILE parameter. Correlation names may be up to 32 characters in length. To extend the AS parameter on the Prompted RUNIQRYF command, type in the (&) ampersand symbol followed by a blank and press Enter, the prompt input field shall be increased. NOTE: SQL iQuery uses this value only when the field (FLD) parameter contains *ROWID or *RRN. User may reference this value on the WHERE and ORDER BY parameters.
Top |
Specifies whether to write the SQL statement to the joblog. and/or write the subsequent RUNIQRYF command to the joblog.
Top |
Specifies the automatic commitment control for non-SELECT statements. This parameter is ignored if the SQL parameter is a SELECT statement.
Top |
Select whether the SQL naming convention or the IBM i object naming convention is specified for the SQL statement specified on the SQL parameter.
On recent versions and technology refreshes of IBM i, we have noticed that regardles of the setting for this parameter, either *SQL or *SYS syntax may be used on the SQL statement. But both may not be used concurrently. This may indicate that the setting for this parameter is being ignored on later releases of IBM i.
Top |
Specifies the number of records to insert into the "OPTIMIZE FOR xx ROWS" clause of the generated SELECT statement.
Top |
Example 1: Simple RUNIQRYF Example
RUNIQRYF FILE(CUSTMAST)
This command queries the CUSTMAST file (searching the library list for the file) and outputs the results to the interactive Display.
Example 2: More Complex Command Example
RUNIQRYF FILE(CUSTMAST) OUTPUT(*PDF)
This command queries the CUSTMAST file (on the library list) and sends the resulting report to a PDF file on the IFS. It stores it in the User's home directory (normally '/home/userid' where userid is the actual user profile). The file may then be emailed, downloaded to a PC, Mac, Linux system, stored in your SPOOL file archive or viewed using IBM Navigator.
Top |
*ESCAPE Messages
Top |