Run SQL using iQuery (RUNIQRY)

Where allowed to run: All environments (*ALL)
Threadsafe: No
Parameters
Examples
Error messages

The COZZI - Run SQL using iQuery (RUNIQRY) provides an interface for running pure SQL statements over your IBM i database files, as well as other databases on other systems. The RUNIQRY command accepts SQL statements to preform the query operation. In addition to database query and report writing, SQL iQuery also allows users to run virtually any native SQL command, such as INSERT, UPDATE, DELETE, CREATE and SELECT.

Output from the RUNIQRY command can be directed to a growing number of output devices, including Display, Print, PDF, IFS as sell as another DB2 file.

Restrictions:

Top

Parameters

Keyword Description Choices Notes
SQL SQL Statement Character value, *STMF, *SRCF, *SRCFILE, *ACTJOB, *ALLOBJ, *ALLUSR, *DB2PTF, *DSK, *GRPPRF, *IBMBLOG, *JOBLOG, *JOBSCD, *JOBSCDE, *LIBL, *OBJ, *PTFBKPLVL, *PTFGRP, *RCDLCK, *SPCVAL, *SYS, *SYSVAL, *TCPIP, *TOPHOGS, *USRGRP, *USRPRF Optional, Positional 1
OUTPUT Output *DFT, *, *PRINT, *FILE, *PDF, *TEXT, *CSV, *STMF, *XML, *HTML, *EXCEL, *XLSX, *ODS, *JSON, *SYLK, *SLK, *LIST, *OUTFILE, *RPG, *RPGIO, *RPGI, *RPGO, *RPGDS, *RPGDSO, *SQL, *SQLINTO, *SQLINTODS, *SQLDCL, *SQLEXEC, *SQLSRC, *SQLSCRIPT, *SQLIMMED, *DISPLAY Optional, Positional 2
COMMIT Commitment control *CHG, *UR, *CS, *AUTO, *ALL, *RS, *NONE, *NC, *RR Optional, Positional 3
NAMING Naming *SYS, *SQL Optional, Positional 4
LOG Log options *LVL1, *LVL2, *LVL3, *LVL4, *LVL5, *LVL0, *YES, *NO, *NONE, 0, 1, 2, 3, 4, 5 Optional, Positional 5
RDB Remote Relational database Character value, *LOCAL, * Optional, Positional 6
USER Remote Database User ID Character value, *NONE, *USER, *USRPRF, *CURRENT Optional, Positional 7
PWD Remote Database Password Character value, *NONE Optional, Positional 8
DFTRDBCOL Default library (DFTRDBCOL) Name, *NONE Optional, Positional 9
DATFMT Date format *ISO, *USA, *EUR, *JIS, *MDY, *DMY, *YMD, *JUL, *JOB, ISO, USA, EUR, JIS, MDY, DMY, YMD, JUL, JOB Optional, Positional 10
DATSEP Date separator *SLASH, *DASH, *PERIOD, *COMMA, *BLANK, /, -, ., ,, , *JOB Optional, Positional 11
TIMFMT Time format *ISO, *USA, *EUR, *JIS, *HMS, ISO, USA, EUR, JIS, HMS Optional, Positional 12
TIMSEP Time separator *COLON, *PERIOD, *COMMA, *BLANK, :, ., ,, , *JOB Optional, Positional 13
DECPOINT Decimal point *PERIOD, *COMMA, *JOB, '.', ',', * Optional, Positional 14
NULLCHAR Null field symbol Character value, *PERIOD, *PLUS, *MINUS, *ASTERISK, '+', '-', *, '.' Optional, Positional 15
DDECHAR Decimal Data Error symbol Character value, *PERIOD, *PLUS, *MINUS, *ASTERISK, '+', '-', *, '.' Optional, Positional 16
DECRESULT Decimal result options Element list Optional, Positional 17
Element 1: Maximum precision (length) 31, 63
Element 2: Maximum scale (decimals) 0-63, 31
Element 3: Minimum divide decimals 0-9, 0
OPTIMIZE Optimization Element list Optional, Positional 18
Element 1: Entire query for *OUTPUT, *FIRSTIO, *ALLIO
Element 2: *FIRSTIO optimize for xxx rows 1-32766, 100, *NONE
SVRMODE SQL server mode *BATCH, *ALL, *INTERACT, *NONE, *YES, *NO Optional, Positional 19
DSPOPTION Green Screen Display options Values (up to 5 repetitions): *NONE, *NOSTMTVIEW, *NOSTMTSAVE, *NOFLDLIST, *NOSAVE, *NOFIELDLST, *NOLIST Optional, Positional 20
COLTOTAL Columns to total Values (up to 100 repetitions): Character value, *NONE, *LAST Optional, Positional 21
LVLBRK Columns to print when changed Values (up to 100 repetitions): Character value, *NONE Optional, Positional 22
PAGEBRK Page break columns Values (up to 100 repetitions): Character value, *NONE Optional, Positional 23
SRCSTMF Source stream file Character value Optional, Positional 24
SRCFILE SQL Source script file name Qualified object name Optional, Positional 25
Qualifier 1: SQL Source script file name Name, QSQLSRC
Qualifier 2: Library Name, *LIBL, *CURLIB
SRCMBR SQL Source script member Name, *FIRST, *FILE, *NONE Optional, Positional 26
OUTFILE Output file Qualified object name Optional, Positional 27
Qualifier 1: Output file Name, QSQLOUTPUT
Qualifier 2: Library Name, *CURLIB
OUTMBR Output (source) member Name, *FIRST, *FILE Optional, Positional 28
MBROPT Output member option *ADD Optional, Positional 29
STMF Stmf path or path & Stmf Name Path name, *HOME, *CURRENT Optional, Positional 30
STMFNAME Stream file name or *STMF Path name, *STMF, *SPLFNAME, *FILE, *LIBFILE, *USRDTA Optional, Positional 31
STMFOPT Output Stream file data option *REPLACE, *ADD Optional, Positional 32
STMFDIROPT Output Stream folder option *CREATE, *YES, *NONE, *NO Optional, Positional 33
STMFCCSID Output Stream file CCSID 1-65535, *PCASCII, *WINDOWS, *APPLE, *JOB, *NONE, *UTF8, *UTF16 Optional, Positional 34
STMFHDR Insert Content-Type Header *YES, *NO Optional, Positional 35
CSVOPT CSV Options Element list Optional, Positional 36
Element 1: Column 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 record 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
SLKOPT OUTPUT(*SLK) options Element list Optional, Positional 37
Element 1: Target platform *WINDOWS, *OTHER, *PC, *MAC, *LINUX
Element 2: Row/Column notation for =SUM() *ROWCOL, *CELL
Element 3: Column headings *NONE, *FIELDS, *COLHDG, *FLDNAME, *FLDNAM, *NOHDG, 0, 1, 2
Element 4: Column Header Font Character value, *NONE, 'Arial'
Element 5: Header Font Size 6.0-250.0, 12
Element 6: Column Header Font Style Character value, *NORMAL, *BOLD, *ITALIC, *BOLDITAL
Element 7: Body (data) Font Character value, *NONE, 'Arial'
Element 8: Body Font Size 6.0-250.0, 10
Element 9: Body Font Style Character value, *NORMAL, *BOLD, *ITALIC, *BOLDITAL
Element 10: Footer (totals) Font Character value, *NONE, 'Arial'
Element 11: Footer Font Size 6.0-250.0, 12
Element 12: Footer Font Style Character value, *NORMAL, *BOLD, *BOLDBOX, *ITALIC, *BOLDITAL
XLSOPT Excel output options Element list Optional, Positional 38
Element 1: Target platform *WINDOWS, *MAC, *OTHER, *WIN, *APPLE, *LINUX
Element 2: Lock header rows Integer, *YES, *NO, *UNLOCK
SPLFNAME SPOOL File name Name, *DFT, *FILE, *SRCMBR, *JOB Optional, Positional 39
USRDTA SPOOL File User data Character value, *SPLF, *SPLFNAME, *FILE, *JOB, *DFT Optional, Positional 40
PAGESIZE Page size Single values: *AUTO, *DFT
Other values: Element list
Optional, Positional 41
Element 1: Page length (height) 0.0-255.0, 11
Element 2: Page width 0.0-378.0, 8.5
Element 3: Unit of measure method *UOM, *INCH, *CM, *ROWCOL
LPI Lines per inch (LPI) 8, *DFT, 3.0, 4.0, 6.0, 7.5, 8.0, 9.0, 12.0 Optional, Positional 42
CPI Characters per inch (CPI) *AUTO, *DFT, 5.0, 10.0, 12.0, 13.3, 15.0, 16.7, 18.0, 20.0 Optional, Positional 43
OVRFLW Overflow line Integer, *AUTO, *DFT Optional, Positional 44
MARGIN Left margin (in *UOM) 0.0-25.9, 0.25, *NONE Optional, Positional 45
FONT Font Single values: *CPI, *DEVD
Other values: Element list
Optional, Positional 46
Element 1: Font Identifier Character value
Element 2: Point size 0.1-999.9, *NONE
PAGRTT Degree of page rotation *DFT, *AUTO, *DEVD, *COR, 0, 90, 180, 270 Optional, Positional 47
UOM Unit of measure *INCH, *CM, *DFT Optional, Positional 48
MAXRCDS Max SPOOL file rcds DFT(40k) 1-99999999, *NOMAX, *DFT, 40000 Optional, Positional 49
OUTQ Output queue Single values: *JOB, *DEV, *DFT
Other values: Qualified object name
Optional, Positional 50
Qualifier 1: Output queue Name
Qualifier 2: Library Name, *LIBL, *CURLIB
HOLD Hold spooled file *YES, *NO Optional, Positional 51
SAVE Save spooled file *YES, *NO Optional, Positional 52
EXPDATE SPOOL File Expires on Date Date, *NONE, *DAYS, *TODAY, *CURRENT, *TOMORROW, *YEAR, *MONTH Optional, Positional 53
EXPDAYS SPOOL file expires in (DAYS) 1-1830, *EXPDATE Optional, Positional 54
APPTITLE Output Title Line 1 Character value, *DFT, *SRCTEXT, *BLANKS, *NONE Optional, Positional 55
USRTITLE Output Title Line 2 Character value, *DFT, *SRCTEXT, *BLANKS, *NONE Optional, Positional 56
RPTTITLE Output Title Line 3 Character value, *DFT, *SRCTEXT, *BLANKS, *NONE Optional, Positional 57
AUTOQUOTE Auto quote replacement text *YES, *NO, *APOS, *QUOTE, *SINGLE, *DOUBLE, *NOQUOTE, *NONE, *DBLQT Optional, Positional 58
VARPREFIX Insert variable prefix Character value, *AMP, *NONE Optional, Positional 59
SETVAR Substitution Var/Value pairs Single values: *NONE
Other values (up to 64 repetitions): Element list
Optional, Positional 60
Element 1: Variable name/identifier Character value, *AUTO
Element 2: Variable value Not restricted
Element 3: Match Whole Word Only *YES, *NO, *WHOLEWORD
EMAIL Email address (send to) Single values: *NONE
Other values (up to 30 repetitions): Path name
Optional, Positional 61
SUBJECT email subject Character value, *NONE Optional, Positional 62
EMAILPMT Prompt email CL command *PROMPT, *NONE, *YES, *NO Optional, Positional 63
EMAILCMD email command *DFT, *SENDMAIL, *IBM Optional, Positional 64
Top

