DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

PROJNO ACTNO ACSTAFF ACSTDATE

ACENDATE

------

-------

----------

AD3112

0.50

1982-02-01

1982-03-15

AD3113

0.50

1982-06-15

1982-07-01

AD3112

1.00

1982-03-15

1982-08-15

MA2112

1.00

1982-02-01

1982-10-01

AD3112

0.75

1982-01-01

1982-10-15

AD3112

0.25

1982-08-15

1982-10-15

AD3113

0.75

1982-09-01

1982-10-15

AD3113

1.25

1982-06-01

1982-12-15

MA2112

1.50

1982-02-15

1983-02-01

AD3113

1.00

1982-07-01

1983-02-01

AD3113

1.00

1982-10-15

1983-02-01

MA2112

1.00

1982-06-01

1983-02-01

MA2113

2.00

1982-04-01

1983-12-15

AD3113

1.75

1982-01-01

1982-04-15

AD3113

0.50

1982-03-01

1982-04-15

AD3112

0.50

1982-10-15

1982-12-01

AD3112

0.35

1982-08-15

1982-12-01

MA2113

0.50

1982-10-01

1983-02-01

MA2113

1.50

1982-09-01

1983-02-01

MA2113

1.00

1982-01-01

1983-02-01

MA2112

1.00

1982-10-01

1983-10-01

IF1000

0.50

1982-10-01

1983-01-01

Figure 14. Results When You Try to Exceed the Limit of a Full Display

The view is moved back to the limit of one full display. Enter the following

command to return to the first rows of the query result:

backward max

Now that you are finished with this query result, end it.

Results That Are Too Wide for One Display

To learn about viewing results that are too wide for a single display, it is necessary

to use a sample table of greater width than the PROJ_ACT table. For the next

several examples, the EMPLOYEE table is used.

Enter the following query:

select * -

from employee

The resulting query on an 80-character display is similar to Figure 15 on page 26.

Chapter 2. Querying Tables

EMPNO FIRSTNME

MIDINIT LASTNAME

WORKDEPT PHONENO HIREDATE

-----

------------ -------

---------------

--------

-------

----------

000010

CHRISTINE

HAAS

A00

3978

1991-05-27

000110

VINCENZO

LUCCHESI

A00

3490

1958-05-16

000120

SEAN

O'CONNELL

A00

2167

1963-12-05

000020

MICHAEL

THOMPSON

B01

3476

1973-10-10

000030

SALLY

KWAN

C01

4738

1975-04-05

000130

DOLORES

QUINTANA

C01

4578

1971-07-28

000140

HEATHER

NICHOLLS

C01

1793

1976-12-15

000060

IRVING

STERN

D11

6423

1973-09-14

000150

BRUCE

ADAMSON

D11

4510

1972-02-12

000160

ELIZABETH

PIANKA

D11

3782

1977-10-11

000170

MASATOSHI

YOSHIMURA

D11

2890

1978-09-15

000180

MARILYN

SCOUTTEN

D11

1682

1973-07-07

000190

JAMES

WALKER

D11

2986

1974-07-26

000200

DAVID

BROWN

D11

4501

1966-03-03

000210

WILLIAM

JONES

D11

0942

1979-04-11

000220

JENNIFER

LUTZ

D11

0672

1968-08-29

000070

EVA

PULASKI

D21

7831

1980-09-30

000230

JAMES

JEFFERSON

D21

2094

1966-11-21

000240

SALVATORE

MARINO

D21

3780

1979-12-05

000250

DANIEL

SMITH

D21

0961

1969-10-30

000260

SYBIL

JOHNSON

D21

8953

1975-09-11

000270

MARIA

PEREZ

D21

9001

1980-09-30

Figure 15. Results of Query That is Too Wide for Display

There is no visual indication that this query result is too wide for the display;

however, because the columns extend to the far right side of the display, there is a

possibility that additional columns of data may be beyond the last column. To

search for any additional columns that may exist, enter the following display

command:

right 7

This moves your view of the query seven columns to the right, resulting in

Figure 16 on page 27.

Interactive SQL Guide and Reference

JOB

EDLEVEL SEX BIRTHDATE

SALARY

BONUS

COMM

-------- -------

---

----------

-----------

PRES

1933-08-01

52750.00

1000.00

4220.00

SALESREP

1929-11-05

46500.00

800.00

3720.00

CLERK

1942-10-18

29250.00

600.00

2340.00

MANAGER

1948-02-02

41250.00

800.00

3300.00

MANAGER

1941-05-11

38250.00

800.00

3060.00

ANALYST

1925-09-15

23800.00

800.00

1904.00

ANALYST

1946-01-19

28420.00

800.00

2274.00

MANAGER

1945-07-07

32250.00

600.00

2580.00

DESIGNER

1947-05-17

25280.00

500.00

2022.00

DESIGNER

1955-04-12

22250.00

400.00

1780.00

DESIGNER

1951-01-05

24680.00

500.00

1974.00

DESIGNER

1949-02-21

21340.00

500.00

1707.00

DESIGNER

1952-06-25

20450.00

400.00

1636.00

DESIGNER

1941-05-29

27740.00

600.00

2217.00

DESIGNER

1953-02-23

18270.00

400.00

1462.00

DESIGNER

1948-03-19

29840.00

600.00

2387.00

MANAGER

1953-05-26

36170.00

700.00

2893.00

CLERK

1935-05-30

22180.00

400.00

1774.00

CLERK

1954-03-31

28760.00

600.00

2301.00

CLERK

1939-11-12

19180.00

400.00

1534.00

CLERK

1936-10-05

1725.00

300.00

1380.00

CLERK

1953-05-26

27380.00

500.00

2190.00

Figure 16. Display of Query Moved Seven Columns to the Right

Now you can see that there are an additional seven columns, but it is still not clear

that you have reached the last column of the table. Enter the following command

to move your view two more columns to the right:

right 2

You should now see the display in Figure 17.

SEX BIRTHDATE

SALARY

BONUS

COMM

---

----------

-----------

1933-08-01

52750.00

1000.00

4220.00

1929-11-05

46500.00

800.00

3720.00

1942-10-18

29250.00

600.00

2340.00

1948-02-02

41250.00

800.00

3300.00

1941-05-11

38250.00

800.00

3060.00

1925-09-15

23800.00

800.00

1904.00

1946-01-19

28420.00

800.00

2274.00

1945-07-07

32250.00

600.00

2580.00

1947-05-17

25280.00

500.00

2022.00

1955-04-12

22250.00

400.00

1780.00

1951-01-05

24680.00

500.00

1974.00

1949-02-21

21340.00

500.00

1707.00

1952-06-25

20450.00

400.00

1636.00

1941-05-29

27740.00

600.00

2217.00

1953-02-23

18270.00

400.00

1462.00

1948-03-19

29840.00

600.00

2387.00

1953-05-26

36170.00

700.00

2893.00

1935-05-30

22180.00

400.00

1774.00

1954-03-31

28760.00

600.00

2301.00

1939-11-12

19180.00

400.00

1534.00

1936-10-05

1725.00

300.00

1380.00

1953-05-26

27380.00

500.00

2190.00

Figure 17. Display of Query Moved Two More Columns to the Right

Now apparently COMM is the final column in the table, because no other columns

appear to the right of it in the query result. If you only want to move your view of

Chapter 2. Querying Tables

the table one column to the right, you can omit the number and just type right.

You can also move your view one column to the right by pressing PF11.

To move your view to the left, type:

left

This command works in the same manner as RIGHT. Again, you can move your

view one column to the left by pressing PF10.

Now move your view all the way back to the first column by using the following

command:

column 1

The COLUMN command aligns the column you specify with the left edge of the

display. The number refers to the column’s position in the query result. If you do

not specify a number with the COLUMN command, column 1 is placed at the left

edge of the display.

A column of characters that is wider than the display width requires another

display command, TAB, to let you see the entire length attribute of a column. The

TAB command is described in “Chapter 10. ISQL Commands” on page 105.

Ending a Query Display

Always end a query result when you are finished with it (so that ISQL

performance for other users is not affected). The END command removes your

query result from the display. Type the following and press ENTER:

