How can I list SQL Table synonyms I have created in Datacom/DB?

Products

• Datacom
• DATACOM - AD

Issue/Introduction

In our SQL environment, we have created synonyms for multiple Datacom/DB tables. How can I see a list of all these synonyms?Here is a sample DQL query:

Environment

Datacom/AD R14.0

Resolution

In Datacom/DB, a synonym can be created for a table through the following command:

CREATE SYNONYM synonym-name FOR authid.tablename;

Example: CREATE SYNONYM SYNAUTH FOR SYSADM.AUTHORIZATION;

These synonyms are stored in the Datadictionary, and can be listed in several ways.

1. Using the DDUTILTY program to produce an INDEX report.
   
   Here is a sample command set:
   

   //SYSIN DD *-USR DATACOM-INSTALL,NEWUSER -DEF PATH,SYN -DEF TRACE,SYNONYM.TABLE,SYN-TBL-REFERS -END -RPT START,SYNONYM,ALL(PROD),SYN -RPT INDEX -END/*

   
   Here is the report (the right side of the report was removed for this display):
   

   Date: mm/dd/yyyy  Time: hh.mm.ss      ***********************************                                      *               CA Datacom DatadictSecurity Level: 1                     *                     Index Report                                       *  COPYRIGHT (C) 1990-2011 CA.   AL User: DATACOM-INSTALL                 ***********************************ENTITY-TYPE..................... OCCURRENCE...................... VRSN ST   RECORD..........................     ELEMENT......................... SYNONYM                          SYSUSR-SYNAUTH                   0001  PTABLE                            AUTHORIZATION                    0012  P SYNONYM                          SYSUSR-SYNBASE                   0001  PTABLE                            DATABASE                         0016  P 

2. Using the DDUTILTY program to produce an INDENTED report.
   
   Here is a sample command set:
   

   //SYSIN        DD *                     -USR DATACOM-INSTALL,NEWUSER            -DEF PATH,SYN                           -DEF TRACE,SYNONYM.TABLE,SYN-TBL-REFERS -END                                    -RPT START,SYNONYM,ALL(PROD),SYN        -RPT INDENT                             -END                                    /*

   
   Here is the report (the right side of the report was removed for this display):
   

   Date: mm/dd/yyyy  Time: hh.mm.ss      ***********************************                                       *               CA Datacom Datadict Security Level: 1                     *                   Indented Report                                       *  COPYRIGHT (C) 1990-2011 CA.   AL User: DATACOM-INSTALL                 *********************************** ENTITY-TYPE...................   OCCURRENCE..............................                                    DESCRIPTION..........................                                        SQLNAME...........................                                                                          SYN                              SYSUSR-SYNAUTH                                                                 SYSUSR.SYNAUTH                       TBL                           AUTHORIZATION                                                               AUTHORIZATION-ENTITY-TYPE-RECORD                                            SYSADM.AUTHORIZATION              SYN                              SYSUSR-SYNBASE                                                                 SYSUSR.SYNBASE                       TBL                           DATABASE                                                                    DD ENTITY DATABASE                                                          SYSADM.DATABASE                   

3. Using a small SQL query through batch program DBSQLPR or submitted through Datacom Server.
   
   Here is a sample SQL query:
   

   //SYSIN    DD  *                                 -- .5...10....5...20....5...30....5...40....5  SELECT DISTINCT SUBJ_OCC_NAME AS "SYNONYM NAME",                  OBJ_OCC_NAME  AS "TABLE NAME"     FROM SYSADM.RELATIONSHIP                          WHERE ENTITY_NAME = 'SYN-TBL-REFERS';           /*Here are the results:SYNONYM NAME                     TABLE NAME                           CHAR(32) NOT NULL                CHAR(32) NOT NULL                    ________________________________ ________________________________     SYSUSR-SYNAUTH                   AUTHORIZATION                        SYSUSR-SYNBASE                   DATABASE                             ___ 2 rows returned ___                                                

   
   
   
4. Using Dataquery (online or batch) in DQL mode.
   
   Here is a sample DQL query:
   

   FIND ALL RELATIONSHIP     WITH ENTITY-NAME = 'SYN-TBL-REFERS'     PRINT  SUBJ-OCC-NAME 'Synonym/Name'                                  OBJ-OCC-NAME  'Table/Name'                                 TITLE 'Report of Datadictionary Table Synonyms'        Here is the report (header information removed for right side of the report):mm/dd/yyyy                                    Report of Datadictionary Tahh:mm:ss                                                                  Synonym                           Table                                Name                             Name                              --------------------------------  --------------------------------  SYSUSR-SYNAUTH                    AUTHORIZATION                     SYSUSR-SYNBASE                    DATABASE                        DQ284I - END OF REPORT                                                   

For more information on using synonyms, please see the CA Datacom/DB SQL User Guide, in the section "Creating SQL Objects > Creating a Synonym."