SET CLPPlus command

The SET CLPPlus command controls a session-level variable for the CLPPlus interface.

Important:
  • For each invocation of the SET command, you can specify only one parameter.
  • In a batch script, you can issue several SET commands in a series.

Invocation

You must run this command from the CLPPlus interface.

Authorization

None

Required connection

None

Command syntax

Read syntax diagramSkip visual syntax diagramSETARRAYSIZEinteger-valueAUTOCOMMITAUTOONOFFOFFALLIMMEDIATEstatement_countAUTOTRACEAUTOTONOFFTRACEONLYTRACEEXPLAINEXPSTATISTICSSTATBGCOLORcolorr,g,bCOLORcolorr,g,bCOLSEPcolumn-separatorDUPLICATESONOFFECHOONOFFENCODINGencoding-formatENVARSUBSTITUTIONENVARSUBSTONOFFESCAPEONOFFcharacterFEEDBACKFEEDONOFFrow-thresholdFLUSHFLUONOFFFONTname,style,sizeHEADINGHEADHEAONOFFHEADSEPHEADSLOGMODElogmode-valueJCCLOGMODEjcclogmode-valueLINESIZELINwidth-of-lineLOCALElocale_nameLOGPATHlog-pathLONGinteger-valueNEWPAGENEWPlines-per-pageNULLnull-stringNUMFORMATformat-stringNUMWIDTHOFFinteger-valuePAGESIZEPAGESlines-per-pageSQLCASESQLCMIXEDMIXUPPERUPLOWERLOPAUSEPAUONOFFPRESERVEWHITESPACETRUEFALSERECSEPONOFFRECSEPCHARcharacterSERVEROUTPUTONOFFSIZEUNLIMITEDnFORMATWRAPPEDWORD_WRAPPEDTRUNCATEDSQLPROMPTSQLPpromptTERMOUTTERMONOFFTIMINGTIMIONOFFTRIMOUTTRIMOONOFFTRIMSPOOLTRIMSONOFFUNDERLINEONOFFVERBOSEONOFFVERIFYVERONOFFWRAPONOFF

Command parameters

ARRAYSIZE integer-value
Defines the number of rows that are fetched at a time from the server. You can use this parameter to tune query performance. Valid values are 1 - 10000. The default value is 10.
AUTOCOMMIT | AUTO
Controls the commit behavior of SQL statements in CLPPlus. CLPPlus always automatically commits DDL statements.
ON | IMMEDIATE
Enables automatic commitment of SQL statements.
OFF
Disables automatic commitment of SQL statements except for DDL statements.
OFFALL
Disables automatic commitment of SQL statements.
AUTOTRACE | AUTOT
Controls the display of explain plans and statistics information for SQL statements in a CLPPlus session.
The AUTOTRACE parameter is supported on Db2® for z/OS®. The AUTOTRACE parameter is also supported on IBM® Informix®, with these restrictions:
  • The EXPLAIN option is supported only for SELECT statements.
  • If you specify the EXPLAIN, option, you must create and specify a default sbspace name for the SBSPACENAME configuration parameter in the ONCONFIG file. This sbspace name is used for creating BLOBs when an explain plan is created.
  • To retrieve statistics from an Informix server, you must have the Informix privilege or an equivalent privilege.
ON
Enables AUTOTRACE. If you set the AUTOTRACE parameter to ON, CLPPlus continues to display the explain information until the session ends or until you set the AUTOTRACE parameter to OFF.
OFF
Disables AUTOTRACE.
TRACEONLY | TRACE
Disables the display of query execution output.
EXPLAIN | EXP
Enables the display of the explain plan.
STATISTICS | STAT
Enables the display of the statistics for statements.
BGCOLOR color|r,g,b
Sets the background color in window mode.
color
Valid values for the color variable are as follows:
  • BLACK|black
  • BLUE|blue
  • CYAN|cyan
  • DARK_GRAY|darkGray
  • GRAY|gray
  • GREEN|green
  • LIGHT_GRAY|lightGray
  • MAGENTA|magenta
  • ORANGE|orange
  • PINK|pink
  • RED|red
  • WHITE|white
  • YELLOW|yellow
r,g,b
Sets an opaque RGB color with the specified red, green, and blue values. The valid range for the red, green, and blue values is 0 - 255. Any invalid value is treated as 255.
COLOR color|r,g,b
Sets the font color in window mode.
color
Valid values for the color variable are as follows:
  • BLACK|black
  • BLUE|blue
  • CYAN|cyan
  • DARK_GRAY|darkGray
  • GRAY|gray
  • GREEN|green
  • LIGHT_GRAY|lightGray
  • MAGENTA|magenta
  • ORANGE|orange
  • PINK|pink
  • RED|red
  • WHITE|white
  • YELLOW|yellow
