Using Global Amper Variables to Create Dynamic DBA Rules

How to:

The DBA attributes USER and VALUE can be parameterized using global Dialogue Manager variables. Using this technique, you can create dynamic DBA rules for the connected user without creating a DBAFILE that contains rules for all users. You can obtain the user ID of the connected user and optionally, a value restriction for that user and insert them directly into the DBA section of the Master File. One convenient way to do this is to use a Master File profile. For information about Master File profiles, see Creating and Using a Master File Profile.

Note: All Master Files that contain DBA rules should be encrypted.
Top of page
x
Syntax: How to Use Global Variables in the DBA Section of a Master File

First assign the variables names after the FILE declaration in the Master File:

VARIABLE NAME=[&&]var, USAGE=Aln, [DEFAULT=defvalue,]
 [QUOTED={OFF|ON},] $

where:

var

Is the name you are assigning to the global variable. When you reference the variable in the Master File or Access File, you must prepend the name with two ampersands (&&). However, the ampersands are optional when defining the variable.

ln

Is the maximum length for the variable value.

defvalue

Is the default value for the variable. If no value is set at run time, this value is used.

QUOTED={OFF|ON}

ON adds single quotation marks around the assigned string for the variable. A single quotation mark within the string is converted to two single quotation marks. OFF is the default value.

Next, reference the global variables in the DBA section of the Master File:

USER={user|&&uid},ACCESS=a[,RESTRICT=VALUE,NAME=name,VALUE={value|&&val}] ,$
Example: Creating a Dynamic DBA Rule in a Master File

The sequential data source named VALTEST.DATA contains a list of user names and their associated value restrictions. 

SALLY               CURR_SAL LT 20000
JOHN                DEPARTMENT EQ PRODUCTION
TOM                 CURR_SAL GE 20000

Before reading this file, you must FILEDEF or allocate it.

FILEDEF VALTEST DISK baseapp/valtest.data

Or, on z/OS under PDS deployment:

DYNAM ALLOC DD VALTEST DA USER1.VALTEST.DATA SHR REU

The following Master File named EMPDBA is a view of the EMPLOYEE data source. It has a DBA section that uses the global variable &&UID for the USER attribute and the global variable &&VAL for the value test against the EMPINFO segment. It also identifies a Master File profile named DBAEMP3. This profile will obtain the user ID of the connected user and find the correct VALUE restriction by reading the VALTEST.DATA file. By setting the global variables to the correct values, it will insert the appropriate DBA rule into the Master File.

FILENAME=EMPLOYEE, SUFFIX=FOC, MFD_PROFILE=DBAEMP3,$
VARIABLE NAME=&&UID, USAGE=A8 , $
VARIABLE NAME=&&VAL, USAGE=A25, $
SEGNAME=EMPINFO,  SEGTYPE=S1
 FIELDNAME=EMP_ID,       ALIAS=EID,     FORMAT=A9,       $
 FIELDNAME=LAST_NAME,    ALIAS=LN,      FORMAT=A15,      $
 FIELDNAME=FIRST_NAME,   ALIAS=FN,      FORMAT=A10,      $
 FIELDNAME=HIRE_DATE,    ALIAS=HDT,     FORMAT=I6YMD,    $
 FIELDNAME=DEPARTMENT,   ALIAS=DPT,     FORMAT=A10,      $
 FIELDNAME=CURR_SAL,     ALIAS=CSAL,    FORMAT=D12.2M,   $
 FIELDNAME=CURR_JOBCODE, ALIAS=CJC,     FORMAT=A3,       $
 FIELDNAME=ED_HRS,       ALIAS=OJT,     FORMAT=F6.2,     $
END
DBA=DBAUSER1,$
USER=&&UID,ACCESS=R,RESTRICT=VALUE,NAME=EMPINFO,VALUE=&&VAL ,$

The following is the MFD_PROFILE procedure.

SET MESSAGE = OFF
-SET &VALUETEST = 'NOTFOUND';
-* Find the user ID of the connected user
-SET &&UID = GETUSER('A20');
-SET &&UID = TRUNCATE(&&UID);
-* Create a HOLD file with the value test for the connected user
TABLE FILE VALTEST
PRINT VALUETEST
WHERE USERNAME EQ '&&UID'
ON TABLE HOLD AS USERVAL FORMAT ALPHA
END
-RUN
-READ USERVAL &VALUETEST.A30
-* If the user name was not in the file, type a message and exit
-IF &VALUETEST NE 'NOTFOUND' GOTO SETVALUE;
-TYPE USER WASN'T THERE
-EXIT
-SETVALUE
-* Set the global variable for the value test to the correct test
-SET &&VAL = ''|&VALUETEST||'';
-* Set the USER parameter to the user ID of the connected user
SET USER = &&UID

The following request displays a report against the EMPDBA view of the EMPLOYEE data source.

USE
EMPLOYEE AS EMPDBA
END
-RUN
TABLE FILE EMPDBA
PRINT LN FN CURR_SAL
BY DEPARTMENT
ON TABLE SET PAGE NOPAGE
END

Running the request when SALLY is the connected user produces a report of employees whose salaries are less than $20,000.

DEPARTMENT  LAST_NAME        FIRST_NAME         CURR_SAL
----------  ---------        ----------         --------
MIS         SMITH            MARY             $13,200.00
            JONES            DIANE            $18,480.00
            MCCOY            JOHN             $18,480.00
            GREENSPAN        MARY              $9,000.00
PRODUCTION  STEVENS          ALFRED           $11,000.00
            SMITH            RICHARD           $9,500.00
            MCKNIGHT         ROGER            $16,100.00

Running the request when TOM is the connected user produces a report of employees whose salaries are greater than or equal to $20,000.

DEPARTMENT  LAST_NAME        FIRST_NAME         CURR_SAL
----------  ---------        ----------         --------
MIS         BLACKWOOD        ROSEMARIE        $21,780.00
            CROSS            BARBARA          $27,062.00
PRODUCTION  BANNING          JOHN             $29,700.00
            IRVING           JOAN             $26,862.00
            ROMANS           ANTHONY          $21,120.00

Running the request when JOHN is the connected user produces a report that includes only the PRODUCTION department.

DEPARTMENT  LAST_NAME        FIRST_NAME         CURR_SAL
----------  ---------        ----------         --------
PRODUCTION  STEVENS          ALFRED           $11,000.00
            SMITH            RICHARD           $9,500.00
            BANNING          JOHN             $29,700.00
            IRVING           JOAN             $26,862.00
            ROMANS           ANTHONY          $21,120.00
            MCKNIGHT         ROGER            $16,100.00
WebFOCUS