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:

• You must have READ authority to the database file (table) to perform a SELECT statement query, and the appropriate authority for other operations.

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:

• SELECT - Database query
• INSERT - Add records (rows) to a file (table)
• UPDATE - Modify the contents of a file (table)
• DELETE - Delete database records (rows) from a file (table)
• CREATE - Create Tables, views, indexes.
• WITH - Common Table Expressions

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

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.

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.

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.

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.

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.

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.

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.

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.

*CURLIB

The current library is used as the current schema. If no current library exists for the job QGPL is used.

library-name

Specify the existing library to be used as the default relational database library for unqualified tables, files, etc.

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.

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.

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)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

EMAIL Subject (SUBJECT)

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

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.

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

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

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.