Run ad hoc iQuery (RUNIQRYF)

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

Parameters

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

Database file to query (FILE)

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

name
Specify the name of an existing database file to be queried.
*xxxxxx
Specify the name of an SQL iQuery macro identifier to be run. When a macro identifier is specified, it must be preceded with an asterisk and followed by a source member name that exists in either QQRYFSRC in the product library, or in source file QMACSRC in any user-library on the library list. If the macro's source member is located, the SQL statement within it is run. If neither source files contain a member with the same name as the macro, an error is issued.

Qualifier 2: Library

*LIBL
The library list is searched for the database file. This is the default.
*CURLIB
The current library is searched for the file. If the job has no current library, then QGPL is searched for the file.
*USRPRF
The user profile's name is used as a library name. That library is searched for the database file.
name
Specify the name of any library that contains the database file.
Top

Member name (MBR)

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.

*FIRST
The first member in the file is queried.
*LAST
The last member in the file is queried.
*FILE
The member name is the same as file, and that member is queried.
name
Specify the name of member that is queried by the RUNIQRYF command.
Top

Output (OUTPUT)

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.

*
Output is directed to the Display. The SQL iQuery Viewer is evoked to display the results of the SQL SELECT statement. Use standard function keys within the display to navigate through the data.
*PRINT
Output is directed to an IBM i SPOOL file. The page dimensions are specified on the PAGESIZE parameter, but by default, auto-calculate the pagesize given the resulting dataset width. When the resulting dataset width is 80 or fewer bytes, the page size is 8.5 wide by 11 tall. and printed in portrait mode. For wider datasets, SQL iQuery switches to landscape mode and prints on 11 wide by 8.5 tall paper. Again, this can be overriden by the PAGESIZE and CPI parameters.
*OUTFILE or *FILE
The output is directed to a new or existing DB2 for i database file. The result dataset rows are written to the file specified on the OUTFILE parameter. The MBROPT (member option) parameter controls whether records are added to the existing file or replace the contents of the OUTFILE member.
*PDF
The output is directed to a PDF file on the IFS. By default the file is created in the user's home directory. The output PDF file name may be specified on the STMF and STMFNAME parameters.
*TEXT
The output is directed to a TEXT file on the IFS. By default the file is created in the user's home directory. The output TEXT file name may be specified on the STMF and STMFNAME parameters. TEXT Files look similar the standard IBM i SPOOL file output listings, but only one row of headers are created and the data is written as if it were all on one large/bottom-less page. The output text file's CCSID is PC ASCII but can be specified on the STMFCCSID parameter.
*CSV
The output is directed to a TEXT file on the IFS in Comma Separated format. This type of file may be read by Spreadsheet products, such as Microsoft Excel.
*RPGxx
The SQL iQuery engine generates Input, Output, or Definition specifications based on the value specified. When one of the following OUTPUT options is specified, the SQL statement is only "prepared" but not executed (run). Instead, the follow RPG source is generated based on the OUTPUT parameter:
*RPGDS
RPG IV Definition (D-specs) specifications
*RPGIO
RPG IV Input and Output specifications
*RPGI
RPG IV Input specifications
*RPGO
RPG IV Output specifications
*RPGDSO
RPG IV Definition (D-specs) and Output specifications
Top

Select fields to include (FLD)

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.

*ALL
All fields are listed by the RUNIQRYF command in the sequence they appear in the database file. When FLD(*ALL) is specified, it may be specified as the only value or along with either *ROWID or *RRN special values. If *ROWID or *RRN are specified, then *ALL is implied. That is, FLD(*ROWID) is identical to FLD(*ROWID *ALL). and FLD(*RRN) is identical to FLD(*RRN *ALL). Specifying FLD(*ALL *ROWID) or FLD(*ALL *RRN) inserts the row or record number after listing all the field values.
*ROWID
The row sequence number of the record is included in the output. If FLD(*ROWID) is the only value, then the rowid is listed first followed by all fields in the file. If *ROWID is included with any other values or fields, then it is listed chronologically with those other values in the output. If *ALL is also specified, the rowid is listed before or after the other fields based on the sequences of *ALL and *ROWID on the FLD parameter.
*RRN
The relative record number of the selected reocrd is output. If FLD(*RRN) is the only value, then the RRN is listed first followed by all fields in the file. If *RNN is included with any other values or fields, then it is listed chronologically with those other values in the output. If *ALL is also specified, then the *RRN value is listed before or after the other fields based on the sequences of *ALL and *RRN on the FLD parameter.
*DATE
The current date is included in the resulting dataset.
*COUNT or *COUNTER
The SQL function COUNT(*) is embedded into the SELECT statement. when this value is selected, no other field values may be specified.
*USER or *USRPRF
The current user profile (of the user running the RUNIQRYF command) is embedded into the list of fields returned by the command.
name
Specify up to 300 field names to be included in the output. Only the included fields are listed in the output, in the order they appear on this parameter.
Top

