|
|
Target Data Type
LONG
LONG
VAR-
SMALL-
TIME-
VAR-
VAR-
Source Data Type
VARCHAR
GRAPHIC
INT
TIME
STAMP
CHAR8
GRAPHIC9
CHAR
YES
NO
NO
YES6
YES6
YES3
NO
DATE
NO
NO
NO
NO
NO
YES
NO
DECIMAL
NO
NO
YES1,2
NO
NO
NO
NO
FLOAT-DOUBLE
NO
NO
YES1,2
NO
NO
NO
NO
FLOAT-SINGLE
NO
NO
YES1,2
NO
NO
NO
NO
GRAPHIC
NO
YES
NO
NO
NO
NO
YES3
INTEGER
NO
NO
YES1
NO
NO
NO
NO
LONG VARCHAR
YES
NO
NO
NO
NO
YES3
NO
LONG VARGRAPHIC
NO
YES
NO
NO
NO
NO
YES3
SMALLINT
NO
NO
YES
NO
NO
NO
NO
TIME
NO
NO
NO
YES
NO
YES7
NO
TIMESTAMP
NO
NO
NO
NO
YES
YES7
NO
VARCHAR8
YES
NO
NO
YES6
YES6
YES3
NO
VARGRAPHIC9
NO
YES
NO
NO
NO
NO
YES3
Figure 44. Data Conversion Chart (Part 2 of 2)
Notes to Figure 44:
1.
An overflow error may result.
2.
The fractional part of the value is dropped.
3.
On output, if the length of the target is smaller than the length of the source,
truncation occurs. On input, an error occurs.
4.
The database manager automatically aligns the decimal point. Overflow of the
integer part may result. The fractional part may be truncated.
5.
The database manager attempts to create the best possible result in converting
from System/370 floating point to scaled fixed point decimal.
6.
The character string must contain a valid representation of a date, time, or
timestamp value. However, you cannot transfer data from a CHAR or
VARCHAR column into a host variable defined as a date, time, or timestamp
type.
7.
On output, when the source is a datetime data type and the corresponding
target is a character data type, certain truncation occurs for time and
timestamp. On input, an error occurs.
8.
This applies to VARCHAR fields less than or equal to 254. VARCHAR fields
greater than 254 are treated like LONG VARCHAR in data conversion.
9.
This applies to VARGRAPHIC fields less than or equal to 127. VARGRAPHIC
fields greater than 127 are treated like LONG VARGRAPHIC in data
conversion.
10.
The single-precision data is padded with eight hex zeros.
11.
The double-precision data is converted and rounded up on the seventh hex
digit.
12.
Conversion is first done in double precision and then rounded to single
precision.
178
Database Administration
13. Some accuracy may be lost when converting DECIMAL data type numbers to
single- or double-precision floating point numbers.
Arithmetic Operations
The following sections define the rules for arithmetic operations with the data
types that are supported. Note the conditions under which overflow errors can
occur.
Decimal Arithmetic Operations
A decimal number has a fixed number of places in total, and a fixed number of
places in its fractional part (to the right of the decimal point). The total number of
places is often called the precision, and the number of places in the fractional part
scale. A decimal column is defined in a CREATE TABLE or ALTER TABLE
statement as: DECIMAL (precision,scale).
The precision and scale of the decimal number resulting from an arithmetic
operation on two numbers (operands) are determined by the following rules:
v
If one operand is a binary integer and the other is a decimal number, the
operation is performed in decimal. A temporary copy of the binary integer that
has been converted to decimal is used. Binary integers defined as SMALLINT
will be converted to DECIMAL(5,0), while those defined as INTEGER will be
converted to DECIMAL(11,0). Integer constants will always be converted to
DECIMAL (11,0). The result is a decimal number as specified below.
v
If both operands are decimal numbers, the result is a decimal number.
v
The precision and scale of the result depend on the arithmetic operation, and on
the precision and scale of the operands.
v
Precision and scale can be influenced by decimal constants with leading or
trailing zeros. See the DB2 Server for VSE & VM Application Programming manual
for more information.
v
If the operation is addition or subtraction, and the operands do not have the
same scale, the operation is performed with a temporary copy of one of the
operands. The copy is extended with trailing zeros so that its fractional part has
the same number of places as the fractional part of the other operand.
v
The result of an addition, subtraction, or multiplication operation is derived
from a temporary result that has a maximum precision of 31. If the precision of
the temporary result is not greater than 31, the final result is the same as the
temporary result.
v
When the scale of the temporary result is greater than that of the result (see
Table 27), then the fractional part of the temporary result will be truncated so
that the scales are the same.
v
For an integer literal, the precision will be the number of digits, and the scale
will be 0. For example, 100 will be set to precision 3 and scale 0.
v
The precision and scale of a result are determined as shown in Table 27.
Chapter 8. Application Design Considerations
179
Table 27. Precision and Scale of Decimal Results
Assume the following notation:
PA = Precision of the first operand.
SA = Scale of the first operand.
PB = Precision of the second operand.
SB = Scale of the second operand.
Operation
Characteristics of the Result
Addition and
Precision: MIN(31,MAX(PA-SA,PB-SB)+MAX(SA,SB))
Subtraction
Scale: MAX(SA,SB)
Multiplication
Precision: MIN(31,PA+PB)
Scale: MIN(31,SA+SB)
Division
Precision: 31
Scale: 31-PA+SA-SB (Scale must not be negative)
Binary Arithmetic Operations
If both operands are binary integers, the operation is performed in fixed binary.
The result is in the INTEGER data type.
The result of a division operation is truncated. The result of a fixed binary
operation must be within the range of the INTEGER data type. See “Specifying
Columns” on page 30 for the ranges of data types.
Floating Point Arithmetic Operations
If either operand is a floating point number, both operands are converted to
double-precision floating point numbers. The result depends on the data type of
the target column or host variable. In the case of decimals, some accuracy may be
lost.
If the target data type or host variable is single-precision floating point, the result
is converted to single-precision floating point; otherwise, it is converted to
double-precision floating point.
The result of a floating point operation must be within the range of the FLOAT
data type. See “Specifying Columns” on page 30 for the ranges of data types.
Date/Time Arithmetic
Durations: Date/time arithmetic involves intervals of time that are represented by
numbers called durations. A duration is an interpretation of a number, not a new
data type. The number may be a constant, a column name, a host variable, a
function, or an expression. Numbers are interpreted as durations, only in certain
contexts as defined below.
The duration types are:
1. Labeled Durations
A labeled duration is any number of years, months, days, hours, minutes,
seconds, or microseconds. It is used in an expression that involves a date/time
value, and consists of a numeric expression followed by one of YEAR(S),
MONTH(S), DAY(S), HOUR(S), MINUTE(S), SECOND(S), or
MICROSECOND(S). For example, in the expression START_DATE + 120 DAYS,
180
Database Administration
the labeled duration is 120 DAYS. Fractional durations will be truncated to
whole numbers (for example, 2.9 DAYS = 2 DAYS).
2. Date Durations
A date duration represents a number of years, months, and days, expressed as
a DEC(8,0) number. It has the format yyyymmdd, where yyyy is the number of
years, mm the number of months, and dd the number of days. An example of a
date duration is the result of D1-D2, where D1 and D2 are dates.
3. Time Durations
A time duration represents a number of hours, minutes, and seconds, expressed
as a DEC(6,0) number. It has the format hhmmss, where hh is the number of
hours, mm the number of minutes, and ss the number of seconds. An example
of a time duration is the result of T1-T2, where T1 and T2 are times.
4. Timestamp Durations
A timestamp duration represents a number of years, months, days, hours,
minutes, seconds, and microseconds, expressed as a DEC(20,6) number. It has
the format yyyy-xx-dd-hh.mm.ss.zzzzzz, where yyyy, xx, dd, hh, mm, ss, and
zzzzzz represent, respectively, the number of years, months, days, hours,
minutes, seconds, and microseconds. An example of a timestamp duration is
the result of TS1-TS2, where TS1 and TS2 are timestamps.
Rules for Date/Time Arithmetic: The only arithmetic operators that can be
applied to date/time values are addition and subtraction. If a date/time value is
the operand of addition, the other operand must be a duration.
A labeled duration can only be used as the operand of an arithmetic operator such
that the other operand is a date/time value. For example, if D is a date and N and
M are numbers, D + N DAYS + M MONTHS is a valid expression, but D + (N
DAYS + M MONTHS) is not.
No automatic data conversion is provided among date/time data types. If an
arithmetic operation is to be performed among different date/time values, the
scalar functions should be used to convert them into the same data type. For
example, if A is a TIMESTAMP column and B is a DATE column, the difference
between the two in date duration can be obtained by DATE(A) - B. If you specify
just A - B, an error will occur indicating incompatible types.
The specific rules for the use of the addition operator on date/time values are as
follows:
1. If one operand is a date, the other must be a date duration or a labeled
duration of years, months, or days.
2. If one operand is a time, the other must be a time duration or a labeled
duration of hours, minutes, or seconds.
3. If one operand is a timestamp, the other may be any kind of duration.
4. Neither operand can be a parameter marker “?”.
The rules for the use of the subtraction operator on date/time values are not the
same as for addition: first, because a date/time value cannot be subtracted from a
duration, and second, because the operation of subtracting two date/time values is
not the same as that of subtracting a duration from a date/time value. The rules
are as follows:
1. If the first operand is a date, the second one must be either a DATE, date
duration, string representation of a date, or labeled duration of years, months,
or days.
Chapter 8. Application Design Considerations
181
2. If the second operand is a date, the first one must be a date or string
representation of a date.
3. If the first operand is a time, the second one must be either a time, time
duration, string representation of a time, or labeled duration of hours, minutes,
or seconds.
4. If the second operand is a time, the first one must be a time or string
representation of a time.
5. If the first operand is a timestamp, the second one must be either a timestamp,
a string representation of a timestamp, or a duration.
6. If the second operand is a timestamp, the first one must be a timestamp or a
string representation of a timestamp.
7. Neither operand can be a parameter marker “?”.
The semantic rules for date, time, and timestamp arithmetic are discussed below.
Since there is no established standard for date/time arithmetic, some of the
operations are defined procedurally. These procedural definitions use some of the
scalar functions.
Date Arithmetic: Dates can be incremented, decremented, and subtracted. The
operation of incrementing or decrementing a date by some number of days is well
defined and can be verified by a calendar. The other operations are subject to
peculiarities because not all months have the same number of days.
Subtracting Dates: When two dates are subtracted, the result is a date duration
that gives the number of years, months, and days between those dates. The data
type of the result is DECIMAL(8,0).
In the following procedural description of the operation, the term “subtrahend”
refers to the number to be subtracted, and “minuend” is the number that the
subtrahend is subtracted from.
If DAY(subtrahend) is not greater than DAY(minuend), the day part of the result is
equal to DAY(minuend) - DAY(subtrahend).
If DAY(subtrahend) is greater than DAY(minuend), the day part of the result is
equal to N + DAY(minuend) - DAY(subtrahend), where N is the last day of
MONTH(subtrahend). (For example, if MONTH(subtrahend) is 1, N is 31.)
MONTH(subtrahend) is incremented by one.
If MONTH(subtrahend) is not greater than MONTH(minuend), the month part of
the result is equal to MONTH(minuend) - MONTH(subtrahend).
If MONTH(subtrahend) is greater than MONTH(minuend), the month part of the
result is equal to 12 + MONTH(minuend) - MONTH(subtrahend).
YEAR(subtrahend) is incremented by one.
The year part of the result is equal to YEAR(minuend) - YEAR(subtrahend).
For example, the result of DATE(’3/15/2000’) - ’12/31/1999’ is 00000215 (a
duration of 0 years, 2 months, and 15 days).
Incrementing and Decrementing Dates: The result of adding a duration or
subtracting it from a date is a date. The result must be within the range of dates.
182
Database Administration
When a labeled duration of years is added to or subtracted from a date, the result
is a date (that is, the specified number of years before or after the date in the
operation). Only years are counted. The month of the result is always the same as
the month of the date in the operation. The day of the result is also the same as
the day of the date in the operation, unless the result would be February 29 of a
non-leap year, in which case the day part of the result is 28 and SQLWARN7 is set
to W.
When a labeled duration of months is added to or subtracted from a date, the
result is a date (that is, the specified number of months before or after the date in
the operation). Only months (calendar pages) and years (if necessary) are counted.
The day of the result is the same as the day of the date in the operation, unless the
result would be an incorrect date, in which case the day part of the result is the
last day of the month and SQLWARN7 is set to W.
When a labeled duration of days is added to or subtracted from a date, the result
is a date (that is, the specified number of days before or after the date in the
operation).
When a positive date duration is added to a date or a negative duration subtracted
from it, the result is a date (that is, y years, m months, and d days after the date in
the operation, where y, m, and d are the year, month, and day parts of the date
duration). When a positive date duration is subtracted from a date or a negative
duration added to it, the result is a date (that is, y years, m months, and d days
before the date in the operation). The arithmetic is performed using the rules
defined above, including the setting of SQLWARN7 whenever an end-of-month
adjustment is performed. The date duration must be DEC(8,0).
Let D1 be the DATE 1984-02-29, a leap year.
SQLWARN7
D1 + 1 DAY
= 1984-03-01
’ ’
D1 + 2 MONTHS = 1984-04-29
’ ’
D1 + 1 YEAR
= 1985-02-28
’W’
D1 + 4 YEARS
= 1988-02-29
’ ’
Let N be DEC(8,0) and set to 00010203.
D1 + N
= 1984-02-29 + 1 YEAR + 2 MONTHS + 3 DAYS
= 1985-02-28 + 2 MONTHS + 3 DAYS
’W’
= 1985-04-28 + 3 DAYS
= 1985-05-01
Let D2 be the DATE 1985-03-31.
SQLWARN7
D2 + 1 MONTH
= 1985-04-30
’W’
D2 + 2 MONTHS = 1985-05-31
’ ’
Figure 45. Setting SQLWARN7 During Date Arithmetic. When incrementing or decrementing
dates, SQLWARN7 is set when the resulting date is an incorrect date because of leap year
or month difference, and a valid date is derived.
Peculiarities of Date Arithmetic: What does it mean to add a month to a given
date? The rules defined above are based on the assumption that the result should
be the same day of the next month. Thus, one month after January 1 is February 1,
and one month after February 1 is March 1. But what is one month after January
Chapter 8. Application Design Considerations
183
31? This difficulty, which is the reason why certain contracts are always dated the
first of the month, is resolved by the further assumption that the result should be
the last day of February.
Thus, adding a month to a given date gives the same day of the next month unless
the next month does not have such a day, in which case the result is the last day of
that month. Similarly, one month from the last day of a month is not necessarily
the last day of the next month. For example, one month from the last day of
February is not the last day of March. In sum, “a date + a labeled duration of
months - a labeled duration of months” is not necessarily equal to the original
date.
The definition of the month does not permit a consistent system of date arithmetic.
If this is a problem, you can avoid it by using days rather than months. For
example, to increment the date “DATE3” by the difference between the dates
“DATE1” and “DATE2”, the expression “DATE (DAYS(DATE1) - DAYS(DATE2) +
DAYS(DATE3))” will give an accurate result, whereas “DATE1 - DATE2 + DATE3”
may not.
Time Arithmetic: Times can be incremented, decremented, and subtracted. The
only peculiarity is the modules of 24 hours. For example, adding any multiple of
24 hours to a time gives the same time. The exception is 00:00:00, where adding
24:00:00 becomes 24:00:00.
Subtracting Times: When two times are subtracted, the result is a time duration
that gives the number of hours, minutes, and seconds between the two times. The
data type of the result is DECIMAL(6,0).
In the following procedural description of the operation, the term “subtrahend”
refers to the number to be subtracted, and “minuend” is the number that the
subtrahend is subtracted from.
If SECOND(subtrahend) is not greater than SECOND(minuend), the seconds part
of the result is equal to SECOND(minuend) - SECOND(subtrahend).
If SECOND(subtrahend) is greater than SECOND(minuend), the seconds part of
the result is equal to 60 + SECOND(minuend) - SECOND(subtrahend).
MINUTE(subtrahend) is incremented by one.
If MINUTE(subtrahend) is not greater than MINUTE(minuend), the minute part of
the result is equal to MINUTE(minuend) - MINUTE(subtrahend).
If MINUTE(subtrahend) is greater than MINUTE(minuend), the minute part of the
result is equal to 60 + MINUTE(minuend) - MINUTE(subtrahend).
HOUR(subtrahend) is incremented by one.
The hour part of the result is equal to HOUR(minuend) - HOUR(subtrahend).
Incrementing and Decrementing Times: The result of adding a duration to a
time or subtracting a duration from it is a time. In each of the following cases, any
overflow or underflow of hours is discarded. Thus, the result is always within the
range of a time.
When a labeled duration of hours is added to or subtracted from a time, the result
is a time (that is, the specified number of hours before or after the time in the
184
Database Administration
operation). Only hours are counted. Thus, the minute and second of the result are
the same as the minute and second of the time in the operation.
When a labeled duration of minutes is added to or subtracted from a time, the
result is a time (that is, the specified number of minutes before or after the time in
the operation). Only minutes and hours (if necessary) are counted. Thus, the
second of the result is the same as the second of the time in the operation.
When a labeled duration of seconds is added to or subtracted from a time, the
result is a time (that is, the specified number of seconds before or after the time in
the operation).
When a time duration is added to or subtracted from a time, the result is a time
(that is h hours, m minutes, and s seconds before or after the time in the operation,
where h, m, and s are the hour, minute, and second parts of the time duration). The
time duration must be a DEC(6,0) value.
Timestamp Arithmetic: Timestamps can be incremented, decremented, and
subtracted. The operations are a combination of the date arithmetic and time
arithmetic defined above, except that any overflow or underflow of hours is
reflected in the date part of the result.
Subtracting Timestamps: When two timestamps are subtracted, the result is a
timestamp duration that gives the number of years, months, days, hours, minutes,
and seconds between the two timestamps. The data type of the result is
DECIMAL(20,6).
In the following procedural description of the operation, the term “subtrahend”
refers to the number to be subtracted, and “minuend” is the number that the
subtrahend is subtracted from.
If MICROSECOND(subtrahend) is not greater than MICROSECOND(minuend), the
microseconds part of the result is equal to MICROSECOND(minuend) -
MICROSECOND(subtrahend).
If MICROSECOND(subtrahend) is greater than MICROSECOND(minuend), the
seconds part of the result is equal to 1000000 + MICROSECOND(minuend) -
MICROSECOND(subtrahend). SECOND(subtrahend) is incremented by one.
Second and minute are subtracted as specified in the rules for “Subtracting Times”
on page 184.
If HOUR(subtrahend) is not greater than HOUR(minuend), the hour part of the
result is equal to HOUR(minuend) - HOUR(subtrahend).
If HOUR(subtrahend) is greater than HOUR(minuend), the hour part of the result
is equal to 24 + HOUR(minuend) - HOUR(subtrahend). DAY(subtrahend) is
incremented by one.
Day, month, and year are subtracted as specified in the rules for “Subtracting
Dates” on page 182.
Incrementing and Decrementing Timestamps: The result of adding a duration to
or subtracting it from a timestamp is a timestamp. In each of the following cases,
Chapter 8. Application Design Considerations
185
date and time arithmetic are performed as defined above, except that an overflow
or underflow of hours is carried into the date part of the result, which must be
within the range of dates.
When a labeled duration of years is added to or subtracted from a timestamp, the
result is a timestamp (that is, the specified number of years from the timestamp).
When a labeled duration of months is added to or subtracted from a timestamp,
the result is a timestamp (that is, the specified number of months from the
timestamp).
When a labeled duration of days is added to or subtracted from a timestamp, the
result is a timestamp (that is, the specified number of days from the timestamp).
When a labeled duration of hours is added to or subtracted from a timestamp, the
result is a timestamp (that is, the specified number of hours from the timestamp).
When a labeled duration of minutes is added to or subtracted from a timestamp,
the result is a timestamp (that is, the specified number of minutes from the
timestamp).
When a labeled duration of seconds is added to or subtracted from a timestamp,
the result is a timestamp (that is, the specified number of seconds from the
timestamp).
When a labeled duration of microseconds is added to or subtracted from a
timestamp, the result is a timestamp (that is, the specified number of microseconds
from the timestamp).
When a date duration is added to or subtracted from a timestamp, the result is a
timestamp. The year, month, and day parts are the result of the arithmetic
operation performed using the rules defined for incrementing or decrementing a
date by a date duration. The hour, minute, second, and microsecond parts are the
same as those of the timestamp in the operation.
When a time duration is added to or subtracted from a timestamp, the result is a
timestamp. The time part is the result of the arithmetic operation performed using
the rules defined above for incrementing or decrementing a time by a time
duration, except that any overflow or underflow of hours is carried into the date
part of the result. The microsecond part of the result is the same as the
microsecond part of the timestamp in the operation.
When a timestamp duration is added to or subtracted from a timestamp, the result
is a timestamp (that is, y years, x months, d days, h hours, m minutes, s seconds,
and z microseconds before or after the time in the operation, where these values
are the year, month, date, hour, minute, second and microsecond parts of the
timestamp duration). Date and time arithmetic are performed as previously
defined, except that an overflow or underflow of hours is carried into the date part
of the result. Microseconds overflow into seconds. The timestamp duration must be
DEC(20,6).
Figure 46 on page 187 and Figure 47 on page 188 summarize date/time addition
and subtraction, respectively. The STRING column in both tables mean a character
string in a valid date/time format.
186
Database Administration
DATE/TIME ADDITION = OPERAND + OPERAND
LEFT OR RIGHT OPERAND
DURATIONS
SIMPLE
D
T
T
S
D
T
T
Y
M
D
H
M
S
M
S
A
I
I
T
A
I
I
E
O
A
O
I
E
I
E
T
M
M
R
T
M
M
A
N
Y
U
N
C
C
C
E
E
E
I
E
E
E
R
T
R
U
O
R
O
S
N
S
H
T
N
O N
LEFT OR
RESULT
T
G
T
E
D
D
RIGHT
DATA
A
A
S
OPERAND
TYPE
M
M
P
P
DATE
X
X
X
X
DATE
TIME
X
X
X
X
TIME
TIME
TIME
STAMP
X
X
X
X
X
X
X
X
X
X
STAMP
Figure 46. Date/Time Addition
An X denotes a valid date/time addition operation.
Chapter 8. Application Design Considerations
187
DATE/TIME SUBTRACTION = MINUEND - SUBTRAHEND
S U B T R A H E N D
DURATIONS
SIMPLE
D
T
T
S
D
T
T
Y
M
D
H
M
S
M
S
A
I
I
T
A
I
I
E
O
A
O
I
E
I
E
T
M
M
R
T
M
M
A
N
Y
U
N
C
C
C
E
E
E
I
E
E
E
R
T
R
U
O
R
O
S
N
S
H
T
N
O N
RESULT
T
G
T
E
D
D
DATA
A
A
S
TYPE
MINUEND
M
M
P
P
DATE
1
1
2
2
2
2
1=(8,0)
2=DATE
TIME
1
1
2
2
2
2
1=(6,0)
2=TIME
1=(20,6)
TIME
2=TIME
STAMP
1
1
2
2
2
2
2
2
2
2
2
2
STAMP
Figure 47. Date/Time Subtraction
Both 1 and 2
denote a valid date/time subtraction operation. 1 means a result data
type of DECIMAL(8,0), DECIMAL(6,0), or DECIMAL(20,6) that is deemed as a
date duration, time duration, or timestamp duration, respectively. 2 means a result
data type of date, time, or timestamp.
Data Access Changes
Users do not have to specify how data is to be accessed; only what data is to be
accessed. Access path selection is done by the database manager, which determines
which strategy will minimize the cost of processing a query. Cost is based on
estimates of processor and I/O requirements. Users are not only free of such
matters, they are not allowed to use any knowledge of such details. This allows the
program to continue to operate when the underlying storage structures are
changed.
Data Structure Changes
Both logical and physical structural changes can be made to data without
significant effect on users or their programs. The database manager permits
flexibility in the binding of programs’ data references to the data objects in the
database. This significantly reduces the impact of changes.
The following sections note a few important considerations to reduce the effect of
data restructuring.
188
Database Administration
Program Reference Flexibility: When a program is preprocessed, references to
nonexistent tables, views, or columns, or the use of statements that require a level
of authority that has not yet been granted, do not prevent a package from being
created; these conditions only cause warning messages. If the required authority or
object exists when the referencing statement is processed, execution will proceed
normally.
Although this design is very useful, it exacts a performance penalty. Preprocessing
of such a program should be done again before the program is used extensively.
By repeating the preprocessing step after acquiring the authority or having the
required objects created, you avoid implicit, dynamic preprocessing of those
statements that had unresolved objects or authority at the time of the original
preprocessing.
Adding New Columns to Existing Tables (ALTER TABLE): When you add new
columns to existing tables, referencing programs are normally not affected.
With SELECT statements, when selected columns are specifically named (rather
than specifying SELECT *) or when a view is used, there is no effect on the
program.
With INSERT statements, the effect of added columns on existing programs can be
eliminated by specifically naming the target fields or by using a view, when the
new columns permit NULL values. If the fields are not named, and if affected or
new columns do not permit NULLS, the program must be changed and
preprocessed again.
With the UPDATE statement, there is no effect because of changes, because
individual fields are always specifically named.
With DELETE statements, the action applies to the row as a whole, so adding
fields has no direct effect on existing programs.
When you add new fields, you may have to rewrite old programs to pick up the
new function associated with those fields. However, with the above described
restrictions, there need be no effect on old programs for existing function.
Programs can continue to work normally through such changes until it is really
necessary to update them.
Dropping Columns and Tables (DROP TABLE): To drop a column from a table,
drop the table (DROP TABLE) and recreate it (CREATE TABLE) without that
column. If the column dropped is not used by an existing program, dropping it in
this manner does not functionally affect that program. The program is
automatically re-preprocessed when it is next used.
Note: If the table has data in it, before dropping it, save the data. Either use the
DBS Utility to unload the table to a tape or DASD SAM file; or create a new
temporary table, and use an INSERT with Subselect statement to copy the
data into it. Later, you can either use the DBS Utility to reload the data into
the newly created table; or use an INSERT with Subselect statement to copy
the data from the temporary table to the newly created table, then drop the
temporary table.
When you drop a table, all keys, indexes, and privileges are lost.
Chapter 8. Application Design Considerations
189
When a program is preprocessed, all of its dependencies (such as tables needed)
are recorded in the SYSTEM.SYSUSAGE table. Then, whenever one of these objects
is dropped, the SYSTEM.SYSUSAGE table is searched to check for dependencies; if
the program depends on the object just dropped, it marks the entry invalid in the
SYSTEM.SYSACCESS table against the package for that program, and marks any
loaded copies of the package (in the cache) unusable.
The next time the package is invoked, it will automatically be re-preprocessed. If
referenced objects have been reestablished properly, the preprocessing will succeed.
The user will not be aware of the activity, except for a longer than usual time delay
when the package is first invoked after the change.
Of course, when a program requires a field that is dropped, it can no longer
function properly until it is brought current with the change. Because the program
references the dropped field, if it is submitted for execution without being
changed, the automatic re-preprocessing will fail and the submitter will be notified.
Adding Indexes to Tables: Adding an index to a table has no effect on users or
programs that use the table. However, to make it possible to take advantage of
potential performance improvements offered by the index, programs using it
should be preprocessed again. (You should apply the UPDATE STATISTICS
statement for the table after adding the index and before preprocessing the
program again.) The preprocessing step enables the database manager to
re-examine the possible access strategies, and possibly take advantage of the new
index.
Dropping an Index for a Table: Indexes for tables used in programs are recorded
in SYSTEM.SYSUSAGE in the same manner described above for tables. When an
index is dropped, the same automatic re-preprocessing occurs for dependent
programs, allowing adjustment of the access strategy to reflect the lost index. For a
dropped index, there is no need for further action by the programmers who create
the using programs, because the automatic re-preprocess activity handles required
adjustments.
Changing Data Relationships: Data relationships are handled by keeping the
data structures simple (see “Step 7: Normalize Your Tables” on page 9) and
expressing the relationships in the accessing statements. If this is done properly,
new relationships can be accomplished without changing existing programs or
users. For example, new tables may be associated with old ones by way of joins;
predicates may use fields from many tables, and new views may be added.
Changing Referential Integrity Relationships: There is considerable flexibility
allowed in adding or dropping referential constraints. If the structure of your data
changes, you can drop the primary key of a table, and create a new primary key
for it. For information on primary keys, see “Step 4: Identify One or More
Columns as a Primary Key” on page 4. You can also add new foreign keys to
accommodate changes in the structure of your data, and drop old ones when they
are no longer used. For more information, see “Step 6: Plan for Referential
Integrity” on page 6.
When referential constraints are changed or if keys are inactivated, application
programs that access the affected tables will automatically be re-preprocessed and
compiled.
Changing Unique Constraints: Unique constraints are similar to primary keys,
and are useful when uniqueness on more than one column is desired. You can
190
Database Administration
drop the unique constraint of a table and create a new one, or add additional ones.
For information on unique constraints, see “Step 4: Identify One or More Columns
as a Primary Key” on page 4.
When unique constraints are dropped or inactivated, application programs that
access the affected tables will automatically be re-preprocessed and compiled.
Data Authorization Changes
When new authorization is added, old programs are completely unaware of the
change.
When the preprocessor encounters program dependencies on specific
authorizations, these dependencies are recorded in the SYSTEM.SYSTABAUTH
table as described above for dependent objects. When a program-dependent
authorization is removed, the package associated with the program is marked
invalid, and the automatic re-preprocessing occurs as described before. If proper
authorization is re-acquired before the automatic re-preprocessing, the package is
re-preprocessed successfully; otherwise, the invoker is notified of the problem and
the re-preprocessing fails.
The Preprocessor KEEP Option for RUN Authority
The preprocessor has an option called KEEP|REVOKE, which allows for either
keeping or revoking previously granted RUN authority. It pertains to the version
of the package that is produced by the new preprocessing step.
This design simplifies the effect of changes that require repeating the preprocessing
step, by not having to repeat the associated authorization procedures. When an
automatic re-preprocessing occurs, the KEEP option is implicitly in effect.
Changing the Users of Data
Because users are not affected by data sharing, new users can be added, data can
be employed by different people in different ways, and previous uses can be
discontinued without effect on current users or their programs.
Hypothetical Change Support
The recovery facilities also offer a significant benefit for managing changes to
applications. With these facilities, changes can be applied, examined, tested and
then everything can be backed out with a ROLLBACK statement, and it will
appear as if nothing happened. With ISQL, you must run with AUTOCOMMIT off.
The DBA may ask hypothetical questions without disrupting live data. Answers for
questions such as: “What if I change the supplier of part ZT33592 to improve the
delivery time?” or “What is the effect on overall product cost?” may be very
valuable and, because they impose no permanent changes on the database, may be
made safely.
Chapter 8. Application Design Considerations
191
192
Database Administration
Chapter 9. DB2 Server for VM Database Configurations
This chapter provides an overview of some of the many possible configurations.
(For detailed information on how to establish any particular configuration, see the
DB2 Server for VM System Administration manual.) It also contains information on
VSE Guest Sharing, and on the VM/ESA operating environment.
DB2
Server for VM Concepts
The following terms are used in the descriptions of the DB2 Server for VM
configurations that follow:
Database
A collection of CMS minidisks that store both user and system information.
The latter includes data used to secure and manage the database, such as a
list of valid users.
Database Manager
A program that provides database management services. This program
executes in its own virtual machine, referred to as the database machine.
The database manager controls any updates or deletes made to the
database, and maintains its security and integrity.
To protect the integrity of the database, users do not have direct access to
it. Rather, their requests are sent to the database manager, which processes
them and returns the results to the users.
Service Machine
A virtual machine required by any processor that does not have its own
DB2 Server for VM database machine, and has users who want to access a
DB2 Server for VM database in a collection.
Notes:
1. A collection is a group of VM processors that are connected together
using channel-to-channel, binary synchronous lines, or local area
networks.
2. The term local applies to either resources or users. The service machine
provides essential DB2 Server for VM support to users by allowing
access to the production minidisk.
User Machine
A virtual machine that runs either ISQL, DBS Utility, or a user-written
application program that uses SQL.
User machines cannot make changes directly to the database. They must
send SQL statements to the database manager.
Resource Adapter
The DB2 Server for VM code used by ISQL, the DBS Utility, and
application programs to communicate with the database machine. It
enables users to communicate with the database manager. Users need not
be aware of it.
193
Operating Modes for the Database Machine
The database machine can run in two modes of operation: multiple user or single
user. The DB2 Server for VM operator (the person logged onto the database
machine) selects the mode when he or she starts up the database machine.
In multiple user mode, the most common mode of operation, one or more users or
applications concurrently access the same database. The database manager runs in
its own virtual machine, while one or more DB2 Server for VM applications run in
other virtual machines.
In single user mode, the database manager and an application program run in a
single VM machine, and no other users are allowed access. Some maintenance
tasks, such as adding auxiliary storage to the database, require this mode.
It is also possible to operate more than one DB2 Server for VM database machine
in multiple user mode: that is, multiple databases are being accessed by many
users concurrently. This is called “multiple database mode”.
Example Configurations
The following configurations all assume that the database machines are operating
in multiple user mode.
One Database Machine with One Database
In the simplest configuration, there is one database machine and one database.
(This environment is created by the installation process.)
Figure 48 shows an example. Here, all virtual machines reside on the same
processor.
User vitual machines access
MARYLOU
STEVE
CINDY
the database through the
SQLMACH database machine.
(2)
The SQLMACH database machine
accepts and services the SQL
SQLMACH
requests from the users. It
is a virtual machine that
executes the database manager
code.
(1)
The DB01 database is
comprised of CMS minidisks
that are owned by the SQLMACH
DB01
minidisks
database machine.
Figure 48. Example of One Database Machine Running One Database
194
Database Administration
Points (1) and (2) in the figure are as follows:
1. The SQLMACH database machine was set up to use the DB01 database.
The operator selects a database when the database machine is started.
2. Three user virtual machines (MARYLOU, STEVE, and CINDY) communicate
with the SQLMACH database machine to access the DB01 database. They must
enter:
SQLINIT DBNAME(DB01)
to specify DB01 as the default database. Then, when they invoke ISQL, the DBS
Utility, or an application program, this default database will be accessed. See
“SQLINIT EXEC” on page 243 for information on the SQLINIT EXEC.
One Database Machine with Two Databases
In Figure 49, all virtual machines reside on the same processor.
User vitual machines access
MARYLOU
STEVE
CINDY
the database through the
SQLMACH database machine.
(2)
The SQLMACH database machine
services the SQL requests
SQLMACH
from users. It can own
more than one database but
can only manage one database
at a time.
(1)
The database, comprised of
CMS minidisks are owned
DB01
DB02
by the SQLMACH machine.
Figure 49. One Database Machine that Owns More than One Database
Points (1) and (2) in the figure are as follows:
1. The database machine was set up to use the DB01 database.
The operator selects a database when the database machine is started. In this
example, the operator had the choice of selecting the DB01 or DB02 database.
The DB01 database was chosen.
Note that a database machine can only access one database at a time. To access
the DB02 database, the operator must restart the SQLMACH database machine,
specifying the DB02 database.
2. Three user virtual machines (MARYLOU, STEVE, and CINDY) communicate
with the database machine to access the DB01 database. They must enter:
SQLINIT DBNAME(DB01)
Chapter 9. DB2 Server for VM Database Configurations
195
to specify DB01 as the default database. Then, when they invoke ISQL, the DBS
Utility, or an application program, this default database will be accessed. (See
“SQLINIT EXEC” on page 243 for information on the SQLINIT EXEC.)
Here, users cannot access the DB02 database. If they entered “SQLINIT
DBNAME(DB02)” and then tried to access DB02 (using ISQL, the DBS Utility,
or an application program), an SQL error would occur.
If users need to access DB02, the operator will have to restart the SQLMACH
database machine, specifying that DB02 is to be accessed. DB01 must be
stopped before DB02 can be restarted.
Several Database Machines with Many Databases
In Figure 50, all virtual machines reside on the same processor.
MARYLOU
STEVE
CINDY
MIKE
MARY
(3)
SQLMACH
SQLMFB
(1)
(2)
SQLMACH owns
SQLMFB owns
the DB01
DB01
DB02
the DB03
DB03
and DB02
database
databases
Figure 50. Two Database Machines with Three Databases
Points (1), (2), and (3) of the figure are as follows:
1. The SQLMACH database machine was set up to use the DB01 database.
The SQLMACH operator selects a database when the database machine is
started. In this example, the operator had the choice of selecting the DB01 or
DB02 database. The DB01 database was chosen.
Note that a database machine can only access one database at a time. To access
the DB02 database, the operator must restart the SQLMACH database machine,
specifying the DB02 database.
2. The SQLMFB database machine was set up to use the DB03 database.
Note that it is possible for one database machine to access a database “owned”
by another database machine, as long as the virtual machines reside on the
same processor. For example, the SQLMFB database machine could access the
DB02 database, provided that the SQLMFB operator knows the minidisk
passwords for the DB02 database minidisks.
196
Database Administration
Note: A database machine “owns” a database if its virtual machine directory
contains the MDISK statements for the database minidisks.
3.
Five user virtual machines (MARYLOU, STEVE, CINDY, MIKE and MARY)
communicate with the database machines. MARYLOU, STEVE, and CINDY
must enter:
SQLINIT DBNAME(DB01)
to specify DB01 as their default database, while MIKE and MARY must enter:
SQLINIT DBNAME(DB03)
to specify DB03 as their default database. (See “SQLINIT EXEC” on page 243
for information on the SQLINIT EXEC.)
Here, users cannot access the DB02 database. If they entered “SQLINIT
DBNAME(DB02)” and then tried to access DB02 (using ISQL, the DBS Utility,
or an application program), an SQL error would occur.
If users need to access DB02, the SQLMACH database machine operator will
have to restart the SQLMACH database machine, specifying that DB02 is to be
accessed. Restarting the SQLMACH database machine to access DB02 will stop
users from accessing DB01.
Users can change the database they are accessing in two ways:
a. Using the SQLINIT EXEC to specify a new default.
b. Using the CONNECT statement to switch databases. This can be done from
within an application (ISQL, the DBS Utility, or an application program).
For example, suppose MARYLOU is accessing DB01 using ISQL. She can
switch to DB03 by entering the following SQL statement:
CONNECT TO DB03
See “Connecting to an Application Server in VM” on page 97 for more
information.
Multiple Database Machines on Different Processors
Users can access a database that resides on another processor, provided both
processors are running on VM/ESA systems, and are connected in TSAF, SNA or
TCP/IP network. (Refer to the DB2 Server for VM System Administration manual for
information about network configurations.)
Figure 51 shows an example of accessing a database located on another processor.
Chapter 9. DB2 Server for VM Database Configurations
197
Processor 1
Processor 2
MARYLOU
STEVE
CINDY
RALPH
(3)
A User Virtual
Machine can
access a data
base on another
system
SQLMACH
SQLMFB
(2)
(1)
DB01
DB02
DB01, DB02
DB04
DB04 is
are owned by
owned by
SQLMACH.
SQLREM
Figure 51. User Accessing a Database on Another Processor
Points (1), (2), and (3) of the figure are as follows:
1.
The SQLMACH database machine was set up to use the DB01 database.
The SQLMACH operator selects a database when the database machine is
started. In this example, the operator had the choice of selecting the DB01 or
DB02 database. The DB01 database was chosen.
Note that a database machine can only access one database at a time. To access
the DB02 database, the operator must restart the SQLMACH database machine,
specifying the DB02 database.
2.
The SQLREM database machine was set up to use the DB04 database.
The database is established as a global resource.
Note: Databases can be classified as either local or global. A local database can
only be accessed by users located on the same processor as itself, while a
global one can also be accessed by users located on other processors
within the collection.
The SQLREM operator specified the DB04 database at startup.
It is possible for a database machine to access a database “owned” by another
database machine, provided the virtual machines reside on the same processor.
The SQLREM database machine cannot access the DB01 database (owned by
SQLMACH), because SQLMACH and DB01 are on a different processor.
3.
Four user virtual machines (MARYLOU, STEVE, CINDY and RALPH)
communicate with the database machines.
MARYLOU and STEVE enter:
SQLINIT DBNAME(DB01)
198
Database Administration
to specify DB01 as their default database, while CINDY and RALPH enter:
SQLINIT DBNAME(DB04)
to specify DB04 as their default database.
Note that although CINDY is on a different processor from RALPH, both access
the DB04 database in the same way, and CINDY is able to specify DB04 as her
default database.
Users can change the database they are accessing in two ways:
a. Using the SQLINIT EXEC to specify a new default database.
After establishing a new default database, the user could then access the
database using ISQL, the DBS Utility, or application programs.
b. Using the SQL CONNECT statement.
This can be done from within an application (ISQL, the DBS Utility or
application program).
For example, suppose MARYLOU is accessing DB01 using ISQL. She can
switch DB04 by entering the following SQL statement:
CONNECT TO DB04
Refer to “Connecting to an Application Server in VM” on page 97 for more
information.
Accessing a Database from a Processor that Does Not Have
One
Users on processors that do not have a database machine or a database can access
a database on another processor. (The processors must be running on VM/ESA
systems, and all user IDs must be unique between processors.) Figure 52 shows an
example.
Processor 1
Processor 2
M
ARYLOU
STEVE
CINDY
JOHN
(2)
(3)
SQLMACH
(1)
DB01
Figure 52. Accessing a Database from Another Processor
Points (1), (2), and (3) of the figure are as follows:
Chapter 9. DB2 Server for VM Database Configurations
199
1. The SQLMACH database machine was set up to use the DB01 database.
The SQLMACH operator selects a database when the database machine is
started. This database is established as a global resource.
2. Four user virtual machines (MARYLOU, STEVE, CINDY, and JOHN)
communicate with the database machine (SQLMACH) to access DB01.
MARYLOU, STEVE, CINDY, and JOHN enter:
SQLINIT DBNAME(DB01)
to specify DB01 as their default database.
When MARYLOU, STEVE, CINDY, or JOHN invoke ISQL, the DBS Utility, or
an application program, DB01 will be accessed.
3. JOHN is on Processor 2 which does not have a database machine or database.
JOHN must have a link to the service machine disk in order to access the
database on Processor 1. This service machine must be installed on Processor 2.
Performance Considerations with Multiple Databases
Most of the processing time for any transaction is spent in the database machine
and not in an application program. It is possible to have users on one processor
accessing data stored on another processor if the operating systems are VM/ESA
systems. However, because of the processor overhead of inter-processor
communication, a database should be placed on a processor that is closest to its
greatest number of users, preferably on the same one.
If you plan to have users on one processor accessing a database on another
processor, you should consider the overhead of inter-processor communication.
Most message traffic between the user and the database machine should flow
within a single processor. Only a small percentage of messages should flow
between processors, as happens when users infrequently access data located on
other processors.
Although the end user does not need to know the physical location of a database,
you must have a good understanding of user-group requirements in a particular
business environment. Users should first be grouped according to the business
tasks they perform. Databases and tables can then be arranged so that they are
always accessed by most users in a particular group. A good understanding of the
business environment and the needs of various user groups can aid in determining
whether users on one processor are allowed to access databases on other
processors.
When planning your database configuration, you should generally avoid:
v Database configurations with central databases
v Databases with randomly distributed tables.
Database configurations should be designed so that:
v Groups of tables are situated in the database closest to their greatest number of
users
v Tables in databases on other processors are only occasionally accessed.
For more information on performance considerations, see the DB2 Server for VM
System Administration manual.
200
Database Administration
VSE Guest Sharing (On VM/ESA Systems Only)
If you have VSE/AF 4.1.0 or higher running as a guest under a VM/ESA system,
VSE users can access DB2 Server for VM databases through VSE Guest Sharing.
The database accessed can be on the same VM/ESA system as the one that
supports the VSE guest, or on another VM/ESA system in the same TSAF
collection, SNA or TCP/IP network. A VSE guest user can use database switching
to target another database. An example of a DB2 Server for VM database with VSE
Guest Sharing support is shown in Figure 53.
BATCH
DBSU
DB2
BATCH
PREPROCESSORS
Server
for VM
ISQL
CMS
CICS
CICS/ICCF
DSC
ISQL
SQL
RDS
CICS
TRANS-
ACTION
DBSS
VSE/AF
CMS
CMS
VSE/AF
CP
APPC
APPC
database
(logs,
directories,
data)
Figure 53. VSE Guest Sharing Configuration
In a VSE Guest Sharing environment, VM/ESA users and applications can use the
same functions they use in a VM/ESA environment without being aware of guest
sharing.
For more information on VSE Guest Sharing, and on the DB2 Server for VM
configurations that you can implement, see the DB2 Server for VM System
Administration manual.
Chapter 9. DB2 Server for VM Database Configurations
201
202
Database Administration
Chapter 10. Usage Environments in VSE
The hardware and software needed to run the DB2 Server for VSE system varies
depending on the usage of the DB2 Server for VSE system.
Depending on your requirements, your DB2 Server for VSE environment may be
set-up in different ways. The purpose of this chapter is to give you an idea of the
various different usage environments that can be set up. Further information about
how to set up various DB2 Server for VSE environments can be found in the DB2
Server for VSE System Administration manual.
This chapter describes five different DB2 Server for VSE usage environments. You
will need to consider each of these environments and choose the one appropriate
for your processing requirements. It also describes the recommended and required
options of the associated program products.
Batch/Interactive Application Processing
VSE/ICCF
MONITOR
/
TTF
VSE/ICCF
/
TERMINALS
TRANSACTION
/
PREPROCESSOR
DBS UTILITY
DB2 Server
for VSE
APPLICATION
VSE/ICCF INTERACTIVE
PARTITIONS
DB2 Server
for VSE
DB2 Server
DBMS
for VSE
(VSAM)
DATABASE
DB2 Server for VSE PARTITION
PREPROCESSOR
DBS UTILITY
DB2 Server
for VSE
APPLICATION
BATCH PARTITION(S)
VSE/ADVANCED FUNCTIONS
Figure 54. Batch/Interactive Configuration
Batch/interactive processing ( Figure 54) is mostly execution of compiled PL/I,
COBOL, C, Fortran or assembler programs which are batch (or VSE/ICCF) SQL
applications. Some dynamic SQL processing may occur in the form of application
preprocessing and compilation, and DBS Utility executions.
203
Operation of this type of system places minimal demands on system resources
(real storage and processor power). However, there is a corresponding loss of
function, because such a system cannot support the query/report writing facilities
(ISQL) of the DB2 Server for VSE or online (CICS) transaction processing with DB2
Server for VSE.
There are no special prerequisites beyond the base DB2 Server for VSE
prerequisites for VSE/Advanced Functions and VSE/VSAM except that one of the
supported programming languages (PL/I, COBOL, C, Fortran, or assembler) is
required. VSE/ICCF is not required, but it can be used for terminal access and
invocation of the SQL applications, and for data administration activities.
A batch/interactive system should be considered for automation of fixed business
applications that do not require end user access to the system. Such a system is
typically developed on a larger system that is configured to support application
development.
Online (CICS) Transaction Processing
SQL
TR
/
/ TERMINALS
SQL
/
TRANSACTION
CICS
DB2 Server
for VSE
DB2 Server
DBMS (VSAM)
for VSE
DATABASE
DB2 Server for VSE PARTITION
PREPROCESSOR
DBS UTITILY
DB2 Server
for VSE
APPLICATION
BATCH PARTITION(S)
VSE/ADVANCED FUNCTIONS
Figure 55. Online Transaction Processing Configuration
Online transaction processing usage of the DB2 Server for VSE system (Figure
55)
is mostly preplanned CICS SQL transactions. Some dynamic SQL processing may
occur in the form of SQL/DS preprocessor and DBS Utility jobs, which do not run
under the CICS subsystem.
Like the batch/interactive application processing usage, the online transaction
processing usage demands fewer real storage and processor resources than
dynamic SQL usage demands.
204
Database Administration
Online transaction processing with the DB2 Server for VSE system requires
installation of CICS Release 2.3, or an equivalent transaction processing product.
The DB2 Server for VSE online support must also be installed. The CICS
subsystem provides the terminal management and transaction processing
environment. Programs may be written in PL/I, COBOL, C, or assembler, but not
Fortran as the CICS subsystem does not support Fortran. You can, however, use
Fortran for batch programs.
An online transaction processing system should be considered for preplanned
business applications where end user access to the system is managed through
CICS transactions programmed for specific end user tasks.
ISQL might be installed, but its use would be limited to data administration
functions.
For the online transaction processing environment, the system should be
configured as follows:
v CICS Options:
- The Dynamic Transaction Backout Program (DBP) is required for proper
coordination and recovery with the DB2 Server for VSE database manager.
- The Exec Interface Program (EIP) is required to support transaction access to
the DB2 Server for VSE application server.
- The CICS User Exit Interface is also required for transaction access to the DB2
Server for VSE application server.
- The CICS Monitoring Facility is optional, but should be used so that the DB2
Server for VSE database manager participates in the monitoring by providing
performance class information.
- The CICS Restart Resynchronization facility is required to support task-related
user exit resynchronization and to use the SQL/DS accounting facility in an
online environment.
v The VSE/POWER program is required for the system printer or remote
workstation printer report-writing support in ISQL. It is not required for report
writing to CICS terminal printers. Only the VSE/POWER program provides
multiple copy capability.
Chapter 10. Usage Environments in VSE
205
Application Development
VSE/ICCF
MONITOR
SQL
TRANS
ISQL
/
TRANS.
/ TERMINALS
/
CICS
VSE/ICCF
INTERACTIVE
TRANS.
PARTITION
PREPROCESSOR
DBS UTITLITY
DB2 Server for
VSE
APPLICATION
VSE/ICCF INTERACTIVE
PARTITIONS
DB2 Server
for VSE
DB2 Server
DBMS
for VSE
(VSAM)
DATABASE
DB2 Server for VSE PARTITION
PREPROCESSOR
DBS UTILITY
DB2 Server for
VSE
APPLICATION
BATCH PARTITION(S)
VSE/ADVANCED FUNCTIONS
Figure 56. Application Development Configuration
Application development usage of the DB2 Server for VSE system ( Figure 56)
includes a large amount of data design, application coding, and testing. Such
activities typically involve a modest level of dynamic SQL activity in the form of
data definition, catalog queries, and program preprocessing. Correspondingly, there
is more demand for real storage and processor resources, than that demanded by
application or transaction processing.
The program products required to support application development on the DB2
Server for VSE system vary depending on the type of application being developed
and the DB2 Server for VSE facilities to be used. The optional program products
and their options are discussed below in terms of their value to application
development usage of the DB2 Server for VSE system.
1. Programming Languages
For development of programmed applications you would need one or more of
the PL/I, COBOL, C, Fortran, or Assembler language products.
2. VSE/ICCF
You can install VSE/ICCF (or an equivalent) to support an interactive
application development capability.
3. The CICS subsystem
206
Database Administration
The CICS subsystem is required to support development of CICS transactions
or use of the ISQL facilities for application development. It should be generated
with the dynamic transaction backout program (DBP), the EXEC interface
program (EIP), and the CICS user exit interface. Optionally, you can generate it
with the Monitoring Facility and Restart Resynchronization. Restart
resynchronization is required if you want to (1) use the SQL/DS accounting
facility in an online environment, and (2) support task-related user exits.
4. VSE/POWER
The VSE/POWER program is required for the system printer or remote
workstation printer report-writing support in ISQL. It is not required for report
writing to CICS terminal printers. Only the VSE/POWER program provides
multiple copy capability.
Query/Report Writing
SQL
TRANS.
ISQL
TERMINALS
TRANS.
CICS
TERMINAL
PARTITION
PRINTER
SYSTEM
VSE/POWER
PRINTER
VSE/POWER PARTITION
DB2 Server
for VSE
DBMS
DB2 Server
(VSAM)
for VSE
DATABASE
DB2 Server for VSE PARTITION
PREPROCESSOR
DBS UTILITY
DB2 Server
for VSE
APPLICATION
BATCH PARTITION
VSE/ADVANCED FUNCTIONS
Figure 57. Query/Report Writing Configuration
The query/report-writing usage environment supports dynamic SQL query and
report writing by end users. Due to the dynamic interpretation of user requests
and the fact that data requests are unconstrained, this usage environment places a
relatively high demand on system resources. The internal sort capability of the DB2
Server for VSE database manager is likely to be used frequently.
The program products and options required to support this environment are as
follows:
1. The CICS subsystem
Chapter 10. Usage Environments in VSE
207
ISQL operates as a set of CICS transactions. The CICS subsystem should be
generated with the following options:
v Dynamic Backout Program (DBP)
v Exec Interface Program (EIP)
v CICS User Exit Interface
v CICS Monitoring Facility (optional)
v CICS Restart Resynchronization, if you want to use either the SQL/DS
accounting facility in an online environment or support task-related user
exits.
2. VSE/POWER
The VSE/POWER program is required for the system printer or remote
workstation printer report-writing support in ISQL. It is not required for report
writing to CICS terminal printers. Only the VSE/POWER program provides
multiple copy capability.
208
Database Administration
Chapter 11. Stored Procedures
Stored Procedure Concepts
A stored procedure is a user-written application program that is compiled and
stored at the server. When the database manager is running in multiple user mode,
local applications or remote DRDA applications can invoke the stored procedure.
Since the SQL statements issued by a stored procedure are local to the server, they
do not incur the high network costs of distributed statements. Instead, a single
network send and receive operation is used to invoke a series of SQL statements
contained in the stored procedure.
There are several other benefits that can be gained through the use of stored
procedures, including:
v In many applications, the integrity of the host variables used in SQL statements
is critical to the business function provided by the application. For example, a
debit/credit application might need to guarantee that the host variable values do
not change between debit and credit operations. In these applications, the
application designer would like to guarantee that sophisticated users cannot
employ online debugging tools to manipulate the content of SQL statements or
host variables used by the SQL application. By using stored procedures, the
application designer can encapsulate the application’s SQL statements into a
single message to the server, which moves the sensitive processing beyond the
reach of even the most sophisticated workstation user.
v Stored procedures can be used to hide the details of the database design from
client applications. In addition to simplifying the writing of client applications,
this means that if the database design is changed, only the stored procedure
needs to be modified. The more client applications that use the stored procedure,
the greater the benefit.
v Stored procedures can be used to hide sensitive data from application programs.
v Business logic can be encapsulated at the server, rather than being included in
numerous application programs.
v It is easier to maintain an environment in which applications are kept at the
server rather than spread across a number of requesters.
Stored Procedure Servers
The Stored Procedure Server
In DB2 Server for VSE & VM, all stored procedures are fenced, which means that
they are separated from the database manager with respect to execution and
memory usage. This is necessary to ensure that a stored procedure does not
v inadvertently use storage that is allocated to the database manager
v monopolize processing in the database machine or partition, which would
effectively hang the database
A fenced implementation is achieved through the use of stored procedure servers.
An stored procedure server is an application requester that is local to the database
manager and is used to execute the stored procedure. A fenced implementation is
achieved as follows:
209
v In VM, the stored procedure server is a separate virtual machine that is local to
the DB2 Server for VSE & VM server, and uses the ’private resource’ facility of
VM.
v In VSE, the stored procedure server is a separate static or dynamic partition.
Note that a stored procedure server must be dedicated to a single database server.
The Stored Procedure Handler
The stored procedure handler is a DB2 Server for VSE & VM supplied utility,
called ARISPRC, that interfaces between the database manager and the stored
procedure. It runs in the stored procedure server and does the following:
v Initializes the runtime environment. This is done when the stored procedure
server is started. For the steps involved, see ″The START PSERVER Command″
in the DB2 Server for VSE & VM Operation manual.
v Waits to receive an SQL CALL request from the database manager.
v Invokes the specified stored procedure, which will send requests and receive
replies directly to and from the database manager.
v Returns the output parameters and result set information to the database after
the stored procedure has terminated.
v Waits for another SQL CALL request from the database manager.
Stored Procedure Server Groups
The group clause of the CREATE PSERVER statement makes it possible to define
groups of stored procedure servers. This is useful if
v certain stored procedures must always have a server available. In this case, a
stored procedure server group could be dedicated to the procedure, and other
procedures could share other groups.
v certain procedures have special requirements, for example, the need for
unusually large amounts of virtual storage.
v certain procedures must access resources that are note required by most
procedures.
The group option gives the database administrator flexibility in defining the
environment and is useful for system tuning.
Setting up a Stored Procedure Server
The SERVGROUP column in SYSTEM.SYSROUTINES is cross-referenced with the
SERVGROUP column in SYSTEM.SYSPSERVERS to establish the server that is to
be used for a stored procedure.
In DB2 Server for VM, the following requirements exist:
v The PSERVER column in SYSTEM.SYSPSERVERS specifies the name of the
stored procedure server.
v The VM machine name (user ID) of the stored procedure server must equal the
value in the PSERVER column in SYSTEM.SYSPSERVERS. The CP directory of
the database machine must contain the following statement:
- IUCV userid
where userid is the VM ID of the stored procedure server virtual machine. This
enables the database manager to request the services of the stored procedure
server. Note that this can also be enabled by putting an IUCV ALLOW statement
in the CP directory of the stored procedure server virtual machine. However, the
first method requires the database machine to have explicit access to the stored
procedure server machine, and the second method allows any machine to
210
Database Administration
connect to the stored procedure server machine. Since the stored procedure
server must be dedicated to a single database machine, the first method is
preferred.
v
The CP directory of the stored procedure server VM machine must contain the
following statements:
1. IPL CMS
This directory control statement causes CP to start CMS in the stored
procedure server virtual machine.
2. OPTION MAXCONN nnnn
This directory control statement indicates the number of IUCV and
APPC/VM connections allowed for the virtual machine. Unless a stored
procedure that runs on the server does work that requires additional
connections, setting nnnn to 1 is sufficient.
v
The PROFILE EXEC for the stored procedure server VM machine must have the
following CMS commands:
- SET SERVER ON
- SET FULLSCREEN OFF
- SET AUTOREAD OFF
The following is a sample profile:
’GLOBALV INIT’
/* The following three lines have to be in PSERVER’s
PROFILE
EXEC
’SET SERVER ON’
’SET FULLSCREEN OFF’
’SET AUTOREAD OFF’
’SET CMSTYPE HT’
’SET IMSG OFF’
’SET LANGUAGE AMENG (ADD ARI USER’
’CP SET RUN ON’
/* This prevents CP READ upon
*/
/* RECONNECTing to userid.
*/
’CP TERM MODE VM’
/* Accept CMS commands.
*/
’GLOBAL LOADLIB SCEERUN’
/* LAODLIB FOR LE PROGRAMS
*/
’CP LINK SQLMACH 195 195 RR’
/*Link to database product disk
*/
’ACCESS 195 Q’
/*Access as Q disk
*/
’EXEC SQLINIT DB(SQLMACH)’
/*Initialize as normal AR
*/
IF RC <> 0 THEN DO
SAY ’SQLINIT FAILED WITH RETURN CODE = ’ RC
’TELL SQLMACH SQLINIT FAILED IN PSERVER’
’#CP LOGOFF’
END
ELSE
SAY ’PSERVER INITIALIZATION COMPLETED.’
Note: the PROFILE EXEC should not contain any commands that require
console input, or put the ID into VM READ.
v
A $SERVER$ NAMES file, which controls who can connect to the VM machine
and what module to invoke when the machine is started, must exist. An
example of a $SERVER$ NAMES file entry is:
Chapter 11. Stored Procedures
211
:nick.SQLSVR01 :module.ARISPRC
:list.SQLMACH
The fields in the $SERVER$ NAMES files represent the following:
- nick
The name of the private resource. This is VM machine name of the stored
procedure server virtual machine.
- list
The user IDs of the users that are authorized to access the private resource.
This is the VM machine name of the database server virtual machine. Since
stored procedure servers must be dedicated to a single database, only one
name can be specified here. The stored procedure handler will not start if
more than one name is specified.
- module
The name of the stored procedure handler, ARISPRC.
For complete details on setting up the machine, see ″Managing Private
Resources: in the VM/ESA Connectivity Planning, Administration, and Operation
manual″.
v
Ensure that the Stored Procedure handler module (ARISPRC) can be accessed by
the stored procedure server. The DB2 Server for VSE & VM installation process
creates this module on the database machine’s production disk.
v
Ensure that the Stored Procedure Server has been defined to the database by
using the CREATE PSERVER command. See DB2 for VSE & VM SQL Reference
for more information on this command.
v
All stored procedures must be LE-compliant and their load modules must reside
in a disk accessible to the stored procedure servers that will invoke them.
In DB2 Server for VSE, the following requirements exist:
v The JCL used to start the database must contain a statement that defines logical
device 097 (for example, ’ASSGN SYS097,cuu’),to enable POWER to find and
execute the JCL that starts the stored procedure handler. An example is
illustrated below:
// ASSGN SYS097,SYSPCH
// ASSGN SYS098,SYSLST
The following is a sample JCL to start up the DB2 Server for VSE 7.5.0 database:
// JOB Start DB2 for VSE in multiple user mode with Stored Procedure Support
// EXEC PROC=ARIS75PL
// EXEC PROC=ARIS75DB
// ASSGN SYS097,SYSPCH
// ASSGN SYS098,SYSLST
// EXEC PGM=ARISQLDS,SIZE=AUTO, PARM=DBNAME=SAMPLE_DB
Note: You will need to customize ARIS75PL, ARIS75DB to work with your local
VSE/ESA environment.
v The PSERVER column in SYSTEM.SYSPSERVERS specifies the name of the
stored procedure server. This name must be of datatype CHAR(8).
212
Database Administration
v
The name in the PSERVER column is also the name by which the stored
procedure handler identifies itself to VSE for XPCC communications.
v
There must be a JCL for each stored procedure server you define. The JCL is
used to start up the server’s partition. The file name of the JCL and the jobname
in the JOB card should both be the stored procedure server’s name (as defined
in the PSERVER column of SYSTEM.SYSPSERVERS). The JCL must be
catalogued into a library, in the search path defined in your database startup
JCL, as an A-TYPE member. The following is a sample JCL for stored procedure
server SQLSVR01:
. $$ PUN CLASS=6,DISP=I,JNM=SQLSVR01
* $$ LST CLASS=V,DISP=D,DEST=(,SYSID01)
// JOB SQLSVR01
// OPTION NODUMP,NOSYSDUMP
// ASSGN SYS098,SYSPCH
// LIBDEF *,SEARCH=(CMPLR22.SCEEBASE,CMPLR22.LEVSEBC,
CMPLR22.SCEECICS,PRD2.DB2710)
ON $RC > 0 GOTO END
// EXEC PGM=ARISPRC,SIZE=1M
/.END
/*
/&
Note: For the PUNCH card, you must use . $$ PUN instead of * $$ PUN. Also,
you must make sure that all your stored procedure phases are in the search path
defined in this JCL so that stored procedure server handler phase, ARISPRC, to
be able to find and load the stored procedure.
v
Stored procedure server support is currently treated as an optional feature.
Hence, you will need to build the stored procedure server handler phase
manually. The name of the phase is ARISPRC. The following is a sample JCL
used to linkedit the ARISPRC phase. The phase should reside in your
production library together with other DB2 for VSE phases.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
L I N K
PSERVER component
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
// OPTION CATAL
INCLUDE ARISLKHZ
// EXEC LNKEDT,PARM=’MSHP’
/*
Note: ARISLKHZ is a the linkbook used to linkedit the stored procedure server
phase, and it is a member of your production library.
v
All stored procedure will have to be compiled using LE enabled compiler. The
phases of these stored procedures have to be in the search path defined in stored
procedure server’s startup JCL.
Managing Stored Procedure Servers
Stored Procedure Server Allocation
When a stored procedure is running, the stored procedure server in which it is
executing is dedicated to it. Since no other stored procedure can execute in that
server until the current one finishes, it is not possible to execute multiple stored
Chapter 11. Stored Procedures
213
procedures concurrently with a single stored procedure server. However, multiple
stored procedures can execute concurrently if multiple stored procedure servers are
defined.
The CREATE PROCEDURE statement must be used to define a stored procedure
before it can be used. The CREATE PROCEDURE puts the definition of the
procedure into the catalog tables SYSTEM.SYSROUTINES and
SYSTEM.SYSPARMS. See DB2 Server for VSE & VM SQL Reference for the
definitions of these tables as well as more details on the CREATE PROCEDURE
statement. A third catalog table, SYSTEM.SYSPSERVERS, is used to identify the
stored procedure servers. See DB2 for VSE & VM SQL Reference for the definition
of this table. When the database manager is started, DB2 Server for VSE & VM
loads the contents of these tables into cached structures. The cached structures
contain the information from the catalog tables, as well as information about the
run time status of stored procedures and stored procedure servers. When the
database manager is started, the status of a stored procedure defaults to STARTED,
and the status of a stored procedure server defaults to STOPPED. The database
manager then issues a START PSERVER command for any stored procedure server
for which the AUTOSTART value of the corresponding row in
SYSTEM.SYSPSERVERS is Y. When the START PSERVER command completes, the
status of that stored procedure server is STARTING. When an SQL CALL
statement is issued, the database manager uses the cached information to
determine the server at which this stored procedure will run. To determine the
server, DB2 Server for VSE & VM does the following. Note that when these steps
make reference to the catalog tables, it is the cached information from the tables
that they are referring to.
1. Gets the value of the SERVGROUP column from the row in
SYSTEM.SYSROUTINES for the procedure
2. Looks for the first row in SYSTEM.SYSPSERVERS in which the value of the
column SERVGROUP matches the SERVGROUP value retrieved from
SYSTEM.SYSROUTINES, and which is not currently running a stored
procedure. If one is found, the action taken by the database manager depends
on the status of the procedure server:
v If its status is STARTED, the database manager sends a command to that
server, to cause it to invoke the stored procedure.
v If its status is STARTING, the database manager completes the START
PSERVER processing, by establishing a connection to the stored procedure
server and invoking the stored procedure handler. It then changes the status
of the stored procedure server to STARTED and sends a command to the
server, to cause it to invoke the stored procedure.
v If its status is STOPPING, the database manager ignores that server.
v If its status is STOPPED, the database manager checks whether that server
can be started implicitly. If it can be started implicitly, DB2 Server for VSE &
VM issues the START PSERVER command, and when the command
completes, that server is used to run the stored procedure. For more
information on starting a stored procedure server implicitly, refer to the
description of the IMPLICIT/NOIMPLICIT option of the STOP PSERVER
command in the DB2 Server for VSE & VM Operation manual.
If none of the servers in the group can be used, and the group checked was not the
default group, DB2 Server for VSE & VM checks whether the stored procedure can
run in the default group. Servers in the default group are indicated by a value of
NULL for the SERVGROUP column in SYSTEM.SYSPSERVERS. If the DEFSERV
column in SYSTEM.SYSROUTINES contains a ’Y’ or is NULL, then that procedure
214
Database Administration
can run in a server in the default group. If no servers are available in
SERVGROUP, or if SERVGROUP in SYSTEM.SYSROUTINES is blank, and the
procedure can run in the default group, DB2 Server for VSE & VM will attempt to
run the stored procedure in one of the default servers. The process it uses to find a
server in the default group is the same as the one it used to look for a server in a
specific group, as described earlier.
If none of the servers at which it can run are available, the stored procedure waits
for a free server. If more than one stored procedure is waiting for the same server,
the one that has been waiting the longest will be invoked when the server is
available.
Figure 58 shows how DB2 Server for VSE & VM resolves the server name. Note
that in order to illustrate the process used, the figure shows cached data. Not all of
the columns shown are in the corresponding catalog table.
The following steps are involved in Figure 58:
EXEC SQL CALL PROC1
1
PROCEDURE
AUTHID
LOADMOD
SERVGROUP
DEFSERV
PROC1
MOD1
GROUP1
Y
2
4
PSERVER
SERVGROUP
AUTOSTART
DESCRIPTION
PROC
STATUS
5
SRV1
Y
DEFAULT
STARTED
SRV2
Y
DEFAULT
STRATED
SRV3
GROUP1
Y
PROCX
STRATED
SRV4
GROUP1
PROCY
STRATED
3
SRV5
GROUP1
N
STOPPED
Figure 58. How DB2
Server for VSE & VM Determines the Stored Procedure Server to Use
1. SQL CALL PROC1 is issued. The database manager retrieves the SERVGROUP
value from the row for PROC1 in the cached information from
SYSTEM.SYSROUTINES.
2. The database manager looks for the first row in the cached information from
SYSTEM.SYSPSERVERS in which the SERVGROUP column matches the
SERVGROUP value retrieved from the cached information for
SYSTEM.SYSROUTINES and the PROC information is blank.
3. The row for PSERVER SRV5 is found, but it is stopped and cannot be started
implicitly.
4. Since there are no other servers in GROUP1 that are not already running a
stored procedure, the database manager checks the column DEFSERV to see if
PROC1 can run in the default server group. A value of ’Y’ or NULL in this
column indicates that it can run in the default group.
5. The SYSTEM.SYSPSERVERS data contains a value of NULL in the
SERVGROUP column for any server in the default group. In this case, SRV1
and SRV2 are in the default server group and both are available. The database
manager will use SRV1 since it is the first one found. It will update the cached
information from SYSTEM.SYSPSERVERS to indicate that SRV1 is being used
for PROC1, and send a command to SRV1 to cause it to invoke the stored
procedure PROC1.
Chapter 11. Stored Procedures
215
States of a Stored Procedure Server
A stored procedure server can be in several different states: STARTED, STARTING,
STOPPING or STOPPED. Each state reflects/indicates the availability of the server
and whether or not its definition can be altered or dropped. The START and STOP
PSERVER operator commands can be used to change the state of a stored
procedure server. An incoming SQL CALL request can also change the state of a
stored procedure server, but only in certain situations, as will be described later.
Following is a description of how each state is achieved and the functions that can
be performed on the server for each of these states.
STOPPED
This is the default state for all stored procedure servers when the database
manager is started. It is also achieved when a procedure server is stopped using
the STOP PSERVER operator command. This state has two conditions that
determine whether certain functions, such as an SQL CALL implicitly starting the
server, are allowed. These two conditions are IMPLICIT and NOIMPLICIT. At
startup, all stored procedure servers are STOPPED with IMPLICIT. Here is a
summary of the differences between the two:
v STOPPED with IMPLICT: in this state, the stored procedure server is considered
to be available as it can be implicitly started by the database manager if an SQL
CALL request requires it to execute. When the IMPLICIT condition is true, the
stored procedure server definition cannot be altered or dropped. A stored
procedure server achieves this state when the STOP PSERVER IMPLICIT
operator command is issued.
v STOPPED with NOIMPLICIT: in this state the stored procedure server is not
available to execute any SQL CALL requests, and the database manager cannot
implicitly start the stored procedure server. Since the server is not available, its
definition can be altered or dropped. This is the only state that allows changes
to the server’s definition. To achieve this status issue the STOP PSERVER
NOIMPLICIT operator command on the desired server. Also, if the database
manager’s attempt to start the server (i.e. establish a physical connection) times
out, the stored procedure server will be STOPPED NOIMPLICIT by the database
manager. This is done as a time-out might indicate a problem with the stored
procedure server handler. See the DB2 Server for VSE & VM Diagnosis Guide and
Reference manual for more information.
A stored procedure server will always be stopped if a severe error is encountered
during the execution of a stored procedure. The IMPLICIT/NOIMPLICIT condition
is not changed, unless a connection time-out has occurred, as mentioned above.
STOPPING
A server can only achieve this state if it was executing a stored procedure at the
time the STOP PSERVER command was issued. When the state is ″stopping″, the
database manager know that once the current SQL CALL requests concludes, the
stored procedure server must be stopped. The stored procedure server will remain
available as long as the IMPLICIT condition is true.
STARTING
In this state, the server is ready to start executing an SQL CALL request. It has
been allocated a communication block but it is not yet connected. The database
manager will establish the connection once an SQL CALL statement requests to use
the server. If the connection is successful, the status will be changed to started. If it
fails, the server will be stopped. The IMPLICIT/NOIMPLICIT condition will
remain unchanged. If the connection times out, as mentioned earlier, the server
216
Database Administration
will be stopped with the NOIMPLICIT condition true. A server in this state can
also be stopped by issuing the STOP PSERVER operator command.
STARTED
In this state, the stored procedure server is connected to the database server and is
either executing an SQL CALL statement, or is ready to execute an SQL CALL.
This state can only be reached if an SQL CALL statement requested to use the
server. The START PSERVER operator command will not promote a stored
procedure server to this state as it does not establish the physical connection with
the database server. The STOP PSERVER command demotes the server to a status
of STOPPED and severs its connection if it is not currently executing an SQL CALL
request. If the server is in use, the STOP PSERVER command will demote it to a
status of STOPPING. The database server will complete the ″stopping″ sequence
once the SQL CALL request concludes, by severing the stored procedure server’s
connection, freeing its communication block and changing the status to STOPPED.
The IMPLICIT/NOIMPLICIT conditions will be dictated by the last STOP
PSERVER operator command issued against the server.
For more information on the STOP and START PSERVER commands see the DB2
Server for VSE & VM Operation manual. For more information on the SQL CALL
statement see the following manuals:
v DB2 Server for VSE & VM Application Programming
v DB2 Server for VSE & VM SQL Reference.
Altering or Dropping a Stored Procedure Server Definition
The following describes how to remove or alter a stored procedure server:
v Issue the STOP PSERVER command for that stored procedure server, specifying
the NOIMPLICIT option.
v Use the SQL DROP or ALTER PSERVER statement to delete or alter the row for
that server from SYSTEM.SYSPSERVERS. Note that the row can not be deleted
from SYSTEM.SYSPSERVERS if it is the only server in its group, and procedures
exist that must run in that group.
v Optionally take the steps necessary to remove the stored procedure virtual
machine (in VM) or redefine the partition (in VSE).
To remove a stored procedure server group, follow the steps above for each stored
procedure server in the group. It is possible to remove all the servers in a group
only if as there are no stored procedures defined that can use that server group. If
this is the case, it is not be possible to remove the last procedure server in the
group. Any stored procedures that run in the group must be moved to another
group (by using the ALTER PROCEDURE statement and specifying the SERVER
GROUP clause) or dropped before the last stored procedure server in the group
can be dropped.
Stored Procedures
Preparing a Stored Procedure to Run
Before a stored procedure can be invoked, it must be
v Preprocessed by the DB2 Server for VSE & VM precompiler to create a package
in the database.
v Compiled by the appropriate high level, LE compliant, language compiler, or
assembled.
v In VM, Linkedited and GENMODed to create a load module, which must be
placed on a disk that can be accessed by the stored procedure server. In VSE,
Chapter 11. Stored Procedures
217
|
||
|
|
|