ZUAUSR Table Maintenance

Overview

Three types of table maintenance for the zuausr@prod user are ongoing.

• Cleaning out unused classes
• Consolidating classes
• Managing Fake or Terminated Users

Cleaning out Unused Classes

As we examine ZUAUSR tables, we at times find base classes that have no users associated with them. When that is the case, the functional user is consulted about its relevance. If the class is determined to be of no value, then the class is recorded in ZUAUSR administrative documents and deleted. See ​ZUAUSR > DELETE ZUAUSR CLASSES.

Removing a SUPER class

A typical report to determine membership follows:

FG_AR_ATN_CLS
Description
This class was created to finalize billing related to an audio conferencing service that was discontinued.  RayAnne Robinson created the class and assigned Laura Morisky to it who used it for a few months to clear out the accounts receivable.  Laura is its only member and has not used it since.
 
Class Definition
ZUAUSR@prod> select ztvclsd_class_inst, ztvclsd_assoc_class_name,
  2  ztvclsd_assoc_class_type, ztvclsd_assoc_class_inst
  3  from ztvclsd
  4  where ztvclsd_class_name = 'FG_AR_ATN_CLS';
 
CLS Inst   Associated Class Name          AssocType  AssocInst
---------- ------------------------------ ---------- ----------
LRGP       MTR_ATN_C                      BANNER     LRGP
LRGP       USR_DEFAULT_Q                  ORACLE     LRGP
LRGP       USR_DEFAULT_M                  ORACLE     LRGP
 
PROD       QAA_ACCESS_C                   BANNER     PROD
PROD       MTR_ATN_C                      BANNER     PROD
PROD       USR_DEFAULT_M                  ORACLE     PROD
PROD       USR_DEFAULT_Q                  ORACLE     PROD
 
7 rows selected.
Zero Members

Since there are no members, and this is a SUPER class, there are two steps required. One must first delete the class from ZTVCLSD, then delete it from ZTVCLSA. All entries in the ZTVCLSD table are deleted using the following command:

DELETE ztvclsd
where ztvclsd_class_name = 'FG_AR_ATN_CLS';

The associated class names are not removed from ZTVCLSA so they remain associated with SUPER classes that are actively used.

The following command deletes the class from ZTVCLSA

DELETE ztvclsa
where ztvclsa_class_name = 'FG_AR_ATN_CLS';

Removing an Associated Class

Several associated classes had not been used in ZUAUSR. If an associated class had not been included in a SUPER class, then removing it from ZTVCLSA is enough. However, if it had been included in a SUPER class, the following would suffice to remove its associations. The following was such a class

Type	Class Name	Instance
ORACLE	UA_APPWORX	APMT
ORACLE	UA_APPWORX	APMP

First query the number of classes to which its associated:

SELECT count(*) from ztvclsd
WHERE ztvclsd_assoc_class_name = 'UA_APPWORX';

A number results. This is the number you use to ensure that you do not delete any other classes from the ZUAUSR tables. The following deletes the class from any association to a SUPER class.

DELETE ztvclsd
WHERE ztvclsd_assoc_class_name = 'UA_APPWORX';

The number should match the result from the previous count. If it does, issue a "COMMIT;" command.

Managing Terminated or Fake Users

As time permits, users who have no classes assigned in ZUAUSR are compared to what privileges are assigned in GURUCLS and ORACLE. If those users have no roles in Banner or ORACLE, they are set to terminated status. This may be done in the ZUAUSR forms, but would require work from another co-worker who happened to be managing the queue. Setting the termination status can be done easily by performing three update queries as zuausr@prod:

INSERT INTO ztrusrd
(ztrusrd_user_acct_id, ztrusrd_class_type, 
 ztrusrd_class_name, ztrusrd_class_inst)
VALUES
('idxxx','SUPER','EMPLOYEE_TERMINATE_CLS','PROD');

UPDATE ztbusrh
SET ztbusrh_term_flag = 'X'
WHERE ztbusrh_user_acct_id = 'idxxx';

UPDATE ztbusrh_load
SET ztbusrh_term_flag = 'X'
WHERE ztbusrh_user_acct_id = 'idxxx';

commit;

At times, on TEST database instance or PREP database instance, fake users have been created that are not managed in both the ZTBUSRH_LOAD and ZTBUSRH table. These users may have Social Security Numbers of actual users who had been affiliated with UA at one time. Since no termination date is set and since they are not showing up as actual users, they linger in ZUAUSR.

As a policy, we are removing these users from ZUAUSR. The following must be run as zuausr@prod. One may find them in only one table, but I list both below:

DELETE ztbusrh
WHERE ztbusrh_user_acct_id = 'fakeid';

DELETE ztbusrh_load
WHERE ztbusrh_user_acct_id = 'fakeid';

commit;