r,g,b
Sets an opaque RGB color with the specified red, green, and blue values. The valid range for the red, green, and blue values is 0 - 255. Any invalid value is treated as 255.
COLSEP column-separator
Places the specified delimiter between columns in a table. The delimiter must be a character, which can be a special character or a space (the default value).
DUPLICATES
Controls the printing of duplicate column values for the break columns that you specify for the BREAK command.
ON
Enables the printing of duplicate column values.
OFF
Disables the printing of duplicate column values.
ECHO
Controls whether all commands are displayed in the standard output of the CLPPlus interface.
ON
Enables the display of commands.
OFF
Disables the display of commands.
ENCODING encoding-format
Controls the encoding format that is used in a CLPPlus session. You can set the encoding format in the window mode and non-window mode CLPPlus consoles. The default value is UTF-8.

When you set the encoding format in the non-window mode CLPPlus console, only the batch file that is read into the console and the output that is written to the spooled file use the specified encoding format. The non-window mode CLPPlus console might not process interactive command input or send output to standard output in the specified encoding.

The following list contains the valid encoding format values:
Big5, Big5-HKSCS, CESU-8, EUC-JP, EUC-KR, GB18030,
GB2312, GBK, hp-roman8, IBM-Thai, IBM00858, 
IBM00924, IBM01140, IBM01141, IBM01142, IBM01143,
IBM01144, IBM01145, IBM01146, IBM01147, IBM01148,
IBM01149, IBM037, IBM1026, IBM1047, IBM273, IBM277,
IBM278, IBM280, IBM284, IBM285, IBM290, IBM297,
IBM420, IBM424, IBM437, IBM500, IBM775, IBM850,
IBM852, IBM855, IBM857, IBM860, IBM861, IBM862,
IBM863, IBM864, IBM865, IBM866, IBM868, IBM869,
IBM870, IBM871, IBM918, ISO-2022-CN, ISO-2022-JP,
ISO-2022-JP-2, ISO-2022-KR, ISO-8859-1, ISO-8859-10,
ISO-8859-13, ISO-8859-14, ISO-8859-15, ISO-8859-16,
ISO-8859-2, ISO-8859-3, ISO-8859-4, ISO-8859-5,
ISO-8859-6, ISO-8859-7, ISO-8859-8, ISO-8859-9,
JIS_X0201, JIS_X0212-1990, KOI8-R, KOI8-U, KZ-1048,
PTCP154, Shift_JIS, TIS-620, US-ASCII, UTF-16,
UTF-16BE, UTF-16LE, UTF-32, UTF-32BE, UTF-32LE,
UTF-8, windows-1250, windows-1251, windows-1252,
windows-1253, windows-1254, windows-1255,
windows-1256, windows-1257, windows-1258,
windows-31j, windows-874, x-Big5-HKSCS-2001,
x-Big5-Solaris, x-compound-text, x-EUC-TW,
x-EUC_CN, x-EUC_JP_LINUX, x-eucJP-Open, x-IBM-udcJP,
x-IBM1006, x-IBM1025, x-IBM1027, x-IBM1041, 
x-IBM1043, x-IBM1046, x-IBM1046S, x-IBM1047_LF,
x-IBM1088, x-IBM1097, x-IBM1098, x-IBM1112, 
x-IBM1114, x-IBM1115, x-IBM1122, x-IBM1123, 
x-IBM1124, x-IBM1141_LF, x-IBM1153, x-IBM1166, 
x-IBM1351, x-IBM1362, x-IBM1363, x-IBM1363C, 
x-IBM1364, x-IBM1370, x-IBM1371, x-IBM1380, 
x-IBM1381, x-IBM1382, x-IBM1383, x-IBM1385, 
x-IBM1386, x-IBM1388, x-IBM1390, x-IBM1390A, 
x-IBM1399, x-IBM1399A, x-IBM16684, x-IBM16684A, 
x-IBM29626, x-IBM29626C, x-IBM300, x-IBM300A, 
x-IBM301, x-IBM33722, x-IBM33722A, x-IBM33722C, 
x-IBM420S, x-IBM4933, x-IBM720, x-IBM737, 
x-IBM808, x-IBM833, x-IBM834, x-IBM835, x-IBM836,
x-IBM837, x-IBM856, x-IBM859, x-IBM864S, x-IBM867,
x-IBM874, x-IBM875, x-IBM897, x-IBM921, x-IBM922,
x-IBM924_LF, x-IBM927, x-IBM930, x-IBM930A, x-IBM933,
x-IBM935, x-IBM937, x-IBM939, x-IBM939A, x-IBM942, 
x-IBM942C, x-IBM943, x-IBM943C, x-IBM947, x-IBM948,
x-IBM949, x-IBM949C, x-IBM950, x-IBM951, x-IBM954,
x-IBM954C, x-IBM964, x-IBM970, x-IBM971, x-ISCII91,
x-ISO-2022-CN-CNS, x-ISO-2022-CN-GB, x-iso-8859-11,
x-ISO-8859-6S, x-JIS0208, x-JISAutoDetect, x-Johab,
x-KOI8_RU, x-KSC5601, x-MacArabic, 
x-MacCentralEurope, x-MacCroatian, x-MacCyrillic, 
x-MacDingbat, x-MacGreek, x-MacHebrew, x-MacIceland,
x-MacRoman, x-MacRomania, x-MacSymbol, x-MacThai,
x-MacTurkish, x-MacUkraine, x-MS932_0213, 
x-MS950-HKSCS, x-MS950-HKSCS-XP, x-mswin-936,
x-mswin-936A, x-PCK, x-SJIS_0213, x-UTF-16LE-BOM,
X-UTF-32BE-BOM, X-UTF-32LE-BOM, x-UTF_8J,
x-windows-1256S, x-windows-50220, x-windows-50221,
x-windows-949, x-windows-950, x-windows-iso2022jp
ENVVARSUBSTITUTION | ENVVARSUBST
Controls whether the CLPPlus interface supports environment variable substitution.
ON
Enables environment variable substitution. This is the default value. CLPPlus treats all text that is prefixed by the dollar sign ($) character and text that is wrapped in percent sign (%) characters as environment variables and substitutes them with the associated values.
OFF
Disables environment variable substitution.
ESCAPE
Controls whether an escape character is set for use in the CLPPlus interface.
ON
Enables the default escape character "\".
OFF
Disables the currently defined escape character.
character
Enables the escape character with the value of character.
FEEDBACK | FEED
Controls the display of interactive information after you issue an SQL statement.
ON
Enables the display of interactive information. This is the default action.
OFF
Disables the display of interactive information.
row-threshold
Specifies the minimum number of rows that are required to enable feedback.
FLUSH| FLU
Controls whether the output buffer is accessible to external programs. The FLUSH parameter is still active while the output buffer is being appended to. The process creates extra processing requirements when you call statements or run commands, however.
ON
Makes the buffer accessible to external programs.
OFF
Prevents the buffer from being available to external programs.
FONT
Sets the font name, style, and size in window mode.
name
Specifies the font name to set. Possible values are as follows:
  • monospaced
  • sansserif
  • serif
  • A valid system font name