SQL Statement (SQL)

Specifies the SQL statement to be run. The syntax is verified by the SQL engine and continues if no syntax errors are detected. Any valid SQL statement may specified, including but NOT limited to:

Runtime substitution values may be specified on the VAR parameter. These values are inserted into the SQL statement at runtime. See the VAR parameter for more information.

This is a required parameter.

character-value
Specify the SQL statement to be run.
*STMF
The SQL Statement is located in the IFS file specified on the SRCSTMF parameter. The stream file is read and the SQL statement stored in it is run by the SQL iQuery engine.
*SRCF or *SRCFILE
The SQL Statement is located in the Source File member specified on the SRCFILE and SRCMBR parameters. The SQL statement can span as many source lines as necessary and may NOT include a terminating semi-colon; Comments may be embedded in the source file using the SQL -- comment syntax. Comments must be the only values on the line; that is they may not appear on the same line as the SQL statement itself.
*xxxxxxxxxx
Specify the name of an SQL iQuery macro identifier to be run. When a macro identifier is specified, it must be begin with an asterisk 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. For example, create a pre-written SQL SELECT statement and save it to source member SALESRPT in QMACSRC in a library on your library list. To run the SQL statement within the SALESRPT source member, do the following:

RUNiQRY *SALESRPT

Top

Output (OUTPUT)

Specifies output from an SQL SELECT statement. If the SQL parameter is not a SELECT statement, this parameter is ignored.

*
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.
*DFT
Output is directed to the device specified on the #OUTPUT or #DFTOUTPUT keyword in the iQuery Script source file member. If no source file member is used with the command, then OUTPUT(*DFT) is the same as OUTPUT(*).
*PRINT or *LIST
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.
*EXCEL or *XLS or *XLSX
The output is directed to a UTF-8 file on the IFS in native MS Excel format. Today, we support .XLS format which is an XML-based Excel file structure. Testing has shown that Excel on mobile OS's such as Apple iOS and Android do not support this format. We are researching a solution to this issue. It is a future objective to support .XLSX (Excel workbooks). A .XLS file contains XML for each row and cell in the Excel File along with formatting information. Numeric fields are editted so their full decimal positions appear, and date fields are inserted and rendered using DD-MMM-YYYY date format. For example, 23-JUL-2016. Column totals via the COLTOTAL parameter are also supported and inserted as a formula not a hard-coded value.
*SLK or *SYLK
The output is directed to a TEXT file on the IFS in Symbolic Link or "SyLK" format. This type of file may be read, changed and written by spreadsheet products, such as Microsoft Excel and others. Once loaded into it may be saved back as SyLK format, or as native XLS and other formats. The advantage of SyLK or OUTPUT(*EXCEL) over OUTPUT(*CSV) is that some formatting is inserted into the file whereas CSV is just raw data. The file suffix is .SLK but the abbreviation for this type of file is SyLK. A SYLK file contains each cell of the Excel sheet in a distinct record and are much larger than CSV files due to the formatting codes that are also embedded int he file. SyLK files originated in Microsoft MultiPlan and have been supported in every version of Excel since version 1.0 back in the 1980s.
*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:
*RPG or RPGLE or RPGSQL or SQLRPGLE
RPG IV EXEC SQL statement is written
*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
*SQL or *SQLSRC or *SQLSRCIPT
The SQL statement is written to an SQL Script Source member.
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

Log SQL statements (LOG)

Specifies whether to write the SQL statement to the joblog and whether or not to include additional information in an SQL iQuery Diagnostic Log at the end of SPOOL file created when OUTPUT(*PRINT or *PDF) is specified. This can help when ad hoc queries are run and a report sent off to a user, and then they ask you to run it or a modified form of it again. The Diagnotics page can be used to recall the SQL statement run, and the settings used during that run.

*NONE or *NO or *LVL0
The SQL statement information is not logged.
*LVL1 or *YES
The SQL statement is written to the joblog.
*LVL2
When OUTPUT(*) is specified, the SQL statement is written to the joblog. When OUTPUT(*PRINT or *PDF) is specified, the SQL Statement is written to an extra "SQL Diagnostics" page.
*LVL3
The SQL statement is written to the joblog and if OUTPUT(*PRINT) is specified, an extra "SQL Diagnostics" are printed with information about the resultSet.
*LVL4
Reserved.
*LVL5
Functions the same as *LVL3 plus when OUTPUT(*PRINT or *PDF) is specified it includes a complete list of Field (column) definitions from the SQL (select) statement.
Top

Remote Relational database (RDB)

Specifies the name of a remote system or remote database where the tables specified on the SQL statement (on the SQL parameter) are located. If 3-level or 3-tiered naming is used, this parameter should be *LOCAL.

*LOCAL
The local system and current partition contain the database files.
simple-name
Specify the name of remote database or remote system where the tables referenced in the SQL statement reside.

When a remote database (which is basically a remote IBM i partition or remote system ID) is specified, SQL iQuery sends the SQL statement to that system for processing. When a SELECT statement is specified on the SQL parameter, the results are pulled to the local system for display, print, IFS output, etc. When an UPDATE, INSERT or DELETE is specified, it is performed entirely on the remote system. The number of rows impacted is returned, however depending on the release level of the remote system and the PTF or TRx level, that value (the number of rows impacte by the statement) may be incorrect. So don't trust the response. We've noticed this particularly when accessing a remote system that is running IBM i5/OS V5R4M0 from a IBM i V7Rx system.

Top

Remote Database User ID (USER)

Specifies a user profile used to run the SQL statement on the remote system that was specified on the RDB parameter. User profile names up to 18 characters in length may be specified.

*NONE
This default is used when RDB(*LOCAL) is specified.
character-value
Specify the any user profile for the remote system that should be used to validate the credentials of the user attempting to perform the SQL statement on data located on the remote system (RDB). If the user profile and password are not validated, the SQL request will fail.
Top

