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

EXERCISE 4 (Answers are in Appendix A. Answers to the Exercises, page 166.)

Perform the following:

1. For DB2 Server for VSE, set the number of copies requested for printed reports to two.

2. List the current settings for format characteristics.

3. Retrieve all of the information from PROJ_ACT where the project number is AD3112; order the result by

activity end date.

4. Create an outline format for the activity end date column and exclude the activity start date column.

5. Create a top title called PERSONNEL PROGRAMMING DEADLINES.

6. For DB2 Server for VSE, request printed copies of this report.

7. For DB2 Server for VM, request two printed copies of this report.

Chapter 5. Formatting Query Results

Chapter 6. Storing SQL Statements

Storing frequently-used SQL statements saves you from retyping them. On longer

statements, such as that used in the previous chapter to format a report, typing

errors can be avoided by storing the statement and reusing it. A stored SQL

statement may also contain placeholders.

Storing the Current Statement

Type the following:

select deptno,mgrno -

from department -

order by mgrno

Now, type the following format commands:

format separator ' | ' -

column mgrno width 8

End the query.

Store the current SQL statement using the storage name dept by typing the

following:

store dept

If you know that the stored command DEPT already exists, and you wish to

replace it, include the keyword REPLACE in your STORE command, as follows:

store dept replace

When the STORE command is processed, a message informs you that the SQL

statement is stored. Use storage names that are 8 characters or less in length. Do

not use the name previous because ISQL always saves the current SQL statement,

and names it previous when a new SQL statement is typed.

Protecting a Stored Statement

If the name you use with the STORE command is the name of a statement that is

already stored, and you do not use the REPLACE keyword, you receive a warning

message. The warning gives you the following choices:

v REPLACE the existing stored SQL statement with the new statement.

v END the processing of the STORE command, leaving the existing stored SQL

statement intact.

v Enter a new name under which your new statement is to be stored.

The message itself appears as in Figure 39 on page 68.

ARI7955I The system ended your query result to process your command.

ARI7577D A stored SQL statement named DEPT already exists.

Enter a new name to store the SQL statement, or enter

one of the following keywords:

REPLACE - to replace the existing stored SQL statement, or

END - to end the store command processing.

Figure 39. Warning Message Displayed If You Try to Store a Name Already Stored

You can use the REPLACE function to replace an old statement with a new one. If

you have already stored a statement under the name DEPT, you can still store the

current statement under the same name by typing:

replace

and pressing ENTER.

If you do not want to replace the previously stored statement, simply type:

end

Press ENTER.

The END command returns you to the ISQL environment.

Starting a Stored Statement

When you start a stored SQL statement, ISQL moves it to the command buffer and

then processes it. Start the statement stored as DEPT by typing the following:

start dept

When the results appear on your display, verify that the FORMAT commands you

typed with the SELECT statement stored as DEPT are still in effect.

Note: Only the SELECT portion of the stored statement is recalled. The stored

formatting commands are not shown although you can see their effect on

the query.

Type END. The stored SELECT statement called DEPT is not erased. It remains in

storage until you decide to erase it.

Starting a Stored Statement That Contains Placeholders

First, store a statement that contains placeholders. Type:

hold select &1 -

from &2 -

where &3

store myquery

Now start the statement, and add parameters to complete it. Type:

start myquery (* employee empno='000010')

This produces the following statement:

select * from employee where empno='000010'

Interactive SQL Guide and Reference

and displays Figure 40.

EMPNO FIRSTNME

MIDINIT LASTNAME

WORKDEPT PHONENO HIREDATE

------

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

-------

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

--------

-------

----------

000010

CHRISTINE

HAAS

A00

3978

1965-01-01

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

Figure 40. A Query Result from Starting a Stored Statement with Placeholders

Note: A message is displayed, indicating that any formatting performed while

viewing this query result is not saved. This condition is explained in detail

in “Saving the Format Information”.

Because the placeholders are replaced by the parameters when the statement is

processed, the stored SQL statement remains unchanged and can be reused.

Recalling a Stored Statement

A stored SQL statement can be recalled to the command buffer to become the

current SQL statement without being processed. You can do this to make changes

to it and store it again, or to simply verify that it is the statement that you want to

process next. For example, recall DEPT by typing:

recall dept

The recalled statement resembles:

SELECT DEPTNO,MGRNO FROM DEPARTMENT ORDER BY MGRNO

You can also use the RECALL command to display the current SQL statement, or

to place the previous SQL statement in the command buffer at the top of the stack

so that it becomes the current SQL statement. To recall the previous SQL statement,

type:

recall previous

The statement that was current in the buffer becomes the new previous SQL

statement.

To display the current SQL statement in the command buffer, type:

recall

or press PF5. The absence of a name on the RECALL command instructs ISQL to

display the current SQL statement.

Saving the Format Information

The formatting information performed while viewing the query result is always

saved when the query does not contain placeholders. When the query contains

placeholders, however, the formatting information is saved only if the placeholders

are not in the SELECT or FROM clause. To illustrate how format information is

saved, remove the placeholders and insert actual values into the SELECT and

FROM clauses of MYQUERY, but retain the placeholder in the WHERE clause by

typing:

Chapter 6. Storing SQL Statements

hold select * -

from employee -

where &1

store myquery replace

Notice the keyword REPLACE; it instructs ISQL that the statement you are now

storing replaces the previous version of MYQUERY. If the keyword REPLACE is

not used, ISQL issues the warning message described earlier. Of course, you can

choose another name and save both the old and new queries.

Now start MYQUERY and include the parameter that completes the WHERE

clause:

start myquery (empno='000010')

This processes the following statement:

select * -

from employee -

where empno='000010'

Add formatting information to the query result. Type:

format separator ' | '

End the query, and then save the formatting information by typing:

store myquery replace

Start the query again, but substitute a different value for the placeholder:

start myquery (empno='000150')

You see that the query result has retained the separator that you formatted and

stored.

Changing a Stored Statement

Changes can be made to a stored SQL statement using the CHANGE command

discussed earlier in this chapter under “Changing the Current SQL Statement” on

page 38. For example, to change the ORDER BY clause in the statement stored as

DEPT, perform the following:

1. Use the RECALL command to display the stored statement. Type:

recall dept

The recalled statement appears as:

SELECT DEPTNO,MGRNO FROM DEPARTMENT ORDER BY MGRNO

2. For this example, change the ORDER BY feature to the DEPTNO column. Type:

change /by mgrno/by deptno/

The changed statement is displayed as:

SELECT DEPTNO,MGRNO FROM DEPARTMENT ORDER BY DEPTNO

3. Now, store the changed SELECT statement as DEPT2:

store dept2

4. To verify the new statement, use the RECALL command:

recall dept2

Interactive SQL Guide and Reference

If you look at the query results of DEPT and DEPT2, you will notice that the

FORMAT information is maintained. As long as the statement to be changed or

stored contains an unchanged SELECT or FROM clause, FORMAT information is

saved.

Listing the Names of Stored Statements

To view the names and contents of stored statements, type:

list sql *

This displays information similar to Figure 41.

DEPT

SELECT DEPTNO,MGRNO FROM DEPARTMENT ORDER BY MGRNO

DEPT2

SELECT DEPTNO,MGRNO FROM DEPARTMENT ORDER BY DEPTN

MYQUERY

SELECT * FROM EMPLOYEE WHERE &1;

ARI7620I You have

3 stored SQL statements.

Figure 41. A Display of the Stored SQL Statements

Notice that only the first 50 characters of each statement are displayed.

You may wish to store a statement but are unsure if the stored name you want to

use is already in use. Using the LIST SQL * command becomes tedious if there are

many stored statements. Instead, you can list a specific stored statement by using

its name in the LIST command. Type:

list sql getsup

You receive a message indicating that GETSUP is not found, and you can use it as

the new store name.

You can also use this form of the LIST command to view statements whose name

