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

Products

Datacom DATACOM - AD Ideal CIS COMMON SERVICES FOR Z/OS 90S SERVICES DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS COMMON PRODUCT SERVICES COMPONENT Common Services CA ECOMETER SERVER COMPONENT FOC Easytrieve Report Generator for Common Services INFOCAI MAINTENANCE IPC UNICENTER JCLCHECK COMMON COMPONENT Mainframe VM Product Manager CHORUS SOFTWARE MANAGER CA ON DEMAND PORTAL CA Service Desk Manager - Unified Self Service PAM CLIENT FOR LINUX ON MAINFRAME MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME GRAPHICAL MANAGEMENT INTERFACE WEB ADMINISTRATOR FOR TOP SECRET Xpertware

Issue/Introduction

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.

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

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

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 ___

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

Environment

Release: DATABB00200-14-Datacom/AD
Component: