Using SQL to create DROP USER syntax

Products

• IDMS
• IDMS - Database

Issue/Introduction

This article describes how SQL can be used to generate DROP USER syntax for use in OCF/IDMSBCF.

Environment

Release: All supported releases.

Resolution

This solution requires the SQL Option.

To produce "DROP USER" syntax with a SELECT statement in OCF/IDMSBCF, use the steps that follow:

1. Prior to going into OCF:
   
   DCUF SET DICTNAME SYSDIRL
DCUF SET DBNAME SYSUSER
   
   
2. If it does not already exist, define the following SCHEMA:
   
   CREATE SCHEMA IDMSSECU FOR NONSQL SCHEMA SYSDIRL.IDMSSECU VERSION 1 DBNAME SYSUSER;
3. In OCF/IDMSBCF use the following syntax:
   
   SELECT 'DROP USER ', AUTHID AS COLUMN1, ';' AS COLUMN2 FROM IDMSSECU.USER;
   
   Result 1:
   
   *+ (CONST)     COLUMN1             COLUMN2
*+ -------     -------             -------
*+ DROP USER   USER1               ;
*+ DROP USER   USER2               ;
*+ DROP USER   USER3               ;
*+ DROP USER   USER4               ;
*+ DROP USER   USER5               ;
   
   
4. Take "Result 1", edit out the "HEADER" columns and remove the comment "*+" from beginning of each row.
   
   Result 2:
   
   DROP USER   USER1               ;
DROP USER   USER2               ;
DROP USER   USER3               ;
DROP USER   USER4               ;
DROP USER   USER5               ;
   
   
5. "Result 2" can be used as input to OCF/IDMSBCF to perform the "DROP USER" actions.