2016-01 Simply Synonyms in DB2 z/OS (again)

As SYNONYMS are dying out, it is a good idea to start DROPping them as soon as you can…

In this short newsletter I wish to remind you all that SYNONYMS are dead! They may still be nailed to their perch (old Monty Python reference here!), but they are dead! Even the DB2 documentation has removed them and a big sign says DEPRECATED over the SQL syntax. So much for the preamble… The question is: What can you do? I will attempt to show you how to discover what size of problem you have, and provide some help in sorting out the mess of synonyms.

Synonyms were a good idea many years ago but they brought with them a few problems: one is that you lose the connection between a synonym and the alias it is created on, another is that GRANTs on synonyms actually are recorded in the DB2 Catalog against the base tables, and finally the SYNONYM did not agree with the rest of the world’s SQL Standards.

Finding the bad guys

Here’s a triplet of SQLs to find all the synonym-dependent items:

SELECT BSCHEMA    AS CREATOR                
     , BNAME      AS NAME                     
     , BCOLNAME   AS COLUMN_NAME              
     , CASE BTYPE                             
       WHEN 'A' THEN 'ALIAS                   '
       WHEN 'C' THEN 'COLUMN                  '
       WHEN 'F' THEN 'FUNCTION                '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE  '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE'
       WHEN 'Q' THEN 'SEQUENCE                '
       WHEN 'S' THEN 'SYNONYM                 '
       WHEN 'T' THEN 'TABLE                   '
       WHEN 'V' THEN 'VIEW                    '
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD      '
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD    '
       ELSE          'UNKNOWN                 '
       END        AS TYPE                     
     , BOWNER     AS OWNER                    
     , CASE BOWNERTYPE                        
       WHEN 'L' THEN 'ROLE   '                
       WHEN ' ' THEN 'AUTH ID'                
       ELSE          'UNKNOWN'                
       END        AS BOWNERTYPE               
     , DSCHEMA    AS DEP_CREATOR              
     , DNAME      AS DEP_NAME                 
     , DCOLNAME   AS DEP_COLUMN_NAME          
     , CASE DTYPE                             
       WHEN 'C' THEN 'GENERATED COLUMN'       
       WHEN 'F' THEN 'FUNCTION        '       
       WHEN 'I' THEN 'INDEX           '       
       WHEN 'X' THEN 'ROW PERMISSION  '       
       WHEN 'Y' THEN 'COLUMN MASK     '       
       ELSE          'UNKNOWN         '       
       END        AS DEP_TYPE                  
     , DOWNER     AS DEP_OWNER                
     , CASE DOWNERTYPE                        
       WHEN 'L' THEN 'ROLE   '                
       WHEN ' ' THEN 'AUTH ID'                
       ELSE          'UNKNOWN'                
       END        AS DEP_OWNERTYPE            
FROM SYSIBM.SYSDEPENDENCIES                
WHERE BTYPE = 'S'                             
ORDER BY 1 , 2 , 3 , 5 , 7 , 8 , 9 , 11       
WITH UR                                       
;

SELECT BQUALIFIER AS CREATOR                 
     , BNAME      AS NAME                             
     , CASE BTYPE                                 
       WHEN 'A' THEN 'ALIAS                      '    
       WHEN 'B' THEN 'BUSINESS_TIME              '  
       WHEN 'C' THEN 'SYSTEM_TIME                '    
       WHEN 'F' THEN 'UDF OR CAST FUNCTION       '    
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE     '    
       WHEN 'I' THEN 'INDEX                      '    
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE   '    
       WHEN 'O' THEN 'STORED PROCEDURE           '    
       WHEN 'P' THEN 'LARGE OR DSSIZE PART. SPACE'    
       WHEN 'Q' THEN 'SEQUENCE OBJECT            '    
       WHEN 'R' THEN 'TABLESPACE                 '    
       WHEN 'S' THEN 'SYNONYM                    '    
       WHEN 'T' THEN 'TABLE                      '    
       WHEN 'U' THEN 'DISTINCT TYPE              '    
       WHEN 'V' THEN 'VIEW                       '    
       WHEN 'W' THEN 'SYSTEM_TIME PERIOD         '    
       WHEN 'Z' THEN 'BUSINESS_TIME PERIOD       '    
       ELSE          'UNKNOWN                    '    
       END        AS OBJECT_TYPE                       
     , DCOLLID AS COLLECTION                          
     , DNAME   AS PACKAGE                             
     , HEX(DCONTOKEN) AS DCONTOKEN                    
     , CASE DTYPE                                     
       WHEN 'F' THEN 'COMPILED SQL SCALAR FUNCTION    '
       WHEN 'N' THEN 'NATIVE SQL ROUTINE PACKAGE      '
       WHEN 'O' THEN 'ORIGINAL COPY OF A PACKAGE      '
       WHEN 'P' THEN 'PREVIOUS COPY OF A PACKAGE      '
       WHEN 'R' THEN 'RESERVED FOR IBM USE            '
       WHEN 'T' THEN 'TRIGGER PACKAGE                 '
       WHEN ' ' THEN 'NOT A TRIGGER/NATIVE SQL PACKAGE'
       ELSE          'UNKNOWN                         '
       END        AS PACKAGE_TYPE                     
     , DOWNER     AS OWNER                            
     , CASE DOWNERTYPE                                
       WHEN 'L' THEN 'ROLE   '                        
       WHEN ' ' THEN 'AUTH ID'                        
       ELSE          'UNKNOWN'                         
       END        AS DOWNERTYPE                       