style
Specifies the font style. Possible values are as follows:
0
Plain text.
1
Bold text
2
Italic text.
3
Bold and italic text.
size
Specifies the font size. The accepted value is an integer.
HEADING | HEA
Determines whether column headings are displayed for SELECT statements.
ON
Enables the display of column headings.
OFF
Disables the display of column headings.
HEADSEP | HEADS
Sets the heading separator character that is used by the COLUMN HEADING command. The default character is a vertical bar (|).
LOGMODE logmode-value
Controls tracing and logging for the CLPPlus client layer and JDBC driver layer (JCC). You can specify one of the following values for the logmode-value variable:
CLPPLUS
Performs tracing and logging for the CLPPlus client layer only.
JCC
Performs tracing and logging for the JDBC client layer only.
BOTH
Performs tracing and logging for the CLPPlus client layer and JDBC client layer.
NONE
Disables all tracing and logging.
JCCLOGMODE jcclogmode-value
Specifies what JCC client layer features are traced, logged, or both. You can specify one of the following values for the jcclogmode-value variable. To specify a value for the jcclogmode-value variable, you must set the LOGMODE parameter to 1 or 2.
0
(TRACE_NONE)
1
(TRACE_CONNECTION_CALLS)
2
(TRACE_STATEMENT_CALLS)
4
(TRACE_RESULT_SET_CALLS)
16
(TRACE_DRIVER_CONFIGURATION)
32
(TRACE_CONNECTS)
64
(TRACE_DRDA_FLOWS)
128
(TRACE_RESULT_SET_META_DATA)
256
(TRACE_PARAMETER_META_DATA)
512
(TRACE_DIAGNOSTICS)
1024
(TRACE_SQLJ)
2048
(TRACE_XA_CALLS)
-1
(TRACE_ALL). By default, the -1 setting is used, meaning that all layers are traced.
LINESIZE | LIN width-of-line
Specifies the width of a line in characters. The valid range is 1 - 32767. The default value is 80.
LOCALElocale_name
Sets the name of the message locale to use in the CLPPlus environment.
LOGPATHlog-path
Sets the path of a file that is used to keep log records of traces that use the settings of the LOGMODE and JCCLOGMODE parameters.
LONG integer-value
Defines the number of characters that are displayed for large text objects such as CLOB and XML. The default value is 50. The valid range is 1 - 2147483647.
NEWPAGE | NEWP lines-per-page
Controls how many blank lines are printed after a page break. The value is an integer of 0 - 100. By default, the value is 1, which indicates that one blank line is printed after a page break. A value of 0 causes a form feed to be printed at the start of each new page.
NULL null-string
Sets the string of characters that is displayed for a null value in a column in the output buffer. The string can include spaces and special characters. By default, the string is set to a space. The use of quotation marks around the string has no affect on its value. The case of letters is maintained.
NUMFORMAT format-string
Sets the default format string that is used for displaying numbers. The supported formats are the same as those for COLUMN FORMAT format-string.
NUMWIDTH
Sets the default width that is used to display numbers. The default value is OFF.
PAGESIZE | PAGES lines_per_page
Sets the number of printed lines that fit on a page. The default is 25. Valid values are 0 and 2 - 50000.
PRESERVEWHITESPACE
TRUE
Retains any indentation for all SQL and PL/SQL syntax and blocks. Spacing is retained in both window and non-window mode. This behavior applies whether input is read from a file or interactively in CLPPlus. TRUE is the default setting.
FALSE
Trims spaces in all SQL and PL/SQL syntax and blocks.
SQLCASE | SQLC
Controls whether the characters in SQL statements that are transmitted to the server are converted to uppercase or lowercase letters.
MIXED | MIX
Specifies that a string of characters can contain uppercase and lowercase letters.
UPPER | UP
Specifies that a string of characters can contain only uppercase letters.
LOWER | LO
Specifies that a string of characters can contain only lowercase letters.
PAUSE | PAU
Determines whether to stop a process before each page break. If the output cannot be displayed in one page, you are prompted with the message Hit ENTER to continue before each page break.
ON
Pauses the display of output.
OFF
Displays the output without pausing.
RECSEP
Specifies whether the record-separating character that you set by using the RECSEPCHAR parameter is displayed after each record in the result set is printed.
ON
Prints the record-separating character following each record in the result set.
OFF
Does not print the record-separating character.
RECSEPCHAR character
Specifies a single record-separating character that is used with the RECSEP parameter.
SERVEROUTPUT
Specifies whether output messages from server-side procedures are retrieved and displayed on the client console.
ON
Specifies that server-side procedure output messages are retrieved and displayed.
OFF
Specifies that server-side procedure output messages are not retrieved and displayed.
SIZE
Specifies the number of characters that are displayed on the screen. Possible values are as follows:
UNLIMITED
The default value.
n
Specifies a particular number of characters, where n must be a positive integer.
FORMAT
Specifies the format style that is used to display server output in the console.
TRUNCATED
Truncates text that exceeds the line size.
WORD_WRAPPED
Enables text to overflow to the next line and does not split words across lines.
WRAPPED
Enables text to overflow to the next line as needed.
SQLPROMPT | SQLP prompt
Specifies the prompt in the CLPPlus interface. By default, the prompt is SQL>. The prompt must be a string, which can include special characters and spaces. The use of quotation marks around the string has no affect on its value; the case of letters is maintained.
TERMOUT | TERM
Determines whether output is displayed in the standard output of the CLPPlus interface.
ON
Displays the output on the screen.
OFF
Does not display output.
TIMING | TIMI
Controls whether elapsed time is displayed for each SQL statement after it is issued.
ON
Specifies that elapsed time is displayed.
OFF
Specifies that elapsed time is not displayed.
TRIMOUT | TRIMO
Controls whether trailing blank spaces are removed from the output before it is written to the console.
ON
Specifies that trailing blank spaces are removed.
OFF
Specifies that trailing blank spaces are not removed. This is the default.
TRIMSPOOL | TRIMS
Controls whether trailing blank spaces are removed from the spool output before it is written to the spool file.
ON
Specifies that trailing blank spaces are removed.
OFF
Specifies that trailing blank spaces are not removed. This is the default.
UNDERLINE
Specifies whether column headings are underlined.
ON
Specifies that column headings are underlined.
OFF
Specifies that column headings are not underlined.
USECURRENTDIRLOGPATH
Controls the value of LOGPATH.
ON
LOGPATH is set to <CurrentDirectory>/clpplus.log unless the user has explicitly set the value of LOGPATH.
OFF
Has no effect on LOGPATH.
VERBOSE
Determines whether all CLPPlus messages are printed to the console.
ON
Specifies that all CLPPlus messages are printed to the console.
OFF
Specifies that only a subset of messages is printed to the console. This is the default value.
VERIFY | VER
Determines whether the old and new values of an SQL statement are displayed when a substitution variable is encountered.
ON
Specifies that the old and new values are displayed.
OFF
Specifies that the old and new values are not displayed.
WRAP
Sets the default alignment that is used when displaying column values.
ON
Specifies that column values that exceed the column width are wrapped.
OFF
Specifies that column values that exceed the column width are truncated.