Remote Database Password (PWD)

Specifies password for the user profile specified on the USER parameter. Passwords up to 18 characters in length may be specified.

*NONE
This default is used when RDB(*LOCAL) is specified.
character-value
Specify the password for the user profile specified on the USER parameter.
Top

Default Relational Database (DFTRDBCOL)

Specifies the name of the schema identifier used for the unqualified names of the tables (files), views, indexes, SQL packages, aliases, constraints, external programs, node groups, and triggers. Specifying a value other that *NONE for this parameter causes the named library to act similar to the CURRENT Library for the job.

*NONE
This default is based on the value specified for the NAMING parameter. If NAMING(*SYS) is specified, the the library list is searched for unqualified tables, files, etc. If NAMING(*SQL) is specified, then the user profile name is used as the default collection (default library) name.
library-name
Specify the existing library to be used as the default relational database library for unqualified tables, files, etc.
Top

Date format (DATFMT)

Specifies the date format used for date variables returned by a SELECT statement.

*JOB
Whatever setting is on the RTVJOBA DATFMT attribute is used as the date format. This is the Default.
*ISO
ISO Date format.
*USA
USA Date format.
*EUR
European Date format.
*JIS
Japaneze Date format.
*MDY
Month/Day/Year (MM/DD/YY) format.
*DMY
Day/Month/Year (DD/MM/YY) format.
*YMD
Year/Month/Day (YY/MM/DD) format.
*JUL
Julian Date (yy/ddd) format.
Top

Date separator (DATSEP)

Specifies separator used when the DATFMT parameter is any of the following: *MDY *YMD *DMY otherwise this parameter is ignored.

*JOB
Whatever setting is on the RTVJOBA DATSEP attribute is used as the date separator. This is the default.
*SLASH or /
The forward slash '/' is used as the separator.
*DASH
The forward dash '-' is used as the separator.
*PERIOD The period '.' is used as the separator.
*COMMA
The comma ',' is used as the separator.
*BLANK
The blank ' ' is used as the separator.
/ - , . &
Specify the actual symbol/character used as the separator.
Top

Time format (TIMFMT)

Specifies the time format used for time variables returned by a SELECT statement. The only real difference in time formats is the separator used. So largely it is recommended to just leave it default to *JOB.

*HMS
The time format is HH:MM:SS (e.g., 15:15:15) the TIMSEP parameter.
*ISO
The time format is HH.MM.SS (e.g., 15.15.15)
*USA
The time format is HH:MMxx (e.g., 3:15PM)
*EUR
The time format is HH.MM.SS (e.g., 15.15.15)
*JIS
The time format is HH:MM:SS (e.g., 15:15:15)
Top

Time separator (TIMSEP)

Specifies the separator used to separate the components of the time.

*COLON
The colon is the separator.
*PERIOD
The period is the separator.
*COMMA
The comma is the separator.
*BLANK
The blank is the separator.
*JOB
Whatever setting is on the RTVJOBA TIMSEP attribute is used as the separator.
Top
Top
Top
Top

Decimal Result Scale (DECRESULT)

Specifies the maximum precision, maximum scale, and minimum divide scale that should be used during decimal operations, such as decimal arithmetic. The specified limits only apply to NUMERIC and DECIMAL data types.

Precision equates to the length of the decimal result field. Scale equates to the decimal positions. In the case of Precision/Length it is the declared length of intermediate values, while the Scale/decimal positions refers to the maximum decimal positions it will use. It is recommended that you cast your results or use DECRESULT(63 31 0) for this parameter, otherwise when larger values are returned you could see truncation that will cause a Data Formatting error (similar to a old fashion DDE).

Max Precision (Length)

63
Specify the maximum precision (length) that is returned for intermediate mathmatical result values. The choices are 31 or 63.

Maximum Scale (decimals)

31
The maximum number of decimals that may be used for intermediate results from mathematical operations. This value must be less than the value specified for the Max Precision (length) option.

Minimum Divide Scale (decimals)

0
The minimum number of decimals that should be returned for intermediate results from divide operations. This value must be between 0 and 9. Note: When 0 is specified, no divide scale is used.
Top

Optimization (OPTIMIZE)

Specifies the optimization the SQE performs to complete the query faster. When OUTPUT(*) is specified, it is recommended that OPTIMIZE(*FIRSTIO) be used. For all other OUTPUT devices, *ALLIO should yield better performance. The second part of the parameter controls the OPTIMIZE FOR xxx ROWS clause being automatically added to SELECT statements when OUTPUT(*) is specified. It identifies the number of rows to use in the OPTIMIZE FOR xxx ROWS clause. This parameter is ignored when the SQL statement is something other than a SELECT statement.

*OUTPUT
Specify this option to have the SQL iQuery command inspect the OUTPUT parameter. When OUTPUT(*) is specified, then the OPTIMIZE parameter is set to *FIRSTIO. Otherwise the OPTIMIZE parameter is set to *ALLIO.
*FIRSTIO
Optimization is performed to return the first set of rows/records to the SQL iQuery user interface quickly and then proceed to complete the overall query. It works with the OPTIMIZE FOR xxx ROWS clause.
*ALLIO
The entire dataset is produced before any data is returned to the SQL iQuery user.

Element 2: OPTIMIZE FOR.

Specifies the number of rows to optimize for when OUTPUT(*) is sepcified for a SELECT statement.

100
OPTIMIZE FOR 100 ROWS is added to interactive SELECT statements that are routed to the display using OUTPUT(*) or OUTPUT(*DISPLAY).
1 to 32766
This number is used as the number of rows on the OPTIMIZE FOR xxx ROWS clause inserted into the SELECT statement.
*NONE
The OPTIMIZE FOR xxx ROWS clause is NOT added to interactive SELECT statements.
Top

Server Mode (SVRMODE)