you know. For example, type the following to view DEPT2:

list sql dept2

You can use multiple statement names with the LIST command. For example, type:

list sql dept myquery

Renaming a Stored Statement

You can rename stored SQL statements. For example, type:

rename dept olddept

The stored statement DEPT is now called OLDDEPT. Do not use previous with the

RENAME command.

Erasing a Stored Statement

When you no longer need a stored SQL statement, you can erase it. Type:

erase olddept

The stored statement called OLDDEPT has been erased.

You can erase several stored statements with a single ERASE command:

Chapter 6. Storing SQL Statements

erase first second

EXERCISE 5 (Answers are in Appendix A. Answers to the Exercises, page 166.)

1. Recall the stored query MYQUERY.

2. Change the query so that information is retrieved for salaries between $25000 and $30000, and is ordered by

the employee’s first name.

3. Start the query.

4. Exclude the middle initial from the result.

5. Separate all columns with a blank, a vertical bar, an asterisk, a vertical bar, and a blank.

6. Change the EDLEVEL column heading to SCHOOL YEARS.

7. End the display and store the command along with all its formatting information using the name EXER11.

8. List all the stored SQL statements.

9. Retrieve the help information for the ISQL STORE command.

Interactive SQL Guide and Reference

Chapter 7. Creating and Using Routines

A routine is a series of ISQL commands, SQL statements, or both, which is stored

under an identifying name. When a routine is run, each command or statement in

the routine is performed as if it had been typed from the keyboard. Routines save

(and later run) frequently-used sequences of commands and statements, such as a

series of ISQL SET commands that set certain operational characteristics.

Routines offer a number of features to users. In addition to using them to perform

a frequently used set of commands or statements, you can:

v Define a profile routine that is run automatically when you start ISQL

v Use a routine to start one or more stored SQL statements

v Share routines with other users

v Use a routine to display and print query results.

Running Routines When ISQL Is Started

Profile Routines

A routine named PROFILE, that was previously set up by you, is performed

automatically when you start ISQL. This allows unique terminal-session

characteristics to be established before any information is typed. You can set these

characteristics yourself by using the SET command described in “Chapter 10. ISQL

Commands” on page 105.

DB2 Server for VM

Note: The PROFILE routines described in this section are ISQL PROFILE

routines and should not be confused with a VM PROFILE EXEC.

Placeholders are not allowed in a profile routine. Because the routine is run

automatically, there is no way to pass parameters to substitute for the placeholders.

In addition, a unique master profile routine can be created by someone having

DBA authority. It is created with an authorization ID of SQLDBA and is run

automatically for each user who starts ISQL as if it were the user's own profile

routine.

Both the master and your own profile routines run automatically when you start

ISQL. Your routine runs second, and its operational characteristics override those

in the master routine.

Routines to Which Parameters Can Be Passed (DB2 Server for

VM)

After the profile routine is run, you can invoke a subsequent routine to run as part

of the ISQL signon process. Parameters can be passed on to this routine. The ISQL

EXEC procedure invokes ISQL and runs this subsequent routine.

The ISQL EXEC procedure has the following format:

ISQL

V1SCRIO

ROUTine

( routine_name

)

(parameter_list)

You can create your own EXEC procedure from the ISQL EXEC procedure. For

example, you can add commands to your EXEC to change the PF-key settings or to

route printed reports.

You set your own terminal-session characteristics by using the SET command

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

Using the ISQL Transaction Identifier (DB2 Server for VSE)

A profile routine cannot contain parameters, but an ISQL routine that runs as part

of the ISQL signon procedure can contain parameters. You use the 4-character

transaction identifier ISQL to start ISQL and to run a routine. Two ways of using

the transaction identifier are:

1. Start ISQL, type your user ID and password, which the signon screen prompts

you to do.

2. Start ISQL from another CICS transaction that suppresses the signon screen and

the related terminal messages. The format for doing this along with its

explanation are found in “Appendix E. Suppressing the ISQL Sign-On Display

for DB2 Server for VSE” on page 187.

For method 1, the ISQL transaction identifier and routine have the following

format:

ISQL

routine_name

(parameter_list)

Both of the above-mentioned methods let you specify a routine, and any

parameters to be passed to that routine, to be run by ISQL after it has run a profile

routine. For method 1, the ISQL transaction identifier and the routine name and all

its parameters must not be longer than the screen width or 132, whichever is less.

For method 2, the command which suppresses the ISQL signon display as

described in “Appendix E. Suppressing the ISQL Sign-On Display for DB2 Server

for VSE” on page 187, must not be longer than the screen width or 132, whichever

is less.

You can insert any number of blanks after the routine name or the parameters and

the surrounding parentheses. For example:

isql

routine

(abc (1 2

3))

You can create your own routine or profile routine. For example, in your profile,

you can add commands to change the number of copies of printed reports or route

printed reports.

Interactive SQL Guide and Reference

You can change the terminal-session characteristics. You can do this each time you

use ISQL (SET command, page 149), or you can have ISQL do it for you (SET

commands in the PROFILE routine).

Establishing Where Routines Are Stored

Routines are stored in a special table called ROUTINE. It consists of four columns:

NAME, SEQNO, COMMAND, and REMARKS (optional). An example of a routine

table is shown in Table 2.

Table 2. Example of a Routine Table

NAME

SEQNO COMMAND

REMARKS

EMPLREP

SELECT PROJNO,ACTNO,ACSTAFF -

GENERATE AND PRINT A

EMPLREP

FROM PROJ_ACT -

REPORT FOR PROJECT

EMPLREP

WHERE PROJNO IN (&1) -

STAFF

EMPLREP

ORDER BY PROJNO,ACTNO

EMPLREP

FORMAT GROUP PROJNO

EMPLREP

FORMAT SUBTOTAL ACSTAFF

EMPLREP

FORMAT TTITLE 'AVERAGE PROJECT STAFF'

EMPLREP

END

PRINTDEP

SELECT * FROM DEPARTMENT

FORMAT DEPARTMENT TABLE

PRINTDEP

FORMAT SEPARATOR ' | '

AND PRINT IT

PRINTDEP

FORMAT COLUMN DEPTNAME WIDTH 30

PRINTDEP

END

The NAME column identifies the rows that belong to a particular routine. SEQNO

specifies the sequence in which the commands and statements are executed. Use

sequence numbers that are increments of ten to allow for later additions. The

COMMAND column contains the SQL statements and ISQL commands.

Before creating routines, you must have a routine table. You can create your own

routine table if you have Resource authority or your own private dbspace (DB2

Server for VM user only). If you do not have Resource authority, ask the

appropriate person to create a routine table for you. The following SQL statement

illustrates the creation of a routine table:

create table routine (name char(8) not null, -

seqno integer not null, -

command varchar(254) not null, -

remarks varchar(254))

The CREATE TABLE statement is discussed in detail in “Chapter 8. Creating and

Managing Tables” on page 83.

When creating this table, use the CREATE TABLE statement exactly as shown

above, except for the REMARKS column which is optional. If it is included,

specifying a data type of VARCHAR with a length of 40 is usually sufficient. The

size selected for the REMARKS column should accommodate the largest entry

used. Allow nulls so that remarks are not required. The COMMAND column can

be a maximum length of 254 characters.

Chapter 7. Creating and Using Routines

Once the table is created, create an index for it so that when the table is referenced,

the commands or statements are displayed or executed in the correct order. To

create an index, type a statement similar to the following (substitute a name of

your choice for RINDEX):

create unique index rindex on routine -

(name, seqno)

For detailed information on the CREATE INDEX statement, refer to “Chapter 8.

Creating and Managing Tables” on page 83.

Storing a Routine

When you insert new commands or statements for a routine into the ROUTINE

table, always assign the same routine name in the NAME column. Commands and

statements are inserted into the routine table in the same manner as data is

inserted into any database manager table by using SQL INSERT statements or the

ISQL INPUT command.

