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

Document ID : KB000019034
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

In our SQL environment, we have created synonyms for multiple CA Datacom/DB tables. How can I see a list of all these synonyms?

Solution:

In CA 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 CA 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 CA 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."