FROM SYSIBM.SYSPACKDEP                                
WHERE BTYPE = 'S'                                     
ORDER BY 1 , 2 , 7                                    
WITH UR                                                
;

SELECT BCREATOR   AS CREATOR
     , BNAME      AS NAME
     , CASE BTYPE
       WHEN 'A' THEN 'ALIAS                      '
       WHEN 'E' THEN 'INSTEAD OF TRIGGER         '
       WHEN 'F' THEN 'UDF OR CAST FUNCTION       '
       WHEN 'G' THEN 'GLOBAL TEMPORARY TABLE     '
       WHEN 'I' THEN 'INDEX                      '
       WHEN 'M' THEN 'MATERIALIZED QUERY TABLE   '
       WHEN 'O' THEN 'STORED PROCEDURE           '
       WHEN 'P' THEN 'LARGE OR DSSIZE PART. SPACE'
       WHEN 'Q' THEN 'SEQUENCE OBJECT            '
       WHEN 'R' THEN 'TABLESPACE                 '
       WHEN 'S' THEN 'SYNONYM                    '
       WHEN 'T' THEN 'TABLE                      '
       WHEN 'V' THEN 'VIEW                       '
       ELSE          'UNKNOWN                    '
       END        AS OBJECT_TYPE
     , DNAME   AS PLAN_NAME
FROM SYSIBM.SYSPLANDEP
WHERE BTYPE = 'S'
ORDER BY 1 , 2
WITH UR
;

If you get no data from using these queries, then that is very good indeed! If you do get data, then you must make a note of all the objects listed, as they will be affected by what we are going to do next. “Affected”, in this case, could be as simple as a REBIND, but could also be as complex as recreating UDFs.

Generate the drop

-- CREATE DRIVER TABLE FOR CARTESIAN JOIN PROCESSING
DECLARE GLOBAL TEMPORARY TABLE DRIVER (NUMBER SMALLINT) ;
INSERT INTO SESSION.DRIVER VALUES 1 ;
INSERT INTO SESSION.DRIVER VALUES 2 ;
INSERT INTO SESSION.DRIVER VALUES 3 ;
INSERT INTO SESSION.DRIVER VALUES 4 ;
INSERT INTO SESSION.DRIVER VALUES 5 ;
-- NOW GENERATE DROP AND CREATE ALIAS COMMANDS
WITH T1 ( CREATOR
        , NAME
        , ALIAS_NAME
        , ALIAS_TABLE
          )
     AS ( SELECT STRIP(CREATOR)
               , STRIP(NAME)
               , STRIP(CREATOR)   CONCAT '.' CONCAT STRIP(NAME)
               , STRIP(TBCREATOR) CONCAT '.' CONCAT STRIP(TBNAME)
          FROM SYSIBM.SYSSYNONYMS
          ORDER BY 1
        )
SELECT CAST(CASE NUMBER
            WHEN 1 THEN 'SET CURRENT SQLID = '''
                        CONCAT CREATOR CONCAT ''' $'
            WHEN 2 THEN 'DROP SYNONYM ' CONCAT NAME CONCAT ' $'
            WHEN 3 THEN 'CREATE ALIAS ' CONCAT ALIAS_NAME
            WHEN 4 THEN 'FOR ' CONCAT ALIAS_TABLE
            WHEN 5 THEN '$'
            END AS CHAR(72))
FROM T1, SESSION.DRIVER
;

The output of this contains the “$” as command terminator, so you must either use a “C ALL $ ;” style ISPF command, or just use a “–#SET TERMINATOR $” line in the SPUFI.

One thing to remember, before executing the generated output, is the problem of the GRANTs. Remember that all GRANTs are recorded at the table level not at the synonym level, so you have two choices here:

1. Ignore the GRANTs and clean up later
2. Analyze the SYSIBM.SYSTABAUTH table to see if any GRANTs are there

Whichever way you choose, there will be work involved! However, as SYNONYMS are dying out, it is a good idea to start DROPping them as soon as you can.

I hope you liked this month’s Topic.

As always, any questions or comments would be most welcome!

TTFN,

Roy Boxwell