DB2 Server for VSE & VM: Interactive SQL Guide and Reference

SET 



>>-SET----| Group_1 |--| Group_2 |-----------------------------><
 
Group_1
 
   .---------------------------------------.
   V               .-ON--.                 |
|----+-AUTOCommit--+-----+--------------+--+--------------------|
     |             '-OFF-'              |
     |       .-UPper--.                 |
     +-CASE--+--------+-----------------+
     |       '-STRing-'                 |
     |             .-?----------.       |
     |       (1)   |       (2)  |       |
     +-CLAss-------+-class------+-------+
     |           .-hyphen----.          |
     +-CONTinue--+-----------+----------+
     |           '-character-'          |
     |        (1)   .-1------------.    |
     +-COPies-------+--------------+----+
     |              |         (3)  |    |
     |              '-integer------'    |
     |          .-0-------.             |
     +-COSTest--+---------+-------------+
     |          +-integer-+             |
     |          '-OFF-----'             |
     |          .-//./----------------. |
     '-DECimal--+---------------------+-'
                '-/thousands/decimal/-'


Notes:



  1. The parameters CLAss, COPies, and PRINTRoute are not applicable to
    VM.

  2. Output class wanted (letters from A to Z).

  3. Number of copies to be printed.

  4. A maximum of 20 characters can be used for null values.


 
Group_2
 
   .---------------------------------------------------------.
   V              .-RR--.                                    |
|----+-ISOLation--+-----+---------------------------------+--+--|
     |            +-CS--+                                 |
     |            '-UR--'                                 |
     +-LANGuage--+-language_name-+------------------------+
     |           '-langid--------'                        |
     |       .-?-----------.                              |
     +-NULL--+-------------+------------------------------+
     |       |        (4)  |                              |
     |       '-string------'                              |
     |                  .-132-----.           .-66------. |
     +-PAGEsize--WIDth--+---------+---LENgth--+---------+-+
     |                  '-integer-'           '-integer-' |
     |            (1)                                     |
     +-PRINTRoute-------+-DESTid--wkstat--+---------------+
     |                  +-TERMid--termid--+               |
     |                  +-TOUser--userid--+               |
     |                  '-SYSTem----------'               |
     |          .-CONTInue--.                             |
     +-RUNMode--+-----------+-----------------------------+
     |          +-STOP------+                             |
     |          '-CANCEL----'                             |
     |              .-2-------.                           |
     +-SEParator--+-+---------+---BLANKs--+---------------+
     |            | '-integer-'           |               |
     |            '-string----------------'               |
     |          .-20------.                               |
     '-VARChar--+---------+-------------------------------'
                '-integer-'


Notes:



  1. The parameters CLAss, COPies, and PRINTRoute are not applicable to
    VM.

  2. Output class wanted, (letters from A to Z).

  3. Number of copies to be printed.

  4. A maximum of 20 characters can be used for null values.

SET is an ISQL command that controls a number of functions.

When ISQL is started, the following operational characteristics are in force:

The current setting of these functions can be listed with the LIST command. Some of these settings may already have been changed by a PROFILE routine, or you may want to change the settings for your session in your PROFILE routine, which is run automatically when you start ISQL.

Notes:

  1. In the VM environment, the user must use the CP commands SPOOL and TAG to change the routing of the print output.

  2. When you are using DRDA protocol in the VSE or VM environment, the isolation level is set to CS.

AUTOCommit

specifies if commands are to be committed automatically. Unless otherwise specified, commands are committed automatically.

ON
specifies that changes to tables resulting from an SQL statement are committed automatically when the statement is processed. This is the default.

There is an exception to the automatic committing of changes. If the SQL statement is INSERT, UPDATE, or DELETE, and it affects more than one data row, the changes are not immediately committed. Instead, the system issues a message that lets you either commit the work, or cancel or rollback the changes. If you type CANCEL or ROLLBACK, the changes are not committed; if you type any other statement, the changes are committed.

OFF
specifies that changes are not to be committed to a table until a COMMIT statement is typed.

CASE
specifies if characters enclosed in single quotation marks are to be converted to uppercase. Unless otherwise specified, all characters typed from the keyboard are converted to uppercase.

If you specify that the characters are to be converted, SQL uses the conversion information from the SYSCHARSETS system catalog to handle the conversion.

UPper
specifies that all characters typed from the keyboard or a routine are converted to uppercase characters. This is the default.