The use of ampersands (&) in a routine is allowed only for creating placeholders

(see the description of the RUN command for more information on placeholders).

In the COMMAND column of the routine, be sure to:

v Put single quotation marks around any placeholder that stands for a character

data item. Enclose the COMMAND column (CHARACTER data type) in single

quotation marks, and use a pair of single quotation marks for each single

quotation mark that is to appear inside an SQL statement. This is shown in the

example below.

v Use the continuation character as if you were typing a long SQL statement.

In the following example, the INPUT command inserts the commands and

statements for a routine named QREPORT into the routine table.

input routine

'qreport',10,'select projno,acstaff -','begin:'

'qreport',20,'from proj_act -',null

'qreport',30,'where projno = '&1'' -',null

'qreport',40,'or actno = &2 -',null

'qreport',50,'order by projno',null

'qreport',60,'format group projno',null

'qreport',70,'format subtotal acstaff',null

'qreport',80,'print',null

'qreport',90,'end','done!'

end

The stored information should resemble Table 3.

Table 3. Routine Statements Inserted in the Routine Table

NAME

SEQNO COMMAND

REMARKS

QREPORT

SELECT PROJNO,ACSTAFF -

BEGIN:

QREPORT

FROM PROJ_ACT -

QREPORT

WHERE PROJNO = ’&1’ -

QREPORT

OR ACTNO = &2 -

QREPORT

ORDER BY PROJNO

QREPORT

FORMAT GROUP PROJNO

QREPORT

FORMAT SUBTOTAL ACSTAFF

QREPORT

END

DONE!

Interactive SQL Guide and Reference

To display your stored QREPORT routine, type:

select * -

from routine -

where name='qreport'

The display resembles Figure 42.

NAME

SEQNO COMMAND

REMARKS

--------

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

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

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

QREPORT

SELECT PROJNO,ACSTAF<

BEGIN:

QREPORT

FROM PROJ_ACT

QREPORT

WHERE PROJNO = '&1'

QREPORT

OR ACTNO = &2 -

QREPORT

ORDER BY PROJNO

QREPORT

FORMAT GROUP PROJNO

QREPORT

FORMAT SUBTOTAL ACST<

QREPORT

END

DONE!

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

Figure 42. Display of Routine Statements Inserted in the Routine Table

Managing a Routine

Routines stored in a table are managed in the same manner as data in any table by

using SQL UPDATE, INSERT, and DELETE statements.

For example, to modify the PRINTDEP routine (shown in Table 2 on page 75) to

use a double bar instead of a single bar to separate columns, you can type:

update routine -

set command = 'format separator '' || ''' -

where seqno = 20 and name = 'printdep'

As another example, you can delete the entire QREPORT routine by typing:

delete from routine -

where name = 'qreport'

Running a Routine

You run routines by using the ISQL RUN command. Use placeholders and

parameters in the same manner as you would use them in stored SQL statements.

For example, type the following command and placeholder replacement values to

run the QREPORT routine you created:

run qreport ('ad3100' 180)

Routine QREPORT creates a printed report similar to that shown in Figure 43 on

page 78. The query result is printed rather than displayed because of the PRINT

command contained in the routine. Producing a query result on the display is

discussed later in this chapter under “Using SELECT Statements in a Routine” on

page 79.

Chapter 7. Creating and Using Routines

. 08/27/89

SELECT

PROJNO,ACSTAFF FROM PROJ_ACT

WHERE

PROJNO

'AD3

. PROJNO ACSTAFF

. ______

_______

. AD3100

0.50

_______

. ******

0.50

. AD3111

1.00

_______

. ******

1.00

. AD3112

0.50

_______

. ******

0.50

. AD3113

0.75

1.00

0.50

_______

. ******

2.25

. MA2112

1.00

Figure 43. Example of a Report Created from a Routine

Running Shared Routines

You can run another user’s routine if you have obtained the SELECT privilege

(using a GRANT statement as described in “Sharing Your Tables with Other Users”

on page 91) on that user’s routine table.

Error Mode Processing in a Routine

When an error is detected in a routine, continued processing depends on a setting

you make to a special RUNMODE indicator. It indicates the mode of operation for

running routines.

The format for the command that sets the indicator is:

CONTInue

SET RUNMode

STOP

CANCEL

CONTInue

indicates that your routine continues to the next command or statement even if

errors are detected. You are likely to use this option when your routine

contains several independent commands or statements. Failure of a particular

command or statement does not affect the remaining commands and

statements.

Interactive SQL Guide and Reference

If you do not specify a RUNMODE option, CONTINUE becomes the default

value.

STOP

indicates that your routine is ended, but a ROLLBACK operation is not

performed. Select this option when commands and statements within a routine

are not interrelated, and you want to maintain any changes made to the

database. The remaining commands and statements are not executed if errors

exist in any of their predecessors.

CANCEL

indicates that your routine is ended and a ROLLBACK operation is performed.

Select this option when your routine contains a series of interrelated

commands and statements that update tables. When an error is detected, all

changes generated by the routine are erased and the integrity of the database is

maintained.

You can display the current RUNMODE setting by issuing the following command:

list set runmode

You can set the RUNMODE indicator at any time, either from the display terminal

or in a routine.

Using INPUT Commands in a Routine

If the ISQL INPUT command is used in a routine, all data and INPUT

subcommands (SAVE and BACKOUT) must also be entered from the routine.

Using SELECT Statements in a Routine

The use of SELECT statements in a routine (either an actual statement contained in

the routine or a stored statement started by the routine) is slightly different from

usage of the statements at a keyboard. SELECT statements contained in a routine

do not cause query results to be displayed automatically at the terminal.

The SELECT statement results can be displayed at the terminal by placing an ISQL

DISPLAY command in the routine at the desired location. This command can be

placed anywhere between the SELECT statement and its associated END

command. The DISPLAY command allows results to be formatted before they are

displayed. It also lets you type display commands from the keyboard after the

routine results are displayed.

To illustrate this process, type the following command:

insert into routine (name,seqno,command) -

values ('qreport',75,'display')

Chapter 7. Creating and Using Routines

The QREPORT routine now appears as Table 4.

Table 4. Modified QREPORT Routine

NAME

SEQNO

COMMAND

REMARKS

QREPORT

SELECT PROJNO, ACSTAFF -

BEGIN:

QREPORT

FROM PROJ_ACT -

QREPORT

WHERE PROJNO = ’&1’ -

QREPORT

OR ACTNO = &2 -

QREPORT

ORDER BY PROJNO

QREPORT

FORMAT GROUP PROJNO

QREPORT

FORMAT SUBTOTAL ACSTAFF

QREPORT

DISPLAY

QREPORT

END

DONE!

Run the routine by typing:

run qreport ('ad3100' 180)

The formatted query result is now displayed at your terminal. If required, you can

alter it using additional FORMAT commands. The display resembles Figure 44.

PROJNO ACSTAFF

------

-------

AD3100

0.50

-------

******

0.50

AD3111

1.00

-------

******

1.00

AD3112

0.50

-------

******

0.50

AD3113

0.75

1.00

0.50

-------

******

2.25

MA2112

1.00

-------

******

2.00

Figure 44. Formatted Query Result from Running Routine QREPORT

Type an END command to end the display of the query result and return to the

routine. The routine issues its remaining commands, causing a report containing

the modified query results to be printed.

Interactive SQL Guide and Reference

Chapter 8. Creating and Managing Tables

Some users of the database manager are authorized to create and manage their

own set of tables. There are two ways you can obtain the authority to create and

manage your own tables. You may have RESOURCE authority, or you may ask

someone with DBA authority to acquire a dbspace on your behalf. The latter lets

you create tables in a private dbspace only (dbspaces are discussed later in this

chapter.)

To determine what authority you have, type:

select resourceauth from sqldba.sysuserlist -

where name = user

A Y under RESOURCEAUTH indicates that you have resource authority and can create

