Managing Objects in Mainframe › Working with Repository Objects › Working with Approval Jobs

Working with Approval Jobs

CA Repository Webstation Option uses stewards to approve the pending request for all data modification actions such as update, insert, or delete. A steward is a workstation associated with a privilege assigned to a list of users having approval rights. CA Repository Webstation Option uses the workstation privilege object to determine the users that are authorized to approve change requests for a steward.

An approver can view the pending change requests for data modification in the form of a table by selecting Approval tab on the home page. The entries in the approval view are obtained from the approval table that stores each pending change request as a record. The approval page offers three different views to display the status of the change requests, listed as follows:

• Valid Requests

  The approver can see all valid pending requests raised by any user. However, when the request is approved it is removed from the list of valid requests.

• Invalid requests

  The approver can see all invalid pending requests and rejected requests raised by any user. There is an option to delete the invalid requests permanently.

• My requests

  This view lists all change requests (valid or invalid) raised by a user.

As an approver, you can use the following options to approve or reject the requests.

• Show Details

  You can use this option to see the details of the pending requests before approving or rejecting the requests.

• Approve

  Use this option to approve the change request.

• Reject

  Use this option to reject the change requests.

Query to View Change Requests for an Approver

CA Repository Webstation Option uses the following query to obtain a list of all objects that have pending change requests for an approver:

SELECT DISTINCT (A.ENT_ID, E.ENT_NAME, UP.NAME AS STEWARD, NAME, STATUS, VERSION)
       COUNT(*)
FROM DBX_APPROVAL A,  DBX_WORKSTN_PRIV WP, DBX_PRIV_USER  UP ,
     DBX_WORKSTATION_D W, DBX_WKSN_XREF WX, DBX_ENT_TYPE_DESC E
WHERE UP.TSO_LOGON = :USERID THAT IS LOGGED IN AND
      UP.PRIV_NAME = WP.PRIV_NAME, AND
      UP.WKSN_NAME = W.NAME AND
      W.ENT_ID = WX.WKSN_ID AND
      WX.ENT_ID = A.ENT_ID AND A.ENT_TYPE = E.ENT_TYPE

Query to View All Change Requests for an Object

The following query displays a list of all pending change requests for an object:

SELECT ENT_ROWID,  ENT_ID, ENT_TYPE, NAME, STATUS, VERSION, 
REQUEST_TIMESTAMP, REQUEST_USER, DESCRIPTION 
FROM DBX_APPROVAL 
WHERE NAME = :SELECTED NAME AND STATUS = :SELECTED STATUS AND
      VERSION = :SELECTED VERSION 
      ORDER BY REQUEST TIMESTAMP, REQUEST_USER

Mainframe Changes for Approval Process

To implement approval process, the DB2 Default panel is updated to include following changes:

• A new parameter Approvals Required is added to the DB2 Defaults panel. The keyword for the parameter is AppReq Key_value_text.

  The valid values for Approvals Required are Y or N, and it is stored in the dbx_system table.

