You will probably develop a standard style for formatting query results that will be consistent across queries. Given this standardization, you can specify some formatting at the beginning of a display terminal session. The formatting remains effective for the session, unless you change or override it.
The formatting that you can set in this fashion is listed below:
The number of copies and page size can also be set for the duration of a display terminal session.
| Note: | Formatting information can also be set up automatically every time you begin a DB2 Server for VSE & VM display-terminal session, by defining the information in a routine. For an explanation of the routines, refer to Profile Routines. |
ISQL gives you some control over what you see on your display. You can specify:
Furthermore, you can specify all of these features in one command and get a list of the current settings.
Punctuation for numeric fields refers to the use of periods, commas, and
blanks for the decimal and thousands separators. Valid combinations of
the decimal and thousands separators are:
| Thousands Separator | Decimal Separator | Example |
|---|---|---|
|
(nothing) , . (a blank) |
. . , , |
1234.56 1,234.56 1.234,56 1 234,56 |
You can set any of these combinations for the duration of a session. For example, set the thousands separator to a comma and the decimal separator to a period for the duration of the current session by typing:
set decimal /,/./
The character between the first two slashes represents the thousands separator; the character between the last two, the decimal separator. The slashes distinguish the thousands separator from the decimal separator in the command.
Now, observe how a number is displayed using these separators. Type the following statement:
select 1000 * acstaff - from proj_act - where projno = 'ma2112' and actno = 60 - and acstdate = '1982-01-01'
This SELECT statement displays a result similar to Figure 38.
Figure 38. A Query Result with a Formatted Numeric Field
+--------------------------------------------------------------------------------+ | EXPRESSION 1 | | -------------- | | 2,000.00 | | * End of Result *** 1 Rows Displayed ***Cost Estimate is 1*********************| +--------------------------------------------------------------------------------+
This punctuation remains in effect for all numeric columns until the end of this session. Your next session begins with the normal default (no thousands separator and a period for the decimal separator).
The valid punctuation combinations are set like this:
set decimal /,/./ set decimal /./,/ set decimal / /,/ set decimal //./ (nothing for thousands separator)
Using the ISQL SET command, you can set the number of blanks or the kinds of characters to be displayed between columns for the duration of a session. The syntax for this command is shown in the following diagram:
.-2-------.
>>-SET--SEParator----+-+---------+---BLANKs--+-----------------><
| '-integer-' |
'-string----------------'
|
This command can set the number of blanks displayed between the columns when an integer is used. For example, the following command causes five blanks to be displayed between columns:
set separator 5 blanks
You can set a character string to be displayed between columns. To display an asterisk between columns, type the following command:
set separator *
You can set the characters displayed for null fields for the duration of a session by typing a SET NULL command as illustrated in the following diagram:
.-?------.
>>-SET--NULL----+--------+-------------------------------------><
'-string-'
|
Replace string with the actual characters you want displayed. The maximum string length is 20 characters.
In addition to specifying the number of copies desired for printed reports on the PRINT command, you can specify the number of copies for all print requests you make during the current session. This command has the following syntax:
.-1--.
>>-SET--COPIES----+----+---------------------------------------><
'-n--'
|
Replace n with the number of copies required. The maximum number that can be specified is 99.
If you specify the number of copies on the PRINT command after also having specified it using a SET command, the PRINT command quantity is used for that print operation. All following PRINT commands use the quantity specified by the SET command unless they too include the COPIES keyword.
Defining the page size lets you place printed query results on various paper sizes. Page size is defined in terms of the number of characters that are to be printed on a line and the number of lines that are to be printed on a page.
Before defining the page size, consider the output paper size to be used and the printer characteristics (characters per inch on a line and number of lines per vertical inch) to ensure that your definition fits on the paper. For example, suppose the printer class you are going to use is set up to use 8-1/2 inch wide by 11 inch long paper, and the printer prints 10 characters to the inch horizontally and prints lines at 6 to the inch vertically. This would allow each line to contain 85 characters (8.5 x 10) and 66 lines to be on a page. The maximum page size would therefore be 85 characters wide and 66 lines in length. The maximum number of lines that can actually be printed on a page is 8 less than the length (8 lines are reserved for top and bottom titles and column headings).
Once set, the specified page size remains in effect for the duration of the display terminal session or until it is changed. The SET PAGESIZE command has the following format:
.-132-----. .-66------.
>>-SET--PAGEsize--WIDth----+---------+--LENgth----+---------+--><
'-integer-' '-integer-'
|
The specified width must be from 19 to 204 and the length must be from 9 to 32767.
When setting the page size, it is not necessary to specify both width and length. In addition, length and width can be specified in either order. The default value for page size is a width of 132 and a length of 66.
Messages and HELP text can be displayed in any of several national languages. If additional languages were installed on your system, you can use the SET command to display messages and HELP text in another language. Operator messages are displayed in the national language of the application server. The SET LANGUAGE command takes the following form:
>>-SET LANGuage----+-language_name-+---------------------------><
'-langid--------'
|
Messages can be displayed in one of the languages listed in the table in Figure 39.
Figure 39. Alternative Languages for Messages and HELP Text
| Language | Language ID |
|---|---|
| American English (mixed case) | AMENG |
| American English (uppercase) | UCENG |
| French | FRANC |
| German | GER |
| Simplified Chinese | HANZI |
| Japanese | KANJI |
Either the name of the language or the language ID can be used as the language identifier in the SET LANGUAGE command. For example, you could have messages displayed in French by typing:
set language franc
If your system uses french as the language name you could also type:
set language french
Languages for which there is no language ID can be set using the name of the language. If your system does not support the language you request, an error message is displayed in the default language.
To find out what languages are available on your system (and what language names or language identifiers you can use to select a language) type:
select * from sqldba.syslanguage
A table is displayed listing the names, language IDs, and a brief description of each language available to you.
You can use the SET command with more than one keyword. In this way, you can specify or change multiple characteristics to be effective for the duration of your display session. For example, type the following command to combine several features that you set earlier using separate commands:
set autocommit on null *null* separator 2 blanks
This command sets AUTOCOMMIT to on, display *NULL* for each null field, and produces a display with columns separated by 2 blanks.
The range of characteristics you can set with the SET command is included in Chapter 10, ISQL Commands.
The current settings of all the format characteristics described above can be listed on your display. List them by typing:
list set *
The asterisk means that you want to list all settings. This displays a series of messages that describe the settings of each operational characteristic.
You can request a specific characteristic by specifying the name of the characteristic instead of the asterisk on the SET command. For example, type the following command to list the current setting for the column separator:
list set separator
Your printed output is automatically sent to the system printer designated by your site. You can change or redirect your printed output to a CICS terminal printer or POWER remote workstation.
To route your printed output to a CICS terminal printer, you would specify:
print termid termid
or
set printroute termid
where termid is replaced with the CICS terminal printer identifier.
To route printed output to a POWER remote workstation, specify:
print destid wkstat
or
set printroute wkstat
where wkstat is replaced with the identifier for the required POWER remote workstation.
The system printer is specified as:
print system
or
set printroute system
EXERCISE 4 (Answers are in Appendix A, Answers to the Exercises, page ***.)
|