and manage your own set of tables.

You can use the following sections if you have resource authority. They describe

how to manage your own tables, share them with other users, and improve query

performance.

Managing Your Own Tables

Managing your tables consists of the following:

v Determining what your information is

v Creating tables

v Creating referential structures

v Determining where your tables are stored

v Dropping (deleting) tables

v Dropping primary or foreign keys

v Activating or deactivating primary or foreign keys

v Copying data from one table into another

v Identifying the minimum contents of a table

v Adding columns to a table.

Querying Information about Your Tables

The database manager automatically maintains a catalog that contains several

catalog tables. The catalog holds information about the application server. Table

names, view names, table owners, view owners, and column names are just a part

of the information that can be found in the catalog tables. The data in the catalog

tables is available to SQL users through the normal SQL query facilities. Whenever

you use the catalog tables, remember to prefix the table name with the owner

name system.

To find out which tables and views belong to you, type the following:

select tname,remarks -

from system.syscatalog -

where creator = user

This query presents results similar to Figure 45 on page 84.

TNAME

REMARKS

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

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

ACT10

ACT60

PROJ1

PROJ2

ROUTINE

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

Figure 45. A Query Result Displaying Which Tables and Views Belong to You

Your display may provide additional information if you have created views and

tables of your own.

The column TNAME contains the table or view name. The REMARKS column contains

information about the table or view. You enter information into this field for your

tables or views using the COMMENT command. (For information on using the

COMMENT command, see the DB2 Server for VSE & VM SQL Reference manual.)

USER in the above query instructs the system to use your authorization ID when

selecting information from the SYSCATALOG table. You can use your

authorization ID in place of USER in the WHERE clause. If your authorization ID

is VELDA, for example, you can type the previous statement as:

select tname,remarks -

from system.syscatalog -

where creator = 'velda'

To determine the column names of the EMPLOYEE table, type the following:

select cname -

from system.syscolumns -

where tname = 'employee'

This query provides results similar to Figure 46.

CNAME

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

BIRTHDATE

BONUS

COMM

EDLEVEL

EMPNO

FIRSTNME

HIREDATE

JOB

LASTNAME

MIDINIT

PHONENO

SALARY

SEX

WORKDEPT

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

Figure 46. A Query Result Displaying the Column Names of the EMPLOYEE Table

For more information on the catalog, see the DB2 Server for VSE & VM SQL

Reference manual.

Interactive SQL Guide and Reference

Creating Your Own Tables

You can create your own table by providing the database manager with a name for

the table and the columns that you want it to contain. The names chosen can

consist of letters, numbers, and some special characters. If you want a blank or

other special character in the name, you must enclose the name in double

quotation marks.

When you provide the column names, you must also indicate the type of data for

each column. For more information on data types that can be defined for a

column, refer to the DB2 Server for VSE & VM SQL Reference manual.

For example, type the following SQL statement to create a table with employee

information for a particular quarter:

create table empl -

(empno char(6), -

lastname varchar(15), -

edlevel smallint, -

birthdate date, -

quarter integer)

Note: ISQL does not support INSERT, UPDATE, or SELECT for tables or views

with VARCHAR>254, VARGRAPHIC>127, LONG VARCHAR or LONG

VARGRAPHIC columns.

ISQL supports hexadecimal constants and graphic constants that can be used

to insert (INSERT or INPUT commands) or update data into DBCS columns

of length <= 127. Hexadecimal constants and graphic constants can also be

used in WHERE clauses with DBCS columns. See the DB2 Server for VSE &

VM SQL Reference manual for more information about this data type.

Defining Column Data

When you create a table, you can indicate that character columns will contain

single-byte characters, single or double-byte characters, or bit data by specifying

FOR SBCS DATA, FOR MIXED DATA, or FOR BIT DATA respectively.

You can also indicate that character or graphic columns will contain data

represented using a specific character set, code page, and encoding scheme by

specifying a Coded Character Set Identifier (CCSID). CCSIDs are important for

applications that use the DRDA protocol. With the DRDA protocol, data at the

application server and application requester could be represented by different

CCSIDs, as for example in the ASCII and EBCDIC environments. The CCSID

clause consists of the keyword CCSID followed by an integer and assigns the

integer identifier as the CCSID attribute of the column. This attribute defines the

specific character set, code page, and encoding scheme used to represent the data

in the character or graphic column.

For more information on column clauses and CCSIDs, see the DB2 Server for VSE

& VM SQL Reference manual.

Storing Your Tables

When you create a table, the database manager inserts it into a section of the

database that is reserved for you. For DB2 Server for VSE, these sections are called

dbspaces. For DB2 Server for VM, these sections are called private dbspaces.

There are private dbspaces and public dbspaces. If a DBA has given you

RESOURCE authority, you can acquire private dbspaces to contain your tables. You

Chapter 8. Creating and Managing Tables

can also create your tables in public dbspaces if you have RESOURCE authority. If

you do not have RESOURCE authority, you can create tables only if a DBA has

acquired a private dbspace for you.

If you have more than one dbspace, you can let the database manager choose the

one in which to place your tables, or you can specify the dbspace yourself. For

example, assume that you have had two dbspaces reserved for you named JOHN1

and JOHN2. To place the EMPL table in JOHN2, you type the following when you

create the table:

create table empl -

(empno char(6), -

lastname varchar(15), -

edlevel smallint, -

birthdate date, -

quarter integer) -

in john2

Copying Data from Other Tables

Data can be loaded into your table by copying data from another table. This is

performed using a variation of the INSERT statement. For example, load (copy)

data from the EMPLOYEE table into the EMPL table using the following INSERT

statement:

insert into empl (lastname,edlevel,birthdate,quarter) -

select lastname,edlevel,birthdate,'014'

from employee

This form of the INSERT statement uses a subquery instead of the VALUES clause.

The information retrieved by the subquery is placed into the table as if multiple

INSERT statements had been entered.

In the example above, the database manager is instructed to copy the employee

information from each row of the EMPLOYEE table into the EMPL table. The

database manager is also instructed to place the value 014 in the QUARTER

column for each row inserted.

Dropping a Table

You can drop (delete) one of your tables using a DROP TABLE statement. For

example, drop the table just created by typing:

drop table empl

The DROP TABLE statement deletes all the rows of the table, as well as the

definition of the table. (Recall that the DELETE statement, deletes individual rows.)

Identifying the Minimum Contents of a Table

When you create a table, you can identify which columns must contain entries.

You would typically want key fields such as EMPNO in the EMPLOYEE table to

always contain valid (not null) values. You prevent the use of nulls by specifying

the NOT NULL option in the column definitions of CREATE TABLE statements.

For example, the following statement can be used to prevent nulls from being used

for the EMPNO and LASTNAME columns of the EMPL table:

Interactive SQL Guide and Reference

create table empl -

(empno char(6) not null, -

lastname varchar(15) not null, -

edlevel smallint, -

birthdate date, -

quarter integer)

Specifying NOT NULL can be very useful in copying data from other tables

because it prevents incomplete rows from being inserted into your table.

In the above example, the BIRTHDATE column was defined as NOT NULL,

because nulls are allowed for the corresponding column in the EMPLOYEE table.

Adding a Column to a Table

When you create a table, it is not necessary to know or specify all columns.

Additional columns can be added later using the SQL statement ALTER TABLE.

For example, you can add a column to the EMPL table to contain employee salary

information by typing:

alter table empl -

add salary decimal(9,2)

You cannot specify the NOT NULL option in an ALTER TABLE statement. All

existing rows of the table assume a null value for the SALARY column as a result

of the ALTER TABLE statement.

After a column has been added, you can insert values using the UPDATE

statement.

Specifying Referential Constraints

Referential constraints can be specified when tables are defined, or they can be

added later.

When a primary key is added to an existing table, the database manager checks

the table to ensure that all keys are unique. When a foreign key is added, the