Specifies when SQL SerVeR Mode is used to process the SQL statements for SQL iQuery. By default for batch jobs, SerVeR mode is always used. For interactive jobs, this parameter allows users to start server mode for the job. Once started, it cannot be ended for the job. If SQL Server Mode is active for the job, this parameter is ignored. SQL Server Mode disables the IBM STRSQL command for the job.

*BATCH
If the RUNIQRY command is run within a BATCH job, then SQL SERVER MODE is activated for the batch job.
*INTERACT
If the job running the RUNIQRY command is an Interactive Job, then SQL SERVER MODE is activated for this job. When SQL SERVER MODE is active, IBM STRSQL is disabled for the job.
*ALL or *YES
SQL SERVER MODE is used to process the SQL statements for the job. The job is changed to SQL SERVER MODE and all SQL statements run within this job, including those run by SQL iQuery, Embedded SQL, QM Query and JDBC shall use SQL SERVER MODE. When SQL SERVER MODE is active, IBM STRSQL is disabled for the job.
*NONE or *NO
SQL SERVER MODE is not started for this statement, however if SQL SERVER MODE is already started, it is used regardless of the setting for this parameter.
Top

Display Options (DSPOPTION)

Custom display options when OUTPUT(*) (display) is specified. This parameter is used only when OUTPUT(*) or equivalent is specified.

*NONE
Specifies that the full features are available. This is the default.
*NOSTMTVIEW
Specifies the "View SQL Statement" option is unavailable. This option is available via F14 (or shift+F2) and is disabled when this option is selected. You may also use #stmtView DISABLE in iQuery Scripts to accomplish the same setting.
*NOSTMTSAVE or *NOSAVE
Specifies the "Save SQL Statement" option is unavailable. This option is available via F2 and is disabled when this option is selected. You may also use #stmtSave DISABLE in iQuery Scripts to accomplish the same setting.
*NOFLDLIST or *NOLIST
Specifies the "List Fields" option is unavailable. This option is available via F4 and is disabled when this option is selected. You may also use #fieldList DISABLE in iQuery Scripts to accomplish the same setting.
Top

Column Totals (COLTOTAL)

When the SQL statement is a SELECT statement, this parameter identifies the columns to be accumulated and whose totals shall be printed. For example RUNIQRY SQL('SELECT CUSTNO, SALES FROM CUSTOMER') COLTOTAL(SALES) causes the SALES column to be accumulated and its total printed. Column names or relative column numbers may be specified. For example, in the previous example, the SALES column is the 2nd column on the SELECT statement. Therefore COLTOTAL(2) could have be used as an alias for the COLTOTAL(SALES) parameter, producing the same results.

*NONE
No columns are totaled.
column name or number
Specify the column (field) name whose value is accumulated and printed. Specify the relative column number whose value is accumulated and printed.
expression
An expression may be specified and it may reference the column totals. To identify another column in the expression, prefix it with an & symbol. For example COLTOTAL( 2 3 '4=(&3/&2)*100') This causes the system to use the totals from columns 2 and 3 in the calculation that is assigned to column 4. Note the column where the result of this expression appears is identified as the first value in the expression, and is followed by an equals sign. Natural expressions are permitted but there are no built-in functions only + - * / % and ^ symbols are permitted. The percent sign is used for "remainder" function as in '3=5%2' which yields 1. The "caret" is used for powers, such as '3=5^2' which yields 25.
Top
Top
Top

Source stream file (SRCSTMF)

Specifies the name of a file on the IFS that contains the SQL statement to be run. This parameter is required when SQL(*STMF) is specified.

character-value
Specify the IFS text file that contains the SQL statement to be run. The IFS file is read and sent to the SQL iQuery processor to run the SQL statement.
Top

SQL Source file name (SRCFILE)

Specifies the source file name that contains the member name (specified on the SRCMBR parameter) that contains the SQL statement to be run. This parameter is required when SQL(*SRCF or *SRCFILE) is specified.

Qualifier 1: SQL Source file name

QSQLSRC
The name of the source file is QSQLSRC.
name
Specify the name of the source file that contains the member name specified on the SRCMBR parameter.

Qualifier 2: Library

*LIBL
The library list is searched for the SQL source file.
*CURLIB
The job's current library contains the SQL source file. Note: The system uses QGPL for *CURLIB when no *CURLIB has been set by the job.
name
Specify the name of library that contains the SQL source file.
Top

SQL Source member (SRCMBR)

Specifies name of the SQL Source File Member that contains the SQL statement to be run by SQL iQuery. This parameter is required when SQL(*SRCF or *SRCFILE) is specified.

*FIRST
The first member in the SQL source file specified on the SRCFILE parameter is read and processed by the RUNIQRY command.
*FILE
The sourc file name specified on the SRCFILE parameter is used as the source member name.
name
Specify the name of source member that contains the SQL statement to be read and processed by the Run SQL using iQuery command.
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 iQuery 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 iQuery 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 path or path & Stmf Name (STMF)

Specifies the name of the IFS folder and optionally the IFS file name where the output is sent when OUTPUT(*PDF) or OUTPUT(*TEXT) is specified. To use just the STMF parameter to identify the path and IFS output file, be sure to specify STMFNAME(*SMTF).

*HOME
The stream file folder is the user's home folder. The actual IFS file name is specified on the STMFNAME parameter.
*CURRENT
The current working directory is used as the location for the streamfile specified on the STMFNAME parameter.
path-name
Specify the path name where the output for PDF and TEXT IFS files are to be written. The actual file name may be included here or specified on the STMFNAME parameter.
Top

Stream file name or *STMF (STMFNAME)

Specifies the name of the stream file being created for any of the myriad stream file output formats, such as Excel, PDF, JSON, text, etc.