STRing
specifies that all characters enclosed in single quotation marks typed from the keyboard or a routine are not converted to uppercase characters.

CLAss (DB2 Server for VSE only)

specifies the printer output class.

class
is the output class desired (a letter from A to Z).

?
specifies the default printer class of the system.

Unless otherwise specified, the default printer class for the system is used.
Note:Class cannot be specified for a CICS/VSE terminal printer.

CONTInue

specifies the continuation character.

character
is the continuation character to use for continuation of input lines.

Choose a character that is not normally the last character of a statement. Also, do not use a single or double quotation mark, a semicolon, or a blank. Unless otherwise set, the continuation character is the hyphen (-).

COPies (DB2 Server for VSE only)
specifies the number of copies for printed reports.

integer
is the number of copies that are to be printed when subsequent PRINT commands are issued.

Unless otherwise specified, the number of copies for printed reports is one. A maximum of 99 copies can be specified.
Note:The number of copies cannot be specified for a CICS/VSE terminal printer.

COSTest

specifies when the ISQL Query Cost Estimate (QCE) is displayed. Unless OFF is specified, the QCE message is always displayed.

OFF
specifies that the ISQL QCE message should not be displayed.

integer
specifies that the ISQL QCE message should be displayed. Replace integer with any number from 0 to 9999. The QCE is displayed if it is greater than integer.

While you cannot specify any number greater than 9999, the QCE message displays '>9999' to indicate the cost is greater than 9999.

For additional information about the ISQL Query Cost Estimate, refer to the DB2 Server for VSE & VM Database Administration manual.

DECimal

specifies the type of punctuation to use when displaying a decimal column.

/thousands/
is the thousands separator character.

Valid characters are a period, a comma, and a blank.

/decimal/
is the decimal separator character. Valid characters are a period and a comma.

The slash distinguishes the thousands separator character from the decimal separator character.

Unless otherwise specified, no thousands separator is used, and the decimal separator is a period.

Valid combinations of t and d are:
Thousands Separator Decimal Separator Example

(nothing)
,
.
(a blank)
.
.
,
,
1234.56
1,234.56
1.234,56
1 234,56

For example, assume the value 123456 is contained in a decimal column that was created with two decimal places. Then, specifying: 

   set decimal /,/./

provides the following punctuation when this field is displayed: 

   1,234.56

The DECIMAL keyword does not change how input is specified, only how output is displayed. For example, to reference the above number (1,234.56) in a column called SALES in a WHERE clause, you use: 

   where SALES=1234.56

ISOLation

specifies the isolation level placed on data when you read information from the application server. You specify the type of lock that the system places on the data. This is specifying the isolation level.

The isolation level specified sets the degree of independence one terminal user has from another terminal user.

For guidelines on using this setting, see Specifying the Isolation Level.

RR
requests the isolation level repeatable read.

This setting holds locks on the data you are using until a COMMIT or ROLLBACK is performed. These locks isolate the data from other users. No one can modify any rows you have read until your work has been committed or rolled back. Use this setting when it is important to keep data completely isolated. This is the default.
Note:For DB2 Server for VSE & VM, if you are using the DRDA protocol, the default isolation level is set to CS. If you specify any other setting, it will be ignored.

CS
requests the isolation level cursor stability. Isolation level cursor stability has meaning only for data in

public dbspaces with row and page level locking. The system locks individual rows or pages depending on the lock specified in ACQUIRE DBSPACE, ALTER DBSPACE, and LOCK statements. Use the CS setting to free the data you are reading as soon as possible.

UR
requests the isolation level uncommitted read. Isolation level uncommitted read has meaning only for data in

public dbspaces with row and page level locking. This setting applies only to read-only operations (SELECTs). For other operations (UPDATE, DELETE, and INSERT), the rules of CS apply. This setting reduces lock contention on data being read; however, data integrity may be compromised because read-only access to uncommitted data is allowed. Use the UR setting only when it is not necessary that the data you are reading be committed.

The isolation level specification affects the UPDATE, DELETE, INSERT with subselect form, and SELECT statements. The correct value to specify depends on what activity you are performing. Locking problems can be reduced if the isolation level can be set to cursor stability in your system.
Note:Read and update access to the catalog is performed with a repeatable read setting, regardless of how you set the isolation level. This access is activated by dynamic preprocessing of SQL statements and by SQL data definition statements such as CREATE, ACQUIRE, and GRANT. Your selects (and updates and deletes, if you are a DBA) against the catalog are performed according to the isolation level you set.

