iQuery Quick Query (IQ)

The iQuery Quick Query (IQ) command is a quick way to query a database table or view using SQL iQuery. The command generates an SQL iQuery statement or Script and then calls the RUNiQRY CL command to process the request. Access to file members is simplified with this command due to its MBR parameter. Since no User Profile/Password parameters are specified on this command, the end-user will need a user ID on any remote system they attempt to access or the command will fail. Local access to your database files and views is provided.

Restrictions:

You must have SQL iQuery 2COZ-IQ5 licensed program installed to use this command.
This command is conditionally threadsafe. not here.

Parameters

Keyword	Description	Choices	Notes
FILE	File name	Element list	Required, Positional 1
	Element 1: Database table name	Qualified object name
	Qualifier 1: Database table name	Name
	Qualifier 2: Library	Name, CURLIB, LIBL, USER, USRPRF
	Element 2: Remote Database	Name, *LOCAL
MBR	Member name	Name, FIRST, LAST, FILE, ONLY	Optional, Positional 2
OUTPUT	Output	, PRINT, FILE, PDF, TEXT, CSV, STMF, XML, HTML, EXCEL, XLSX, JSON, SYLK, SLK, RPG, LIST, CHART, CHARTJS, DTAARA, OUTFILE, *DISPLAY	Optional, Positional 3
RELNO	Include RRN or Row ID	RRN, ROWID, *NONE	Optional, Positional 4
FLD	Select field names	Single values: ALL, COUNT Other values (up to 300 repetitions): Name	Optional, Positional 5
WHERE	Select/Omit WHERE clause	Character value, *NONE	Optional, Positional 6
ORDERBY	Sort Order by	Character value, *NONE	Optional, Positional 7
LOG	Log RUNiQry command	YES, NO, *RQS	Optional, Positional 8
COMMIT	Commitment control	AUTO, CHG, UR, CS, ALL, RS, NONE, NC, *RR	Optional, Positional 9
OUTFILE	Output file	Qualified object name	Optional, Positional 10
	Qualifier 1: Output file	Name, IQOUTFILE
	Qualifier 2: Library	Name, *CURLIB
OUTMBR	OUTFILE member name	Name, FIRST, FILE	Optional, Positional 11
MBROPT	Member Add/Replace	ADD, REPLACE	Optional, Positional 12

Top

File name (FILE)

Specifies qualified database file name and library to be queried. The optional Remote Database name allows users to specify that the file is on a different server. To use the remote database option, users must have an identical user profile on the remote database server or the attempt will fail.

This is a required parameter.

Element 1: Database table name

The first element is the qualified database table name.

Qualifier 1: Database table name

name: Specify the name of database table (file) and library (schema) to be queried. The file name is inserted into a SELECT statement and passed to the SQL iQuery RUNiQRY command.

Qualifier 2: Library

*LIBL: The library list is searched for the file.
*CURLIB: The current library of the library list contains the file.
*USER or *USRPRF: The user profile of the user running the command is used as the name of the library that contains the file.
name: Specify the name of database file and library to be queried.

Element 2: Remote Database

*LOCAL: The local system contains the database table (file).
name: The name of a remote database server where the file exists. The user running this command must have a user profile on the remote server.

Member name (MBR)

Specifies the name of the database member to be queried. The SQL SELECT statement does not support members directly, so the IQ command creates an SQL iQuery Script that creates an ALIAS that references the member name and then generates the SELECT statement to that ALIAS.

*FIRST: The first memeber added to the file is queried.
*LAST: The last memeber added to the file is queried.
*FILE: The member name that matches the file name is queried.
*ONLY: A legacy term used to access the a member in a file when only one member exists in the file. Typically *FIRST is perferred.
name: The specific member name to be queried.

Output (OUTPUT)

Specifies option to direct the output from the generated SQL SELECT statement.

*: All the standard SQL iQuery output options are supported.

Include RRN or Row ID (RELNO)

Specifies whether the Relative Record Number of sequencial row ID is included as the first column of the results.

*RRN: The relative record number is included in the resultSet.
*ROWID: The row ID (a count of the row in the result) is included in the resultSet.
*NONE: The relative record number and row ID are not included in the results.

Select field names (FLD)

Specifies columns (fields) to be included in the result.

Single values

*ALL: All columns (fields) are included in the result.
*COUNT: The only resulting value is COUNT(*).

Other values (up to 300 repetitions)

name: Specify the name of the columns (fields) to be included in the resultSet.

Select/Omit WHERE clause (WHERE)

Specifies the selection criteria for the query. Any valid SQL WHERE clause value may be specified.

*NONE: All records (rows) are included.
character-value: Specify the WHERE clause to be added to the generated RUNiQRY CL command's SQL SELECT statement. Do not include the word "WHERE" itself as that is automatically inserted.

Sort Order by (ORDERBY)

Specifies sequence for the resultSet generated by the query.

*NONE: The SQL processor will select the optimimal resultSet sequence.
character-value: Specify any ORDER BY clause to order the resultSet. Do not include the "ORDER BY" term itself.

Log RUNiQry command (LOG)

Specifies whether to write the generated RUNiQRY CL command to the JOBLOG as a *RQS or *INFO essage.

*YES or *RQS: The generated RUNiQRY CL command is written to the joblog as a *RQS (request) message.
*NO: The generated RUNiQRY CL command is not written to the joblog.
*INFO: The generated RUNiQRY CL command is written to the joblog as an informational (*INFO) message.

Commitment control (COMMIT)

NOTE: When LOB (Large Object Binary) columns are included in the SQL iQuery resultSet, (CLOB, BLOB, DBLOB) you may NOT run with COMMIT(*NONE). In this context, COMMIT(*AUTO) will allow the resultSets that contain LOB columns to be viewed/processed.

*AUTO

Automatically issues a begin/end commit on the transaction if the file is under commitment control.

There doesn't seem to be an issue with "always running" with COMMIT(*AUTO) but our default remains COMMIT*NONE).

*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

*ALL or *RS

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

Output file (OUTFILE)

Specifies the name of the database file used as the target of the result when OUTPUT(*OUTFILE) is specified. Othewise this parameter is ignored.

Qualifier 1: Output file

IQOUTFILE: The output file is named IQOUTFILE.
name: Specify the name of output file written to when OUTPUT(*OUTFILE) is specified.

Qualifier 2: Library

*CURLIB: The library where the OUTPUT file is written to is the current library.
name: The library where the OUTPUT file is written to.

OUTFILE member name (OUTMBR)

Specifies output file member to which the resultSet is written when OUTPUT(*OUTFILE) si specified.

*FIRST: The first member in the file is the target of the resultSet. If the file doesn't exist the this parameter is the same as specifying *FILE.
*FILE: The file name is used as the output member name.
name: Specify the name of member for the output file.

Member Add/Replace (MBROPT)

Specifies whether to replace the OUTFILE member or add the result to the file.

*ADD: Data is added to the output file member.
*REPLACE: Data is replaced in the output file member.