Change SQL iQuery Attributes (CHGIQRYA)

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

The Change SQL iQuery Attributes (CHGIQRYA) command allows you to specify the defaults for the RUNIQRY and RUNIQRYF commands. When no value is specified for a parameter, these default values are used.

Top

Parameters

Keyword Description Choices Notes
APPTITLE Output Title Line 1 Character value, *DFT Optional, Positional 1
USRTITLE Output Title Line 2 Character value, *DFT Optional, Positional 2
RPTTITLE Output Title Line 3 Character value, *DFT Optional, Positional 3
COMMIT Commitment control *CHG, *UR, *CS, *ALL, *RS, *NONE, *NC, *RR, *AUTO Optional, Positional 4
NAMING Naming *SYS, *SQL Optional, Positional 5
DATFMT Date format *ISO, *USA, *EUR, *JIS, *MDY, *DMY, *YMD, *JUL, *JOB, ISO, USA, EUR, JIS, MDY, DMY, YMD, JUL, JOB Optional, Positional 6
DATSEP Date separator *SLASH, *DASH, *PERIOD, *COMMA, *BLANK, /, -, ., ,, , *JOB Optional, Positional 7
TIMFMT Time format *ISO, *USA, *EUR, *JIS, *HMS, ISO, USA, EUR, JIS, HMS Optional, Positional 8
TIMSEP Time separator *COLON, *PERIOD, *COMMA, *BLANK, :, ., ,, , *JOB Optional, Positional 9
OPTIMIZE Optimization *FIRSTIO, *ALLIO Optional, Positional 10
DECPOINT Decimal point *PERIOD, *COMMA, *JOB, '.', ',', * Optional, Positional 11
NULLCHAR Null field symbol Character value, *PERIOD, *PLUS, *MINUS, *ASTERISK, '+', '-', *, '.' Optional, Positional 12
DDECHAR Decimal Data Error symbol Character value, *PERIOD, *PLUS, *MINUS, *ASTERISK, '+', '-', *, '.' Optional, Positional 13
SPLFNAME SPOOL File name Name, *DFT Optional, Positional 14
USRDTA SPOOL File User data Character value, *DFT Optional, Positional 15
LPI Lines per inch (LPI) 8, 3.0, 4.0, 6.0, 7.5, 8.0, 9.0, 12.0 Optional, Positional 16
CPI Characters per inch (CPI) *AUTO, 5.0, 10.0, 12.0, 13.3, 15.0, 16.7, 18.0, 20.0 Optional, Positional 17
PAGESIZE Page size Element list Optional, Positional 18
Element 1: Page length (height) 0.0-255.0
Element 2: Page width 0.0-378.0
Element 3: Unit of measure method *UOM, *ROWCOL, *INCH, *CM
OVRFLW Overflow line Integer, *AUTO Optional, Positional 19
UOM Unit of measure *INCH, *CM Optional, Positional 20
PAGRTT Degree of page rotation *REDUCE, *AUTO, *DEVD, *COR, 0, 90, 180, 270 Optional, Positional 21
MSGF Message file (language) Qualified object name Optional, Positional 22
Qualifier 1: Message file (language) Name, COZLANGEN
Qualifier 2: Message library Name, IQUERY, *LIBL
EMAILCMD Email command to run (if any) Command string Optional, Positional 23
Top

Output Title Line 1 (APPTITLE)

Specifies the first line of output headings for print and print-related output.

*DFT
The internal default value is used for this parameter.
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 title text for the second line of the output media. Used when OUTPUT(*PRINT) or OUTPUT(*) is specified.

*DFT
The shipped applicationd default for this parameter is used.
character-value
Specify the new default value to appear as the Title on the output media.
Top

Output Title Line 3 (RPTTITLE)

Specifies the title text for the third line of the output media. Used when OUTPUT(*PRINT) or OUTPUT(*) is specified.

*DFT
The shipped applicationd default for this parameter is used.
character-value
Specify the new default value to appear as the Title on the output media.
Top

Commitment control (COMMIT)

*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 run, *NONE or *NC must be used. If a 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. It is recommended that *NONE or *NC be used when specifying a SELECT statement.
*CHG or *UR
Specifies the objects referred to in SQL ALTER, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, and REVOKE statements and the row updates, deletes, and inserts are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs can be seen.
*CS
Specifies the files referred to in SQL ALTER, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, and REVOKE statements and 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 rows that are 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)

Specifies SQL naming convention used within the SQL statements.

*SYS or *IBMi
The IBM i naming convention of LIBRARY/FILE is being used.
*SQL
The SQL standard naming convention of LIBRARY.FILE is being used. This naming standard is required when using 3-level naming, such as: SELECT * FROM rmtSysName.rmtLibrary.file
Top

Date format (DATFMT)

Specifies the default date format for Date fields returned by SQL iQuery when the SQL statement is a SELECT statement.

*ISO
Dates are returned as yyyy-mm-dd
*USA
Dates are returned as mm/dd/yyyy
*EUR
Dates are returned as dd-mm-yyyy
*JIS
Dates are returned as dd.mm.yyyy
*MDY
Dates are returned as mm/dd/yy
*DMY
Dates are returned as dd/mm/yy
*YMD
Dates are returned as yy/mm/dd
*JUL
Dates are returned as yyyy/ddd
*JOB
Dates are returned in the format specified on the DATFMT job attribute.
Top

Date separator (DATSEP)