*SPLFNAME
The value specified on the SPLFNAME parameter is used as the file name.
*STMF
The stream file name is included on the STMF parameter When a user specifies the file name with the path name on the STMF parameter, be sure to specify *STMF for STMFNAME (this parameter) otherwise the output will not be stored where the users expects.
*FILE
The base file name is used as the Stream file name. If not base file name is identified, then the default SQL iQuery name is used.
*LIBFILE
The base library name (i.e., the Schema) and the base file name (i.e., the Table) are combined and used as the stream file name.
*USRDTA
The USRDTA (user data) parameter is also used as the stream file name. Any embedded blanks in the USRDTA are translated to underscore symbols.
stmf-name
Specify the stream file name where the output from the Run SQL using iQuery command is written.
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 iQuery command is written to the stream file, which is re-created.
*ADD
The output from the Run SQL using iQuery 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

Write HTTP Header (STMFHDR) - Help

Controls whether or not to write the Content-Type header to the output stream file when *JSON or *HTML output options are selected.

*NO
No content-type header is written.
*YES
The Content-Type header is written to stream file when OUTPUT(*JSON | *HTML) is selected. This parameter is ignored otherwise.
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

Excel Sylk Options (SLKOPT)

Specifies custom options when writing an Excel-compatible Sylk file. The SLKOPT parameter consists of 4 options:

Target Platform (Windows or Other) so that the correct SLK linefeed can be generated. Windows uses a colon for a SLK linefeed, while other platforms, such as Mac OS X and Linux use an equals sign.

Column Headings Font can be specified to control the font used for the individual column headings. This originally applied to Column Headings only, but has recently been enhanced to include the 3-lines of User-specified "document" headings.

Body Content Font can be specified to control the font used for the body of the Spreadsheet being created.

Footer Font can be specified to control the font used for "footers" such as Column Totals created by the COLTOTALS parameter.

Top

Excel Options (XLSOPT)

Specifies custom options when writing Excel files. The XLSOPT parameter consists of these options:

Target Platform (Windows, Mac, or Other) so that the correct XLS linefeed can be generated. Windows uses a X'13' while Mac/Apple uses X'10'.

Column Headings and Titles receive the linefeed too break heading lines.

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 2COZIQ4, then "QPIQUERY" 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.
*JOB
The name of the job running the RUNIQRY command is used as the spool file name.
*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

SPOOL UserData (USRDTA)

Specifies user data for the SPOOL file when OUTPUT(*PRINT *PDF or *TEXT) is specified.

*DFT
The user data is retrieved from the SQL iQuery Default user space. If no user space is located, the program name (QRYFPRINT) is used as the user data.
*SPLFNAME
The value specified for the SPLFNAME parameter is also used as the USRDTA.
*JOB
The name of the job running the RUNIQRY command is used as the user data.
character-value
Specify the user data attribute for the SPOOL file. Standard USRDTA values may be specified, which means anything is valid, including embedded blanks.
Top

Page size (PAGESIZE)

Specifies page size when OUTPUT(*PRINT) is specified.

Single values

*AUTO
SQL iQuery attempts to determine the best page dimensions for the selected output. If the data can fit within a classic North American 8.5 x 11.0 inch page, it is printed in portrait mode. If it is too wide for an 8.5 inch wide page, the output is auto-rotated to landscape mode. NOTE: The characters per inch (CPI) parameter is also adjust when excessively wide data is detected.

Element 1: Page length (height)

11
The default measurement is in inches, the page height is 11 inches. The page height (how tall the page will be) is set by this value.
0.0-255.0
Specify the page height in the unit of measure value (UOM parameter). By default SQL iQuery uses Inches as the UOM.

Element 2: Page width

8.5
The default measurement is in inches, the page width is 8.5 inches. The page width (how wide the page will be) is set by this value.
0.0-378.0
Specify the page width in the unit of measure value (UOM parameter). By default SQL iQuery uses Inches as the UOM.

Element 3: Unit of measure method

*UOM
IBM i print files may be set up in ROW/COLUMN measurement or the more contemporary *UOM measurement. It is recommended to only use *UOM here and specify Inches UOM(*INCH) or Centimeters UOM(*CM) for the measurement type.
*INCH
The Unit of Measure method and Unit of Measure (the UOM parameter) are set to inches. When PAGESIZE(h w *INCH) is specified the UOM parameter is ignored.
*CM
The Unit of Measure method and Unit of Measure (the UOM parameter) are set to centimeters. When PAGESIZE(h w *CM) is specified the UOM parameter is ignored.
*ROWCOL
When *ROWCOL is specified, the height and width of the page is represented as rows (number of lines per page) and columns (number of charaters across the page). This option is only provided for legacy applications and should be avoided.
Top

Lines per inch (LPI) (LPI)

