Creating synonyms for AutoSys databases running on Oracle for use with AAI

Products

• Automic Automation Intelligence

Issue/Introduction

For AutoSys databases using Oracle, you will need to create synonyms and grant read-only permissions for tables the Automic Automation Intelligence (AAI)application accesses.

If not, you will get the following error message when AAI attempts to query the tables: ORA-00942: table or view does not exist

Resolution

List of Synonyms - For pre-R11.x AutoSys instances such as AutoSys 4.0 and 4.5, the user must have read access to the following AutoSys database tables:

alamode
calendar
event
glob
job
job_cond
job_status
job2
machine
proc_event
timezones

 

For AutoSys R11.x and later* , the user must have read access to the following AutoSys database tables:

ujo_alamode
ujo_calendar
ujo_comm_send_alias
ujo_command_job
ujo_connectionprofile
ujo_event
ujo_file_watch_job
ujo_ftp_job
ujo_glob
ujo_hdfs_job
ujo_hive_job
ujo_i5_job
ujo_informatica_job
ujo_job
ujo_job_cond
ujo_job_resource_dep
ujo_job_status
ujo_jobtype
ujo_machine
ujo_meta_enumerations
ujo_meta_properties
ujo_meta_restart_afm_types
ujo_meta_types
ujo_micro_focus_job
ujo_monitor_object_job
ujo_monitor_winevent_log
ujo_oozie_job
ujo_oraapps
ujo_oraapps_props
ujo_oraapps_steps
ujo_peoplesoft_job
ujo_pig_job
ujo_proc_event
ujo_real_resource
ujo_sap_arcspec
ujo_sap_infodetail
ujo_sap_job
ujo_sap_jobstep
ujo_sap_prtspec
ujo_sap_recipient
ujo_sched_info
ujo_service_desk
ujo_snmp_job
ujo_spark_job
ujo_sql_job
ujo_sql_proc_parms
ujo_sqlagent_job
ujo_sqoop_job
ujo_strings
ujo_timezones
ujo_uninotify
ujo_virt_resource_lookup
ujo_web_services
ujo_web_services2
ujo_wol_job
ujo_ws_criteria
ujo_ws_parm
ujo_ws_security
ujo_zos_condcodes
ujo_zos_dsn_trigger
ujo_zos_job

* Above list is good through AutoSys R11.3.6 SP7

For Autosys R12.1 and later you will also need to add a synonym for:

ujo_ws_output_var

ujo_ws_info

ujo_meta_esj_data

 

How to Add a Synonym - 

Use the following syntax to add a synonym:

CREATE SYNONYM <JAWS_USER_ID>.<synonym_name> FOR <DB_ADMIN_ID>.<synonym_name>;

Use the following syntax to grant read-only permission for the synonym:

GRANT SELECT ON <DB_ADMIN_ID>.<synonym_name> TO <JAWS_USER_ID>;

Below is an example of adding a synonym using AEDBADMIN as the AutoSys schema owner and autojaws as the username provided in the JAWS Add Scheduler dialog:

 

 

To create and grant read access, execute the queries below: 

CREATE SYNONYM autojaws.ujo_alamode FOR AEDBADMIN.ujo_alamode;

GRANT SELECT ON AEDBADMIN.ujo_alamode TO autojaws;

Repeat the process to add all the necessary synonyms for the AutoSys scheduler you are using.

 

SQL Queries -

To determine which synonyms exist, run the following SQL as an administrative user on the AutoSys database. The queries presume that AEDBADMIN is the owner of the AutoSys schema:

SELECT *
FROM dba_synonyms
WHERE table_owner = 'AEDBADMIN'
ORDER BY synonym_name;
To list permissions: 

SELECT *
FROM dba_tab_privs
WHERE grantor = 'AEDBADMIN';

 

To retrieve all synonyms:

SELECT * 
FROM ALL_SYNONYMS;