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

Maintaining Dbspaces

v Altering the design of a dbspace

When you created a dbspace, you specified the following parameters for it: its

potential size (in pages), its type (PUBLIC or PRIVATE), its storage pool

assignment (STORPOOL), the number of pages for its header (NHEADER), the

percentage of each page reserved for updates that cannot be placed in the

original location (PCTFREE), the number of pages reserved for indexes

(PCTINDEX), and the size of the locks (LOCK).

As requirements change, you may need to change some of these settings. You

can change the PCTFREE and LOCK parameters with the ALTER DBSPACE

statement. If any of the other parameters need to be changed, you will have to

acquire a new dbspace (which satisfies your new requirements), and move all

the tables from the old dbspace to the new one.

v Reorganizing a dbspace to free storage pool pages

As part of maintaining your dbspaces, you may have to reorganize it to release

pages back to a storage pool.

v Removing dbspaces

If a dbspace is no longer required, you can remove it and its contents by using

the DROP DBSPACE statement.

v Using VSAM (VSE only)

There are VSAM restrictions when managing storage.

Reorganizing Catalog Table Indexes

The catalog tables have indexes to improve the speed of access. Occasionally, you

should reorganize these indexes. See “Reorganizing Indexes on the Catalog Tables”

on page 77.

Database Administration

Maintaining Tables

After designing and creating a table, you may have to load data into it, copy it,

move it from one dbspace to another, move data in it from one application server

to another, change an aspect of its design, or remove it from the database.

Loading Data into Tables

This section reviews the possible ways to load data into tables. Many of these

methods use the Database Services Utility commands: for more information on

these commands, refer to the DB2 Server for VSE & VM Database Services Utility

manual.

Loading Data in VM Using the DBS Utility

Interactively: You can load data into tables interactively through the DBS Utility.

To do this, invoke the utility so the terminal controls file input (SYSIN). You can

then either enter multiple INSERT statements, or execute the DBS Utility

DATALOAD TABLE command using the INFILE (*) subcommand.

From a CMS File: The DBS Utility DATALOAD TABLE command will accept

input data records in a user-created CMS file. One or more tables can be loaded

during a single pass of the data records. The existing data in the tables loaded with

this method are not affected. Rows are added to a table through the PREPARE,

OPEN, PUT, and CLOSE facilities of SQL.

From a Virtual Reader File: The DBS Utility DATALOAD TABLE command will

also accept input data records in a CMS virtual reader file with no header. One or

more tables can be loaded during a single pass of the data records. The existing

data in the tables loaded with this method are not affected. Rows are added to a

table through INSERT statements executed using the PREPARE and EXECUTE

facilities of SQL.

Refer to the DB2 Server for VSE & VM Database Services Utility manual for more

information.

Loading Data Using the DBS Utility in VSE/ICCF

To load data into a table from data records entered from a terminal, as an

alternative to entering multiple INSERT statements, users can use the DBS Utility

under VSE/ICCF in conversational mode. To initiate this, enter the following

VSE/ICCF control statements:

/LOAD ARIDBS

/OPTION GETVIS=AUTO

/DATA INCON

In response to the prompt to ENTER DATA, the appropriate series of SQL

statements or DBS Utility DATALOAD TABLE commands must be entered. After a

DATALOAD TABLE command the user must enter the INFILE (*) subcommand to

initiate input data record processing and the ENDDATA subcommand to end it. An

outline of the interactive terminal input is:

CONNECT userid IDENTIFIED BY password;

DATALOAD TABLE (table-name)

column-name1

1-5

column-name2

6-7

INFILE (*)

data record

Chapter 3. Maintaining Your Database

data record

ENDDATA

These commands are described in the DB2 Server for VSE & VM Database Services

Utility manual.

Each record (row) is entered in a fixed format as defined by the column

specifications in your DATALOAD command. In this example, the user enters

column 1 data into typing positions 1-5 of the command line, column 2 data into

positions 6-7, and so on.

Do not put quotation marks around character data, and do not use commas to

separate data values. Such punctuation can be used outside the data positions of

the command line defined by the column specifications of the DATALOAD

command.

As an alternative to entering each input data record interactively, the user can

embed DBS Utility commands and data records in the VSE/ICCF control

statements. An outline of loading a table under VSE/ICCF in a nonconversational

manner is:

/LOAD ARIDBS

/OPTION GETVIS=AUTO

/DATA

CONNECT userid IDENTIFIED BY password;

DATALOAD TABLE (table-name)

column-name1

1-5

column-name2

6-7

INFILE (*)

data record

ENDDATA

Loading Data from a Terminal Using ISQL INPUT

The ISQL INPUT statement enables a user to enter multiple rows of data into a

table. The table name and (optionally) the column names need to be entered only

once. The column names, along with their data types, are then displayed in the

order that the data must be entered, and the user can then enter data one row at a

time.

For data that is similar, the user can use the PF12 RETRIEVE function. That is, the

user can retrieve the previous data row entered, and then type over the fields that

are different. This can save keystrokes.

Data entered with an INPUT statement is not stored in the table until the INPUT

statement is ended by an END statement. ISQL will issue an INSERT statement for

every row entered, using the PREPARE and EXECUTE facilities of SQL. However,

before the INPUT statement is ended, the data can be committed or backed out by

the statement:

SAVE — Stores all data entered since the last SAVE statement. If no SAVE

statement has been issued, it commits all the data since the start of the INPUT

statement.

Database Administration

BACKOUT — Deletes all data entered since the last SAVE statement. If no

SAVE statement has been issued, it deletes all the data since the start of the

INPUT statement.

CANCEL — Performs a BACKOUT and also ends the INPUT statement.

Remember that the AUTOCOMMIT mode affects the processing of the SAVE,

BACKOUT, and CANCEL statements. For additional information on the ISQL

INPUT, SAVE, and BACKOUT statements, refer to the DB2 Server for VSE & VM

Interactive SQL Guide and Reference manual.

Loading Data from Sequential Files in VSE

The DBS Utility DATALOAD TABLE command accepts SYSIPT data records or

data records contained in a user-created sequential file. One or more tables can be

loaded during a single pass of the data records. The existing data in the tables

loaded with this method is not affected. The DATALOAD TABLE processing adds

rows to a table through the PREPARE, OPEN, PUT, and CLOSE facilities of SQL.

Loading Data from VSAM Files

A VSAM file can be converted to either of the following:

v a sequential (SAM) file using the VSE/VSAM Access Methods Services REPRO

command

v a CMS or tape file through the VM VSE/VSAM Access Methods Services

(AMSERV command) using the REPRO control statement.

This sequential file can then be identified as the input data file to DBS Utility

DATALOAD TABLE processing.

Note: The VSAM REPRO command should never be used to copy the DB2

database itself.

Loading Data from Other Tables

Data can be copied into a table from other tables by using the following methods:

v An INSERT with Subselect statement executed through ISQL, the DBS Utility, or

a user program. An INSERT with Subselect copies one or more rows which are

selected or computed from other tables into a table.

v The execution of a DBS UNLOAD and RELOAD command series. This

technique allows data to be copied from tables in the same or different databases

but only a complete replacement of the data in the target table is possible.

v The execution of a DBS DATAUNLOAD and DATALOAD command series. This

technique allows data to be copied from tables in the same or different

application servers, and allows more selectivity than the UNLOAD/RELOAD

sequence. This is useful when you want to copy only parts of tables.

All of these techniques allow the source of the data to be copied to be identified by

a view that is defined on one or more tables. A view can be used to identify the

target table if the view definition meets the requirements defined for inserting

rows into a view.

If referential constraints are in place on tables in which you wish to load data, you

should consider whether you would like to enforce constraints while the data is

loading or after it is loaded. See “Enforcing Referential Constraints” on page 68 for

more information.

Chapter 3. Maintaining Your Database

Copying Tables

To make a copy of an existing table, use the DBS Utility UNLOAD and RELOAD

commands.

Example

A user with the user ID SMITH has the SELECT privilege on the

SQLDBA.EMPLOYEE table. To make a copy of this table, to be called

SMITH.EMPLOYEE, in the PRIVATE dbspace SMITHDB, enter the following

commands either in a CMS file called CONTROL DBSINPUT A or in the

appropriate job control:

CONNECT SMITH IDENTIFIED BY SMITHPW;

UNLOAD TABLE (SQLDBA.EMPLOYEE) OUTFILE(TEMPFIL);

RELOAD TABLE (EMPLOYEE) NEW (SMITHDB)

INTABLE (SQLDBA.EMPLOYEE) INFILE(TEMPFIL);

To execute these commands in VM, invoke the DBS Utility, as follows:

FILEDEF TEMPFIL DISK MYDATA MYFILE A4 (RECFM VBS BLOCK 800

SQLDBSU SYSIN(CONTROL DBSINPUT A) SYSPRINT(LIST DBSLIST A)

The RELOAD statement creates tables without constraints, losing all referential

constraints on the table you are copying. You must reinstate referential constraints

later with the ALTER TABLE statement. See “Altering Referential and Unique

Constraints” on page 65.

The RELOAD statement with the ’NEW’ parameter recreates the table without field

procedures. Instead of reloading the table using the ’NEW’ parameter, recreate the

table to include field procedures and reload the table using the ’PURGE’

parameter.

Moving Tables from One Dbspace to Another

To move a table from one dbspace to another, you must first unload it using the

DBS UNLOAD command, drop it from the database, then reload it into the new

dbspace. When a table is dropped, all indexes, privileges, views, primary and

foreign keys, and unique constraints for it are removed, and must be

re-established.

As well, if a table has field procedures associated with it, the table should be

dropped and recreated to include the field procedures and reloaded using the

’PURGE’ parameter.

Example

User SMITH has a table (called SMITH.MYTABLE) that he wishes to move from

the SMITH.PERSONAL dbspace to the SMITH.SECRET dbspace.

Enter the following commands in either a CMS file called CONTROL DBSINPUT

A, or inside the appropriate job control:

CONNECT SMITH IDENTIFIED BY SMITHPW;

UNLOAD TABLE (SMITH.MYTABLE) OUTFILE(TEMPFIL);

DROP

TABLE SMITH.MYTABLE;

RELOAD

TABLE (SMITH.MYTABLE)

NEW (SMITH.SECRET) INFILE(TEMPFIL);

In VM you run these commands by invoking the DBS Utility, as follows:

FILEDEF TEMPFIL DISK MYDATA MYFILE A4 (RECFM VBS BLOCK 800

SQLDBSU SYSIN(CONTROL DBSINPUT A) SYSPRINT(LIST DBSLIST A)

Database Administration

Merging Data from Multiple Tables

It may be necessary to combine all columns or a subset of the columns from

different tables into a new table. You can do this through ISQL or the DBS Utility

using the following procedure:

1. Create the new table with a CREATE TABLE statement.

2. Insert rows into the new table by selecting columns from the source tables with

an INSERT with Subselect statement.

3. Execute an UPDATE STATISTICS statement against the new table.

4. Create the required indexes for the new table with CREATE INDEX statements.

5. Create the required views on the new table.

6. Grant the required authorizations on the new table and views.

7. If necessary, redefine the views on the old tables to eliminate access to the

columns merged into the new table.

To identify authorizations and views on the old tables, you can query the system

catalog with a SELECT statement entered through ISQL or the DBS Utility. The

following tables contain information pertinent to this task:

v SYSTEM.SYSUSAGE identifies the base table on which a view is defined

v SYSTEM.SYSVIEWS identifies the view definitions

v SYSTEM.SYSTABAUTH identifies the users who have privileges to access tables

and views.

Example

To identify the base tables for the view ORGANIZATION, enter the following

query:

SELECT BNAME FROM SYSTEM.SYSUSAGE

WHERE DNAME = ’ORGANIZATION’

To identify the view definitions, enter the following query:

SELECT VIEWTEXT FROM SYSTEM.SYSVIEWS

WHERE VIEWNAME = ’ORGANIZATION’

To identify the users who have privileges to access the view or its base tables,

enter the following query:

SELECT GRANTEE, STNAME FROM SYSTEM.SYSTABAUTH

WHERE TTNAME = ’ORGANIZATION’

If a view is defined for all the columns required in the new table, steps 1, 2, and 3

(needed to merge data from multiple tables) can be replaced by the following:

1. Enter the DBS Utility UNLOAD command to unload the view.

2. Enter the DBS Utility RELOAD command to create and load the new table.

3. Process an UPDATE STATISTICS statement for the new table, if necessary. By

default, this statement is performed for each table loaded during RELOAD

TABLE command processing. For more information, see the DB2 Server for VSE

& VM Database Services Utility manual.

Examples

In VM: Include the following SQL statements and DBS Utility commands within

your DBS Utility control file to perform the above task:

CONNECT userid IDENTIFIED BY userpw;

UNLOAD TABLE creator.viewname OUTFILE(DUMPFIL);

RELOAD TABLE creator.newtablename NEW INFILE(DUMPFIL);

Chapter 3. Maintaining Your Database

Invoke the DBS Utility, as usual, to process the above statements and commands.

In VSE: Use the following job control commands, SQL statements, and DBS Utility

commands to perform the above task:

// JOB MERGE DATA

// EXEC PROC=DBNAME01

// ASSGN SYS005,...

// ASSGN SYS004,...

// TLBL DUMPFIL,

CONNECT userid IDENTIFIED BY userpw;

UNLOAD TABLE creator.viewname OUTFILE(DUMPFIL)

RELOAD TABLE creator.newtablename NEW INFILE(DUMPFIL)

See the DB2 Server for VSE & VM Database Services Utility manual for details.

Altering the Design of a Table

If you want to change the design of a table after it has been created, use the SQL

ALTER TABLE statement. This will not change the data in the table; only its

specifications. You can:

v Add a column to a table

v Add or drop a primary key, a foreign key, or a unique constraint.

When you alter a table, information in the system catalog about it is also changed.

For example, when you add a new column to a table, SYSTEM.SYSCOLUMNS is

changed to record it, and the field in there that records the number of columns is

increased by one.

Authorization

To alter a table, you must have the ALTER privilege on it, and if the operation

involves a primary key you must have the ALTER privilege on all dependent

tables as well. If the operation involves a foreign key, you must have the

REFERENCES privilege on the parent table.

You can alter any table if you have DBA authority.

You cannot delete a column, change the name of a column, change the data type of

a column, or add or change a field procedure for a column for existing tables using

the ALTER TABLE statement. To do these operations, you must drop the existing

table and re-create it.

Example

There are two ways to change the data type of the DEPTNAME column of the

DEPARTMENT table from VARCHAR(36) to VARCHAR(40):

v Create a new table (DEPT) with the required column definitions, and copy data

to it.

CREATE TABLE DEPT

(DEPTNO

CHAR(3)

NOT NULL,

DEPTNAME VARCHAR(40)

NOT NULL,

MGRNO

CHAR(6)

ADMRDEPT

CHAR(3)

NOT NULL,

PRIMARY KEY (DEPTNO)

)

INSERT INTO DEPT SELECT * FROM DEPARTMENT

Database Administration

Indexes, views, and privileges have to be reestablished for the new table DEPT;

only the data is copied from the DEPARTMENT table. Also, all applications that

used the original table must be changed to reflect the new table name, then

re-preprocessed.

v DATAUNLOAD the contents of the DEPARTMENT table to a flat file, drop the

table, re-create it with the new data type definition of the DEPTNAME column,

then DATALOAD the contents of the flat file back into the DEPARTMENT table.

For details on DATAUNLOAD and DATALOAD, see the DB2 Server for VSE &

VM Database Services Utility manual.

Adding a New Column

When you add a column to an existing table, it is placed on the far right.

The physical records are not actually changed until users insert values in the new

column, so access time to the table is not affected immediately. After values are

inserted, however, this could impact performance by forcing rows onto another

physical page. To avoid that situation, define enough free space on each page

ahead of time.

You cannot define the new column as NOT NULL; it must allow NULL values.

Example

Add a new column to the table DEPARTMENT, containing a location code for the

department. The column name is LOCNCODE, and its data type is CHAR (4).

ALTER TABLE DEPARTMENT

ADD LOCNCODE CHAR (4)

Table 15 shows part of the original table.

Table 15. Before Adding a New Column to a Table

DEPTNO

DEPTNAME

MGRNO

ADMRDEPT

A00

SPIFFY COMPUTER SERVICE DIV.

000010

A00

B01

PLANNING

000020

A00

C01

INFORMATION CENTER

000030

A00

Table 16 shows the table after adding the new column and updating a location

code in the third row.

Table 16. After Adding a New Column to a Table and Updating a Row

DEPTNO

DEPTNAME

MGRNO

ADMRDEPT

LOCNCODE

A00

SPIFFY COMPUTER SERVICE

000010

A00

DIV.

B01

PLANNING

000020

A00

C01

INFORMATION CENTER

000030

A00

B126

Altering Referential and Unique Constraints

If you plan to let the database manager enforce referential integrity in a set of

tables, see “Considerations for Referential Integrity when Creating Tables” on page

39 and “Specifying a UNIQUE Constraint” on page 38.

The following terms are used in the discussion of the ALTER TABLE statement:

Chapter 3. Maintaining Your Database

Inactive Key or Constraint

A primary key, a foreign key, or a unique

constraint that has been made inoperable by the

ALTER TABLE ... DEACTIVATE statement. Neither

referential nor unique constraints are enforced until

the related keys are activated.

Implicitly Inactive Key

A foreign key that is not explicitly inactive, but

references a table with an inactive primary key. A

referential constraint is not enforced until the

related primary key is activated.

Inactive Table

A table that contains an inactive or implicitly

inactive key, or contains an active primary key

referenced by an inactive foreign key. This limits

access to the table to the creator or a DBA, and

allows deferred constraint enforcement.

Dependently Inactive Table A dependent table or foreign key that has been

flagged as inactive because the primary key of its

parent table has been deactivated.

Table 17 is a summary of the authorization required to alter referential constraints.

Table 17. Authorization Required when Altering Referential Constraints

Statement

Parent Table

Dependent Table

ADD

Primary Key

Foreign Key

DROP

Primary Key

A, R(1)

Foreign Key

ACTIVATE

Primary Key

A, R(1)

Foreign Key

All

A, R(1)

DEACTIVATE

Primary Key

A, R(1)

Foreign Key

All

A, R(1)

Note: ALTER privilege is required when A appears. REFERENCES privilege is required

when R appears, and (1) applies when a dependent table exists.

Considerations When Adding Keys or Constraints

The following restrictions apply when you add a primary key, a foreign key, or a

unique constraint to an existing table:

v The columns named for the key being added must exist.

v If adding a primary key, there should be no existing primary key on the table.

v If adding a primary key or a unique constraint there must not be duplicate

values in the specified columns.

v When adding a foreign key:

- The constraint name must not already exist.

- If the key columns are identical to those of another foreign key that references

the same parent table, a warning is issued and the foreign key is created.

Database Administration

v You can use only one FOREIGN KEY clause in each ALTER TABLE statement; if

you want to add two foreign keys to a table, you must execute two statements.

v If you add a foreign key, the primary key of the parent table must already exist.

v To add a foreign key, you must have REFERENCES privilege on the parent table

and ALTER privilege on the dependent table.

v If adding a foreign key, the foreign key must not cause a table to be

delete-connected to another table through multiple paths with different delete

rules or with a delete rule of SET NULL.

v A referential cycle with two or more tables must not cause a table to be

delete-connected to itself. For further information on delete-connected tables,

refer to “Restrictions on Keys and Referential Constraints:” on page 40.

For further information on referential integrity, refer to “Elements of Referential

Integrity” on page 6.

Considerations When Dropping a Primary or Foreign Key

The following restrictions apply when you drop a primary key or a foreign key

from an existing table:

v When you drop a foreign key, the corresponding referential relationship is also

dropped.

v To drop a foreign key, you must have REFERENCES privilege on the parent

table and ALTER privilege on the dependent table.

v When you drop a primary key, all the referential relationships in which the table

is a parent are also dropped.

v You must have ALTER privilege on any dependent tables.

v The dependent tables no longer have foreign keys.

v The unique index (created to enforce uniqueness in the primary key) is dropped.

In both situations, you should consider carefully the effects on your application

programs of dropping keys. The primary key of a table is intended to serve as a

permanent, unique identifier of the occurrences of the entities it describes, and

quite likely some of your programs depend on that. The foreign key defines a

referential relationship and a delete rule, and without it your programs must

enforce the constraints.

Considerations When Activating Keys and Constraints

Primary Key: To activate a primary key you must have ALTER privilege on the

parent and dependent tables and REFERENCES privilege on all dependent tables.

If any dependent foreign keys were deactivated implicitly when the primary key

was made inactive, they will be verified against the primary key. If the primary

key index can be created successfully and the dependent foreign key values are

found in the parent table’s primary key, then the primary key and the dependent

foreign keys will be activated. If any of these processes fail, none of the keys will

be activated.

Activating the primary key will neither verify nor affect the status of any

dependent foreign keys that were deactivated explicitly with the ALTER TABLE

table-name DEACTIVATE FOREIGN KEY statement.

Foreign Key: To activate a foreign key you must have ALTER privilege on the

dependent table and REFERENCES privilege on the parent table.

Chapter 3. Maintaining Your Database

If a foreign key is already active, attempts to activate it are ignored. If the primary

key of the parent table referenced by this foreign key is inactive, the foreign key

cannot be activated. Otherwise, the inactive foreign key will have its values

verified against its parent table. If all values can be found in the parent’s primary

key, the foreign key will be activated.

Unique Constraint: To activate a unique constraint you must have ALTER

privilege on the table. The unique constraint will be activated only if all values in

its key are unique. If there are duplicate values you must change them to be

unique before the constraint can be activated.

All: To activate the primary key, each unique constraint, and each explicitly

inactive foreign key in a table, use the ACTIVATE ALL option. You must have the

required ALTER and REFERENCES privileges.

Implications of Activating a Primary Key or Unique Constraint: Activating a

primary key or unique constraint that is already active causes the unique index

associated with the key or constraint to be reorganized. This is more efficient than

deactivating the key or constraint (which would drop the underlying index), and

then activating the key or constraint (which would re-create the underlying index).

For more information on the benefits of reorganizing an index, see the DB2 Server

for VSE & VM Diagnosis Guide and Reference manual.

Considerations When Deactivating Keys and Constraints

Primary Key: Deactivating a primary key drops the primary key index from the

parent table and implicitly deactivates all active dependent foreign keys. This

limits the access to all inactive dependent tables to the creator or a DBA, and allow

deferred constraint enforcement. For information on deferred constraint

enforcement see “Enforcing Referential Constraints.”

To deactivate a primary key you must have ALTER and REFERENCES privileges

on the parent table, and ALTER privilege on all dependent tables.

Foreign Key: To deactivate a referential constraint that is active, you must have

ALTER privilege on the dependent table and REFERENCES privilege on the parent

table.

If a foreign key has been explicitly deactivated already, attempts to deactivate it

again are ignored.

Deactivating a foreign key will make the two tables in the relationship inactive.

Access to the inactive table is limited to the creator or a DBA. For information on

the effects of deactivating a foreign key, see “Advantages and Disadvantages of

Deferred Constraint Enforcement” on page 69.

Unique Constraints: Deactivating a unique constraint drops the unique index

associated with the constraint, causing the table to become inactive. This will limit

access to the table to its creator or a DBA.

Enforcing Referential Constraints

Two forms of enforcement are possible:

v Immediate Constraint Enforcement.

After the referential constraints have been defined, the enforcement of the

referential constraint is immediate. That is, the insert, update, and delete rules

Database Administration

are enforced when the INSERT, UPDATE, and DELETE statements are issued.

During immediate constraint enforcement, keys and tables are in the active state.

v Deferred Constraint Enforcement.

A table can be made inactive by deactivating its primary key, any of its foreign

keys, any of its unique constraints, or a dependent foreign key, by using the

ALTER TABLE statement. A referential relationship is between two keys in

different tables. If either a primary or foreign key is deactivated, both tables

become inactive.

When a table is in an inactive state, only the owner or someone with DBA

authority can issue Data Manipulation Language (DML) statements against it.

No one can issue DML statements (for example, SELECT or UPDATE

statements) against any table that would result in implicit access of an inactive

table to enforce referential constraints.

When the keys are activated, the constraints will be verified automatically and

the tables become active again.

Advantages and Disadvantages of Deferred Constraint

Enforcement

You may want to deactivate the enforcement of referential integrity among tables

to improve performance when you are loading data into a table.

When referential integrity is active between two tables, each INSERT statement on

a dependent table causes a check to be issued against the parent table. This check

verifies that the foreign key value being inserted has a matching primary key value

in the parent table. When data is being loaded into a dependent table, each

inserted row causes a check of the parent table; if many rows are being loaded, the

overhead of this checking becomes significant. In this case, you may improve your

overall performance of the load by deactivating any referential constraints. When

the load completes, you then reactivate them to validate the data.

If referential integrity is in effect at the beginning of an LUW, and the constraints

are deactivated, the data loaded and the constraints re-activated all within the

same LUW, then referential integrity exists at the end of the LUW as well.

However, within that LUW, referential constraints are not enforced. You could load

rows into the dependent table that had no parents when loaded. Since the database

can be in an inconsistent state during an LUW, but not at its completion, you can

use a more flexible sequence of statements within an LUW. At some point you

must load parent rows for the dependent rows into the parent table. Otherwise,

you would be unable to reactivate the referential constraint. There are some

disadvantages to deactivating a referential constraint between tables:

v Only users with DBA authority and the owner of a table can use DML

statements on that table, or tables referenced by it through an inactive referential

constraint. This is to prevent people from inserting, deleting, or updating data in

a table that they may believe to have an active referential constraint.

v When referential constraints are deactivated, any indexes created to enforce the

constraints are dropped. Dropping these indexes will invalidate any packages

that require the use of the indexes. Three major costs will be incurred on

reactivating the referential constraints:

- The underlying indexes are re-created

- Any dependent rows are checked against the referential constraints

- All invalidated packages are automatically re-preprocessed when they are first

used.

Chapter 3. Maintaining Your Database

If a relatively small number of rows are added to the table by the load process,

then the costs of reactivating the referential constraints may exceed the savings

realized by deferring referential constraint enforcement on each row loaded.

You should deactivate the referential constraints between tables only when large

amounts of data are to be loaded, or when a significant amount of data is to be

loaded in an order that violates the referential constraint at some point during

the data-loading operation. For example, you can load new rows into a

dependent table before loading matching rows into the parent table only while

the referential constraint is inactive.

Repairing Rows that Violate Referential Constraints

If you deactivate a referential constraint in order to load data, then receive an error

when you try to reactivate it, it could be for one of the following reasons:

v You activated a foreign key that references an inactive primary key. You must

first activate the inactive primary key.

v One or more rows in one of the tables violates the referential constraint, and you

must fix these rows. This error condition may also arise when you are creating a

referential constraint.

Note: When the above error occurs, SQLCODE -667 (SQLSTATE 22519) and the

name of the constraint in error are returned as a message token in SQLCA.

Isolating Duplicate Primary Key Values: To find duplicate primary key values,

use the statement shown below. In the example, the name of the table is P1, and

the primary key is represented by the columns PKCOL1, PKCOL2, and so on, for

all columns that form the primary key:

SELECT PKCOL1, PKCOL2, ... FROM P1

GROUP BY PKCOL1, PKCOL2, ...

HAVING COUNT(*) > 1

You could then eliminate the duplicate values with UPDATE and DELETE

statements, or move them to a special table if you do not want to eliminate them

immediately.

To move the rows to a special table (called an EXCEPTION table in this

explanation), create a table with the same column definitions as the original table

(but with no key definitions). If there are many duplicate values, you may want to

create a nonunique index for the duplicate primary key columns in the

EXCEPTION table to improve performance.

Use the statements shown below to copy the rows with duplicate primary key

values into the EXCEPTION table (called E1 in this example):

INSERT INTO E1

SELECT * FROM P1 A WHERE EXISTS

(SELECT PKCOL1, PKCOL2, ... FROM P1 B

GROUP BY PKCOL1, PKCOL2, ...

HAVING COUNT(*) > 1

AND B.PKCOL1 = A.PKCOL1

AND B.PKCOL2 = A.PKCOL2

... )

To remove these rows from P1, use this statement:

DELETE FROM P1 A WHERE EXISTS

(SELECT 1 FROM E1

WHERE E1.PKCOL1 = A.PKCOL1

AND E1.PKCOL2 = A.PKCOL2

... )

Database Administration

Isolating Nonmatching Foreign Key Values: Foreign key values may not match

primary key values because either of them may be wrong. This example shows

you how to move the nonmatching foreign keys to a separate table. Then, you can

determine whether the foreign or the primary keys are wrong, and fix them with

INSERT, UPDATE, or DELETE statements.

This statement retrieves nonmatching foreign key values. In the example, P1 is the

parent table; C1 is the dependent table; PKCOL1, PKCOL2, and so on form the

primary key; and FKCOL1, FKCOL2, and so on form the foreign key.

SELECT FKCOL1, FKCOL2, ... FROM C1 A

WHERE (FKCOL1 IS NOT NULL AND

FKCOL2 IS NOT NULL AND

... )

AND NOT EXISTS

(SELECT 1 FROM P1 B

WHERE B.PKCOL1 = A.FKCOL1 AND

B.PKCOL2 = A.FKCOL2 AND

... )

To move the rows to a special table (called an EXCEPTION table in this

explanation), create a table with the same column definitions as the dependent

table (but with no key definitions). If there are many duplicate values, you may

want to create a nonunique index for the foreign key columns in the EXCEPTION

table to improve performance. To copy the rows with nonmatching foreign keys to

the EXCEPTION table (E1 in this example), use the following statement:

INSERT INTO E1

SELECT * FROM C1 A

WHERE (FKCOL1 IS NOT NULL AND

FKCOL2 IS NOT NULL AND

... )

AND NOT EXISTS

(SELECT 1 FROM P1 B

WHERE B.PKCOL1 = A.FKCOL1 AND

B.PKCOL2 = A.FKCOL2 AND

... )

To remove the rows from C1, use the following statement:

DELETE FROM C1 A WHERE EXISTS

(SELECT 1 FROM E1

WHERE E1.FKCOL1 = A.FKCOL1

AND E1.FKCOL2 = A.FKCOL2

... )

Moving Data from One Application Server to Another

You can use the DBS Utility to move data from one application server to another.

Moving data from a DB2 Server for VSE & VM application server to a remote

DRDA application server requires unloading the data from the DB2 Server for VSE

& VM application server using the DBS Utility DATAUNLOAD command and

reloading the data into the other application server using the DBS Utility

DATALOAD command. Moving data from one DB2 Server for VSE & VM

application server to another local DB2 Server for VSE & VM application server

can be done as above, or by using the DBS Utility UNLOAD and RELOAD

commands.

For more information about DBS Utility commands, refer to the DB2 Server for VSE

& VM Database Services Utility manual.

Chapter 3. Maintaining Your Database

Notes:

1. When moving data between two application servers, ensure that the

appropriate coded character set identifier (CCSID) conversion is done to

maintain the correct interpretation of the data.

For example, an application server uses a CHARNAME value of ENGLISH (or

the CCSID equivalent), and another application server uses a CHARNAME

value of GERMAN (or the CCSID equivalent). Issue the SQLINIT EXEC (in

VM), the transaction DSQU (in CICS), or the VSE batch program ARIRBGUD

(JCL: ARISBGUD.Z) and specify a CHARNAME for the application requester

corresponding to the CHARNAME of one of the application servers (either

ENGLISH or GERMAN). Then, to ensure the integrity of the data when

moving it between these two application servers, specify the same

CHARNAME value for the application requester for both the DATAUNLOAD

(or UNLOAD) and DATALOAD (or RELOAD) operations. If ENGLISH is the

CHARNAME value specified for the application requester for the data unload

operation, then it must also be set to ENGLISH for the data load operation. You

can then perform the data unloading and reloading operations.

For more information on CCSID conversion, see the DB2 Server for VSE System

Administration manual.

2. If you want to move data from one DB2 Server for VSE application server to a

DB2 Server for VM application server (not using Guest Sharing), or vice versa,

using the DBS Utility UNLOAD command, you can only do so when using a

tape.

Removing Tables

To remove tables from the database, use the DROP TABLE statement. For example,

to remove a table called PROJECT, enter:

DROP TABLE PROJECT

Only the table’s creator or a user with DBA authority can remove the table. If you

have DBA authority, include the user ID of the owner to remove a table. For

example, to remove SMITH’s table called PROJECT, enter:

DROP TABLE SMITH.PROJECT

When a table is dropped, the row in the SYSTEM.SYSCATALOG catalog table that

contains information about it is deleted. Any other objects that depend on that

table are also dropped. As a result:

v The column names of the table are dropped from SYSTEM.SYSCOLUMNS.

v Any views based on the table are dropped.

v Application plans using the table are invalidated.

v Synonyms for the table are dropped from SYSTEM.SYSSYNONYMS.

v Indexes created on any columns of the table are dropped.

v Unique constraints on any columns of the table are dropped.

v Referential constraints that involve the table are dropped. In the case of the

PROJECT table, it is no longer a dependent of the DEPARTMENT and

EMPLOYEE tables, nor a parent of the PROJ_ACT table.

v Authorization information kept in the authorization tables is updated to reflect

the dropping of the table. Users who were previously authorized to use the

table, or views on it, no longer have those privileges.

Database Administration

You must commit the DROP statement on a table before you can re-create a table

of the same name, or before you can create any new indexes with the same name

as an index on the table being dropped.

Maintaining Dbspaces

Altering the Design of a Dbspace

You may need to change the parameters of a dbspace for any of the following

reasons:

Storage capacity (PAGES).

You may have underestimated the storage required by the tables in the dbspace,

and need to increase its potential size (in pages).

Storage pool assignment (STORPOOL).

You may want to change the storage pool assignment, which determines

whether a dbspace is recoverable or nonrecoverable.

Type (PUBLIC or PRIVATE).

If the tables in a PRIVATE dbspace are to be shared by many users, then you

should consider making it PUBLIC.

Header Space (NHEADER).

At the front of every dbspace are one to eight header pages, which contain

control information on the tables and indexes stored there. You may need to

increase the number of these pages.

Index Space (PCTINDEX).

If your dbspace contains more indexes than expected, you may need to increase

the index space to accommodate them.

Free Space (PCTFREE).

You may want to change the percentage of each data page reserved for updates

of rows resulting in larger rows that cannot be placed in the original locations in

the page.

Lock Size (LOCK).

For PUBLIC dbspaces, you may change the locking level. A lower lock level

allows more users to access the same table at the same time; however, there is a

cost because of lock acquisitions, an increased possibility of lock escalations. If

lock escalation occurs frequently, you may want to increase the locking level.

Refer to the DB2 Server for VSE & VM Diagnosis Guide and Reference manual. for

more information about lock escalations.

For a review of these parameters, see “Acquiring Dbspaces” on page 22.

Changing the PAGES, STORPOOL, DBSPACE Type, NHEADER,

or PCTINDEX

There is no statement to change these five parameters of a dbspace. If you need to

change any of them, you must move all the data in the current dbspace to another

dbspace that has the required characteristics. To do this:

1. UNLOAD the current dbspace.

2. DROP the current dbspace.

3. ACQUIRE a new dbspace with the required characteristics.

4. RELOAD the new dbspace.

5. Drop the table with field procedures, recreate it to include the field procedures,

and reload the table using the ’PURGE’ parameter.

6. CREATE all indexes for the tables involved.

Chapter 3. Maintaining Your Database

7. Recreate all referential constraints.

8. GRANT all authorizations for the tables involved.

9. CREATE all views relating to the tables involved.

To identify the tables, views, authorizations, and referential constraints related to

the dbspace, query the system catalog.

To identify the tables with field procedures, query the SYSFIELDS and

SYSFPARMS tables.

Example: To increase the storage capacity of a PRIVATE dbspace called

SMITH.SAMPLE to 2 048 pages with defaults for the other dbspace parameters,

use the following SQL statements and DBS Utility commands:

CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;

UNLOAD DBSPACE (SMITH.SAMPLE) OUTFILE (TEMPFIL);

DROP DBSPACE SMITH.SAMPLE;

ACQUIRE PRIVATE DBSPACE NAMED (SMITH.SAMPLE) (PAGES=2048);

RELOAD DBSPACE (SMITH.SAMPLE) NEW INFILE(TEMPFIL);

Invoke the DBS Utility, as usual, to process the above statements and commands

(see the DB2 Server for VSE & VM Database Services Utility manual for details).

Indexes, views, authorizations, and referential constraints must be recreated for all

the tables in the dbspace.

Changing the PCTFREE and LOCK Parameters

To change these parameters, use the ALTER DBSPACE statement. You must have

DBA authority or (in the case of a PRIVATE dbspace) be the owner of the dbspace.

Example: Change the PCTFREE parameter to 10 for the dbspace called

MYDBSPACE. type:

ALTER DBSPACE MYDBSPACE (PCTFREE = 10)

To change both the PCTFREE and the LOCK parameters at the same time, type:

ALTER DBSPACE MYDBSPACE (PCTFREE = 10, LOCK = PAGE)

Reorganizing a Dbspace to Free Storage Pool Pages

Reorganizing a dbspace releases pages in it back to its storage pool. There are two

reasons why you might want to do this:

v You are unable to drop a table in a dbspace when you issue a DROP TABLE

statement and you receive a message that the storage pool is full. This occurs

because there are not enough shadow pages in the storage pool to allow the

database manager to remove all the rows for that table from the dbspace. For

information on shadow pages, see the DB2 Server for VSE & VM Diagnosis Guide

and Reference manual.

After the database has been restarted (with STARTUP=W), there will be a row in

the catalog table SYSDROP for the dropped table. Any subsequent DROP TABLE

statements will cause SYSDROP to be processed. When the database manager

processes the row for the dropped table, it will end and issue a message

indicating that the storage pool is full unless you take other steps to provide

sufficient pages in the storage pool for shadow pages. You can provide sufficient

pages in the storage pool by adding dbextents to the storage pool, or by

reorganizing the dbspace where the table resides.

If reorganizing the dbspace does not provide sufficient shadow pages to allow

you to drop the table, then you must add dbextents to the storage pool. For

Database Administration

information on adding dbextents, see the DB2 Server for VM System

Administration or DB2 Server for VSE System Administration manual.

v You want to release unused pages back to the storage pool.

Once a page is allocated to a dbspace, it remains allocated, even when the pages

are empty, until you drop the dbspace or the empty pages are released. Empty

pages can be released back to the storage pool either by running the SQLRELEP

EXEC in VM or by bringing up the server using STARTUP=P in VSE. For more

information, see “Releasing Empty Pages.” Empty pages can cause the storage

pool that contains the dbspace to become short on storage. For example, if a

large table occupied a dbspace, and has been dropped, all pages used to store

the rows for that table are still allocated to the dbspace. To determine whether

many empty pages are allocated to a dbspace, enter the SHOW DBSPACE

operator command.

To reorganize a dbspace, follow these steps:

1. Unload all tables in the dbspace, except those that should be dropped.

2. Drop the dbspace (see note 1 below).

3. Reacquire the dbspace.

4. Reload the tables (see note 2 and 4 below).

5. Re-create all indexes and unique constraints for all tables.

6. Grant all authorizations for the tables again.

7. Re-create all referential constraints for tables (see note 3 below).

8. Re-create all views that reference the tables.

Notes:

1. Before dropping the dbspace, obtain the information necessary to perform steps

5, 6, 7, 8, and note 4 below from the catalog tables.

2. The RELOAD TABLE commands create all tables by default with the user ID of

the person who enters the commands, usually the DBA. If you want a table to

retain the user ID of its original owner, specify this user ID in the table

parameter of the RELOAD TABLE command. When performing this procedure,

use the NEW option on the RELOAD TABLE and RELOAD DBSPACE

commands. See the DB2 Server for VSE & VM Database Services Utility manual

for more details.

3. If a table has referential constraints, these will be lost when the table is

unloaded and reloaded. To re-create any foreign keys, primary keys, unique

constraints, or primary keys that have dependent foreign keys in tables that

reside in other dbspaces, use the ALTER TABLE statement.

4. If a table has field procedures, they will be lost when the table is reloaded

using the ’NEW’ option. To include the field procedures, drop the table,

recreate it, and reload the table using the ’PURGE’ option.

Releasing Empty Pages

Rather than reorganizing the dbspace to release the empty pages, you can run the

VM utility SQLRELEP or start the database in VSE with STARTUP=P and

DBSPNUM=dbspnum, where dbspnum is an acquired dbspace number or *. In

both VM and VSE, the database must be brought up in single user mode.

The changes made to the directory while pages are being released are not logged.

If an abend occurs during release empty pages processing, a database archive must

be restored to ensure inconsistencies do not exist between the directory and

dbextents. A database archive should be taken after the release empty pages utility

completes to ensure unexpected storage pool full situations do not occur during

restore. Consider the following example:

Chapter 3. Maintaining Your Database

1. You take a database archive with LOGMODE=L

2. You release empty pages on dbspace A, preventing a potential short on storage

situation for storage pool X where it resides.

3. Inserts are made to tables in dbspace B, which is also in storage pool X.

4. A restore of the archive taken in step 1 is done. This forces a log archive.

After the database restore completes, the directory is at the same state as before

empty page processing was run. The log archive created in step 4 is applied but it

does not contain information about release empty pages processing. Therefore,

storage pool X is near a short on storage condition. When the insert statements are

applied from the log archive, the database manager needs to use free pages in

storage pool X. This could lead to a storage pool full condition that did not occur

earlier.

To run the release empty pages utility in a VSE environment, start the database in

single user mode with STARTUP=P and DBSPNUM=dbspnum, where dbspnum is

an acquired dbspace number to release empty pages from or * to release empty

pages for all dbspaces. The default value for DBSPNUM is 1. Figure 20 shows an

example of the job control statements.

// JOB REORG

// EXEC PROC=ARIS75SL

// EXEC PROC=DBNAME01

// EXEC PGM=ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,STARTUP=P,DBSPNUM=*,PARMID=name’

Figure 20. Example Job Control to Release Empty Pages in a Dbspace

Notes:

For ARIS75SL, substitute your procedure or job control that identifies the DB2

Server for VSE service libraries.

For DBNAME01, substitute your procedure or job control that identifies the

database whose dbspace(s) you wish to reorganize.

The initialization parameters SYSMODE=S, STARTUP=P, and DBSPNUM are

required. You can also supply any of the following initialization parameters

(PARMID is included in the example in Figure 20.):

PARMID=name

DBPSWD=password

NPAGBUF=n

NDIRBUF=n

NCSCANS=n

LOGMODE=Y|A|L|N

CHKINTVL=n

SLOGCUSH=n

ARCHPCT=n

SOSLEVEL=n

CHARNAME=name

DSPLYDEV=L|C|B

DUMPTYPE=P|F|N

TRACDBSS=nnnnnnnnnnn

TRACRDS=nnnnnn

TRACCONV=n

TRACDSC=nn

TRACBUF=n

TRACSTG=n

LTIMEOUT=n

SYNCPNT=Y|N

Database Administration

See the DB2 Server for VSE System Administration manual for a description of

initialization parameters.

To avoid the processing involved in switching log modes, use the same

LOGMODE that you normally use.

To run the release empty pages utility in a VM environment:

1. Log on to the virtual machine that owns the database.

2. Get read access to the service minidisk (ACCESS 193 V).

3. Invoke the SQLRELEP EXEC. This EXEC resides on the service minidisk. It

invokes the DB2 Server for VM application server in single-user mode with

STARTUP=P. See “SQLRELEP EXEC” on page 259 for its syntax.

Because the release empty pages utility runs in single user mode, the only way to

trace it is with the TRACRDS, TRACDBSS, TRACDSC, and TRACCONV

initialization parameters. The TRACE operator command cannot be used in single

user mode.

Removing Dbspaces

To drop the contents of a dbspace and return it to the available state, issue the

DROP DBSPACE statement. Dbspaces that are available can then be reacquired,

using the ACQUIRE DBSPACE statement.

When a dbspace is dropped, all tables in it are also dropped. When a table is

dropped, all authorizations, views, referential constraints, unique constraints, and

field procedures relating to it are dropped.

If a dbspace contains only one table, it is more efficient to drop and then reacquire

the entire dbspace later, than to drop the table.

The DROP DBSPACE statement may be carried out on both PUBLIC and PRIVATE

dbspaces. You must have DBA authority to delete a dbspace or (in the case of a

PRIVATE dbspace) be the owner. No user, not even one with DBA authority, can

delete the dbspace that contains the system catalog.

Example

To remove your own PRIVATE dbspace named MYDBSPACE, type:

DROP DBSPACE MYDBSPACE

VSAM Restrictions

VSAM defines storage for DB2 Server for VSE databases but it does not manage

this storage. VSAM commands such as EXPORT, IMPORT, REPRO, and VERIFY

should never be used on the DB2 Server for VSE database. If you receive an error

message indicating an OPEN error (RC=74), ignore it and do not run VERIFY.

Reorganizing Indexes on the Catalog Tables

The catalog indexes need to be reorganized when indexes on the catalog tables

become fragmented, and the database manager can no longer insert entries into the

catalog dbspace.

Index fragmentation often happens in an application development environment.

Application development requires frequent preprocessing; and each time a

Chapter 3. Maintaining Your Database

program is preprocessed, many entries are added to the catalog tables. It may not

be possible to plan properly for the range of index keys that might be created.

Index fragmentation can lead to the inefficient use of the index pages of the catalog

dbspace (SYS0001). If most of the index pages in your catalog dbspace are

occupied, fragmentation is a likely cause. To determine the number of index pages

occupied in the catalog dbspace, enter the SHOW DBSPACE command. (The

number of this catalog dbspace is 1; so you type SHOW DBSPACE 1.) If there is a

high percentage of occupied pages, consider running the catalog index

reorganization utility, which optimizes the indexes as they exist on the catalog

tables.

To run the catalog index reorganization utility in a VSE environment, start the

database in single user mode with STARTUP=I specified. Figure 21 shows an

example of the job control statements.

// JOB REORG

// EXEC PROC=ARIS71SL

// EXEC PROC=DBNAME01

// EXEC PGM=ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,STARTUP=I,PARMID=name’

Figure 21. Example Job Control to Reorganize the Catalog Indexes

Notes:

For ARIS71SL, substitute your procedure or job control that identifies the DB2

Server for VSE service libraries. The catalog index reorganization utility uses

the ARISCAT source member.

For DBNAME01, substitute your procedure or job control that identifies the

database whose catalog indexes you wish to reorganize.

The initialization parameters SYSMODE=S and STARTUP=I are required. You

can also supply any of the following initialization parameters (PARMID is

included in the example in Figure 21.):

PARMID=name

DBPSWD=password

NPAGBUF=n

NDIRBUF=n

NCSCANS=n

LOGMODE=Y|A|L|N

CHKINTVL=n

SLOGCUSH=n

ARCHPCT=n

SOSLEVEL=n

CHARNAME=name

DSPLYDEV=L|C|B

DUMPTYPE=P|F|N

TRACDBSS=nnnnnnnnnnn

TRACRDS=nnnnnn

TRACCONV=n

TRACDSC=nn

TRACBUF=n

TRACSTG=n

LTIMEOUT=n

SYNCPNT=Y|N

Database Administration

See the DB2 Server for VSE System Administration manual for a description of

initialization parameters.

To avoid the processing involved in switching log modes, use the same

LOGMODE that you normally use.

To run the catalog index reorganization utility in a VM environment:

1. Log on to the virtual machine that owns the database.

2. Get read access to the service minidisk (ACCESS 193 V).

3. Invoke the SQLCIREO EXEC. This EXEC resides on the service minidisk. It

invokes the DB2 Server for VM application server in single-user mode with

STARTUP=I. See “SQLCIREO EXEC” on page 257 for its syntax.

Because the catalog index reorganization utility runs in single user mode, the only

way to trace it is with the TRACRDS, TRACDBSS, TRACDSC, and TRACCONV

initialization parameters. The TRACE operator command cannot be used in single

user mode.

Moving Your Database

If you are moving your database, use the SQLDBDEF utility to extract the

definition of database objects from a DB2 Server for VSE & VM database, and

generate a DBSU job. This job can be used to create the same objects on another

DB2 database. The target database can be any DB2 database, such as DB2 Server

for VSE & VM, DB2 UDB for OS/390, or DB2 UDB for Linux. The SQL statements

in the DBSU job can be grouped by dbspace or by object type. Once the objects

have been created on the target platform, the load utilities of the target database

can be used to load the data. Packages can be unloaded from the source database

and reloaded to the target database so that existing client applications can continue

to be used.

For more information about the SQLDBDEF utility, refer to the DB2 Server for VM

System Administration manual, Appendix G, Service and Maintenance Utilities or the

DB2 Server for VSE System Administration manual, Appendix G, Service and

Maintenance Utilities.

Chapter 3. Maintaining Your Database

Chapter

4. Supporting Your Users

As the database administrator, you provide the support that users need to gain

access to your DB2 Server for VSE & VM application server and the data it

manages.

This chapter describes the tasks involved in supporting new users, and removing

the data and access of those who have left.

Adding a New User

To a DB2 Server for VSE & VM Application Server:

The following are the steps involved in adding new users to DB2 Server for VSE &

VM application servers.

New users need CONNECT authority on the application server (alternatively, this

may be granted to ALLUSERS). To add a new user to the system, perform the

following tasks:

1. In VM, define the user’s virtual machine as a DB2 Server for VM user machine.

This involves making VM directory changes and is discussed in the DB2 Server

for VM System Administration manual.

2. Setup the user as a new ISQL user.

3. Grant the user an appropriate level of authority to access data and use

resources.

4. Specify the default application server.

5. If required, load initial tables.

6. Ensure that the new user obtains adequate system training.

To a Non-DB2 Server for VSE & VM Application Server:

To enable a user to access a non-DB2 Server for VSE & VM application server

perform the following tasks:

Notes:

1. In VM, if the user is not already a DB2 Server for VM user, define the user’s

virtual machine as a DB2 Server for VM user machine. This involves making

VM directory changes and is discussed in the DB2 Server for VM System

Administration manual.

2. Arrange system-level sign-on authority with the system administrator of each

remote application server.

Note: A new user ID and password may be required at some of the remote

application servers, depending on the LU 6.2 security level that is required for

the connection. See the Distributed Relational Database Connectivity Guide manual.

3. In VM, setup a new entry in the CMS Communication Directory (COMDIR) for

the remote application server (if it has not already been done), and make the

COMDIR accessible to the user.

In VSE, set up a new entry in the DBNAME Directory for the remote

application server (if it has not already been done).

4. If the user will be accessing an application server through ISQL, then setup the

user as a new ISQL user. Make sure that ISQL has been installed on the remote

application server.

5. Grant the user (or arrange to have granted) the appropriate level of authority

to access data and use resources at each of the remote application servers.

6. Specify the default application server.

7. If required, load initial tables.

8. Ensure that the new user obtains adequate training on the remote application

server and on how to access it from the local DB2 Server for VSE & VM

application requester.

Setting Up New ISQL Users

To set up a new ISQL user to access the resources of an application server, run the

SQLDBA.ARINEWUS routine supplied by IBM and previously loaded into the

SQLDBA.ROUTINE table during database generation.

Note: The ARINEWUS routine is intended for DB2 Server for VSE & VM

application servers only. If you need to add a new ISQL user to a non-DB2

Server for VSE & VM application server create your own routine using

ARINEWUS as a sample. This routine uses a CONNECT statement

containing an IDENTIFIED BY clause, as well as a GRANT CONNECT

statement. These statements are unique to DB2 Server for VSE & VM

application servers and may not be supported by non-DB2 Server for VSE &

VM application servers.

Start ISQL and connect as SQLDBA (or some other user ID with DBA authority),

then type:

RUN SQLDBA.ARINEWUS (newuser newuserpw)

For newuser, specify:

v In VM, the CP LOGON user ID (the name of the user’s virtual machine) or,

v In VSE, the user’s CICS sign-on ID.

For newuserpw, specify a password for the new user.

The ARINEWUS routine does the following:

v Issues an ISQL SET RUNMODE CANCEL command.

v Issues an ISQL SET AUTOCOMMIT OFF command.

v Grants CONNECT authority to the new user. The routine parameters newuser

and newuserpw are used on the CONNECT statement.

v Creates a copy of a set of sample tables for the user, and grants him or her full

authority on them. These tables are named:

newuser.DEPARTMENT

newuser.EMPLOYEE

newuser.PROJECT

newuser.ACTIVITY

newuser.PROJ_ACT

newuser.EMP_ACT

newuser.CL_SCHED

newuser.IN_TRAY

Database Administration

v Copies data from the sample tables owned by user ID SQLDBA into the new

user’s sample tables. (Only the rows needed to duplicate the examples shown in

the DB2 Server for VSE & VM Interactive SQL Guide and Reference manual are

copied.)

v Creates indexes on the sample tables.

v Creates and loads an ISQL routine table (newuser.ROUTINE), which includes an

ISQL PROFILE routine, as follows:

NAME

SEQNO COMMAND

REMARKS

-------

-----

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

-------

PROFILE

SET VARCHAR 35

NULL

PROFILE

SET CASE UPPER

NULL

v Creates an index on the routine table.

v Issues an ISQL SET AUTOCOMMIT ON command.

When you run ARINEWUS, you will be prompted to enter either COMMIT or

ROLLBACK. If no errors occurred, enter COMMIT; otherwise, enter ROLLBACK.

The ARINEWUS routine sets up the new user only in the application server that

you are connected to when you invoke the routine. If a user is to have access to

more than one application server, connect to these other application servers and

run ARINEWUS again for each one.

Example

A new user with a user ID of ALEX and a password of ALEXPW is defined. Alex

does application development work and needs access to two application servers:

PROD and TEST.

Do the following in a VM system:

Log on to your own user machine, and IPL CMS.

Issue SQLINIT DBNAME(PROD). (Assume that the PROD application server is

currently being accessed by some database machine in multiple user mode.)

Start the ISQL program.

Connect to the PROD application server under a user ID with DBA authority.

In the example below, the user ID is SQLDBA. The step is optional if you

already have DBA authority. Enter:

CONNECT SQLDBA IDENTIFIED BY sqldbapw

Assume you know the password of the SQLDBA user ID for both application

servers

Start the ARINEWUS routine:

RUN SQLDBA.ARINEWUS (ALEX ALEXPW)

Connect to the TEST application server under a user ID with DBA authority:

CONNECT SQLDBA IDENTIFIED BY sqldbapw TO TEST

Start the ARINEWUS routine:

RUN SQLDBA.ARINEWUS (ALEX ALEXPW)

Exit from the ISQL program.

Do the following in a VSE system:

Ensure that the application servers PROD and TEST have been started with the

DLBL and LIBDEF statements required for accessing the application servers.

Also ensure that the CICS system has been started and initialized for DB2

Server for VSE on-line access to both PROD and TEST.

Chapter 4. Supporting Your Users

2. Start the ISQL program.

3. Connect to the PROD application server under a user ID with DBA authority.

In the example below, the user ID is SQLDBA. The step is optional if you

already have DBA authority. Enter:

CONNECT SQLDBA IDENTIFIED BY sqldbapw TO PROD

Assume you know the password of the SQLDBA user ID for both application

servers

4. Start the ARINEWUS routine:

RUN SQLDBA.ARINEWUS (ALEX ALEXPW)

5. Connect to the TEST application server as SQLDBA (or with any ID that has

DBA authority).

CONNECT SQLDBA IDENTIFIED BY sqldbapw TO TEST

6. Start the ARINEWUS routine:

RUN SQLDBA.ARINEWUS (ALEX ALEXPW)

7. Exit from the ISQL program.

Alex is now set up to use both application servers.

If you want to review the contents of the ARINEWUS routine before you invoke it,

issue the following SELECT statement on either application server:

SELECT COMMAND FROM SQLDBA.ROUTINE WHERE NAME = ’ARINEWUS’

Authorizing Access

Once you have run ARINEWUS, your new user has CONNECT authority to the

application server. This is the lowest level of authority. To decide if this is the

appropriate level for this user, and to change it if not, see Chapter 5, “Providing

Security,” on page 89.

After providing new users with CONNECT authority, you can do any of the

following:

v Acquire PRIVATE dbspaces for them so that they can create their own tables

v Grant them RESOURCE authority

v Grant them DBA authority

v Ensure that they are granted privileges on other users’ tables and views

v Create new views on tables to restrict their access to data that is appropriate for

them to see.

Specifying a Default Application Server in VM

Before VM users can access an application server, a default application server

needs to be established. Users must process the SQLINIT EXEC to specify the

application server they intend to access. For example, if the user intends to access

the TEST application server, he or she must enter:

SQLINIT DBNAME(TEST)

Users only need to re-process the SQLINIT EXEC if they want to explicitly change

the current SQLINIT options. The most current SQLINIT information is stored on

each user’s A-disk. For more information, see “SQLINIT EXEC” on page 243.

Database Administration

Loading Initial Tables

New users likely have existing files of data that they want to store in the database.

If the files are short, the data they contain can be typed in at the terminal using

ISQL statements. This method, however, is not suitable for large files. Here, you

can use the DBS Utility to transfer data into a database. For information on how to

use the DBS Utility, see “Loading Data into Tables” on page 59.

Training New Users

It is your responsibility to assist new users with the DB2 Server for VSE & VM

database manager, and to deal with their questions and problems. Ensuring new

users are adequately trained will reduce your problem-solving duties.

Removing Users from an Application Server

When users leave your area, both their access to the application server and any

unwanted data should be removed. You should try to get people to remove their

own data before they leave; however, you will often have to do so yourself.

The following steps describe how to remove a user’s access to an application

server. If a user was using multiple application servers, you must perform this

process for each server. You must have DBA authority to perform these steps.

If you have DBA authority, you can revoke a user’s authority to access the

application server at any time by issuing the REVOKE CONNECT statement listing

the user(s) affected. For example:

REVOKE CONNECT FROM JOHN,KAREN,ALICE

Revoking a user’s CONNECT authority prevents that user ID from accessing the

application server. This action only removes the user IDs from the

SYSTEM.SYSUSERAUTH catalog table; it does not affect any objects (for example,

tables) in the database which those users may have created, nor does it affect any

privileges that may have been granted to them.

Example

An employee whose user ID was SMITH has left the company. To remove

SMITH’s database objects, do the following:

1. Determine the names of PRIVATE dbspaces owned by SMITH. Type:

SELECT DBSPACENAME FROM SYSTEM.SYSDBSPACES

WHERE OWNER=’SMITH’

2. Determine the names of tables owned by SMITH. Type:

SELECT TNAME,DBSPACENAME FROM SYSTEM.SYSCATALOG

WHERE CREATOR=’SMITH’

AND TABLETYPE=’R’

This command displays the names of the tables that SMITH created, and the

dbspaces where they were created. The TABLETYPE=‘R’ (R stands for real

table) indicates that you want to see only the tables at this point; you do not

yet want to see any views that SMITH defined. Record those tables that are in

PUBLIC dbspaces for later use in step 8.

3. Determine whether any of SMITH’s tables participate in a referential structure

that is not wholly owned by SMITH.

SELECT TNAME, TCREATOR, REFTNAME, REFTCREATOR FROM SYSTEM.SYSKEYS

WHERE

(TCREATOR ¬= ’SMITH’ AND REFTCREATOR = ’SMITH’)

OR (TCREATOR = ’SMITH’ AND REFTCREATOR ¬= ’SMITH’)

Chapter 4. Supporting Your Users

This command displays tables created by others that reference tables created

by SMITH, as well as tables created by SMITH that reference tables created by

others. Make note of the tables you want to save.

Determine if the PRIVATE dbspace owned by SMITH contains any tables that

were created by other users. Remember that when you drop a dbspace, you

drop all tables that exist in it, whether they were created by the owner or by

other users.

For each PRIVATE dbspace owned by SMITH, type:

SELECT TNAME,CREATOR FROM SYSTEM.SYSCATALOG

WHERE CREATOR¬=’SMITH’

AND DBSPACENAME=’dbspacename’

AND TABLETYPE=’R’

This command lists the names of all tables in dbspacename that SMITH did not

create, along with the names of who created them. The TABLETYPE=‘R’ (R

stands for real table) indicates that you want to see only the tables at this

point, not views.

Based on the information you acquired in the last three steps, transfer any

tables that you want to save. If any of these tables participate in referential

structures, the referential constraints must be rebuilt to reflect the changed

ownership of the tables.

There are many ways to transfer (copy) tables to another dbspace. One way is

to first create a new table with the same format in a different dbspace; then

use an INSERT with Subselect statement to retrieve data from the original

table and insert it into the new table.

There are more sophisticated techniques available using the DBS Utility. For

information, refer to “Maintaining Tables” on page 59 or to the DB2 Server for

VSE & VM Database Services Utility manual.

Copy any programs that you want to save that currently reside in SMITH’s

PRIVATE dbspaces into another dbspace.

Drop the PRIVATE dbspaces owned by SMITH, which you determined in step

1, by issuing the DROP statement:

DROP DBSPACE SMITH.dbspacename

Drop any of SMITH’s tables you no longer need, as determined in step 2. All

associated indexes and views are also dropped.

DROP TABLE SMITH.tablename

Drop any of SMITH’s views that were defined on other users’ objects in

PUBLIC dbspaces or in other users’ PRIVATE dbspaces. To get the names of

those views from the catalog tables, type:

SELECT VIEWNAME FROM SYSTEM.SYSVIEWS

WHERE VCREATOR=’SMITH’

DROP VIEW SMITH.viewname

10.

Drop any of SMITH’s indexes that were defined on other users’ objects in

PUBLIC dbspaces or in other users’ PRIVATE dbspaces. To get the names of

those indexes from the catalog tables, type:

SELECT INAME FROM SYSTEM.SYSINDEXES

WHERE ICREATOR=’SMITH’

DROP INDEX SMITH.indexname

11.

Drop any of the packages created by SMITH. To display the names of those

packages from the catalog tables, type:

Database Administration

SELECT TNAME FROM SYSTEM.SYSACCESS

WHERE CREATOR=’SMITH’

DROP PACKAGE SMITH.packagename

12.

Delete any synonyms created by SMITH:

DELETE FROM SYSTEM.SYSSYNONYMS

WHERE USERID=’SMITH’

13.

Delete any ISQL stored queries created by SMITH.

To determine these queries, type:

SELECT STMTNAME FROM SQLDBA."STORED QUERIES"

WHERE CREATOR=’SMITH’

Then issue a single DELETE statement:

DELETE FROM SQLDBA."STORED QUERIES"

WHERE CREATOR=’SMITH’

It is helpful if departing employees remove their own data from the database.

Only someone with DBA authority can delete stored queries in the above

manner; others use the ISQL ERASE command. For example, to delete a

stored query called MYQUERY, SMITH would start ISQL and type:

ERASE MYQUERY

14.

Revoke any privileges granted to SMITH. To get the names of all users who

granted privileges to SMITH, type:

SELECT * FROM SYSTEM.SYSTABAUTH

WHERE GRANTEE=’SMITH’

SELECT * FROM SYSTEM.SYSPROGAUTH

WHERE GRANTEE=’SMITH’

Contact these users and have them revoke all of SMITH’s privileges. Or, if a

user is not available, you can explicitly connect with his or her password to

revoke them yourself.

15.

In VM, remove any IUCV links.

If SMITH’s VM directory contains IUCV entries or the MAXCONN OPTION

for the database resources, these entries should be removed, as well as access

to the 195 production disk.

16.

Remove Access from VSE guests.

If SMITH accessed a DB2 Server for VM application server from a VSE guest,

and used the CICS system, you should remove the transaction IDs used by

SMITH in the CICS system. For more information on transaction IDs, see the

DB2 Server for VM System Administration manual.

Chapter 4. Supporting Your Users

Chapter

5. Providing Security

The database manager controls security with authorities and privileges granted to

users (identified by their user IDs). Authorities limit people’s use of DB2 resources

(for example, whether they can create tables in PUBLIC dbspaces or acquire

PRIVATE dbspaces), while privileges provide security for existing objects in the

database (tables, views, indexes, and packages).

All privileges and authorities held within an application server are recorded in the

catalog tables.

To access and perform SQL requests for an application server, users (ISQL users,

DBS Utility users, and application programs) must be allowed to CONNECT to the

application server implicitly (without a user ID or password), or explicitly (with a

user ID and its password). With either type of connecting, the user can work with

utilities, programs, and the data in the database based on pre-established

authorities. Connecting is much the same as logging on to the VM or VSE system.

This chapter discusses the following topics:

1. Authorities.

This section discusses the four types of authorities and how they can be given

(granted) to or taken away (revoked) from users.

2. User Privileges.

This section describes how privileges can be used to share or restrict access to

the data in tables or views.

3. Connecting to an Application Server

This section discusses how a user can connect to an application server. Users

must connect to an application server before they can use it.

4. Restricting Access Using Views.

This section discusses the use of views to restrict access to tables.

5. Changing User Passwords.

This section describes how you can change the password of your DB2 Server

for VSE & VM users.

6. Securing the Database Catalog Tables.

This section discusses how you can limit access to the catalog tables.

7. Security Audit Trace.

This section describes the two ways that you can audit security: by querying

the catalog tables or by having the database manager do a security audit trace.

Authorities

When a database is initially generated, there is only one user ID defined for it:

SQLDBA. This user ID belongs to the database administrator (DBA). Only a DBA

can grant or revoke authorities to other users.

Types of Authorities

There are four types of authority: CONNECT, RESOURCE, SCHEDULE, and DBA.

Authorities are hierarchical, with DBA the highest, RESOURCE and SCHEDULE

the next, and finally CONNECT. If you have a higher authority, then you also have

the authority below it. For example, if you are given DBA authority, you have

RESOURCE, SCHEDULE, and CONNECT authority as well. If you are given

RESOURCE authority, you also have CONNECT authority but not SCHEDULE or

DBA authority.

CONNECT Authority

This authority enables a user to access a particular application server, and to

exercise all privileges that have been granted to PUBLIC. These privileges are

discussed in detail in “Privileges” on page 94.

A user with CONNECT authority can access data in one of two ways:

v By owning a PRIVATE dbspace, in which he or she can create tables and load

and access them. A user with DBA authority must acquire the dbspace for this

user.

v By receiving access privileges (such as SELECT, INSERT, and UPDATE) for

tables created by other users. See “Privileges” on page 94.

RESOURCE Authority

Users with this authority can acquire PRIVATE dbspaces for themselves, and create

tables both there and in PUBLIC dbspaces.

A DBA automatically possesses RESOURCE authority and the ability to grant it to

users. You can give it to just a few users to exercise tight control, or you can

extend it to any number. If you want to allow someone to create tables and you

must also control how much resources are used, acquire a PRIVATE dbspace for

that user rather than granting him or her RESOURCE authority. Because you

acquire this dbspace yourself, you control its size and the amount of resources

used. This technique is sometimes called “CREATE TABLE authority”, but this

term is misleading because there is no GRANT CREATE TABLE statement.

SCHEDULE Authority

The function associated with SCHEDULE authority is not available in the SQL

statement set. Therefore, DB2 Server for VSE & VM users cannot use it and

SCHEDULE authority is of no direct benefit to DB2 Server for VSE & VM users.

SCHEDULE authority is useful only to online resource managers that manage

subsystems of multiple second-level users. The only current example is the DB2

Server for VSE online resource adapter that manages secondary users through the

CICS subsystem. The CICS subsystem is a first-level user of the database manager.

The use of SCHEDULE authority in a CICS subsystem is discussed here.

The online resource adapter resides in each CICS partition. It initializes the

communication links between the CICS partition and the local DB2 Server for VSE

database manager, or the DB2 Server for VM database manager accessed through

guest sharing, when the operator executes the CICS CIRB transaction or the CICS

CIRA transaction. It also does a CONNECT on each link, specifying DBDCCICS as

the user ID and SQLDBAPW as the password. This user ID and password can be

overridden. Refer to “CICS Transaction Environment” on page 103 for details.

The online resource adapter in each CICS partition can connect to many

application servers. The DBNAME parameter of the CIRB or CIRA transaction

specifies the application server to which you want to connect. If DBNAME is not

specified on the CIRB transaction, the default application server is used. Refer to

“Establishing a Default Application Server” on page 101 for information on

Database Administration

DBNAME default rules. All online applications in a CICS partition can access the

application servers connected with the online resource adapter.

The schedule function comes into play when a CICS transaction uses SQL

statements without preceding them with a CONNECT statement¹ on a local

application server or on a VM application server accessed through guest sharing.

When this occurs, the resource adapter sends a schedule request to the database

manager. This request travels on the link being used by the transaction. A schedule

request is similar to a CONNECT, but it has no password. The resource adapter

determines the user ID as described in “CICS Transaction Environment” on page

103.

The schedule function allows dynamic changing of the current user ID on a link to

the database manager without requiring a password. For this to occur, the initial

user of the link must have SCHEDULE authority. For a CICS session, the initial

user of the link is the unique application name (APPLID) assigned to the CICS

partition in the DFHSIT table. The default APPLID name is DBDCCICS. This user

ID represents the entire CICS subsystem. The database administrator must grant

each APPLID SCHEDULE authority on the application server so that the links to

the database manager can be shared implicitly by multiple transactions. If a CICS

partition is to connect to more than one local application server, the APPLID for

the partition must be granted SCHEDULE authority on each application server.

Transactions that do not issue CONNECT statements¹ receive their connection to

the database manager implicitly through the CICS subsystem. The assumption is

made that the CICS subsystem checked the user’s identification and password

when the user began the CICS session, so the database manager does not need to

do further checking. On the other hand, each transaction is subject to all the other

security controls. The user ID received by the database manager with the schedule

request is the basis for this transaction user’s authorization.

Because CONNECT authority is not needed for CICS transactions, the user IDs

that they use need not appear in the SYSTEM.SYSUSERAUTH catalog table. This

catalog table does not necessarily have an entry for every user. Second-level users

can access all PUBLIC data and may be granted access to PRIVATE data as well.

Although a user may not be given CONNECT authority explicitly, that user can be

granted RESOURCE authority or SCHEDULE authority and will receive

CONNECT authority as a result.

Note: This discussion applies only to transactions that do not issue a CONNECT

statement. When a transaction does issue a CONNECT statement¹, it

appears as an ordinary user, and the schedule function is not used.

A user possessing DBA authority possesses SCHEDULE authority and the ability

to grant SCHEDULE authority to other users.

To grant SCHEDULE authority, use a statement such as:

GRANT SCHEDULE TO dbdccics IDENTIFIED BY password

If the user’s password has been entered previously and is not to be changed, you

can omit the “IDENTIFIED BY password” portion of the GRANT statement. Refer

to “CICS Transaction Environment” on page 103 for details.

1. The CONNECT statement with the following format: CONNECT userid IDENTIFIED BY password.

Chapter 5. Providing Security

DBA Authority

Authorization mechanisms do not apply to users with this authority. They can

perform all operations on all tables, can run all programs, and are the only ones

who have the following privileges:

Grant and revoke SCHEDULE, CONNECT, RESOURCE, and DBA authority

to/from other users. All DBAs at a site have equal authority, and can grant and

revoke DBA authorities to each other. Because no user may revoke his or her

own authority, there will always be at least one DBA (not necessarily the original

one).

Acquire a PUBLIC dbspace.

Alter or drop any PUBLIC dbspace except for system dbspaces (those whose

names begin with “SYS”).

Acquire, alter, or drop a PRIVATE dbspace or create, alter, or drop a table, index,

synonym or view, in the name of another user.

Drop a package belonging to another user.

Lock another user’s PRIVATE dbspace or any PUBLIC dbspace (except system

dbspaces).

Lock another user’s table (except the catalog tables).

Issue a COMMENT statement on a table or field owned by another user.

Create a table in a system dbspace.

Issue Data Manipulation Language statements directly against an inactive table.

See “Altering Referential and Unique Constraints” on page 65.

Modify the contents of a catalog table with a regular UPDATE statement. Rows

cannot be INSERTed or DELETEd. Because all access to the data in the database

depends on the correctness of the catalog tables, manual updating of catalog

tables should be done only under extraordinary circumstances. Only a small set

of catalog table columns can be updated. These are listed in the DB2 Server for

VSE & VM SQL Reference manual.

For Extended Dynamic Statements:

- Drop another user’s program (package) or drop a statement from that

package.

- Use PREPARE, DESCRIBE, EXECUTE, or DECLARE CURSOR for a statement

residing in another user’s package.

No user, including those with DBA authority, can drop a catalog table.

As DBA, you may perform certain operations that are otherwise unauthorized, but

may not grant or revoke these operations. For example, you may update a

particular table that you do not own explicitly, but you may not grant or revoke

this privilege to others.

The functions enabled by DBA authority are potentially quite dangerous to the

integrity of the database if applied by an untrained user. Therefore, you should

carefully control who receives this authority, as well as being very cautious in the

use of this special authority yourself.

Granting Authorities

To grant any authority (SCHEDULE, CONNECT, RESOURCE, or DBA) to other

users of an application server, issue the GRANT statement. You must have DBA

authority on that application server. For information on the syntax of this

statement, see the DB2 Server for VSE & VM SQL Reference manual.

Database Administration

Granting someone a higher authority automatically gives them the lower authority

as well, regardless of whether these are specified on the GRANT statement. Thus, a

user who is granted RESOURCE authority will also have CONNECT authority;

one who has DBA authority also has CONNECT, RESOURCE, and SCHEDULE

authority.

If you are granting authority to a user at a remote system, the authorization-name

specified in the GRANT statement must be the authorized user ID of the user on

the system where the authority is being granted, not that on the system where the

request originates.

Examples

Granting authority to a single user: To give the user ID MIKE CONNECT

authority to the application server, enter:

GRANT CONNECT TO MIKE IDENTIFIED BY mikespwd

If the user MIKE intends to connect to the application server implicitly, you can

omit his password:

GRANT CONNECT TO MIKE

Granting authority to many users: To give the user IDs MIKE and JOHN

RESOURCE authority to the application server, enter:

GRANT RESOURCE TO MIKE,JOHN IDENTIFIED BY mikespwd,johnspwd

If MIKE intends to connect to the application server implicitly, you may omit his

password and just enter:

GRANT RESOURCE TO JOHN IDENTIFIED BY johnspwd

GRANT RESOURCE TO MIKE

Granting CONNECT authority to all users: The following statement enables all

users to connect to the application server implicitly:

GRANT CONNECT TO ALLUSERS

Users who wish to connect explicitly to the application server must be given

CONNECT authority with a password. In VM, the ability to communicate with a

DB2 Server for VM database manager depends on VM directory statements and is

discussed in the DB2 Server for VM System Administration manual.

Granting Access to VSE Guests

When VSE/AF runs as a guest operating system under the VM/ESA operating

systems, VSE users and programs can optionally access a DB2 Server for VM

application server. A VSE guest who wishes to do this must obtain authorization.

On the GRANT statement, specify a VM user ID that is authorized to run the VSE

subsystem.

The subsystemid follows the same general rules for naming data objects as the user

ID, and cannot contain lowercase characters, special characters, or DBCS

characters.

Example: To give the CICS subsystem MYCICS SCHEDULE authority, enter:

GRANT SCHEDULE TO MYCICS IDENTIFIED BY cicspw

where cicspw is the current password set for the subsystem.

Chapter 5. Providing Security

Revoking Authorities

To revoke authorities previously granted to users, issue the REVOKE statement.

You must have DBA authority. For information on the syntax of this statement, see

the DB2 Server for VSE & VM SQL Reference manual.

Revoking a user’s CONNECT authority does not automatically cause any objects

owned by that user to be dropped, nor does it revoke any privileges the user has

on those objects. For information on how to drop objects, see “Removing Users

from an Application Server” on page 85.

If a user’s CONNECT authority is revoked, all other authorities are lost. For

example, if you are a DBA and another DBA revokes your CONNECT authority,

then you will lose your RESOURCE, SCHEDULE, and DBA authorities as well.

A user who loses RESOURCE authority will still have CONNECT authority. You

cannot revoke RESOURCE authority from a user with DBA authority.

A user who loses SCHEDULE authority will still have CONNECT authority. You

cannot revoke SCHEDULE authority from a user with DBA authority.

A user who loses DBA authority will also lose RESOURCE and SCHEDULE

authority, but will retain CONNECT authority.

When revoking remote users, the authorization-name specified in the REVOKE

statement must be the authorized user ID of the user on the remote system where

the authority is being revoked, not that on the system where the request originates.

Examples

To revoke JOHN’s CONNECT authority, enter:

REVOKE CONNECT FROM JOHN

To revoke JOHN and ALICE’s DBA authority, enter:

REVOKE DBA FROM JOHN,ALICE

To revoke JOHN and ALICE’s SCHEDULE authority, enter:

REVOKE SCHEDULE FROM JOHN,ALICE

Revoking Access from VSE Guests

Use the REVOKE SCHEDULE statement to revoke remote access by a VSE

subsystem.

To revoke the SCHEDULE authority of the CICS subsystem called MYCICS, enter:

REVOKE SCHEDULE FROM MYCICS

Privileges

The DBA grants authorities to the users of the application server. Within the

framework set up by the DBA, individual users can grant to each other the

privileges they need to access specific data. To grant or revoke privileges on an

object, a user must hold GRANT authority on those privileges, and be connected to

the application server where the object resides.

The following are the privileges that can be held on a table (or view) in the

database:

SELECT

To read from a table

Database Administration

INSERT

To add rows to a table

DELETE

To delete rows from a table

UPDATE

Can apply to individual columns

ALTER

To add new columns, primary keys or foreign keys to a table, or to

activate or deactivate existing keys

INDEX

To create or manipulate indexes on a table

REFERENCES To add, drop, activate, or deactivate a foreign key relationship

The first four privileges in this list apply to views as well as to tables.

The holder of a privilege may exercise it directly through a user mechanism such

as ISQL, or by compiling and running programs that entail using it.

Privileges of Ownership

When an object is created, its ownership is established. If the object name is not

qualified (for example, EMPLOYEE), the owner is the connected user. If the object

is qualified (for example, JESSICA.EMPLOYEE), the owner is the individual whose

user ID is specified. The owner of an object automatically has full privileges on it.

Once the ownership of a table or view is established, there is no way to change it

or to revoke the privileges that accompany ownership. If either of these is

necessary, you must drop the object, which deletes all privileges on it, and then

re-create it with a new owner.

Granting Privileges to Other Users

The owner of an object possesses the GRANT option on each privilege, meaning

the ability to grant individual privileges, or any combination of them, to other

users. When a privilege is granted, the GRANT option (the ability for the recipient

to in turn make further grants) may or may not be included.

Privileges can be granted to other users using the GRANT statement described in

the DB2 Server for VSE & VM SQL Reference manual.

v Issuing GRANT ALL or GRANT ALL PRIVILEGES grants the recipient all the

privileges possessed by the grantor on that object (which may of course not

include all possible ones). If GRANT ALL is issued on a view, only the

privileges on the view, not those on the base tables, are granted.

v Issuing GRANT REFERENCES enables the recipient to reference the parent table

when a foreign key is added, dropped, activated, or deactivated through the

CREATE TABLE or ALTER TABLE statements.

v Issuing GRANT ALTER enables the recipient to add a new column or to add,

drop, activate, or deactivate a primary or foreign key. To alter a primary key, the

ALTER privilege is required on the parent table and all dependent tables. To

alter a foreign key, the ALTER privilege is required on the dependent table, and

the REFERENCES privilege is required on the parent.

Withholding these privileges restricts the ability of the recipient to change the

state of referential constraints. If the owner of a parent table grants the

REFERENCES privilege on it to another user, and the recipient then creates a

foreign key relationship with the parent’s primary key but does not grant

ALTER privilege on the dependent table back to the owner of the parent table,

the owner cannot drop the primary key. (He or she may, of course, drop the

entire table.)

v The UPDATE privilege can apply to specific columns. For example, the

following statement will allow CINDY to update the address (ADDR) and phone

number columns (PHONE) of the EMPDATA table:

Chapter 5. Providing Security

GRANT UPDATE (ADDR,PHONE) ON EMPDATA TO CINDY

If you are granting a user privileges at a remote system, the authorization-name

specified in the GRANT statement must be the same as the name that the grantee

uses to access the database manager system on the remote system.

Revoking Privileges

A user who grants another user a privilege may later revoke it, by issuing the

REVOKE statement described in the DB2 Server for VSE & VM SQL Reference

manual. If a user loses a privilege, all other users to whom that user granted it

automatically lose it too by the cascading effect, unless they have another

independent source for it. Issuing REVOKE ALL or REVOKE ALL PRIVILEGES

takes away all privileges that were granted.

If you are revoking a user’s privileges at a remote system, the authorization-name

specified in the REVOKE statement must be the name that the user specifies to

access the database manager system on the remote system.

Monitoring Privileges

All the privileges held by users on tables and views are listed in the catalog tables

SYSTEM.SYSTABAUTH and SYSTEM.SYSCOLAUTH. Users can check which

privileges they hold and which they have granted to others, by querying these

tables.

Examples

To determine the privileges that you hold, enter:

SELECT * FROM SYSTEM.SYSTABAUTH

WHERE GRANTEE = user

To determine the privileges that you have granted to other users, enter:

SELECT * FROM SYSTEM.SYSTABAUTH

WHERE GRANTOR

= user

AND GRANTEE <> user

AND GRANTEETYPE = ’ ’

For descriptions of the catalog tables, see the DB2 Server for VSE & VM SQL

Reference manual.

Privileges on Application Programs

DB2 Server for VSE & VM application programs must be preprocessed before they

are compiled or assembled. In VM and VSE batch environments, successful

preprocessing of an application program results in the creation or replacement of a

package in the database. In VSE, in addition to successful preprocessing, binding

(Batch Binding/CBND) of an application program also results in the creation or

replacement of a package in the database. The contents of the package are

instructions used to satisfy database requests at run time.

When a package is created, a level of EXECUTE privilege is granted to its creator.

This level is dependent on several factors, such as the preprocessed SQL

statements, the existence and ownership of the referenced objects (tables, indexes,

and dbspaces), and the creator’s authorization level (DBA, RESOURCE, or

CONNECT). The creator’s EXECUTE privilege follows rules and conditions that

are discussed in the DB2 Server for VSE & VM Application Programming manual.

Database Administration

Connecting to an Application Server in VM

A VM user must have CONNECT authority and be connected to an application

server in order to perform SQL requests on it.

All VM users must connect to an application server explicitly or implicitly

regardless of whether they are accessing it in multiple user mode or single user

mode. If a user does not have a DB2 Server for VM authorization ID and

password, the user must connect implicitly. A user with a DB2 Server for VM

authorization ID and password can connect either implicitly or explicitly.

Establishing a Default Application Server

In order to run a preprocessor, the DBS Utility, any application program, or ISQL,

VM users must establish a default application server. This is done by invoking the

SQLINIT EXEC, and needs to be done only once.

Example

To establish the SQLDBA application server as the default, enter:

SQLINIT DBNAME(SQLDBA)

Information about the default application server chosen is stored on the VM user’s

minidisk (A-disk) in the ARISRMBT module and the LASTING GLOBALV file. If

the VM user wants to establish another application server as the default or to

change any of the options, he or she would have to re-run the SQLINIT EXEC. For

more information see “SQLINIT EXEC” on page 243.

Connecting to the Application Server Implicitly

Connecting to the application server implicitly means to connect to it without

providing an authorization ID and password explicitly. If a VM user does not

provide a CONNECT statement, then the first time that he or she tries to run an

SQL statement, the VM application requester connects to the application server

implicitly. The database manager checks its catalog tables to see whether that

user’s ID, the VM logon ID (established in the CP LOGON procedure), has been

granted CONNECT authority. (It does not compare the user’s CP LOGON

password with the DB2 Server for VM application server password, as it can be

assumed that a password that has been verified by the CP LOGON procedure is

valid.)

Most VM users will want to connect to the application server implicitly, so when

you grant them CONNECT authority, use their CP LOGON user IDs.

The implicit connect support works the same for VM application programs, for

ISQL, for the DBS Utility, and for remote application servers; however, each has its

own considerations, as discussed below.

Note: When working in an environment that includes several application servers

that can be accessed from several different application requesters, there is

the need for unique authorization IDs. The database manager does not

recognize the same authorization ID from two (or more) different

application requesters as being different. It is the administrator’s

responsibility to ensure that the authorization IDs in this situation are

unique.

Chapter 5. Providing Security

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