Specifies the number of printed lines per inch. This measurement is always in lines-per-inch regardless of the UOM setting or the *UOM/*ROWCOL element of the PAGESIZE parameter. Most applications can use LPI(8) or LPI(6) but they are not the only supported values.

8
The default for LPI is eight lines per inch.
3.0 4.0 6.0 7.5 8.0 9.0 or 12.0
Specify one of the supported lines per inch for the IBM i system.
Top

Characters per inch (CPI) (CPI)

Specifies the characters per inch across the printed page. This parameter is only valid when OUTPUT(*PRINT) is specified.

*AUTO
The characters per inch are determined by SQL iQuery. Normally 10 characters per inch is used unless the data cannot fit within the page size specified on the PAGESIZE parameter.
5 10 12 13.3 15 16.7 18 20
Specify the characters per horizontal inch. This setting impacts the FONT that is used to print the data.
Top

Overflow line (OVRFLW)

Specifies the line number that triggers SQL iQuery to print the next line on the next page. The classic "Overflow line number".

*AUTO
SQL iQuery uses a 1/2 inch (or 1 CM) margin at the bottom of the page to automatically set the overflow line number.
integer
Specify the number of overflow line. This value must be great than 1 and less than or equal to the maximum number of printed lines on the page. The number of lines per page can be calculated by multplying the LPI parameter by the page height element of the PAGESIZE parameter.
Top
Top
Top

Degree of page rotation (PAGRTT)

Specifies how to control auto-page rotation when printing in landscape mode. Specifies the degree of rotation for the output sent to the page, relative to how the page is stored in the printer.

*AUTO
Indicates that automatic rotation is performed in order to fit the printed output on the page size specified on the PAGESIZE parameter. If rotating the output cannot fit the content onto the page, then Computer Output Reduction or PAGRTT(*COR) is performed automatically.
*DEVD
SQL iQuery sends the printer's default rotation value to the printer. If the printer does not support rotation then unpredictable results may occur.
*COR
Computer Output Reduction. This allows output intended for a 13.2-inch wide by 11.0-inch height page to be printed on an 8.5-inch wide by 11.0-inch height form.
0 90 180 270
Specify the degrees of rotation for the output.
Top

Unit of measure (UOM)

Specifies the Unit of Measure (Inches or Centimeters) used on the PAGESIZE parameter. When PAGESIZE(h w *INCH or *CM) is specified, this parameter is ignored.

*INCH
The PAGESIZE is specified as inches.
*CM
The PAGESIZE is specified as centimeters.
Top

Maximum SPOOL File Records (MAXRCDS)

Specifies, for spooled output only, the maximum number of records that can be written to the SPOOL file for the iQuery printer file. This parameter overrides the value specified in the printer file or in other called OVRPRTF commands.

*DFT
The default SPOOL file MAXRCDS (normally 20,000) is used. SQL iQuery uses 40,000 for the default instead of 20,000. If a spearate OVRPRTF command was applied to the print file QPRINT then that value overrides the default.
1 to 99999999
The maximum number of printed lines permitted in the SPOOL file. When this value is reached, CPA4072 is issued to job or QSYSOPR and the user can respond with the standad NOMAX or 1-999999 option.
*NOMAX
No maximum number of printed lines is set. The output will continue until all resultSet rows are processed and printed.
Top

Output queue (OUTQ)

When OUTPUT(*PRINT) is specified, the output queue name where the SPOOL file is created, is specified on this parameter.

Single values

*JOB
The output queue associated with the job running SQL iQuery is used as the output queue.
*DEV
The output queue associated with the print device associated with this job is used as the output queue.

Qualifier 1: Output queue

name
Specify the name of an output queue where the SPOOL file will be created.

Qualifier 2: Library

*LIBL
The library list is searched for the output queue name.
*CURLIB
The current library contains the output queue. If there is no current library associated with the job running SQL iQuery, then QGPL is used.
name
Specify the name of library where the output exists.
Top
Top
Top
Top
Top

Output Title Line 1 (APPTITLE)

Specifies the first line of the output headings.

*DFT
The SQL iQuery Default value is used for this parameter. See the CHGQRYF (Change SQL iQuery) command.
character-value
Specify up to 50 characters of title/headings for the first line of output.
*SRCTEXT
If SQL(*SRCF or *SRCFILE) is specified, the text description associated with the source member name specified on the SRCMBR parameter is used as the title.
Top

Output Title Line 2 (USRTITLE)

Specifies the second line of the output headings.

*DFT
The SQL iQuery Default value is used for this parameter. See the CHGQRYF (Change SQL iQuery) command.
character-value
Specify up to 50 characters of title/headings for the second line of output.
*SRCTEXT
If SQL(*SRCF or *SRCFILE) is specified, the text description associated with the source member name specified on the SRCMBR parameter is used as the title.
Top

Output Title Line 3 (RPTTITLE)

Specifies the third line of the output headings.

*DFT
The SQL iQuery Default value is used for this parameter. See the CHGQRYF (Change SQL iQuery) command.
character-value
Specify up to 50 characters of title/headings for the third line of output.
*SRCTEXT
If SQL(*SRCF or *SRCFILE) is specified, the text description associated with the source member name specified on the SRCMBR parameter is used as the title.
Top

Automatically Quote Text Values (AUTOQUOTE)

Specifies whether or not to force quotes or double-quotes around text values specified on the SETVAR parameter. When specified as AUTOQUOTE(*YES) text substitution value specified on the SETVAR parameter are inserted into the target SQL statement and are enclosed in approstrophies 'XX' or when *DBLQUOTE is specified double quotes "XX". When AUTOQUOTE(*NO) is sepcfied (the default) the text values are inserted directly into the SQL statement without quoting.

*NO
Automatic quoting of text fields is not performed.
*YES
Automatic quoting of text fields is performed.
*DBLQUOTE
Automatic quoting of text fields is performed with double-quotes used to enclosed the text values.
Top

Insert variable prefix (VARPREFIX)

Specifies whether or not to include an ampersand prefix on the variable identifiers/names specified on the SETVAR parameter. This allows variables to be specified as SETVAR((*AUTO FROMDATE)) and the SQL iQuery VARPREFIX(*AMP) SETVAR((FROMDATE '2014-04-30')) will cause runtime will search for &FROMDATE in the SQL statement. This is provided due to syntax issues with CL when including an ampersand on the CL command while in a CL program.

*AMP
If the variable name does not include an ampersand, SQL iQuery automatically inserts on for you.
*NONE
The variable name is used exactly as it is specified by the user.
character-value
Specify a prefix character to be added to the beggining of the variable name. For example, VARPREFIX('Q') SETVAR((FROMDATE '2014-04-30')) will cause SQL iQuery to use the variable QFROMDATE and replace it with the date specified.
Top

Runtime Substitution values (SETVAR)

Specifies one or more substitution values that are inserted into the SQL statement at runtime.

Single values

*NONE
No runtime substitution variables are specified for the SQL statement.

Other values (up to 64 repetitions)

Element 1: Variable identifier

*AUTO
When *AUTO is specified. SQL iQuery automatically generates the variable name/identifier using the sequencing: &1 &2 &3 etc.
character-value
Users may prefer to specify a named identifier rather than the default numeric sequence style identifiers. Specify the variable identifier that is searched for in the SQL statement. The variable is searched by prepending the symbol specified on the VARPREFIX parameter. By default VARPREFIX(*AMP) embeds an ampersand before the value you specify. For example, in SETVAR((REG 'MIDWEST')) the REG identifier become &REG when the search is performed. By omitting the '&' on the SETVAR parameter, the use of CL variables is simplified. For example: SETVAR((REG &REGION))

Element 2: Substitution Value

unrestricted-value
Specify the value or CL variable that contains the data to insert into the SQL statement where the variable name/identifier is located. Any value may be specified, literal or CL variable, character or numeric. If auto sequencing is NOT used, the values are matched up with their identifiers. When auto sequencing is specified, then the first value specified on this parameter is inserted into the SQL statement where the &1 identifier is specified; replacing the &1 identifier. The second value specified on this parameter is inserted into the SQL statement where the &2 identifier is specified; replacing the &1 identifier.

Identifiers may appear more than once per SQL statement. Up to 64 identfiers are allowed.

Element 3: Match Whole World Only

*YES or *WHOLEWORD
When *YES (the default) is specified, matching occurs only when the entire pattern can be found and that pattern is not part of larger word.

For example: SETVAR((REG 'XXX' *YES)) with an SQL statement that contains SELECT * FROM MYLIB.SALES WHERE REGION = '&REG' then the result is ... WHERE REGION = 'XXX' However if the SQL statement were: WHERE REGION = '&REGION' The replacement would not take place. This permits users to specify variables such as &SALES1 &SALES2 &SALES3... &SALES11 &SALES12 When WholeWordOnly is specified, the search and replace feature works as expected. if WholeWOrldOnly(*NO) is specified, then when replace &SALES1 with 'IBM' then &SALES11 and &SALES12 would also be replaced but the trailing digit would remain.

*NO
The substitution value is located in the SQL statement using a generic pattern matching routine and is replaced with the text or numeric value.

Using *NO allows users to build dynamic SQL statements that contain data regardless of the context. For example, suppose you have a database file that starts with a 3-characdter Region Code. Such as:

SELECT a,b,c, FROM MYLIB.MIDDATA

where the 'MID' in 'MIDDATA' would be any of several values. To allow users to write one query statement and then access the desired file, the SELECT statement could be modified as follows:

SELECT a,b,c FROM MYLIB.&REGDATA

Now when RUNIQRY is run and SETVAR((REG &REGION *NO)) is specified, the '&REG' in the SQL statement is translated to whever value is passed in from the &REGION CL variable. For example: If &REGION = 'NYC' then the resulting SQL statement would be:

SELECTD a,b,c FROM MYDATA.NYCDATA

Top

EMAIL (EMAIL)

Specifies list of email addresses to which the generated output (IFS file) is sent. Each email address may be up to 128 characters in length. To extend the length of the prompted email address, type an ampersand & into the first position of the email address, followed by one or more blanks, then press Enter. The CL prompter will extend the input field for you. This parameter is valid when an IFS-file is created from the resultSet. IFS files are created when a stream file is produced, such as *PDF, *EXCEL, *CSV, *TEXT, *JSON, *HTML, etc. The email command that is used is retrieved from the iQuery XML config file config.xml that is located on the IFS in this location:

 /home/iquery/config/config.xml

SQL iQuery uses the Cozzi Productions free SENDMAIL email command or the IBM-supplied Send Email Message using SMTP (SNDSMTPEMM) command.

*NONE
No email addresses are specified.
email address
Specifies one or more email addresses to which the generated IFS file is sent as an attachment.
Top

EMAIL Subject (SUBJECT)

Specifies the optional email message Subject Line. Up to 255 characters may be specified. The default is no Subject text.

Top

EMAIL Prompt (EMAILPMT)

Specifies whether or not to prompt the EMAIL CL command that is used to email the resultSet to the end-user.

*NO
The email CL command is run without prompting.
*YES or *PROMPT
The email CL command is prompted after the resultSet is created, allowing the end-user to modify the email command parameters or add a message body/text to the email itself.
Top

EMAIL Command (EMAILCMD)

Allows the user of the RUNiQRY command to override the default EMAIL CL command that is configured in the config.xml file on the IFS.

*DFT
The email CL command run is the same one configured in config.xml
*IBM
The email CL command run is the IBM-supplied SNDSMTPEMM command.
*SENDMAIL or *COZZI
The email CL command run is the SENDMAIL command supplied by Cozzi Productions, Inc. It may be downloaded free from the SQL iQuery website: http://www.SQLiQuery.com/downloads
Top

Examples for RUNIQRY

Example 1: Simple Command Example

RUNIQRY  'SELECT * FROM QIWS/QCUSTCDT'

This command queries the example QCUSTCDT file in library QIWS (provided by IBM Corp.) It returns to the display, a list of all the records and all of the file's fields in arrival sequence.

Example 2: More Complex Command Example

RUNIQRY  'SELECT cusnum, lstnam, city, baldue
          FROM qiws/qcustcdt
          order by BALDUE DESC'

This command lists all the records in the QCUSTCDT file in descending balance due order. It output includes only the Customer Number, Last Name, City and Balance Due fields.

Example 3: SQL iQuery Output to PDF

RUNIQRY  'SELECT cusnum, lstnam, city, baldue
          FROM qiws/qcustcdt
          order by BALDUE DESC' OUTPUT(*PDF)

This command performs the same query as Example 2, however the output from it is redirected to a PDF file on the IFS. By default the file is created in the user's Home Directory with the name QRYFPRINT.PDF (but that name may be overridden by the user)/

Top

Error messages for RUNIQRY

*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