end

DB2 Server for VM

ISQL returns to wait mode.

Obtaining a Printed Report

So far you have learned how to view query results at your display terminal. Now

produce a printed copy of a query result on the system printer. The information

requested to be printed is a copy of the DEPARTMENT table. Begin by entering

the following query:

select * -

from department -

order by deptno

As you can see, this query selects all the columns and all the rows from the

DEPARTMENT table. Request a printed copy of this query result by entering the

following display command:

Another way to request a printed copy of the query result is to press PF4.Figure 18

on page 29 illustrates the printed information for the preceding query. Consult the

appropriate person at your location for printing procedures.

Interactive SQL Guide and Reference

Each printed page is numbered, dated, and titled.The title consists of the first 100

characters of the SELECT statement that you typed. For information on the

creation of report titles, refer to “Chapter 5. Formatting Query Results” on page 43.

07/13/89

SELECT * FROM DEPARTMENT ORDER BY DEPTNO

PAGE

1 .

DEPTNO DEPTNAME

MGRNO ADMRDEPT

______

____________________________

______

________

A00

SPIFFY COMPUTER SERVICE DIV.

000010

A00

B01

PLANNING

000020

A00

C01

INFORMATION CENTER

000030

A00

D01

DEVELOPMENT CENTER

A00

D11

MANUFACTURING SYSTEMS

000060

D01

D21

ADMINISTRATION SYSTEMS

000070

D01

E01

SUPPORT SERVICES

000050

A00

E11

OPERATIONS

000090

E01

E21

SOFTWARE SUPPORT

000100

E01

Figure 18. Example of a Printed Report

Although this query fits on one display, a long query that requires several displays

is also printed entirely, regardless of the rows being displayed when you typed the

PRINT command. Also, when the PRINT command completes printing the report,

the end rows of the query result are displayed regardless of the rows being

displayed when you entered the PRINT command.

The printed report does, however, start with the column that is currently at the left

edge of the display, and continues for as many print positions as can fit on the

specified page size. You can print reports that are too wide for the page size by

entering a PRINT command at column 1, moving the display to start with the

column that would not fit on the page, entering another PRINT command, and so

on. Page-size specification is discussed in “Page Size of Printed Reports” on

page 62.

You may have special printing requirements for your query results (such as the

type of paper or its size) that you can specify using the CLASS keyword of the

PRINT command. See the PRINT command in “Chapter 10. ISQL Commands” on

page 105 for a description of this procedure. For DB2 Server for VM, the CLASS

value can also be set by a CP SPOOL command.

Obtaining Multiple Copies of a Printed Report

For three copies, type:

print copies 3

Chapter 2. Querying Tables

DB2 Server for VM

You can also use the CP SPOOL command to specify the number of copies.

The value for copies that is specified on the CP SPOOL command remains in

effect until another value is specified by another CP SPOOL command.

Note: If you specify the number of copies on the PRINT command after you

specified it using a CP SPOOL command, the PRINT command

quantity is used for that print operation. All following PRINT

commands use the quantity specified by the CP SPOOL command,

unless you specify it using the COPIES keyword.

Using More Than One Keyword with the Print Command

You can specify both your print requirements and number of copies with one

PRINT command. For example, you can type:

print copies 3 class a

Interactive SQL Guide and Reference

EXERCISE 1 (Answers are in Appendix A. Answers to the Exercises, page 165.)

Enter the following command:

select * -

from proj_act -

where projno <> 'AD3111' -

order by actno,acendate

Perform the following:

1. Move the display so that it begins with the 40th row of the query result.

2. Display the end of the query result.

3. Move the display so that it begins with the second column of the query result.

4. Move the display so that it begins with the first column of the query result.

5. Move the display so that it begins with the first row of the query result.

6. Request a printed copy of this result.

7. End the query result.

Chapter 2. Querying Tables

Chapter 3. Managing Table Data

This chapter shows how to maintain the data in your tables. When the tables are

interrelated, you must maintain the accuracy of all the tables whenever you make

changes. The following sections describe how to control your changes to the tables.

Controlling Changes to Table Data

Sometimes you want to make updates only with other updates (or deletions). For

example, a new employee is hired and is assigned to a project. This involves

updates to the EMPLOYEE and EMP_ACT tables. In addition, the employee is

made responsible for the project, requiring an update of the PROJECT table

(RESPEMP column). The three tables are updated simultaneously, because if only

some of the updates are made, and an interval of time passes before the remaining

updates are made, anyone accessing the tables during the interval for information

about the new employee receives inaccurate information.

For simultaneous updating of tables, you can group SQL statements into a single

unit. The statements are run as a group, and only upon your specification. You can

also cancel the statements as a group. Such a grouping of one or more statements

is called a logical unit of work (LUW). Two system settings are used in LUW

processing: AUTOCOMMIT ON and AUTOCOMMIT OFF.

Using the AUTOCOMMIT ON Setting

With the AUTOCOMMIT ON setting, each statement is treated as an LUW. The

work performed by each statement is committed as part of statement processing,

resulting in a permanent change. There are, however, a few exceptions. When an

INSERT, UPDATE, or DELETE statement that affects more than one row is typed,

the LUW is not completed until the next statement is typed. If the next statement

is CANCEL or ROLLBACK, the work performed by the INSERT, UPDATE, or

DELETE operation before the CANCEL or ROLLBACK is undone instead of being

committed. If the next statement is not CANCEL or ROLLBACK, the work

performed by the INSERT, UPDATE, or DELETE is committed automatically before

the new statement is processed. The CANCEL command is described under

“Chapter 10. ISQL Commands” on page 105.

The default setting in ISQL is AUTOCOMMIT ON.

Using the AUTOCOMMIT OFF Setting

Use the AUTOCOMMIT OFF setting to control the committing of information.

After you type SET AUTOCOMMIT OFF, any subsequent statements you type are

grouped into a single LUW. The statements are processed but not committed until

you type the SQL statement COMMIT. As you type the statements, the table data

shown on the display looks as if the changes are being committed: they are not. In

addition, no other system user can view or modify the changes that you are

making until you type the COMMIT statement. Typing COMMIT completes your

LUW and commits all processing performed since the beginning of the LUW.

After you set AUTOCOMMIT OFF, and if for any reason, such as an update error,

you decide not to commit the LUW processing, you can cancel all processing

performed since the beginning of the LUW by typing ROLLBACK. After you set

AUTOCOMMIT OFF, you must explicitly end the LUW by typing COMMIT or

ROLLBACK.

Attention Use AUTOCOMMIT OFF cautiously because it prevents other users

from accessing the rows of tables used in your processing. In particular, avoid

using it for a query.

To return to the condition in which ISQL automatically commits your work, type

SET AUTOCOMMIT ON. The system displays a message requesting you to commit or

rollback any work done during the logical unit of work. You must respond to this

message before any further statements can be typed.

Interpreting Messages While Making Changes

Messages are displayed after data has been inserted, deleted, or updated to

indicate the number of rows affected by each operation. These messages help you

verify the changes.

Interpreting Errors While Making Changes

A single statement can change many rows in a table. If a statement error occurs

after only some rows have been changed, all changes are rolled back or

withdrawn, and the entire operation fails. If the failed statement is part of an LUW,

previously completed statements in the LUW are not affected. You still have the

option of entering COMMIT or ROLLBACK for the other statements in the LUW.

Interactive SQL Guide and Reference

Chapter 4. Using ISQL Commands to Save Time When

Executing Statements

To avoid retyping similar SQL statements or retyping an entire statement just to

correct one error, you can use the ISQL commands in this chapter. It explains how

to retrieve, modify, and delete SQL statements, how to withhold a statement from

processing until you have checked it for errors, and how to reuse the same SQL

statement for different tables, columns, and rows.

Reusing the Current SQL Statement

Before the system processes an SQL statement, it inserts it into a special storage

area called the SQL command buffer. Once in the buffer, the statement becomes the

current statement until it is pushed down in the queue by the next statement. ISQL

commands are also inserted into the command buffer when you type them in, but

not when you invoke them by using a PF key.

Using the command buffer facility, you can rerun a typed statement by using the

