Changes between Initial Version and Version 1 of pushingData

Timestamp:

06/19/17 17:00:47 (8 years ago)

Author:

lttoth@…

Comment:

--

pushingData

@@ +1 @@
+= 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 [[https://drive.google.com/drive/folders/0B1lxXqYPLTCicW1OZmswd2dYdUk|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;
+}}}
+
+