SET ISOLATION is acceptable only when the target application server is a local application server. Otherwise the isolation level of CS is assumed and the SET ISOLATION command will have no effect.

LANGuage
specifies the language in which online HELP and error messages are displayed. Operator messages are displayed in the national language of the application server.

language_name
is the language being specified; for example, French. The description of the language can be either the IBM-supplied description or the description chosen by your site. For example, your site may prefer to use Francais instead of French. Language-name can be up to 40 characters long.

langid
is a 5-character language identifier that can be specified instead of the language name. The language identifiers are:

AMENG
Mixed Case American English

UCENG
Uppercase American English

FRANC
French

GER
German

KANJI
Japanese

KOR
Korean

HANZI
Simplified Chinese

If the language you specify is not supported, the current language remains unchanged.

NULL

specifies the characters to be displayed in null fields. Unless otherwise specified, a question mark (?) is used.

string
specifies the characters (up to a maximum of 20) to use for null fields. Enclose the string in single quotation marks if it contains a blank.

For example, the following command: 

   set null empty

causes the word EMPTY to be displayed in all null fields.

PAGEsize

specifies the page size for printed output.

WIDth
specifies the width of the paper being used.

integer
is the number of characters that can fit on a line of the output paper. Unless otherwise specified, the page width is 132. You can specify values from 19 to 204.

Note:If you are sending your output to the terminal printer, you should set the page width to the printer width-1 to avoid spacing problems. For example, if the terminal printer width is 132, then set the width to 131 with the command SET PAGE WID 131.

LENgth
specifies the length of the page being used.

integer
is the number of printed lines that can fit on the output paper. Unless otherwise specified, the page length is 66. You can specify values from 9 to 32767. The maximum number of lines that can actually be printed on a page is 8 less than the length. Eight lines are reserved for top and bottom titles and margins.

PRINTRoute (DB2 Server for VSE only)

specifies where print output is to be sent.

DESTid
specifies that printed output is to be directed to the designated POWER remote workstation.

wkstat
is the ID of the desired remote workstation. wkstat can be any number from 0 to 250. When wkstat is 0, the printed output is to be directed to the system printer.

TERMid
specifies that printed output is to be directed to the designated CICS/VSE terminal.

termid
is the terminal identifier of the desired CICS/VSE terminal. The termid must be from one to four alphanumeric characters.

TOUser
spools the print output the same way it will spool the print output when the user enters PRINT TOUser userid

userid
is the VSE POWER user identifier of the user to whom the output is being spooled. An identifier cannot be longer than eight alphanumeric characters. If the userid is any number from 1 to 250, ISQL will spool the print output to the POWER remote workstation whome ID is the number specified.

SYStem
specifies that printed output is to be directed to the system printer.

RUNMode

specifies whether processing should continue when an error is detected in a routine. Unless otherwise specified, processing continues.

CONTInue
specifies that processing is to continue to the next command even if errors are detected in the routine. This is the default.

STOP
specifies that processing is to stop if an error is detected in the routine. No rollback is performed, and processing is terminated.

CANCEL
specifies that an internal CANCEL is to be issued if an error is detected. A rollback is issued internally.

SEParator

specifies the separation between columns. Unless otherwise specified, two blanks are used.

integer BLANKs
specifies the number of spaces to be displayed between columns. Replace integer with the number of blanks desired. The maximum number of blanks that can be specified is 254.

string
specifies the characters to be displayed between columns. Enclose the string in single quotation marks if it contains a blank. For example, if you want to draw a vertical line between columns, you type: 
   set separator ' | '

which places a blank, a vertical bar, and a blank between all columns. The string can be up to 254 characters long.

VARChar

specifies the display width of variable length columns.

integer
is the length desired. The maximum width that can be specified is 254. Unless otherwise set, the VARCHAR length is 20. Since only the first 20 characters of a variable length column are displayed, this command or a FORMAT VARCHAR is necessary to view those columns that are wider than 20 characters.

The SET command can be used with more than one keyword option, allowing you to set several operational characteristics with a single SET command. These characteristics are effective for the duration of your terminal session. In the following example, a single SET command sets operational characteristics for AUTOCOMMIT, NULL, and SEPARATOR: 

set autocommit on null 'no data' separator 2 blanks

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]