|
|
How Implicit CONNECT Applies to VM Programs
For application programs that contain SQL statements, a distinction is made
between the creator and the runner of the program.
v The creator is the VM user who submits the program to one of the language
preprocessors. This individual’s authorization ID, which is specified in the
USERID parameter passed to the preprocessor, is used to perform all
authorization checking for the functions performed against data managed by
DB2 Server for VSE & VM, and is the default owner of all objects (tables or
views) created by the program. This authorization ID automatically has the
EXECUTE privilege for the program.
When not coded explicitly, the authorization ID is derived from the CP LOGON.
v The runner is the VM user who runs (executes) the program. This individual’s
authorization ID is either that specified in the CONNECT statement run by the
program, or is the authorization ID that is connected implicitly. The runner may
be the creator, or may be someone to whom the creator has granted the
EXECUTE privilege.
When coded explicitly, the authorization ID and password for the CONNECT
statement are derived from host variables in the program. The values for these
variables should be acquired at run time from control cards by the executing
program. If they are constants fixed in the program, anyone can run the
program.
When not coded explicitly, the authorization ID is derived from the CP LOGON.
Refer to the DB2 Server for VSE & VM Application Programming manual for more
information about how implicit CONNECT applies to application programs.
How Implicit CONNECT Applies to ISQL (VM)
To start ISQL, a VM user invokes the ISQL EXEC. The database manager always
initially does an implicit connect for ISQL users, so this EXEC does not accept an
authorization ID. The authorization ID is derived from the ID of the user’s virtual
machine, as described on page 97.
The user can issue explicit CONNECT statements to override any previous explicit
or implicit connection established for the ISQL session.
Refer to the DB2 Server for VSE & VM Interactive SQL Guide and Reference manual
for more information.
How Implicit CONNECT Applies to the DBS Utility (VM)
When the DBS Utility begins processing an input control file, it expects a
CONNECT statement before any other DBS Utility or SQL statements. If none is
supplied, the database manager will use the ID of the user’s virtual machine.
If the utility is invoked from an application that has already issued a CONNECT
statement (implicitly or explicitly), then another one is not expected. Here, the
authorization ID that was in effect when the program first invoked the utility is
used.
The user can issue explicit CONNECT statements to override any previous explicit
or implicit connection.
Refer to the DB2 Server for VSE & VM Database Services Utility manual for more
information.
98
Database Administration
How Implicit CONNECT Applies to Remote Application Servers
When a VM user implicitly connects to a remote application server, the
authorization ID passed by the requester or received by the server may be different
than the VM logon user ID. It will depend on how the CMS Communication
Directory has been set up for the requester, and whether the server performs user
ID translation. Refer to the Distributed Relational Database Connectivity Guide manual
for more information about security levels specified in the CMS Communication
Directory when implicitly connecting to a remote application server.
How Implicit CONNECT Applies to TCP/IP
When a VM user implicitly connects to an application server using TCP/IP as the
communications protocol, an explicit connect is performed by the resource adapter
using the authorization ID and password found in the CMS Communications
Directory. There is no implicit connect when TCP/IP is being used.
Connecting to the Application Server Explicitly
VM users may want to connect to an application server other than the default one,
switch to another application server, or connect to an application server as a
different authorization ID. These situations entail making an explicit connection.
Switching to Another Application Server
After connecting to an application server, a VM user may want to switch to a
different one. The user issues an SQL CONNECT statement to switch to this
second application server.
Example - Without Specifying an Authorization ID and Password: To switch to
the DB01 application server, enter:
CONNECT TO DB01
Since the authorization ID and password are not specified on the CONNECT
statement, they will be taken from the VM communications directory file if it is
used and if it contains an entry for the DB01 application server. If the file is not
used, if it does not exist, or if it does not contain an entry for the DB01 application
server, the VM logon user ID will be used in an implicit connect.
If this statement fails, the VM user will not remain connected to the original
application server and no other SQL statements will be accepted. The VM user will
have to issue a new CONNECT statement.
When the VM user issues the first SQL statement to be processed on the second
application server, the database manager will try to implicitly connect him or her
to that application server, using the VM logon user ID as the authorization ID. VM
users can avoid the implicit connect by connecting as another user (discussed next)
while switching application servers.
Example - Specifying an Authorization ID and Password: To switch to the DB01
application server under an authorization ID JOHN with a password of johnpw,
enter:
CONNECT JOHN IDENTIFIED BY johnpw TO DB01
If this statement fails, the VM user will not remain connected to the original
application server and no other SQL statements will be accepted. The user will
have to issue a new CONNECT statement.
Chapter 5. Providing Security
99
Connecting under Another Authorization ID
A VM user connects under another authorization ID to the currently established
application server by issuing an SQL CONNECT statement. If the user is not
currently connected to an application server, if the previous connection has been
released, or if the user switched to a new CMS Work Unit in VM/ESA, then the
default application server, established by the SQLINIT EXEC, will be used.
Example: To connect to the currently established application server under the
authorization ID JOHN with a password of johnpw, enter:
CONNECT JOHN IDENTIFIED BY johnpw
If this statement fails, the VM user will remain connected to the application server
as the original authorization ID.
A previous connection could be released for the following reasons:
v A COMMIT RELEASE or ROLLBACK RELEASE statement was issued.
v The previous logical unit of work (LUW) was canceled by the user (using
SQLHX or ISQL CANCEL) or by the operator (using the FORCE statement). The
cancellation releases the connection.
v The previous connection was disabled by the operator (using FORCE DISABLE)
or by other errors such as the database machine not being ready or
communications problems.
v In the VM environment, the previous connection was disabled by the operator
using a FORCE without the DISABLE option.
Determining the Currently Established Application Server
If a user issues an SQL CONNECT statement without any parameters, the database
manager will return the following information:
v The currently connected user ID
v The application server name
v The product ID which can be ’ARI ’ or ’ARI7010’ depending on when the
CONNECT was issued.
Refer to the DB2 Server for VSE & VM SQL Reference manual for more information
about the CONNECT statement.
Connecting to an Application Server in VSE
To control access to the data managed by the database manager, it is necessary to:
v Tell the database manager the users that are authorized to use the DB2 Server
for VSE database and protect their access by means of passwords.
v Inform the database manager when a particular user wants to begin accessing
the DB2 Server for VSE database.
The authority to use a DB2 Server for VSE database is established by granting a
user CONNECT authority. The CONNECT authority carries with it a DB2 Server
for VSE password, which is that user’s key to the application server. After a user
has received CONNECT authority (been assigned an authorization ID and
password), the user can begin to use an application server through the CONNECT
function. After users have received their authorization IDs and passwords, they
can change their own passwords at any time.
All VSE users must connect to an application server explicitly or implicitly:
100
Database Administration
v Batch users must have a DB2 Server for VSE authorization ID and password,
and must connect explicitly.
v Online users who do not have a DB2 Server for VSE authorization ID and
password must connect implicitly. Other online users can use either method.
The CONNECT function can also be used either directly (through the CONNECT
statement with the “userid IDENTIFIED BY password” clause) or indirectly (through
a subsystem logon procedure). The procedure for connecting to an application
server is slightly different for each user environment. The following sections
describe these situations.
Establishing a Default Application Server
|
You may identify the desired application server by specifying the DBNAME
|
parameter at system startup, on the CICS CIRB or CICS CIRC transaction, on the
|
CONNECT statement, when preprocessing, or when binding (Batch
|
Binding/CBND). If you do not specify a server name, these DBNAME default
|
rules apply:
|
v The partition default DBNAME is used if it is specified in the PARTDEF field of
|
the DBNAME Directory.
|
v If a partition default is not specified, the system default DBNAME is used if it is
|
specified in the SYSDEF field of the DBNAME Directory.
|
v If neither a partition nor a system default is specified, the default DBNAME is
|
SQLDS and the default APPLID is SYSARI00.
|
Note: SQLDS must still be identified in the DBNAME Directory.
|
For further information on the DBNAME Directory, refer to the DB2 Server for VSE
System Administration manual.
Connecting to the Application Server in Different VSE
Environments
DB2 Server for VSE users can connect to the application server in the following
environments:
CICS/VSE Online Environment
In a CICS/VSE online environment, online users can connect to the application
server implicitly and explicitly. If online users do not explicitly issue a CONNECT
statement specifying the authorization ID and the password, then the first time
they try to process an SQL statement, the CICS/VSE user is connected to the
application server implicitly.
A CONNECT...TO statement is supported in this environment and can be used to
switch to a different application server between logical units of work. For further
information on switching, refer to “Switching to Another Application Server” on
page 105.
If the first SQL statement in a CICS/VSE application is not a CONNECT statement
with the TO clause, the default application server is connected. On subsequent
CONNECTs performed by that application, if the TO parameter is not specified,
then the connection to the previously connected server will be maintained. For
further information on default application servers, refer to “Establishing a Default
Application Server.”
Chapter 5. Providing Security
101
Batch/Interactive Environment
In a VSE batch or VSE/ICCF environment, an explicit CONNECT must be the first
statement entered by the batch user to access the application server. This statement
is described in the DB2 Server for VSE & VM Application Programming manual.
Explicit connection is required for all user programs. This connection identifies the
authorization ID, and optionally the name of the application server on which the
program will run.
A CONNECT..TO statement is supported in this environment and can be used to
switch to a different application server between logical units of work. For further
information on switching, refer to “Switching to Another Application Server” on
page 105.
If the first SQL statement in an application is a CONNECT statement in which the
TO server_name clause is not specified, or if this clause is not specified as part of
the CONNECT statement following a COMMIT RELEASE or ROLLBACK
RELEASE statement, the default application server is connected. If the TO
server_name clause is not specified as part of the CONNECT statement following a
COMMIT or ROLLBACK statement, the connection to the previously connected
server will be maintained. For further information on default application servers,
refer to “Establishing a Default Application Server” on page 101.
In this environment, there is a distinction between the user who preprocesses a
program that contains SQL statements, and the user who later runs that program.
v The creator of a program is the VSE user who submits the program to one of the
language preprocessors. The user ID specified in the USERID= parameter passed
to the preprocessor is the basis for all authorization checking for the functions
performed against data managed by the system as well as the default owner of
all objects (tables or views) created by the program. This user ID receives RUN
authority when the program is successfully preprocessed.
v The runner of a program is the VSE user who runs (executes) a program that
contains SQL statements. The user ID specified in the CONNECT statement run
by the program must be either the creator or a user ID to whom the creator has
granted the RUN privilege for this program.
The user ID and password for the CONNECT statement are derived from host
variables in the program. Their values should be acquired at run time from
control cards by the executing program. If they are constants fixed in the
program, anyone can run the program.
The runner of a program gets the privilege of accessing the application server
from the creator of the program.
ISQL Environment
When CICS users start ISQL, they are prompted for a user ID, password, and
target database. If the user enters the user ID and password only, ISQL does an
explicit CONNECT to the default target database for the user. If the user does not
enter a user ID, password or target database, ISQL does a CONNECT to the
default target database as a default user ID for the user; this defaulting is called an
implicit CONNECT. If the ISQL user enters a target database only, a CONNECT
would be made to that target database using a default user ID. If the user enters
the user ID, password and target database, ISQL does an explicit CONNECT to the
target database.
In the ISQL environment, you can access any of the application servers connected
with the online resource adapter. If the online resource adapter is not connected to
an application server, you cannot access the ISQL environment.
102
Database Administration
Note: The ISQL environment is a specific case of the CICS transaction
environment, which is discussed in the next section. An ISQL user can enter
explicit CONNECT statements to change the connection and override any
previous explicit or implicit connection established for the ISQL terminal
session.
Refer to the DB2 Server for VSE & VM Interactive SQL Guide and Reference or the
DB2 Server for VSE & VM SQL Reference manual for additional details.
CICS Transaction Environment
Online transactions need not enter a CONNECT command to establish the user ID
within the database manager. If a CONNECT command2 is not entered, the online
support establishes the authorization ID for the transaction. The implicit
CONNECT is carried out by a SCHEDULE call in the case where the online
transaction is connecting to a local application server.
This implicit CONNECT capability is useful if your installation requires terminal
users to sign on CICS. For many transactions, your installation might consider the
sign-on verification sufficient. It may also be useful if your installation has just
installed the database manager, and finds it convenient to have all users identified
by one name (for example, TESTUSER).
The online support establishes a user ID for CICS transactions connecting to a local
DB2 Server for VSE application server as follows:
1. If the local transaction issues a CONNECT command2 the user ID is established
explicitly for the application.
2. If the transaction does not issue a CONNECT command,2 the online support
establishes the user ID as follows:
a. If the transaction had a user ID established from a previous local logical
unit of work (LUW) and that LUW did not specify the RELEASE option for
COMMIT WORK or ROLLBACK WORK, that user ID is used. The CICS
communication link to the application server is freed every time an LUW
ends, and a new link is established for each LUW in the transaction.
b. If the transaction has a valid CICS sign on userid and is associated with a
terminal, the CICS signon userid is used for the user ID.
c. If a user ID was specified as an input parameter to the CIRB or CIRA
transaction that established connections to the application server, that user
ID is used. The person who invoked CIRB or CIRA will know what the user
ID is.
d. If a user ID was not specified in the CIRB or CIRA transaction that
established connections to the application server, the default user ID
CICSUSER is established for your transaction.
After the user ID is determined as described above for cases b, c, and d, one more
requirement must be met to successfully complete the connection to the application
server: CONNECT authority must be granted to either the specific authorization ID
or “ALLUSERS”. ALLUSERS is a special authorization ID that permits any user ID
to be implicitly connected without having been specifically granted CONNECT
authority, and can be used by the database administrator to turn on or turn off the
implicit CONNECT capability. During database generation, ALLUSERS is granted
CONNECT authority by default.
2. The CONNECT statement with the following format: CONNECT userid IDENTIFIED BY password.
Chapter 5. Providing Security
103
At many installations, the CICS user need not be aware of DB2 Server for VSE
authorization ID or authorization capabilities. Here, the CICS implicit connect
support can be very useful.
Suppose you code a transaction called STAT that displays the inventory status of a
given part. Banes and Smith are to be the users of the application.
You define Banes and Smith to the CICS signon process.
You must then authorize BANES and SMITH to run your program. Of course, you
must have the RUN privilege with the GRANT option on your program. For this
example, assume that the program was preprocessed with the name INVSTAT:
GRANT RUN ON INVSTAT TO BANES, SMITH
Note: BANES and SMITH do not need CONNECT authority. It is connected
through internal mechanisms of the DB2 Server for VSE online support.
You must also establish the security key when you define the inventory program
to CICS.
To use the STAT transaction, Banes and Smith merely sign on to the CICS
subsystem by entering, for example:
CESN BANES, XXXX
After signed on, they need only enter the transaction identifier STAT, which causes
the INVSTAT program to be loaded and invoked. Since there is no CONNECT
statement in the program, the user ID established is the signed-on user ID
(BANES). Because BANES was granted RUN authority on INVSTAT, the database
manager allows the program to process.
Online applications can access any of the application servers connected with the
online resource adapter. The online resource adapter can connect to many
application servers using the CIRA or CIRB transactions.
Refer to the DB2 Server for VSE & VM Application Programming manual for
additional information on this environment.
User IDs for Remote CICS/VSE Transactions
For online DB2 Server for VSE transactions which are accessing a remote server
and which issued an SQL CONNECT statement with the “userid IDENTIFIED BY
password” clause to establish the user ID within the database manager, the user ID
is established explicitly for the transaction.
For online DB2 Server for VSE transactions which are accessing a remote server
and which did not issue an SQL CONNECT statement with the “userid
IDENTIFIED BY password” clause to establish the user ID within the database
manager, the Online Resource Adapter will attempt to establish the user ID for the
transaction implicitly as follows:
1. If the transaction had a user ID established for a previous remote logical unit of
work, and the previous logical unit of work did not specify the RELEASE
option for COMMIT WORK or ROLLBACK WORK, and the transaction did not
switch to another application server, that user ID and its corresponding
password are used. (Remember that every time a logical unit of work ends
with RELEASE or the transaction switched to another application server, and
you enter another SQL statement, you are implicitly connected as the CICS
104
Database Administration
signon userid. Therefore, the user ID has to be re-established if the transaction
has more than one logical unit of work ending with RELEASE or if the
transaction is switching application servers.)
2. The user ID returned by the CICS ASSIGN command is used for the user ID.
Connecting to an Application Server in Special Circumstances
VSE users can connect to an application server other than the default one, or
connect to an application server as a different authorization ID. VSE batch users
can switch from an application server to another. These situations require making
an explicit connection. VSE online users can also switch from an application server
to another, by issuing an SQL CONNECT statement with the TO parameter,
provided that the online resource adapter has established connections to the
application server.
Switching to Another Application Server
After connecting to an application server, a VSE user can switch to another one by
issuing an SQL CONNECT statement. The switch occurs between logical units of
work.
Example - Without Specifying an Authorization ID and Password
To switch to the DB01 application server, enter:
CONNECT TO DB01
Because the user ID and password are not specified on the CONNECT statement,
the user ID and password used is determined according to the rules described in
“CICS Transaction Environment” on page 103 and “User IDs for Remote
CICS/VSE Transactions” on page 104. For VSE batch users, the user ID and
password used in the previous LUW are used if the LUW ends with a COMMIT
WORK or ROLLBACK WORK statement. However, if the LUW ends with a
COMMIT RELEASE or ROLLBACK RELEASE statement, the next SQL statement
after the CONNECT statement is unsuccessful.
If the CONNECT statement is not successful, the VSE batch user does not remain
connected to the original application server, and no other SQL statements are
accepted. The batch user has to enter a new CONNECT statement.
When the VSE user enters the first SQL statement to be processed on the second
application server, the batch resource adapter or the online resource adapter
connects the user to that application server using user ID and password previously
established. A VSE user can switch to another application server as different ID by
connecting as another user, as discussed in the next section.
Example - Specifying an Authorization ID and Password
To switch to the DB01 application server under an authorization ID JOHN with a
password of johnpw, enter:
CONNECT JOHN IDENTIFIED BY johnpw TO DB01
If this statement is not successful, the VSE batch user does not remain connected to
the original application server and no other SQL statements are accepted. The
batch user will have to enter a new CONNECT statement.
Connecting under Another Authorization ID
A VSE user connects under another authorization ID to the established application
server by issuing an SQL CONNECT statement. If the user is not connected to an
Chapter 5. Providing Security
105
application server, the default application server is accessed. For batch users, if the
previous connection has been released, the default application server is accessed.
Example
To connect to the currently established application server under the authorization
ID JOHN with a password of johnpw, enter:
CONNECT JOHN IDENTIFIED BY johnpw
If this statement fails, the VSE user will remain connected to the application server
as the original authorization ID.
A previous connection could be released for the following reasons:
v A COMMIT RELEASE or ROLLBACK RELEASE statement is entered.
v The previous LUW is canceled by a local user entering the FORCE DISABLE
statement, or a remote user entering the FORCE statement. Canceling an LUW
in this manner releases the connection.
v The previous connection is ended by the operator (using FORCE DISABLE) or
by other errors, for example, communications problems.
v The CICS transaction switched from a local to a remote server, from a remote to
a local server, from one remote server to another remote server.
Determining the Current User ID and Application Server
If a user enters an SQL CONNECT statement without any parameters, or after the
successful execution of a CONNECT statement, the database manager returns the
following information in the SQLCA:
v Currently connected user ID
v Application server name
v Product ID, which can be ‘ARI
’ or ‘ARI7010’ depending on when the
CONNECT was issued.
Refer to the DB2 Server for VSE & VM SQL Reference manual for more information
about the CONNECT statement.
Resolving Remote Server Name to Target Database (CICS)
1. If the CICS/VSE transaction issues an SQL CONNECT statement with the “TO
server name” clause, the server name is established explicitly for the transaction
and the Online Resource Adapter will use the DBNAME Directory to resolve
the server name to the target database.
If the specified application server is remote and the Communication Protocol
specified by the connected user is not TCP/IP, the AR will issue a GDS
ALLOCATE command to acquire a session to the remote system where the
server runs. The SYSID used in this ALLOCATE command will be the SYSID
value from the matching DBNAME Directory entry (and must match a CEDA
DEF CONNECTION definition). The AR will then issue a GDS CONNECT
PROCESS command to initiate an APPC basic conversation with the remote
server. The PROCNAME used in this CONNECT PROCESS command will be
the REMTPN value from the matching DBNAME Directory entry.
If the specified application server is remote and the Communication Protocol
specified by the user is TCP/IP, the AR will acquire a TCP/IP socket. Then the
AR will use this socket to originate a connection request to initiate a TCP/IP
communication with the remote server. In this case, the TCPPORT and the
TCPHOST or the IPADDR from the matching DBNAME Directory entry are
required for issuing the connect request.
106
Database Administration
2. If the CICS/VSE transaction did not issue an SQL CONNECT statement with
the “TO server name” clause, the Online Resource Adapter will attempt to
connect to the default application server.
If the default application server is remote and the Communication Protocol
specified by the connected user is not TCP/IP, the AR will issue a GDS
ALLOCATE command to acquire a session to the remote system where the
default application server runs. The SYSID used in this ALLOCATE command
will be the SYSID value of the default server (and must match a CEDA DEF
CONNECTION definition). The AR will then issue a GDS CONNECT
PROCESS command to initiate an APPC basic conversation with the remote
server. The PROCNAME used in this CONNECT PROCESS command will be
the REMTPN value of the default server.
If the default application server is a remote server and the Communication
Protocol specified by the user is TCP/IP, the AR will acquire a TCP/IP socket.
Then the AR will use this socket to originate a connection request to initiate a
TCP/IP communication with the remote server. In this case, the TCPPORT and
the TCPHOST or the IPADDR from the default server’s DBNAME Directory
entry are required for issuing the connect request.
The default application server is determined when the CIRB transaction was
invoked and can be changed subsequently by a CIRC transaction. For more
information on establishing a default application server, see Establishing a
Default Application Server.
Resolving Remote Server Name to Target Database (VSE Batch)
If the Batch application issues an SQL CONNECT statement with the ″TO server
name″ clause, the server name is established explicitly for the transaction and the
Batch Resource Adapter uses the DBNAME Directory to resolve the server name to
the target database.
If the specified application server is a local or host VM (Guest Sharing) server,
communications is done using XPCC as it is currently done. If the application
server is remote and TCP/IP information is present in the matching DBNAME
Directory entry, communications is done using TCP/IP. If TCP/IP information is
not present, an error is returned in the SQLCA: SQLCODE -841, SQLSTATE 57040,
with a reason code in SQLERRD2.
If the Batch Application issues an SQL CONNECT statement without the ″TO
server name″ clause, the actions taken by the Batch Resource Adapter depend on
the previous connection state. If the previous state was established with a
COMMIT or ROLLBACK, then the Batch Resource Adapter connects back to the
previous Server name. If the previous state was established with a COMMIT or
ROLLBACK with the RELEASE option, then the Batch Resource Adapter attempts
to connect to the default application server.
If the default application server is a local or host VM (Guest Sharing) server,
communications is done using XPCC as it is currently done. If the application
server is remote and TCP/IP information is present in the matching DBNAME
Directory entry, communications is done using TCP/IP. If TCP/IP information is
not present, an error is returned in the SQLCA: SQLCODE -841, SQLSTATE 57040,
with a reason code in SQLERRD2.
The default application server is determined from the DBNAME Directory as is
currently done. For more information on establishing a default application server,
Chapter 5. Providing Security
107
see “Establishing a Default Application Server” on page 101. Note that Batch
applications cannot access a Remote server via SNA, only via TCP/IP.
Restricting Access Using Views
Views control who has access to what data. They can be set up to allow access to a
subset of the columns or the rows of a table.
Example
To show how a view can be used to restrict access to information, consider the
information presented in Table 18.
Table 18. Employee Information (EMP_INFO) Table
NAME
DEPT
SALARY
PHONENO
SMITH
100
25750
3978
BANES
200
15051
3476
ADAMSON
105
33075
4738
PARKER
200
26250
6789
KWAN
100
22260
7831
WALKER
105
23840
5498
Many different people may require access to information in this table for different
reasons.
Examples
1. The personnel department needs to be able to update and look at the entire
table.
This requirement is met by granting users in the personnel department SELECT
and UPDATE privileges on this table, as follows:
GRANT SELECT,UPDATE ON EMP_INFO TO PERSONNL
2. Individual department managers need to look at the salary information for
their employees.
This requirement is met by creating a view for each manager. For example, the
following view (called EMP100) can be created for JANE, the manager of
department 100:
CREATE VIEW EMP100
AS SELECT NAME,SALARY,PHONENO
FROM EMP_INFO
WHERE DEPT=100
GRANT SELECT ON EMP100 TO JANE
JANE (and any others who have SELECT privilege on this view) would query
it as they would an ordinary table. It would appear as the following:
Table 19. EMP100 View
NAME
SALARY
PHONENO
SMITH
25750
3978
KWAN
22260
7831
3. All users require access to telephone number information.
108
Database Administration
This requirement is met by creating a view (called PHONE) on the NAME and
PHONENO columns:
CREATE VIEW PHONE
AS SELECT NAME,PHONENO
FROM EMP_INFO
GRANT SELECT ON PHONE TO PUBLIC
The keyword PUBLIC grants the privileges on the PHONE view to all users.
Users who access it will see the following table:
Table 20. PHONE View
NAME
PHONENO
SMITH
3978
BANES
3476
ADAMSON
4738
PARKER
6789
KWAN
7831
WALKER
5498
Changing User Passwords
All users’ passwords are recorded in the SYSTEM.SYSUSERAUTH catalog table. As
a DBA, you can change any user’s password at any time. To do this, use a GRANT
CONNECT statement.
Example
GRANT CONNECT TO JOHN IDENTIFIED BY xyzabc
Users can also change their own passwords at any time, by issuing a GRANT
CONNECT statement to themselves. To change a user’s password verified by the
CICS subsystem, or some other subsystem, follow the procedure for that
subsystem.
You should change all passwords on a periodic basis; for example, every four
months.
Securing the Database Catalog Tables
During database generation, the SELECT privilege is granted to PUBLIC on the
catalog tables. In most cases this presents no security problem, but for very
sensitive data it may be undesirable. These tables describe every object in the
database, thus, while users would not know what specific items of data are stored,
they would be able to tell what kind of data existed. Conceivably, a malicious
individual could make destructive use of this knowledge.
Before revoking general access to the tables, however, you must weigh the
advantages of securing the information in them against the disadvantages of users
being unable to retrieve the information they require. The catalog tables are an
active dictionary facility, and help to maintain definitions, control information, and
general information on data. For example, users can query them to find out what
tables they have created, the names and data types of the columns in each of those
tables, and any synonyms they have defined.
Chapter 5. Providing Security
109
You might consider revoking PUBLIC access to only the SYSCOLSTATS table,
which records the first- and second-most frequent values in the first column used
by every index on every table in the database.
If you do decide to secure all the catalog tables, the easiest way to do this is to
revoke the SELECT privilege from PUBLIC on them. You must be connected as
user ID SQLDBA and have DBA authority. You can then grant authority on specific
tables to specific users.
Example 1
To revoke the SELECT privilege from PUBLIC on SYSTEM.SYSCATALOG, enter:
REVOKE SELECT ON SYSTEM.SYSCATALOG FROM PUBLIC
Before you revoke SELECT privileges from PUBLIC, you should also consider
what impact there might be on existing applications. In particular, some
applications may need to read a catalog table, so will fail if this authority is
revoked. Naturally, in these cases you must grant the SELECT privilege to the
creator of the program.
Note also that if the creator (the person who preprocessed the program) is not its
sole runner, you must also specify the WITH GRANT OPTION clause for this
person, in order to enable him or her to grant authority to other users to run the
program.
Example 2
User JULIE has created a program that accesses SYSTEM.SYSCATALOG, and she
grants RUN authority to KATHY and BILL. If you revoke the SELECT privilege
from PUBLIC, you can preserve KATHY’s and BILL’s authority to run JULIE’s
program by issuing:
GRANT SELECT ON SYSTEM.SYSCATALOG TO JULIE WITH GRANT OPTION
If you revoke the SELECT privilege from PUBLIC on a catalog table, and later
wish to completely restore it, you should also specify the WITH GRANT OPTION
clause.
Example 3
To restore authority to PUBLIC on SYSTEM.SYSACCESS, enter:
GRANT SELECT ON SYSTEM.SYSACCESS TO PUBLIC WITH GRANT OPTION
Refer to the DB2 Server for VSE & VM SQL Reference manual for a description of
the catalog tables.
Security Auditing
There are two ways to audit security: by querying the catalog tables, or by having
the database manager do a security audit trace.
If you simply want to know what security structures exist, the first method is
sufficient. The catalog tables maintain a record of authorization privileges: who has
what authority and from whom they received it. But they do not record
information about the use of these privileges: for example, the number of
unsuccessful attempts to access a resource, the number of accesses based strictly on
DBA authority, or similar authorization use information. For this type of
information, you must use a security audit trace.
110
Database Administration
Both ways of auditing security are discussed below.
Auditing Security Using the Catalog Tables
The following are examples of queries you might enter against the catalog tables in
security auditing:
1.
What users are permitted to connect directly to the DB2 Server for VSE & VM
application server? (DBA authority is required for this query.)
SELECT NAME FROM SYSTEM.SYSUSERAUTH
WHERE AUTHOR=’ ’
The WHERE clause serves to eliminate any entries in SYSTEM.SYSUSERAUTH
for program dependencies from the query result.
2.
How many users have been granted RUN authority on WALTERS.PAYROLL by
user BENNETT? (User WALTERS is the creator of the program; the creator is
determined by the USERID parameter when the program is preprocessed.)
SELECT COUNT(*) FROM SYSTEM.SYSPROGAUTH
WHERE CREATOR = ’WALTERS’
AND PROGNAME = ’PAYROLL’
AND GRANTOR = ’BENNETT’
This query only counts user BENNETT’s first-level grantees (those who
received their authority directly from user BENNETT).
3.
Who are all the users who have received RUN authority on PAYROLL from
someone other than WALTERS?
SELECT COUNT(*) FROM SYSTEM.SYSPROGAUTH
WHERE CREATOR = ’WALTERS’
AND PROGNAME = ’PAYROLL’
AND GRANTOR <> ’WALTERS’
4.
How many users have RESOURCE authority but not DBA authority?
SELECT COUNT(*) FROM SQLDBA.SYSUSERLIST
WHERE RESOURCEAUTH = ’Y’
AND DBAAUTH <> ’Y’
AND AUTHOR = ’ ’
5.
How many secondary authorizations (those that originated from other than the
creator) exist for the JOHNSON.EMPLOYEE table created by user JOHNSON?
SELECT COUNT(*) FROM SYSTEM.SYSTABAUTH
WHERE TCREATOR = ’JOHNSON’
AND TTNAME = ’EMPLOYEE’
AND GRANTOR <> ’JONES’
AND GRANTEETYPE = ’ ’
Here, the GRANTEETYPE = ’ ’ portion of the WHERE clause eliminates
entries for programs.
6.
Which users have been granted SELECT authority on the
PERSONNL.EMPLOYEE table by user LAPIS?
SELECT * FROM SYSTEM.SYSTABAUTH
WHERE TCREATOR = ’PERSONNL’
AND TTNAME = ’EMPLOYEE’
AND SELECTAUTH = ’Y’
AND GRANTEETYPE = ’ ’
AND GRANTOR = ’LAPIS’
ORDER BY TIMESTAMP
Auditing Security Using Tracing
Security audit tracing is one of the functions that can be performed using the trace
facility. A security audit trace is unique in that it is not necessarily done for
problem determination. Start a trace of the security audit function of the RDS
Chapter 5. Providing Security
111
component by using the TRACRDS initialization parameter. Alternatively, you can
start it by issuing the TRACE command from the operator’s console after the
application server has been started.
For descriptions of the TRACRDS parameter, the TRACE operator command, the
trace output records, and the utility that formats these records into readable
output, see the DB2 Server for VSE & VM Operation manual.
In VM, you can direct the trace output to tape, to a CMS file, or to a memory area
known as a trace buffer. However, if your installation uses the security audit trace
frequently, you may want to direct the output to a CMS file. To do this, you must
enter a CMS FILEDEF command before starting the application server, and supply
particular responses to the prompts that come up when tracing is started. For
descriptions of the FILEDEF command and the appropriate message responses, see
the DB2 Server for VSE & VM Operation manual.
As with other traces, you can get two levels of information. Level 1 traces and
records the following information:
v All unsuccessful attempts to obtain access to a resource
v Access that is based strictly on DBA authority
v All CONNECTs to the application server
v All grants of special privileges (DBA, CONNECT, SCHEDULE, or RESOURCE
authorities)
v All grants of RUN authority.
Level 2 keeps track of all DB2 Server for VSE & VM authorization checks.
Table 21 shows each type of authorization verification that the database manager
does, and which results are traced.
Table 21. Information Recorded by a Security Audit Trace
Result Traced at
Result Traced at
Type of Authorization Check
Level 2
Level 1
CONNECT
Y,I,N
Y,I,N
RUN
G,Y,D,N,P
D,N
SELECT, INSERT, UPDATE,
G,Y,D,N,P
D,N
DELETE, ALTER, and INDEX
RESOURCE
Y,N
N
REFERENCES
Y,N
D,N
DBA
D,N
D,N
Grants of Special Privileges
D,N
G,N
(DBA, CONNECT,
RESOURCE, and SCHEDULE)
Grants of RUN Authority
G,N
Y
Yes, the user is authorized.
N
No, the user is not authorized.
G
Yes, the user is authorized to use and grant this privilege.
P
The resource is PUBLIC, and thus all users are authorized.
D
The user is authorized based only on DBA authority (that is, does not have
specific privileges).
I
CONNECT on special link without password verification (scheduled).
112
Database Administration
For each result of an authorization check that is traced, the database manager
creates a trace record in the same format as other kinds of trace records. These
records are identical in format for all levels and types of authorization, and are
written to the same (VSE) trace output file, or (VM) trace tape (or CMS file).
If a value does not apply for a specific occurrence, the database manager sets it to
blanks. For example, a trace record for CONNECT does not contain the name of a
resource (that is, a table name).
Each trace record contains (where applicable):
v Date and time of verification.
v The user ID for which the verification is being done.
v Resource 1 (for example, the name of a table to be accessed or the name of a
program to be run).
v Resource 2 (for example, the name of a particular column to be updated).
v The creator of the resource.
v The type of authorization requested (as listed in Table 21 on page 112).
v The result of the authorization check (Y, N, G, P, D, I).
v The external logical unit of work identifier (EXTLUWID) of the connection,
which uniquely identifies an LU6.2 conversation. Its value is
netid.luname.instance_number.sequence_number, where netid and luname are up to 8
characters long, instance_number is 12 characters long, and sequence_number is 4
characters long. The EXTLUWID is only used for conversations that use the
DRDA protocol.
The Resource 2 field shows the column (where applicable) on checks of UPDATE
authority. It can also contain a description of the reason that the database manager
is checking a certain authority. For example, it might contain “ALTER PUB
DBSPACE” on a check for the DBA authority needed to alter a PUBLIC dbspace. In
this case, DBA would be the type of authorization being checked, while the
Resource 2 field provides more information about why this authority is required.
When analyzing trace records, remember that many operations on views are
restricted. These restrictions are reflected in the trace records generated during
CREATE VIEW processing. When the database manager creates a view, it checks
the user’s authority on the base tables to determine what authority to give that
user on the view. It also checks the view itself to see what operations cannot be
performed on it. For example, because deletions are not allowed in views that
involve a join, the authorization check for DELETE would return an N. The N
shows that deletions are not allowed against the view; it does not necessarily
imply that the creator is not authorized to delete from the base table.
Authorization checks during CREATE VIEW processing are traced, but only at
level 2. The result field of the trace record indicates whether an authorization check
is a result of CREATE VIEW processing. The CREATE VIEW indicator is the letter
V following the usual result indicator. For example, a successful verification of
SELECT authority on a base table produces a result value of YV — yes during
view creation. You can use this indicator to distinguish between normal
authorization checks and those done during view creation.
Note: Tracing occurs during preprocessing and execution of programs, and during
the dynamic execution of statements in ISQL or DBS Utility.
Chapter 5. Providing Security
113
Authorization traces for data manipulation operations in programs occur
during preprocessing, not during execution.
Loading Security Audit Information into Tables
You can use the DBS Utility to load security audit trace records into a table. When
the trace information is in a table, you can use SQL statements to answer questions
such as:
v Who was denied access to a resource?
v Who used DBA authority to access a resource?
v When was RUN authority on a particular program granted to additional users?
Figure 22 on page 115 shows a DB2 Server for VSE example DBS Utility job to
create a security table and load trace records into it. In the example, the trace
output file is on tape.
114
Database Administration
// JOB DATALOAD SECURITY AUDIT TRACE
// EXEC=PROC=DBNAME01
// EXEC=PROC=ARIS71PL
// TLBL ARITRAC
// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=Y,PROGNAME=ARIDBS’
COMMENT ’
’
COMMENT ’ *********************************
’
COMMENT ’ * DATALOAD SECURITY AUDIT TRACE *
’
COMMENT ’ *********************************
’
COMMENT ’
’
COMMENT ’ ACQUIRE A DBSPACE(PRIVATE)
’
COMMENT ’ NAMED SECURITY
’
COMMENT ’
’
ACQUIRE PRIVATE DBSPACE NAMED SECURITY;
COMMENT ’
’
COMMENT ’ CREATE A TABLE IN THE PRIVATE DBSPACE ’
COMMENT ’
’
CREATE TABLE AUDIT_TAB(TRPOINT SMALLINT,
YEAR
CHAR(2),
MONTH
CHAR(2),
DAY
CHAR(2),
TIME
CHAR(8),
USERID
CHAR(8),
GRANTEE
CHAR(8),
RESOURCE1 CHAR(18),
RESOURCE2 CHAR(18),
OWNER
CHAR(8),
AUTHTYPE
CHAR(8),
RESULT
CHAR(2),
EXTLUWID
CHAR(35))
IN SECURITY;
COMMENT ’
’
COMMENT ’ LOAD DATA - (NOTE _ YOU MAY
’
COMMENT ’ WISH TO INTERCHANGE DAY/MONTH)
’
COMMENT ’
’
DATALOAD TABLE (AUDIT_TAB) IF POS (11-14)=-220659706
TRPOINT
7-8
FIXED
EXTLUWID
41-75
CHAR
YEAR
124-125
CHAR
MONTH
118-119
CHAR
DAY
121-122
CHAR
TIME
143-150
CHAR
USERID
168-175
CHAR
GRANTEE
193-200
CHAR
RESOURCE1
218-235
CHAR
RESOURCE2
253-270
CHAR
OWNER
288-295
CHAR
AUTHTYPE
313-320
CHAR
RESULT
338-339
CHAR
INFILE(ARITRAC PDEV(TAPE) BLKSZ(4096) RECFM(VB) RECSZ(384))
Figure 22. Loading Security Audit Records into a Table - DB2 Server for VSE
Figure 23 on page 116 shows a DB2 Server for VM example of running the DBS
Utility. The utility reads a CMS file (SECTAB DATA A), which contains statements
to create a security audit table and load trace records into it. Before invoking the
utility, ensure that the appropriate trace tape is mounted on virtual device 182.
Chapter 5. Providing Security
115
Command to Invoke the DBS Utility:
FILEDEF TRACE1 TAP2 SL (RECFM VB BLOCK 4096 LRECL 384
EXEC SQLDBSU ID(SQLDBA) IN(SECTAB DATA A) PR(TERMINAL)
SECTAB DATA A Contains:
CONNECT user IDENTIFIED BY password;
COMMENT ’
’
COMMENT ’ *********************************
’
COMMENT ’ * DATALOAD SECURITY AUDIT TRACE *
’
COMMENT ’ *********************************
’
COMMENT ’
’
COMMENT ’ ACQUIRE A DBSPACE(PRIVATE)
’
COMMENT ’ NAMED SECURITY
’
COMMENT ’
’
ACQUIRE PRIVATE DBSPACE NAMED SECURITY;
COMMENT ’
’
COMMENT ’ CREATE A TABLE IN THE PRIVATE DBSPACE ’
COMMENT ’
’
CREATE TABLE AUDIT_TAB(TRPOINT SMALLINT,
YEAR
CHAR(2),
MONTH
CHAR(2),
DAY
CHAR(2),
TIME
CHAR(8),
USERID
CHAR(8),
GRANTEE
CHAR(8),
RESOURCE1 CHAR(18),
RESOURCE2 CHAR(18),
OWNER
CHAR(8),
AUTHTYPE
CHAR(8),
RESULT
CHAR(2),
EXTLUWID
CHAR(35))
IN SECURITY;
COMMENT ’
’
COMMENT ’ LOAD DATA - (NOTE _ YOU MAY
’
COMMENT ’ WISH TO INTERCHANGE DAY/MONTH)
’
COMMENT ’
’
DATALOAD TABLE (AUDIT_TAB) IF POS (11-14) = -220659706
TRPOINT
7-8
FIXED
EXTLUWID
41-75
CHAR
YEAR
124-125
CHAR
MONTH
118-119
CHAR
DAY
121-122
CHAR
TIME
143-150
CHAR
USERID
168-175
CHAR
GRANTEE
193-200
CHAR
RESOURCE1
218-235
CHAR
RESOURCE2
253-270
CHAR
OWNER
288-295
CHAR
AUTHTYPE
313-320
CHAR
RESULT
338-339
CHAR
INFILE(TRACE1)
Figure 23. Loading Security Audit Records into a Table - DB2 Server for VM
Note: The external logical unit of work identifier (EXTLUWID) is only used for
conversations that use the DRDA protocol.
If you have other trace functions active while you are tracing a security audit,
include an input-record-id clause (IF POS (11-14) = -220659706) on the
DATALOAD command to identify that only security audit trace records are to be
loaded. This is necessary because the trace records from other functions are
interspersed with those of the security audit trace.
116
Database Administration
When doing a security audit trace, it is usually to your advantage to trace the
parser component at the same time. When you trace this component at level 1, the
resultant trace records describe the SQL statement entered into the database
manager. By using the timestamp in the trace records, you can correlate the input
to the security audit trace records produced.
If you plan to load the security audit trace records into a table, you may want to
print the parser trace records by using the trace formatter. If you are printing the
security audit records, you may want to also print the parser records by specifying
both the parser and security audit components for the trace formatter. An example
producing such a listing is shown in Table 22 on page 119 and Table 23 on page
119.
In VM, if you directed the trace output to a CMS file (by issuing a CMS FILEDEF
command), you can still use the DBS Utility to load the trace data into tables. To
do this, enter the following CMS FILEDEF command before invoking the
SQLDBSU EXEC:
FILEDEF ddname DISK filename filetype filemode (RECFM VB LRECL 384 BLOCK 4096
Notes:
1. The ddname on the FILEDEF command must match that used in the DBS
Utility INFILE parameter of the DATALOAD command.
2. You must enter the RECFM, LRECL, and BLOCK values shown.
In VSE, if, when starting the application server, you directed the trace output to
disk, you must change the INFILE statement to:
INFILE(ARITRAC PDEV(DASD) BLKSZ(4088) RECFM(VB) RECSZ(384))
In addition, you must change the job control to identify the DASD SAM trace
output file. For example:
v For a DASD file that is not managed by the VSE/VSAM Space Management for
SAM Feature, you might specify:
// DLBL ARITRAC,’TRACE.FILE1’
// EXTENT ,VSER01,1,0,301,120
// ASSGN SYS006,195
v For one that is, you might specify:
// DLBL ARITRAC,’TRACE.FILE1’,0,VSAM,DISP=(,DELETE)
When DISP=(,DELETE), the VSAM file is deleted after it is read. If you do not
want the file to be deleted, specify DISP=(,KEEP) or omit the DISP parameter.
The above examples would replace the TLBL statement in Figure 22 on page 115.
Once you have loaded the security audit trace records into a table, you can enter
SQL statements against them. This method may make viewing the records easier,
but has a disadvantage in that any user who has DBA authority can change the
table, and any tampering may make the data incorrect. You should always print
the trace records and protect the trace tape to ensure that there is always a valid
copy.
Figure 24 on page 118 shows examples of typical security audit queries. Some of
the records traced appear only at level 2; level 2 can generate a significant number
of trace records. These queries are shown as they might appear as input to the DBS
Utility.
Chapter 5. Providing Security
117
COMMENT ’******************************************’
COMMENT ’ SELECT ALL RECORDS FROM AUDIT TABLE
’
COMMENT ’
WHERE AUTHORIZATION WAS DENIED
’
COMMENT ’******************************************’
SELECT * FROM AUDIT_TAB WHERE RESULT=’N’;
COMMENT ’******************************************’
COMMENT ’ SELECT RECORDS FROM AUDIT TABLE
’
COMMENT ’ RECORDED BETWEEN 8 A.M. AND 12:30 P.M.
’
COMMENT ’ ON JUNE 29
’
COMMENT ’******************************************’
SELECT * FROM AUDIT_TAB WHERE MONTH = ’06’
AND DAY = ’29’ AND TIME BETWEEN ’08:00:00’ AND ’12:30:00’;
COMMENT ’******************************************’
COMMENT ’ SELECT RECORDS FROM AUDIT TABLE
’
COMMENT ’ RECORDED BETWEEN 12:30 P.M. AND 4:00 P.M.’
COMMENT ’ ON JUNE 29 AND AUTHORIZED DUE TO DBAAUTH.’
COMMENT ’******************************************’
SELECT * FROM AUDIT_TAB WHERE MONTH = ’06’
AND DAY = ’29’ AND TIME BETWEEN ’12:30:00’
AND ’16:00:00’ AND
RESULT = ’D’;
COMMENT ’******************************************’
COMMENT ’ SELECT CHECKS OF UPDATE AUTHORITY
’
COMMENT ’ AGAINST TABLE USER1.TAB1
’
COMMENT ’ RECORDED BETWEEN 08:00 P.M. AND 4:00 P.M.’
COMMENT ’ ON JUNE 29 NOT DUE TO VIEW CREATION.
’
COMMENT ’
(update checks traced at level 2)
’
COMMENT ’******************************************’
SELECT * FROM AUDIT_TAB WHERE MONTH = ’06’
AND DAY = ’29’ AND TIME BETWEEN ’08:00:00’
AND ’16:00:00’ AND OWNER = ’USER1’
AND RESOURCE1 = ’TAB1’ AND AUTHTYPE = ’UPDATE’
AND RESULT NOT LIKE ’%V’;
COMMENT ’******************************************’
COMMENT ’ SELECT CHECKS OF UPDATE AUTHORITY
’
COMMENT ’ AGAINST TABLE USER1.TAB1
’
COMMENT ’ RECORDED BETWEEN 08:00 P.M. AND 4:00 P.M.’
COMMENT ’ ON JUNE 29 DUE TO DBAAUTH
’
COMMENT ’
(DBA activity traced at level 1 or 2)
’
COMMENT ’******************************************’
SELECT * FROM AUDIT_TAB WHERE MONTH = ’06’ AND
DAY = ’29’ AND TIME BETWEEN ’08:00:00’
AND ’16:00:00’ AND OWNER = ’USER1’
AND RESOURCE1 = ’TAB1’ AND AUTHTYPE = ’UPDATE’
AND RESULT = ’D’;
COMMENT ’******************************************’
COMMENT ’ SELECT ALL GRANTS OF RUN AUTH ON
’
COMMENT ’ PROGRAMS USER1.DBD1 AND USER1.DBD3.
’
COMMENT ’******************************************’
SELECT * FROM AUDIT_TAB WHERE AUTHTYPE = ’RUN’ AND
OWNER = ’USER1’ AND RESOURCE1 = ’DBD1’ OR
RESOURCE1 = ’DBD3’;
Figure 24. Example Security Audit Queries
Printing Security Audit Information from the Trace File
A security audit trace, especially a level 2 one, can generate a large amount of
information, and even more information is generated if you are tracing other
components or functions at the same time. All of these records are placed in a
single trace file. To print them selectively, you need to use the trace formatting
utility.
118
Database Administration
This utility accepts control statements, which in VM, it reads from a CMS file. As it
does not access the database manager, the latter does not have to be running for
the trace formatter to work.
Table 22 shows an example of invoking the DB2 Server for VSE trace formatter.
The control statements print out all security audit trace records and all parser trace
records. The example also restricts the output by date and time and is only for
USER1.
Table 22. Printing Security Audit Records from the Trace File
// JOB RUN TRACE FORMATTER
// TLBL ARITRAC,file-id
<-- File-id of trace tape (optional
)
// ASSGN SYS004,cuu
<-- Address of tape unit
// EXEC ARIMTRA,SIZE=AUTO
SUBCOMP AU PA
USERID USER1
DATE 06/29/85
TIME 12:00:00 23:00:00
/*
/&
Notes:
1. The tape should be mounted on the physical device specified by cuu before
running the job.
2. The tape file-id must be the same file-id as was specified on the TLBL
statement when the tape was created.
3. The DB2 Server for VSE & VM Operation manual contains examples of running
the trace formatter to process a trace file that resides on DASD.
Table 23 shows an example of invoking the DB2 Server for VM trace formatter. The
interactive SQLTRFMT EXEC supplied by IBM resides on the production minidisk
(Q-disk) and invokes XEDIT to edit a CMS file called SQLTRFMT TRACE A. Use
this exec to type in the control statements. When you file SQLTRFMT SQLTRACE
A, the SQLTRFMT EXEC then asks where you want its output directed.
The control statements shown in Table 23 print all security audit (AU) trace records
and all parser (PA) trace records. They also restrict the output to those records
generated for a specific date (06/29/85), time (12:00:00 to 23:00:00), and user
(USER1).
Table 23. Printing Security Audit Records from the Trace File
Invoking the Trace Formatter:
SQLTRFMT
Example Control Statements
SUBCOMP AU PA
USERID USER1
DATE 06/29/85
TIME 12:00:00 23:00:00
If you are directing your trace output to tape, then before invoking the trace
formatter, ensure that the appropriate tape is mounted on virtual device 182. If you
are directing it to a CMS file, you must enter a CMS FILEDEF command for the
file before invoking SQLTRFMT. Use the same FILEDEF that you issued before you
Chapter 5. Providing Security
119
invoked SQLSTART (and initiated the trace). See the DB2 Server for VSE & VM
Operation manual for the command format.
Complete instructions for using the utility are in the DB2 Server for VSE & VM
Operation manual.
120
Database Administration
Chapter
6. Recovering from Failures
A variety of problems can occur in a relational database management system,
leading to inaccuracies or loss of data. A power failure can bring the computer to a
halt; the disk used to store information could become damaged; users can make
errors such as dropping the wrong table or dbspace. Database recovery refers to
the processing needed to correct the data when something goes wrong.
The problems that can occur fall into the following categories. This chapter
explains how to recover from those that fall into the first two categories. For
information on how to recover from the other types, see the DB2 Server for VM
System Administration or DB2 Server for VSE System Administration manual.
v Application Failure
A single application (for example, an ISQL command or routine, or a DBS Utility
command) fails to complete successfully.
v User Logic Errors
The system or application does the requested function, but the request itself was
in error: that is, the user (or application program) did not specify the correct
function.
v System Failure
The operating system, CICS subsystem, or the database manager can end
abnormally because of error conditions or a power failure.
v DASD Failure
The system may be unable to read data from or write it to the DASD device on
which it is stored because the storage medium is unreadable or damaged. Such
an error could occur on the log or the storage pool.
v Subsystem Failures (VM Only)
With VSE Guest Sharing, whereby users on VSE are accessing a DB2 Server for
VM application server, any of the subsystems involved (the database manager,
VM/ESA operating system, VSE, or the CICS subsystem) may end abnormally.
Overview of Recovery Concepts
Logical Units of Work
When a user or an application program has made a change or a group of related
changes to the database, and if the application in question completed successfully,
the user or program issues an SQL COMMIT WORK statement to the application
server, to commit these changes to the database. If the application did not complete
successfully, the user instead issues an SQL ROLLBACK WORK statement, which
undoes all the changes made up to the point of the error since the last COMMIT
WORK statement, or since the start of the program or session.
A group of SQL statements is called a logical unit of work (LUW). An LUW can be
as small as one statement, or as large as an entire application execution (or ISQL
session). All SQL statements are processed within an LUW. If no LUW exists when
a statement is issued, then the database manager creates one.
121
CMS Work Units
VM users can take advantage of CMS work units which allow them to maintain
more than one logical unit of work (LUW) at a time. With separate CMS work
units, application programs can be independent of one another. For example, a
user can run a program, and in the middle of an LUW, have that program call a
second program which runs in a separate CMS work unit. When work is
committed in the second program, it does not affect the active LUW in the first
program.
Note: CMS work units require extra processing overhead, so should only be used
when necessary. If an application does not need this support, set the
WORKUNIT option of the SQLINIT command to NO.
Atomic Operations
An operation is atomic if within a logical unit of work (LUW), it can succeed or
fail on its own; that is, it does not affect other operations as long as they do not
depend on it. The DB2 Server for VSE & VM database manager considers all
operations are atomic except those that occur in dbspaces residing in
nonrecoverable storage pools, and those that occur when LOGMODE=N (running
with the no-log option).
Example:
Suppose you have an application program that performs the following operations
within one LUW: a DELETE, an UPDATE, and an INSERT statement. Assume the
DELETE statement will process successfully; then, the UPDATE statement will
change the values in the table as specified. If, at the end of statement processing,
any duplicates exist in the primary key, the UPDATE operation is rolled back.
Because the failure of the UPDATE statement does not affect the DELETE
statement (both operations are atomic), you can let the program proceed and
perform the INSERT. Alternatively, you could COMMIT the successful DELETE or
ROLLBACK the LUW.
For a further discussion of atomic operations, see “Backouts Initiated by
Application Programs” on page 131.
Dynamic Application Backout
This process reverses the changes made by a logical unit of work (LUW) that ends
abnormally. It is performed while the system is online and processing other
applications. It is supported for the following:
v DB2 Server for VM
v DB2 Server for VSE
v ISQL
v DBS Utility
v preprocessor operations
v Batch
v VSE/ICCF, and
v the CICS subsystem
The DB2 Server for VSE dynamic application backout facilities are also coordinated
with the Dynamic Transaction Backout facilities of the CICS subsystem. A backout
initiated by the DB2 Server for VSE database manager initiates a CICS transaction
122
Database Administration
backout for the affected transaction. Similarly, a transaction backout initiated by the
CICS subsystem initiates a DB2 Server for VSE backout, if the transaction was
doing any SQL processing.
Restart Processing
If the system or the database manager ends abnormally, this process reverses any
database changes made by applications that were in progress within an LUW at
the time of the failure. It also ensures commitment of all changes made by those
applications that completed successfully.
Recovery from Application Failures
To take advantage of the DB2 Server for VSE & VM recovery support, applications
should be designed so that all SQL requests that constitute one logical change to
the database are properly grouped into logical units of work (LUWs). For example,
if an application transfers funds from one account to another, which entails an
update to two different rows in the database, the updates should be done in the
same LUW. Thus, if the application should fail, the database would be left in one
of two consistent states: either the transfer was done completely (both rows
updated), or it was not done at all (neither row updated). If the updates were in
different LUWs, an application failure could result in only half of the transfer
being performed (only one row updated).
Designing an application properly requires an understanding of when an LUW
begins and ends. When it ends, the changes made within the LUW are either
committed to the database, or backed out. Figure 25 on page 124 shows the general
rules for DB2 Server for VSE LUWs. Table 24 shows the general rules for VM users
and Table 25 on page 125 shows the general rules for VSE guest users accessing an
application server on a VM/ESA system. There are, however, variations and
special considerations that depend on the application environment and application
implementation techniques. These variations are discussed in the following
sections.
Chapter 6. Recovering from Failures
123
WHEN A LOGICAL
WHEN A LOGICAL UNIT OF WORK ENDS
UNIT OF WORK
BEGINS
COMMITTED
BACKED OUT
Programs
First SQL
-COMMIT WORK
-ROLLBACK WORK
-Batch/ICCF
statement
-Normal end
-Abnormal end
-Implicit rollback
-Statement error
First SQL
-COMMIT WORK
-ROLLBACK WORK
-CICS
statement
-SYNCPOINT
-SYNCPOINT ROLLBACK
-Normal end
-Abnormal end
-Implicit rollback
ISQL Sessions
Command
-After successful
-CANCEL
-AUTOCOMMIT ON
entry
command process-
-Command Error
ing
-ROLLBACK WORK
-COMMIT WORK for
-Implicit rollback
multiple row
update
First Command
-COMMIT WORK
-ROLLBACK WORK
-AUTOCOMMIT
-Abnormal end
OFF
-CANCEL
-Command Error
-Implicit rollback
DBS Jobs
Command entry
-After successful
-Command error
-AUTOCOMMIT ON
command process-
-Abnormal end
ing
First Command
-COMMIT WORK
-Command error
-AUTOCOMMIT
-Normal end of
-ROLLBACK WORK
OFF
program
-Abnormal end
of program
Start of job
-Normal end of
-Abnormal end of
Preprocessor
setp
job step
job step
Jobs
Figure 25. General Rules for DB2 Server for VSE Logical Units of Work
Notes to Figure 25:
v Note that DBS ERRORMODE processing may change the DBS AUTOCOMMIT
mode. Refer to the DB2 Server for VSE & VM Database Services Utility manual for
details.
v When AUTOCOMMIT is on, ISQL issues a COMMIT WORK when the statement
completes successfully. The exception is for UPDATE, DELETE, and INSERT
statements that affect more than one row. For that case, you are prompted before
ISQL issues a COMMIT WORK.
Table 24. General Rules for DB2 Server for VM Logical Units of Work
ISQL Sessions
DBS Utility
LOGICAL
All
UNIT OF
Programs
AUTOCOMMIT
AUTOCOMMIT AUTOCOMMIT AUTOCOMMIT
Pre-
WORK
Under CMS
ON
OFF
ON
OFF
processors
BEGINS
First SQL
Each SQL
First SQL
Command
First command
Start of CMS
statement
statement entry
statement
entry
command
ENDS
COMMIT
After successful
COMMIT
After successful
COMMIT
Normal end
COMMITTED
WORK
SQL statement
WORK
command
WORK
of CMS
processing,
processing
command
Normal end
COMMIT
Normal end
of CMS
WORK for
of CMS
Implicit
command
multi-row
command
rollback
updates
SQLHX
124
Database Administration
Table 24. General Rules for DB2 Server for VM Logical Units of Work (continued)
ISQL Sessions
DBS Utility
LOGICAL
All
UNIT OF
Programs
AUTOCOMMIT
AUTOCOMMIT AUTOCOMMIT AUTOCOMMIT
Pre-
WORK
Under CMS
ON
OFF
ON
OFF
processors
ENDS
ROLLBACK
CANCEL
ROLLBACK
Abnormal
Command
Abnormal end
BACKED OUT
WORK
WORK
end
error
of CMS
Statement
command
Abnormal
error
Abnormal
Command
ROLLBACK
end of CMS
end
error
WORK
command
ROLLBACK
WORK
CANCEL
SQLHX
Abnormal
Implicit
end of CMS
rollback
Implicit
Implicit
Implicit
command
rollback
rollback
rollback
SQLHX
Implicit
rollback
Statement
error
SQLHX
Notes to Table 24 on page 124:
1. If a DB2 application program (including preprocessors and utilities) is not
invoked from an EXEC, it is considered to be a command, and the COMMIT
and ROLLBACK rules apply. If the program is issued from an EXEC (as is
almost always the case), then it is considered to be a subcommand. For EXECs,
end-of-command COMMIT and ROLLBACK processing does not occur until
the EXEC completes.
2. DBS ERRORMODE processing may change the DBS AUTOCOMMIT mode.
Refer to the DB2 Server for VSE & VM Database Services Utility manual for
details.
3. When AUTOCOMMIT is on, ISQL issues a COMMIT WORK when the
statement completes successfully (as shown in Table 24 on page 124). The
exception is for UPDATE, DELETE, and INSERT statements that affect more
than one row. For that case, you are prompted before ISQL issues a COMMIT
WORK.
4. For the normal end situation, the database manager will attempt to commit
LUWs. The commit may fail if a deadlock occurs, a log full condition is
encountered, or some other system condition occurs that causes the program to
end.
Table 25. General Rules for DB2 Server for VM Logical Units of Work from VSE Guests
PROGRAMS
LOGICAL UNIT OF
WORK
CICS
Batch/ICCF
Preprocessor Jobs
BEGINS
First SQL Statement
First SQL Statement
Start of job step
ENDS COMMITTED
COMMIT WORK
COMMIT WORK
Normal end of job step
Normal end
SYNCPOINT
Normal end
Chapter 6. Recovering from Failures
125
Table 25. General Rules for DB2 Server for VM Logical Units of Work from VSE Guests (continued)
PROGRAMS
LOGICAL UNIT OF
WORK
CICS
Batch/ICCF
Preprocessor Jobs
ENDS BACKED OUT
ROLLBACK WORK
ROLLBACK WORK
Abnormal end of job
step
Abnormal end
SYNCPOINT ROLLBACK
Implicit rollback
Abnormal end
Statement error
Implicit rollback
Application Program Recovery in VM
An application is considered to have ended normally when it returns to CMS. In
single user mode, an application ends normally when it returns to the DB2 Server
for VM calling routine. All other types of termination (such as HX, CMS abend,
program check, or any user machine termination) are considered abnormal.
Note: In single user mode, an application’s Register 15 return code protocol is not
part of the definition of termination, and is not used by the application
server to determine whether it should proceed with normal or abnormal
termination processing. The application server establishes a CMS ABNEXIT
exit in the database machine. The exit attempts recovery and dumps
important diagnostic information when the recovery attempt is not
successful. If a single user mode application establishes an abnormal end
exit (for example, by way of ABNEXIT, STAE, SPIE, STXIT), the DB2 Server
for VM abend exit is overridden.
Some compilers provide a mechanism that handles program interrupts during the
execution of a program and before control returns to CMS. Consequently, the
application server may not be aware that the program termination is abnormal,
and will perform an implicit COMMIT rather than an implicit ROLLBACK. See the
DB2 Server for VSE & VM Application Programming manual for more information
about program interrupts.
Users should be aware of how CMS handles multiple abnormal end exits, and
should clear any that have been set by the application program before returning to
the DB2 Server for VM application server, or else unpredictable results may occur
when later CMS commands are issued. Also, the user should reset the abnormal
exit before returning to the CMS abnormal termination routine after handling an
abnormal end condition.
Dropping the DB2 Server for VM Resource Adapter Code
When users switch from one program to another, the SQLRMEND EXEC enables
application programs to free the storage used by the resource adapter code. This
EXEC can also be used to perform COMMIT/ROLLBACK processing on
outstanding work before running the next program.
For more information, see “SQLRMEND EXEC” on page 261.
Batch and VSE/ICCF Application Recovery
If a batch application executing in multiple user mode ends without freeing its link
to the DB2 Server for VSE & VM application server, the operating system informs
the application server whether the application ended normally or abnormally. The
126
Database Administration
indication is normal if the application ends with the EOJ macro and the high-order
bit of general purpose register 15 is set to 0. Other conditions indicate an abnormal
end. The database manager automatically commits updates if the termination is
normal, or does a rollback if it is abnormal.
The DB2 Server for VSE application server establishes an STXIT AB exit in the
database partition. The exit attempts recovery and dumps important diagnostic
information if the recovery attempt is not successful. If an application is running
with the TRAP(ON) run-time option of LE/VSE and it did not issue an STXIT AB
MACRO, LE/VSE and DB2 Server for VSE will keep track of calls to and returns
from DB2 Server for VSE. If an abend occurs while the application is running, the
LE/VSE condition manager is informed whether the problem occurred in the
application or in DB2 Server for VSE. If the abend occurs in DB2 Server for VSE,
the LE/VSE condition handler passes the condition back to DB2 Server for VSE.
For information on condition handling with LE/VSE see the DB2 Server for VSE &
VM Application Programming manual. Furthermore, if a single user mode
application issues an STXIT AB macro, the DB2 Server for VSE abend exit is
overridden. Similarly, if the application issues an STXIT PC, then the DB2 Server
for VSE abend exit is overridden for program check conditions. Other abend
conditions are still processed by the application server.
Online Application Recovery
DB2 Server for VSE & VM recovery from failures of online (CICS) transaction is
coordinated with CICS recovery processing.
Consistency among multiple application servers is ensured at CICS synchronization
points, when related data across multiple application servers is kept in a consistent
state. Synchronization points (syncpoints) are points, during the processing of a
transaction, at which updates or modifications to the transaction’s resources are
logically complete and error-free. To take advantage of the CICS syncpoints, the
database manager online support runs as a CICS resource adapter, using the CICS
Application Program interface and User Exit interface. For more information, refer
to the CICS/VSE Customization Guide.
Syncpoints occur during the execution of an application under any of the following
circumstances:
v An application explicitly issues a request for a syncpoint: either the statement
EXEC CICS SYNCPOINT to request a COMMIT of all updates, or EXEC CICS
SYNCPOINT ROLLBACK to request a ROLLBACK. For further information,
refer to the CICS/VSE Application Programming Reference.
v Any termination of a CICS transaction calls the CICS syncpoint manager.
Normal termination results in COMMIT. Abnormal termination results in
ROLLBACK.
v The SQL COMMIT WORK statement causes the DB2 Server for VSE online
support to issue a CICS SYNCPOINT (COMMIT). The SQL ROLLBACK WORK
statement causes a CICS SYNCPOINT (ROLLBACK).
Additionally, when the DB2 Server for VSE online support detects an internal
ROLLBACK of a unit of work, it issues CICS SYNCPOINT (ROLLBACK). (Such
an internal rollback could happen, for example, if the system operator entered
the FORCE command to ROLLBACK an LUW).
As a performance note, it is more efficient for applications to use a CICS syncpoint.
The SQL COMMIT or ROLLBACK calls are less efficient, because they result in a
Chapter 6. Recovering from Failures
127
longer path. A CICS syncpoint is also easier to understand : when it is time to
commit, the application program calls the global synchronization function (CICS
SYNCPOINT [ROLLBACK]).
The assumptions are that individual application programs do not plan to do their
own recovery, and that updates are not to be committed unless normal termination
occurs or the application program explicitly requests a commit.
Notes:
An installation must explicitly request the CICS subsystem to start the syncpoint
protocol by:
1. Generating CICS System Initialization Table (DFHSIT) with DBP=YES. If this is
not done, the CICS process at synchronization points attempts to commit all
updates. Alternatively, DBP=xx may be specified if a suffixed version of the
CICS Dynamic Transaction Backout Program is being used.
2. Ensuring that each online application program that accesses an application
server has Dynamic Transaction Backout set to YES. Do this by specifying
INDOUBT=BACKOUT when defining the transaction.
ISQL Sessions
If an ISQL session ends abnormally, the database manager attempts to notify the
user about the abnormal condition, and leaves the database in a consistent state. In
VM, the database manager issues a ROLLBACK WORK and the session ends.
Control returns to CMS. In VSE or in a VSE Guest Sharing environment, the CICS
syncpoint manager issues a ROLLBACK WORK. All CICS temporary storage for
routines is deleted, and both the ISQL transaction and the CISQ transaction are
terminated, if possible. If the CICS syncpoint manager is in control when the CISQ
transaction abnormal termination occurs, the ISQL transaction abends with the
abend code GCBE. For more information on GCBE, see the DB2 Server for VSE
Messages and Codes manual.
DBS Utility Processing
If the DBS Utility fails to complete the processing of all commands supplied in the
command input, or if it terminates with a return code equal to or greater than 8,
then before the Utility can be restarted the DBS message file listing must be
analyzed to determine the commands that were processed and the error that
occurred. If there are no error messages here that describe the reason for the
failure, then the database machine console messages must be analyzed. After the
error has been corrected, restart the Utility as described below:
v If the DBS command input that failed was processing without any of the
following commands:
SET AUTOCOMMIT ON
SET ERRORMODE OFF
SET ERRORMODE CONTINUE
COMMITCOUNT parameter on an INFILE subcommand
COMMITCOUNT parameter on an INMOD subcommand (VSE Only)
SQL COMMIT WORK statements,
just restart the Utility.
v If it was processing with any of the above commands:
1. Correct any command syntax errors.
2. Remove all commands that were successfully processed and committed.
3. Restart the Utility.
128
Database Administration
If the Utility ends with a return code of 4, this means that all the commands
supplied in the command input were processed successfully but a DBS program
termination error occurred. The Utility does not need to be rerun.
For full descriptions of DBS Utility return codes and error processing, see the DB2
Server for VSE & VM Database Services Utility manual.
Preprocessor
If the preprocessor fails to complete the processing of all source statements
supplied as input, or if it terminates with a return code equal to or greater than 8,
then before running it again you must analyze the source statement listing
produced to determine the errors that occurred. If there are no error messages
there that describe the error condition(s), look at the console messages. After all
source statements and any other errors are corrected, rerun the preprocessor from
the beginning.
If the preprocessor ends with a return code of 1 while the program is being
preprocessed with the BLOCK option, this means that one or more SQL statements
are disqualified for blocking. For further information on blocking, refer to the DB2
Server for VSE & VM Performance Tuning Handbook manual.
If it ends with a return code of 4, then one or more preprocessor warning messages
are contained in the source statement listing. The preprocessor does not have to be
rerun; however, the source statement listing should be checked to insure that the
warning conditions involve objects known to be nonexistent at the time the
preprocessor was run.
If it ends with a return code of 0 and no package was created, then the source
statements read by the preprocessor contained no SQL statements. Here, the
preprocessor must be rerun if the incorrect input source statements were supplied
as input.
Recovery from User Logic Errors
User logic errors are those where the application server carries out the functions as
requested, but the user (or program) determines that the change(s) requested
should not have been made — for example, the wrong table or dbspace may have
been dropped.
Recovery from a user logic error depends on when the error is detected. If it is
detected before the changes have been committed, the application server supports
user (or program) invoked dynamic application backout. A user or program can
take certain actions to back out these changes, depending on the way in which the
application server is being used. ISQL users accomplish this by issuing either the
SQL ROLLBACK WORK statement or the ISQL CANCEL command, or by
responding to ISQL prompts for CANCEL or ROLLBACK. The error handling logic
in application programs can accomplish this by issuing a ROLLBACK WORK
statement. In addition, in VM the invoker of the program can enter either the HX
or SQLHX immediate command (HX causes a rollback and ends the CMS
command; SQLHX causes a rollback, but does not end the CMS command.) If you
have coded your own interactive program to process SQL statements dynamically,
you can also code a cancel exit. This would allow a user of your program to
perform a function similar to the ISQL CANCEL command.
Chapter 6. Recovering from Failures
129
For more information on cancel exits, refer to the DB2 Server for VM System
Administration or DB2 Server for VSE System Administration manual. For user errors
that are detected after changes have been committed, the user has three choices:
1.
Manually reverse the effects of the changes.
This involves issuing the INSERTs, PUTs, UPDATEs, and DELETEs necessary to
cancel all changes. If the committed changes involved definitional change
statements (CREATE, DROP, or ALTER), these too must be manually backed
out, which can be quite a chore since definitional statements do not always
have straightforward cancellation operations. For example, a DROP TABLE
statement would have dropped views and authorizations along with the table;
thus, to reverse its effects would include re-creating the views and regranting
the authorizations.
2.
Reset the data and reenter valid changes.
If a back-up copy of the data exists, it may be simplest to just revert to this
version and then reenter any valid changes made to the data since the copy
was made. Reentering the valid changes can, of course, be as involved as the
effort to back out invalid ones; however, it has the advantage in that it can be
done by reexecuting applications.
The DBS Utility UNLOAD facilities can be used to create back-up copies of
data, and the RELOAD facilities can be used to reset data to a previous state.
The DB2 Server for VSE & VM database archiving support can also be used to
create back-up copies of the entire database and reset it.
3.
Use filtered log recovery to bypass the changes.
Filtered log recovery lets you rollback a committed logical unit of work (LUW).
It sounds like an easy solution, but it must be exercised with extreme care.
When you undo past errors, other database changes may be altered as well:
rows that users thought they had deleted may unexpectedly reappear; the
values in updated rows may change.
If you are using referential integrity, then on completion of the filtered log
recovery you should deactivate and activate your primary and foreign keys to
have the database manager automatically recheck the referential constraints. See
“Altering Referential and Unique Constraints” on page 65.
Filtered log recovery can be used to bypass the operations recorded in the log.
The smallest set of operations you can bypass is all the work done in a single
LUW. You tell the application server which logical units of work to bypass by
supplying EXTEND input file commands. Because you want to bypass work
that has already been committed, you would use the ROLLBACK
COMMITTED WORK command. All the EXTEND input file commands are
described in the DB2 Server for VSE & VM Diagnosis Guide and Reference
manual.
Dynamic Recovery from User Errors
To dynamically recover from user errors, users should take advantage of the
facilities that are provided for detecting error situations and for backing out
changes that should not have been committed.
Backing Out Data During an ISQL Session
When using ISQL, there will be times when you will want to backout an invalid
action: for example, if AUTOCOMMIT is OFF and you entered an SQL statement
that resulted in a negative SQLCODE, or changes to a table that proved to be
incorrect.
Note: You cannot backout changes in a nonrecoverable storage pool.
130
Database Administration
If you detect an error before a change is committed, you can backout the change.
How many changes you can backout depends on whether AUTOCOMMIT is ON
or OFF.
If it is ON, every statement is its own logical unit of work (LUW), and ISQL will
immediately issue a COMMIT WORK after processing the statement. The only
exception is for INSERT, UPDATE, and DELETE statements that affect more than
one row: in that case, ISQL displays a message that gives you the option of
backing out. For all other statements, you can backout the changes before the
statement completes its processing, by:
v Answering CANCEL, ROLLBACK, or NO (based on the reply prompt) to an
ISQL message requesting a reply
v Entering the ISQL CANCEL command when you are informed that the terminal
is free (VSE Only)
v Entering CANCEL if you are prompted to clear the screen or enter CANCEL.
(VSE Only)
v Entering CANCEL or SQLHX if you are prompted to clear the screen (clear the
screen after entering CANCEL). (VM Only)
When using the INPUT command, you can enter the BACKOUT command after an
invalid data row is entered. This deletes all data rows entered since INPUT was
issued, or since the last SAVE command was entered.
If AUTOCOMMIT is OFF, you have control over what is an LUW and when
changes are to be made. When you backout a change, this undoes all changes
made since the beginning of the LUW. You can backout a change by any of these
methods:
v Entering a CANCEL or an SQL ROLLBACK WORK statement
v Answering CANCEL to any ISQL message requesting a reply (and then
answering YES to message ARI7041D)
v Entering the ISQL CANCEL command when you are informed that the terminal
is free (VSE Only)
v Entering CANCEL if you are prompted to clear the screen or enter CANCEL.
(VSE Only)
v Entering CANCEL or SQLHX if you are prompted to clear the screen (clear the
screen after entering CANCEL). (VM Only)
Note: In VM, when you enter a CANCEL command, ISQL does ROLLBACK
WORK RELEASE processing. Any explicit connection you have made will
be released. You should reissue the CONNECT statement if you want to
explicitly connect to ISQL again.
Backouts Initiated by Application Programs
An application program may begin a backout if the application server shows that
there is an error, or if the program detects something wrong internally. To detect
and handle errors, the program should have the WHENEVER statement coded into
it. It can then determine whether to continue or to stop execution when an error
occurs.
All operations against recoverable storage pools are atomic, except in SUM
NOLOG mode. That is, either the operation will be completed successfully, or any
changes made by the operation will be reversed automatically. Changes made by
previous operations in the same LUW are not affected. The application is free to
either continue working within the same LUW, to COMMIT the changes made so
Chapter 6. Recovering from Failures
131
far, or to ROLLBACK the LUW. Some errors, such as deadlock, still require the
entire LUW to be rolled back. The status of the LUW is indicated in SQLWARN6 in
the SQLCA.
When running with LOGMODE=N, atomicity of operations is enforced by rolling
back the current LUW to avoid partial completion of an operation. For operations
on data in nonrecoverable storage pools, there is no support for atomicity of
operations.
Note: When blocking, the database manager does not insert rows into the database
until the block is full and it does not notify your program of an insert error
until the PUT that fills a block is run. To determine when (or if) rows are
actually inserted into the database, your program should examine
SQLERRD(3) in the SQLCA when doing PUTs.
To rollback work when an SQL error is encountered, code a ROLLBACK WORK
statement in the program, and use a WHENEVER SQLERROR GO TO statement to
cause a branch to the ROLLBACK statement when there is an SQL error. After the
program issues a ROLLBACK WORK, it may continue processing more SQL
statements without the previous error affecting their outcome.
If the application programmers do not wish to worry about setting up
error-recovery logic in their programs, they can enable them to stop executing
when an SQL error is detected. This is done by coding WHENEVER SQLERROR
STOP (COBOL, COBOL II, PL/I) or WHENEVER SQLERROR GOTO. When this is
coded, the database manager will issue either a CANCEL (in VSE) or a CMS
DMSABN macro (in VM) for the application when any command results in a
negative SQLCODE, which results in a ROLLBACK WORK for any outstanding
LUW within the application program. Alternatively, the application programmer
could code a WHENEVER SQLERROR GOTO and branch to a label or routine to
perform the ROLLBACK WORK and end the program.
If the program detects an internal error and wishes to discontinue processing, it is
probably best to issue a ROLLBACK WORK (if possible) before terminating it. This
can be done by coding a ROLLBACK WORK statement in the application and
branching to it when an internal program error is detected. After the ROLLBACK
WORK statement is run, the program can stop, or continue if desired.
In VM, once a program is running, you can stop it by using the immediate
commands HX or SQLHX, both of which cause a ROLLBACK WORK for the
current LUW. You might want to do this if, for example, you start the program and
then realize you have provided the wrong inputs. The difference between the two
commands is that HX causes an end to the CMS command, while SQLHX does
not. Thus, the choice of command is a matter of convenience. For example, issuing
HX from ISQL both rolls back the current LUW and ends the ISQL session, so the
user must reinitialize ISQL to continue processing; issuing SQLHX causes the LUW
to be rolled back but the ISQL session continues.
Note: The ISQL CANCEL command and the more general SQLHX command have
equivalent functions. The CANCEL command, however, does not work for
user programs. In addition, CANCEL, SQLHX, and HX do not work if you
have processed the SQLINIT command with the SYNCHRONOUS(YES)
option.
132
Database Administration
Selective Recovery from User Data Errors
It is a good idea to maintain backup copies of specific tables or dbspaces, so that
they can be reset in case of major errors.
Periodic Backup of Critical Data
Individual tables or entire dbspaces should be periodically unloaded to either a
SAM tape or DASD file (in VSE), or to a tape or CMS minidisk file (in VM) with
the DBS Utility UNLOAD command.
Multiple UNLOAD commands can be put in a single DBS SYSIPT (VSE), or SYSIN
(VM), input file. You might establish one such job stream for periodic back-up of
users’ PRIVATE dbspaces, and others for periodic back-up of selected application
production data. Different types of data would typically have different back-up
schedules. For example, production data would probably be backed up more
frequently than query user data. Some DB2 Server for VSE data, such as certain
data extracted from DL/I, would not require back-up; that is, the DL/I copy of the
data is sufficient back-up.
Note: You cannot use the DBS Utility UNLOAD facilities to back up data in the
system dbspaces (SYS000n). The catalog tables and packages cannot be reset
by DBS RELOAD processing.
Resetting Data Using DBS RELOAD Processing
When data is backed up, you can recall the backup copy if necessary. Data that
was backed up with the UNLOAD TABLE command is recalled with the RELOAD
TABLE command; data that was backed up with the UNLOAD DBSPACE
command can be recalled with either RELOAD DBSPACE (to reset the entire
dbspace) or with RELOAD TABLE (to recall selected tables in the dbspace). Often,
user data errors that have been introduced into the database are isolated to just a
few tables; thus, even if the data had been unloaded with an UNLOAD DBSPACE
command, you would use RELOAD TABLE to reset it.
When a table is RELOADed with the NEW option, a new table is created and data
reloaded. None of the primary keys, indexes, unique constraints, referential
constraints or field procedures are reproduced in the new table.
When you use the RELOAD command with the PURGE option to replace the
contents of a table, the DBS Utility does the following to the table being replaced:
1. Drops the CLUSTERING index (if one exists).
2. Deactivates the active primary key (if one exists).
3. Deactivates all active foreign keys.
4. Deactivates all unique constraints.
5. Drops all other indexes.
6. Deletes all rows from the table.
7. Reloads data.
8. Recreates the CLUSTERING index previously dropped.
9. Activates the primary key previously deactivated.
10. Activates the unique constraints previously deactivated.
11. Recreates any remaining indexes previously dropped.
As a result, the CLUSTERING index will be preserved, as well as the primary key,
foreign keys, unique constraints, and indexes existing on the table at the time of
the RELOAD/PURGE command. If no CLUSTERING index exists, then the
primary key becomes the CLUSTERING index. There is no requirement to order
the reloading of tables, because all referential constraints are inactive while the
data is inserted.
Chapter 6. Recovering from Failures
133
Consider running the DBS Utility in single user mode with LOGMODE=N when
resetting data through RELOAD DBSPACE or RELOAD TABLE processing. This
will eliminate any log overflow conditions that result from the table row deletes
and inserts performed by RELOAD processing with the PURGE option. If you use
log archiving, however, remember that switching the log mode disrupts the
continuity of the log.
Running the DBS Utility with LOGMODE=N is shown in Figure 26 and Figure 27.
If the data resides in a nonrecoverable storage pool, there is no need to use
LOGMODE=N, because logging is automatically suppressed for nonrecoverable
data.
// JOB RESTORE DBSPACE
// EXEC PROC=DBNAME01
// EXEC PROC=ARIS71PL
// TLBL DUMPTAP,
// ASSGN SYS004,
// EXEC ARISQLDS,SIZE=AUTO,PARM=’STARTUP=L,SYSMODE=S,LOGMODE=N,DUALLOG=Y’,
ALTLOG=Y’
// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=N,PROGNAME=ARIDBS’
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;
RELOAD DBSPACE (SQLDBA.EXAMPLE) PURGE INFILE(DUMPTAP PDEV(TAPE)
/&
Figure 26. Resetting a DB2 Server for VSE DBSPACE from a Back-up Copy
Notes:
1. The job control here assumes that the DB2 Server for VSE database was last
shut down with the ARCHIVE, UARCHIVE, or LARCHIVE option (depending
on whether you use LOGMODE=A or LOGMODE=L).
2. The first execution of the ARISQLDS exec starts the DB2 Server for VSE system
in single user mode (SYSMODE=S), and does a COLDLOG (STARTUP=L) to
redefine the log data sets. This step switches from LOGMODE=A or
LOGMODE=L to LOGMODE=N, and is not needed unless you run with
LOGMODE A or L. Omit the parameters DUALLOG=Y and ALTLOG=Y if you
are not using dual logging or alternate logging.
3. The second execution of the ARISQLDS exec runs the DBS Utility with the
input shown. This step RELOADs all the table data into the DBSPACE named
SQLDBA.EXAMPLE from a tape file (filename=DUMPTAP) created by the DBS
Utility UNLOAD DBSPACE command.
For further information about switching log modes, see the DB2 Server for VSE
System Administration manual.
EXEC SQLLOG DB(dbname)
FILEDEF DUMPTAP TAPn (RECFM VBS BLOCK 800
EXEC SQLDBSU DB(dbname) IN(TERM) LOGMODE(N)
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;
RELOAD DBSPACE (SQLDBA.EXAMPLE) PURGE INFILE(DUMPTAP)
COMMIT WORK RELEASE;
Figure 27. Resetting a DB2 Server for VM Dbspace from a Back-up Copy
Notes:
1. The CMS commands here assume that the application server was last shut
down with the ARCHIVE, UARCHIVE, or LARCHIVE option (depending on
134
Database Administration
whether you use LOGMODE=A or LOGMODE=L). This ensures that you will
be able to restore the database if a DASD fails.
2. The first run of the DB2 Server for VM program (by way of the SQLLOG
EXEC) does a COLDLOG, which is necessary to switch from LOGMODE=A or
L to LOGMODE=N. If you do not run with LOGMODE=A or L, you do not
need to run SQLLOG to do a COLDLOG.
3. Respond N for NO to message ARI0688D, which asks whether you want to
FORMAT and RESERVE the log minidisk(s).
4. The second run processes the DBS Utility with the input shown, to RELOAD
all the table data in the dbspace named SQLDBA.EXAMPLE from a tape file
(ddname=DUMPTAP) created by DBS Utility UNLOAD DBSPACE command
processing. CMS FILEDEF commands direct the DBS input to the terminal and
DUMPTAP to the tape.
5. After reloading the table, switch back to LOGMODE=A or L and create another
database archive.
Database Recovery from User Logic Errors
To protect the entire database from user logic errors, use the archiving and
COLDLOG facilities of the database manager. These facilities are required to
protect the system catalog tables and the package dbspaces. Backup copies of the
system dbspaces (SYS000n) made by DBS Utility UNLOAD command cannot be
used to reset catalog tables or packages to a previous state.
Creating a Proper Back-up Copy of the Database
The back-up copy of the database can be either a database archive or a database
archive and subsequent log archives.
You can create the database archive by using a variety of facilities. You must,
however, create the archive when no user is accessing the database. Create the
archive by using either the SQLEND ARCHIVE or SQLEND UARCHIVE
command. Because no user is accessing the database when the database archive is
taken, no incomplete changes are recorded in the database archive.
If you use log archiving, you can think of the last back-up copy as being the last
database archive plus all subsequent log archives. Log archives do not record
changes by incomplete logical units of work.
Note: If you are using the CICS subsystem and it ends abnormally, or the
connections from the online resource adapter to the application server are
ended by a CIRR QUICK, or the online adapter is ended by a CIRT QUICK
or CIRR QUICK command, an exception can occur: that is, incomplete
changes can be in the archive copy of the database if there are CICS
transactions that are left in-doubt when the SQLEND archive is taken. To
avoid this condition, enter a SHOW ACTIVE command to see if there are
any LUWs that are marked as being in-doubt. If there are, enter the
necessary FORCE commands to complete them before you enter the
SQLEND ARCHIVE command.
You can create a proper back-up copy even if you have been running the database
manager with LOGMODE=Y. However, if you create a database archive by using
SQLEND parameters when LOGMODE=Y, you must follow the steps outlined in
the DB2 Server for VM System Administration or DB2 Server for VSE System
Administration manual to restart the database manager with LOGMODE=Y, because
the log mode will automatically change to A when taking the database archive.
Chapter 6. Recovering from Failures
135
Resetting the Database to a Previous Copy
If you are restoring from a database archive without using subsequent log archives,
you can reset the database to any previous database archive copy, not just the
latest one.
To reset a DB2 Server for VSE database to a previous copy generated by an
SQLEND command, run COLDLOG before restoring the database from the archive
copy. This reformats the log so that changes since the archive was taken are not
applied again.
To reset a DB2 Server for VM database, run the SQLLOG EXEC (omitting the
LOG1, LOG2, ALTLOG1, and ALTLOG2 parameters) to reformat the log with a
COLDLOG. Respond “NO” to message ARI0688D (for single logging) or ARI6129D
(for dual or alternate logging). When you respond NO, the database manager
reformats the log such that changes since the archive was taken are not applied
again.
If you are restoring from a database archive and subsequent log archives, no
COLDLOG is required. When the database is restored, the logs are restored in
sequence. You are prompted to continue the log restore before processing each log
archive. You can end the restore process at any log archive by responding “END
RESTORE” to the appropriate prompt.
When resetting the database to a back-level copy, even if you are using subsequent
log archives, you should be aware of the following:
v
The database archive copy includes a copy of the database directory, but the
database manager does not recognize any ADD DBSPACE and ADD DBEXTENT
operations which were done after the database archive. To reestablish these
dbspaces and dbextents in a VSE system, you must rerun the appropriate ADD
DBSPACE and ADD DBEXTENT operations. You can determine how many
dbextents exist in the restored back-level database by using the SHOW
DBEXTENT operator command. (Add the values in the NO._OF_EXTENTS
column.) You can determine the numbers of public and private dbspaces in the
restored database by querying the SYSTEM.SYSDBSPACES catalog table. For
more information on the catalog tables, see the DB2 Server for VSE System
Administration manual or the DB2 Server for VM System Administration manual. In
VM, if you want to reestablish the dbspaces added after the database archive
was created, you must rerun the SQLADBSP EXEC. For more information, see
the DB2 Server for VM System Administration manual.
Any dbextents added to the database (by an ADD DBEXTENT operation) after
the database archive was created do not exist in the archive copy of the
database. In VM, the CP LINK and CMS FILEDEF commands for these
dbextents are present in the resid SQLFDEF file (on the DB2 Server for VM
production minidisk) for the database. To redefine these dbextents in the DB2
Server for VM database, perform the following procedure:
1. Create an ADD DBEXTENT card image input CMS file with a line entry for
each added dbextent. Each entry should contain the dbextent number and
the storage pool number for the dbextent.
Note: The SHOW DBEXTENT operator command tells you how many
dbextents are defined in the database.
2. Enter a CMS FILEDEF command with ddname SYSIN for the CMS input file:
FILEDEF SYSIN DISK fn ft fm
136
Database Administration
3. Run the SQLSTART EXEC with PARM(SYSMODE=S,STARTUP=E...) to
redefine the dbextents in the database.
v The database archive of the directory shows the DUALLOG or ALTLOG value in
effect when the database archive was created. The database archive also shows
the size of the logs when the archive was taken. You can reset the DUALLOG or
the ALTLOG value and the size of the logs by doing a COLDLOG operation to
reformat the logs after the database is restored.
In VSE, do a COLDLOG by specifying STARTUP=L and the DUALLOG or
ALTLOG value that you want. For more information about DUALLOG, see the
DB2 Server for VSE System Administration manual.
In VM, do a COLDLOG by running the SQLLOG EXEC without the LOG1,
LOG2, ALTLOG1, and ALTLOG2 parameters. In this situation, the log minidisk
is already reserved and formatted; only the directory needs to be updated.
Respond “NO” to message ARI0688D (for single logging) or ARI6129D (for dual
or alternate logging), which prompts you to FORMAT and RESERVE the log
minidisk (or minidisks).
This final consideration applies when you restore a database archive without
applying subsequent log archives:
v All data (including the catalog table information) is reset to the database archive
copy. Any preprocessing, data definitions, grants, revokes, and stored queries or
routines established after the database archive was created are lost. The database
may not be consistent with other facilities on your system. In particular, it may
not be consistent with your CICS or DL/I data, and the packages may not reflect
the SQL application programs you installed on your system after the database
archive was created.
Resetting the Database without Reformatting the DB2 Server for
VSE Data Sets
A database restore (STARTUP=R) reformats the VSAM database data sets before
the data is reloaded. Reformatting the data sets is necessary after a data set is
replaced (for example, when restoring because of a media failure or database
reconfiguration). Reformatting the data sets is not necessary when none of the
database data sets is being replaced.
When restoring the database to a previous level to recover from a user logic error,
you usually do not change the data sets. To save processing time, use STARTUP=F
(fast restore) when you have not replaced any of the database data sets. The
STARTUP=F processing does not format the VSAM data sets: it loads the data.
Eliminating the formatting of the data sets significantly reduces the restore time.
Chapter 6. Recovering from Failures
137
|
||
|
|
|