database manager checks all non-null foreign keys to ensure that they exist in the

parent table.

Constraints can also be dropped, activated, or deactivated. When referential

constraints have been deactivated, the database manager suspends checking, and

the tables become unavailable for access by anyone other than the owner of the

table or by someone possessing DBA authority.

Tables are deactivated, for example, to load large amounts of data onto the table.

Since checking of the data has been suspended, the speed of the loading process

increases considerably. When loading is complete, the table is activated.

When the primary key is deactivated, the primary key index on the parent table is

automatically dropped and all active dependent foreign keys are implicitly

deactivated. When a primary key is deactivated, all associated foreign keys are

implicitly deactivated. When a primary key or a dependent foreign key is

deactivated, all tables involved in the referential constraint become unavailable

until the keys are activated once again. Activating the keys causes the database

manager to validate the references in the data, and referential constraints are

automatically enforced once again.

Chapter 8. Creating and Managing Tables

Additional information is provided for activation and deactivation in “Activating

and Deactivating Primary Keys, Foreign Keys, or Unique Constraints” on page 90.

You can remove a referential constraint by dropping the foreign key. Dropping a

table containing foreign keys removes the constraints associated with its keys.

When a table containing a primary key is dropped, any foreign keys that reference

the primary key are dropped automatically, thereby removing all the constraints

that reference the primary key.

Identifying Required Privileges

The following table identifies the privileges required for changes to the referential

structure.

Table 5. Privileges Required

Privilege on

ALTER TABLE Clause

Parent Table

Dependent Table

Add Column

ALTER

Add Primary Key

ALTER

Add Foreign Key

REFERENCES

ALTER

Drop Primary Key

ALTER

REFERENCES

Drop Foreign Key

REFERENCES

ALTER

Deactivate Primary Key

ALTER

REFERENCES

Deactivate Foreign Key

REFERENCES

ALTER

Activate Primary Key

ALTER

REFERENCES

Activate Foreign Key

REFERENCES

REFERENCES²

Create Foreign Key

REFERENCES

not applicable

Create Primary Key

not applicable

Notes:

1. The REFERENCES privilege is required only if the parent table has any

dependents.

2. The ALTER privilege is required if the primary key has any foreign keys

defined on it.

To add, drop, deactivate, activate, or create a unique constraint, you must have the

ALTER privilege on the table. See the discussions of the CREATE TABLE and

ALTER TABLE statements in the DB2 Server for VSE & VM SQL Reference manual.

Creating a Table That Contains a Primary Key

You can create a table with a primary key by using a PRIMARY KEY clause in the

CREATE TABLE statement. This clause specifies the column that is the primary

Interactive SQL Guide and Reference

key. For example, create a new table for students that contains a student first

name, last name, and student number (the primary key) with the following

statement:

create table students -

(firstname varchar(12)

not null, -

lastname varchar(15)

not null, -

studentno char(6)

not null, -

primary key (studentno))

The last line in the CREATE TABLE statement defines the STUDENTNO column as

the primary key for this table. A column named as a primary key must have been

defined with the NOT NULL option.

Adding a Primary Key to an Existing Table

It is not necessary to define the primary key in the CREATE TABLE statement. It

can be added later using the ALTER TABLE statement. You can create the table

first using:

create table students -

(firstname varchar(12)

not null, -

lastname varchar(15)

not null, -

studentno char(6)

not null)

Then add a primary key:

alter table students -

add primary key (studentno)

This makes the STUDENTNO column the primary key in the STUDENTS table if

there are no duplicate values in that column. If duplicate values exist when you

attempt to add a primary key on an existing column, the ALTER TABLE statement

fails. If the column named for the primary key allows nulls, the statement also

fails.

Creating a Table That Contains a Foreign Key

You can create a table with a foreign key by adding the FOREIGN KEY clause to

the CREATE TABLE statement. This clause specifies the column that will be the

foreign key and the table containing the primary key to be referenced. The parent

table referenced must already exist and must have a primary key defined.

Create a table for a computer science class that contains a row for each student

enrolled in the class and references the STUDENTS table as follows:

create table cs110 -

(studentno char(6) not null, -

midterm integer, -

final

integer, -

foreign key r_studt (studentno) references -

students on delete cascade)

This creates a table where every row must represent a student who is listed in the

STUDENT table. If a student is deleted from the STUDENT table, that student is

also automatically deleted from this class list because of the delete cascade rule

specified in the foreign key definition.

The referential constraint defined in the above example is r_studt. This name is

used when the foreign key is deactivated, activated, or dropped.

Chapter 8. Creating and Managing Tables

Adding a Foreign Key to an Existing Table

Foreign keys can be added after a table has been created by using the ALTER

TABLE statement. The dependent table CS110 can be created by creating the table

first and without the foreign key:

create table cs110 -

(studentno char(6) not null, -

midterm integer, -

final

integer)

You then add the foreign key using the following statement:

alter table cs110 add -

foreign key r_studt (studentno) references -

students on delete cascade

When a foreign key is added in this way, all foreign key values currently in the

table must match existing values in the primary key referenced, or the attempt to

add a foreign key fails.

When creating referential constraints involving two tables that reference each other,

at least one of the foreign keys must be added after the table has been created. It is

impossible to reference a table (and its primary key) if that table has not been

created. To create this type of structure, create one table with its primary key.

Create the second table with its primary key and the foreign key referencing the

first table. Then, add a foreign key to the first table which references the primary

key in the second.

Activating and Deactivating Primary Keys, Foreign Keys, or

Unique Constraints

The constraints placed on altering tables that contain primary or foreign keys, or

unique constraints, can be suspended by deactivating the keys in the table. For

example, the primary key in the STUDENTS table is deactivated with the

following statement:

alter table students deactivate primary key

The above statement causes the restrictions on inserting, deleting, and updating to

be suspended until the key is reactivated. No other users are allowed access to a

table while it has an inactive key. In addition, keys that are related to an inactive

key through a referential constraint are also considered inactive by the database

manager. If the primary key in STUDENTS is deactivated, the foreign key in CS110

becomes inactive, and that table cannot be accessed.

To activate an inactive key, you must alter the table as follows:

alter table students activate primary key

If you make changes to the STUDENTS table while its primary key is inactive, the

result of those changes cannot violate any of the constraints on the primary key, or

of the referential constraint. If the changes produced any duplicate primary key

values, dependent foreign key values without matching primary key values, or

null primary key values, the primary key activation fails.

Foreign keys can be deactivated and then activated in the same way as primary

keys by using an ALTER TABLE statement. When activating or deactivating a

foreign key, the ALTER TABLE statement must include the name of the referential

constraint. Deactivating the foreign key in the referential constraint r_studt for the

CS110 table is accomplished by typing:

Interactive SQL Guide and Reference

alter table cs110 deactivate foreign key r_studt

You can also use the ALTER TABLE statement to deactivate and activate unique

constraints. As with a foreign key, you have to use the constraint name when you

deactivate or activate it. To deactivate the unique constraint empno for the table

TEACHERS, type:

alter table teachers deactivate unique empno

This statement causes the restrictions on inserting and updating to be suspended

until the constraint is reactivated. For more information about adding a unique

constraint to an existing table, or creating a table that contains a unique constraint,

refer to the DB2 Server for VSE & VM Database Administration manual.

Determining Effects on Stored Format Information

Performing management tasks on your tables also affects any stored queries you

have. The result of these tasks depends on the type of query stored and the type of

changes made to the table.

If the table referred to by a stored query is changed by DROP TABLE, CREATE

TABLE, or ALTER TABLE statements, the formatting information stored with that

query may no longer be valid. For example, suppose a stored query performed

grouping on columns 1 and 2 of a table. These two table columns are defined as

VARCHAR.

The table is dropped and recreated with column 1 now defined as CHAR and

column 2 again defined as VARCHAR. The formatting information saved in the

stored query for column 1 is no longer valid. The formatting for the other columns

