How to create 'DROP USER' syntax, with a SELECT statement and take that result and use it as input to an OCF/BCF step to perform the actions.

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

Description:

In IDMSBCF/OCF you can include the 'DROP USER ' syntax as a literal field column in your select statement, to produce a report with the desired syntax.
Forexample: SELECT 'DROP USER ', AUTHID AS COLUMN1, ';' AS COLUMN2 FROM IDMSSECU.USER;

Solution:

This solution requires the SQL Option.

To produce 'DROP USER' syntax with a SELECT statement in OCF/BCF use the steps that follow:

1. Prior to going into OCF:
   
   DCUF SET DICTNAME SYSDIRL
   DCUF SET DBNAME SYSUSER
   
   
2. In BCF/OCF use the following syntax:
   
   If you have not previously created an SQL schema from the non-SQL schema IDMSSECU you must do that first:
   
   CREATE SCHEMA IDMSSECU FOR NONSQL SCHEMA SYSDIRL.IDMSSECU VERSION 1 DBNAME SYSUSER;
   
   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               ;

   
   
3. Take 'Result 1' and 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               ;

• 'Result 2' can be used as input to OCF/BCF to perform the 'DROP USER' actions.

Environment

Release:
Component: IDMS