Specifies the date separator used for *MDY, *DMY, and *YMD format dates.

*SLASH
The forward slash symbol is used mm/dd/yy
*DASH
The dash or "hyphen" symbol is used mm-dd-yy
*PERIOD
The period is used mm.dd.yy
*BLANK
A blank is used: mm dd yy
*JOB
The DATSEP job attribute is used as the separator.
Top

Time format (TIMFMT)

Specifies the default Time format for Time fields returned by SQL iQuery when the SQL statement is a SELECT statement.

*ISO The ISO standard time format is used.
*USA The USA standard time format is used.
*EUR The EUR standard time format is used.
*JIS The JIS standard time format is used.
*HMS The time is returned as HH:MM:SS
Top

Time separator (TIMSEP)

Specifies the separator used for time values.

*COLON
The Colon symbol is used HH:MM:SS
*PERIOD
The Period symbol is used HH.MM.SS
*COMMA
The Comma symbol is used HH,MM,SS
*BLANK
The Blank is used HH MM SS
*JOB
The TIMFMT job attribute symbol is used as the time format separator.
Top

Optimization (OPTIMIZE)

Specifies the level of optimization to suggest to the SQE engine. This weighs heavily in the query processing but may be ignored if an alternative is determined to be a better option.

*FIRSTIO
For OUTPUT(*) or OUTPUT(*PRINT) the query is optimize for the first I/O operation. This option is strongly recommneded when OUTPUT(*) is sepcified. For other types of output, this option is ignored.
*ALLIO
The query is optimize for all I/O. The entire query result set is produced before control returns to the SQL iQuery application. This is recommended for all OUTPUT options except OUTPUT(*).
Top
Top
Top
Top

SPOOL File name (SPLFNAME)

Specifies the SPOOLED FILE name when OUTPUT(*PRINT) is specified. The default name of SPOOLED FILES is normally QRYFREPORT.

*DFT
The name QRYFREPORT is used for SPOOL file names.
name
Specify the name of the SPOOL file to create when OUTPUT(*PRINT) is specified.
Top

SPOOL File User data (USRDTA)

Specifies the user data value to included in the SPOOLED FILE produced when OUTPUT(*PRINT) is specified.

*DFT
Typically this is either QRYFREPORT or the name of the file being queried. When RUNIQRY is is specified and no USRDTA parameter is specified, then if this value is *DFT, RUNIQRY attempts to use the database file name as the USRDTA parameter when OUTPUT(*PRINT) is specified.
character-value
Specify the default user data for SPOOLED FILEs created by SQL iQuery.
Top

Lines per inch (LPI) (LPI)

Specifies the number of lines per inch for Print Files.

8
Eight lines per inch are used to produce SPOOLED FILEs and PDF output.
3.0 to 12.0
Specify the Lines Per Inch to use when creating SPOOL or PDF files.
Top

Characters per inch (CPI) (CPI)

Specifies the characters per inch (across the page) when creating SPOOLED or PDF files.

*AUTO
SQL iQuery calculates the best CPI given the result set data width. When possible it tries to fit the data within the page margins. When the data width is large, you should not use *AUTO for this parameter.
10.0
The CPI(10) value is used for 10 characters per inch. This is the system default.
5.0 to 20.0
Specify the custom characters per inch. This setting controls the FONT used to create the SPOOL or PDF file.
Top

Page size (PAGESIZE)

Specifies the printed page dimensions. Specify the dimensions as "rows and columns", inches or centimeters.

Element 1: Page length (height)

0.0-255.0
Specifies the page height (length).

Element 2: Page width

0.0-378.0
Specifies the page width.

Element 3: Unit of measure method

*UOM
Specifies that the UOM parameter (*INCH or *CM) is to determine the page dimensions specified on this parameter. When *UOM is specified, the page size is specified in inches or centimeters.
*ROWCOL
Specifies that the page size is specified as rows and columns. That is number of lines per page by number of columns per line.
Top

Overflow line (OVRFLW)

Specifies spcifies the line number (regardless of the UOM setting) that triggers the Overlow Indicator in the application. Once this line is printed, the app skips to the top of the next page and continues printing.

*AUTO
SQL iQuery sets the overflow line to 1/2 inch or 1 cm from the bottom of the page.
integer
Specify the number of line that, once printed, causes the overflow indicator to be set on for the print file.
Top

Unit of measure (UOM)

Specifies the unit of measure for the PAGESIZE parameter. When the third element of PAGESIZE is *UOM, this parameter is used to determine the measurements of the page size.

*INCH
The English or "imperial" unit of measure is used.
*CM
The metric system unit of measure is used.
Top

Degree of page rotation (PAGRTT)

Specifies the amount of page rotation to perform when sending the output to a Laser-like printer.

*AUTO
The page is automatically rotated 90 degrees if necessary.
*REDUCE
The page is reduced to fit on the page if the paper is oriented in such as way that the output would not fit on the page, normally.
*DEVD
*COR
<...>
0
<...>
90
<...>
180
<...>
270
<...>
Top
Top
Top

Examples for CHGIQRYA

Example 1: Simple Command Example

CHGIQRYA  KWD1(PARMVAL1)

This command <...>

Example 2: More Complex Command Example

CHGIQRYA   KWD1(PARMVAL1)  KWD2(PARMVAL2)
          KWD3(PARMVAL3)

This command <...>

Top

Error messages for CHGIQRYA

*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