• Another parameter Reject Batch for Pending Approval is added; the valid values are: Y or N, which, is stored the dbx_system table. The parameter is defined by the keyword RejBatch Key_value_text. Depending on the value of RejBatch Key_value_text, the CA Repository for Webstation Option decides whether to accept or reject any update or delete while running a batch job if the row being updated or deleted has a pending approval request against it. This also applies to all text processing tasks.
• The value of Reject Batch for Pending Approval applies to all text processing tasks also. If there is a pending request against the row the batch job will fail if the text is being updated for that row.
• All update, delete, move or replace tasks for a row that is performed through the online ISPF screens are checked for pending approval request. The batch job will either fail or succeed depending on the value of Reject Batch.
• The RejBatch Key_value_text parameter is also stored in the DBXPARM file so that it can be read by batch jobs that do not access DB2.

  The following screen displays a sample DB2 default panel:

  ------------------------- CA Repository for z/OS -------------
  

  ----------------------------- DB2 Defaults ---------------------
  

  COMMAND ===>                                                        
  

                Default DB2 Subsystem Name         ===>  D81A                       
  

                Default Storage Group                    ===>   DATA720                    
  

                Control Table Storage Group           ===>   CNTL720                    
  

                Default Buffer Pool                          ===>  BP1                        
  

                Default Index Buffer Pool                 ===>  BP2                        
  

                Repository Owner                           ===>  ARZR720                    
  

                Catalog Owner                                 ===>  SYSIBM                     
  

                 WLM environment Name                  ===>  D81AWLM                    
  

                AR/zOS Plan name                            ===>  ARZR720                    
  

                AR/zOS Package Collection               ===>  ARZR720_PACK               
  

                Implement Audit/History Trail              ===>  N  (Y or N)                
  

                Defer DB2 Data Set Creation             ===>  Y  (Y or N)                
  

                Require Approval Process                 ===>  N  (Y or N)  
  

                 Reject Batch for Pending Approvals ===>  N  (Y or N)                
  

  The following screen allows you to enter CA Repository Webstation Option database Names:

          Control Database ===> ARZ720CN           Database 2 ===> ARZ7202 
  

          Database 3 ===> ARZ7203            Database 4 ===> ARZ7201 
  

          Database 5 ===> ARZ7204            Database 6 ===> ARZ7205 
  

          Database 7 ===> ARZ7206            Database 8 ===> ARZ7207 
  

          Database 9 ===> ARZ7208           Database 10 ===> ARZ7209 
  

  You can define DB2 Install parameters as follows:

         DB2 Version                     ===> 8 (7, 8)                           
  

         Decimal Point Indicator      ===> ; (. OR ,)                         
  

         String Delimiter                  ===> S (S-Single Quote,D-Double Quotes) 
  

• A new column called APPROVAL_REQ is added to the DBX_ENT_TYPE_DESC table. You can enter the APPROVAL attribute in the entity detail panel.
• The DBX_EXT_ENTITY extend table will be updated to have the attribute APPROVAL_REQ. The SYSTEM.EXTEND.BUILD command will need to save this attribute in the control table DBX_ENT_TYPE_DESC. The SYSTEM.EXTEND.IMPORT will retrieve the value from the control table and store it in the extend table.
• The SQL for approval table DDL is as follows:

  	CREATE DATABASE APPROV;             
  	CREATE TABLESPACE APPRTS IN APPROV; 
  	COMMIT;                             
   
  	CREATE TABLE  DBX_APPROVAL                             
           	(  ENT_ROWID  ROWID NOT NULL GENERATED ALWAYS,
              	ENT_ID              INTEGER,
             	ENT_TYPE            SMALLINT,
             	NAME                VARCHAR(245),          
              	STATUS              CHAR(8),               
             	VERSION             SMALLINT,              
             	REQUEST_TIMESTAMP   TIMESTAMP,             
             	REQUEST_USER        CHAR(8),               
              	DESCRIPTION         VARCHAR(512),          
              	REQUEST_DOC         CLOB(1073K),           
              	CREATE_BY           CHAR(8),               
              	MOD_BY              CHAR(8),               
              	CREATE_DATE         DATE,                  
              	CREATE_TIME         TIME,                  
              	MOD_DATE            DATE,                  
              	MOD_TIME            TIME)                  
        	IN APPROV.APPRTS                                 
        	;
   
     	CREATE LOB TABLESPACE APCLOB         
         IN APPROV                        
         LOG NO                           
   	;                                      
     	CREATE AUXILIARY TABLE APPROVAL_CLOB 
        	  IN APPROV.APCLOB                 
        	  STORES DBX_APPROVAL              
        	  COLUMN REQUEST_DOC               
   
     	CREATE UNIQUE INDEX XAPPROVAL 
       	ON APPROVAL_CLOB