ISQL START command (PF12).

Process the current SQL statement by performing the following:

1. Type the statement:

select * -

from department

2. When the result is displayed, type the END command.

3. Reenter the SQL statement by typing:

start

The START command becomes even more useful when you have a typing error in

an SQL statement. The error can be corrected (described in the next topic) and the

START command used to reenter the corrected statement.

Retrieving and Correcting SQL Lines

You can use the RETRIEVE function to correct a statement already in the command

line buffer. RETRIEVE moves the previously-typed line into the input area. The

following example shows how to retrieve and correct a line in the output area.

Type the following and press ENTER:

select actnum,actkwd,actdesc -

The line is moved to the output area.

Now type:

from activity

Press ENTER.

An error message is issued indicating that the column ACTNUM was not found.

(ACTNO is the correct name.)

To retrieve the last input line entered, press the PF12 key to invoke the RETRIEVE

function. The following line is now displayed in the input area with the cursor

positioned at the end of the line:

from activity

Press the PF12 key again to retrieve the previous input line. The input area now

contains:

select actnum,actkwd,actdesc -

with the cursor positioned at the end of the line. You can now correct the error as

you would any other error in the input area by backspacing and typing the correct

characters.

After you have corrected the line in the input area, press ENTER.

DB2 Server for VSE

The corrected line is now displayed in the output area, and CONTINUE COMMAND

has appeared in the status area to confirm that you are to continue entering

the statement.

DB2 Server for VM

The corrected line is now displayed in the output area, VM READ has appeared

in the status area, and message ARI7068I has appeared in the output area to

confirm that you are to continue typing the statement.

Continue the statement by using either of the following two methods:

v Type the FROM clause in the input area as follows:

from activity

Press ENTER.

v Press PF12 twice. The FROM clause is displayed in the input area with the

cursor at the end of the line:

from activity

Press ENTER.

The result is the same using either method: the SELECT statement is reissued.

Note: The command buffer holds a variable number of statements, depending on

their length. If your statements are short, the buffer can hold more of them.

As you press PF12 repeatedly, the system retrieves statements from the

buffer starting with the most recent, and continues until it retrieves the

oldest statement. Then it starts over and returns the most recent statement.

Interactive SQL Guide and Reference

DB2 Server for VM

The RETRIEVE facility functions differently in wait mode compared to

display mode. In display mode, the system can retrieve any information

typed during the ISQL session that is still contained in the command buffer.

In wait mode (VM READ shows in the status area of the display), the system

can retrieve information typed in wait mode or from a time prior to starting

ISQL.

Incorrect information typed from wait mode can be retrieved immediately,

and corrected as illustrated in the previous example. Incorrect information

typed from display mode forces display mode to end, and returns you to

wait mode. Pressing PF12 does not retrieve the incorrect information in this

case, because you typed it from display mode. The following examples

illustrate the RETRIEVE-function differences between wait and display mode.

Ensure that you are in wait mode. If you do not see VM READ in the

lower-right corner of your display, press PF3. Now, type the following (with

activity spelled incorrectly):

select * from abtivity

An error message is issued indicating that ABTIVITY cannot be found. Press

PF12. The incorrect select * from abtivity statement appears in your input

area, where you can correct it.

Erroneous lines typed from display mode cannot be immediately retrieved

with RETRIEVE key PF12. To illustrate, type the following correct statement

from wait mode.

select * from activity

The ACTIVITY table is displayed, and the message disappears from the status

area to indicate that you are in display mode.

Do not press PF3 to end the display. Instead, type the following incorrect

statement from display mode:

select * from supply

The SUPPLY table is not found; VM READ reappears in the lower-right corner

of your display to indicate that you are back in wait mode.

Now, press PF12. select * from activity appears in the input area. It is not

the last statement you typed, but is the last one you typed from wait mode.

The line in error that was entered from display mode cannot be retrieved

from wait mode. It has been saved, however, and can be retrieved when you

return to display mode. To illustrate this, enter the following correct query,

which returns you to display mode:

select actno from activity

Now, from display mode, press PF12. The last line typed, select actno from

activity, appears in the input area.

Press PF12 again. This time the line in error, select * from supply, does

appear in the input area.

Chapter 4. Using ISQL Commands to Save Time When Executing Statements

DB2 Server for VM

All lines typed during an ISQL session can be retrieved from within display

mode (up to the maximum number of lines the buffer can hold), including

lines entered from display mode and wait mode. Only those lines entered

from wait mode can be retrieved from wait mode. Since ISQL commands

other than display commands, SQL statements other than SELECT, and

incorrect SELECT statements entered from display mode return you to wait

mode, you cannot immediately retrieve this type of statement or command.

Pressing PF3 after you have finished each query ensures that you enter each

statement or command from wait mode and can immediately retrieve and

change the information.

Altering and Reusing SQL Lines

The RETRIEVE facility is especially useful when you enter similar commands. To

save time, you can retrieve an earlier command, alter it, and then enter it.

For example, enter the following statement:

select * -

from employee

Your next statement to be typed is select * from department. To save time, reuse

the statement you just typed as follows.

Press PF12 twice to place select * - in the input area. Then, press ENTER.

Press PF12 twice. from employee is now contained in the input area. Backspace and

type DEPARTMENT over EMPLOYEE. Then, press ENTER.

In this way, select * from department is typed in just a few keystrokes.

Remember, a variable number of your statements are retained, and you may or may

not be able to retrieve a particular statement.

Changing the Current SQL Statement

These statements can be corrected, altered and reused, and portions of them can be

deleted.

Correcting Typing Errors in the Statement

Having the facility to change the current SQL statement lets you correct one

containing typing errors. For example, enter the following statement (with activity

misspelled):

select actno -

from adtivity -

where actno > 100

This returns an error message stating that there is no table owned by you named

ADTIVITY. Correct the error by entering the following ISQL command:

change /adtivity/activity/

Interactive SQL Guide and Reference

The slashes in CHANGE commands separate the data to be changed (between the

first two slashes) from the new data to replace it (between the last two slashes).

Always include a blank before the first slash and always enter the final slash. ISQL

changes the statement at the first occurrence of the data you place between the first

two slashes, so ensure that the data you want changed is the first occurrence of

that data in the statement to be changed.

Note: You do not have to use a slash to separate the data; any character except a

blank can be used in its place. To change data that contains a slash, choose a

character that does not occur in the data to be changed or in the new data.

Now enter the START command to process the changed SQL statement. If you

have made no other mistakes, the query result for this SELECT statement is

displayed on your display.

Altering and Reusing the Statement

You can use the CHANGE command to alter and then reuse the statement in the

command buffer.

In “Altering and Reusing SQL Lines” on page 38, you used the RETRIEVE function

to change the table name from EMPLOYEE to DEPARTMENT. Alternatively, you

can query the EMPLOYEE table, end the query, and then type the following

statement:

change /employee/department/

This command changes the statement in the buffer. To perform the new statement,

which queries the DEPARTMENT table, type:

start

Notice that the RETRIEVE command uses lines of information from the command

line buffer; the CHANGE command uses the statement from the command buffer.

Deleting Portions of the Statement

The CHANGE command can also be used to delete a portion of a current

statement. For example, suppose you had selected the ACTNO, ACTKWD, and

ACTDESC columns from the ACTIVITY table. After you end the query, you can

delete the selection of the ACTKWD column table by typing:

change /,actkwd//

By providing no replacement string (nothing between the second and third

slashes), the characters matching the string between the first two slashes are

effectively deleted from the statement. You can see the result of your changed

query by using the START command.

Ignoring an SQL Line

Another way to correct a typing mistake in a multiple-line statement is to use the

IGNORE command.

To illustrate, type the following (deptname is misspelled):

select depname,mgrno -

Press ENTER. Now, you realize the valid column name is DEPTNAME. Type:

ignore

Chapter 4. Using ISQL Commands to Save Time When Executing Statements

DB2 Server for VSE

You receive the message:

ARI7061I Previous input ignored.

in the output area, and the status area contains ENTER A NEW COMMAND.

DB2 Server for VM

You receive the message:

ARI7061I Previous input ignored.

in the output area, and the status area contains VM READ.