is still valid if the data types defined for these columns is the same in the recreated

table as in the original table.

In this last example, you get the same result if the stored query did not contain a

GROUP BY clause.

Sharing Your Tables with Other Users

When you create a table, you become its owner. Only you and a person with DBA

authority can use the table. However, you may want to share access to your tables.

You can give access to your data using an SQL GRANT statement, and you can

take away access using the SQL REVOKE statement. See the DB2 Server for VSE &

VM SQL Reference manual for more information.

Granting Privileges to Multiple Users

If you want to grant the identical capabilities to a group of users, you use a single

GRANT statement. For example, assume Mona is part of a department responsible

for maintaining the quarter information. Users Jim, Dan, and Dee also need update

privileges on the QUARTER column of the EMPL table. You can extend the update

capability to all three by typing:

grant update(quarter) -

on empl -

to jim,dan,dee

You can also grant privileges to the public within a list of grantees as illustrated

below:

Chapter 8. Creating and Managing Tables

grant update(quarter) -

on empl -

to jim,public,dee

Using a View to Restrict Privileges to Certain Rows

Views can be used to restrict a privilege to specific rows of your tables. For

example, assume Jim and Dee maintain quarter information for different groups of

employees based on level of education. The following view supplies the

information Jim needs for employees of education level 16 or less:

create view to16 -

as select empno,lastname,edlevel,birthdate,quarter -

from empl -

where edlevel <= 16

Similarly, the following view supplies all the information needed by Dee:

create view past16 -

as select empno,lastname,edlevel,birthdate,quarter -

from empl -

where edlevel > 16

The next step is to grant Jim and Dee privileges on the views. Type the following

GRANT statements:

grant select,update(quarter) -

on to16 -

to jim

grant select,update(quarter) -

on past16 -

to dee

Using a View to Restrict Privileges to Certain Columns

Views can also be used to restrict the columns on which a user can type SELECT

or INSERT statements. For example, you can give Jim and Dee the capability to

select salary information from the EMPLOYEE table, but restrict them from

viewing commission information simply by specifying the SALARY column and

omitting the COMM column when you create the view. Create the view using:

create view blindempl -

as select salary -

from employee

Now give Jim and Dee table privileges:

grant select -

on blindempl -

to jim,dee

Creating Tables That You Want to Share

In an earlier section of this chapter, a private dbspace as a section of the database

reserved for your tables was described. A private dbspace is suitable for your

personal tables and is not really appropriate for tables that are to be shared.

Instead, a public dbspace should be used. When a table is inserted into a public

dbspace, multiple users can update the table simultaneously. No two users,

however, can use the same row at the same time.

Interactive SQL Guide and Reference

To insert a table into a public dbspace, provide the database manager with the

name of the dbspace using the IN clause of the CREATE TABLE statement. For

example, the EMPL table can be created in a public dbspace named SAMPLEDB by

typing:

create table empl -

(empno char(6), -

lastname varchar(15), -

edlevel smallint, -

birthdate date, -

quarter integer) -

in sampledb

When the dbspace name is not supplied on the CREATE TABLE statement, the

table is created in one of your private dbspaces.

Accessing Tables Belonging to Other Users

When you refer to a table (or view) in an SQL statement, the database manager

assumes you are referring to a table that you own. If you want to access a table

that belongs to another user, you must identify the user and the table name. This is

done by inserting the owner’s authorization ID before the table name and

separating the two with a period.

For example, you can access the system copy of the EMPLOYEE table (created

during the installation of the database manager) by using the following query:

select * -

from sqldba.employee

This query retrieves the copy of the EMPLOYEE table that is owned by the sample

user, SQLDBA. Your personal copy of the EMPLOYEE table is ignored. Similarly,

other users have to use this method to access your EMPL table.

If an authorization ID does not begin with a letter, number, $, #, or @, you must

enclose it in double quotation marks. For example:

select * -

from "%A23C".payroll

For detailed information about identifier naming conventions, see the DB2 Server

for VSE & VM SQL Reference manual.

Using Synonyms

To avoid specifying an authorization ID for another user’s table or view that you

access frequently, you can create synonyms for their tables and views. For example,

you can assign a synonym to be used in place of SQLDBA.EMPLOYEE by typing:

create synonym dbaemp -

for sqldba.employee

Now you can refer to SQLDBA.EMPLOYEE by the synonym DBAEMP. For

example, if you type:

select * -

from dbaemp

The database manager displays all information from the EMPLOYEE table.

You can also use the CREATE SYNONYM statement to assign a synonym to one of

your own tables. For example, if you are user JOHN, you can assign the synonym

ih to your EMPL table by typing:

Chapter 8. Creating and Managing Tables

create synonym ih -

for john.empl

When you finish using the table or view for which you have defined the synonym,

you should drop the synonym. To drop the synonym ih, for example, type:

drop synonym ih

The table or view on which the synonym was defined is not affected by this

command.

Note: Because the performance of a DROP TABLE or DROP VIEW statement does

not drop associated synonyms, you must drop the synonym(s) yourself.

Improving Query Performance

There are several ways you can improve data-access performance. This section

describes three methods: indexing a table, updating statistics, and locking data.

Indexing a Table

The database manager uses an index to locate particular rows of a table.

Although you create these indexes, you do not use them directly. You simply enter

your query and the database manager searches for, and attempts to use, an

appropriate index to locate the information. The database manager finds the

information whether an index exists or not, but it may find the information faster

using an index.

A good table index is one that anticipates the kinds of queries to be used for the

table.

For example, if you typically look for departments in the DEPARTMENT table by

department name, create an index for the DEPTNAME column by typing the

following SQL statement:

create index dptnme -

on department -

(deptname)

You can delete an index by using the DROP INDEX statement. For example, drop

the DPTNME index by typing:

drop index dptnme

You can create a unique index when you create a table by using the CREATE

TABLE statement, or you can add a unique index to an already existing table by

using the ALTER TABLE statement. For more information about the UNIQUE

attribute of these statements, see the DB2 Server for VSE & VM SQL Reference

manual.

A unique index is also automatically created for each primary key and dropped

automatically when the primary key is dropped. The primary key is dropped

either by using an ALTER TABLE statement or by dropping the table or dbspace.

You cannot create a unique index on a VARCHAR or VARGRAPHIC column that

has values that differ only by trailing blanks. Trailing blanks are ignored for values

with these data types. Therefore, 'Adm ' is the same as 'Adm'.

Interactive SQL Guide and Reference

If a VARCHAR or VARGRAPHIC column is not defined as unique, the trailing

blanks on values do not affect the index. The order of 'Adm ' and 'Adm' is

unpredictable because they only differ in trailing blanks.

Maintaining Updated Statistics

Another performance consideration concerns data statistics that are kept in the

database manager catalogs. These statistics provide information about tables such

as the number of rows in a particular table, or the number of different values

contained in a particular column. This information is used by the database

manager to determine the best method to satisfy query requests.

It may be important to keep these statistics up to date. For example, some tables

are rarely updated, and their statistics change very little over the life of the table.

Other tables, however, are updated frequently, and their statistics should be

updated periodically.

You can update the statistics on a table by using an SQL UPDATE STATISTICS

statement. For example, update the statistics on the ACTIVITY table by typing:

update statistics -

for table activity

Updating statistics involves a scan of both the rows and indexes of a table. This

can be time-consuming for a large table; performing the update during off-peak

hours is a good idea. As a general rule, try to develop a rule of thumb for

determining when to update table statistics; for example, you might decide to

update a table when it has changed by 20% or more. The full description of the

UPDATE STATISTICS statement is shown in the DB2 Server for VSE & VM SQL

Reference manual.

Locking Data

When you update or delete data in a table, the table is considered unstable because

its data is changing. The database manager protects you and other users from

obtaining unreliable query results by limiting access to unstable tables. It also

prevents deadlocks when several users are trying to update the same data.

