Blog

It’s surprising how many times you need to know whether a record exists in any one of two or more tables. Traditionally, you would have to check each table individually until you either find a match or have checked every table to confirm that no match exists.

Finding myself faced with just such a question, I started wondering whether I could check all of the tables in question in one go. And I can.

So I have a environment where records are written first to a transactions file and then, once the transactions are processed, to an archive. I need to know whether a transaction has been written to either table and if it hasn’t then I can do an insert.

My first check looked like this:

select
(select count(*) from TRANSACTIONS where TRANSID = 677447208) as count1,
(select count(*) from ARCHIVE where TRANSID = 677447208) as count2
from SYSIBM.SYSDUMMY1

This gives me a single row with a separate column for each count, which is a reasonable enough start and may prove to be useful at some point. What I really want right now, though, is a simple count of all records across all tables.

For this I have to adjust the query very slightly indeed:

select
(select count(*) from TRANSACTIONS where TRANSID = 677447208) +
(select count(*) from ARCHIVE where TRANSID = 677447208)
from SYSIBM.SYSDUMMY1

… to get a single count of all occurrences of the transaction across as many tables as I need to check.

The SQL TRANSLATE function is worth knowing about if you ever find yourself needing to (for example) fix data from an external feed before you attempt to do anything with it.

The function requires returns a value in which one or more characters in expression have been converted into other characters. You can pass it up to four parameters: the expression (obviously); the to-string; the from-string and a pad character.

You can use the function with only the expression, in which case the characters are simply converted to uppercase, which is a bit pointless. Things become useful when you specify the to-string and from-string:

If more than one argument is specified, the result string is built character by character from expression, converting characters in from-string to the corresponding character in to-string. For each character in expression, the same character is searched for in from-string. If the character is found to be the nth character in from-string, the resulting string will contain the nth character from to-string. If to-string is less than n characters long, the resulting string will contain the pad character. If the character is not found in from-string, it is moved to the result string unconverted.

Here’s an example.

I’ve knocked together a sample table with two columns — a code and a description — to represent a data feed. The description can contain accented characters, but we don’t want to see these in the target system.

The unadjusted table looks like this:

IDX   CODE    DESCRIPTION  
  1   FAC01   Façile       
  2   UML01   Umlaüt       

To get rid of those accents in the description, this query…

select                                                          
 IDX, CODE, translate(DESCRIPTION, 'eeaaocuouee', 'éèâàôçûöüëê')
from FEED                                                       

… returns this result:

IDX   CODE    TRANSLATE 
  1   FAC01   Facile    
  2   UML01   Umlaut    

If the from-string is longer than the to-string, the pad character is used to pad the to-string to ensure the lengths match. So, in the above example,

This query:

select translate(CODE, 'xx', '01') from FEED

… is equivalent to this one…

select translate(CODE, 'x', '01', 'x') from FEED

I’m not entirely convinced of the value of this argument. It is probably bot safer and clearer to simply ensure your to-string and from-string are the same length.

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