Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
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:
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, *CURLIB | 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 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 |
EMAILEMPTY | Email empty resultSet | *NO, *YES | Send email when resultSet is empty. |
Top |
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.
RUNiQRY *SALESRPT
Top |
Specifies output from an SQL SELECT statement. If the SQL parameter is not a SELECT statement, this parameter is ignored.
Top |
Specifies the automatic commitment control for non-SELECT statements. This parameter is ignored if the SQL parameter is a SELECT statement.
Top |
Select whether the SQL naming convention or the IBM i object naming convention is specified for the SQL statement specified on the SQL parameter.
On recent versions and technology refreshes of IBM i, we have noticed that regardles of the setting for this parameter, either *SQL or *SYS syntax may be used on the SQL statement. But both may not be used concurrently. This may indicate that the setting for this parameter is being ignored on later releases of IBM i.
Top |
Specifies 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.
Top |
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.
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 |
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.
Top |
Specifies password for the user profile specified on the USER parameter. Passwords up to 18 characters in length may be specified.
Top |
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.
Top |
Specifies the date format used for date variables returned by a SELECT statement.
Top |
Specifies separator used when the DATFMT parameter is any of the following: *MDY *YMD *DMY otherwise this parameter is ignored.
Top |
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.
Top |
Specifies the separator used to separate the components of the time.
Top |
Top |
Top |
Top |
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)
Maximum Scale (decimals)
Minimum Divide Scale (decimals)
Top |
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.
Element 2: OPTIMIZE FOR.
Specifies the number of rows to optimize for when OUTPUT(*) is sepcified for a SELECT statement.
Top |
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.
Top |
Custom display options when OUTPUT(*) (display) is specified. This parameter is used only when OUTPUT(*) or equivalent is specified.
Top |
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.
Top |
Top |
Top |
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.
Top |
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
Qualifier 2: Library
Top |
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.
Top |
Specifies name of a new or existing DB2 for i database file that receives the rowset data from an SQL SELECT statement. This parameter is only valid with an SQL SELECT statement or a CTE.
Qualifier 1: Output file
Qualifier 2: Library
Specify the library name where the output file is located or will be located (if it is being created).
Top |
Specifies an output member for the OUTFILE. SQL largely ignores members in database tables. This parameter is reserved for future use.
Top |
Specifies whether to replace or add records in the output file member.
Top |
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).
Top |
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.
Top |
Specifies whether to clear or add to an existing stream file.
Top |
Specifies whether to create the folders that contain the stream file. SQL iQuery creates the entire folder directory tree for you when STMFDIROPT(*CREATE) is specified.
Top |
Specifies CCSID used when creating and translating the data being written to the IFS file specified on the STMF and STMFNAME parameters.
Top |
Controls whether or not to write the Content-Type header to the output stream file when *JSON or *HTML output options are selected.
Top |
Specifies options to control how comma separated files are created.
Element 1: Headings
Note that column headings are not enclosed in quotes.
Element 2: Trim blanks
Specifies whether or not trailing and leading blanks are removed.
Element 3: Quote symbol
Specifies the symbol (character) used to enclose non-numeric fields.
Element 4: Escape symbol
Specifies the symbol inserted before the Quote symbol when that quote symbol is detected within the data.
Element 5: Separator symbol
Element 6: Null value symbol
Element 7: End of line symbol(s)
Element 8: Prefix for Character Fields
Use this parameter to insert something like ="..." around text (character) output to "help" Excel display the data more correctly
Top |
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 |
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 |
Specifies name of the SPOOL file when OUTPUT(*PRINT *PDF or *TEXT) is specified.
Top |
Specifies user data for the SPOOL file when OUTPUT(*PRINT *PDF or *TEXT) is specified.
Top |
Specifies page size when OUTPUT(*PRINT) is specified.
Single values
Element 1: Page length (height)
Element 2: Page width
Element 3: Unit of measure method
Top |
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.
Top |
Specifies the characters per inch across the printed page. This parameter is only valid when OUTPUT(*PRINT) is specified.
Top |
Specifies the line number that triggers SQL iQuery to print the next line on the next page. The classic "Overflow line number".
Top |
Top |
Top |
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.
Top |
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.
Top |
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.
Top |
When OUTPUT(*PRINT) is specified, the output queue name where the SPOOL file is created, is specified on this parameter.
Single values
Qualifier 1: Output queue
Qualifier 2: Library
Top |
Top |
Top |
Top |
Top |
Specifies the first line of the output headings.
Top |
Specifies the second line of the output headings.
Top |
Specifies the third line of the output headings.
Top |
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.
Top |
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.
Top |
Specifies one or more substitution values that are inserted into the SQL statement at runtime.
Single values
Other values (up to 64 repetitions)
Element 1: Variable identifier
Element 2: Substitution Value
Identifiers may appear more than once per SQL statement. Up to 64 identfiers are allowed.
Element 3: Match Whole World Only
For example: SETVAR((REG 'XXX' *YES)) with an SQL statement that contains SELECT * FROM MYLIB.SALES WHERE REGION = '®' then the result is ... WHERE REGION = 'XXX' However if the SQL statement were: WHERE REGION = '®ION' 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.
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.®DATA
Now when RUNIQRY is run and SETVAR((REG ®ION *NO)) is specified, the '®' in the SQL statement is translated to whever value is passed in from the ®ION CL variable. For example: If ®ION = 'NYC' then the resulting SQL statement would be:
SELECTD a,b,c FROM MYDATA.NYCDATA
Top |
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.
Top |
Specifies the optional email message Subject Line. Up to 255 characters may be specified. The default is no Subject text.
Top |
Specifies whether or not to prompt the EMAIL CL command that is used to email the resultSet to the end-user.
Top |
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.
Top |
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 |
*ESCAPE Messages
Top |