The database manager isolates the data by locking it. You can control the amount of

data locked from other users, as well as the length of time that the lock is applied.

Controlling the amount of locked data affects system performance. For example, a

small amount of locked data requires less processing time than a large amount.

If you try to access an SQL object while another user is locking it, your processing

is suspended until the other user has finished with the object. If you do not want

to wait indefinitely, you can type the following to stop your transaction:

cancel

The database manager then rolls back any uncommitted work, and issues messages

ARI7043I and ARI7040I. You can find more information about the CANCEL

command under “CANCEL” on page 108.

Specifying the Isolation Level

You control locks by specifying the isolation level. The isolation-level setting affects

only those tables stored in public dbspaces.

Chapter 8. Creating and Managing Tables

The isolation level you set is related to the task you are performing. The selection,

insertion, updating, and deletion of table data are all affected by the isolation level.

Specifically, the isolation level determines how soon data you have read can be

changed by other users.

The isolation level has three settings: repeatable read (RR), cursor stability (CS), and

uncommitted read (UR).

You can use the SET ISOLATION command only when the target AS is a local

application server. Otherwise, the isolation level of CS is used and the SET

ISOLATION command will have no effect.

Using the Repeatable Read Setting: Use the RR setting when you are modifying

data. The RR setting ensures that data is completely isolated for your use. No other

user can update the data until your work has completed.

Using the Cursor Stability Setting: Use the CS setting when you are simply

querying (selecting) committed data. The term cursor in this case refers to the

database manager cursor that points to the data in the table that you are using.

The data involved in a CS setting is unlocked as soon as possible by the database

manager for other users.

Using the Uncommitted Read Setting: Use the UR setting when you are

querying (selecting) either committed or uncommitted data. With this setting, data

can be read without waiting for other logical units of work that are updating the

data and reading data will not prevent other application processes from updating

it. However, you should remember that data integrity may be compromised with

this setting and that UR should only be used when it is not necessary that the data

you are reading be committed.

Using the SET Command

You use the SET command to control the isolation level. The SET command format

is given on page 149, and an explanation of isolation level settings is found on

page 154. See “Chapter 7. Creating and Using Routines” on page 73 for information

on how to set the isolation level from a routine.

Note: Do not forget to change the isolation level back to the default isolation level

when you have completed the operation for which you changed it.

Handling Lock Contention

Use an RR setting unless lock contention is a significant problem on the application

server you are accessing. If lock contention is significant, use a CS setting

whenever you can. The UR setting minimizes lock contention but it should only be

used when it is not necessary that the data you are reading be committed.

Determining the Isolation-Level Setting

Table 6 and Table 7 on page 98 describe isolation level settings for various tasks.

The first table discusses cursor stability, and the second, repeatable read. There is

no table for the uncommitted read setting because this setting is not recommended

for regular use.

Table 6. Guidelines for Using Cursor Stability

Activity

Description

Browsing a query

When you are simply displaying data, the isolation level cursor stability

result

is sufficient. At this time, do not plan to update the table or to print a

formal report.

Interactive SQL Guide and Reference

Table 6. Guidelines for Using Cursor Stability (continued)

Activity

Description

Preparing sample

While you are preparing a draft of a report, which you are using to

reports

check format and general content, use isolation level cursor stability.

Selecting or

Read-only data resides in tables that are subject to controlled

printing read

maintenance (insert, update, or delete). That is, any maintenance is

only data

done on a known periodic basis (for example, tables that are only

updated overnight).

Browsing HELP

ISQL HELP text information is read-only data, and isolation level

information

cursor stability is sufficient.

Using SQL data

Read and update access to a catalog table during the performance of

definition

data definition statements (CREATE, ACQUIRE, GRANT) is always

operations

done with an isolation level setting of repeatable read. You do not have

to set the isolation level to protect your definitions.

Using EXPLAIN

Using the EXPLAIN statement to access a catalog table is always

performed with isolation level repeatable read, regardless of your

isolation level setting.

Accessing data in

Accessing private dbspaces is effectively isolation level repeatable read,

private dbspaces

because locking is only performed at the dbspace level. You do not

need to adjust your setting for isolation level.

Accessing data in

For public dbspaces with dbspace locking, access is always effectively

public dbspaces

isolation level repeatable read, because locking is only performed at the

with dbspace

dbspace level. You need not change your isolation level setting.

level locking

Working with

Stored queries are always stored and recalled with an isolation level

stored queries

repeatable read.

You control the isolation level used for starting a stored query.

Therefore, you must set the isolation level to the desired value before

starting the stored query.

Invoking routines

Retrieving from a routine is performed with an isolation level

repeatable read.

You control the isolation level used for running a routine. You can

change the isolation level setting before running the routine or within

the routine (as many times as needed).

Using ISQL

For ISQL commands that access the application server, the isolation

commands

level used is repeatable read.

For the ISQL commands RUN, START, and HELP, you can control the

setting of the isolation level.

Using the

Using INSERT statements with values and INPUT commands are not

INSERT

affected by the isolation level setting because no data is read from the

statement and the

application server.

INPUT command

Using INSERT statements with subselect are affected by the isolation

level setting because of the SELECT clause. You must follow the

guidelines for selecting data given above when you use INSERT

through subselect statements.

Chapter 8. Creating and Managing Tables

Table 7. Guidelines for Using Repeatable Read

Activity

Description

Using DELETE,

If you type a DELETE, INSERT, or UPDATE statement based on a

INSERT, or

SELECT statement result, do not set the isolation level to repeatable

UPDATE from a

read before you type the SELECT statement. It is then impossible for

display

the displayed data to change before you have typed the DELETE,

INSERT, or UPDATE statement.

Note: In addition, set AUTOCOMMIT OFF so the SELECT and data

change statements are contained in the same LUW.

You need not set AUTOCOMMIT OFF and isolation level repeatable

read if any of the following is true:

v Data selected is read-only.

v You are the only person authorized to modify the selected data.

v You have other ways of ensuring the data selected does not change.

v A command that changes the contents of the table is valid even if the

selected data does change.

Using DELETE or

Use an isolation level repeatable read when you delete or update data

UPDATE

unless:

statements

v You are the only person authorized to modify the data.

v You have other ways of ensuring the data selected does not change.

Preparing formal

To prevent data from changing, set the isolation level to repeatable read

reports

when you prepare a formal report unless:

v Data selected is read-only.

v You are the only person authorized to modify the selected data.

v You have other ways of ensuring the data selected does not change.

Interactive SQL Guide and Reference

Chapter 9. Using VM Functions

In this chapter, you progress beyond ISQL and learn some VM commands and

functions that can enhance your use of the database manager. Although this

chapter does not show you how to use VM, it does identify many of the VM

features that help you get the most out of ISQL and this RDBMS.

The commands described in this chapter are neither SQL statements nor ISQL

commands; they are CMS and CP commands that can be used to control the

characteristics of your virtual machine, direct printed output, and provide

additional VM facilities. As CMS and CP commands, they can only be used in

CMS or CMS subset mode.

CMS-Subset Processing

During your ISQL session, you can enter CMS or CP commands without

terminating your ISQL session. When finished you can return to ISQL.

To enter CMS-subset mode, type:

cms

You can now type CMS or CP commands, EXEC procedures, or user programs.

Note: Do not enter any EXEC procedure or program that accesses the application

server while you are in CMS subset mode.

Returning to ISQL from CMS Subset Mode

When you finish entering your CMS and CP commands and are ready to return to

ISQL, type:

return

Entering CP Commands

You can enter CP commands in three ways:

v A CP command can be typed at any time during ISQL processing by prefixing

the command with #CP. This procedure immediately interrupts the current ISQL

processing, enters CMS subset mode and performs the designated CP command,

and then returns to ISQL when the command is complete.

v You can press PA1 at any time. CP READ appears in the status area and you can

type any CP commands.

If you are running with CP SET RUN ON, control automatically returns to ISQL

