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.

Document ID : KB000020281
Last Modified Date : 14/02/2018
Show Technical Document Details

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.