Select fields to omit (OMITFLD)

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

*NONE
No fields are omitted. All fields from the FLD parameter are included.

Other values (up to 300 repetitions)

generic-name
Specify the name of one or more fields that are exclused from the queried output. These fields names must match exactly unless a generic name is sepcified (partial field name followed by an asterisk, for example: OMITFLD(CM*) omits all fields that begin with the letter 'CM'. Otherwise use a full field name, such as OMITFLD(CHGDATE YTDSALES)
Top

WHERE clause (SQL syntax) (WHERE)

Specifies the select and omit conditions for records returned by RUNIQRYF. This parameter accepts a standard SQL "WHERE" clause.

*NONE
No WHERE ("select/omit") clause is generated. All records are included in the queried output.
character-value
Specify a standard SQL WHERE clause that indentifies the selection of records included by RUNIQRYF. Do not included the world "WHERE" in your WHERE parameter as it is automatically inserted. An example where the records returned should have a customer number between 100 and 500 would be: WHERE('custno between 100 and 500')
Top

ORDER By clause (SQL syntax) (ORDERBY)

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.

*NONE
No sorting is applied to the queried output. That is, no ORDER BY clause is generated. Data is returned in arrival sequence, but may vary each time the query is run. To insure a consistent sequence, specify a sort sequence (ORDERBY parameter) for the query.
character-value
Specify a standard SQL ORDER BY sequencing. Separate each Order By ("SORT") field with a comma. If the sort sequence shoudl be DESCENDING order, then follow the field name with at least one blank and the keyword DESC. For example: ORDERBY('ordDate desc,invnbr') returns the results in Order Data Sequence in descending order, then sorts withing ORDDATE by INVNBR. Do not include the "ORDER BY" keywords themselves, as they are automatically inserted into the resulting query for you.
Top

Remote System name for RDB (RDB)

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.

*LOCAL
The local system contains the file being queried. This is the value to use to access database files on the current partition.
character-value
Specify the name of the remote database directory entry on which the file that shall be queried reside.
Top

Remote Database User ID (USER)

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.

*NONE
No remote user ID is specified.
character-value
Specify the user ID to use to connect to the remote system specified on the RDB parameter.
Top

Remote Database Password (PWD)

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.

*NONE
No password is specified.
character-value
Specify the User ID's password used to sign onto the remote system specified on the RDB parameter.
Top

SPOOL File name (SPLFNAME)

Specifies name of the SPOOL file when OUTPUT(*PRINT *PDF or *TEXT) is specified.

*DFT
The SPOOL file name is retrieved from the SQL iQuery Defaults user space. If no user space exists on the library list or in the product library for licensed program 2COZIQ3, then "QPIQRYFILE" is used.
*FILE
The base table name (if specified) is used as the SPLFNAME parameter. If no base table is specified or cannot be determined, the default SQL iQuery name is used.
*SRCMBR
If a source file and member are specified on the SRCFILE and SRCMBR parameters, the value for SRCMBR is used as the SPOOL file name.
name
Specify the name of the SPOOL file being created for OUTPUT(*PRINT *PDF *TEXT).
Top

Output file (OUTFILE)

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

QSQLSELECT
The command default for the output file name is QSQLSELECT.
name
Specify the name of new or existing database file that receives the output of the SQL SELECT statement. If the file does not exist it is automatically created by the Run SQL using Query File command.

Qualifier 2: Library

Specify the library name where the output file is located or will be located (if it is being created).

*CURLIB
The output file is located in the current library for the job. If no current library is specified for the job, QGPL is used.
name
Specify the name of the library where the output file exists or will be created by the Run SQL using Query File command.
Top

Output member (ignored) (OUTMBR)

Specifies an output member for the OUTFILE. SQL largely ignores members in database tables. This parameter is reserved for future use.

*FIRST
The first member is used as the target of the output file. If the file is being created for the first time, then the OUTFILE name is used as the *FIRST member name.
*FILE
The output file name is also used as the output member name.
name
Specify the name of member in the output file that receives the data from the SELECT statement.
Top

Output member option (MBROPT)

Specifies whether to replace or add records in the output file member.

*ADD
Records (rows) are added to the output file(member).
*REPLACE
The output file member is cleared and new records (rows) are added to the output file(member).
Top

Stream File Location (STMF)

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.

*HOME
The user's home directory (associated with their user profile) is retrieved as the stream file location. Specify the actual stream file or PDF file name on the STMFNAME parameter.
path-name
Specify the path name and optionally the stream file or PDF file name. For example: /dir1/dir2 or /dir1/dir2/myoutput.pdf
Top

Stream File name (STMFNAME)

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

*FILE
The file name specified on the FILE parameter is used as the name of the IFS stream file or PDF file being created. If a PDF file is being created then the '.PDF' suffix is added to the file name.
path-name
Specify the IFS stream file name to be created. If OUTPUT(*PDF) is specified then the '.PDF' suffix is automatically added to the file name specified here unless the '.PDF' suffix is included in the file name by the user.
Top

Output Stream file data option (STMFOPT)

Specifies whether to clear or add to an existing stream file.

*REPLACE
If the stream file already exists, it is deleted, then the output from the Run SQL using Query File command is written to the stream file, which is re-created.
*ADD
The output from the Run SQL using Query File command is added to the stream file if the stream file already exists. Otherwise the stream file is created.
Top

Output Stream folder option (STMFDIROPT)

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.

*NONE or *NO
No folders or subfolders are create on the IFS.
*CREATE or *YES
The folders and subfolders are created on the IFS if they do not exist.
Top

Output Stream file CCSID (STMFCCSID)

Specifies CCSID used when creating and translating the data being written to the IFS file specified on the STMF and STMFNAME parameters.

*PCASCII
CCSID 819, normally associated with Microsoft Windows is use.
*JOB
The CCSID associated with the user's job is used.
*NONE
The file is created as "binary" or CCSID 65535.
*UTF8
CCSOD 1208 which is UTF-8 is used to create the IFS file.
*UTF16
CCSOD 1200 which is UTF-16 is used to create the IFS file.
1-65535
Specify any valid CCSID for the output IFS stream file.
Top
Top

CSV Options (CSVOPT)

Specifies options to control how comma separated files are created.

Element 1: Headings

Note that column headings are not enclosed in quotes.

*NOHDG or *NONE
No column headings are generated. Use this option when adding to an existing CSV file or when no column headings are desired.
*FIELDS
The field names are used as column headings. Field names used as column headings are not enclosed in quotes.
*COLHDG
The column headings (COLHDG keyword in DDS or the LABEL ON IS statement in SQL) are used as the column heading. Column headings are not enclosed in quotes.

Element 2: Trim blanks

Specifies whether or not trailing and leading blanks are removed.

*NONE
No blanks are trimmed from the data.
*RIGHT
Blanks on the right side (trailing end) of the field are trimmed.
*LEFT
Blanks on the left side (leading end) of the field are trimmed.
*BOTH
Blanks on both ends of the field are trimmed.

Element 3: Quote symbol

Specifies the symbol (character) used to enclose non-numeric fields.

*QUOTE
The standard double-quote (") symbol is used.
*APOS
The appostrophy (') is used.
*BAR
The vertical bar symbol (|) is used.
*NONE
The output data is not enclosed a any characters.
character-value
Specify any valid character to use as "Quote" symbol. Up to 10 characters may be specified.

Element 4: Escape symbol

Specifies the symbol inserted before the Quote symbol when that quote symbol is detected within the data.

*DBL or *STRDLM
The symbol specified on the Quote symbol portion of this parameter is used as the escape symbol.
*ESC
The ASCII back slash is used to escape the data.
*TRIM
If an embedded quote symbol is detected, it is deleted/removed from the data before it is written to the CSV file. The position where the quote was detected is removed from the data, reducing the overall length of the data by 1 or the number of characters indicated for a quote.
character-value
Specify any character to insert before the Quote symbol value when the Quote symbol is detected within the field being converted.

Element 5: Separator symbol

*COMMA
The ASCII comma is used as the delimitor.
*TAB
The ASCII TAB is used as the delimitor.
*BAR
The ASCII vertical bar is used as the delimitor.
character-value
Specify any character(s) to be used as the delimitor.

Element 6: Null value symbol

*NONE
No NULL value symbol is used. Data that is NULL is returned normally.
*DASH
The dash - symbol is returned when a field is NULL.
*PLUS
The plus + symbol is returned when a field is NULL.
*PERIOD
The period . is returned when a field is NULL.
character-value
Specify any character(s) to return when a NULL field is detected.

Element 7: End of line symbol(s)

*LF
The end of line character is an ASCII linefeed.
*CR
The end of line character is an ASCII carrage return.
*CRLF
The end of line characters are an ASCII carrage return followed by a linefeed.
*LFCR
The end of line characters are an ASCII linefeed followed by a carrage return.
character-value
Specify the character(s) to be used as end-of-line markers.
Top

Correlation name for file (AS)

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.

*NONE
No correlation name is assigned.
name
Specify the name used as the correleation name. You can reference this name on the WHERE and ORDERBY parameters.
Top

Log Options (LOG)

Specifies whether to write the SQL statement to the joblog. and/or write the subsequent RUNIQRYF command to the joblog.

*NONE
No logging is performed.
*SQL
The SQL statement is written to the joblog.
*QRYF
The generated RUNIQRY command is written to the joblog.
*LVLx
One of the RUNIQRY LOG parameter options may be specified. This value is passed along to the embedded RUNIQRY command and processed. For more information on LOG(*LVLx) options, see the help text for the RUNIQRY command's LOG parameter.
Top

Commitment control (COMMIT)

Specifies the automatic commitment control for non-SELECT statements. This parameter is ignored if the SQL parameter is a SELECT statement.

*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 specified, then *NONE or *NC must be used. If a remote relational database is specified on the RDB parameter and the relational database is on a system that is not IBM i, then *NONE or *NC cannot be specified.
*CHG or *UR
Specifies the objects referred to in SQL ALTER, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, 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, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, 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, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, 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.
*RR
Specifies the objects referred to in SQL ALTER, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, 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).
*AUTO
Automatically issues a begin/end commit on the transaction if the file is under commitment control.
Top

Naming (NAMING)

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.

*SYS
The IBM i naming convention is being used on the SQL statement specified on the SQL parameter. Tables are qualified to library names using a forward slash.
*SQL
The standard SQL convention is used on the SQL statement specified on the SQL parameter. Tables are qualified to library names using a period.
Top

Optimize FOR (OPTIMIZE)

Specifies the number of records to insert into the "OPTIMIZE FOR xx ROWS" clause of the generated SELECT statement.

50
"OPTIMIZE FOR 50 ROWS" is added to the SQL SELECT statement that is sent to the RUNIQRY command.
*ALL
No "OPTIMIZE FOR" clause is inserted. The RUNIQRY command's OPTIMIZE parameter is set to OPTIMIZE(*ALLIO). For OUTPUT choices other than OUTPUT(*PRINT) using RUNIQRYF ... OPTIMIZE(*ALL) is recommended.
*NONE
No "OPTIMIZE FOR" clause is inserted. The RUNIQRY's OPTIMIZE parameter is defaulted.
record-count
Specify the number of records to insert as the XX argument of the "OPTIMIZE FOR xx ROWS" clause that is added to the generated SELECT statement and passed to the RUNIQRY command.
Top

Examples for RUNIQRYF

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

Error messages for RUNIQRYF

*ESCAPE Messages

CPF9801
Object &2 in library &3 not found.
CPF9802
Not authorized to object &2 in &3.
CPF9803
Cannot allocate object &2 in library &3.
CPF9807
One or more libraries in library list deleted.
CPF9808
Cannot allocate one or more libraries on library list.
CPF9810
Library &1 not found.
CPF9811
Program &1 in library &2 not found.
CPF9812
File &1 in library &2 not found.
CPF9820
Not authorized to use library &1.
CPF9830
Cannot assign library &1.
CPF9899
Error occurred during processing of command.
Top