Interpreting the DSPPGMREF file usage

I mentioned the DSPPGMREF command a couple of weeks ago. This command allows you to generate a list of system objects used by specified programs and is particularly handy when you want to see which programs are doing what to a specific file. The only quirk is that the file usage field is a two digit field that needs to be interpreted.

This field is a decimal representation of a three-digit binary field for which the rightmost column (1) indicates the file is used for input, the middle column (2) indicates the file is used for output and the leftmost column (4) indicates the file is used for update. Combining these columns gives you a single digit (decimal) number that fully describes how the file is used.

Interpreting this field is simple enough, but I do have to keep looking up what the values mean. Since I am directing the output to a file and using SQL to query this file, I am also letting the SQL bitwise functions interpret the file usage for me.

At it’s simplest, this query:

select WHLIB, WHPNAM, WHTEXT, WHFNAM, WHLNAM, WHSNAM, WHFUSG,
       bitand(WHFUSG, 1) as Input,                           
       bitand(WHFUSG, 2) as Output,                          
       bitand(WHFUSG, 4) as Update                           
FROM LSCLIB/CROSSREF WHERE WHSNAM like '%LSCTST%'              

… gives me a result that looks like this:

Library     Program     Object       Library      Source       File Usage  INPUT  OUTPUT  UPDATE
                        Referenced                File Name                                     
LSCLIB      LSCT100     LSCTSTP      LSCLIB       LSCTSTP           3        1       2       0  
LSCLIB      LSCT100N    LSCTSTP      LSCLIB       LSCTSTP           3        1       2       0  
LSCLIB      LSCT110N    LSCTSTL1     LSCLIB       LSCTSTL1          1        1       0       0  
LSCLIB      LSCT120     LSCTSTL1     LSCLIB       LSCTSTL1          5        1       0       4  
LSCLIB      LSCT120N    LSCTSTL1     LSCLIB       LSCTSTL1          5        1       0       4  

If the file is used for input, the INPUT column contains a 1. If the file is used for output, the OUTPUT column contains a 2. And, if the file is updated, the UPDATE column contains a 4.

This is immediately more readable than just looking at the raw File Usage field, but it can be simplified further by using some case statements:

select WHLIB, WHPNAM, WHFNAM, WHLNAM, WHSNAM, WHFUSG,              
  case bitand(WHFUSG, 1) when 1 then 'Yes' else 'No' end as Input, 
  case bitand(WHFUSG, 2) when 2 then 'Yes' else 'No' end as Output,
  case bitand(WHFUSG, 4) when 4 then 'Yes' else 'No' end as Update 
FROM LSCLIB/CROSSREF WHERE WHSNAM like '%LSCTST%'                    

… gives me a result that looks like this:

Library     Program     Object       Library      Source       File Usage  INPUT  OUTPUT  UPDATE
                        Referenced                File Name                                     
LSCLIB      LSCT100     LSCTSTP      LSCLIB       LSCTSTP           3       Yes    Yes     No   
LSCLIB      LSCT100N    LSCTSTP      LSCLIB       LSCTSTP           3       Yes    Yes     No   
LSCLIB      LSCT110N    LSCTSTL1     LSCLIB       LSCTSTL1          1       Yes    No      No   
LSCLIB      LSCT120     LSCTSTL1     LSCLIB       LSCTSTL1          5       Yes    No      Yes  
LSCLIB      LSCT120N    LSCTSTL1     LSCLIB       LSCTSTL1          5       Yes    No      Yes