DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 39
LO
identifies that only list format is to be used. The list format is used even when
the report or message file record can contain all column names or column data
for a selected row.
SET ISOLATION
SET ISOLATION Format
Format:
(
RR
)
►►
SET ISOLation
CS
►◄
UR
This command allows you to control the isolation level used for Database Services
Utility processing. Every time the utility is run, the isolation level is initialized to
repeatable read (RR). SQL processing through the utility is performed at this
isolation level until a SET ISOLATION command is encountered. The utility sets
the isolation level to the value specified in the command and processes at this level
until another SET ISOLATION command is executed or Database Services Utility
processing ends. The other isolation level settings are cursor stability (CS) and
uncommitted read (UR). This command cannot span input records.
If you are accessing a non-DB2 Server for VM application server, or if you are
using DRDA flow, the isolation level for the Database Services Utility is always set
to CS and the SET ISOLATION command has no effect.
RR
is used to protect a logical unit of work from uncommitted updates of another
logical unit of work. Also, no other logical unit of work can modify any row
that has been read by this logical unit of work.
CS
is used to protect a logical unit of work from uncommitted updates of another
logical unit of work. After data is read, the data is freed for others to update
before the end of the logical unit of work.
Use this setting only when the data is read or when you are the only user
authorized to update the data.
UR
is used when protection from other logical units of work is not required. Data
can be read without waiting for other logical units of work that are updating
the data. Reading data will not prevent other application processes from
updating it.
Note that data integrity may be compromised because read-only access to
uncommitted data is allowed.
This setting applies only to read-only operations (SELECTs, DATAUNLOAD
and UNLOAD) against data in public dbspaces with ROW or PAGE level
locking. For other operations (UPDATE, DELETE, INSERT, DATALOAD, and
LOAD), the rules of CS apply.
218
Database Services Utility
For dbspaces with DBSPACE level of locking, the rules of RR apply.
Recommended settings for Database Services Utility processing:
v Repeatable Read (RR)
- To ensure that the database is in a consistent state when using UNLOAD and
RELOAD TABLE/DBSPACE commands for database backup or migration.
The isolation level used to perform DATALOAD commands or perform
RELOAD DBSPACE/TABLE commands with the NEW option is not important.
Regardless of your isolation level setting, all UNLOAD/RELOAD PACKAGE
functions are performed with isolation level repeatable read. This does not affect
your setting of isolation level when you are performing other functions.
v Cursor Stability (CS)
- To reduce the contention on the database when running the Database Services
Utility with multiple user mode
- To perform RELOAD DBSPACE/TABLE commands with the PURGE option
- To use the Database Services Utility in the terminal input mode with
AUTOCOMMIT OFF in DB2 Server for VM
- To perform UNLOAD DBSPACE/TABLE or DATAUNLOAD processing for
read only data
- To update data for which you are the only person with update authorization.
v Uncommitted Read (UR)
- To reduce the contention on the database when running the Database Services
Utility with multiple user mode
Note that data integrity may be compromised when using UR. UR should only
be used when it is not necessary that the data be committed.
SET LINECOUNT, SET LINEWIDTH
SET LINECOUNT (LINEWIDTH) Format
Format:
►► SET
LineCount
(ccc)
►◄
80
120
LineWidth
(
www
)
LineWidth
(www)
60
LineCount
(
ccc
)
Note: 80 is valid in DB2 Server for VM only.
The SET LINECOUNT/LINEWIDTH command allows you to:
v Define the number of lines per page for Database Services Utility report output
or message file output.
v Define the number of print data positions used in each Database Services Utility
report or message file record containing SQL SELECT statement output.
This command cannot span input records.
Chapter 8. Command Reference
219
LINECOUNT(ccc) or LC(ccc)
If LINECOUNT(ccc) or LC(ccc) is specified, the value ccc is the number of lines
per page of printed output written to the Database Services Utility report or
message file. The value ccc can range from 10 to 32767; the default value is 60.
LINEWIDTH(www) or LW(www)
If LINEWIDTH(www) or LW(www) is specified, the value www is the maximum
number of print data positions used in a Database Services Utility report or
message file record containing SQL SELECT statement output. The default
value for www is 120. In DB2 Server for VM, if the Database Services Utility
message file (ddname=SYSPRINT) is assigned to the terminal, the number of
print data positions used for the SQL SELECT statement defaults to 80.
The value www can range from 60 to 256, but cannot be equal to or greater
than the logical record length of the Database Services Utility message file.
Notes:
1. The Database Services Utility always supplies an American Standards
Association (ASA) control character in the first position of the print record.
The second through nth positions of the print record are the print data
positions. If the value www+1 is less than the print record length, all
unused print data positions in the print record contain a blank (hex 40).
2. The DB2 Server for VSE Database Services Utility report record length is
always 121.
3. The minimum DB2 Server for VM Database Services Utility message file
record length is 81. If the Database Services Utility control parameter
PAGECTL(NO) is specified, the minimum message file record length is 80.
4. If the value www is equal to or greater than the print record length, an
error occurs.
SET UPDATE STATISTICS
SET UPDATE STATISTICS Format
Format:
UPDATE
(
ON
)
►►
SET
STATISTICS
OFF
►◄
The SET UPDATE STATISTICS command allows you to control the automatic
statistics collection performed during Database Services Utility RELOAD TABLE,
RELOAD DBSPACE, and DATALOAD TABLE command processing. This
command cannot span input records. If you do not supply a SET UPDATE
STATISTICS command in the input records, the utility operates as if you had
issued SET UPDATE STATISTICS ON.
The SET UPDATE STATISTICS command is not supported on a non-DB2 Server for
VM application server or if you are using DRDA flow.
ON
causes the utility to automatically collect statistics for each table loaded during
220
Database Services Utility
Database Services Utility RELOAD TABLE, RELOAD DBSPACE, and
DATALOAD TABLE command processing. This is the default mode of
processing.
The Database Services Utility writes message ARI8980I for each table or
dbspace loaded by a RELOAD TABLE, RELOAD DBSPACE, or DATALOAD
TABLE command. The message informs you that the statistics were collected
while the data was loading.
The Database Services Utility writes message ARI8996I and issues an SQL
UPDATE STATISTICS statement for each table loaded if you are using the
DATALOAD command and when any one of the following is true:
v You are loading data into more than one table.
v Indexes exist on the table.
v The table that you are loading data into already contains rows.
The SQL UPDATE STATISTICS FOR TABLE statement issued by the Database
Services Utility must read the whole table to update the internal DB2 Server
for VSE & VM statistics for the table. The statistics are updated based on the
current contents of the table and dbspace to which the table is assigned. Refer
to the DB2 Server for VSE & VM Application Programming manual and the DB2
Server for VSE & VM Database Administration manual for a description of the
SQL UPDATE STATISTICS statement processing.
OFF
suppresses the Database Services Utility statistics collection performed during
RELOAD TABLE, RELOAD DBSPACE, and DATALOAD TABLE command
processing.
A SET UPDATE STATISTICS OFF remains in effect until a SET UPDATE
STATISTICS ON command is processed or until the Database Services Utility is
restarted.
Note: If tables are loaded by the Database Services Utility RELOAD TABLE,
RELOAD DBSPACE, and DATALOAD TABLE commands while SET
UPDATE STATISTICS OFF is in effect, you must issue an SQL UPDATE
STATISTICS statement for the table or dbspace to update the internal
statistics.
To avoid the processing overhead associated with an SQL UPDATE STATISTICS
statement processing, you can suppress the normal Database Services Utility
UPDATE STATISTICS when you are using the DATALOAD TABLE command to
load a few records into a table that currently contains a larger number of records.
See “Update Statistics Considerations” on page 229 for details.
Chapter 8. Command Reference
221
Chapter 9. Error Handling and Debugging
This chapter describes the types of errors you can encounter, return codes, and
storage dumps. The action you take depends on the message that you receive. The
Database Services Utility generates its own messages as well as displaying DB2
Server for VSE & VM messages. All messages are explained in the DB2 Server for
VSE Messages and Codes and DB2 Server for VM Messages and Codes manuals.
Except for a report or message file processing error, all Database Services Utility
messages are written to the report or message file. If a report or message file error
occurs, a Database Services Utility message is generated on the operator console
describing the condition. In the report or message file, messages follow the
commands that caused them to be generated.
Types of Errors
The Database Services Utility takes different actions depending on the cause of the
error. The various kinds of errors are:
v
Database Manager or Operating System Failure
If the database manager (or the operating system) is terminated abnormally
while the Database Services Utility is running, any database updates that
occurred during the in-process Database Services Utility logical unit of work are
rolled back by recovery processing when the database manager is restarted. No
rollback occurs if the tables reside in nonrecoverable storage pools.
v
Database Services Utility Abnormal Termination Error Handling
If the Database Services Utility is terminated abnormally, database manager
processing restores any database updates that occurred during the in-process
logical unit of work unless updates are made to tables residing in
nonrecoverable storage pools.
v
Database Services Utility Processing Errors
Database Services Utility processing errors are those errors that do not cause
abnormal terminations. If a processing error occurs, and SET ERRORMODE is
not reset to OFF, or SET ERRORMODE CONTINUE is not in effect, the Database
Services Utility performs these actions:
1. Writes SQL or Database Services Utility error messages to the report or
message file except when message output is suppressed by either of the
Database Services Utility control parameters MESSAGES(NONE) or
MESSAGES(SQLONLY).
2. Restores any database updates made during the in-process logical unit of
work by executing an SQL ROLLBACK command, unless the updates were
made to tables residing in nonrecoverable storage pools.
3. Performs all possible Database Services Utility syntax checking on the
remainder of the (input) control file. The utility does no further database
manager processing.
4. Writes a Database Services Utility message identifying the unsuccessful
termination of Database Services Utility processing.
5. Updates register 15 with a nonzero return code.
6. Returns control to the invoking program with register linkage processing.
223
v
Report Errors
In DB2 Server for VSE, if the Database Services Utility encounters problems
when it tries to open the report, it generates a message to the operator console
and terminates processing after performing steps 5 and 6 above. If the utility
encounters problems when it tries to write to the report, it generates a message
and terminates processing after performing steps 2, 5, and 6 above.
v
Input Control Card File Errors
If the Database Services Utility encounters problems when it tries to open the
input control card file, it terminates processing after performing steps 4, 5, and 6
described above. If the utility encounters problems when it tries to read from the
input control card file, it terminates processing after performing steps 2, 4, 5,
and 6 as described above.
v
Message File Errors
In DB2 Server for VM, if the Database Services Utility encounters problems
when it tries to open the message file, it generates a WTO (write-to-operator)
message and terminates processing after performing steps 5 and 6 above. If the
utility encounters problems when it tries to write to the message file, it generates
a WTO message and terminates processing after performing steps 2, 5, and 6
above.
v
Command File Errors
If the Database Services Utility encounters problems when it tries to open the
command file, it terminates processing after performing steps 4, 5, and 6
described above. If the utility encounters problems when it tries to read from the
command file, it terminates processing after performing steps 2, 4, 5, and 6 as
described above.
v
Tape or DASD Data File Errors
If an incorrect ddname is specified in a Database Services Utility command, or
incorrect data file job control or CMS FILEDEF statements are supplied,
Database Services Utility processing is terminated. Database manager processing
restores any database changes when the Database Services Utility job abnormally
terminates because of a data-file-open error.
Return Codes
Although one or more of the following return codes may be encountered during
Database Services Utility processing, one final return code is provided at the end
of processing (the highest return code found). The following is a list of the return
codes:
0
All commands processed successfully.
4
All requested processing completed successfully, and all changes were
committed to the database. This return code indicates that an error
occurred during Database Services Utility termination; no SQL or Database
Services Utility commands need to be reprocessed.
6
One or more errors have occurred in command processing while SET
ERRORMODE CONTINUE was in effect.
8
Database Services Utility processing error encountered. From the point of
error, no further commands were executed, but subsequent Database
Services Utility commands were checked for syntax errors.
If a Database Services Utility SET ERRORMODE OFF command is
encountered before the end of the (input) control file is reached, normal
Database Services Utility command processing is resumed.
224
Database Services Utility
12
Input control card or Control command file-open-error. No commands are
processed.
16
Report or Message file-open-error. No commands are processed.
20
Initialization error. Sufficient processor storage was not available for
Database Services Utility working storage areas. No commands are
processed.
The type of ERRORMODE processing in effect at the time of a command
processing error determines the final return code. If both ERRORMODE OFF and
ERRORMODE CONTINUE processing are used within one (input) control file
(either by default or through a SET ERRORMODE command), one of the following
scenarios can exist:
v If an error occurs during ERRORMODE CONTINUE, the final return code is 6.
v If an error occurs during ERRORMODE OFF, the final return code is 8.
v If an error occurs during both ERRORMODE CONTINUE and ERRORMODE
OFF, the final return code is 8.
Storage Dumps
Dumps Initiated by the Database Services Utility
The Database Services Utility initiates a storage dump if an illogical condition or
critical error arises during its execution. Before a Database Services Utility storage
dump of the partition or virtual machine is initiated, the message ARI804E is
normally generated, and register 15 is set to a hexadecimal dump identification
(DUMP ID) value. After a storage dump is generated, Database Services Utility
processing continues.
The message ARI0804E identifies:
v The Database Services Utility module initiating the dump
v The reason code for the dump.
In two instances, Database Services Utility storage dumps are not preceded by the
usual ARI0804E message. These are:
v The storage dump (DUMP ID = hex 811) is initiated as a result of the .DEBUG
command.
v The storage dump (DUMP ID = hex 803) is initiated by the module ARIDBS
before Database Services Utility processing is terminated, and the final Database
Services Utility return code (register 15) value is 4 or is greater than 8.
The Database Services Utility modules initiating storage dumps, the reasons for the
dumps, and the hexadecimal dump identification values are explained in the DB2
Server for VSE Messages and Codes and DB2 Server for VM Messages and Codes
manuals.
Debugging
Processing for Debug Mode
In debug mode, if the sequence of commands described below is supplied in the
DB2 Server for VSE Database Services Utility input control card file, a storage
dump of a partition is taken following the next SQL error that occurs. And in DB2
Server for VM, if this sequence of commands is supplied in the Database Services
Utility command file, a virtual machine dump is taken following the next SQL
Chapter 9. Error Handling and Debugging
225
error that occurs. An SQL error is identified by an SQLCODE less than 0 or greater
than +100 received after the execution of an SQL statement. The resulting storage
dump reflects a register 15 value of hex 811 and is generated by a Database
Services Utility call to entry point ARISYSDA.
The command sequence necessary to initiate the storage dump is:
.DEBUG
SET ERRORMODE OFF;
Note:
v
.DEBUG must begin in command record column 1.
v An error condition occurs when the .DEBUG command is processed.
v A SET ERRORMODE OFF command must follow the .DEBUG command.
The storage dump is then taken after the next SQL error returned by the database
manager (in the SQLCA) after the execution of an SQL statement. Database
Services Utility processing continues after the dump is generated. If you want
subsequent storage dumps during the same execution of the Database Services
Utility, repeat the special command sequence described above.
Guidelines for DEBUG Storage Dump Analysis
Register 15 = X'811' Register 13 + 4 = ARIDSQLA register save area address for
last ARIPRDI CALL
ARIDSQLA save area address:
+ 12 = ARISYSDA (storage dump routine) return address within ARIDSQLA
+ 16 = ARISYSDA entry point
+ 32 = Address of SQLTIE (Register 3 contents at time of dump)
+ 36 = Contents of register 4 at time of dump.
Common processing area (CPA) address + X'0C' = the address of the special save
area containing the register 0 through register 15 contents saved by the Database
Services Utility before executing the dump request call to ARISYSDA.
226
Database Services Utility
Chapter 10. Improving Performance
This chapter adds to the information in previous chapters by raising issues you
should consider to get the best performance from the Database Services Utility.
Refer to the appropriate sections of earlier chapters if you want more basic
information.
Note: All of the following UNLOAD and RELOAD command references apply to
TABLE and DBSPACE unless PACKAGE is specified.
Nonrecoverable Storage Pool
With the database manager, you can define nonrecoverable storage pools. It
provides limited recovery functions for dbspaces that are assigned to
nonrecoverable storage pools. A problem may arise when you are inserting or
updating tables stored in nonrecoverable storage pools, especially when you use
the DATALOAD and RELOAD commands. See the DB2 Server for VSE System
Administration or DB2 Server for VM System Administration manual for information
about nonrecoverable storage pools.
Tape-File Support in DB2 Server for VM
Tape File Support Considerations
You use the CMS FILEDEF command and an optional CMS LABELDEF command
to define input or output tape files processed by the Database Services Utility.
Refer to the DB2 Server for VM System Administration manual for a complete
description of the tape file support.
Locking Considerations
When running the Database Services Utility with multiple user mode to load
(INSERT) or unload (SELECT) rows from a DB2 Server for VSE & VM database,
you may encounter lock escalation, particularly when using isolation level CS or
RR. Lock escalation reduces the ability to access the database and increases the
likelihood of deadlock conditions, which terminate processing. SQL LOCK
DBSPACE or LOCK TABLE statements override the database manager automatic
locking mechanism; they can be used to reduce deadlock conditions during
Database Services Utility processing. Using isolation level UR to unload rows from
a DB2 Server for VSE & VM database may also reduce lock escalation and
deadlock conditions; however, it is not recommended because it can cause data
integrity problems.
A user-issued SQL LOCK statement is useful only during multiple user mode
processing for table data in a public dbspace that is not defined with locking at the
dbspace level. A user-acquired database lock remains in effect until the end of the
logical unit of work in which it was issued. You cannot lock any database manager
catalog tables—regardless of the database authority you have. To lock an eligible
dbspace or table, you (the user connected to the database) must meet the
requirements in Table 14 on page 228:
227
Table 14. Requirements to Lock a Dbspace or Table
To Lock:
You Must
DBSPACE
v Be the owner of the dbspace
or
v Have DBA authority
Table
v Be the owner of the TABLE
or
v Have DBA authority
or
v Have SELECT privilege on the table
DATALOAD and RELOAD Locking Considerations
If you insert many rows into the database with a RELOAD command or a
DATALOAD command without the COMMITCOUNT option specified, consider
using the SQL LOCK DBSPACE statement to eliminate or reduce lock escalation.
An exclusive lock on the dbspace where the tables being loaded are defined does
not appreciably increase lock contention and reduces the likelihood of deadlock
with another user.
Note: An exclusive lock on a table being loaded does not prevent lock escalation
and is not recommended.
To exclusively lock a dbspace, issue the following command before the
DATALOAD or RELOAD command:
Format:
►► LOCK DBSPACE dbspace_name IN EXCLUSIVE MODE;
►◄
You can also avoid lock escalation during multiple user mode DATALOAD
processing by issuing a SET AUTOCOMMIT ON command before the DATALOAD
command and specifying a sufficiently low COMMITCOUNT value in the
DATALOAD INFILE subcommand. Use of DATALOAD COMMITCOUNT
processing reduces the likelihood of the locking required by DATALOAD
processing delaying other users accessing the table being loaded or other tables in
the same dbspace where the table being defined resides. If the target table is in a
dbspace defined with ROW level locking, a COMMITCOUNT value of
approximately 200 should be sufficiently low. If the dbspace is defined with PAGE
locking, the COMMITCOUNT value can be higher (1000, for example) and lock
escalation is still avoided. Do not arbitrarily set the COMMITCOUNT value too
low because frequent commit points increase DATALOAD run time.
SELECT, DATAUNLOAD, and UNLOAD Locking Considerations
If you are running the Database Services Utility with the isolation level setting of
repeatable read (the default Database Services Utility processing mode) and you
know that a particular SELECT, DATAUNLOAD, or UNLOAD operation is going
to access many rows from one or more tables in the database, lock escalation then
normally occurs. You should consider acquiring a SHARE lock on the table(s)
being accessed. If all the tables being accessed reside in the same dbspace, you
should consider acquiring a SHARE lock on the dbspace being accessed. This
action can reduce lock contention and the likelihood that a SELECT,
228
Database Services Utility
DATAUNLOAD, or UNLOAD causes a deadlock with another user. Other users
can modify other tables in the same dbspace where the table being accessed
resides.
To acquire a SHARE lock on a table or dbspace being accessed, issue the following
command before the SELECT, DATAUNLOAD, or UNLOAD statement:
Format:
►► LOCK TABLE table_name IN SHARE MODE;
►◄
or
►► LOCK DBSPACE dbspace_name IN SHARE MODE;
►◄
UNLOAD and RELOAD PACKAGE Considerations
To obtain the best performance when using the UNLOAD PACKAGE command
and the RELOAD PACKAGE command, consider doing the following:
v Unload or reload large numbers of packages in your system’s off-peak usage
time or with single user mode.
v If you are unloading or reloading packages with multiple user mode, use
blocking (by ensuring that the Database Services Utility was initialized with the
BLOCK option).
These actions improve performance by preventing interruptions by other users.
PROGRAM is a synonym for PACKAGE. Therefore, UNLOAD or RELOAD
PROGRAM, and UNLOAD or RELOAD PACKAGE are equivalent commands.
When unloading or reloading a modifiable package, an exclusive lock is held on
the catalog table SYSACCESS. This may cause a performance deterioration for
other users wanting to run the exclusively locked package.
See the DB2 Server for VSE & VM Database Administration manual for further
information on locking.
Update Statistics Considerations
If you suppress automatic statistics collection by specifying SET UPDATE
STATISTICS OFF in the (input) control file before issuing the DATALOAD
command, you must issue an UPDATE STATISTICS statement to collect statistics.
The UPDATE STATISTICS statement performs a dbspace scan, so it can be
time-consuming if the number of active data pages in that dbspace is large.
Consider suppressing statistics collection only if you know the statistics are not
going to change significantly (for example, a small amount of data is being added
to a large table). In this situation, you can postpone updating the statistics until
more substantial changes have occurred.
Reorganizing Indexes
The REORGANIZE INDEX command corrects index fragmentation and corrects the
skewing of index key values. REORGANIZE INDEX also revalidates an invalid
index.
Chapter 10. Improving Performance
229
REORGANIZE INDEX automatically updates statistics while the index is being
rebuilt. To calculate index statistics, the Database Services Utility has to have an
exact count of the pages in the dbspace which contain rows from the indexed table.
When the table is the only table in the dbspace, the database manager can find out
how many pages contain rows from the dbspace directory. In other words, the
number of used pages is the number of pages containing rows. If there is more
than one table in the dbspace, the database manager has to scan each page to
determine which ones are occupied by the indexed table. Hence, you get better
performance for the REORGANIZE INDEX command when the indexed table is
the only table in the dbspace. The DB2 Server for VSE & VM manuals recommend
allocating one table per dbspace if the tables are large.
To reorganize a valid index, the database manager uses an internal dbspace as
temporary storage to hold the keys of the index. The internal dbspace requirements
to perform a REORGANIZE INDEX operation are one third of that required to
perform the equivalent CREATE INDEX. If you do not have enough space, see the
DB2 Server for VSE System Administration or DB2 Server for VM System
Administration manual.
Packages that depend on an index are not invalidated when the index is
reorganized. Therefore, using the REORGANIZE INDEX command instead of
dropping and re-creating the index explicitly avoids the cost of the automatic
preprocessing that reoccurs the next time an invalidated package is run. This
benefit is realized whether the index you reorganize is valid or invalid.
Double-Byte Character Set
The database manager provides support for basic DBCS, while the Database
Services Utility provides support for extended DBCS.
Basic Support
The Database Services Utility supports the use of all DB2 Server for VSE & VM
data types, including the GRAPHIC data type for double-byte character set (DBCS)
data. The following general rules apply to DBCS data in Database Services Utility
input or output:
v DBCS data in SQL statements processed by the Database Services Utility can be
supplied as a constant with the format:
G’SOxx-xxSI’
where:
G' is the required constant prefix.
SO is a shift-out delimiter (hex 0E).
xx is a DBCS character in paired bytes.
SI is a shift-in delimiter (hex 0F).
' is the character terminating the constant.
Note: N' can be used as a synonym for G'
v DBCS data appearing in input data records is read into a graphic data type
column by the Database Services Utility DATALOAD command processing. The
DBCS data must be represented in paired bytes; the SO and SI delimiters are
optional.
v The paired bytes of a DBCS character cannot be split across Database Services
Utility (input) control file records, except for data records read by Database
Services Utility DATALOAD command processing. A DBCS data string, as well
230
Database Services Utility
as the paired bytes representing a DBCS character, can be split across (input)
control file data records processed using the DATALOAD continued record
support.
v GRAPHIC data appearing in Database Services Utility DATAUNLOAD
command output and GRAPHIC data in SELECT
command print records has the format:
SOxx-xxSI
where:
SO is a shift-out delimiter (hex 0E).
xx is a DBCS character in paired bytes.
SI is a shift-in delimiter (hex 0F).
The SO and SI delimiters result in blank print positions.
v A DBCS data string appearing in a Database Services Utility COMMENT
command must include shift-out and shift-in delimiters and cannot be continued
across (input) control file records.
Extended Support
In DB2 Server for VSE, if the SQLOPTION column value is DBCS and the VALUE
column value is no in the SYSOPTIONS catalog table, you can use the extended
DBCS support of the Database Services Utility. The DBCS option value will be
retrieved from the SQLGLOB file. The User DBCS SQLGLOB value, if it exists, will
be the default setting for DBCS.
The DB2 Server for VM Database Services Utility retrieves information about the
DBCS setting from the LASTING GLOBALV file on the database user machine.
Extended DBCS supports the following characteristics:
v Any SQL identifier or character string constant in a command can contain
DBCS/EBCDIC mixed data if the DBCS string and the shift-in or shift-out
delimiters reside on the same line.
v The SQL SELECT statement ensures that the data printed for CHAR,
VARCHAR, or long field columns contain matched pairs of shift-out and shift-in
delimiters for each data line. A long field is a field that is either a LONG
VARCHAR field, a LONG VARGRAPHIC field, a VARCHAR(n) field where n is
greater than 254 but less than or equal to 32767, or a VARGRAPHIC(n) field
where n is greater than 127 but less than or equal to 16383.
v The Database Services Utility assumes, when a command data record contains a
shift-out delimiter without a shift-in delimiter, that all trailing command data
positions within the command record contain DBCS data. The Database Services
Utility inserts a shift-in delimiter after the last assumed nonblank DBCS
character position before the record is written to the report or message file.
Omitting a shift-in or shift-out delimiter causes unreadable (input) control file
records in the report or message file. To suppress this display of unreadable
DBCS data, set the LIST parameter of the INFILE subcommand to no.
If an error occurs, in DB2 Server for VSE, during access of the SYSOPTIONS
catalog, or if an invalid DBCS option value is found, the Database Services Utility
continues processing the input control card file as if the extended DBCS feature
were not in effect.
Chapter 10. Improving Performance
231
Part 3. Appendixes
233
234
Database Services Utility
Appendix A. Sample Tables
The sample tables illustrated in this appendix are used in examples throughout the
library. These tables simulate a database created for use in organization or project
management applications. As a group, the tables include information that describes
employees, departments, projects, and activities. Figure 100 shows the relationships
among the tables. These relationships are established by referential constraints,
where a foreign key in the dependent table references a primary key in the parent
table. In the figure, the referential constraint is symbolized by lines joining the
keys; the arrowheads point from the primary key to the foreign key. Only those
columns named as foreign or primary keys are listed in the figure. All tables have
additional columns. You can easily review the contents of any table by executing
an SQL statement, such as SELECT * FROM SQLDBA.DEPARTMENT.
DEPARTMENT
PROJECT
DEPTNO
PROJNO
MGRNO
RESTRICT
DEPTNO
RESPEMP
RESTRICT
SET
SET NULL
SET
NULL
NULL
EMPLOYEE
ACTIVITY
ACTNO
EMPNO
WORKDEPT
RESTRICT
CASCADE
EMP_ACT
PROJ_ACT
EMPNO
PROJNO
PROJNO
RESTRICT
ACTNO
ACTNO
ACTSTDATE
EMSTDATE
Figure 100. Relationships among Tables in the Sample Application
DEPARTMENT Table
The DEPARTMENT table describes each department in the business and identifies
its manager and the department to which it reports. The table contents are shown
in Figure 101 on page 236; a description of the columns is shown in Figure 102.
235
DEPTNO
DEPTNAME
MGRNO
ADMRDEPT
A00
SPIFFY COMPUTER SERVICE DIV.
000010
A00
B01
PLANNING
000020
A00
C01
INFORMATION CENTER
000030
A00
D01
DEVELOPMENT CENTER
?
A00
E01
SUPPORT SERVICES
000050
A00
D11
MANUFACTURING SYSTEMS
000060
D01
D21
ADMINISTRATION SYSTEMS
000070
D01
E11
OPERATIONS
000090
E01
E21
SOFTWARE SUPPORT
000100
E01
Figure 101. DEPARTMENT Table Contents
Column Name
Description
DEPTNO
Department number, the primary key
DEPTNAME
A name describing the general activities of
the department
MGRNO
Employee number (EMPNO) of the
department manager
ADMRDEPT
Number of the department to which this
department reports; the department at the
highest level reports to itself
Figure 102. Columns of the DEPARTMENT Table
The DEPARTMENT table is created with:
CREATE TABLE DEPARTMENT
(DEPTNO
CHAR(3)
NOT NULL,
DEPTNAME VARCHAR(36)
NOT NULL,
MGRNO
CHAR(6)
,
ADMRDEPT
CHAR(3)
NOT NULL,
PRIMARY KEY (DEPTNO)
)
After the EMPLOYEE table has been created, a foreign key is added to the
DEPARTMENT table with this statement:
ALTER TABLE DEPARTMENT ADD
FOREIGN KEY R_EMPLY1 (MGRNO) REFERENCES EMPLOYEE
ON DELETE SET NULL
Relationship to Other Tables
DEPARTMENT is a parent of the EMPLOYEE and PROJECT tables.
The DEPARTMENT table is a dependent of the EMPLOYEE table; the MGRNO
column is the foreign key in the DEPARTMENT table and references EMPNO, the
primary key in the EMPLOYEE table.
236
Database Services Utility
EMPLOYEE Table
The EMPLOYEE table identifies all employees by an employee number and lists
basic personnel information. The table in Table 15 on page 238 shows the contents
of the EMPLOYEE table; Table 16 on page 240 shows a description of the columns.
Appendix A. Sample Tables
237
Table 15. EMPLOYEE Table Contents
MID
WORK
PHONE
ED
EMPNO
FIRSTNME
INIT
LASTNAME
DEPT
NO
HIREDATE
JOB
LEVEL
SEX
BIRTHDATE
SALARY
BONUS
COMM
char(6)
varchar(12)
char(1)
varchar(15)
char(3)
char(4)
date
char(8)
smallint
char(1)
date
dec(9,2)
dec(9,2)
dec(9,2)
not null
not null
not
not null
not null
null
000010
CHRISTINE
I
HAAS
A00
3978
1965-01-01
PRES
18
F
1933-08-24
52750
1000
4220
000020
MICHAEL
L
THOMPSON
B01
3476
1973-10-10
MANAGER
18
M
1948-02-02
41250
800
3300
000030
SALLY
A
KWAN
C01
4738
1975-04-05
MANAGER
20
F
1941-05-11
38250
800
3060
000050
JOHN
B
GEYER
E01
6789
1949-08-17
MANAGER
16
M
1925-09-15
40175
800
3214
000060
IRVING
F
STERN
D11
6423
1973-09-14
MANAGER
16
M
1945-07-07
32250
500
2580
000070
EVA
D
PULASKI
D21
7831
1980-09-30
MANAGER
16
F
1953-05-26
36170
700
2893
000090
EILEEN
W
HENDERSON
E11
5498
1970-08-15
MANAGER
16
F
1941-05-15
29750
600
2380
000100
THEODORE
Q
SPENSER
E21
0972
1980-06-19
MANAGER
14
M
1956-12-18
26150
500
2092
000110
VINCENZO
G
LUCCHESSI
A00
3490
1958-05-16
SALESREP
19
M
1929-11-05
46500
900
3720
000120
SEAN
O’CONNELL
A00
2167
1963-12-05
CLERK
14
M
1942-10-18
29250
600
2340
000130
DOLORES
M
QUINTANA
C01
4578
1971-07-28
ANALYST
16
F
1925-09-15
23800
500
1904
000140
HEATHER
A
NICHOLLS
C01
1793
1976-12-15
ANALYST
18
F
1946-01-19
28420
600
2274
000150
BRUCE
ADAMSON
D11
4510
1972-02-12
DESIGNER
16
M
1947-05-17
25280
500
2022
000160
ELIZABETH
R
PIANKA
D11
3782
1977-10-11
DESIGNER
17
F
1955-04-12
22250
400
1780
000170
MASATOSHI
J
YOSHIMURA
D11
2890
1978-09-15
DESIGNER
16
M
1951-01-05
24680
500
1974
000180
MARILYN
S
SCOUTTEN
D11
1682
1973-07-07
DESIGNER
17
F
1949-02-21
21340
500
1707
000190
JAMES
H
WALKER
D11
2986
1974-07-26
DESIGNER
16
M
1952-06-25
20450
400
1636
000200
DAVID
BROWN
D11
4501
1966-03-03
DESIGNER
16
M
1941-05-29
27740
600
2217
000210
WILLIAM
T
JONES
D11
0942
1979-04-11
DESIGNER
17
M
1953-02-23
18270
400
1462
000220
JENNIFER
K
LUTZ
D11
0672
1968-08-29
DESIGNER
18
F
1948-03-19
29840
600
2387
000230
JAMES
J
JEFFERSON
D21
2094
1966-11-21
CLERK
14
M
1935-05-30
22180
400
1774
000240
SALVATORE
M
MARINO
D21
3780
1979-12-05
CLERK
17
M
1954-03-31
28760
600
2301
000250
DANIEL
S
SMITH
D21
0961
1969-10-30
CLERK
15
M
1939-11-12
19180
400
1534
000260
SYBIL
P
JOHNSON
D21
8953
1975-09-11
CLERK
16
F
1936-10-05
17250
300
1380
000270
MARIA
L
PEREZ
D21
9001
1980-09-30
CLERK
15
F
1953-05-26
27380
500
2190
Table 15. EMPLOYEE Table Contents (continued)
MID
WORK
PHONE
ED
EMPNO
FIRSTNME
INIT
LASTNAME
DEPT
NO
HIREDATE
JOB
LEVEL
SEX
BIRTHDATE
SALARY
BONUS
COMM
000280
ETHEL
R
SCHNEIDER
E11
8997
1967-03-24
OPERATOR
17
F
1936-03-28
26250
500
2100
000290
JOHN
R
PARKER
E11
4502
1980-05-30
OPERATOR
12
M
1946-07-09
15340
300
1227
000300
PHILIP
X
SMITH
E11
2095
1972-06-19
OPERATOR
14
M
1936-10-27
17750
400
1420
000310
MAUDE
F
SETRIGHT
E11
3332
1964-09-12
OPERATOR
12
F
1931-04-21
15900
300
1272
000320
RAMLAL
V
MEHTA
E21
9990
1965-07-07
FIELDREP
16
M
1932-08-11
19950
400
1596
000330
WING
LEE
E21
2103
1976-02-23
FIELDREP
14
M
1941-07-18
25370
500
2030
000340
JASON
R
GOUNOT
E21
5698
1947-05-05
FIELDREP
16
M
1926-05-17
23840
500
1907
Table 16. Columns of the EMPLOYEE Table
Column Name
Description
EMPNO
Employee number (the primary key)
FIRSTNME
First name of the employee
MIDINIT
Middle initial of the employee
LASTNAME
Last name of the employee
WORKDEPT
Number of department in which the
employee works
PHONENO
Employee telephone number
HIREDATE
Date of hire
JOB
Job held by the employee
EDLEVEL
Number of years of formal education
SEX
Sex of the employee (M or F)
BIRTHDATE
Date of birth
SALARY
Yearly salary
BONUS
Yearly bonus
COMM
Yearly commission
The EMPLOYEE table has a foreign key referencing the primary key in the
DEPARTMENT table. The DEPARTMENT table must, therefore, be created first.
The EMPLOYEE table is then created with:
CREATE TABLE EMPLOYEE
(EMPNO
CHAR(6)
NOT NULL,
FIRSTNME VARCHAR(12)
NOT NULL,
MIDINIT CHAR(1)
NOT NULL,
LASTNAME VARCHAR(15)
NOT NULL,
WORKDEPT CHAR(3)
,
PHONENO CHAR(4)
,
HIREDATE DATE
,
JOB
CHAR(8)
,
EDLEVEL
SMALLINT
NOT NULL,
SEX
CHAR(1)
,
BIRTHDATE DATE
,
SALARY
DECIMAL(9,2)
,
BONUS
DECIMAL(9,2)
,
COMM
DECIMAL(9,2)
,
PRIMARY KEY (EMPNO)
,
FOREIGN KEY R_DEPT1 (WORKDEPT) REFERENCES DEPARTMENT
ON DELETE SET NULL
)
Relationship to Other Tables
The EMPLOYEE table is a parent of the DEPARTMENT table, the PROJECT table,
and the EMP_ACT table.
The EMPLOYEE table is a dependent of the DEPARTMENT table; the foreign key
on the WORKDEPT column in the EMPLOYEE table references the primary key on
the DEPTNO column in the DEPARTMENT table.
240
Database Services Utility
PROJECT Table
The PROJECT table describes each project that the business is currently
undertaking. Data contained in each row includes the project number, name,
person responsible, and schedule dates as shown in Table 17; Table 18
describes the
columns.
Table 17. PROJECT Table Contents
PROJNO
PROJNAME
DEPTNO
RESPEMP
PRSTAFF
PRSTDATE
PRENDATE
MAJPROJ
AD3100
ADMIN SERVICES
D01
000010
6.5
1982-01-01
1983-02-01
?
AD3110
GENERAL ADMIN
D21
000070
6
1982-01-01
1983-02-01
AD3100
SYSTEMS
AD3111
PAYROLL
D21
000230
2
1982-01-01
1983-02-01
AD3110
PROGRAMMING
AD3112
PERSONNEL
D21
000250
1
1982-01-01
1983-02-01
AD3110
PROGRAMMING
AD3113
ACCOUNT
D21
000270
2
1982-01-01
1983-02-01
AD3110
PROGRAMMING
IF1000
QUERY SERVICES
C01
000030
2
1982-01-01
1983-02-01
?
IF2000
USER EDUCATION
C01
000030
1
1982-01-01
1983-02-01
?
MA2100
WELD LINE
D01
000010
12
1982-01-01
1983-02-01
?
AUTOMATION
MA2110
WL
D11
000060
9
1982-01-01
1983-02-01
MA2100
PROGRAMMING
MA2111
W L PROGRAM
D11
000220
2
1982-01-01
1982-12-01
MA2110
DESIGN
MA2112
W L ROBOT
D11
000150
3
1982-01-01
1982-12-01
MA2110
DESIGN
MA2113
W L PROD CONT
D11
000160
3
1982-02-15
1982-12-01
MA2110
PROGS
OP1000
OPERATION
E01
000050
6
1982-01-01
1983-02-01
?
SUPPORT
OP1010
OPERATION
E11
000090
5
1982-01-01
1983-02-01
OP1000
OP2000
GEN SYSTEMS
E01
000050
5
1982-01-01
1983-02-01
?
SERVICES
OP2010
SYSTEMS SUPPORT
E21
000100
4
1982-01-01
1983-02-01
OP2000
OP2011
SCP SYSTEMS
E21
000320
1
1982-01-01
1983-02-01
OP2010
SUPPORT
OP2012
APPLICATIONS
E21
000330
1
1982-01-01
1983-02-01
OP2010
SUPPORT
OP2013
DB/DC SUPPORT
E21
000340
1
1982-01-01
1983-02-01
OP2010
PL2100
WELD LINE
B01
000020
1
1982-01-01
1982-09-15
MA2100
PLANNING
Table 18. Columns of the PROJECT Table
Column Name
Description
PROJNO
Project number (the primary key)
PROJNAME
Project name
Appendix A. Sample Tables
241
Table 18. Columns of the PROJECT Table (continued)
Column Name
Description
DEPTNO
Number of department responsible for the
project
RESPEMP
Number of employee responsible for the
project
PRSTAFF
Estimated mean project staffing (mean
number of persons) needed between
PRSTDATE and PRENDATE to achieve the
whole project, including any subprojects
PRSTDATE
Estimated project start date
PRENDATE
Estimated project end date
MAJPROJ
Number of any major project of which the
subject project may be a part
The PROJECT table has foreign keys referencing DEPARTMENT and EMPLOYEE.
The EMPLOYEE and DEPARTMENT tables must be created before the PROJECT
table. Once EMPLOYEE and DEPARTMENT are created, the following statement
creates the PROJECT table:
CREATE TABLE PROJECT
(PROJNO
CHAR(6)
NOT NULL,
PROJNAME VARCHAR(24)
NOT NULL,
DEPTNO
CHAR(3)
NOT NULL,
RESPEMP
CHAR(6)
,
PRSTAFF
DECIMAL(5,2)
,
PRSTDATE
DATE
,
PRENDATE
DATE
,
MAJPROJ
CHAR(6)
,
PRIMARY KEY (PROJNO)
,
FOREIGN KEY R_DEPT2 (DEPTNO) REFERENCES DEPARTMENT
ON DELETE RESTRICT
,
FOREIGN KEY R_EMPLY2 (RESPEMP) REFERENCES EMPLOYEE
ON DELETE SET NULL
)
Relationship to Other Tables
PROJECT is a parent of the PROJ_ACT table.
PROJECT is a dependent of:
v The DEPARTMENT table; the foreign key on the DEPTNO column in PROJECT
references the primary key in the DEPARTMENT table.
v The EMPLOYEE table; the foreign key on the RESPEMP column in PROJECT
references the primary key in the EMPLOYEE table.
ACTIVITY Table
The ACTIVITY tables describes the activities that can be performed during a
project. The table acts as a master list of possible activities, identifying the activity
number, and providing a description of the activity. Figure 103 on page 243 shows
table contents; Figure 104 on page 243 shows a description of the columns.
242
Database Services Utility
ACTNO
ACTKWD
ACTDESC
160
ADMDB
Adm databases
170
ADMDC
Adm data comm
90
ADMQS
Adm query system
150
ADMSYS
Adm operating sys
70
CODE
Code programs
110
COURSE
Develop courses
30
DEFINE
Define specs
180
DOC
Document
20
ECOST
Estimate cost
40
LEADPR
Lead program/design
60
LOGIC
Describe logic
140
MAINT
Maint software sys
10
MANAGE
Manage/advise
130
OPERAT
Oper computer sys
50
SPECS
Write specs
120
STAFF
Pers and staffing
100
TEACH
Teach classes
80
TEST
Test programs
Figure 103. ACTIVITY Table Contents
Column Name
Description
ACTNO
Activity number (the primary key)
ACTKWD
Activity keyword (up to six characters)
ACTDESC
Activity description
Figure 104. Columns of the ACTIVITY Table
The ACTIVITY table is created with:
CREATE TABLE ACTIVITY
(ACTNO
SMALLINT
NOT NULL,
ACTKWD CHAR(6)
NOT NULL,
ACTDESC VARCHAR(20)
NOT NULL,
PRIMARY KEY (ACTNO)
)
Relationship to Other Tables
ACTIVITY is a parent of the PROJ_ACT table.
PROJ_ACT Table
The PROJ_ACT table lists the activities performed for each project. The table
contains information on the start and completion dates of the project activity as
well as staffing requirements as shown in Figure 105 on page 244. Figure 106 on
page 245 shows a description of the columns.
Appendix A. Sample Tables
243
PROJNO
ACTNO
ACSTAFF
ACSTDATE
ACENDATE
AD3100
10
0.50
1982-01-01
1982-07-01
AD3110
10
1.00
1982-01-01
1983-01-01
AD3111
60
0.80
1982-01-01
1982-04-15
AD3111
70
1.50
1982-02-15
1982-10-15
AD3111
80
1.25
1982-04-15
1983-01-15
AD3111
180
1.00
1982-10-15
1983-01-15
AD3112
60
0.75
1982-01-01
1982-05-15
AD3112
60
0.75
1982-12-01
1983-01-01
AD3112
70
0.75
1982-01-01
1982-10-15
AD3112
80
0.35
1982-08-15
1982-12-01
AD3112
180
0.50
1982-08-15
1983-01-01
AD3113
60
0.75
1982-03-01
1982-10-15
AD3113
70
1.25
1982-06-01
1982-12-15
AD3113
80
1.75
1982-01-01
1982-04-15
AD3113
180
0.75
1982-03-01
1982-07-01
IF1000
10
0.50
1982-01-01
1983-01-01
IF1000
90
1.00
1982-01-01
1983-01-01
IF1000
100
0.50
1982-01-01
1983-01-01
IF2000
10
0.50
1982-01-01
1983-01-01
IF2000
100
0.75
1982-01-01
1982-07-01
IF2000
110
0.50
1982-03-01
1982-07-01
IF2000
110
0.50
1982-10-01
1983-01-01
MA2100
10
0.50
1982-01-01
1982-11-01
MA2100
20
1.00
1982-01-01
1982-03-01
MA2110
10
1.00
1982-01-01
1983-02-01
MA2111
40
1.00
1982-01-01
1983-02-01
MA2111
50
1.00
1982-01-01
1092-06-01
MA2111
60
1.00
1982-06-01
1983-02-01
MA2112
60
2.00
1982-01-01
1982-07-01
MA2112
70
1.50
1983-02-01
1983-02-01
Figure 105. Partial Contents of PROJ_ACT Table
244
Database Services Utility
Column Name
Description
PROJNO
Project number
ACTNO
Activity number
ACSTAFF
Estimated mean number of employees
needed to staff the activity
ACSTDATE
Estimated activity start date
ACENDATE
Estimated activity completion date
Figure 106. Columns of the PROJ_ACT Table
The table has a composite primary key and was created with:
CREATE TABLE PROJ_ACT
(PROJNO
CHAR(6)
NOT NULL,
ACTNO
SMALLINT
NOT NULL,
ACSTAFF
DECIMAL(5,2)
,
ACSTDATE
DATE
NOT NULL,
ACENDATE
DATE
,
PRIMARY KEY (PROJNO, ACTNO, ACTSTDATE),
FOREIGN KEY R_PROJ2 (PROJNO) REFERENCES PROJECT
ON DELETE RESTRICT,
FOREIGN KEY R_ACTIV (ACTNO) REFERENCE ACTIVITY
ON DELETE RESTRICT)
Relationship to Other Tables
PROJ_ACT is a parent of the EMP_ACT table.
It is a dependent of:
v The ACTIVITY table; the foreign key on ACTNO in the PROJ_ACT table
references the primary key, ACTNO, in the ACTIVITY table.
v The PROJECT table; the foreign key on PROJNO in the PROJ_ACT table
references the primary key, PROJNO, in the PROJECT table.
EMP_ACT Table
The EMP_ACT table identifies the employee performing each activity listed for
each project. The table in Figure 107 on page 246 shows some of the rows in this
table. Figure 108 on page 246 shows a description of the columns.
Appendix A. Sample Tables
245
EMPNO
PROJNO
ACTNO
EMPTIME
EMSTDATE
EMENDATE
000130
IF1000
90
1.00
1982-01-01
1982-10-01
000130
IF1000
100
.50
1982-10-01
1983-01-01
000140
IF1000
90
.50
1982-10-01
1983-01-01
000030
IF1000
10
.50
1982-06-01
1983-01-01
000030
IF2000
10
.50
1982-01-01
1983-01-01
000140
IF2000
100
1.00
1982-01-01
1982-03-01
000140
IF2000
100
.50
1982-03-01
1982-07-01
000140
IF2000
110
.50
1982-03-01
1982-07-01
000140
IF2000
110
.50
1982-10-01
1983-01-01
000010
MA2100
10
.50
1982-01-01
1982-11-01
000110
MA2100
20
1.00
1982-01-01
1982-03-01
000020
PL2100
30
1.00
1982-01-01
1982-09-15
000010
MA2110
10
1.00
1982-01-01
1983-02-01
000220
MA2111
40
1.00
1982-01-01
1983-02-01
Figure 107. Partial Contents of EMP_ACT Table
Column Name
Description
EMPNO
Employee number
PROJNO
Project number of the project to which the
employee is assigned
ACTNO
Activity number within a project to which
an employee is assigned
EMPTIME
A proportion of the employee’s full time
(between 0.00 and 1.00) to be spent on the
project from EMSTDATE to EMENDATE
EMSTDATE
Date the activity starts
EMENDATE
Completion date of the activity
Figure 108. Columns of the EMP_ACT Table
Since the table has foreign keys referencing EMPLOYEE and PROJ_ACT, those
tables must be created first.
This table was created with:
CREATE TABLE EMP_ACT
(EMPNO
CHAR(6)
NOT NULL,
PROJNO
CHAR(6)
NOT NULL,
ACTNO
SMALLINT
NOT NULL,
EMPTIME
DECIMAL(5,2)
,
EMSTDATE
DATE
,
EMENDATE
DATE
,
FOREIGN KEY R_PROACT (PROJNO,ACTNO,EMSTDATE)
246
Database Services Utility
REFERENCES PROJ_ACT ON DELETE RESTRICT,
FOREIGN KEY R_EMPLY3 (EMPNO) REFERENCES EMPLOYEE
ON DELETE CASCADE
)
Relationship to Other Tables
The EMP_ACT table is a dependent of:
v The EMPLOYEE table; the foreign key on EMPNO in the EMP_ACT table
references the primary key, EMPNO, in the EMPLOYEE table.
v The PROJ_ACT table; the foreign key on the set of PROJNO, ACTNO,
EMSTDATE in the EMP_ACT table references the primary key, PROJNO,
ACTNO, ACSTDATE, in the PROJ_ACT table.
IN_TRAY Table
The IN_TRAY table contains a person’s note log. The table contents are shown in
Figure 109; a description of the columns is shown in Figure 110.
RECEIVED
SOURCE
SUBJECT
NOTE_TEXT
1965-01-01-07.00.00
SQLDBA
English
Here is a note from
your DBA.
Figure 109. IN_TRAY Table Contents
Column Name
Description
RECEIVED
Date and time note was received
SOURCE
User id of person sending note
SUBJECT
Brief description
NOTE_TEXT
The text of the note
Figure 110. Columns of the IN_TRAY Table
This table was created with:
CREATE TABLE IN_TRAY
(RECEIVED
TIMESTAMP NOT NULL,
SOURCE
CHAR(8)
NOT NULL,
SUBJECT
CHAR(64)
,
NOTE_TEXT
VARCHAR(4000)
)
CL_SCHED Table
The CL_SCHED table describes a classroom schedule. The table contents are
shown in Figure 111; a description of the columns is shown in Figure 112 on page
248.
CLASS_CODE
DAY
STARTING
ENDING
101:KAR
2
14.10.00
16.10.00
202:LMM
3
14.40.00
16.40.00
303:RAR
4
09.00.00
09.40.00
Figure 111. CL_SCHED Table Contents
Appendix A. Sample Tables
247
CL_SCHED Table
Column Name
Description
CLASS_CODE
Class Code (room:teacher)
DAY
Day number of four day schedule
STARTING
Class start time
ENDING
Class end time
Figure 112. Columns of the CL_SCHED Table
This table was created with:
CREATE TABLE CL_SCHED
(CLASS_CODE
CHAR(7) NOT NULL,
DAY
SMALLINT NOT NULL,
STARTING
TIME
NOT NULL,
ENDING
TIME
NOT NULL)
Note: For more information about data types, refer to the DB2 Server for VSE &
VM Application Programming manual.
248
Database Services Utility
Appendix B. FILEDEF Command Syntax and Notes
Whenever you run the Database Services Utility under CMS, first identify the files
to CMS with the FILEDEF command.
Note: Use the SQLDBSU EXEC, which generates standard FILEDEF statements, to
define the control and message files. Create a FILEDEF statement for all
additional input and output files.
The FILEDEF command in CMS performs the same functions as the data definition
(DD) record in OS job control language (JCL). When you enter a FILEDEF
command, specify:
v A ddname
v The device type
v A file identification if the device type is DISK
v Options (as required).
The format of the FILEDEF command is:
Format:
►► FIledef ddname
Terminal
►◄
PRinter
( Options
Reader
)
DISK fn_ft_fm
TAPn
ddname (data definition name)
identifies the name used in your Database Services Utility command that refers
to the input or output file.
Terminal
your workstation
PRinter
the spooled printer available to you
Reader
the spooled reader available to you
DISK fn ft fm
virtual direct access storage device (DASD) CMS file
TAPn
magnetic tape drive, where n can be 1, 2, 3, or 4, representing virtual units 181,
182, 183, and 184, respectively.
Options
to avoid error messages, specify only those options that are valid for a
particular device. Table 19 on page 251 shows valid options for each device
type.
The following diagram illustrates the FILEDEF options available when the device
is a workstation or a tape drive:
249
Format:
►►
(
►
PERM
CHANGE
RECFM
LRECL nnnn
NOCHANGE
F
FB
V
VB
FBS
VBS
► BLOCK nnnn
►◄
(1)
(2)
)
UPCASE
7TRACK
(1)
(2)
LOWCASE
9TRACK
Notes:
1
Terminal only.
2
Tape only.
The following diagram illustrates the FILEDEF options available when the device
specified is DISK:
Format:
►►
(
►
PERM
CHANGE
RECFM
F
LRECL nnnn
NOCHANGE
FB
BLOCK nnnn
V
VB
FBS
VBS
►
►◄
XTENT50
DISP MOD
MEMBER mbrname
CONCAT
DSORG
PS
)
XTENT nnnn
PO
DA
250
Database Services Utility
Table 19. FILEDEF Options and Parameters
OPTION NAME
DISK
READER/PRINTER
TAPn
TERMINAL
BLOCK, BLOCKSIZE
X
X
X
X
CHANGE,
X
X
X
X
NOCHANGE
CONCAT
X
DEN
X
DISP MOD
X
X
DSORG
X
LOWERCASE/
X
UPCASE
LRECL
X
X
X
X
MEMBER
X
PERM
X
X
X
X
RECFM
X
X
X
X
7TRACK/9TRACK
X
Some guidelines for entering FILEDEF specifications are given below.
Specifying ddname
If the FILEDEF command is issued for a program input or output file, the ddname
must be the same as the ddname or file name specified for the file in the source
program. For example, you have an Assembler language source program that
contains the line:
INFILE DCB ddname=INPUTDD,MACRF=GL,DSORG=PS,RECFM=F,LRECL=80
For a particular execution of this program, you want to use as your input file a
CMS file on your A-disk that is named MYINPUT FILE. You must issue a FILEDEF
like this before executing the program:
FILEDEF INPUTDD DISK MYINPUT FILE A1
CMS FILEDEF command information for RELOAD processing should be identical
to the information in the FILEDEF command used when the file was created by the
package’s UNLOAD command processing.
If the input data file was created by DATAUNLOAD processing, then the CMS
FILEDEF command that defines the DATALOAD input data file should be
identical to the information in the FILEDEF command used when the file was
created by DATAUNLOAD processing.
Specifying Device Type
For input files, the device type you enter on the FILEDEF command indicates the
device from which you want records read. It can be DISK, TERMINAL, READER
(for input from real cards or virtual cards), or TAPn (for tape). Using the above
example, if your input file is to be read from your virtual card reader, the FILEDEF
command might be as follows:
FILEDEF INPUTDD READER
Appendix B. FILEDEF Command Syntax and Notes
251
Or, if you were reading from a tape attached to your virtual machine at virtual
address 181 (TAP1):
FILEDEF INPUTDD TAP1
For output files, the device you specify can be DISK, PRINTER, TAPn (tape), or
TERMINAL.
Entering File Identifiers
If you are using a CMS disk file for your input or output, specify:
FILEDEF ddname DISK filename filetype filemode
Note: If an asterisk (*) is used for the file mode of an output file, the results are
unpredictable. The file mode field is optional; your A-disk is the default
assumed.
If you want an output file to be constructed in OS simulated data set format, you
must specify the file mode number as 4. For example, a program contains a
dbspace for an output file with the ddname OUTPUTDD, and you are using it to
create a CMS file named DTABSE OUTPUT on your B-disk:
FILEDEF OUTPUTDD DISK DTABSE OUTPUT B4
If you enter only the ddname and device type on the FILEDEF command, such as:
FILEDEF ddname DISK
where ddname is the name of the output file you assigned as the parameter of the
FILEDEF command, you have then created a file on your A-disk. For example, if
you assign a ddname of OSCAR to an output file and do not issue a FILEDEF
command before you execute the program, the CMS file FILE OSCAR A1 is created
when you execute the program.
Specifying CMS Tape Label Processing
You can use the label operands on the FILEDEF command to indicate that CMS
tape label processing is not desired. (This is the default.) If CMS tape label
processing is desired, you can use the label operands on the FILEDEF command to
indicate the types of labels on your tape.
Specifying Options
The FILEDEF command has many options; those mentioned below are a sampling
only. For complete descriptions of all the options of the FILEDEF command, see
the VM/ESA: CMS Command Reference.
Note: If a SET ERRORMODE CONTINUE command is in effect during Database
Services Utility command processing, which requires tape file operation
involving multifile volume, the use of the LEAVE option in the FILEDEF
may cause a tape positioning error. If a Database Services Utility command
processing involving tape file operation fails, the subsequent command
processing requiring access to the same tape will get a tape file open error.
This error results from the wrong tape positioning caused by the use of the
LEAVE option in the FILEDEF.
BLOCK, LRECL, RECFM, DSORG
If you are using the FILEDEF command to relate a data control block (DCB) in a
program to an input or output file, you need to supply some of the file format
information, such as the record length and block size, on the FILEDEF command
line. For example, you have coded a DCB macro for an output file as follows:
252
Database Services Utility
OUTFILE DCB ddname=OUT,MACRF=PM,DSORG=PS
When you are issuing a FILEDEF for this ddname, you must specify the format of
the file. To create an output file on disk, blocked in OS-simulated data set format,
you could issue:
FILEDEF OUT DISK fn ft A (RECFM FB LRECL 80 BLOCK 1600
Note the following command-specific information for the RECFM, BLOCK, and
LRECL parameters:
v
DATALOAD
If the DATALOAD input data file contains records with more than 32 760
positions of data, you can do one of the following:
1. Use VS or VBS records. Specify (as options) only the RECFM and block size
(BLOCK or BLKSIZE) parameters in the FILEDEF command defining the
data file. (The LRECL specification does not apply and will be overridden if
specified.)
2. Use F or V records if you are using CMS 15 or later, and the DATALOAD
input data file contains records with less than 65 536 positions of data.
v
UNLOAD DBSPACE and UNLOAD TABLE
You should always specify a record format (RECFM) of VBS for UNLOAD
processing. UNLOAD processing changes the record format to U if the
system-required logical record length is greater than the specified block size
(BLOCK) value minus 4. Otherwise, UNLOAD processing changes the record
format to VB. See below for more information about undefined (U) record
format usage.
A block size greater than 8 244 is recommended for tape output files created by
UNLOAD processing.
v
UNDEFINED RECORD FORMAT and UNLOAD DBSPACE / UNLOAD TABLE
/ DATAUNLOAD
UNLOAD processing changes the RECFM=VBS, specified on the FILEDEF
command, to either VB or U. It changes the RECFM to VB if the record length
required to unload the data will fit within the specified or default block size
(minus 4), so that there are no spanned records. However, when the record
length exceeds the block size (minus 4), UNLOAD processing produces spanned
records (records that span more than one block). Likewise, DATAUNLOAD
processing changes the RECFM=VS or RECFM=VBS, specified on the FILEDEF
command, to U, producing spanned records. Note, however, that this VBS-like
or VS-like file may NOT be acceptable to other programs that use the OS VBS
access method (CMS OS simulation on OS/390 and VSE/ESA), because the
logical record length may exceed the OS-defined maximum of 65 535. Each table
row is written out as a single spanned record, and tables with very long rows
(especially containing long fields) can produce a record exceeding 65 535
positions. This is why UNLOAD / DATAUNLOAD changes VBS or VS to U,
since CMS OS simulation cannot handle records longer than 65 535. In that case,
the spanning of records is done by the DBS Utility and not by CMS OS
simulation.
v
RELOAD DBSPACE and RELOAD TABLE
You should always specify a record format (RECFM) of VBS for RELOAD
processing. If a RECFM value other than VBS, or an LRECL value, is specified it
is ignored. RELOAD processing changes the record format to VB. Always use
the same RECFM and BLKSIZE values on the FILEDEF for a RELOAD as were
used on the FILEDEF for the corresponding UNLOAD.
v
UNLOAD and RELOAD PROGRAM
Appendix B. FILEDEF Command Syntax and Notes
253
If you specify a RECFM other than FB, or specify an LRECL value, the value is
ignored.
v SCHEMA
If you specify a RECFM other than FB, or specify an LRECL value, the value is
ignored.
PERM
Usually, when you execute one of the language processors, all existing file
definitions are cleared. If the development of a program requires you to recompile
and reexecute it frequently, you might want to use the PERM option when you
issue file definitions for your input and output files. For example:
CP SPOOL PUNCH TO *
FILEDEF INDD DISK TEST FILE A1 (LRECL 80 PERM
FILEDEF OUTDD PUNCH (LRECL 80 PERM
In this example, because you spooled your virtual punch to your own virtual card
reader, output files are placed in your virtual reader. You can either read or delete
them.
All file definitions issued with the PERM option stay in effect until you log off;
therefore, specifically clear those definitions or redefine them:
FILEDEF INDD CLEAR
FILEDEF OUTDD TAP1 (LRECL 80
In the above example, the definition for INDD is cleared; OUTDD is redefined as a
tape file.
When you issue the command:
FILEDEF * CLEAR
all file definitions are cleared, except those you enter with the PERM option.
Note: When a program ends abnormally, or when you issue the HX immediate
command, all file definitions are cleared, including those entered with the
PERM option.
DISP MOD
Suppose you issue a FILEDEF command for an output file and assign it a CMS file
identifier that is identical to that of an existing CMS file; then, when anything is
written to that ddname, the existing file is replaced by the new output file. If you
want, instead, to have new records added to the end of the existing file, you can
use the DISP MOD option as follows:
FILEDEF ddname DISK fn ft fm (DISP MOD
Note: To see the file characteristics used in the Database Services Utility’s
processing, look at message ARI08681 in the message file.
254
Database Services Utility
Notices
IBM may not offer the products, services, or features discussed in this document in
all countries. Consult your local IBM representative for information on the
products and services currently available in your area. Any reference to an IBM
product, program, or service is not intended to state or imply that only that IBM
product, program, or service may be used. Any functionally equivalent product,
program, or service that does not infringe any IBM intellectual property right may
be used instead. However, it is the user’s responsibility to evaluate and verify the
operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter
described in this document. The furnishing of this document does not give you
any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10594-1785
U.S.A.
For license inquiries regarding double-byte (DBCS) information, contact the IBM
Intellectual Property Department in your country or send inquiries, in writing, to:
IBM World Trade Asia Corporation
Licensing
2-31 Roppongi 3-chome, Minato-ku
Tokyo 106, Japan
The following paragraph does not apply to the United Kingdom or any other
country where such provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS
PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS
FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or
implied warranties in certain transactions, therefore, this statement may not apply
to you.
This information could include technical inaccuracies or typographical errors.
Changes are periodically made to the information herein; these changes will be
incorporated in new editions of the publication. IBM may make improvements
and/or changes in the product(s) and/or the program(s) described in this
publication at any time without notice.
Any references in this information to non-IBM Web sites are provided for
convenience only and do not in any manner serve as an endorsement of those Web
sites. The materials at those Web sites are not part of the materials for this IBM
product and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it
believes appropriate without incurring any obligation to you.
255
Licensees of this program who wish to have information about it for the purpose
of enabling: (i) the exchange of information between independently created
programs and other programs (including this one) and (ii) the mutual use of the
information which has been exchanged, should contact:
IBM Corporation
Mail Station P300
522 South Road
Poughkeepsie, NY 12601-5400
U.S.A
Such information may be available, subject to appropriate terms and conditions,
including in some cases, payment of a fee.
The licensed program described in this information and all licensed material
available for it are provided by IBM under terms of the IBM Customer Agreement,
IBM International Program License Agreement, or any equivalent agreement
between us.
Any performance data contained herein was determined in a controlled
environment. Therefore, the results obtained in other operating environments may
vary significantly. Some measurements may have been made on development-level
systems and there is no guarantee that these measurements will be the same on
generally available systems. Furthermore, some measurement may have been
estimated through extrapolation. Actual results may vary. Users of this document
should verify the applicable data for their specific environment.
Information concerning non-IBM products was obtained from the suppliers of
those products, their published announcements, or other publicly available sources.
IBM has not tested those products and cannot confirm the accuracy of
performance, compatibility, or any other claims related to non-IBM products.
Questions on the capabilities of non-IBM products should be addressed to the
suppliers of those products.
All statements regarding IBM’s future direction or intent are subject to change or
withdrawal without notice, and represent goals and objectives only.
This information may contain examples of data and reports used in daily business
operations. To illustrate them as completely as possible, the examples include the
names of individuals, companies, brands, and products. All of these names are
fictitious and any similarity to the names and addresses used by an actual business
enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information may contain sample application programs in source language,
which illustrates programming techniques on various operating platforms. You
may copy, modify, and distribute these sample programs in any form without
payment to IBM, for the purposes of developing, using, marketing, or distributing
application programs conforming to the application programming interface for the
operating platform for which the sample programs are written. These examples
have not been thoroughly tested under all conditions. IBM, therefore, cannot
guarantee or imply reliability, serviceability, or function of these programs.
256
Database Services Utility
Programming Interface Information
This book documents intended Programming Interfaces that allow the customer to
write programs to obtain services of DB2 Server for VSE & VM.
Trademarks
The following terms are trademarks of International Business Machines
Corporation in the United States, or other countries, or both:
APL2
C/370
CICS
CICS/ESA
CICS/VSE
DATABASE 2
DataPropagator
DB2
DFSMS/VM
Distributed Relational Database Architecture
DRDA
IBM
MVS
OS/2
QMF
SQL/DS
System/370
VM/ESA
VSE/ESA
VTAM
Microsoft, Windows, Windows NT, and the Windows logo are trademarks of
Microsoft Corporation in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks
of others.
Notices
257
DB2 Server for VSE & VM
Data Restore Guide
Version 7 Release 3
SC09-2991-01
Contents
About This Book
vii
Chapter 4. Installing Data Restore . . .
33
Who Should Use This Book
vii
Supported Environments
33
How to Use This Book
vii
Complete the Preinstallation Checklist
33
Organization
vii
Section 1. Installing Under VM
34
Prerequisites
. viii
Step 1. Define the VM Resources
34
Terminology
viii
Step 2. Define the Database Manager Resources
34
National Language Support
x
Step 3. Complete the VM Installation Process .
35
Co-existence
x
Starting Data Restore in a VM Environment .
38
Section 2. Installing Under VSE
38
Summary of Changes
xiii
Placement of Data Restore Distribution Libraries
38
Machine-Readable Material
38
Summary of Changes for DB2 Version 7 Release 3
xiii
IBM-Supplied Installation Aids
39
|
Enhancements, New Functions, and New
Step 1. Define the Library to be Installed . .
40
|
Capabilities
. xiii
Step 2. Restore the Data Restore Distribution
Reliability, Availability, and Serviceability
Library
42
Improvements
xiv
Step 3. Install Data Restore
42
Starting Data Restore in a VSE Environment .
50
Part 1. Getting Started
1
Section 3. Installing Under VSE Guest Sharing. .
50
Step 1. Define the VM Resources
50
Chapter 1. How to Recover from Failures 3
Step 2. Define the Database Manager Resources
51
Failures to be Considered
3
Step 3. Complete the VM Installation Process .
52
Power or Hardware Failure
3
Step 4. Define the VSE Resources
54
DASD Failure
3
Step 5. Complete the VSE Installation Process .
57
Operating System Abend
5
Complex Environment Considerations
62
DB2 Failure
5
Installation Considerations
62
Application or User Logic Error
5
A Complex Environment Example
64
Security and Authorizing Access to the Server
Minidisks (VM Only)
65
Chapter 2. Recovery Strategies
7
Recommendations
7
Evaluation
8
Chapter 5. Migration
67
Archive Advantages
8
Migration under VM
67
Archive Weaknesses
9
Migration under VSE
67
Comparison between Data Restore BACKUP and
Migration under VSE Guest Sharing
67
DB2 ARCHIVE
9
Considerations
10
Chapter 6. How to Apply Service . . .
69
Possible Strategies
16
Applying Service for Data Restore for VM
69
User Archives and Unloads
16
Applying Service for Data Restore for VSE
69
Online DB2 Archive and Translation
16
Online DB2 Archive
17
Part 2. Using Data Restore
71
Data Restore BACKUP
17
Changing Your Recovery Strategies
18
Chapter 7. Data Unload and Reload . .
73
Chapter 3. Archive and Recovery . . . 23
Overview
73
DBSU DATAUNLOAD and DATALOAD
73
Overview
23
Ease of Operation with Control Center
74
Control Center
23
DBSU UNLOAD and RELOAD
75
User Archives
23
Ease of Operation with Control Center
75
Archiving with DB2
24
Data Restore SELECT
75
DB2 Archive
24
Data Restore UNLOAD and RELOAD
76
Log Archive
25
Data Restore UNLOAD
77
DB2 Restore
26
Data Restore RELOAD
78
Log Recovery
26
Data Restore RELOAD with Forward Recovery .
81
Data Restore BACKUP
27
Data Restore RELOAD for Incremental Backup
84
Data Restore BACKUP FULL/INCREMENTAL
28
Summary
87
Data Restore RESTORE
29
Compatibilities
87
Summary
31
iii