Search code examples
oracle-databaseoracle11gautosys

Writing autosys job information to Oracle DB

Here's my situation: we have no access to the autosys server other than using the autorep command. We need to keep detailed statistics on each of our jobs. I have written some Oracle database tables that will store start/end times, exit codes, JIL, etc.

What I need to know is what is the easiest way to output the data we require (which is all available in the autosys tables that we do not have access to) to an Oracle database.

Here are the technical details of our system: autosys version - I cannot figure out how to get this information Oracle version - 11g We have two separate environments - one for UAT/QA/IT and several PROD servers

Solution

  • Do something like below

    1. Create a table with the parameters you want to put. Put a key columns which should be auto generated. The jil column should be able to handle huge data. Also add one columns for sysdate.

    2. Create a shell script. Inside it do as follows

    3. "autorep -j -l0" to get all the jobs you want and put them in a file. -l0 is to ignore duplicate jobs. If a Box contain a job, then without -l0 you will get the job twice.

    4. create a loop and read all the job names one by one.

    5. In the loop, set varaibles for jobname/starttime/endtime/status (which all you can get from autorep -j . Then use a variable to hold jil by autorep -q -j

    6. Append all these variable values in a flat file.

    7. End the loop. After exiting a loop you wil end up with a file with all the job details.

    8. Then use SQL loader to put the data in your oracle table. You can hardcode a control file and use it for every run. But the content of data file will change for every run.

    Let me know if any part is not clear.