after each CP command is executed. ISQL resumes processing from the point of

interruption. If you are running with CP SET RUN OFF, you must type a B or

begin on the input line and press ENTER to resume processing in ISQL.

The SET FULLSCREEN ON command cancels the interrupt action of PA1.

Suspending the full-screen option or turning it off does not reset PA1 to the

original interrupt setting. If PA1 does not interrupt ISQL, you can reestablish it

as an interrupt key with the following command:

#cp terminal brkkey pa1

v While in CMS subset mode, you can type CP commands (with or without the

CP prefix) in the same manner as that described above for CMS commands.

As a DB2 Server for VM user, you probably use CP commands only when you

want to change printer spooling or routing characteristics, or change PF key

definitions.

Obtaining Printed Reports on a Workstation Printer

Your printed output is automatically sent to the printer designated by your site.

You can change or redirect your printed output to another printer and specify

other print characteristics by using the CP commands TAG and SPOOL. The TAG

command identifies the receiving destination; the SPOOL command directs the

printed output to the network machine.

First, start the statement stored as DEPT in “Chapter 6. Storing SQL Statements” on

page 67 by typing:

start dept

Suppose that you want to print this information on a remote printer. You have to

know the node ID for that printer and the user ID of the network machine. You

can use the CMS IDENTIFY command for information about network identifiers

(NETIDs). For this example, assume that you want to send your output to a

3262-13 printer with a destination ID of RMT3262. Enter CMS subset mode by

typing the following:

cms

Then type the following CMS command:

identify

The results of this command give you the necessary information to use the SPOOL

and TAG commands. The format of the output produced by typing this command

is:

user AT nodeid VIA netid

Now you would type the CP SPOOL and TAG commands to route your printed

output to the 3262 printer:

#cp spool printer to netid nohold

#cp tag dev printer RMT3262 system

To return to ISQL and print your report, type:

return

Specifying the Number of Copies of Printed Reports

You can use the CP SPOOL command to specify the number of copies for all

reports you print during the current terminal session.

For example, you can specify three copies by modifying the previous CP SPOOL

command example as follows:

#cp spool printer to netid copy 3

All following PRINT commands use the quantity specified by the most recently

typed CP SPOOL command or ISQL PRINT command with the COPIES keyword.

100

Interactive SQL Guide and Reference

Note: This section assumes your site is running with the Remote Spooling

Communications Subsystem (RSCS) Networking Program Product (Version 3

Release 2 or later).

^| Using EXEC Files

An EXEC is a file with a file type of EXEC. It contains a series of commands and

statements that are executed when the file name of the EXEC file is typed.

You can use EXECs to stack SQL statements and ISQL commands before you begin

your ISQL session. As soon as you start ISQL, this information is read from the

stack and executed. By using EXEC processing in this way, you can predefine all

default settings to be used during your display session, establish PF key values,

and designate print specifications. This method can also be used to automatically

start procedures tailored to the needs of specific users.

The EXEC examples in this chapter do not include the CONNECT statement as

part of the stacked set; it is assumed that you are working with your own sample

tables. If you must gain access to another user ID to use ISQL, use a CONNECT

statement as the first stacked statement. See the DB2 Server for VSE & VM SQL

Reference manual for additional information on the CONNECT statement.

For information on creating CMS EXEC files, see the VM/ESA: CMS User’s Guide

manual. For information on the REXX language, see the VM/ESA REXX/VM User’s

Guide and the VM/ESA REXX/VM Reference manuals.

Stacking Commands in an EXEC File

In the CMS environment, you can run EXECs that stack SQL statements and ISQL

commands to be processed automatically as soon as ISQL begins. If the EXEC also

starts ISQL, the user of the EXEC need not know anything about ISQL.

You can write specialized EXECs for your own use or for other users. Figure 47

shows an EXEC that is written in the REXX language:

/* this exec develops a report for project mean numbers */

Queue 'SELECT * FROM PROJ_ACT ORDER BY PROJNO'

Queue 'FORMAT GROUP PROJNO'

Queue 'FORMAT SUBTOTAL ACSTAFF'

Queue "FORMAT TTITLE 'PROJECT MEAN EMPLOYEES'"

Queue 'PRINT'

Queue 'END'

Queue 'EXIT'

'EXEC ISQL'

Exit

/* end of exec */

Figure 47. Example of Stacked ISQL Commands in a REXX EXEC

Prompting by Using an EXEC File

To create a prompt environment for a casual SQL user, you can write an EXEC like

the one shown in Figure 48 on page 102. The user would run the EXEC from the

CMS environment without starting ISQL.

Chapter 9. Using VM Functions

101

/* This exec prompts for a table to be viewed or printed */

Trace Value 'OFF'

Say 'WHICH TABLE WOULD YOU LIKE TO SEE?'

Parse Upper Pull tablename

Say 'WOULD YOU LIKE TO HAVE THIS TABLE PRINTED? (Y OR N)'

Parse Upper Pull printoption

Say 'YOU MUST ENTER END TO LEAVE THE DISPLAY OF THE TABLE'

If printoption = 'Y' then Do

Queue 'SELECT * FROM' tablename

Queue 'DISPLAY'

Queue 'PRINT'

Queue 'END'

End

Else If printoption = 'N' then Do

Queue 'SELECT * FROM' tablename

Queue 'DISPLAY'

Queue 'END'

End

If printoption = 'Y' | printoption = 'N' then Do

Queue 'EXIT'

'EXEC ISQL'

End

Exit /* End of exec */

Figure 48. Example of an EXEC That Prompts the User

When you run this EXEC, the following user/system dialog occurs. If the EXEC

has a file name of MYTABLES, you type:

mytables

The system prompts you with the message WHICH TABLE WOULD YOU LIKE TO SEE?.

You type:

department

The system prompts you with the message WOULD YOU LIKE TO HAVE THIS TABLE

PRINTED? (Y OR N). You type:

The system prompts you with the message YOU MUST ENTER END TO LEAVE THE

DISPLAY OF THE TABLE.

The ISQL startup messages immediately follow. Then the partial display in

Figure 49 should appear.

DEPTNO DEPTNAME

MGRNO ADMRDEPT

------

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

------

--------

A00

SPIFFY COMPUTER SERV< 000010

A00

B01

PLANNING

000020

A00

C01

INFORMATION CENTER

000030

A00

D01

DEVELOPMENT CENTER

A00

D11

MANUFACTURING SYSTEM< 000060

D01

D21

ADMINISTRATION SYSTE< 000070

D01

E01

SUPPORT SERVICES

000050

A00

E11

OPERATIONS

000090

E01

E21

SOFTWARE SUPPORT

000100

E01

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

Figure 49. Query Result from an EXEC

You see the ISQL signoff messages when you end the display.

102

Interactive SQL Guide and Reference

Starting ISQL from a Terminal

Because ISQL is a full-screen interactive program, it must be started from a display

terminal. When a user is automatically logged on using the CP AUTOLOG

command, or when a user tries to start ISQL while disconnected, ISQL is not

started. The user must be logged on to a display terminal to start ISQL.

Disconnecting after Starting ISQL

Although the restriction of a logged-on display terminal applies to the starting of

ISQL, it does not apply if a user disconnects after starting ISQL. For example, if

you have an EXEC that starts ISQL and then executes a number of ISQL SELECT

statements, you can disconnect your display terminal after ISQL is started and the

remainder of your SELECT statements are executed.

Avoid using commands and statements that result in the issuance of ISQL

decision-type messages. For example, stacking a SET AUTOCOMMIT OFF

command causes the following message to be issued:

ARI7602D You are in a logical unit of work. Type COMMIT to

have a COMMIT issued for you or ROLLBACK to

have a ROLLBACK issued for you.

Your response to this message cannot be accepted from the command stack

because of the interactive design of ISQL. You must type the response yourself.

Chapter 9. Using VM Functions

103

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