How to query RA FTP Job and parameter information

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

Is there a sql statement to query RA FTP Job and Component name and parameter information?

Environment

Release : 9.x

Component : CA Automic Applications Manager (AM)

Resolution

RA FTP Job and component name and parameter information can be found 

For Process Flow components:

select a.so_module as "Process Flow Name", b.so_task_name as "Component Name", c.aw_comp_xml_clob as "XML String"
from so_job_table a, so_chain_detail b, aw_chain_detail_xml c
where a.so_job_seq=b.so_chain_seq
and b.so_det_seq=c.aw_det_seq
and c.aw_comp_xml_clob like '%cit name="FtpAgent"%';

For Jobs:

select a.so_module as "Job Name", b.aw_job_xml_clob as "XML String"
from so_job_table a, aw_module_xml b
where a.so_job_seq=b.aw_job_seq
and b.aw_job_xml_clob like '%cit name="FtpAgent"%';

Parameters of Rapid Automation Jobs such as RA FTP Jobs are stored in a long XML string that needs to be extrapolated. Below is example of the xml parameters for from/to connection parameters as well as from/to file path: 

prompt_0_destDir=
prompt_0_fromConnection=
prompt_0_sourceDir=
prompt_0_toConnection=