You can now retype the statement, or use PF12 to retrieve the line for correction.

Preventing the Immediate Processing of an SQL Statement

You can prevent an SQL statement from being processed immediately after being

typed. This lets you check the statement for typing errors before it is processed

using a START command. It also allows an SQL statement containing placeholders

to be placed in the SQL command buffer, and values to be substituted for the

placeholders when the statement is started using the START command. (The

START command and placeholders are discussed in the next section.)

To illustrate, the following example shows how to prevent the statement SELECT *

FROM PROJECT from being processed immediately. Type the following ISQL

command:

hold select * from project

This statement remains in the buffer as the current statement until you enter

another SQL statement (or another HOLD command).

The HOLD command can also be invoked by pressing PF9. If you press PF9

instead of ENTER after typing an SQL statement, it is placed in the command

buffer and is not processed.

Your held statement can then be processed using the START command.

Note: HOLD cannot be used with ISQL commands.

Using Placeholders in SQL Statements

You can form SQL statements that contain placeholders. They reserve areas in the

statements, which are filled in when the statement is performed. One reason for

doing this, for example, is to avoid typing an UPDATE statement for each table

update. Suppose you want to update the EMPLOYEE table to reflect a $100 bonus

increase for particular employees.

1. To prevent your changes from being automatically committed, type:

set autocommit off

Interactive SQL Guide and Reference

This starts a logical unit of work. Now type:

hold update employee -

set bonus = &1 -

where empno = '&2'

The use of HOLD at the beginning of this example places the UPDATE statement

in the command buffer without processing it.

In the example, &1 and &2 are the placeholders. The number following the

ampersand refers to the sequence in which the placeholders are replaced. The

database manager performs the replacement as follows: the first item of

information replaces &1, the second replaces &2, and so on.

Start the UPDATE statement and supply the replacement information:

start (bonus+100.00 '000010')

This command adds a $100 bonus to employee 000010, and produces a message

indicating that one row (ROWCOUNT=1) was updated. The actual UPDATE

statement processed is:

update employee

set bonus = bonus + 100.00

where empno = '000010'

The two items of information which replace the placeholders are called

parameters.

Because START is an ISQL command, it does not replace the UPDATE

statement in the command buffer. This lets you continue to use the UPDATE

statement to update another row as follows:

start (bonus+100.00 '000050')

This process can continue for as many updates as needed. Remember though,

that the START command uses the statement currently contained in the

command buffer. If you typed another SQL statement, it would replace the

UPDATE statement in the buffer, and you would have to recall the UPDATE

statement to continue. (Statement recalling is discussed in “Chapter 6. Storing

SQL Statements” on page 67.)

Type the following command to return to normal command processing:

set autocommit on

Respond to the resulting message with the following to prevent the changes

from being committed:

rollback

The following are rules for the use of placeholders and parameters:

v A placeholder for a character data item must be enclosed in single quotation

marks.

v A parameter for a character data item must also be enclosed in single quotation

marks.

v When using parameters in a START command, enclose them in parentheses and

separate each parameter with a blank.

v A parameter may be one word, several words, or an expression.

- If one parameter (replacing just one placeholder) consists of a list of words

separated by commas, such as a list of column names in a select_list, the list of

words must use commas (and not blanks) as separators.

Chapter 4. Using ISQL Commands to Save Time When Executing Statements

- If a parameter contains a list of words separated by blanks, that list of words

must be enclosed in single quotation marks to distinguish between the blanks

within the one parameter and the blanks that separate parameters.

v In a stored SQL command, you can only use the ampersand (&) to create

placeholders.

EXERCISE 2 (Answers are in Appendix A. Answers to the Exercises, page 165.)

Perform the following:

1. Enter and hold an SQL statement that retrieves the entire DEPARTMENT table.

2. Change the command created in step 1 to select two columns, and use placeholders to define them.

3. Start the command, replacing the two placeholders with values that retrieve the DEPTNO and DEPTNAME

columns. Check the results, and then end the display.

4. Change the current SQL statement to add a WHERE clause. Use a placeholder for the search condition.

5. Start the command, replacing the placeholders with values that select the department name and the manager

number for departments that report to E01.

Interactive SQL Guide and Reference

Chapter 5. Formatting Query Results

This chapter builds on the information in previous chapters. When the information

appears on your display, you can change the way it looks. You can format the

columns so that they appear with different headings, widths, or separators. You

can format the whole query result so that it appears as a report, divided into

groups, totaled, and titled. You can also use ISQL commands to set the query

format characteristics for all queries in a session.

Formatting Columns

In “Chapter 2. Querying Tables” on page 21, you learned how to produce, display,

and print query information. In this chapter, you will learn the techniques to

arrange the information in a report format. You can:

v Change the number of blanks that separate columns or even change the blanks

to some other characters.

v Cease display of a column (and later include it, if desired).

v Change the name of a displayed column heading.

v Specify the number of decimal places to display for numeric columns.

v Control the display of leading zeros on numeric columns.

v Change the display width of a column.

Creating a Report from Query Results

To illustrate the formatting changes that you can make to columns, you first need a

query result. Type the following statement:

select projno,actno,acstaff,acstaff + .25,acstdate -

from proj_act -

where projno = 'AD3100' -

or projno = 'AD3111' -

or projno = 'AD3112' -

order by projno,actno

The query result for this statement (shown in Figure 19 on page 44) is used over

the next several topics. Do not end it until asked to do so.

PROJNO ACTNO ACSTAFF

EXPRESSION 1

ACSTDATE

------

-------

--------------

----------

AD3100

0.50

0.75

1982-01-01

AD3111

0.80

1.05

1982-01-01

AD3111

0.50

0.75

1982-03-15

AD3111

1.50

1.75

1982-02-15

AD3111

0.50

0.75

1982-03-15

AD3111

1.25

1.50

1982-04-15

AD3111

1.00

1.25

1982-09-15

AD3111

180

1.00

1.25

1982-10-15

AD3112

0.75

1.00

1982-01-01

AD3112

0.50

0.75

1982-02-01

AD3112

0.75

1.00

1982-12-01

AD3112

1.00

1.25

1983-01-01

AD3112

0.75

1.00

1982-01-01

AD3112

0.50

0.75

1982-02-01

AD3112

1.00

1.25

1982-03-15

AD3112

0.25

0.50

1982-08-15

AD3112

0.35

0.60

1982-08-15

AD3112

0.50

0.75

1982-10-15

AD3112

180

0.50

0.75

1982-08-15

* End of Result *** 19 Rows Displayed ***Cost Estimate is 1********************

Figure 19. A Query Result to Be Used to Illustrate Formatting Techniques

Field procedures can affect the order of the rows when you use the ORDER BY

clause. For more information about field procedures, see the DB2 Server for VSE &

VM SQL Reference manual.

Modifying the Separation between Columns

Using the query result described above, change the number of blanks used to

separate the columns by typing the following display command:

format separator 4 blanks

FORMAT is the name of the command; SEPARATOR describes the kind of

formatting to be done. After this command has been processed, four blanks are

displayed between columns. Now separate the columns with a vertical bar and a

couple of blanks by typing the following command:

format separator ' | '

This defines a separator that consists of a blank, followed by a vertical bar,

followed by a blank. The quotation marks were used to include the blanks as part

of the separator. Quotation marks are needed whenever the separator you are

defining contains a blank. For example, the 3-character separator:

|||

does not require quotation marks, whereas this separator does:

'| |'

The result of typing the above FORMAT command is shown in Figure 20 on

page 45.

Interactive SQL Guide and Reference

PROJNO | ACTNO | ACSTAFF | EXPRESSION 1 | ACSTDATE

------ | ------ | ------- | -------------- | ---------- |

AD3100 |

10 |

0.50 |

0.75 | 1982-01-01 |

AD3111 |

60 |

0.80 |

1.05 | 1982-01-01 |

AD3111 |

60 |

0.50 |

0.75 | 1982-03-15 |

AD3111 |

70 |

1.50 |

1.75 | 1982-02-15 |

AD3111 |

70 |

0.50 |

0.75 | 1982-03-15 |

AD3111 |

80 |

1.25 |

1.50 | 1982-04-15 |

AD3111 |

80 |

1.00 |

1.25 | 1982-09-15 |

AD3111 |

180 |

1.00 |

1.25 | 1982-10-15 |

AD3112 |

60 |

0.75 |

1.00 | 1982-01-01 |

AD3112 |

60 |

0.50 |

0.75 | 1982-02-01 |

AD3112 |

60 |

0.75 |

1.00 | 1982-12-01 |

AD3112 |

60 |

1.00 |

1.25 | 1983-01-01 |

AD3112 |

70 |

0.75 |

1.00 | 1982-01-01 |

AD3112 |

70 |

0.50 |

0.75 | 1982-02-01 |

AD3112 |

70 |

1.00 |

1.25 | 1982-03-15 |

AD3112 |

70 |

0.25 |

0.50 | 1982-08-15 |

AD3112 |

80 |

0.35 |

0.60 | 1982-08-15 |

AD3112 |

80 |

0.50 |

0.75 | 1982-10-15 |

AD3112 |

180 |

0.50 |

0.75 | 1982-08-15 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 20. A Query Result with a Formatted Separator between Columns

Excluding Columns from the Display

There can be occasions when you obtain a query result and decide that it contains

columns that you do not want in your report. You can end the result and retype

the statement with the correct columns listed in the SELECT clause, but there is an

alternative to retyping.

Suppose you want to exclude the ACSTAFF and ACSTDATE columns in the

current display. Type:

format exclude (acstaff acstdate)

Issuing a FORMAT command with EXCLUDE instructs ISQL to exclude the

columns specified from the current display. When specifying more than one

column name, enclose them in parentheses and separate the names with a blank.

The above FORMAT command displays Figure 21 on page 46.

Chapter 5. Formatting Query Results

PROJNO | ACTNO | EXPRESSION 1 |

------ | ------ | -------------- |

AD3100 |

10 |

0.75 |

AD3111 |

60 |

1.05 |

AD3111 |

60 |

0.75 |

AD3111 |

70 |

1.75 |

AD3111 |

70 |

0.75 |

AD3111 |

80 |

1.50 |

AD3111 |

80 |

1.25 |

AD3111 |

180 |

1.25 |

AD3112 |

60 |

1.00 |

AD3112 |

60 |

0.75 |

AD3112 |

60 |

1.00 |

AD3112 |

60 |

1.25 |

AD3112 |

70 |

1.00 |

AD3112 |

70 |

0.75 |

AD3112 |

70 |

1.25 |

AD3112 |

70 |

0.50 |

AD3112 |

80 |

0.60 |

AD3112 |

80 |

0.75 |

AD3112 |

180 |

0.75 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 21. A Query Result Formatted to Exclude Two Columns

You can use a number instead of a column name to identify the excluded column.

The number refers to the column’s position in the SELECT clause. For example, the

ACSTAFF and ACSTDATE columns can be excluded by typing:

format exclude (3 5)

Sometimes it is easier to define columns you want included. For example, if you

want to include only the third column of a query result that contained many

columns, you can type:

format exclude all but (3)

The parentheses can be omitted when only one column is specified.

Including Columns in the Display

The effects of excluding a column from the display can be reversed. You can use

the INCLUDE option to include the ACSTAFF and ACSTDATE columns in the

current display. Do this by typing:

format include (acstaff acstdate)

Again, numbers can be used instead of column names. For example, to include

only the first and third columns of a query result, you can type:

format include only (1 3)

The above FORMAT command displays Figure 22 on page 47.

Interactive SQL Guide and Reference

PROJNO | ACSTAFF |

------ | ------- |

AD3100 |

0.50 |

AD3111 |

0.80 |

AD3111 |

0.50 |

AD3111 |

1.50 |

AD3111 |

0.50 |

AD3111 |

1.25 |

AD3111 |

1.00 |

AD3111 |

1.00 |

AD3112 |

0.75 |

AD3112 |

0.50 |

AD3112 |

0.75 |

AD3112 |

1.00 |

AD3112 |

0.75 |

AD3112 |

0.50 |

AD3112 |

1.00 |

AD3112 |

0.25 |

AD3112 |

0.35 |

AD3112 |

0.50 |

AD3112 |

0.50 |

* End of Result *** 19 Rows Displayed ***Cost Estimate is 1********************

Figure 22. A Query Result Formatted for Include-Only Columns

To display all the columns again, type:

format include

Changing a Displayed Column Heading

The query result you are using has EXPRESSION 1 as a column heading. To make

the heading more meaningful, use the NAME keyword of the FORMAT command.

Type the following command:

format column 'expression 1' name 'staff + .25'

The above FORMAT command displays Figure 23.

PROJNO | ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

------ | ------ | ------- | -------------- | ---------- |

AD3100 |

10 |

0.50 |

0.75 | 1982-01-01 |

AD3111 |

60 |

0.80 |

1.05 | 1982-01-01 |

AD3111 |

60 |

0.50 |

0.75 | 1982-03-15 |

AD3111 |

70 |

1.50 |

1.75 | 1982-02-15 |

AD3111 |

70 |

0.50 |

0.75 | 1982-03-15 |

AD3111 |

80 |

1.25 |

1.50 | 1982-04-15 |

AD3111 |

80 |

1.00 |

1.25 | 1982-09-15 |

AD3111 |

180 |

1.00 |

1.25 | 1982-10-15 |

AD3112 |

60 |

0.75 |

1.00 | 1982-01-01 |

AD3112 |

60 |

0.50 |

0.75 | 1982-02-01 |

AD3112 |

60 |

0.75 |

1.00 | 1982-12-01 |

AD3112 |

60 |

1.00 |

1.25 | 1983-01-01 |

AD3112 |

70 |

0.75 |

1.00 | 1982-01-01 |

AD3112 |

70 |

0.50 |

0.75 | 1982-02-01 |

AD3112 |

70 |

1.00 |

1.25 | 1982-03-15 |

AD3112 |

70 |

0.25 |

0.50 | 1982-08-15 |

AD3112 |

80 |

0.35 |

0.60 | 1982-08-15 |

AD3112 |

80 |

0.50 |

0.75 | 1982-10-15 |

AD3112 |

180 |

0.50 |

0.75 | 1982-08-15 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 23. A Query Result Formatted to Change a Displayed Column Heading

Chapter 5. Formatting Query Results

Numbers can also be used to identify the column heading to change; for example:

format column 4 name 'staff + .25'

Remember, the 4 refers to the column’s position in the SELECT clause, not the

position of the column displayed.

Changing the Number of Decimal Places Displayed

The query result on your display shows two decimal places for both the ACSTAFF

and ACSTAFF + .25 columns. You control the number of decimal places displayed

using the DPLACES option of the FORMAT command. For example, to display

only one decimal place for the STAFF + .25 column, type:

format column 'staff + .25' dplaces 1

This displays a result similar to Figure 24.

PROJNO | ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

------ | ------ | ------- | -------------- | ---------- |

AD3100 |

10 |

0.50 |

0.7 | 1982-01-01 |

AD3111 |

60 |

0.80 |

1.0 | 1982-01-01 |

AD3111 |

60 |

0.50 |

0.7 | 1982-03-15 |

AD3111 |

70 |

1.50 |

1.7 | 1982-02-15 |

AD3111 |

70 |

0.50 |

0.7 | 1982-03-15 |

AD3111 |

80 |

1.25 |

1.5 | 1982-04-15 |

AD3111 |

80 |

1.00 |

1.2 | 1982-09-15 |

AD3111 |

180 |

1.00 |

1.2 | 1982-10-15 |

AD3112 |

60 |

0.75 |

1.0 | 1982-01-01 |

AD3112 |

60 |

0.50 |

0.7 | 1982-02-01 |

AD3112 |

60 |

0.75 |

1.0 | 1982-12-01 |

AD3112 |

60 |

1.00 |

1.2 | 1983-01-01 |

AD3112 |

70 |

0.75 |

1.0 | 1982-01-01 |

AD3112 |

70 |

0.50 |

0.7 | 1982-02-01 |

AD3112 |

70 |

1.00 |

1.2 | 1982-03-15 |

