ESP dSeries Workload Automation

 View Only
Expand all | Collapse all

CAWA DE DB Schema

  • 1.  CAWA DE DB Schema

    Posted Jun 04, 2020 07:24 AM
    Does anyone know which Database Schema's/Tables FTP job information, and ORACLE Financial job information are stored?




  • 2.  RE: CAWA DE DB Schema

    Broadcom Employee
    Posted Jun 05, 2020 01:31 AM
    Hi,

    All jobs in an application are stored as xmls in ESP_AMS_DF table. 

    Ravi Kiran


  • 3.  RE: CAWA DE DB Schema

    Posted Jun 05, 2020 07:08 AM
    Edited by Kenneth Chrapczynski Jun 05, 2020 07:09 AM
    Hi Ravi,

    Are there tables specific to FTP and ORACLE job definitions? Like the columns/information I can extract from ESP_GENERIC_JOB?

    I'm specifically looking for Remote File Name, Local File Name, Agent , Server Address. And for ORACLE jobs I'm looking for OA Application Name, Program Name and the OA Parameters (User Name, Responsibility, Program Arguments).

    I would like to create a queries similar to this, but for FTP information, and ORACLE Job information.

    SELECT A.[APPL_NAME]  
                  ,A.[EVENT_NAME]
                  ,B.[JOB_NAME]
                  ,B.[JOB_QUALIFIER] 
                  ,B.[START_DATE_TIME]
                  ,B.[END_DATE_TIME]
                  ,A.[STATE]

              
      FROM [CAWorkload_Prod].[dbo].[ESP_APPLICATION]  A inner join [CAWorkload_Prod].[dbo].[ESP_GENERIC_JOB] B  on A.APPL_ID = B.APPL_ID

    WHERE A.APPL_NAME LIKE 'PS_%%' AND A.[STATE] LIKE 'Complete' AND B.[START_DATE_TIME]>'1/23/2020 15:00 PM' AND b.END_DATE_TIME<'1/24/2020 07:00 AM'  ORDER by START_DATE_TIME asc
    ;


  • 4.  RE: CAWA DE DB Schema

    Posted Jun 05, 2020 04:43 PM
    Edited by Andy Reimer Jun 05, 2020 09:02 PM
    This may or may not be helpful, but I've found it quite useful to extract information from the XML files that Workload Automation exports using the imexutil.
    These XML files have all the information about the applications and jobs.  After loading the XML into an xml variable you can use the select-xml command to pull just about any information you want out of the application.

    Example using PowerShell:
    [xml]$xmlData = Get-Content -Path 'D:\ACP010'
    Select-Xml -Xml $xmlData -XPath "//ns:script_definition" -Namespace @{ns="http://dto.wa.ca.com/application"} | foreach {write-host -ForegroundColor Green $_.Node.ParentNode.ParentNode.ParentNode.ParentNode.Name -NoNewline; Write-Host "`t`t`t`t   " $_.node.innertext "`n"}​

    I load the application ACP010 into an xml variable called $xmlData
    Since all inline Javascript is stored in XML tags called <script_definition> I can specify that in the -XPath argument, then pipe that to a foreach command that prints the job name, a few tabs, and the content of the <script_definition> element (the actual JavaScript).

    The output looks like this:


    I can see that this application has two jobs with inline JavaScript; ACPDLYR and ACPANNR, and I can see the content of those scripts.

    Similar reports can be done with anything you are looking for in your applications.  It's not as direct as a SQL query, but you can inspect the XML at will to determine what you are looking for, and there is a certain comfort (for me) knowing that everything I'm doing is offline from the Workload Automation database. I use PowerShell, but this could just as easily be done with Perl.

    I can provide more examples and explanation if you want.

    ------------------------------
    Andy Reimer
    ------------------------------



  • 5.  RE: CAWA DE DB Schema

    Posted Jun 08, 2020 07:27 AM
    Andy,

    Thanks for your reply. What you are suggesting looks very interesting. Is the data you are extracting current, or historical? I have never used the imexutil, this would be opening a whole new world to me, ha! I guess I need to read up on "imexutil" before your next reply...

    I'm looking for FTP job information (Remote File Name, Local File Name, Agent , Server Address). In the screenshot below you there isn't any FTP Job Definition info, only the parts that are from the Application and GENERIC Job tables. If what you suggested can do this then I'm interested. But there should be a table where these are stored I just haven't been able to locate it yet.




  • 6.  RE: CAWA DE DB Schema

    Posted Jun 08, 2020 08:21 AM
    Edited by Andy Reimer Jun 08, 2020 08:25 AM
    I think the short answer in your case is that the native FTP job type does not have command or arguments fields.

    imexutl exports (or imports) pretty much any artifact in Workload Automation (applications, events, resources, alerts, etc) as xml files.  It always exports the current version so they are not historical in that sense, and it contains no historical run data.  It is just the xml version of actual artifacts.

    For example, in Workload Automation an FTP job type looks like this:

    If you were to look at the xml for the application in which this job was found, you would see this as part of the xml file.
    	<ftp_job name="TESTFPT" qualifier="EXAMPLE">
    		<comment>not much to say</comment>
    		<dependencies>
    			<relcount>0</relcount>
    		</dependencies>
    		<notifynodefaults>false</notifynodefaults>
    		<alert_notifynodefaults>false</alert_notifynodefaults>
    		<snmp_notifynodefaults>false</snmp_notifynodefaults>
    		<hold>true</hold>
    		<request>false</request>
    		<conditional>false</conditional>
    		<criticaljob>false</criticaljob>
    		<job_ancestor_wait_default_ignore>true</job_ancestor_wait_default_ignore>
    		<retry>
    			<retry_count>0</retry_count>
    			<retry_interval>0</retry_interval>
    			<last_notify_email>false</last_notify_email>
    			<last_notify_alerts>false</last_notify_alerts>
    			<last_notify_snmp>false</last_notify_snmp>
    		</retry>
    		<subappl_wait>false</subappl_wait>
    		<agent>FTPP02</agent>
    		<transferdirection>DOWNLOAD</transferdirection>
    		<serveraddress>216.181.92.85</serveraddress>
    		<remotename>a_remote_file.txt</remotename>
    		<localname>a_local_file.txt</localname>
    		<userid>FTPXFR</userid>
    		<transfertype>B</transfertype>
    		<commands>
    			<command>lcd testdir</command>
    			<command>ls</command>
    		</commands>
    	</ftp_job>
    What I'm describing is useful for answering questions like :
    • What jobs have inline javascipt?
    • How many FTP jobs do I have?
    • What resources are defined across all jobs in all applications?
    • What are the File Trigger events using agent XXX?
    • How many jobs use the Alert named XXX?

    What it can't tell you is when jobs ran.

    ------------------------------
    Andy Reimer
    ------------------------------



  • 7.  RE: CAWA DE DB Schema

    Posted Jun 08, 2020 08:51 AM
    Sorry I wasn't clearer in my reply. I know FTP doesn't have command/arguments columns, but they do have (Remote File Name, Local File Name, Agent , Server Address), if I can locate the correct table I can inner join them into my SELECT statement. 

    I can see possibilities for the information you are sharing, but I'm looking for historical information that tells me the file name that was detected/processed. We have Applications that detect a wild carded file, then renames the file to a specific name.

    Thanks for sharing. I'll be researching the information you shared and contact you privately if I need more detail. Is that OK?

    Ken



  • 8.  RE: CAWA DE DB Schema
    Best Answer

    Posted Jun 08, 2020 01:14 PM
    Sure.  Reach out if you need.  Not sure where to find the information you are looking for, but it would have to exist somewhere in the database.  I hesitate to suggest the brute force approach of taking a file name you know and doing a global select to find any reference to it in any table.  I'm not sure what performance impact that would have but it doesn't seem like the kind of thing I'd want to try in a production environment.

    ------------------------------
    Andy Reimer
    ------------------------------



  • 9.  RE: CAWA DE DB Schema

    Posted Jun 23, 2020 11:13 AM
    Ken

    I am looking for the same info in the db for ftp jobs. Where you able to find the table that holds that info? 

    Thanks

    Tom


  • 10.  RE: CAWA DE DB Schema

    Posted Jun 24, 2020 01:12 PM
    Hi Tom,

    The short answer is no, I wasn't able to locate table where the FTP information resides. Andy Reimer had provided good info on extracting current information. 

    Ken


  • 11.  RE: CAWA DE DB Schema

    Posted Jun 24, 2020 10:07 PM
    Thanks for sharing this Andy, I was able to setup your example and it works great!

    Do you happen to have an example readily available and one you would mind sharing that would output the application name along with information such as the jobs within the application and the command and arguments they are running? I have a PS script that will output the job names but am struggling with grabbing extra information.


  • 12.  RE: CAWA DE DB Schema

    Posted Jun 26, 2020 11:14 AM
    Sure.  Perhaps I'll create a new thread for this

    ------------------------------
    Andy Reimer
    ------------------------------



  • 13.  RE: CAWA DE DB Schema

    Posted Jun 26, 2020 08:52 AM
    Ravi,

    The information in ESP_AMS_DF does have all the information of an Application in the single column. Is there another table similar to ESP_GENERIC_JOBS that has all of an FTP job definition information? I'm looking specifically for columns Name, Qualifier, Server, Remote File Name, Local File name.




  • 14.  RE: CAWA DE DB Schema

    Broadcom Employee
    Posted Jun 26, 2020 09:59 AM
    Hi,

    If you are using dSeries release R12.1 and above , please use the cli command - jobmap. 
    It will take event name as input , So please provide the event name with some FTP jobs and check the output . It should contain the details that you are looking for.

    Ravi Kiran