AD3112 |

70 |

0.25 |

0.5 | 1982-08-15 |

AD3112 |

80 |

0.35 |

0.6 | 1982-08-15 |

AD3112 |

80 |

0.50 |

0.7 | 1982-10-15 |

AD3112 |

180 |

0.50 |

0.7 | 1982-08-15 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 24. A Query Result Formatted to Display One Decimal Place

Controlling the Display of Leading Zeros

You can show leading zeros for a numeric column. Do this for the ACTNO column

by typing:

format column actno zeros on

The above FORMAT command displays Figure 25 on page 49.

Interactive SQL Guide and Reference

PROJNO | ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

------ | ------ | ------- | -------------- | ---------- |

AD3100 |

00010 |

0.50 |

0.7 | 1982-01-01 |

AD3111 |

00060 |

0.80 |

1.0 | 1982-01-01 |

AD3111 |

00060 |

0.50 |

0.7 | 1982-03-15 |

AD3111 |

00070 |

1.50 |

1.7 | 1982-02-15 |

AD3111 |

00070 |

0.50 |

0.7 | 1982-03-15 |

AD3111 |

00080 |

1.25 |

1.5 | 1982-04-15 |

AD3111 |

00080 |

1.00 |

1.2 | 1982-09-15 |

AD3111 |

00180 |

1.00 |

1.2 | 1982-10-15 |

AD3112 |

00060 |

0.75 |

1.0 | 1982-01-01 |

AD3112 |

00060 |

0.50 |

0.7 | 1982-02-01 |

AD3112 |

00060 |

0.75 |

1.0 | 1982-12-01 |

AD3112 |

00060 |

1.00 |

1.2 | 1983-01-01 |

AD3112 |

00070 |

0.75 |

1.0 | 1982-01-01 |

AD3112 |

00070 |

0.50 |

0.7 | 1982-02-01 |

AD3112 |

00070 |

1.00 |

1.2 | 1982-03-15 |

AD3112 |

00070 |

0.25 |

0.5 | 1982-08-15 |

AD3112 |

00080 |

0.35 |

0.6 | 1982-08-15 |

AD3112 |

00080 |

0.50 |

0.7 | 1982-10-15 |

AD3112 |

00180 |

0.50 |

0.7 | 1982-08-15 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 25. A Query Result Formatted to Display Leading Zeros

Stop the display of leading zeros in the ACTNO column by typing:

format column actno zeros off

Changing the Displayed Length Attribute of a Column

You may want to modify the displayed length attribute of a column to fit your

report on the paper being used for printing. For example, to change the length

attribute of the PROJNO column of the current query result, type:

format column projno width 8

Chapter 5. Formatting Query Results

The above FORMAT command displays Figure 26.

Columns defined as variable-character have an additional command to control the

PROJNO

| ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

-------- | ------ | ------- | -------------- | ---------- |

AD3100

10 |

0.50 |

0.75 | 1982-01-01 |

AD3111

60 |

0.80 |

1.05 | 1982-01-01 |

AD3111

60 |

0.50 |

0.75 | 1982-03-15 |

AD3111

70 |

1.50 |

1.75 | 1982-02-15 |

AD3111

70 |

0.50 |

0.75 | 1982-03-15 |

AD3111

80 |

1.25 |

1.50 | 1982-04-15 |

AD3111

80 |

1.00 |

1.25 | 1982-09-15 |

AD3111

180 |

1.00 |

1.25 | 1982-10-15 |

AD3112

60 |

0.75 |

1.00 | 1982-01-01 |

AD3112

60 |

0.50 |

0.75 | 1982-02-01 |

AD3112

60 |

0.75 |

1.00 | 1982-12-01 |

AD3112

60 |

1.00 |

1.25 | 1983-01-01 |

AD3112

70 |

0.75 |

1.00 | 1982-01-01 |

AD3112

70 |

0.50 |

0.75 | 1982-02-01 |

AD3112

70 |

1.00 |

1.25 | 1982-03-15 |

AD3112

70 |

0.25 |

0.50 | 1982-08-15 |

AD3112

80 |

0.35 |

0.60 | 1982-08-15 |

AD3112

80 |

0.50 |

0.75 | 1982-10-15 |

AD3112

180 |

0.50 |

0.75 | 1982-08-15 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 26. A Query Result Formatted to Display a Different Column Width

displayed length attribute. See the explanation of the VARCHAR keyword in the

FORMAT command description in “Chapter 10. ISQL Commands” on page 105.

You have now finished formatting the report. To produce a copy, type a PRINT

command before typing the END command.

You can specify more than one keyword in a single FORMAT command. For

example, the following command combines several keywords:

format separator ' | ' column 'expression 1' name -

'staff + .25' dplaces 1 column actno zeros off

Because ISQL treats this information as a single command, considerable processing

time is saved. Multiple-keyword entry is described in more detail under “Using

More Than One Keyword in a FORMAT Command” on page 56.

EXERCISE 3 (Answers are in Appendix A. Answers to the Exercises, page 165.)

Perform the following:

1. Retrieve the employee number, project number, and proportion of employee time from the EMP_ACT table for

project numbers IF1000 and IF2000. Order the results primarily by project number and secondarily by

employee number.

2. Separate all columns with two blanks, an asterisk, and two more blanks.

3. Display all columns except the EMPNO column.

4. Change the column heading for the EMPTIME column to PROPTN and its displayed length attribute to 8

characters.

Interactive SQL Guide and Reference

Formatting Reports

Earlier in this chapter, you learned formatting techniques to create a report. In this

section, you learn to prepare totals, create an outline format, and specify report

titles.

To illustrate these functions, type the following query statement and format

commands:

select projno,actno,acstaff,acstaff + .25,acstdate -

from proj_act -

where projno = 'AD3100' -

or projno = 'AD3111' -

or projno = 'AD3112' -

order by projno,actno

format separator ' | ' column 'expression 1' name -

'staff + .25' -

column projno width 8

This produces the following result in Figure 27.

PROJNO

| ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

-------- | ------ | ------- | -------------- | ---------- |

AD3100

10 |

0.50 |

0.75 | 1982-01-01 |

AD3111

60 |

0.80 |

1.05 | 1982-01-01 |

AD3111

60 |

0.50 |

0.75 | 1982-03-15 |

AD3111

70 |

1.50 |

1.75 | 1982-02-15 |

AD3111

70 |

0.50 |

0.75 | 1982-03-15 |

AD3111

80 |

1.25 |

1.50 | 1982-04-15 |

AD3111

80 |

1.00 |

1.25 | 1982-09-15 |

AD3111

180 |

1.00 |

1.25 | 1982-10-15 |

AD3112

60 |

0.75 |

1.00 | 1982-01-01 |

AD3112

60 |

0.50 |

0.75 | 1982-02-01 |

AD3112

60 |

0.75 |

1.00 | 1982-12-01 |

AD3112

60 |

1.00 |

1.25 | 1983-01-01 |

AD3112

70 |

0.75 |

1.00 | 1982-01-01 |

AD3112

70 |

0.50 |

0.75 | 1982-02-01 |

AD3112

70 |

1.00 |

1.25 | 1982-03-15 |

AD3112

70 |

0.25 |

0.50 | 1982-08-15 |

AD3112

80 |

0.35 |

0.60 | 1982-08-15 |

AD3112

80 |

0.50 |

0.75 | 1982-10-15 |

AD3112

180 |

0.50 |

0.75 | 1982-08-15 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 27. A Formatted Query Result to Be Used to Illustrate Report Format

Obtaining an Outline Report Format

An outline report format suppresses the display of duplicate values in a particular

column. To provide this feature for the PROJNO column, type the following

command:

format group (projno)

This produces Figure 28 on page 52.

Chapter 5. Formatting Query Results

PROJNO

| ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

-------- | ------ | ------- | -------------- | ---------- |

AD3100

10 |

0.50 |

0.75 | 1982-01-01 |

AD3111

60 |

0.80 |

1.05 | 1982-01-01 |

60 |

0.50 |

0.75 | 1982-03-15 |

70 |

1.50 |

1.75 | 1982-02-15 |

70 |

0.50 |

0.75 | 1982-03-15 |

80 |

1.25 |

1.50 | 1982-04-15 |

80 |

1.00 |

1.25 | 1982-09-15 |

180 |

1.00 |

1.25 | 1982-10-15 |

AD3112

60 |

0.75 |

1.00 | 1982-01-01 |

60 |

0.50 |

0.75 | 1982-02-01 |

60 |

0.75 |

1.00 | 1982-12-01 |

60 |

1.00 |

1.25 | 1983-01-01 |

70 |

0.75 |

1.00 | 1982-01-01 |

70 |

0.50 |

0.75 | 1982-02-01 |

70 |

1.00 |

1.25 | 1982-03-15 |

70 |

0.25 |

0.50 | 1982-08-15 |

80 |

0.35 |

0.60 | 1982-08-15 |

80 |

0.50 |

0.75 | 1982-10-15 |

180 |

0.50 |

0.75 | 1982-08-15 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 28. A Query Result Displayed in Outline Report Format

Outlining is appropriate only on a column that has been ordered into groups of

similar values (through an ORDER BY clause in the SELECT statement). Although

outlining can be performed on an unordered column, the frequent changes in the

values that are likely to occur in that column cause such outlining to be of little

value.

Outlining is normally performed when you specify GROUP on a FORMAT

command. For a description of how this process is controlled, see the FORMAT

command section in “Chapter 10. ISQL Commands” on page 105.

Note: If you have VARCHAR or VARGRAPHIC columns with values that differ

only by trailing blanks, the FORMAT GROUP command treats them as

duplicates. Therefore, if you have 'AD3100' and 'AD3100 ' in a VARCHAR

column, a FORMAT GROUP command eliminates one of them.

Obtaining Totals for Reports

To produce a total for the STAFF + .25 column, type:

format total ('staff + .25')

Note: Although included here, the parentheses are necessary only when specifying

multiple columns to be totaled.

This FORMAT command displays a result similar to Figure 29 on page 53.

Interactive SQL Guide and Reference

PROJNO

| ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

-------- | ------ | ------- | -------------- | ---------- |

AD3100

10 |

0.50 |

0.75 | 1982-01-01 |

AD3111

60 |

0.80 |

1.05 | 1982-01-01 |

60 |

0.50 |

0.75 | 1982-03-15 |

70 |

1.50 |

1.75 | 1982-02-15 |

70 |

0.50 |

0.75 | 1982-03-15 |

80 |

1.25 |

1.50 | 1982-04-15 |

80 |

1.00 |

1.25 | 1982-09-15 |

180 |

1.00 |

1.25 | 1982-10-15 |

AD3112

60 |

0.75 |

1.00 | 1982-01-01 |

60 |

0.50 |

0.75 | 1982-02-01 |

60 |

0.75 |

1.00 | 1982-12-01 |

60 |

1.00 |

1.25 | 1983-01-01 |

70 |

0.75 |

1.00 | 1982-01-01 |

70 |

0.50 |

0.75 | 1982-02-01 |

70 |

1.00 |

1.25 | 1982-03-15 |

70 |

0.25 |

0.50 | 1982-08-15 |

80 |

0.35 |

0.60 | 1982-08-15 |

80 |

0.50 |

0.75 | 1982-10-15 |

180 |

0.50 |

0.75 | 1982-08-15 |

| ============== |

18.65 |

* End of Result ************* 19 Rows Displayed ****** Cost Estimate is

Figure 29. A Query Result Formatted to Produce Totals

You can also create subtotals for this column by typing the following command:

format subtotal ('staff + .25')

This displays Figure 30.

PROJNO

| ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

-------- | ------ | ------- | -------------- | ---------- |

AD3100

10 |

0.50 |

0.75 | 1982-01-01 |

| -------------- |

******** |

0.75 |

AD3111

60 |

0.80 |

1.05 | 1982-01-01 |

60 |

0.50 |

0.75 | 1982-03-15 |

70 |

1.50 |

1.75 | 1982-02-15 |

70 |

0.50 |

0.75 | 1982-03-15 |

80 |

1.25 |

1.50 | 1982-04-15 |

80 |

1.00 |

1.25 | 1982-09-15 |

180 |

1.00 |

1.25 | 1982-10-15 |

| -------------- |

******** |

8.30 |

AD3112

60 |

0.75 |

1.00 | 1982-01-01 |

60 |

0.50 |

0.75 | 1982-02-01 |

60 |

0.75 |

1.00 | 1982-12-01 |

60 |

1.00 |

1.25 | 1983-01-01 |

70 |

0.75 |

1.00 | 1982-01-01 |

70 |

0.50 |

0.75 | 1982-02-01 |

70 |

1.00 |

1.25 | 1982-03-15 |

70 |

0.25 |

0.50 | 1982-08-15 |

Figure 30. A Query Result Formatted to Produce Subtotals

Notice that subtotals are created in the STAFF + .25 column for each change in the

value in the PROJNO column.

Chapter 5. Formatting Query Results

The conditions on which subtotals are created are defined, like outlining, by the

GROUP option of a FORMAT command. Subtotals are created whenever the value

changes in the column (or columns) specified with FORMAT GROUP. Columns

identified with FORMAT GROUP should have been specified in the ORDER BY

clause of the SELECT statement that produced the query results, and should

appear in the same sequence as they appeared in the ORDER BY clause.

Otherwise, the resulting subtotals can be meaningless.

You can erase these totals and subtotals. For example, to erase the totals in the

above report, you would type:

format total erase

This FORMAT command would display a result similar to Figure 31.

Subtotals can be erased and included in the same manner by substituting

PROJNO

| ACTNO | ACSTAFF |

STAFF + .25 | ACSTDATE

-------- | ------ | ------- | -------------- | ---------- |

60 |

0.50 |

0.75 | 1982-03-15 |

70 |

1.50 |

1.75 | 1982-02-15 |

70 |

0.50 |

0.75 | 1982-03-15 |

80 |

1.25 |

1.50 | 1982-04-15 |

80 |

1.00 |

1.25 | 1982-09-15 |

180 |

1.00 |

1.25 | 1982-10-15 |

| -------------- |

******** |

8.30 |

AD3112

60 |

0.75 |

1.00 | 1982-01-01 |

60 |

0.50 |

0.75 | 1982-02-01 |

60 |

0.75 |

1.00 | 1982-12-01 |

60 |

1.00 |

1.25 | 1983-01-01 |

70 |

0.75 |

1.00 | 1982-01-01 |

70 |

0.50 |

0.75 | 1982-02-01 |

70 |

1.00 |

1.25 | 1982-03-15 |

70 |

0.25 |

0.50 | 1982-08-15 |

80 |

0.35 |

0.60 | 1982-08-15 |

80 |

0.50 |

0.75 | 1982-10-15 |

180 |

0.50 |

0.75 | 1982-08-15 |

| -------------- |

******** |

9.60 |

Figure 31. A Query Result Formatted with the Totals Erased

SUBTOTAL for TOTAL in the above example. Erasing subtotals also erases totals

for the specified columns.

There are some variations in the use of GROUP, SUBTOTAL, and TOTAL with

FORMAT commands. See the FORMAT command section in “Chapter 10. ISQL

Commands” on page 105 for details.

Creating Titles for Printed Reports

Specify a top title for the current report by typing:

format ttitle 'summary of employee time'

The quotation marks are needed because the title contains blanks. The command

displays the current top title, and prompts you to return to the query result by

issuing the following message in the status area:

Interactive SQL Guide and Reference

DB2 Server for VSE

Press clear key to continue

DB2 Server for VM

MORE ...

To return to the query result, press CLEAR.

The top title can be erased and replaced with the first 100 characters of the

associated SELECT statement by typing:

format ttitle erase

A bottom title can also be specified. Use the following command to create a bottom

title for this report:

format btitle 'company confidential'

The bottom title can also be erased by typing:

format btitle erase

Top and bottom titles are centered in the top and bottom margins of the printed

report. Although the titles cannot be seen until your report is printed, you can

view them by typing FORMAT TTITLE (or FORMAT BTITLE) without specifying a

title. For example, type:

format ttitle

Pressing CLEAR in response to this message returns you to the query result.

Now print a copy of this report by typing:

Your printed report is similar to Figure 32 on page 56.

Chapter 5. Formatting Query Results

08/10/89

SUMMARY

OF EMPLOYEE TIME

PROJNO

ACTNO

ACSTAFF

STAFF + .25

ACSTDATE

_______

_____

_______

_____________

__________

AD3100

0.50

0.75

1982-01-01

______________

********

0.75

AD3111

0.80

1.05

1982-01-01

0.50

0.75

1982-03-15

1.50

1.75

1982-02-15

0.50

0.75

1982-03-15

1.25

1.50

1982-04-15

1.00

1.25

1982-09-15

180

1.00

1.25

1982-10-15

______________

********

8.30

AD3112

0.75

1.00

1982-01-01

0.50

0.75

1982-02-01

0.75

1.00

1982-12-01

1.00

1.25

1983-01-01

0.75

1.00

1982-01-01

0.50

0.75

1982-02-01

1.00

1.25

1982-03-15

0.25

0.50

1982-08-15

0.35

0.60

1982-08-15

0.50

0.75

1982-10-15

180

0.50

0.75

1982-08-15

______________

********

9.60

==============

18.65

COMPANY CONFIDENTIAL

Figure 32. Example of a Printed Report

Using More Than One Keyword in a FORMAT Command

The following example provides additional practice in using multiple-keyword

FORMAT commands.

Assume you want to create a report from a query result that is to include the

following modifications:

v Change the blanks that separate columns to another character (SEPARATOR

keyword).

v Change the name of a column heading (NAME keyword).

v Leave out a column (EXCLUDE keyword).

v Specify the number of decimal places to be displayed for decimal columns

(DPLACES keyword).

v Create subtotals for specific columns (SUBTOTAL keyword and GROUP

keyword).

v Create totals for specific columns (TOTAL keyword and GROUP keyword).

First, type the following statement:

Interactive SQL Guide and Reference

select actno,acstaff,acstdate -

from proj_act -

where actno between 0 and 20 -

order by actno

This produces the display in Figure 33.

Using the above query result, type the following FORMAT command:

ACTNO ACSTAFF ACSTDATE

------

-------

----------

0.50

1982-01-01

1.00

1982-01-01

0.25

1982-01-01

1.00

1982-01-01

0.50

1982-01-01

0.50

1982-01-01

0.50

1982-06-01

0.50

1982-01-01

1.00

1982-01-01

1.00

1982-01-01

1.00

1982-01-01

* End of Result *** 11 Rows Displayed ***Cost Estimate is 1********************

Figure 33. A Query Result to Be Used for a Multiple-Keyword Format Command

format column 2 name 'mean empl' dplaces 1 -

separator ' | ' exclude (acstdate) -

group actno -

subtotal ('mean empl')

The command produces Figure 34.

ACTNO | MEAN EMPL |

------ | --------- |

10 |

0.5 |

1.0 |

0.2 |

1.0 |

0.5 |

1.0 |

| --------- |

****** |

6.7 |

20 |

1.0 |

| --------- |

****** |

1.0 |

| ========= |

7.7 |

* End of Result ************* 11 Rows Displayed ****** Cost Estimate is

Figure 34. A Query Result Formatted by a Multiple-Keyword Format Command

Use the END command to end this query and clear the display.

Chapter 5. Formatting Query Results

Displaying Null Values and Arithmetic Errors

Null values (indicated by a question mark) and arithmetic errors (indicated by

number signs separated by blanks) are displayed in a formatted table. They are

treated as zeros in any total or subtotal calculations of the columns they appear in.

In the sample report in Figure 35, the mean employee staff is unknown for one

project activity, and contains an arithmetic error for another.

SUMMARY OF EMPLOYEE DISTRIBUTION FOR ACTIVITIES

AND 20

02/09/89

PAGE

ACTNO |

MEAN EMPL |

______ |

_________ |

10 |

0.5 |

# # # # # |

0.2 |

1.0 |

0.5 |

? |

1.0 |

_________ |

****** |

6.7 |

20 |

1.0 |

_________ |

****** |

1.0 |

========= |

7.7 |

COMPANY CONFIDENTIAL

Figure 35. Sample Report Displaying Null Values and Arithmetic Errors

Controlling Null-Field Displays

When formatting reports, you may want something other than a question mark to

be used to represent null values. To illustrate, type the following statement:

select *

from department -

order by mgrno

This displays Figure 36 on page 59.

Interactive SQL Guide and Reference

DEPTNO DEPTNAME

MGRNO ADMRDEPT

------

--------------------

------

--------

A00

SPIFFY COMPUTER SERV< 000010

A00

B01

PLANNING

000020

A00

C01

INFORMATION CENTER

000030

A00

E01

SUPPORT SERVICES

000050

A00

D11

MANUFACTURING SYSTEM< 000060

D01

D21

ADMINISTRATION SYSTE< 000070

D01

E11

OPERATIONS

000090

E01

E21

SOFTWARE SUPPORT

000100

E01

D01

DEVELOPMENT CENTER

A00

* End of Result *** 9 Rows Displayed ***Cost Estimate is 1*********************

Figure 36. A Query Result Displaying a Null Value

To format a report from this query result that replaces the question mark with

*NULL*, type:

format null *null*

This FORMAT command should display a result similar to Figure 37.

DEPTNO DEPTNAME

MGRNO ADMRDEPT

------

--------------------

------

--------

A00

SPIFFY COMPUTER SERV< 000010

A00

B01

PLANNING

000020

A00

C01

INFORMATION CENTER

000030

A00

E01

SUPPORT SERVICES

000050

A00

D11

MANUFACTURING SYSTEM< 000060

D01

D21

ADMINISTRATION SYSTE< 000070

D01

E11

OPERATIONS

000090

E01

E21

SOFTWARE SUPPORT

000100

E01

D01

DEVELOPMENT CENTER

*NULL*

A00

* End of Result *** 9 Rows Displayed ***Cost Estimate is 1*********************

Figure 37. A Query Result Displaying a Formatted Null Field

The maximum number of characters that can be used as a null field indicator is 20.

Use the END command to end this query and clear the display.

Controlling Query Format Characteristics

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:

v Punctuation displayed for numeric columns.

v Separation characters displayed between columns.

v Characters displayed for null fields.

v The displayed length attribute of variable character fields. This topic is explained

in the description of the VARCHAR item in the FORMAT command section of

“Chapter 10. ISQL Commands” on page 105.

Chapter 5. Formatting Query Results

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” on page 73.

Setting the Format Characteristics by Using the SET

Command

ISQL gives you some control over what you see on your display. You can specify:

v Punctuation displayed for numeric fields

v Separation characters displayed between columns

v Characters displayed for null fields

v Page size of printed reports

v Language of messages and HELP text.

Furthermore, you can specify all of these features in one command and get a list of

the current settings.

Punctuation Displayed for Numeric Fields

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)

1234.56

1,234.56

1.234,56

(a blank)

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 on page 61.

Interactive SQL Guide and Reference

EXPRESSION 1

--------------

2,000.00

* End of Result *** 1 Rows Displayed ***Cost Estimate is 1*********************

Figure 38. A Query Result with a Formatted Numeric Field

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)

Separation Characters Displayed between Columns

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:

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 *

Characters Displayed for Null Fields

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.

Chapter 5. Formatting Query Results

Number of Copies of Printed Reports (DB2 Server for VSE)

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:

SET COPIES

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.

Page Size of Printed Reports

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

SET PAGEsize WIDth

LENgth

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.

Language of Messages and HELP Text

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

Interactive SQL Guide and Reference

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 Table 1.

Table 1. 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.

Multiple Format Characteristics

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” on page 105.

List of Current Settings

The current settings of all the format characteristics described above can be listed

on your display. List them by typing:

Chapter 5. Formatting Query Results

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

Printing Reports on a Workstation Printer (DB2 Server for

VSE)

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

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

set printroute wkstat

where wkstat is replaced with the identifier for the required POWER remote

workstation.

The system printer is specified as:

print system

set printroute system

Interactive SQL Guide and Reference

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 17